How to ignore warning 'null value is eliminated by an aggregate or other SET operation' when retrieving a recordset from an sql query through ADODB?

Hi everyone,

I extract data from SQL-server DB to Excel using a macro-command calling ADODB objects.
Works well except for a query that generates no warnings in Query Analyzer but through ADODB it generates the message  'null value is eliminated by an aggregate or other SET operation'.

I learned that by default ADODB connection turns ansi_warnings to ON.
1) Is it possible to turn it to OFF (or which driver would do that?)?
2) if impossible, how can the VBA code ignore the message and retrieve the data behind?

I tried to put the command 'set ansi_warnings off' in front of the query but it then yielded error message 'Cursor type changed'.

What is bewildering me is that the query stored in cell "Termqry" should produce no null values by design:
select case when sum(isnull(VBSPOts_total,0))=0 then null else sum(isnull(Ots_NoTerm,0))/sum(isnull(Ots_total,0)) end as [%Ots_NoTerm]
from Ots_by_Term o join AdminAreas aa on aa.areacode=o.areacode where aa.codeup=[Province_code_here]

Thank you for your help!
Dim ProvArea As Variant
ProvArea = Left(Range("ProvArea"), 2)
 
Dim Cn As ADODB.Connection
Dim Server_Name As String
Dim Database_Name As String
Dim User_ID As String
Dim Password As String
Dim sSql As String
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
 
Server_Name = "CEDRIC" ' Enter your server name here
Database_Name = "VWU_MF" ' Enter your  database name here
User_ID = "FPT" ' enter your user ID here
Password = "" ' Enter your password here
 
Set Cn = New ADODB.Connection
Cn.ConnectionTimeout = 180
Cn.Open "Driver={SQL Server};Server=" & Server_Name & ";Database=" & Database_Name & _
";Uid=" & User_ID & ";Pwd=" & Password & ";"
 
sSql = Replace(Range("Termqry"), "[Province_code_here]", "'" & ProvArea & "'")
rs.Open sSql, Cn, adOpenStatic
 
 ' Dump to spreadsheet
With Range("Term")  ' Enter your sheet name and range here
    .ClearContents
    .CopyFromRecordset rs
End With     '            Tidy up
rs.Close

Open in new window

Cedric-BTCAsked:
Who is Participating?
 
Chris LuttrellSenior Database ArchitectCommented:
A previous question was along the same lines, maybe one of their suggestions will help you.
http://www.experts-exchange.com/Programming/Languages/Pascal/Delphi/Q_23253181.html
0
 
Cedric-BTCAuthor Commented:
I had read it but you had have a more in-depth look (especially at MS documentation on ansi warnings [http://technet.microsoft.com/en-us/library/ms190368.aspx]) and I could find a workaround: basically setting up an ODBC connection which can be configured with ansi warnings= off. Thank you for that.

Still, it's impractical because it means that I have to create this ODBC connection on any computer using my Excel file. [and simply for 1 query, for the 3 others it is not needed]

Don't you know a way without setting up an ODBC connection?
0
 
Rory ArchibaldCommented:
Have you tried using:
cn.execute "SET ANSI_WARNINGS OFF"
after opening the connection?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Cedric-BTCAuthor Commented:
I had read it but you had have me take a more in-depth look (especially at MS documentation on ansi warnings [http://technet.microsoft.com/en-us/library/ms190368.aspx]) and I could find a workaround: basically setting up an ODBC connection which can be configured with ansi warnings= off. Thank you for that.

Still, it's impractical because it means that I have to create this ODBC connection on any computer using my Excel file. [and simply for 1 query, for the 3 others it is not needed]

Don't you know a way without setting up an ODBC connection?
0
 
Cedric-BTCAuthor Commented:
Inserting this command just before
sSql = Replace(Range("Termqry"), "[Province_code_here]", "'" & ProvArea & "'")
rs.Open sSql, Cn, adOpenStatic

works perfectly well and avoids using an ODBC connection.

Thanks a lot. Issue closed on my side!
(I think you'd deserve half the points!)
0
 
Rory ArchibaldCommented:
If you were referring to my post, then you accepted the wrong answer! :)
If not, which command did you mean?
Regards,
Rory
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.