CODEWITHSUNDEEP
sqlexcel
CAR
CAR

Reputation: 155

SQL Variables not working in Excel Query

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

Upvotes: 3

Views: 10137

Answers (3)

Byers1979
Byers1979

Reputation: 137

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

Upvotes: 7

Treb
Treb

Reputation: 20271

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.

Upvotes: 4

Fionnuala
Fionnuala

Reputation: 91356

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.

Upvotes: 0

Related Questions