The data types varchar and nvarchar are incompatible in the modulo operator

Question

Post reply

The data types varchar and nvarchar are incompatible in the modulo operator

Bhuvnesh

SSC Guru

Points: 59351
More actions
January 18, 2010 at 4:59 am

#215799

while running the below query , i m getting the error :
"The data types varchar and nvarchar are incompatible in the modulo operator."
----------------------------------------------------------------------
USE COMPANYNAME_PROD
GO
DECLARE @lstr nvarchar(max),
@Query varchar(100),
@DB varchar(100)
--Checking Query Performance-----------
SET @lstr = '
SELECT TOP 5
sdest.dbid
,sdest.[text] AS Batch_Object,
SUBSTRING(sdest.[text], (sdeqs.statement_start_offset/2) + 1,
((CASE sdeqs.statement_end_offset
WHEN -1 THEN DATALENGTH(sdest.[text]) ELSE sdeqs.statement_end_offset END
- sdeqs.statement_start_offset)/2) + 1) AS SQL_Statement
, sdeqp.query_plan
, sdeqs.execution_count
, sdeqs.total_physical_reads
,(sdeqs.total_physical_reads / sdeqs.execution_count) AS average_physical_reads
, sdeqs.total_logical_writes
, (sdeqs.total_logical_writes / sdeqs.execution_count) AS average_logical_writes
, sdeqs.total_logical_reads
, (sdeqs.total_logical_reads / sdeqs.execution_count) AS average_logical_lReads
, sdeqs.total_clr_time
, (sdeqs.total_clr_time / sdeqs.execution_count) AS average_CLRTime
, sdeqs.total_elapsed_time
, (sdeqs.total_elapsed_time / sdeqs.execution_count) AS average_elapsed_time
, sdeqs.last_execution_time
, sdeqs.creation_time
FROM sys.dm_exec_query_stats AS sdeqs
CROSS apply sys.dm_exec_sql_text(sdeqs.sql_handle) AS sdest
CROSS apply sys.dm_exec_query_plan(sdeqs.plan_handle) AS sdeqp
WHERE sdeqs.last_execution_time > DATEADD(HH,-2,GETDATE())
--AND sdest.dbid = (SELECT DB_ID(''' + @DB + '''))
--AND sdest.[text] LIKE '''%@Query%'''
ORDER BY execution_count DESC'
PRINT @lstr
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

dsdeming Ten Centuries Points: 1236 More actions · Answer 1

I think you're having a problem concatenating the % wildcard and the @Query variable.

Try something like this: AND sdest.[text] LIKE ''%' + @Query + '%''

HTH

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

Bhuvnesh (1/18/2010)
while running the below query , i m getting the error :
"The data types varchar and nvarchar are incompatible in the modulo operator."

Please post the result of the PRINT statement.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

LutzM SSC Guru Points: 107049 More actions · Answer 3

The percentage sign is misplaced. Therefore it's treated like

SELECT @Query%

Try

AND sdest.[text] LIKE ''%' + @Query +'%''

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 4

Thanks

it working well

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)