Operation not allowed when object is closed - Create table statement in first line?

Question

I am attempting to write the results of a query in specific spaces on a spreadsheet. The SQL creates temporary tables for use during the query and then drops them at the end. Is this the cause of my problem? I have posted my source code below. The error is thrown on line 530. Is there a better way to do this?

410   With wsSheet
420   Set rnStart = Sheets("Discharge Information").Range("Q51")
430   End With

440   strSQL = "create table #encounters ( DischargeKey int,EncounterID varchar(25))          insert into #encounters " & _
           "SELECT top 30 dischargekey,encounternumber from discharges order by dischargedate desc " & _
           "CREATE TABLE #icd9_poa(DischargeKey int,ICD9 nvarchar(max),POA nvarchar(max)) " & _
           "DECLARE @i int, @f int SET @i = 1 SET @f = ( " & _
           "SELECT REPLACE(column_name,'icd9_POA_','') FROM information_schema.Columns WHERE column_name LIKE 'icd9_POA_%' AND table_name = 'temp_discharge' AND ordinal_position IN ( " & _
           "SELECT Max (ordinal_position) FROM information_schema.Columns " & _
           "WHERE column_name LIKE 'icd9_POA_%' AND table_name = 'temp_discharge')) " & _
           "WHILE @i <= @f " & _
           "BEGIN IF @i=1 " & _
           "BEGIN INSERT INTO #icd9_poa " & _
           "SELECT d.DischargeKey,i.icd9code,poa.poa " & _
           "FROM discharges d " & _
           "inner join #encounters e on e.dischargekey = d.dischargekey INNER join icd9diagnosesbridge icb on icb.discharge=d.dischargekey INNER join icd9diagnoses i on icb.icd9 = i.icd9key INNER join presentonadmission poa on icb.presentonadmission = poa.poakey " & _
           "WHERE icb.Icd9Sequence = 1 End " & _
           "IF @I>1 BEGIN " & _
           "Update t SET t.Icd9 = t.Icd9 + ', '+i.Icd9Code,t.poa = t.poa + ', '+ poa.poa " & _
           "FROM  #Icd9_poa t" & _
           "INNER JOIN Discharges d ON (t.DischargeKey=d.DischargeKey) INNER JOIN Icd9DiagnosesBridge icb ON (icb.Discharge=d.DischargeKey) INNER JOIN Icd9Diagnoses i ON (icb.Icd9=i.icd9Key) INNER JOIN PresentOnAdmission poa ON (icb.PresentOnAdmission=poa.PoaKey) " & _
           "WHERE icb.Icd9Sequence=@i End " & _
           "SET @i = @i + 1 End " & _
           "select icd9, poa from #icd9_poa " & _
           "drop table #icd9_poa " & _
           "drop table #encounters "


450   Set cnt = New ADODB.Connection

460

470   With cnt
480   .CursorLocation = adUseClient
490   .Open ConnectionString
500   .CommandTimeout = 0
510   Set rst = .Execute(strSQL)
520   End With
530   rnStart.CopyFromRecordset rst

where do you actually return the recordset? I see what is essentially a stored procedure, but no `RETURNS @Recordset TABLE (...) AS BEGIN ...` — SeanC, Nov 27 '12 at 15:14
Sorry Sean, but I'm not very familiar with VBA. I am selecting the results of the #temp tables towards the bottom of the query. Is there something outside of the query string that I need to add? — Chris Barnes, Nov 28 '12 at 22:39
you can't get at the temporary tables - see http://stackoverflow.com/questions/440308/tsql-returning-a-table-from-a-function-or-store-procedure — SeanC, Nov 28 '12 at 23:06
Is there a way this can be accomplished without creating the stored procedure on all of my databases? — Chris Barnes, Nov 30 '12 at 21:45
yes . remove the temporary table, wrap it in the `RETURNS RECORDSET`, where you will define your table, and as the *very* last instruction, select * from that table. unfortunately, I won't be back at work until monday, so I can't give you any code. look [here](http://stackoverflow.com/questions/2473102/can-you-call-a-sql-stored-procedure-that-returns-a-record-set-and-have-those-val) or [here](http://www.akadia.com/services/ora_return_result_set.html) — SeanC, Dec 01 '12 at 00:14

score 1 · Accepted Answer · answered Dec 03 '12 at 14:38

1

At a very quick look, I would try this method:

RETURNS @icd9_poa TABLE (
    DischargeKey int,
    ICD9 nvarchar(max),
    POA nvarchar(max)) 
AS
    BEGIN

... then insert your SQL (remembering that icd9_poa is already defined)

END

Don't bother with dropping temp tables - SQL Server will remove them as soon as your procedure finishes running

I can't test it fully, as I don't have your data, but this is a method to return a recordset from an SQL procedure.

answered Dec 03 '12 at 14:38

SeanC

15,695
5
45
66

I have this working in SSMS. I am checking for the existence of the function and dropping it, then creating it and running it. The problem I am running into is the Batch Separator. The SQL gets passed to the VBA scripting in one line as part of the strSQL variable but batch separators need to be on their own line. Is there a way around this? `IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ICD9andPOA]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT')) drop function [dbo].[ICD9andPOA] EndBatch create function ICD9andPOA()` – Chris Barnes Dec 03 '12 at 20:30
`ALTER FUNCTION` will allow you to overwrite any existing function. you can use `vbCrLf` to insert lime separators. I haven't tried a multi line function from vba, as I tend to use stored procedures, and parameters if I need to pass additional information – SeanC Dec 03 '12 at 21:12
Right, but the function needs to be created first, correct? I am creating an Excel spreadsheet that can be run on any database in our system ad-hoc. I don't want to create the function on every DB. – Chris Barnes Dec 03 '12 at 21:39
1

using `cnt.execute` will allow you to run almost any SQL command (apart from `GO`), so you can use `cnt.Execute "IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ICD9andPOA]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT')) drop function [dbo].[ICD9andPOA]"` – SeanC Dec 03 '12 at 22:04
This did the trick! I'm using `execute` to do the function existence check/drop and then getting the results from the function using the strSQL variable. Thanks so much for your help Sean! – Chris Barnes Dec 04 '12 at 00:35

score 0 · Answer 2 · answered Nov 27 '12 at 02:30

0

Deleting the temp tables is not necessary and will not be causing your error.

It looks like your connection has closed by the time you call "CopyFromRecordSet", try moving that call into the "With" block.

answered Nov 27 '12 at 02:30

Nat

14,175
5
41
64

Hey Nat, this is the first thing I tried but I am still receiving a "cannot do this when closed" error. – Chris Barnes Nov 27 '12 at 14:55

Operation not allowed when object is closed - Create table statement in first line?

2 Answers2