SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Run a SQL Stored Procedure from VBA
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

spareus
Yak Posting Veteran

India
52 Posts

Posted - 06/20/2013 :  10:50:32  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
6059 Posts

Posted - 06/20/2013 :  11:59:20  Show Profile  Reply with Quote
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 Optimizer
TG
Go to Top of Page

spareus
Yak Posting Veteran

India
52 Posts

Posted - 06/21/2013 :  03:11:02  Show Profile  Reply with Quote
I am using Excel 2007 VBA which do not have try catch functions.
Error is thrown up by .Execute line.
Error is
1st line
run-time error '-2147467259 (80004005)'
2nd line
2013-06-20

Since 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
Go to Top of Page

spareus
Yak Posting Veteran

India
52 Posts

Posted - 06/21/2013 :  03:29:47  Show Profile  Reply with Quote
update :
error shown in 2nd line is todays date.
Stored procedure is successfully executed but error continuous.



Regards,
Spareus
Go to Top of Page

spareus
Yak Posting Veteran

India
52 Posts

Posted - 06/21/2013 :  10:13:36  Show Profile  Reply with Quote
HI,
Thanks for your time. Issue resolved.
This was due to duplicate records in a table.

Regards,



Regards,
Spareus
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6059 Posts

Posted - 06/21/2013 :  10:26:11  Show Profile  Reply with Quote
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 Optimizer
TG
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000