This query kills all ODBC process in SQL Server that are created for generating reports. This is useful for removing SQL locks without having to close Control or the SSLIP.

See control_error_-_stuck_on_printing_reports.

Moderate: No data is modified, but there is a small risk of killing a report query that is in progress, and causing the report a throw an error.

DECLARE @DatabaseName VARCHAR(20);
SET @DatabaseName = ???;
DECLARE @ProcessId INT;
DECLARE [cursorProcess] CURSOR FOR 
        SELECT sp.spid
        FROM sys.sysprocesses sp
        LEFT JOIN sys.databases db ON (sp.dbid = db.database_id)
        WHERE sp.program_name LIKE 'Cyrious% ODBC%' AND db.name = @DatabaseName
OPEN [cursorProcess]
FETCH NEXT FROM [cursorProcess] INTO @ProcessId
WHILE @@FETCH_STATUS = 0
BEGIN
 EXEC('KILL ' + @ProcessId);
 FETCH NEXT FROM [cursorProcess] INTO @ProcessId
END
CLOSE [cursorProcess];
DEALLOCATE [cursorProcess];

Note: The “???” in the second line will need to be replaced with the name of the SQL database wrapped in single quotes.

• Entered : 7/23/2009
• Version :