|
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? |
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? |
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? |
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? |
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? |
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? |
Run a Stored Procedure from Excel
I tried it using this one line
sQRY = "EXEC jez.sp_WLName_Report 'September', '2008', '17-09-2008', 'PGA'" I altered the Stored Procedure to pick up this one parameter. This works great... My only problem is that I have more than 1 parmater in most cases, thats why I have changed the Exec Statement to represnt the changes in parmaters. "Joel" wrote: 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? |
Run a Stored Procedure from Excel
As I said in my last posting use AND to connect parameters.
I was doing a macro for Outlook which also uses SQL for doing searches in the Inbox. Here is what I had to do strF = "urn:schemas:httpmail:" & _ "subject LIKE '%Lock%' AND" & _ "%today(urn:schemas:httpmail:datereceived)%" Here is another trick I use. In Excel worksheet turn on Macro Recorder: Tool - Macro - Record New Macro. Then do from worksheet menu: Data - Import External Data - New Database Query. Setup you SQL query in the Excel Wizard. When you are done stop record macro Tools - Macro - Stop Recording. Then look at the query that was recorded to see if you can figure out the correct format. "Jez" wrote: I tried it using this one line sQRY = "EXEC jez.sp_WLName_Report 'September', '2008', '17-09-2008', 'PGA'" I altered the Stored Procedure to pick up this one parameter. This works great... My only problem is that I have more than 1 parmater in most cases, thats why I have changed the Exec Statement to represnt the changes in parmaters. "Joel" wrote: 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? |
Run a Stored Procedure from Excel
I tried using the AND to connect the parameters... that again didnt work,
brought same error message. I tried what you had done in your code and still same error message... I alos tried copying what the record macro was doing. It wouldnt let me run a stored procedure through the MS Query, but I brough back a table and tried to use the code. This used a cmdSP.CommandText = Array("stProcName") Again a problem... I have been reading things on the net about this and found that if I changed the code to this below it should work Option Explicit Dim cnnDW As ADODB.Connection Dim rsDW As ADODB.Recordset Dim cmdSP As ADODB.Command Dim strDWFilePath As String Dim stProcName As String Sub GetData() strDWFilePath = "Driver={SQL Native Client};Server=CISSQL1;Database=CORPINFO;Trusted_C onnection=Yes" Set cnnDW = New ADODB.Connection Application.ScreenUpdating = False cnnDW.Open strDWFilePath Set cmdSP = New ADODB.Command Set rsDW = New ADODB.Recordset stProcName = "DECLARE @wlvals varchar(100) " & _ "SET @wlvals = char(34) + 'T2DEA' + char(34) + ',' + char(34) + 'T2DEP' + char(34);" & _ "EXEC sp_WLName_Report 'September', '2008', '18-09-2008', @wlvals" 'Define name of Stored Procedure to execute." cmdSP.CommandType = adCmdStoredProc 'Define the ADODB command cmdSP.ActiveConnection = cnnDW 'Set the command connection string cmdSP.CommandText = stProcName 'Define Stored Procedure to run Sheet1.Range("E4:F9").ClearContents rsDW.Open cmdSP.Execute(stProcName) 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 End Sub It now has moved my errror message from the Exec Statement to the line below, but I am still baffled as to why it doesnt work... rsDW.Open cmdSP.Execute(stProcName) My Error message... [Microsoft][SQL Native Client] Syntax Error, Permission Violation or Other NonSpecific Error Where am I going wrong? "Joel" wrote: As I said in my last posting use AND to connect parameters. I was doing a macro for Outlook which also uses SQL for doing searches in the Inbox. Here is what I had to do strF = "urn:schemas:httpmail:" & _ "subject LIKE '%Lock%' AND" & _ "%today(urn:schemas:httpmail:datereceived)%" Here is another trick I use. In Excel worksheet turn on Macro Recorder: Tool - Macro - Record New Macro. Then do from worksheet menu: Data - Import External Data - New Database Query. Setup you SQL query in the Excel Wizard. When you are done stop record macro Tools - Macro - Stop Recording. Then look at the query that was recorded to see if you can figure out the correct format. "Jez" wrote: I tried it using this one line sQRY = "EXEC jez.sp_WLName_Report 'September', '2008', '17-09-2008', 'PGA'" I altered the Stored Procedure to pick up this one parameter. This works great... My only problem is that I have more than 1 parmater in most cases, thats why I have changed the Exec Statement to represnt the changes in parmaters. "Joel" wrote: 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? |
Run a Stored Procedure from Excel
No tsure if I'm interpreting yuo last posting correctly. which statment is
failing. You said the line after the execute statement. do you mean the Apllication statement or the CopyFromRecordset statement. I would also like to know if you got any data returned from the execute statement. If the line executed you should see data. then I would think this line is the error line Sheet1.Range("E4").CopyFromRecordset rsDW Do you have a sheet1. Look in Your VBA Project Explorer. Sheet1 should be the first name for the sheet not the name in the parenthsis. You may not have the correct name of the sheet. You also want to find out if you have a rsDW variable defined. From the worksheet menu File - Properties - Custom and see if there is a rsDW defined. Also, I think you need to replace a blank in the code with a semicolon. Your problem may still the original one wher you have three commands in a single string. Your code had a semicolon between the 2nd command and the third command. There was no sperator between the 1st command and the 2nd command. I also did two other things. 1) added some line continuation characters becasue the long lines on this website become multiple lines that creat errors. 2) I added the ADO library to my references in VBA by going to the VBA menu Tools - References and then checking the Active Data Objects 2.8 library. Use the highest number library that is in your computrer. Option Explicit Dim cnnDW As ADODB.Connection Dim rsDW As ADODB.Recordset Dim cmdSP As ADODB.Command Dim strDWFilePath As String Dim stProcName As String Sub GetData() strDWFilePath = "Driver={SQL Native Client};" & _ "Server=CISSQL1;" & _ "Database=CORPINFO;" & _ "Trusted_Connection=Yes" Set cnnDW = New ADODB.Connection Application.ScreenUpdating = False cnnDW.Open strDWFilePath Set cmdSP = New ADODB.Command Set rsDW = New ADODB.Recordset stProcName = "DECLARE @wlvals varchar(100);" & _ "SET @wlvals = " & _ "char(34) + 'T2DEA' + char(34) + ',' + " & _ "char(34) + 'T2DEP' + char(34);" & _ "EXEC sp_WLName_Report" 'September', '2008', '18-09-2008', @wlvals" 'Define name of Stored Procedure to execute." cmdSP.CommandType = adCmdStoredProc 'Define the ADODB command cmdSP.ActiveConnection = cnnDW 'Set the command connection string cmdSP.CommandText = stProcName 'Define Stored Procedure to run Sheet1.Range("E4:F9").ClearContents rsDW.Open cmdSP.Execute(stProcName) 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 End Sub "Jez" wrote: I tried using the AND to connect the parameters... that again didnt work, brought same error message. I tried what you had done in your code and still same error message... I alos tried copying what the record macro was doing. It wouldnt let me run a stored procedure through the MS Query, but I brough back a table and tried to use the code. This used a cmdSP.CommandText = Array("stProcName") Again a problem... I have been reading things on the net about this and found that if I changed the code to this below it should work Option Explicit Dim cnnDW As ADODB.Connection Dim rsDW As ADODB.Recordset Dim cmdSP As ADODB.Command Dim strDWFilePath As String Dim stProcName As String Sub GetData() strDWFilePath = "Driver={SQL Native Client};Server=CISSQL1;Database=CORPINFO;Trusted_C onnection=Yes" Set cnnDW = New ADODB.Connection Application.ScreenUpdating = False cnnDW.Open strDWFilePath Set cmdSP = New ADODB.Command Set rsDW = New ADODB.Recordset stProcName = "DECLARE @wlvals varchar(100) " & _ "SET @wlvals = char(34) + 'T2DEA' + char(34) + ',' + char(34) + 'T2DEP' + char(34);" & _ "EXEC sp_WLName_Report 'September', '2008', '18-09-2008', @wlvals" 'Define name of Stored Procedure to execute." cmdSP.CommandType = adCmdStoredProc 'Define the ADODB command cmdSP.ActiveConnection = cnnDW 'Set the command connection string cmdSP.CommandText = stProcName 'Define Stored Procedure to run Sheet1.Range("E4:F9").ClearContents rsDW.Open cmdSP.Execute(stProcName) 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 End Sub It now has moved my errror message from the Exec Statement to the line below, but I am still baffled as to why it doesnt work... rsDW.Open cmdSP.Execute(stProcName) My Error message... [Microsoft][SQL Native Client] Syntax Error, Permission Violation or Other NonSpecific Error Where am I going wrong? "Joel" wrote: As I said in my last posting use AND to connect parameters. I was doing a macro for Outlook which also uses SQL for doing searches in the Inbox. Here is what I had to do strF = "urn:schemas:httpmail:" & _ "subject LIKE '%Lock%' AND" & _ "%today(urn:schemas:httpmail:datereceived)%" Here is another trick I use. In Excel worksheet turn on Macro Recorder: Tool - Macro - Record New Macro. Then do from worksheet menu: Data - Import External Data - New Database Query. Setup you SQL query in the Excel Wizard. When you are done stop record macro Tools - Macro - Stop Recording. Then look at the query that was recorded to see if you can figure out the correct format. "Jez" wrote: I tried it using this one line sQRY = "EXEC jez.sp_WLName_Report 'September', '2008', '17-09-2008', 'PGA'" I altered the Stored Procedure to pick up this one parameter. This works great... My only problem is that I have more than 1 parmater in most cases, thats why I have changed the Exec Statement to represnt the changes in parmaters. "Joel" wrote: 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? |
Run a Stored Procedure from Excel
The error falls on line
rsDW.Open cmdSP.Execute(stProcName) When debugging it, hovering over the yellow line the rsDW.Open shows as rsDW=Nothing. I've changed the Refernece in VBA Tools, but dont understand how you mean with this below... I couldnt see anything like rsDW in there "You also want to find out if you have a rsDW variable defined. From the worksheet menu File - Properties - Custom and see if there is a rsDW defined" "Joel" wrote: No tsure if I'm interpreting yuo last posting correctly. which statment is failing. You said the line after the execute statement. do you mean the Apllication statement or the CopyFromRecordset statement. I would also like to know if you got any data returned from the execute statement. If the line executed you should see data. then I would think this line is the error line Sheet1.Range("E4").CopyFromRecordset rsDW Do you have a sheet1. Look in Your VBA Project Explorer. Sheet1 should be the first name for the sheet not the name in the parenthsis. You may not have the correct name of the sheet. You also want to find out if you have a rsDW variable defined. From the worksheet menu File - Properties - Custom and see if there is a rsDW defined. Also, I think you need to replace a blank in the code with a semicolon. Your problem may still the original one wher you have three commands in a single string. Your code had a semicolon between the 2nd command and the third command. There was no sperator between the 1st command and the 2nd command. I also did two other things. 1) added some line continuation characters becasue the long lines on this website become multiple lines that creat errors. 2) I added the ADO library to my references in VBA by going to the VBA menu Tools - References and then checking the Active Data Objects 2.8 library. Use the highest number library that is in your computrer. Option Explicit Dim cnnDW As ADODB.Connection Dim rsDW As ADODB.Recordset Dim cmdSP As ADODB.Command Dim strDWFilePath As String Dim stProcName As String Sub GetData() strDWFilePath = "Driver={SQL Native Client};" & _ "Server=CISSQL1;" & _ "Database=CORPINFO;" & _ "Trusted_Connection=Yes" Set cnnDW = New ADODB.Connection Application.ScreenUpdating = False cnnDW.Open strDWFilePath Set cmdSP = New ADODB.Command Set rsDW = New ADODB.Recordset stProcName = "DECLARE @wlvals varchar(100);" & _ "SET @wlvals = " & _ "char(34) + 'T2DEA' + char(34) + ',' + " & _ "char(34) + 'T2DEP' + char(34);" & _ "EXEC sp_WLName_Report" 'September', '2008', '18-09-2008', @wlvals" 'Define name of Stored Procedure to execute." cmdSP.CommandType = adCmdStoredProc 'Define the ADODB command cmdSP.ActiveConnection = cnnDW 'Set the command connection string cmdSP.CommandText = stProcName 'Define Stored Procedure to run Sheet1.Range("E4:F9").ClearContents rsDW.Open cmdSP.Execute(stProcName) 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 End Sub "Jez" wrote: I tried using the AND to connect the parameters... that again didnt work, brought same error message. I tried what you had done in your code and still same error message... I alos tried copying what the record macro was doing. It wouldnt let me run a stored procedure through the MS Query, but I brough back a table and tried to use the code. This used a cmdSP.CommandText = Array("stProcName") Again a problem... I have been reading things on the net about this and found that if I changed the code to this below it should work Option Explicit Dim cnnDW As ADODB.Connection Dim rsDW As ADODB.Recordset Dim cmdSP As ADODB.Command Dim strDWFilePath As String Dim stProcName As String Sub GetData() strDWFilePath = "Driver={SQL Native Client};Server=CISSQL1;Database=CORPINFO;Trusted_C onnection=Yes" Set cnnDW = New ADODB.Connection Application.ScreenUpdating = False cnnDW.Open strDWFilePath Set cmdSP = New ADODB.Command Set rsDW = New ADODB.Recordset stProcName = "DECLARE @wlvals varchar(100) " & _ "SET @wlvals = char(34) + 'T2DEA' + char(34) + ',' + char(34) + 'T2DEP' + char(34);" & _ "EXEC sp_WLName_Report 'September', '2008', '18-09-2008', @wlvals" 'Define name of Stored Procedure to execute." cmdSP.CommandType = adCmdStoredProc 'Define the ADODB command cmdSP.ActiveConnection = cnnDW 'Set the command connection string cmdSP.CommandText = stProcName 'Define Stored Procedure to run Sheet1.Range("E4:F9").ClearContents rsDW.Open cmdSP.Execute(stProcName) 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 End Sub It now has moved my errror message from the Exec Statement to the line below, but I am still baffled as to why it doesnt work... rsDW.Open cmdSP.Execute(stProcName) My Error message... [Microsoft][SQL Native Client] Syntax Error, Permission Violation or Other NonSpecific Error Where am I going wrong? "Joel" wrote: As I said in my last posting use AND to connect parameters. I was doing a macro for Outlook which also uses SQL for doing searches in the Inbox. Here is what I had to do strF = "urn:schemas:httpmail:" & _ "subject LIKE '%Lock%' AND" & _ "%today(urn:schemas:httpmail:datereceived)%" Here is another trick I use. In Excel worksheet turn on Macro Recorder: Tool - Macro - Record New Macro. Then do from worksheet menu: Data - Import External Data - New Database Query. Setup you SQL query in the Excel Wizard. When you are done stop record macro Tools - Macro - Stop Recording. Then look at the query that was recorded to see if you can figure out the correct format. "Jez" wrote: I tried it using this one line sQRY = "EXEC jez.sp_WLName_Report 'September', '2008', '17-09-2008', 'PGA'" I altered the Stored Procedure to pick up this one parameter. This works great... My only problem is that I have more than 1 parmater in most cases, thats why I have changed the Exec Statement to represnt the changes in parmaters. "Joel" wrote: 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 |
Run a Stored Procedure from Excel
I wasn't sure where the error occur from you last posting. I thought if it
got past the Open statement that a query would of been added to the workbook. If it did there should be a reference to the query in the File - Properties - custom. Did you try adding the missing semicolon? I would also change the line below from rsDW.Open cmdSP.Execute(stProcName) to rsDW.Open cmdSP stProcName is already defined in cmdSo.CommandText "Jez" wrote: The error falls on line rsDW.Open cmdSP.Execute(stProcName) When debugging it, hovering over the yellow line the rsDW.Open shows as rsDW=Nothing. I've changed the Refernece in VBA Tools, but dont understand how you mean with this below... I couldnt see anything like rsDW in there "You also want to find out if you have a rsDW variable defined. From the worksheet menu File - Properties - Custom and see if there is a rsDW defined" "Joel" wrote: No tsure if I'm interpreting yuo last posting correctly. which statment is failing. You said the line after the execute statement. do you mean the Apllication statement or the CopyFromRecordset statement. I would also like to know if you got any data returned from the execute statement. If the line executed you should see data. then I would think this line is the error line Sheet1.Range("E4").CopyFromRecordset rsDW Do you have a sheet1. Look in Your VBA Project Explorer. Sheet1 should be the first name for the sheet not the name in the parenthsis. You may not have the correct name of the sheet. You also want to find out if you have a rsDW variable defined. From the worksheet menu File - Properties - Custom and see if there is a rsDW defined. Also, I think you need to replace a blank in the code with a semicolon. Your problem may still the original one wher you have three commands in a single string. Your code had a semicolon between the 2nd command and the third command. There was no sperator between the 1st command and the 2nd command. I also did two other things. 1) added some line continuation characters becasue the long lines on this website become multiple lines that creat errors. 2) I added the ADO library to my references in VBA by going to the VBA menu Tools - References and then checking the Active Data Objects 2.8 library. Use the highest number library that is in your computrer. Option Explicit Dim cnnDW As ADODB.Connection Dim rsDW As ADODB.Recordset Dim cmdSP As ADODB.Command Dim strDWFilePath As String Dim stProcName As String Sub GetData() strDWFilePath = "Driver={SQL Native Client};" & _ "Server=CISSQL1;" & _ "Database=CORPINFO;" & _ "Trusted_Connection=Yes" Set cnnDW = New ADODB.Connection Application.ScreenUpdating = False cnnDW.Open strDWFilePath Set cmdSP = New ADODB.Command Set rsDW = New ADODB.Recordset stProcName = "DECLARE @wlvals varchar(100);" & _ "SET @wlvals = " & _ "char(34) + 'T2DEA' + char(34) + ',' + " & _ "char(34) + 'T2DEP' + char(34);" & _ "EXEC sp_WLName_Report" 'September', '2008', '18-09-2008', @wlvals" 'Define name of Stored Procedure to execute." cmdSP.CommandType = adCmdStoredProc 'Define the ADODB command cmdSP.ActiveConnection = cnnDW 'Set the command connection string cmdSP.CommandText = stProcName 'Define Stored Procedure to run Sheet1.Range("E4:F9").ClearContents rsDW.Open cmdSP.Execute(stProcName) 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 End Sub "Jez" wrote: I tried using the AND to connect the parameters... that again didnt work, brought same error message. I tried what you had done in your code and still same error message... I alos tried copying what the record macro was doing. It wouldnt let me run a stored procedure through the MS Query, but I brough back a table and tried to use the code. This used a cmdSP.CommandText = Array("stProcName") Again a problem... I have been reading things on the net about this and found that if I changed the code to this below it should work Option Explicit Dim cnnDW As ADODB.Connection Dim rsDW As ADODB.Recordset Dim cmdSP As ADODB.Command Dim strDWFilePath As String Dim stProcName As String Sub GetData() strDWFilePath = "Driver={SQL Native Client};Server=CISSQL1;Database=CORPINFO;Trusted_C onnection=Yes" Set cnnDW = New ADODB.Connection Application.ScreenUpdating = False cnnDW.Open strDWFilePath Set cmdSP = New ADODB.Command Set rsDW = New ADODB.Recordset stProcName = "DECLARE @wlvals varchar(100) " & _ "SET @wlvals = char(34) + 'T2DEA' + char(34) + ',' + char(34) + 'T2DEP' + char(34);" & _ "EXEC sp_WLName_Report 'September', '2008', '18-09-2008', @wlvals" 'Define name of Stored Procedure to execute." cmdSP.CommandType = adCmdStoredProc 'Define the ADODB command cmdSP.ActiveConnection = cnnDW 'Set the command connection string cmdSP.CommandText = stProcName 'Define Stored Procedure to run Sheet1.Range("E4:F9").ClearContents rsDW.Open cmdSP.Execute(stProcName) 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 End Sub It now has moved my errror message from the Exec Statement to the line below, but I am still baffled as to why it doesnt work... rsDW.Open cmdSP.Execute(stProcName) My Error message... [Microsoft][SQL Native Client] Syntax Error, Permission Violation or Other NonSpecific Error Where am I going wrong? "Joel" wrote: As I said in my last posting use AND to connect parameters. I was doing a macro for Outlook which also uses SQL for doing searches in the Inbox. Here is what I had to do strF = "urn:schemas:httpmail:" & _ "subject LIKE '%Lock%' AND" & _ "%today(urn:schemas:httpmail:datereceived)%" Here is another trick I use. In Excel worksheet turn on Macro Recorder: Tool - Macro - Record New Macro. Then do from worksheet menu: Data - Import External Data - New Database Query. Setup you SQL query in the Excel Wizard. When you are done stop record macro Tools - Macro - Stop Recording. Then look at the query that was recorded to see if you can figure out the correct format. "Jez" wrote: I tried it using this one line sQRY = "EXEC jez.sp_WLName_Report 'September', '2008', '17-09-2008', 'PGA'" I altered the Stored Procedure to pick up this one parameter. This works great... My only problem is that I have more than 1 parmater in most cases, thats why I have changed the Exec Statement to represnt the changes in parmaters. "Joel" wrote: 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) " & _ |
Run a Stored Procedure from Excel
Hi. I've had exactly the same problem.
Try this - it worked for me. Change: cmdSP.CommandType = adCmdStoredProc To: cmdSP.CommandType = adCmdUnknown |
| All times are GMT +1. The time now is 07:05 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com