Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Run a Stored Procedure from Excel
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
Jez Jez is offline
external usenet poster
  
Posts: 38
Default Run a Stored Procedure from Excel
I have this code below to Execute a Command in Stored Procedure in SQL
Server. My problem is that I keep having an error message saying

Error: Operation is not allowed when the object is closed. 3704
I have no idea what that means,

Option Explicit

Dim cnnDW As ADODB.Connection
Dim rsDW As ADODB.Recordset
'Dim cnnSP As ADODB.Command
Dim sQRY As String
Dim strDWFilePath As String

Sub GetData()

On Error GoTo Err:

strDWFilePath = "Driver={SQL Native
Client};Server=CISSQL1;Database=CORPINFO;Trusted_C onnection=Yes"
Set cnnDW = New ADODB.Connection

Application.ScreenUpdating = False
cnnDW.Open strDWFilePath
Sheet1.Range("E4:F9").ClearContents
Set rsDW = New ADODB.Recordset
sQRY = "DECLARE @wlvals varchar(100) " & _
"SET @wlvals = char(39) + 'T2DEA' + char(39) + ',' +
char(39) + 'T2DEP' + char(39) " & _
"EXEC sp_WLName_Report 'September', '2008', '18-09-2008',
@wlvals"
rsDW.CursorLocation = adUseClient
rsDW.Open sQRY, cnnDW, adOpenDynamic, adLockOptimistic, adCmdText
Application.ScreenUpdating = False
Sheet1.Range("E4").CopyFromRecordset rsDW
rsDW.Close
Set rsDW = Nothing
MsgBox "Import Complete", vbInformation, "SQL Connection"
cnnDW.Close
Set cnnDW = Nothing
Exit Sub
Err:
MsgBox "The following error has occured-" & vbCrLf & vbCrLf & VBA.Error,
vbCritical, "SQL Connection"
MsgBox VBA.Err
End Sub


Where am I going wrong?
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default Run a Stored Procedure from Excel
It meansd the OBJECT was closed for some other reason. Either the server
closed the connection because there was some security problem (an illegal
command could of done this) or when you closed rsDW. I would move the close
statement just after the open to check that it works. then I would keep
moving the line down in the code until you get the error message again. This
way you can find out where the problem is.

"Jez" wrote:

I have this code below to Execute a Command in Stored Procedure in SQL
Server. My problem is that I keep having an error message saying

Error: Operation is not allowed when the object is closed. 3704
I have no idea what that means,

Option Explicit

Dim cnnDW As ADODB.Connection
Dim rsDW As ADODB.Recordset
'Dim cnnSP As ADODB.Command
Dim sQRY As String
Dim strDWFilePath As String

Sub GetData()

On Error GoTo Err:

strDWFilePath = "Driver={SQL Native
Client};Server=CISSQL1;Database=CORPINFO;Trusted_C onnection=Yes"
Set cnnDW = New ADODB.Connection

Application.ScreenUpdating = False
cnnDW.Open strDWFilePath
Sheet1.Range("E4:F9").ClearContents
Set rsDW = New ADODB.Recordset
sQRY = "DECLARE @wlvals varchar(100) " & _
"SET @wlvals = char(39) + 'T2DEA' + char(39) + ',' +
char(39) + 'T2DEP' + char(39) " & _
"EXEC sp_WLName_Report 'September', '2008', '18-09-2008',
@wlvals"
rsDW.CursorLocation = adUseClient
rsDW.Open sQRY, cnnDW, adOpenDynamic, adLockOptimistic, adCmdText
Application.ScreenUpdating = False
Sheet1.Range("E4").CopyFromRecordset rsDW
rsDW.Close
Set rsDW = Nothing
MsgBox "Import Complete", vbInformation, "SQL Connection"
cnnDW.Close
Set cnnDW = Nothing
Exit Sub
Err:
MsgBox "The following error has occured-" & vbCrLf & vbCrLf & VBA.Error,
vbCritical, "SQL Connection"
MsgBox VBA.Err
End Sub


Where am I going wrong?
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
Jez Jez is offline
external usenet poster
  
Posts: 38
Default Run a Stored Procedure from Excel
The problem area is around this section here

sQRY = "DECLARE @wlvals varchar(100) " & _
"SET @wlvals = char(39) + 'T2DEA' + char(39) + ',' +
char(39) + 'T2DEP' + char(39) " & _
"EXEC sp_WLName_Report 'September', '2008', '18-09-2008',
@wlvals"

When I run this in SQL Server it works perfect.
DECLARE @wlvals varchar(100)
SET @wlvals = char(39) + 'T2DEA' + char(39) + ',' + char(39) + 'T2DEP' +
char(39)
EXEC sp_WLName_Report 'September', '2008', '18-09-2008', @wlvals

Do I need to do something different with my code in Excel to run this
Execute Statement?


"Joel" wrote:

It meansd the OBJECT was closed for some other reason. Either the server
closed the connection because there was some security problem (an illegal
command could of done this) or when you closed rsDW. I would move the close
statement just after the open to check that it works. then I would keep
moving the line down in the code until you get the error message again. This
way you can find out where the problem is.

"Jez" wrote:

I have this code below to Execute a Command in Stored Procedure in SQL
Server. My problem is that I keep having an error message saying

Error: Operation is not allowed when the object is closed. 3704
I have no idea what that means,

Option Explicit

Dim cnnDW As ADODB.Connection
Dim rsDW As ADODB.Recordset
'Dim cnnSP As ADODB.Command
Dim sQRY As String
Dim strDWFilePath As String

Sub GetData()

On Error GoTo Err:

strDWFilePath = "Driver={SQL Native
Client};Server=CISSQL1;Database=CORPINFO;Trusted_C onnection=Yes"
Set cnnDW = New ADODB.Connection

Application.ScreenUpdating = False
cnnDW.Open strDWFilePath
Sheet1.Range("E4:F9").ClearContents
Set rsDW = New ADODB.Recordset
sQRY = "DECLARE @wlvals varchar(100) " & _
"SET @wlvals = char(39) + 'T2DEA' + char(39) + ',' +
char(39) + 'T2DEP' + char(39) " & _
"EXEC sp_WLName_Report 'September', '2008', '18-09-2008',
@wlvals"
rsDW.CursorLocation = adUseClient
rsDW.Open sQRY, cnnDW, adOpenDynamic, adLockOptimistic, adCmdText
Application.ScreenUpdating = False
Sheet1.Range("E4").CopyFromRecordset rsDW
rsDW.Close
Set rsDW = Nothing
MsgBox "Import Complete", vbInformation, "SQL Connection"
cnnDW.Close
Set cnnDW = Nothing
Exit Sub
Err:
MsgBox "The following error has occured-" & vbCrLf & vbCrLf & VBA.Error,
vbCritical, "SQL Connection"
MsgBox VBA.Err
End Sub


Where am I going wrong?
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default Run a Stored Procedure from Excel
I think you are missing the carriage returns. The SQL server expects each
command to start on a new line or seperated by a terminator like a semicolon.
Your combined string don't provide a method for the server to know when one
command ends and the next commands starts.

I don't know SQL very well by try one of three solutions.

chr(13) is a cariage return and chr(39) is a single quote.

semicolons added

sQRY = "DECLARE @wlvals varchar(100); " & _
"SET @wlvals = char(39) + 'T2DEA' + char(39) + ',' +
char(39) + 'T2DEP' + char(39); " & _
"EXEC sp_WLName_Report 'September', '2008', '18-09-2008',
@wlvals"

or
Carriage returns added

sQRY = "DECLARE @wlvals varchar(100)" & chr(13) & _
"SET @wlvals = char(39) + 'T2DEA' + char(39) + ',' +
char(39) + 'T2DEP' + char(39)" & chr(13) & _
"EXEC sp_WLName_Report 'September', '2008', '18-09-2008',
@wlvals"

"Jez" wrote:

The problem area is around this section here

sQRY = "DECLARE @wlvals varchar(100) " & _
"SET @wlvals = char(39) + 'T2DEA' + char(39) + ',' +
char(39) + 'T2DEP' + char(39) " & _
"EXEC sp_WLName_Report 'September', '2008', '18-09-2008',
@wlvals"

When I run this in SQL Server it works perfect.
DECLARE @wlvals varchar(100)
SET @wlvals = char(39) + 'T2DEA' + char(39) + ',' + char(39) + 'T2DEP' +
char(39)
EXEC sp_WLName_Report 'September', '2008', '18-09-2008', @wlvals

Do I need to do something different with my code in Excel to run this
Execute Statement?


"Joel" wrote:

It meansd the OBJECT was closed for some other reason. Either the server
closed the connection because there was some security problem (an illegal
command could of done this) or when you closed rsDW. I would move the close
statement just after the open to check that it works. then I would keep
moving the line down in the code until you get the error message again. This
way you can find out where the problem is.

"Jez" wrote:

I have this code below to Execute a Command in Stored Procedure in SQL
Server. My problem is that I keep having an error message saying

Error: Operation is not allowed when the object is closed. 3704
I have no idea what that means,

Option Explicit

Dim cnnDW As ADODB.Connection
Dim rsDW As ADODB.Recordset
'Dim cnnSP As ADODB.Command
Dim sQRY As String
Dim strDWFilePath As String

Sub GetData()

On Error GoTo Err:

strDWFilePath = "Driver={SQL Native
Client};Server=CISSQL1;Database=CORPINFO;Trusted_C onnection=Yes"
Set cnnDW = New ADODB.Connection

Application.ScreenUpdating = False
cnnDW.Open strDWFilePath
Sheet1.Range("E4:F9").ClearContents
Set rsDW = New ADODB.Recordset
sQRY = "DECLARE @wlvals varchar(100) " & _
"SET @wlvals = char(39) + 'T2DEA' + char(39) + ',' +
char(39) + 'T2DEP' + char(39) " & _
"EXEC sp_WLName_Report 'September', '2008', '18-09-2008',
@wlvals"
rsDW.CursorLocation = adUseClient
rsDW.Open sQRY, cnnDW, adOpenDynamic, adLockOptimistic, adCmdText
Application.ScreenUpdating = False
Sheet1.Range("E4").CopyFromRecordset rsDW
rsDW.Close
Set rsDW = Nothing
MsgBox "Import Complete", vbInformation, "SQL Connection"
cnnDW.Close
Set cnnDW = Nothing
Exit Sub
Err:
MsgBox "The following error has occured-" & vbCrLf & vbCrLf & VBA.Error,
vbCritical, "SQL Connection"
MsgBox VBA.Err
End Sub


Where am I going wrong?
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
Jez Jez is offline
external usenet poster
  
Posts: 38
Default Run a Stored Procedure from Excel
I have tried all 3 solutions and they still dont connect the results.

I changed all the Char(39) to Char(13) and found that I got an error message
telling me to look at the part of my statement which was like this

+ ',' +

As this didnt seem to be liked.

The other 2 solutions brought back the same error message,

Operation is not allowed when the object is closed. 3704

I am totally baffled, by this...

"Joel" wrote:

I think you are missing the carriage returns. The SQL server expects each
command to start on a new line or seperated by a terminator like a semicolon.
Your combined string don't provide a method for the server to know when one
command ends and the next commands starts.

I don't know SQL very well by try one of three solutions.

chr(13) is a cariage return and chr(39) is a single quote.

semicolons added

sQRY = "DECLARE @wlvals varchar(100); " & _
"SET @wlvals = char(39) + 'T2DEA' + char(39) + ',' +
char(39) + 'T2DEP' + char(39); " & _
"EXEC sp_WLName_Report 'September', '2008', '18-09-2008',
@wlvals"

or
Carriage returns added

sQRY = "DECLARE @wlvals varchar(100)" & chr(13) & _
"SET @wlvals = char(39) + 'T2DEA' + char(39) + ',' +
char(39) + 'T2DEP' + char(39)" & chr(13) & _
"EXEC sp_WLName_Report 'September', '2008', '18-09-2008',
@wlvals"

"Jez" wrote:

The problem area is around this section here

sQRY = "DECLARE @wlvals varchar(100) " & _
"SET @wlvals = char(39) + 'T2DEA' + char(39) + ',' +
char(39) + 'T2DEP' + char(39) " & _
"EXEC sp_WLName_Report 'September', '2008', '18-09-2008',
@wlvals"

When I run this in SQL Server it works perfect.
DECLARE @wlvals varchar(100)
SET @wlvals = char(39) + 'T2DEA' + char(39) + ',' + char(39) + 'T2DEP' +
char(39)
EXEC sp_WLName_Report 'September', '2008', '18-09-2008', @wlvals

Do I need to do something different with my code in Excel to run this
Execute Statement?


"Joel" wrote:

It meansd the OBJECT was closed for some other reason. Either the server
closed the connection because there was some security problem (an illegal
command could of done this) or when you closed rsDW. I would move the close
statement just after the open to check that it works. then I would keep
moving the line down in the code until you get the error message again. This
way you can find out where the problem is.

"Jez" wrote:

I have this code below to Execute a Command in Stored Procedure in SQL
Server. My problem is that I keep having an error message saying

Error: Operation is not allowed when the object is closed. 3704
I have no idea what that means,

Option Explicit

Dim cnnDW As ADODB.Connection
Dim rsDW As ADODB.Recordset
'Dim cnnSP As ADODB.Command
Dim sQRY As String
Dim strDWFilePath As String

Sub GetData()

On Error GoTo Err:

strDWFilePath = "Driver={SQL Native
Client};Server=CISSQL1;Database=CORPINFO;Trusted_C onnection=Yes"
Set cnnDW = New ADODB.Connection

Application.ScreenUpdating = False
cnnDW.Open strDWFilePath
Sheet1.Range("E4:F9").ClearContents
Set rsDW = New ADODB.Recordset
sQRY = "DECLARE @wlvals varchar(100) " & _
"SET @wlvals = char(39) + 'T2DEA' + char(39) + ',' +
char(39) + 'T2DEP' + char(39) " & _
"EXEC sp_WLName_Report 'September', '2008', '18-09-2008',
@wlvals"
rsDW.CursorLocation = adUseClient
rsDW.Open sQRY, cnnDW, adOpenDynamic, adLockOptimistic, adCmdText
Application.ScreenUpdating = False
Sheet1.Range("E4").CopyFromRecordset rsDW
rsDW.Close
Set rsDW = Nothing
MsgBox "Import Complete", vbInformation, "SQL Connection"
cnnDW.Close
Set cnnDW = Nothing
Exit Sub
Err:
MsgBox "The following error has occured-" & vbCrLf & vbCrLf & VBA.Error,
vbCritical, "SQL Connection"
MsgBox VBA.Err
End Sub


Where am I going wrong?
Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default Run a Stored Procedure from Excel
The SQL seperator may be a colon instead of a semicolon. From the SQL
command line try putting two instructions on the same line seperated by a
colon and see what happens. get a single line multiple command working from
your SQL server before trying it in code. another possible solution is to
combine the commands on One Line using an And.

"Jez" wrote:

I have tried all 3 solutions and they still dont connect the results.

I changed all the Char(39) to Char(13) and found that I got an error message
telling me to look at the part of my statement which was like this

+ ',' +

As this didnt seem to be liked.

The other 2 solutions brought back the same error message,

Operation is not allowed when the object is closed. 3704

I am totally baffled, by this...

"Joel" wrote:

I think you are missing the carriage returns. The SQL server expects each
command to start on a new line or seperated by a terminator like a semicolon.
Your combined string don't provide a method for the server to know when one
command ends and the next commands starts.

I don't know SQL very well by try one of three solutions.

chr(13) is a cariage return and chr(39) is a single quote.

semicolons added

