ADO/ODBC connection string for SQL Server 2008

If upgrading SQL Server from 2005 to 2008, one thing to note is that the provider name has changed for SQL Server 2008. The provider portion of the string in both ADO and ODBC connection strings should be either

Provider=SQLNCLI10

or

Provider=SQLNCLI10.1

depending on which version of SQL Native Client is installed.

http://blogs.msdn.com/b/mattm/archive/2007/06/07/sql-native-client-10-provider-name-change.aspx

http://connectionstringexamples.com/staticpages/index.php?page=20081229061112484

.NET applications will be unaffected as you do not need to provide the provider name in the connection string when using the .NET provider.

Ending SQL Server connections

To end a connection to an SQL Server instance, use the KILL command passing the SPID associated with the connection.

To kill all users from a given database, insert the output of sp_who into a temporary table, then set up a cursor with the appropriate WHERE clause and step through issuing a KILL command for each row:

DECLARE @Users TABLE(
[spid] int,
[ecid] int,
[status] NVARCHAR(60),
[login] NVARCHAR(100),
[hostname] NVARCHAR(100),
[blk] int,
[dbname] SYSNAME NULL,
[cmd] NVARCHAR(60),
[request_id] INT
)
INSERT INTO @Users EXEC SP_WHO
DECLARE [LoginCursor] CURSOR READ_ONLY FOR
SELECT [spid], [login]
FROM @Users
WHERE [dbname] = 'DBName' AND [spid] <> @@SPID
DECLARE @spid INT,
@login NVARCHAR(100)
OPEN LoginCursor
FETCH NEXT FROM [LoginCursor] INTO @spid, @login
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2) EXEC ('KILL ' + @spid)
FETCH NEXT FROM [LoginCursor] INTO @spid, @login
END
CLOSE [LoginCursor]
DEALLOCATE [LoginCursor]

Users that have been disconnected should reestablish their connections. (SQL Server Management Studio clients should reconnect queries. ODBC clients should be restarted.)