Long sql proc called from VB6 using ADO

[jtseltmann]

I have a bunch of stored procedures that I call from my vb6 app and they work great. The problem is that some new ones may be very long. Is there a way to open the connection, call the proc using ado and then finish and let the proc run in the background? I'm not finding much at all when I google it and search.

Any input would be greatly appreciated. I thought I could use an cmd.Execute,,AdAsyncExecute but as soon as my sub exits the proc seemingly stops working. IT works great when I remove out the AdAsyncExecute and just run it and wait.

Thanks all

 

[gmmastros]

One simple thing you may want to consider...

There is a free tool that you can download and install for sql server. It's called SQLCMD, and it's a command line tool for connecting and doing stuff.

You can download it here:

http://www.microsoft.com/downloads/...60-a13c-4479-9b91-9e8b9d835cdc&displaylang=en

Scroll down to "Microsoft SQL Server 2005 Command Line Query Utility"

You can use this command line tool against any version of SQL Server. From within VB, simply run a shell command for SQLCMD. The shell command will return immediately and the SLQCMD process will run to completion.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[jtseltmann]

Thanks gmmastros. I am familiar with that and do use it in another area. I'm not familiar with how to use it and pass input parameters to the proc. I'll have to review the doc for that utility.

Thanks.
J

 

[gmmastros]

At a command prompt:

SQLCMD -?

This will show you all of the command line arguments. One thing to be careful of, the command line arguments are case sensitive.

-s = col separator
-S = server name


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[jtseltmann]

g,
one question..from what I am reading...sqlcmd utility runs a file..and I have a stored procedure in my target database that I want to execute.

 

[gmmastros]

I think you'll want to use the -Q command line argument. According to the documentation, -Q is "cmdline query" and exit.

So, something like...

[tt][blue]
sqlcmd -S[!]YourServerName[/!] -E -d[!]YourDatabaseName[/!] -Q"exec [!]YourProcedureNameHere[/!]"
[/blue][/tt]

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom