Control SQL - ODBC Process Removal Query

Explanation of SQL

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 Stuck on Printing Reports.

Risk of Data Corruption if Run Improperly

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.

Version Information

  • Entered : 7/23/2009
  • Version :

Related SQLs

See Also