Excel - SQL Server - Running store procedure

How do I call a SQL Server store procedure to run from Excel without having Excel hanging with hourglass? For example if my SQL store procedure takes 10 mins to run, then the Excel is hanging in there for 10 mins at the "cmd.Excecute" line. Is there a way to run the sp in the background and let Excel continue to the next line?

    If Me.M_Sat_Load_and_process Then
        Set cmd = New ADODB.Command
        cmd.ActiveConnection = Cn
        cmd.CommandText = "Exec TRP.M_Sat_update_process"
        cmd.CommandTimeout = 1200               ' 20 minutes
        cmd.Execute
        MsgBox ("Loaded & Processed!")
    Else
        MsgBox ("Only loaded, please process!")
        GoTo Clean_up
    End If
HNA071252Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

FamousMortimerCommented:
Hi HNA,

The answer is technically "No, that cannot be done with VBA."  VBA is a single threaded apartment and for this to be possible, you need multiple threads.

However, you can build an application that runs the procedure and returns the data back to Excel.  That could be tricky, but can be done.

You might want to evaluate your stored procedure and find a way to make it process faster.
If it is a local server and processing only local databases, there is something wrong... horribly wrong.  If you are processing external data, you might want to think about creating a 'data abstraction layer' on your local server that updates every so often, then process the data via the layer.  Depending on your requirements that might not be feasible because the data will not be 100% live.

Maybe someone else has a method to handle this that I am unaware of.

Anyway, good luck!
0
sameer2010Commented:
I would suggest that you move above code that executes SP to another macro file, say Test.VBS. Also, let it produce some textfile with output as mentioned above.
Then execute the macro file using SHELL command.
Write a timer based macro to check if the file is present (you can add header, etc. to check it for current processing, etc.)
If so, continue with your rest of the processing.
0
Anthony PerkinsCommented:
There are a couple of ways you can do that:
1. Execute the Stored Procedure asynchronously using ADO.
2. Start a SQL Server Agent job that has the Stored Procedure in a step.
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

HNA071252Author Commented:
I like acperkins' solution. Can you please guide the detail steps on how to do this?
0
Anthony PerkinsCommented:
Which one?  There were two solutions.
0
Anthony PerkinsCommented:
Also, please take the time to re-read the EE Guidelines regarding grading standards:
What grade should I award?
0
HNA071252Author Commented:
Yes I read the grading guidelines. I need help in details both step 1 & 2 of your solution.
0
Anthony PerkinsCommented:
1. Use the adAsyncExecute option when executing the ADO Command object.
2. Create a job in SQL Server that contains this Stored Procedure.  Start the job with the system Stored Procedure sp_start_job
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
HNA071252Author Commented:
I can't understand your comment. I appreciate if you could give me the steps in detail on how do I Use the adAsyncExecute option when executing the ADO Command object? and how to Create a job in SQL Server that contains this Stored Procedure or how to Start the job with the system Stored Procedure sp_start_job? Thanks so much, I'm still learning, not an expert with the SQl Server
0
Anthony PerkinsCommented:
Ok.  

Are you familiar with the Command object in ADO and specifically the Execute method?  I see you have it in your code, but are you familiar with all the parameters?  If not may I suggest you read up on it.  One of the options is the adAsyncExecute.  This will allow you to run the Stored Procedure asynchronously.

Second for the SQL Server Agent job approach, you are not using SQL Server Express are you?  If so, this is not an option.  If you are using some other retail Edition are you familiar with creating SQL Server Agent jobs?  Do you have permission to create Jobs?  Do you have permission to execute jobs?

Yes I read the grading guidelines
Then would you like to revisit the grade you awarded in the following link?
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_28100469.html
0
HNA071252Author Commented:
What do you want me to do with revisiting the grade that I already awarded?
0
Anthony PerkinsCommented:
Since this is off topic I have posted in that thread.
0
HNA071252Author Commented:
No, I'm familiar with all the parameters. Could you please help me with the syntax?

Is it like this?

cmd.CommandType = adCmdStoredProc
cmd.Execute , , adAsyncExecute
0
HNA071252Author Commented:
I meant to say I'm not familiar with all the parameters
0
Anthony PerkinsCommented:
Is it like this?
That is correct.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.