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]
 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 :
 +
 +
 +