July 8, 2010 at 1:50 pm
hi guys, i have my dev and production instances in two different servers with the same database with the same settings. When i run the following script in my development DB, i get no errors, but when i run it in my production DB i get the following error:
SET NOCOUNT OFF
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
IF OBJECT_ID(N'TempDB..#tempDistributors', N'U') IS NOT NULL
DROP TABLE #tempDistributors
CREATE TABLE #tempDistributors (
ID INT PRIMARY KEY,
Name VARCHAR(100) NOT NULL,
Description VARCHAR(250) NOT NULL
);
---------------------------------------------------
---------------------------------------------------
---------------------------------------------------
BULK INSERT #tempDistributors
FROM 'c:\temp\listofDistributors.txt'
WITH
(
DATAFILETYPE = 'char',
FIRSTROW = 2,
ROWTERMINATOR = '',
KEEPNULLS
)
SELECT * FROM #tempDistributors
Msg 1934, Level 16, State 1, Procedure PROC_adddistributors, Line 12
SELECT failed because the following SET options have incorrect settings: 'ANSI_NULLS, QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods.
i was able to run it in production by adding :
SET QUOTED_IDENTIFIER ON
before the create table
but my question is , why does it work in my development database withouth having to add that?
the databases are supposed to be the same and configured the same eventhough they are in different servers. What can i check?
July 8, 2010 at 4:43 pm
You can check the SET options for the current connection with "DBCC UserOptions"
July 8, 2010 at 5:47 pm
thank you for your reply, i get the same in both environments:
textsize2147483647
languageus_english
dateformatmdy
datefirst7
lock_timeout-1
quoted_identifierSET
arithabortSET
ansi_null_dflt_onSET
ansi_warningsSET
ansi_paddingSET
ansi_nullsSET
concat_null_yields_nullSET
isolation levelread committed
July 9, 2010 at 6:04 pm
anything else i could check?
July 9, 2010 at 7:03 pm
hmmm... what you didn't actually point out initially is that the code is part of a Stored Procedure, yes?
Try this:
Do a select from sys.sql_modules and identify your procedure.
on that row record the "uses_quoted_identifier" option value (I'm using Sql 2008 so I'm hoping the column is the same in 2005.)
Do the same as above on your Live Server and see if they differ.
July 19, 2010 at 10:29 am
it is query not a stored procedure. help! i still have this error :crying:
July 19, 2010 at 3:55 pm
You might want to comapre the settings for TEMPDB. ALthough, I'm not sure what would cause that particular issue.
July 20, 2010 at 10:41 am
i checked the DBCC UserOptions for tempdb is the same for both environments.
how do i check the QUOTED_IDENTIFIER settting in the server level?
July 21, 2010 at 4:18 am
"Msg 1934, Level 16, State 1, Procedure PROC_adddistributors, Line 12
SELECT failed because the following SET options have incorrect settings: 'ANSI_NULLS, QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods."
Is the message above the one you are getting? If so, I think that means the message originates from within a Procedure? If that is the case, then you need to ensure that the Procedures were both created with the Quoted_Identifier option set to what you require.
Script the Procedures out from within SSMS in both the Live and Dev. Environments and tell us what the Quoted Identifier option is set to in both scripts.
July 21, 2010 at 7:07 am
this is the message i am getting, sorry at the beginning i was using a proc, same script , this is the error:
Msg 1934, Level 16, State 1, Server WIN2KPROD\DISTRTRAN, Line 57
READTEXT 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 query notifications and/or xml data type methods.
i check both server's properties and both servers have all of the default connections options unchecked
there is an index view that is related to one of the tables used in query but i checked and in both environments is exactly the same.
for this index view i checked select * from sys.sql_modules and in both environments the column uses_quoted_identifier is set to "1"
:crying::crying::crying::crying:
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply