I want to run a sql stored procedure from VBA but getting error. Error is run-time error '-2147467259 (80004005)' and 2nd line 2013-06-20. I have tried all variations found on google but getting same error. Despite getting error, stored procedure is run by server but VBA is throwing error and code execution is holted. Here is my code. =============== Sub Run_SP() Dim cmd As ADODB.Command Set con = New ADODB.Connection con.Open "Provider=SQLOLEDB;Password=anish;User ID=anish;Initial Catalog=cpcom;Data Source=bhw1;Extended Properties=Trusted_Connection=no;" Set cmd = New ADODB.Command With cmd .ActiveConnection = con .CommandText = "exec cpsrmail" .CommandType = adCmdText .Execute End With con.Close Set cmd = Nothing Set con = Nothing End Sub ================ Regards,
If you have confirmed that the stored procedure has actually executed then perhaps the error has nothing to do with this Run_SP sub. I don't see any error handling in your code. Generally speaking you should use try/catch blocks so that you can gracefully recover from errors. Have you stepped through the code in debug mode to see on which line the error is thrown?
I guess I should have phrased that differently. error handling strictly speaking doesn't have to mean try/catch depending on the technology you're using and as you pointed out vba doesn't implement a try/catch. However it does have some means to handle errors. Here is a decent looking article on the topic.
Another trouble shooting tip that may have saved you some time is to run the stored procedure in a query window in management studio just to take the front end application out of the equation. That way you would have known right away that the problem was in the database.