Solved

the MS SQL IF and else statement

Posted on 2014-03-09
6
210 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Parse field in SQL View 15 97
Need a starter for ETL protocol? 4 42
SSRS troubles 4 14
MS SQL Server - Looking to filter rows based on column value 3 0
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

910 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now