1

I have this rather awkward problem:

For two weeks now, whenever after I've updated/created stored procedures using my SQL scripts, when these stored procedures are run, they fail with above error.

Other posts dealing with this problem didn't help in my case.

Here's a number of parameters, helping to exclude common solutions which do not apply in my case:

  1. My stored procedure scripts work flawlessly on my laptop (SQL Server 2012, Windows Server 2008 R2).

  2. My stored procedure scripts correctly create stored procedures on any other machine (which is our build machine, with SQL Server 2012 installed; our TEST server, with SQL Server 2005 installed, and our PROD server, with SQL Server 2005 installed). However, the stored procedures won't run on any other machine than mine.

  3. I'm using a database backup of our production SQL Server (SQL Server 2005) on my machine (like any other machine here does).

  4. Even the most basic stored procedure fails (e. g. DELETE myTable WHERE ID = @delID).

  5. On every SQL Server installation I've checked, quoted identifier is set to OFF (!), both on server and on database level. So why do my stored procedures all of a sudden require to have this option set to ON?

  6. I'm using SQLCMD to run my scripts. This gives me an option to dynamically set the server instance's database name in the USE statement.

  7. My scripts only contain a USE statement and right after the ALTER PROCEDURE; or alternatively IF EXISTS (...) DROP PROCEDURE ... GO; CREATE PROCEDURE ...

This all worked for years now, but suddenly, since two weeks ago, stored procedures created with my scripts suddenly fail.

I know that I could manually set QUOTED_IDENTIFIER to ON in my scripts - but I don't want to. There is something wrong here. I want to know what that problem is.

What's happening here?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
AxD
  • 2,714
  • 3
  • 31
  • 53

2 Answers2

6

SQLCMD sets the QUOTED_IDENTIFIER option to OFF by default. You can change it with -I option.

dean
  • 9,960
  • 2
  • 25
  • 26
  • Yes, but that's not the solution I'm looking for. It should work flawlessly without adding the -I option. That worked for years. But all of a sudden, it doesn't. I'd like to know the reason for this behaviour, I don't just want to circumvent it. – AxD Apr 08 '14 at 12:33
  • Sorry, I can't say what suddenly changed on your machine two weeks ago. I gave you the best explanation anyone could without having full access to your machine. – dean Apr 08 '14 at 16:59
  • Thanks, Dean, for trying to help. Sure, I understand. But I assume there must be some SS tracing tool available, enabling me to track down the reason for this behaviour. The error doesn't give the reason causing it to occur. I'd like to investigate on the reason to revert the SS back to its original, functioning state. – AxD Apr 08 '14 at 20:48
0

Could it be that your stored procedure is now doing something on a table that has had an index added? I've had the same issue, and it's due to a new index on a computed column.

squeel
  • 33
  • 4
  • My customer doesn't use computed columns. So, I'm afraid, that's not the case with our problem. – AxD Oct 13 '14 at 20:08
  • Ok. The only other suggestion I have is whether there is a view with an index. I have a situation where we have two applications running over the same database. One can cope with indexes on views and/or computed columns, and the other (that apparently uses Quoted Identifier = off) doesn't. – squeel Oct 14 '14 at 20:28