[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2909
  • Last Modified:

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

0
Cedric-BTC
Asked:
Cedric-BTC
  • 3
  • 2
1 Solution
 
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
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

 
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

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now