Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

System DSN and SET OPTIONS Issue

Status
Not open for further replies.

Borvik

Programmer
Jan 2, 2002
1,392
US
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.
 
Not from the main software - 3rd party.
 
I restarted the MSSQL Service, it is working now.
 
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
Code:
--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]
 
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:
Code:
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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top