sQRY = "DECLARE @wlvals varchar(100); " & _
"SET @wlvals = char(39) + 'T2DEA' + char(39) + ',' +
char(39) + 'T2DEP' + char(39); " & _
"EXEC sp_WLName_Report 'September', '2008', '18-09-2008',
@wlvals"

or
Carriage returns added

sQRY = "DECLARE @wlvals varchar(100)" & chr(13) & _
"SET @wlvals = char(39) + 'T2DEA' + char(39) + ',' +
char(39) + 'T2DEP' + char(39)" & chr(13) & _
"EXEC sp_WLName_Report 'September', '2008', '18-09-2008',
@wlvals"

"Jez" wrote:

The problem area is around this section here

sQRY = "DECLARE @wlvals varchar(100) " & _
"SET @wlvals = char(39) + 'T2DEA' + char(39) + ',' +
char(39) + 'T2DEP' + char(39) " & _
"EXEC sp_WLName_Report 'September', '2008', '18-09-2008',
@wlvals"

When I run this in SQL Server it works perfect.
DECLARE @wlvals varchar(100)
SET @wlvals = char(39) + 'T2DEA' + char(39) + ',' + char(39) + 'T2DEP' +
char(39)
EXEC sp_WLName_Report 'September', '2008', '18-09-2008', @wlvals

Do I need to do something different with my code in Excel to run this
Execute Statement?


"Joel" wrote:

It meansd the OBJECT was closed for some other reason. Either the server
closed the connection because there was some security problem (an illegal
command could of done this) or when you closed rsDW. I would move the close
statement just after the open to check that it works. then I would keep
moving the line down in the code until you get the error message again. This
way you can find out where the problem is.

"Jez" wrote:

I have this code below to Execute a Command in Stored Procedure in SQL
Server. My problem is that I keep having an error message saying

Error: Operation is not allowed when the object is closed. 3704
I have no idea what that means,

Option Explicit

Dim cnnDW As ADODB.Connection
Dim rsDW As ADODB.Recordset
'Dim cnnSP As ADODB.Command
Dim sQRY As String
Dim strDWFilePath As String

Sub GetData()

On Error GoTo Err:

strDWFilePath = "Driver={SQL Native
Client};Server=CISSQL1;Database=CORPINFO;Trusted_C onnection=Yes"
Set cnnDW = New ADODB.Connection

Application.ScreenUpdating = False
cnnDW.Open strDWFilePath
Sheet1.Range("E4:F9").ClearContents
Set rsDW = New ADODB.Recordset
sQRY = "DECLARE @wlvals varchar(100) " & _
"SET @wlvals = char(39) + 'T2DEA' + char(39) + ',' +
char(39) + 'T2DEP' + char(39) " & _
"EXEC sp_WLName_Report 'September', '2008', '18-09-2008',
@wlvals"
rsDW.CursorLocation = adUseClient
rsDW.Open sQRY, cnnDW, adOpenDynamic, adLockOptimistic, adCmdText
Application.ScreenUpdating = False
Sheet1.Range("E4").CopyFromRecordset rsDW
rsDW.Close
Set rsDW = Nothing
MsgBox "Import Complete", vbInformation, "SQL Connection"
cnnDW.Close
Set cnnDW = Nothing
Exit Sub
Err:
MsgBox "The following error has occured-" & vbCrLf & vbCrLf & VBA.Error,
vbCritical, "SQL Connection"
MsgBox VBA.Err
End Sub


Where am I going wrong?
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Return a stored procedure value to Excel nurkus[_2_] Excel Programming 1 September 6th 06 07:52 AM
Run SQL Server stored procedure in Excel macro Peder Myhre Excel Programming 1 November 8th 05 10:54 PM
execute stored procedure from excel maxzsim Excel Worksheet Functions 3 May 11th 05 04:58 PM
Getting stored procedure result to excel mkarja[_2_] Excel Programming 9 March 9th 05 12:56 PM
Run a stored procedure in Excel 2K ArthurB Excel Programming 0 February 26th 04 04:46 PM


All times are GMT +1. The time now is 01:28 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement