December 23, 2009
As you’ve experienced, test data and junk typically ends up in a development database. At some point, you might want to get your tables in order because Lookup table Id columns sometimes match to constants in your code, and so
Here is what you’d do on SQL Server…
If you want to insert specific IDs, you would typically use IDENTITY_INSERT:
SET IDENTITY_INSERT [myTable] ON
INSERT myTable (ID) VALUES (42)
INSERT myTable (ID) VALUES (43)
SET IDENTITY_INSERT [myTable] OFF
You typically do this if you want to jump ahead of existing IDs.
Note: If the Ids already exist, you will get a Primary key violation error.
After this operation, new rows will start with IDs from 44.
If you want to backtrack on IDs (and add new data) and want to use auto-increment as is, you would want to delete rows in a table and reset the auto-increment id value.
In order to do this, we need to turn to the back-door man in SQL Server, viz. dbcc
e.g. The following statement will set the next Id of myTable to 42
DBCC CHECKIDENT (myTable, RESEED, 42)
For details on CheckIdent, go to: http://msdn.microsoft.com/en-us/library/aa258817(SQL.80).aspx
June 27, 2008
Many people, including myself, have problems whenever we setup a new machine with SQL Server and have to connect to SQL Server remotely. I thought I’d make a note of the fix here as a quick reference.
The most common ways of connecting to a SQL Server are with using named pipes (specifying the computer name) or tcp/ip (specifying the Server IP and port)
If you’re using a SQL Server client to connect to the remote server, in the auth dialog, click on Options and select network protocol = named pipes.
Note the weird UI change as the Connection Properties now becomes a tab.
Specify the name of the remote computer and other info and you should be good to go.
In order to access via TCP/IP for JDBC and other clients, you need to first configure SQL Server to accept TCP/IP connections. This is done by going to Start -> Microsoft SQL Server xxx – Configuration Tools – SQL Configuration Manager. Expand the Network Configuration tree item and you should see Protocols for MSSQLSERVER. In the details pane, right-click on the required prototocol, in this case TCP/IP and enable it. So now, your SQL Server engine can accept TCP/IP connections, however your computer may not.
If you use XP or Vista out of the box, most likely that the Windows Firewall would be active and needs to be tweaked to allow incoming TCP/IP requests to SQL Server.
To do this, open the Windows Firewall (via Control Panel), go to the Exceptions tab and add a Port 1433 (used by default by SQL Client) or whatever port number you specify in your client connection.
Get the IP address of the Server (Choose Start – Run, type cmd to get a command prompt and type ipconfig and hit Enter) and specify it with the port in your client, if required, and you should be good to go.