Set ANSI_WARNINGS OFF not working

[millerk]

I'm running the following code

SET ANSI_WARNINGS OFF
set @sqlAlter = 'alter table #tblQCResults add [' + @TestName + '] nvarchar(30)'
exec (@sqlAlter)

Even after setting warnings off, it still generates this message:

Warning: The table '#tblQCResults' has been created but its maximum row size (8179) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.

I understand the message, and it is not a problem. The problem is that I am calling the stored procedure from VB and putting the results in an ADODB recordset. So the warning messages are messing up my recordset.

Anyone have any idea why SET ANSI_WARNINGS OFF is not eliminating these messages?
I've tried
set @sqlAlter = 'SET ANSI_WARNINGS OFF alter table #tblQCResults add [' + @TestName + '] nvarchar(30)'
and it still doesn't stop the warning messages.

Any ideas would be greatly appreciated.
Thanks

 

[tlbroadbent]

The message is not an ANSI warning message so setting ANSI_WARNINGS OFF does no good, as you see. You need to modify the VB to handle the error message. You should be able to use the ADO errors collection. If you want to get the best answer for your question read faq183-874 and thread183-468158.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[millerk]

Thanks. I was afraid that might be the case.
I guess it's a VB/ADO question now, but how do I deal with this.
I've tried
set rs = rs.NextRecordset and that doesn't work.
I still get a "Cannot perform requested operation when object is closed" error when checking rs.eof.
If I am getting multiple warning messages, is each one considered a recordset by ADO?

In other words, do I have to keep calling rs.NextRecordset until I get to the actual data?

 

[tlbroadbent]

I'm not a VB/ADO expert. I do know that ADO connections have an Errors collection. You may want to post your Question in a VB forum.

Check the following links.

http://msdn.microsoft.com/library/en-us/modcore/html/deconTheADOErrorObjectErrorsCollection.asp

http://msdn.microsoft.com/library/en-us/adosql/adoprg03_59pr.asp

http://msdn.microsoft.com/library/en-us/olapdmpr/pt_clien_0cfb.asp

http://www.devguru.com/Technologies/ado/quickref/connection_errorscollection.html

If you want to get the best answer for your question read faq183-874 and thread183-468158.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm