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
Solved

error in script

Posted on 2014-03-07
10
278 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

856 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