Solved

the MS SQL IF and else statement

Posted on 2014-03-09
6
215 Views
Last Modified: 2014-03-13
Dear all,

right now I have scirpt and I need to add mroe logic on it:

1) if the query string we scan out is begin with "FETCH_API....." then we do more !
2) if the query string is not begin with FETCH_API, then we do other loop:

existing code:

Create proc dbo.check_GatewayOracle_longrunningQueries  @Total_time_Executed int
as
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

Declare 
@spid int, 
@loginname Varchar(50), 
@programname Varchar(50), 
@host  Varchar(50), 
---------------------------------
@Cursor_CreationTime datetime,
@Cursor_Properties nvarchar(256),
@Cursor_query nvarchar(max), 
@Cursor_Name nvarchar(256), 
--------------------------------
@startime datetime, 
@lastudpatetime datetime , 
@DBName Varchar(50), 
@query  Varchar(4000),   
@msg Varchar(8000),
@kill varchar(50), 
@servername varchar(100)
 
DECLARE db_cursor CURSOR FOR 
SELECT spid, loginame, program_name, hostname as "From host", login_time, last_batch, DB_NAME(dbid) AS "Against database" , 
(SELECT text FROM sys.dm_exec_sql_text(sql_handle))as "Query executed" 
FROM master..sysprocesses WHERE loginame= 'ixapi' and open_tran > 0 and DATEDIFF (minute, login_time, GETDATE()) > 
@Total_time_Executed  ;

OPEN db_cursor;

set @msg = 'Alert: A long running transactions from gateway were killed that can potentially make tempdB full';
set @servername= CAST((select SERVERPROPERTY ('ServerName') ) as varchar (100));

FETCH NEXT FROM db_cursor INTO @spid, @loginname , @programname , @host , @startime , @lastudpatetime , @DBName , @query  ;
WHILE @@FETCH_STATUS = 0  
BEGIN  

------------------------------------------------------------------------------
		/*check the cursor's content if it is beginning with FETCH_API statement */
	    DECLARE Cursor_content CURSOR FOR 
			SELECT c.name, c.properties as "The cursor properties", 
			c.creation_time, t.text
			FROM sys.dm_exec_cursors (@spid) c
			CROSS APPLY sys.dm_exec_sql_text (c.sql_handle) t 
		/*    */
		OPEN Cursor_content;
		FETCH NEXT FROM Cursor_content INTO @Cursor_Name, @Cursor_Properties, @Cursor_CreationTime, @Cursor_query;

		WHILE @@FETCH_STATUS = 0  
		BEGIN  
		set @query= @query+ 'Name: ' + @Cursor_Name+ '
		Propert is: ' + @Cursor_Properties + ', it created at:' + CONVERT(VARCHAR,@Cursor_CreationTime ,102) + ' 
		the content is: '+ @Cursor_query;
		FETCH NEXT FROM Cursor_content INTO @Cursor_Name, @Cursor_Properties, @Cursor_CreationTime, @Cursor_query;
		END;
		CLOSE Cursor_content;
		DEALLOCATE Cursor_content; 
---------------------------------------------------------------------------------

  set @query ='The long running transaction(s) from gateway were killed on ' + @servername+ ' as it ran for more than ' + cast(@Total_time_Executed as varchar(5)) + ' minutes and the query is: ' + @query 

  set @kill = 'Kill ' + cast(@spid as varchar(5)) + ';';
  exec(@kill)  ;

	EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'Account_1',
    @recipients = <email address> ,
    @body = @query,
    @subject = @msg

       FETCH NEXT FROM db_cursor INTO @spid, @loginname , @programname , @host , @startime , @lastudpatetime , @DBName , @query  ;
END;
CLOSE db_cursor
DEALLOCATE db_cursor
end

GO

Open in new window


I am not sure if the IF else loop and condition should be this format:

Create proc dbo.check_GatewayOracle_longrunningQueries  @Total_time_Executed int
as
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

Declare 
@spid int, 
@loginname Varchar(50), 
@programname Varchar(50), 
@host  Varchar(50), 
---------------------------------
@Cursor_CreationTime datetime,
@Cursor_Properties nvarchar(256),
@Cursor_query nvarchar(max), 
@Cursor_Name nvarchar(256), 
--------------------------------
@startime datetime, 
@lastudpatetime datetime , 
@DBName Varchar(50), 
@query  Varchar(4000),   
@msg Varchar(8000),
@kill varchar(50), 
@servername varchar(100)
 
