Stored Procedure causes "UPDATE failed because the following SET options have incorrect settings: 'CONCAT_NULL_YIELDS_NULL, ANSI_WARNINGS, ANSI_PADDING'."

[James]

I'm trying to use tedious to execute a stored procedure on MS SQL
Server 2008 R2. I'm getting the following message, however.

UPDATE failed because the following SET options have incorrect
settings: 'CONCAT_NULL_YIELDS_NULL, ANSI_WARNINGS, ANSI_PADDING'.
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.

I don't get this message if I connect from a .NET script, or if I run
the stored procedure from SQL Management Studio.

Any thoughts or suggestions?

[Mike Pilsbury]

The only SET statement that tedious executes on a newly established connection is SET TEXTSIZE. It doesn't execute a SET statement for any of CONCAT_NULL_YIELDS_NULL, ANSI_WARNINGS or ANSI_PADDING. I can only assume that other clients, such as SQL Management Studio, do set values for some of these.

You could try executing SET statements to turn some of them ON or OFF before executing the problematic stored procedure. The "Considerations ..." section in http://msdn.microsoft.com/en-us/library/ms190356.aspx warns "if a SET statement is specified in a dynamic SQL string that is run by using either sp_executesql or EXECUTE, the value of the SET option is restored after control is returned from the batch specified in the dynamic SQL string.". As Connection.execSql uses sp_executesql, you should use Connection.execSqlBatch to execute the SET statements.

Alternatively you could try adding the necessary SET statements to the start of your stored procedure. In http://msdn.microsoft.com/en-us/library/ms190356.aspx it states "If a SET statement is run in a stored procedure or trigger, the value of the SET option is restored after control is returned from the stored procedure or trigger."

Reading the documentation for all three SET statement, I see that they all contain a sentence along the lines of "SET ANSI_WARNINGS must be ON when you are creating or manipulating indexes on computed columns or indexed views. If SET ANSI_WARNINGS is OFF, CREATE, UPDATE, INSERT, and DELETE statements on tables with indexes on computed columns or indexed views will fail.". So I would think that your best bet would be to start off by setting all three values to ON, and seeing if that works.

[James]

Using execSqlBatch worked! I had tried adding the SET statements to
the stored procedure and using execSql to issue the SET statements, to
no avail. However, now I've got a different issue...

The stored procedure either updates a row or inserts a new one in the
database. Both actually work, so I assume the issue is with whatever
data is getting returned to tedious after the stored procedure
completes. If it's updating, everything works fine. If it's inserting
a new row, I get the following...

The main difference between the two cases, other than INSERT vs
UPDATE, is that another stored procedure is executed in the insert
case (in order to get an ID).
----------------
/home/jcornwell-shiel/node/node_modules/tedious/lib/token/token-stream-
parser.js:103
throw error;
^
Error: Unsupported ENVCHANGE type 8 BEGIN_TXN at offset 3
at Object.227 (/home/jcornwell-shiel/node/node_modules/tedious/lib/
token/env-change-token-parser.js:67:15)
at Parser.nextToken (/home/jcornwell-shiel/node/node_modules/
tedious/lib/token/token-stream-parser.js:81:35)
at Parser.addBuffer (/home/jcornwell-shiel/node/node_modules/
tedious/lib/token/token-stream-parser.js:65:17)
at Connection.sendPacketToTokenStreamParser (/home/jcornwell-shiel/
node/node_modules/tedious/lib/connection.js:439:35)
at Connection.<anonymous> (/home/jcornwell-shiel/node/node_modules/
tedious/lib/connection.js:146:23)
at Connection.dispatchEvent (/home/jcornwell-shiel/node/
node_modules/tedious/lib/connection.js:375:59)
at MessageIO.<anonymous> (/home/jcornwell-shiel/node/node_modules/
tedious/lib/connection.js:329:20)
at MessageIO.emit (events.js:67:17)
at MessageIO.<anonymous> (/home/jcornwell-shiel/node/node_modules/
tedious/lib/message-io.js:47:12)
at Socket.<anonymous> (/home/jcornwell-shiel/node/node_modules/
tedious/lib/message-io.js:3:59)
----------------
If I change token-stream-parser.js:97 and env-change-token-parser.js:
67 from throw new Error(...) to console.log(...), execution continues
unhindered.

On Jun 6, 3:41 am, Mike Pilsbury <mike.pilsb...@gmail.com> wrote:
> The only SET statement that tedious executes on a newly established
> connection is SET TEXTSIZE. It doesn't execute a SET statement for any
> of CONCAT_NULL_YIELDS_NULL, ANSI_WARNINGS or ANSI_PADDING. I can only
> assume that other clients, such as SQL Management Studio, do set values for
> some of these.
>
> You could try executing SET statements to turn some of them ON or OFF
> before executing the problematic stored procedure. The "Considerations ..."

> section inhttp://msdn.microsoft.com/en-us/library/ms190356.aspxwarns "if

> a SET statement is specified in a dynamic SQL string that is run by using
> either sp_executesql or EXECUTE, the value of the SET option is restored
> after control is returned from the batch specified in the dynamic SQL
> string.". As Connection.execSql uses sp_executesql, you should use
> Connection.execSqlBatch to execute the SET statements.
>
> Alternatively you could try adding the necessary SET statements to the

> start of your stored procedure. Inhttp://msdn.microsoft.com/en-us/library/ms190356.aspxit states "If a SET

[Mike Pilsbury]

Commit 5fb8c9abe0 on master should address that.

[Rob Saylo]

Hello,

This error is happening for me when I try to update or insert any tables that have computed persisted columns (normal computed columns work just fine).  I tried using execSqlBatch() and setting the correct SET statements, I also checked @@OPTIONS within the batch to confirm they were being set.

For example I tested a simple table here:

create table dbo.TestComputedTable ( id int, computedColumn as id * id PERSISTED )

insert into dbo.TestComputedTable (id) values (2)

The insert statement works fine in SSMS however fails with the above error.  Note that if I remove the persisted keyword it works fine.

Any help would be appreciated.  Thanks.

[Mike Pilsbury]

From a bit or reading (http://stackoverflow.com/questions/9235527/incorrect-set-options-error-when-building-database-project http://msdn.microsoft.com/en-us/library/ms175088.aspx http://sqlcat.com/sqlcat/b/msdnmirror/archive/2011/09/01/quoted-identifier-causes-unexpected-query-plan-for-persisted-computed-column-query.aspx), it seems that the key are the the OPTIONS that are set. There's one appears to mentioned a lot for PERSISTED.

SET QUOTED_IDENTIFIER ON

As mentioned in earlier comments, "...you should use Connection.execSqlBatch to execute the SET statements".

The reason that it may work in SSMS and not tedious is probably down to whether any statements are executed by the client when a connection is first established. If tedious set various OPTIONS to ON or OFF for every connection, I think that there's a chance that it would break some existing uses of tedious. So I'd be a bit nervous about doing so.

Having said that, I'm open to setting some options when the connection is made, if a good case can be made. If you think that it's worthwhile, please open an issue on github, so that the various options can be discussed.

--
You received this message because you are subscribed to the Google Groups "tedious" group.
To unsubscribe from this group and stop receiving emails from it, send an email to node-tedious...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Mark Volkmann]

I am hitting this same issue, but in my case there is a login trigger that prevents making a connection because I don't have these options set.

I can't use Connection.execSqlBatch to set the options because I can't get the initial connection due to the login failure.

Is there a way to specify these options in the config object?