Why is server ANSI_WARNINGS OFF setting ignored?

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
lcoelho
Posts: 47
Joined: Wed 13 Apr 2011 13:41

Why is server ANSI_WARNINGS OFF setting ignored?

Post by lcoelho » Mon 26 Mar 2012 10:46

I have a project that I've migrated from BDE to make use of SDAC data access components. I understand, from one of the posts in this forum, that BDE BDE turns off ANSI_WARNINGS to prevent 'divide by 0' and 'binary/string data will be truncated' warnings from occuring. I'm also aware that to prevent this from occuring when using SDAC, I can include 'SET ANSI_WARNINGS OFF' in the stored procedure or query that causes the problem.

However, at this stage I do not want to go to all the queries and stored procedures to include 'SET ANSI_WARNININGS OFF'. Therefore, we have set the server's (SQL Server 2000) ANSI_WARNINGS option to OFF. Why do I still get the 'divide by 0' and 'binary/string will be truncated' warnings, even with the ANSI_WARNING set to OFF on the server?

Thank you for your kind assistance

AndreyZ

Post by AndreyZ » Mon 26 Mar 2012 16:58

Hello,

The point is that OLEDB and SQL Server Native Client providers automatically set ANSI_WARNINGS to ON when connecting. You can find more information about this here: http://msdn.microsoft.com/en-us/library/ms190368.aspx
To avoid the problem, you should set ANSI_WARNINGS to OFF after connection is established. Here is an example:

Code: Select all

MSConnection.Open;
MSConnection.ExecSQL('SET ANSI_WARNINGS OFF', []);

lcoelho
Posts: 47
Joined: Wed 13 Apr 2011 13:41

Post by lcoelho » Tue 27 Mar 2012 04:01

Okay, now I understand why the setting is ignored even if set on the server. I will implement the code you mentioned when opening the connection.

Thank you for your prompt reply.

lcoelho
Posts: 47
Joined: Wed 13 Apr 2011 13:41

Post by lcoelho » Tue 27 Mar 2012 11:03

My project has a main form that has the TConnection component. The rest of the application forms of the project make use of this one TConnection component. So I added this code into the TConnection's 'AfterConnect' event 'SET ANSI_WARNINGS OFF', []);' but the error still occurs.

Should it be in the 'BeforeConnect' event?

AndreyZ

Post by AndreyZ » Wed 28 Mar 2012 11:22

I cannot reproduce the problem. Please try creating a small sample to demonstrate the problem and send it to andreyz*devart*com , including a script to create all needed server objects.

Post Reply