Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

error in script

Posted on 2014-03-07
10
Medium Priority
?
304 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
  • 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
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.

 
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

[Webinar] Cloud Security

In this webinar you will learn:

-Why existing firewall and DMZ architectures are not suited for securing cloud applications
-How to make your enterprise “Cloud Ready”, and fix your aging DMZ architecture
-How to transform your enterprise and become a Cloud Enabler

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

885 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