Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

error in script

Posted on 2014-03-07
10
Medium Priority
?
296 Views
Last Modified: 2014-03-09
Dear all,

right now I have the following script to find out the costly query from the ixapi login and kill it if it rans for more than 30 minutes:

USE [master]
GO
/****** Object:  StoredProcedure [dbo].[check_longrunningQueries]    */
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER 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), 
@startime datetime, 
@lastudpatetime datetime , 
---------------------------------
@Cursor_CreationTime datetime,
@Cursor_Properties nvarchar(256),
@Cursor_query nvarchar(max), 
@Cursor_Name nvarchar(256), 
--------------------------------
@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 was killed';
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  */
		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:' + @Cursor_CreationTime + ' 
		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 = <receipt>,
    @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

Open in new window


problems come when I add this piece of code in the middle (that's why I have the --- to isolate it):

/*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:' + @Cursor_CreationTime + ' 
		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;

Open in new window


What I can image is that, the variable I use for that part is of type:

nvarchar(256)

but when concating it:

		set @query= @query+ 'Name: ' + @Cursor_Name+ '
		Propert is: ' + @Cursor_Properties + ', it created at:' + @Cursor_CreationTime + ' 
		the content is: '+ @Cursor_query;

Open in new window


and

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 

Open in new window


probably has problem as :
1) @query  Varchar(4000),
2) @Cursor_query nvarchar(max),

as they are different typoe so they can't concat to in a singal string to print out in the email.

any idea?
0
Comment
Question by:marrowyung
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 3
10 Comments
 
LVL 15

Accepted Solution

by:
Anuj earned 2000 total points
ID: 39911918
I think the problem is while concatenating datetime data type with varchar, you cannot concatenate VARCHAR and a DATETIME variable.

set @query= @query+ 'Name: ' + @Cursor_Name+ '
		Propert is: ' + @Cursor_Properties + ', it created at:' + @Cursor_CreationTime + ' 
		the content is: '+ @Cursor_query;

Open in new window



your @Cursor_CreationTime variable is date time, here you should convert that to varchar
CONVERT(VARCHAR,@Cursor_CreationTime ,102) to make this work
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39911931
but not convert to Varchar(4000) ? nvarchar and varchar can be concat together like what I am doing ?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39911932
what is 102 means?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:marrowyung
ID: 39911933
so it will be:"

set @query= @query+ 'Name: ' + @Cursor_Name+ '
		Propert is: ' + @Cursor_Properties + ', it created at:' + CONVERT(VARCHAR,@Cursor_CreationTime ,102)+ ' 
		the content is: '+ @Cursor_query;

Open in new window


?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39911940
0
 
LVL 15

Expert Comment

by:Anuj
ID: 39912038
102 is just a date format, you can use other standards as well mentioned in that link depending on your requirement. 103 gives you dd/mm/yyy.

eg SELECT CONVERT(VARCHAR,getdate(),103) results in 07/03/2014 i.e in dd/mm/yyyy format.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39912554
ok ,let see if I get error message again, so you sure it is not about varchar concat with nvarchar ?
0
 
LVL 15

Expert Comment

by:Anuj
ID: 39912573
I am sure the conflict is with datetime and varchar :-)
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39914242
ok, let's try !
thanks.
0
 
LVL 1

Author Closing Comment

by:marrowyung
ID: 39916640
at this moment I don't see anything bad in our side and I asume this is ok and this totally makes a lot of sense, close this one and give you score first.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

670 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