April 17, 2012 at 10:15 am
Hello,
I am trying to execute a dynamic stored procedure that will take 1 input parameter and will perform an operation in SQL Server 2008 database.
I have created a SQL Connection from excel and written the following macro.
Sub RefreshQuery()
With ActiveWorkbook.Connections("ELTH_INVOICE_READ").OLEDBConnection
.CommandText = "EXECUTE dbo.USP_Invoice_Claim " & Range("P2").Value & ""
End With
ActiveWorkbook.Connections("ELTH_INVOICE_READ").Refresh
End Sub
When I am executing the macro I am getting the following error:
Runtime Error 9:
Subscript out of range
Can anybody help me out with a proper script to execute the Stored Procedure from excel or to find out a fix for the issue?
April 17, 2012 at 9:19 pm
Can anybody provide any suggestion on this?
April 18, 2012 at 2:42 am
Hi,
a little difficult to see what is going on but the error is definitely an Excel error and, as far as I can see at the moment, seem to suggest that the connection named ELTH_INVOICE_READ does not exist in the active workbook (if you have more than one workbook open when running this thing, make sure the correct one is active).
To test this, I created a connection to a SQL server which had a SP named as above, I named the connection the same as yours and ran the code.
As an aside note, the use of the WITH statement is unnecessary in your code - you should use WITH only when you are going to grab an object and toy with it more than once ... e.g.
WITH myObject
.Color = pink
.font = arial
END WITH
... there I picked the object, did 2 things to it and then then dropped it ... better than
myobject.color = pink
mybject.font = arial
which is equivalent to picking up the object, doing something to it, dropping it, then picking it up again, doing something to it again, and dropping it again.
In your case, you are preparing to hold up an object in the air but then do only one thing to it ... your CPU = disappointed 🙂
I would change it to
With ActiveWorkbook.Connections("ELTH_INVOICE_READ")
.OLEDBConnection.CommandText = "EXECUTE dbo.USP_Invoice_Claim " & Range("P2").Value & ""
.Refresh
End With
Whay are you adding an empty string at the end of your CommandText btw?
Cheers,
B
April 19, 2012 at 4:28 am
Thanks for your reply.
With this code I am able to execute the stored procedure.
the stored proc is updating the required table in the database but is throwing an error saying :
Run-time error:1004
The query did not run, or the database tabble could not be opened.
Check the database server or contact your database administrator. Make sure the external database is available and hasn't been removed or reorganized, then try the operator again.
Can you please help me in getting rid of the error?
April 19, 2012 at 9:12 am
Hi,
step through your code and see where it falls over.
In addition, check that the server accepts remote connections (server properties > connections), and that your user credentials allow to log into the server/database/select table rows.
Furthermore, it is not impossible that the firewall may also be blocking something, ensure that the correct ports are open and all that (considering the table does update, I would think you're ok on that front).
Finally, 1004 is again an Excel error code - application defined error ... may indicate that the worksheet name is incorrect in your code? range is invalid (do you have an offset somewhere?), or something like that.
Sorry I can not be of more help just now,
Cheers,
B
April 19, 2012 at 10:01 am
When ran through the steps it failes at .refresh.
The excel is able to connect to the server. When I pull record for any table using the same connection by selecting a table it is able to pull records.
Even when I use the macro it is executing the stored procedure and updating the table. after that it is throwing the mentioned error.
April 20, 2012 at 1:33 am
NOt sure where to go from there - I would suggest deconstructing the thing ...
Do your code to call SP and update SQL Server table ... then separately - i.e. create two buttons on your interface - call the sub to update your data-area from the (updated) SQL Server table.
So essentially separate it out into distinct SubProcedures (i know it's not good practice, but just trying to see if the connection drops after calling SP but before .refresh ... )
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply