SQL Variables not working in Excel Query

Question

Hi I have a query that can not be represented graphically in Excel External SQL Server Query, because the query is not simple I can not set Excel Variables. Instead I try to set variables in the SQL using declare/set however when doing this it fails when trying to import the data.

Is there a way to get around this? I need a level of flexibility for running the query and piping the data into Excel..

Okay so here is an example as requested.

declare @enddate as datetime
set @enddate = (select max(rpt_week) from [results].dbo.t_Data)
Select * from results.dbo.t_Data
where rpt_week = @enddate

This will not run in excel, obviously my query is more complex but the use of variables is essential and I am looking for a way to have this work.

Thanks

I think you need to some code. I don't really understand your issue, but perhaps setting the provider to native SQL - Provider=SQLNCLI. That's allowed me to build more complex queries in Excel VBA. — Doug Glancy
– Doug Glancy, Commented Aug 10, 2012 at 16:05

Byers1979 · Accepted Answer · 2015-03-06 19:53:55Z

7

When using declare values in Excel, you need to SET NOCOUNT ON

your sample would be as follows:

SET NOCOUNT ON;
declare @enddate as datetime
set @enddate = (select max(rpt_week) from [results].dbo.t_Data)
Select * from results.dbo.t_Data
where rpt_week = @enddate

answered Mar 6, 2015 at 19:53

Byers1979

1371 silver badge8 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 12:17:53Z

4

There is another question on this subject which has a correct answer:

Use SET NOCCOUNT ON in your query.

I encountered the same problem as the OP when I tried to run an SP from Excel, which was supposed to return data from an embedded SELECT. It was caused by a ... row(s) affected message being returned from the server. Apparently, Excel can't deal with this message correctly, and the returned data is ignored. Once I used SET NOCOUNT ON in my SP, the data was displayed correctly in Excel.

edited May 23, 2017 at 12:17

CommunityBot

11 silver badge

answered Sep 9, 2014 at 11:11

Treb

20.3k8 gold badges60 silver badges88 bronze badges

Comments

Fionnuala · Accepted Answer · 2012-08-21 22:07:42Z

I am not quite sure what you wish, but here are some notes:

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim cmd As New ADODB.Command

cn.Open ServerCon ''Connection string

''Straight sql
sSQL = "SELECT * FROM table_1 WHERE adate = (SELECT Max(adate) FROM table_1)"
rs.Open sSQL, cn

''Not a good idea, see procedure parameter below for a better way
sSQL = "SELECT * FROM table_1 WHERE adate = '" _
     & Sheets("Sheet7").Range("B2") & "'"
rs.Open sSQL, cn

''Stored procedure
rs.Open "ExcelTest", cn

''Stored procedure with parameter
cmd.ActiveConnection = cn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "ExcelTest"
cmd.Parameters("@enddate") = Sheets("Sheet7").Range("B2")
Set rs = cmd.Execute

''Write to Excel
ActiveWorkbook.Sheets("Sheet12").Cells(1, 1).CopyFromRecordset rs

You can also use Query Tables.

Collectives™ on Stack Overflow

SQL Variables not working in Excel Query

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related