x

Error formatting query

Hello i was trying to execute the following query then i am facing the error please suggest how can i avoid this error. the query is:


Declare @query_text varchar(8000)
set @query_text = ' SELECT TOP 10
 RANK() OVER (ORDER BY (total_worker_time + 0.0) / execution_count DESC,sql_handle,statement_start_offset) AS row_no
    ,(RANK() OVER (ORDER BY (total_worker_time + 0.0) / execution_count DESC,sql_handle,statement_start_offset)) % 2 AS l1
    ,creation_time
    ,last_execution_time
    ,(total_worker_time + 0.0) / 1000 AS total_worker_time
    ,(total_worker_time + 0.0) / (execution_count * 1000) AS [AvgCPUTime]
    ,total_logical_reads AS [LogicalReads]
    ,total_logical_writes AS [LogicalWrites]
    ,execution_count
    ,total_logical_reads + total_logical_writes AS [AggIO]
    ,(total_logical_reads + total_logical_writes) / (execution_count + 0.0) AS [AvgIO]
    ,CASE
 WHEN sql_handle IS NULL
 THEN
 '' ''
 ELSE
 (SUBSTRING(st.text,(qs.statement_start_offset + 2) / 2,(CASE
 WHEN qs.statement_end_offset = -1 THEN
 LEN(CONVERT(NVARCHAR(MAX),st.text)) * 2
 ELSE
 qs.statement_end_offset
 END - qs.statement_start_offset) / 2))
 END AS query_text
    ,DB_NAME(st.dbid) AS db_name
    ,st.objectid AS object_id
 FROM
 sys.dm_exec_query_stats qs
 CROSS APPLY sys.dm_exec_sql_text(sql_handle) st'
EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'My profiler name',
    @recipients = 'sand143eep@gmail.com',
    @query = '@query_text' ,
    @subject = 'Top10 queries used the maxcpu',
    @attach_query_result_as_file = 1 ;

and the error is :


Msg 22050, Level 16, State 1, Line 0
Error formatting query, probably invalid parameters
Msg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 495
Query execution failed: Msg 137, Level 15, State 2, Server zx009435\qikxmpm, Line 1
Must declare the scalar variable "@query_text".
more ▼

asked Oct 12, 2012 at 12:53 PM in Default

sand143eep gravatar image

sand143eep
811 29 37 41

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

remove the quotes on this line:

@query = '@query_text' ,
more ▼

answered Oct 12, 2012 at 12:57 PM

Kev Riley gravatar image

Kev Riley ♦♦
53.8k 47 49 76

it worked
Oct 12, 2012 at 01:18 PM sand143eep
(comments are locked)
10|1200 characters needed characters left

I believe that you need to remove the quotes from around @query_text in the sp_send_dbmail portion of your code. It would need to look like this:

DECLARE @query_text VARCHAR(8000)
SET @query_text = ' SELECT TOP 10 RANK() OVER (ORDER BY (total_worker_time + 0.0) / execution_count DESC,sql_handle,statement_start_offset) AS row_no ,(RANK() OVER (ORDER BY (total_worker_time + 0.0) / execution_count DESC,sql_handle,statement_start_offset)) % 2 AS l1 ,creation_time ,last_execution_time ,(total_worker_time + 0.0) / 1000 AS total_worker_time ,(total_worker_time + 0.0) / (execution_count * 1000) AS [AvgCPUTime] ,total_logical_reads AS [LogicalReads] ,total_logical_writes AS [LogicalWrites] ,execution_count ,total_logical_reads + total_logical_writes AS [AggIO] ,(total_logical_reads + total_logical_writes) / (execution_count + 0.0) AS [AvgIO] ,CASE WHEN sql_handle IS NULL THEN '' '' ELSE (SUBSTRING(st.text,(qs.statement_start_offset + 2) / 2,(CASE WHEN qs.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX),st.text)) * 2 ELSE qs.statement_end_offset END - qs.statement_start_offset) / 2)) END AS query_text ,DB_NAME(st.dbid) AS db_name ,st.objectid AS object_id FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(sql_handle) st'

EXEC msdb.dbo.sp_send_dbmail @profile_name = 'My profiler name'
    , @recipients = 'sand143eep@gmail.com'
    , @query = @query_text,
    , @subject = 'Top10 queries used the maxcpu'
    , @attach_query_result_as_file = 1; 
Hope this helps!
more ▼

answered Oct 12, 2012 at 01:01 PM

JohnM gravatar image

JohnM
6.9k 1 3 7

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1951

asked: Oct 12, 2012 at 12:53 PM

Seen: 766 times

Last Updated: Oct 12, 2012 at 01:18 PM