SET options ?

[Mag Chew]

I'm currently connecting to SQL server 2000 with compatibility set to 6.5 for my database.
I'm making an ADO connection thru my vb program.
It's DSNless.
I'm wondering when u have this kind of connection, which SET options does it follow ?
Database / server / that particular login connection / etc... ?

I'm trying to switch off the ANSI_WARNINGS to OFF for this connection but it seems that no matter what command I use (sp_dboption / dbcc useroptions), I still get the warning like
'String or binary data would be truncated.'

How do I switch off this error message when I'm running through the program. I'm able to do this if I run from query analyzer.

Pls help urgently. Thanx.

p/s : I do not wish to change the existing program to cater for column length checking.
Is there a way to OFF all these options ?

[Cakkie]

Normally, SET affects the connection, so if you set ANSI_WARNINGS to OFF, this should affect everything you do beyond that point, until you set it back ON, or the connection is closed. To do this, you can just call the execute method of the connection.

cnn.execute "set ANSI_WARNINGS OFF"



... should do the trick

I'm not sure, but I don't believe that using SQL2000 with a database set to compatibility mode of 65 affects this behaviour.


Tom Cannaerts
[email protected]

Programming today is a race between software engineers striving to build bigger and better idot-proof programs, and the universe trying to produce bigger and better idiots. So far, the universe is winning -- Rich Cook

[Mag Chew]

Hi,

Thanx. It works.

But, is there a way that I do not need to do this programmatically ? Because I would like this behaviour to be for all connections to this database. It would be very time consuming to change all the existing programs to do this.

Thanx....

[Cakkie]

I don't think so, but I'm not sure. I tried consulting SQL server documentation, but the closest I got was that by default it is turned ON, but it didn't say where to change it other then using SET.

Tom Cannaerts
[email protected]

Programming today is a race between software engineers striving to build bigger and better idot-proof programs, and the universe trying to produce bigger and better idiots. So far, the universe is winning -- Rich Cook