ANSI Warnings and Vista

I have an Access 2003 application that connects to a SQL Server 2005 database using pass-through queries that either contain T-SQL or execute stored procedures.

When some of the user PC's were upgraded to Windows Vista, I noted that sometimes new tables were being created in Access with names like "Admin - 00", "Admin - 01" and so forth. Each table contained one to three records that held ANSI warnings messages being returned from SQL Server. This had never occurred before under SQL 2000 or Windows XP.

For example, a message might be the familiar "Null value is eliminated by an aggregate or other SET operation" preceded by an error number.

The pass-through queries were all set for Log Messages = No, and I experimented with changing the ANSI settings on the ODBC connection for the SQL Native Client, but nothing made these message tables stop.

When executing certain T-SQL statements using either Query Analyzer (2000) or SSMS (2005) it is quite common for the developer to see these ANSI warnings displayed in the messages window, but they are not normally returned to the application. However, the combination of Access 2003, SQL Server 2005 and Windows Vista behaves differently from combinations of the prior products.

The only way I found to prevent these message tables was to step through the T-SQL in SSMS and locate & fix the offending code. For example, there might have been a statement

SUM(Payment) AS TotalPaid

which had to be revised as

SUM(ISNULL(Payment,0)) AS Total Paid

Another example that I found was a statement like

COUNT(DISTINCT InvoiceID)

in which case I had to add a statement

WHERE InvoiceID IS NOT NULL

to correct the problem. Fortunately, only certain users were triggering the creation of these message tables, so I was able to narrow the scope of my troubleshooting to the statements that those users were executing.

In the future, all new code will need to be checked so that ANSI warnings are not being generated, for compatibility with Windows Vista.