Overview
You interface LabVIEW with Microsoft SQL Server and users enter an unrealistic search criteria that results in large amounts of data being pulled from the database. LabVIEW will appear frozen until the search concludes. This article shows you how to terminate the query execution avoiding long wait times that frustrates users. From there your users can try a more reasonable search criteria.
Description
When it comes to search statements, users can erroneously enter unrealistic bounds for search criteria resulting in long wait times for the SQL Server to get data. Regardless of whether you use ADO.NET or ADODB (Com), once a synchronous SQL statement runs on the server side, LabVIEW will remain in waiting mode until the statement concludes running. Once users realize this they usually want to cancel the search process and re-try with a different 'narrow bounds' search criteria. One effective way of doing this is by killing the user session using a specific SPID number.
Here are the steps for doing this:
Steps to Implement or Execute Code
- Once a user logs in to SQL server database, get the SPID number for that active session. You can do this by the statement
SELECT @@SPID
The result will be an integer number equivalent to Process ID. For purposes of this example, lets assume that the SPID = 61
2. At some point the user may run a long query. You can create a button to Kill the long running Query that does the following
3. Create a separate connection to MS SQL Server which will result in new SPID say that the new SPID = 86
4. Use that new connection of SPID = 86 to kill the SPID = 61. To do this, just use the new connection to issue the command:
Kill 61
Where 61 is the process that was running the long Query.
Here are Stored Procedure Statements that can help you Encapsulate the SQL statements for 'Get SPID' and 'Kill SPID'
Stored Procedure to 'Get' SPID
-----------------------------------------------
CREATE PROC dbo.[lst_SpID]
AS
BEGIN
SET NOCOUNT ON;
SELECT @@SPID AS [SessIDProc]
END
GO
Stored Procedure to 'Kill' SPID
-----------------------------------------------
CREATE PROC [dbo].[Admin_Kill_SPID]
@SpID bigint
AS
BEGIN
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON;
DECLARE @KillStatement nvarchar(50)
SET @KillStatement = 'KILL ' + cast(@spid as varchar(3))
EXEC sp_executesql @KillStatement
END
Requirements
Software
Microsoft SQL Server (Ver: 2000 or later)
LabVIEW (Any Version)
Hardware
None or N/A
Additional Images or Video
