System DSN and SET OPTIONS Issue

[Borvik]

I was trying out SqlDependency in a .NET application I am writing, when all my users were now getting the following error in the main software.

[Microsoft][SQL Native Client][SQL Server]INSERT failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER, CONCAT_NULL_YIELDS_NULL, ANSI_WARNINGS'. Verify that SET options are correct for use with indexed views and/or indexes.

Our software requires that the "Use ANSI quoted identifiers" and "Use ANSI nulls, paddings, warnings" be unchecked in the System DSN. Though to try things out I checked those options, and the error was almost gone - it was left with just the ANSI_WARNINGS in the error message.

Changing the DSN on every machine is not the best scenario, I would rather do something on the server to fix this.

Any ideas?

Thanks.

 

[markros]

Can you programmatically change these options from your code?

PluralSight Learning Library

 

[Borvik]

Not from the main software - 3rd party.

 

[Borvik]

I restarted the MSSQL Service, it is working now.

 

[Qik3Coder]

Is it across the board or just with one particular script?
Are you creating/altering an indexed view in the code?

You may want to consider altering the database default for ANSI_NULLS, but I'd be careful about that:
Code from

http://beyondrelational.com/blogs/j...rver-set-options-2-set-ansi-nulls-on-off.aspx

--To change the setting of ANSI_NULLS of a given database, you 
--can run one of the following:

-- Using ALTER DATABASE
ALTER DATABASE dbname 
SET ANSI_NULLS ON;GO

-- Using sp_dboption
EXEC sp_dboption 'dbname', 'ANSI_NULLS', 'ON';GO

Lodlaiden

If [blue]you have problems[/blue], I want [green]source code[/green] AND [green]error messages[/green], none of this [red]"there was an error crap"[/red]

 

[Borvik]

It was across the board - though I don't have access to what all the 3rd party program was doing.

I had tried changing Database defaults using the Management Studio - which didn't really have any effect.

In my other program - the one I assume triggered in fault, I was attempting this:

Dim dependency As New SqlClient.SqlDependency(cmd)
AddHandler dependency.OnChange, AddressOf OnOpenCountChange
dt.Load(cmd.ExecuteReader(CommandBehavior.CloseConnection))

This must have triggered some global option change.

After the restart of the services, everyone is up and running again - though it looks like I can't use SqlDependency.