DECLARE db_cursor CURSOR FOR 
SELECT spid, loginame, program_name, hostname as "From host", login_time, last_batch, DB_NAME(dbid) AS "Against database" , 
(SELECT text FROM sys.dm_exec_sql_text(sql_handle))as "Query executed" 
FROM master..sysprocesses WHERE loginame= 'ixapi' and open_tran > 0 and DATEDIFF (minute, login_time, GETDATE()) > 
@Total_time_Executed  ;

OPEN db_cursor;

set @msg = 'Alert: A long running transactions from gateway were killed that can potentially make tempdB full';
set @servername= CAST((select SERVERPROPERTY ('ServerName') ) as varchar (100));

FETCH NEXT FROM db_cursor INTO @spid, @loginname , @programname , @host , @startime , @lastudpatetime , @DBName , @query  ;
WHILE @@FETCH_STATUS = 0  
BEGIN  

------------------------------------------------------------------------------
		/*check the cursor's content if it is beginning with FETCH_API statement */

IF @query = "FETCH_API%" 
BEGIN
	    DECLARE Cursor_content CURSOR FOR 
			SELECT c.name, c.properties as "The cursor properties", 
			c.creation_time, t.text
			FROM sys.dm_exec_cursors (@spid) c
			CROSS APPLY sys.dm_exec_sql_text (c.sql_handle) t 
		/*    */
		OPEN Cursor_content;
		FETCH NEXT FROM Cursor_content INTO @Cursor_Name, @Cursor_Properties, @Cursor_CreationTime, @Cursor_query;

		WHILE @@FETCH_STATUS = 0  
		BEGIN  
		set @query= @query+ 'Name: ' + @Cursor_Name+ '
		Propert is: ' + @Cursor_Properties + ', it created at:' + CONVERT(VARCHAR,@Cursor_CreationTime ,102) + ' 
		the content is: '+ @Cursor_query;
		FETCH NEXT FROM Cursor_content INTO @Cursor_Name, @Cursor_Properties, @Cursor_CreationTime, @Cursor_query;
		END;
		CLOSE Cursor_content;
		DEALLOCATE Cursor_content; 

END
ELSE
---------------------------------------------------------------------------------

  set @query ='The long running transaction(s) from gateway were killed on ' + @servername+ ' as it ran for more than ' + cast(@Total_time_Executed as varchar(5)) + ' minutes and the query is: ' + @query 

  set @kill = 'Kill ' + cast(@spid as varchar(5)) + ';';
  exec(@kill)  ;

	EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'Account_1',
    @recipients = <email address> ,
    @body = @query,
    @subject = @msg

       FETCH NEXT FROM db_cursor INTO @spid, @loginname , @programname , @host , @startime , @lastudpatetime , @DBName , @query  ;
END;
CLOSE db_cursor
DEALLOCATE db_cursor
end

GO

Open in new window

0
Comment
Question by:marrowyung
  • 4
  • 2
6 Comments
 
LVL 12

Expert Comment

by:Harish Varghese
ID: 39916917
Hello,

Since you are looking for Query beginning with "FETCH_API", you need to use LIKE operator instead of "=" as below:
IF @query LIKE 'FETCH_API%'

And you need to enclose the statements in the ELSE part within BEGIN and END blocks. If you want to execute the KILL part only on the ELSE condition, then do as below:
ELSE
BEGIN
     set @query ='The long running transaction(s) from gateway were killed on ' + @servername+ ' as it ran for more than ' + cast(@Total_time_Executed as varchar(5)) + ' minutes and the query is: ' + @query 
     set @kill = 'Kill ' + cast(@spid as varchar(5)) + ';';
     exec(@kill)  ;
END

Open in new window

If you want the email to be sent only when the process is killed, then include the send_dbmail statement also inside the BEGIN and END.

-Harish
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39917164
so the whole thing will be:

Create proc dbo.check_GatewayOracle_longrunningQueries  @Total_time_Executed int
as
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

Declare 
@spid int, 
@loginname Varchar(50), 
@programname Varchar(50), 
@host  Varchar(50), 
---------------------------------
@Cursor_CreationTime datetime,
@Cursor_Properties nvarchar(256),
@Cursor_query nvarchar(max), 
@Cursor_Name nvarchar(256), 
--------------------------------
@startime datetime, 
@lastudpatetime datetime , 
@DBName Varchar(50), 
@query  Varchar(4000),   
@msg Varchar(8000),
@kill varchar(50), 
@servername varchar(100)
 
