Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
spareus
Yak Posting Veteran
52 Posts |
 Posted - 2013-06-20 : 10:50:32
|
| 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,Regards,Spareus |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2013-06-20 : 11:59:20
|
| 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?Be One with the OptimizerTG |
 |
|
|
spareus
Yak Posting Veteran
52 Posts |
Posted - 2013-06-21 : 03:11:02
|
| I am using Excel 2007 VBA which do not have try catch functions.Error is thrown up by .Execute line. Error is 1st linerun-time error '-2147467259 (80004005)' 2nd line 2013-06-20Since I am not having good experiance, I am barely able to assamble the code from various forums.Not sure how to go about error handling.Regards,Spareus |
|
|
|
spareus
Yak Posting Veteran
52 Posts |
Posted - 2013-06-21 : 03:29:47
|
| update :error shown in 2nd line is todays date.Stored procedure is successfully executed but error continuous.Regards,Spareus |
|
|
|
spareus
Yak Posting Veteran
52 Posts |
Posted - 2013-06-21 : 10:13:36
|
| HI,Thanks for your time. Issue resolved.This was due to duplicate records in a table.Regards,Regards,Spareus |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2013-06-21 : 10:26:11
|
| 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.Anyway glad it's working for you.Be One with the OptimizerTG |
|
|
|
|
|
|
Run a SQL Stored Procedure from VBA