Solved

error in script

Posted on 2014-03-07
10
267 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 500 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
 
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
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.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

758 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

19 Experts available now in Live!

Get 1:1 Help Now