Databases
StackOverflow2013
Postsdbo
PODo I need to define a table variable in MSQL stored procudre if I want to call a stored procedure from excel VBA
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PODo I need to define a table variable in MSQL stored procudre if I want to call a stored procedure from excel VBA
    text
    copied!<p>I try to call a stored procedure from excel 2007 by VBA. I would like get return from stored procedure into excel sheet. But I get the 'run-time 3074 Operation is not allowed when the object is closed' on Worksheets("Sheet1").Range("A1").CopyFromRecordset objMyRecordset.</p> <p>I do a lot of search without conclusion. I just want to know whether I need to define a table variable first in MSSQL stored procedure, then insert into it.</p> <p>below is the codes I write:</p> <p><em><strong></em>**<em>*</em>***</strong> Stored procedure sp_weekly <strong><em>*</em>***</strong></p> <pre><code>select [field1], [field2] into mytable_2 from mytable_1 select a.*, b.field4 from mytable_2 a inner mytable_3 b on join a.field1 = b.field3 </code></pre> <hr> <p><em><strong></em>**<em>*</em>**<em>*</em>*</strong> in excel Marcos</p> <pre><code>Option Explicit Sub OpenConnection() Dim objMyConn As ADODB.Connection Dim objMyCmd As ADODB.Command Dim objMyRecordset As ADODB.Recordset Dim ConnectionString As String Dim CommandText As String Set objMyConn = CreateObject("ADODB.Connection") Set objMyCmd = New ADODB.Command Set objMyRecordset = New ADODB.Recordset ConnectionString = "Provider=sqloledb;Data Source=abcsql,1433;Initial Catalog=admin;User Id=sa;Password=1234abcd;" objMyConn.Open ConnectionString Set objMyCmd.ActiveConnection = objMyConn CommandText = "exec [admin].[dbo].[sp_weekly]" objMyCmd.CommandType = adCmdText Set objMyRecordset.Source = objMyCmd objMyRecordset.Open Worksheets("Sheet1").Range("A1").CopyFromRecordset objMyRecordset end </code></pre> <hr>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload