SQL Job error with OPENQUERY

Question

I am trying to run a SQL job with this TSQL script, and the following error is raised. The code runs well in a query window!

What is the problem with this code in the sql job?

SELECT *    
FROM OPENQUERY("192.168.1.1",'SET FMTONLY OFF; EXEC spNewTest @Param1 = ''Test1''')

Error Message: Executed as user: DOMAIN\USER. Incorrect syntax near '192.168.1.1'. [SQLSTATE 42000] (Error 102)

Regards, Elio Fernandes

Generally, when you have a problem with a job, copy paste the SQL command into a new query window and try it there. This way the noise of it being a "job" will no longer mask the actual TSQL error. — ajeh, Sep 14 '16 at 17:45

score 0 · Answer 1 · edited May 23 '17 at 12:15

0

Use this

SELECT * FROM OPENQUERY('192.168.1.1','SET FMTONLY OFF; EXEC spNewTest @Param1 = ''Test1''')

instead of this

SELECT * FROM OPENQUERY("192.168.1.1",'SET FMTONLY OFF; EXEC spNewTest @Param1 = ''Test1''')

EDIT: The difference between simple comma and double comma is that the second one isn't use it in SQL Server, this post from Vineet in 2010 can support the answer

What is the difference between single and double quotes in SQL?

Thanks Charlie Fish for the observation

edited May 23 '17 at 12:15

Community

1
1

answered Sep 14 '16 at 17:39

Alex Gonzalez

9
2

Would be helpful to explain why that would work. Also why would changing from single quotes to double quotes make a difference. Some more explanation might be helpful. – Charlie Fish Sep 14 '16 at 17:49
I tried to run the job with single quote as you said and the error still remains. This same command in a query with does not work as well. – Elio Fernandes Sep 14 '16 at 17:54

score 0 · Answer 2 · answered Sep 14 '16 at 18:22

0

I just replaced the double quotes with squares brackets [192.168.1.1] and the job is running as expected.

Thanks.

answered Sep 14 '16 at 18:22

Elio Fernandes

1,335
2
14
29

SQL Job error with OPENQUERY

2 Answers2