Differences

This shows you the differences between two versions of the page.

Link to this comparison view

control_sql_-_odbc_process_removal_query [2019/01/27 11:29] (current)
Line 1: Line 1:
 +
 +
 +
 +
 +===== 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 [[control_error_-_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.
 +
 +
 +
 +===== SQL =====
 +
 +
 +
 +<code sql>
 +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];​
 +</​code>​
 +
 +**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 :
 +
 +
 +