DECLARE db_cursor CURSOR FOR 
SELECT spid, loginame, program_name, hostname as "From host", login_time, last_batch, DB_NAME(dbid) AS "Against database" , 
(SELECT text FROM sys.dm_exec_sql_text(sql_handle))as "Query executed" 
FROM master..sysprocesses WHERE loginame= 'ixapi' and open_tran > 0 and DATEDIFF (minute, login_time, GETDATE()) > 
@Total_time_Executed  ;

OPEN db_cursor;

set @msg = 'Alert: A long running transactions from gateway were killed that can potentially make tempdB full';
set @servername= CAST((select SERVERPROPERTY ('ServerName') ) as varchar (100));

FETCH NEXT FROM db_cursor INTO @spid, @loginname , @programname , @host , @startime , @lastudpatetime , @DBName , @query  ;
WHILE @@FETCH_STATUS = 0  
BEGIN  
IF @query LIKE 'FETCH_API%'
begin 
------------------------------------------------------------------------------
		/*check the cursor's content  */
	    DECLARE Cursor_content CURSOR FOR 
			SELECT c.name, c.properties as "The cursor properties", 
			c.creation_time, t.text
			FROM sys.dm_exec_cursors (@spid) c
			CROSS APPLY sys.dm_exec_sql_text (c.sql_handle) t 
		/*    */
		OPEN Cursor_content;
		FETCH NEXT FROM Cursor_content INTO @Cursor_Name, @Cursor_Properties, @Cursor_CreationTime, @Cursor_query;

		WHILE @@FETCH_STATUS = 0  
		BEGIN  
		set @query= @query+ 'Name: ' + @Cursor_Name+ '
		Propert is: ' + @Cursor_Properties + ', it created at:' + CONVERT(VARCHAR,@Cursor_CreationTime ,102) + ' 
		the content is: '+ @Cursor_query;
		FETCH NEXT FROM Cursor_content INTO @Cursor_Name, @Cursor_Properties, @Cursor_CreationTime, @Cursor_query;
		END;
		CLOSE Cursor_content;
		DEALLOCATE Cursor_content; 
---------------------------------------------------------------------------------
end
else 
begin 
  set @query ='The long running transaction(s) from gateway were killed on ' + @servername+ ' as it ran for more than ' + cast(@Total_time_Executed as varchar(5)) + ' minutes and the query is: ' + @query 

  set @kill = 'Kill ' + cast(@spid as varchar(5)) + ';';
  exec(@kill)  ;

	EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'Account_1',
    @recipients = <email>,
    @body = @query,
    @subject = @msg

       FETCH NEXT FROM db_cursor INTO @spid, @loginname , @programname , @host , @startime , @lastudpatetime , @DBName , @query  ;
END;
end;

CLOSE db_cursor
DEALLOCATE db_cursor
end

GO

Open in new window


?

So the else statment also need end statement ?
0
 
LVL 12

Accepted Solution

by:
Harish Varghese earned 500 total points
ID: 39917216
Yes.
Move the FETCH NEXT statement outside the ELSE BEGIN and END block, as shown here:
else 
else 
begin 
	set @query ='The long running transaction(s) from gateway were killed on ' + @servername+ ' as it ran for more than ' + cast(@Total_time_Executed as varchar(5)) + ' minutes and the query is: ' + @query 

	set @kill = 'Kill ' + cast(@spid as varchar(5)) + ';';
	exec(@kill)  ;

	EXEC msdb.dbo.sp_send_dbmail
	@profile_name = 'Account_1',
	@recipients = <email>,
	@body = @query,
	@subject = @msg
END
   FETCH NEXT FROM db_cursor INTO @spid, @loginname , @programname , @host , @startime , @lastudpatetime , @DBName , @query  ;
end

Open in new window

-Harish
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 1

Author Comment

by:marrowyung
ID: 39920137
let wait for one night and see it catch anything. thanks for your advice.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39928343
from here:

http://technet.microsoft.com/en-us/library/ms182717.aspx

can't see why one more END statment. if I remove the last End statement, it complied with error !
0
 
LVL 1

Author Closing Comment

by:marrowyung
ID: 39928349
once after I make sure of the SSMS feature, I know why there are 3 x END; statement
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Slow Connectivity over ODBC 8 32
Add different cell to otherwise similiar row 4 38
Help Required 3 92
Find SQL query used by application 3 17
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

785 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question