• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 333
  • Last Modified:

error in script

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
marrowyung
Asked:
marrowyung
  • 7
  • 3
1 Solution
 
AnujSQL Server DBACommented:
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
 
marrowyungSenior Technical architecture (Data)Author Commented:
but not convert to Varchar(4000) ? nvarchar and varchar can be concat together like what I am doing ?
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
what is 102 means?
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
marrowyungSenior Technical architecture (Data)Author Commented:
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
 
marrowyungSenior Technical architecture (Data)Author Commented:
0
 
AnujSQL Server DBACommented:
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
 
marrowyungSenior Technical architecture (Data)Author Commented:
ok ,let see if I get error message again, so you sure it is not about varchar concat with nvarchar ?
0
 
AnujSQL Server DBACommented:
I am sure the conflict is with datetime and varchar :-)
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
ok, let's try !
thanks.
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

  • 7
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now