Excel VBA - SQL Call - Операция не разрешена, когда объект закрыт

Question

У меня есть пара макросов для вызовов SSMS 2014, чтобы выполнить запрос и вернуть результаты в определенной ячейке на моем листе. Они работают успешно, но когда я пытаюсь использовать определенные запросы с временными таблицами, я получаю следующее сообщение об ошибке:

Я исследовал онлайн, и лучший ответ, который я могу найти, это добавить SET NOCOUNT ON в начале моего запроса. Я попробовал это, и все еще получил то же самое сообщение.

Кусок кода, который Debug подводит меня к следующему:

bqr.Range("B6").CopyFromRecordset rst

Мясо и картошка моего кода, а также значения переменных, которые имеют значение, таковы:

Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim ConnectionString As String
Dim StrQuery As String
Dim SOURCE As String
Dim DATABASE As String
Dim QUERY As String
Dim intColIndex As Integer
Dim sDate As String
Dim eDate As String
Dim qt As Worksheet
Dim qtr As Worksheet
Dim bqr As Worksheet
Dim bp As Worksheet

ConnectionString = "Provider=SQLOLEDB;Data Source=" & SOURCE & "; Initial Catalog=" & DATABASE & "; Integrated Security=SSPI;"
cnn.Open ConnectionString

cnn.CommandTimeout = 900

StrQuery = QUERY

rst.Open StrQuery, cnn

bqr.Range("B6").CopyFromRecordset rst

For intColIndex = 0 To rst.Fields.Count - 1
    Range("B5").Offset(0, intColIndex).Value = rst.Fields(intColIndex).Name
Next

Самым запутанным является то, что ошибка предполагает, что мой rst набор записей закрыт, хотя он открывается непосредственно перед использованием CopyFromRecordset

Я пытался добавить DROP TABLE в конце моего запроса SET NOCOUNT ON функционировать в начале, и даже протестировал несколько небольших простых временных таблиц в качестве тестов.

Например, я установил свой QUERY переменная к:

QUERY = "CREATE TABLE #Test1 (TestID INT, TestValue VARCHAR(20))"
QUERY = QUERY + " INSERT INTO #Test1"
QUERY = QUERY + " VALUES (1, 'Pass'), (2, 'Fail'), (3, 'Try Again')"
QUERY = QUERY + " SELECT * INTO #Test2 FROM #Test1 WHERE TestID = 1"
QUERY = QUERY + " SELECT * FROM #Test2"

Затем запустил код для извлечения и вставки в Excel, и это сработало.

Поэтому я в тупике. Может быть, есть ограничение на длительность запроса? Прямо сейчас это 180 строк, так что это довольно большой...

Любые предложения приветствуются!

РЕДАКТИРОВАТЬ: полный макрос ниже (за исключением фактического запроса):

Private Sub CommandButton1_Click()

If TextBox1.Value = "i.e. 20160101" Or TextBox2.Value = "i.e. 20160131" Then

MsgBox "Please fill out all fields before proceeding"

ElseIf Len(TextBox1.Value) <> 8 Or Len(TextBox2.Value) <> 8 Or Not IsNumeric(TextBox1.Value) Or Not IsNumeric(TextBox2.Value) Then

MsgBox "Please use correctly formatted Datekeys (i.e. yyyymmdd)"

Else

Application.DisplayAlerts = False

Sheets(ActiveWorkbook.Sheets.Count).Select

While ActiveSheet.Name <> "[worksheet I want to keep]"

ActiveSheet.Delete

Sheets(ActiveWorkbook.Sheets.Count).Select

Wend

Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim ConnectionString As String
Dim StrQuery As String
Dim SOURCE As String
Dim DATABASE As String
Dim QUERY As String
Dim intColIndex As Integer
Dim sDate As String
Dim eDate As String
Dim qtr As Worksheet
Dim bqr As Worksheet
Dim bp As Worksheet

Set qtr = Sheets([sheet name])

Sheets.Add after:=qtr
Set bqr = ActiveSheet
bqr.Name = "[sheet name]"
Sheets.Add after:=bqr
Set bp = ActiveSheet
bp.Name = "[sheet name]"

SOURCE = "[server]"
DATABASE = "[database]"
sDate = UserForm1.TextBox1.Value
eDate = UserForm1.TextBox2.Value

QUERY = "[beginning of query]"
QUERY = QUERY + " [more query here]" 'This gets repeated a lot for each additional line in the query'

qtr.Select
Range("B6").Select

While ActiveCell.Value <> ""

QUERY = QUERY + " " + ActiveCell.Value

ActiveCell.Offset(1, 0).Select

Wend

QUERY = QUERY + " [more query here]" 'This gets repeated a lot for the remaining lines in the query'



    ConnectionString = "Provider=SQLOLEDB;Data Source=" & SOURCE & "; Initial Catalog=" & DATABASE & "; Integrated Security=SSPI;"

    cnn.Open ConnectionString

    cnn.CommandTimeout = 2000


    StrQuery = QUERY


    rst.Open StrQuery, cnn

    bqr.Range("B6").CopyFromRecordset rst
For intColIndex = 0 To rst.Fields.Count - 1
    Range("B5").Offset(0, intColIndex).Value = rst.Fields(intColIndex).Name
Next

End If

Application.DisplayAlerts = True

End Sub

Answer 1

Начните ваш запрос T-SQL с set nocount on;

QUERY = "set nocount on;"
QUERY = QUERY & "declare @Test1 table (TestID INT, TestValue VARCHAR(20))"
QUERY = QUERY & " INSERT INTO @Test1"
QUERY = QUERY & " VALUES (1, 'Pass'), (2, 'Fail'), (3, 'Try Again')"
QUERY = QUERY & " SELECT * FROM @Test1 WHERE TestID = 1"

Тогда это должно работать. Следующий пример также будет работать и немного ближе к вашему примеру (но с использованием табличных переменных).

set nocount on;
declare @Test1 table (TestID INT, TestValue VARCHAR(20))
declare @Test2 table (TestID INT, TestValue VARCHAR(20))

INSERT INTO @Test1
VALUES (1, 'Pass'), (2, 'Fail'), (3, 'Try Again')

insert into @Test2
select *
from @Test1 WHERE TestID = 1

select * from @Test2