CODEWITHSUNDEEP
sqlsyntax-errorsql-job
Matt
Matt

Reputation: 15061

SQL Job running a few select statements Incorrect syntax near '\'. [SQLSTATE 42000] (Error 102)

I have a SQL job that contains a few select statements (4 views and 1 text).

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'Database Email',
    @recipients = '[email protected]',
    @subject = 'SQL Replication Deletion Check',
    @query = 'SELECT '\\server1\folder1\SQL\check.sql' AS [Script]
SELECT * FROM [repserv].[dbo].[1repdupecheck]
SELECT * FROM [repserv].[dbo].[2repdupecheck]
SELECT * FROM [repserv].[dbo].[3repdupecheck]
SELECT * FROM [repserv].[dbo].[4repdupecheck]',
    @attach_query_result_as_file = 1;

I currently get the following error when running it.

Message
Executed as user: SERVER1\sqlusr. Incorrect syntax near '\'. [SQLSTATE 42000] (Error 102).  The step failed.

Now I have also tried taking out the line with the '\' in and it works so I can only assume a bracket or something needs to be put around the back slashes in the select text.

Upvotes: 0

Views: 4831

Answers (2)

Matt
Matt

Reputation: 15061

Got it with help from the below, just needed to add double single quotes (didn't need to double up on back slashes)

'SELECT ''\\server1\folder1\SQL\check.sql'' AS [Script]

Upvotes: 0

juergen d
juergen d

Reputation: 204854

You need to escape the backslashes and quotes

'SELECT ''\\\\server1\\folder1\\SQL\\check.sql'' AS [Script]

Upvotes: 4

Related Questions