SQL Server 2008 error - Incorrect syntax near '%'. [SQLSTATE 42000] (Error 102). The step failed

Question

I have a SQL query that fails with the above error when used in a SQL job designed to email the query results. The syntax is fine when running a normal query.

I see that other people have had this issue when using non-standard characters in server names, but my issue is with the % signs in my query I think and the other solutions don't work for me :

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'SQL-Server',
@recipients = 'XXXXX',
@subject = 'Missing Essence File Check',
@query = N'SELECT *   FROM ([omnibus].[dbo].[OmniBus_CLP_Clips] CLP 
inner join [omnibus].[dbo].[OmniBus_CLD_ClipDetails] CLD on CLP.FLE_ID = CLD.FLE_ID) 
inner join [omnibus].[dbo].[OmniBus_FLE_Files] fle on clp.FLE_ID = fle.fle_id
where CLD_user_data  like '%.mov%'
and CLD_user_data  not like '%.m2v%'
and CLD_user_data  not like '%.wav%'
and fle.FLE_file_deleted = 0;',
@attach_query_result_as_file = 1,
@query_attachment_filename = 'Missing Essence File Check.txt'

Can anyone advise on how to overcome this error?

Thanks,

Rob

You need to escape your single quotes by doubling them. Any good dynamic sql tutorial should demonstrate this. — Tab Alleman, Jul 06 '15 at 13:20

score 0 · Answer 1 · answered Jul 06 '15 at 13:23

0

You can change this line.

where CLD_user_data  like '''%.mov%'''
and CLD_user_data  not like '''%.m2v%'''
and CLD_user_data  not like '''%.wav%'''

answered Jul 06 '15 at 13:23

Mukesh Kalgude

4,814
2
17
32

This worked, but you've put 3 single quotes around each file extension and it should actually be two. – Rob Jul 09 '15 at 15:36

SQL Server 2008 error - Incorrect syntax near '%'. [SQLSTATE 42000] (Error 102). The step failed

1 Answers1