Quoted_Identifier error (1934) with computed column 1

[AndyInNC]

I've got a stored procedure (SQL 2008) that is supposed to update all the tables in the database (unless explicitly named) when the table meets the criteria. I won't go into why; trust me, it's a necessary tool.

Anyway, it has worked fine until we added a computed column to one of the tables. Apparently there is a problem with a computed column and Quoted_Identifier! ([URL unfurl="true"]http://msdn.microsoft.com/en-us/library/ms174393.aspx[/url] 4th paragraph in Remarks)

Here is the syntax for the computed column (it's nothing fancy):

(CASE Len([sFirst]) WHEN (3) THEN (65) ELSE (8) END)

This is my code:

[COLOR=blue]SET[/color] @sSQL = '[COLOR=blue]UPDATE[/color] [' + @sTableName + ']' +
' [COLOR=blue]SET[/color] [iRequestID] = ' + @sNewRequestID +
' [COLOR=blue]WHERE[/color] [iAssignmentID] = ' + Convert(varchar(10), @iAssignmentID)

[COLOR=blue]EXEC[/color](@sSQL)

[COLOR=green]-- Looks like this, but bombs (Error 1934) when a computed column is in the table[/color]
[COLOR=blue]UPDATE[/color] [tblAppointments]
[COLOR=blue]SET[/color] [iRequestID] = 27122
[COLOR=blue]WHERE[/color] [iAssignmentID] = 2349

Running an Execute with a SQL statement creates a new process which is outside the realm of the current setting for Quoted_Identifier. When I run the statement alone, it still doesn't work, but if I add "SET QUOTED_IDENTIFIER OFF" before, it does.

Any ideas on how I can get around this? I can't add SET QUOTED_IDENTIFIER OFF to the dynamic SQL because it requires a GO beforehand, right?

 

[markros]

Can you try nested execute?

e.g.

set @SQL = 'declare @set nvarchar(100)
set @set = ''SET QUOTED_IDENTIFIER OFF''
execute (@set)
update ....'

execute(@SQL) ?

PluralSight Learning Library

 

[AndyInNC]

Thanks Markos. That did the job. I didn't think it would work because each Execute opens up a new PID, but it did. I actually skipped the whole DECLARE and just added

[COLOR=blue]SET[/color] @sSQL = 'EXEC(''SET QUOTED_IDENTIFIER OFF'')'
[COLOR=blue]SET[/color] @sSQL = @sSQL + ' UPDATE [' + @sTableName +
.
.
.
[COLOR=blue]EXEC[/color] (@sSQL)

and it worked perfectly!

You're a genius!

 

[markros]

For this "genius" idea didn't I deserve a purplee?

PluralSight Learning Library

 

[markros]

Oops, I got it - didn't see.

PluralSight Learning Library