SQL Runs as a Query but not as a step in a job
Hi,
We have some jobs that run and insert data from one table to another using a view. Something like:
Insert into table1
(column1, column2, column3)
select column1, column2, column3 from DB2.dbo.View1
If I run that code as a query it executes fine. However when I run it as a step in a job I get this error:
"INSERT failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'." Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations. [SQLSTATE 42000] (Error 1934). The step failed.
When I got the error I searched for it and found many posts that said I should turn "Quoted_Identifier" on when creating the view. I had done that but just to be sure I dropped all the views and re-created them. I still get the same error.
Any ideas?
We have some jobs that run and insert data from one table to another using a view. Something like:
Insert into table1
(column1, column2, column3)
select column1, column2, column3 from DB2.dbo.View1
If I run that code as a query it executes fine. However when I run it as a step in a job I get this error:
"INSERT failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'." Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations. [SQLSTATE 42000] (Error 1934). The step failed.
When I got the error I searched for it and found many posts that said I should turn "Quoted_Identifier" on when creating the view. I had done that but just to be sure I dropped all the views and re-created them. I still get the same error.
Any ideas?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
"SQL Runs as a Query but not as a step in a job"
Is the login that "Runs as a Query " identical with the account starting SQL Agent? a job step runs under SQL Agent Service account and a query as the user connected to the database.
Is the login that "Runs as a Query " identical with the account starting SQL Agent? a job step runs under SQL Agent Service account and a query as the user connected to the database.
If QUOTED_IDENTIFIER has been on the whole time, its possible it needs to be turned off. I just say this because it seems logical. If the above suggestions fail, give this a try.
http://msdn.microsoft.com/en-us/library/ms174393(v=sql.100).aspx
When SET QUOTED_IDENTIFIER is ON, identifiers can be delimited by double quotation marks, and literals must be delimited by single quotation marks. When SET QUOTED_IDENTIFIER is OFF, identifiers cannot be quoted and must follow all Transact-SQL rules for identifiers. For more information, see Identifiers. Literals can be delimited by either single or double quotation marks.