3

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

CAR
  • 155
  • 1
  • 3
  • 9
  • 3
    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 Aug 10 '12 at 16:05
  • "Show some code" is what the last comment should say. – Doug Glancy Aug 10 '12 at 16:32

3 Answers3

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
Byers1979
  • 137
  • 1
  • 8
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.

Community
  • 1
  • 1
Treb
  • 19,903
  • 7
  • 54
  • 87
0

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.

Fionnuala
  • 90,370
  • 7
  • 114
  • 152