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 [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 = @DatabaseName
OPEN [cursorProcess]
FETCH NEXT FROM [cursorProcess] INTO @ProcessId
 EXEC('KILL ' + @ProcessId);
 FETCH NEXT FROM [cursorProcess] INTO @ProcessId
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 :
You could leave a comment if you were logged in.