executing a stored procedure in the background

I am new to SQL and to vbscript, none the less I have a need to handle
some data that is stored in a MS SQL2000 data base.
I created a stored procedure that will select data from the SQL and
store it in a text file in a folder where is will be accessed and
processed by another process. I did this using the tools in Enterprise
Manager.
I found an example vbscript to use to execute the stored procedure. I
modified it a bit and, when it is run, the stored procedure is executed
and, utlimately, the text file is placed in the folder.
I have a few details to work out before this project is fully
functional. The major detail at this juncture is getting the stored
procedure to run without presenting dialog boxes or the process status
screen. Which brings me to the point of this topic.
I need some guidelines or pointers to the way a stored procedure is
designed so that when it runs, the process stays in the background and
dialog boxes or process screens do not appear.
Any help is appreciated.
PatrickThis isn't a feature of a stored procedure. It's how you wrote your
application. If you call a stored procedure, passing all the parameters
needed to it, you are going to get a return set of data, which the
application will handle (or not handle) based on how you wrote the app.
There is no dialog box or process screen, unless your app is producing them.
MeanOldDBA
derrickleggett@.hotmail.com
http://weblogs.sqlteam.com/derrickl
When life gives you a lemon, fire the DBA.
"raglin" wrote:

> I am new to SQL and to vbscript, none the less I have a need to handle
> some data that is stored in a MS SQL2000 data base.
> I created a stored procedure that will select data from the SQL and
> store it in a text file in a folder where is will be accessed and
> processed by another process. I did this using the tools in Enterprise
> Manager.
> I found an example vbscript to use to execute the stored procedure. I
> modified it a bit and, when it is run, the stored procedure is executed
> and, utlimately, the text file is placed in the folder.
> I have a few details to work out before this project is fully
> functional. The major detail at this juncture is getting the stored
> procedure to run without presenting dialog boxes or the process status
> screen. Which brings me to the point of this topic.
> I need some guidelines or pointers to the way a stored procedure is
> designed so that when it runs, the process stays in the background and
> dialog boxes or process screens do not appear.
> Any help is appreciated.
> Patrick
>|||raglin (pzelenka@.gmail.com) writes:
> I am new to SQL and to vbscript, none the less I have a need to handle
> some data that is stored in a MS SQL2000 data base.
> I created a stored procedure that will select data from the SQL and
> store it in a text file in a folder where is will be accessed and
> processed by another process. I did this using the tools in Enterprise
> Manager.
> I found an example vbscript to use to execute the stored procedure. I
> modified it a bit and, when it is run, the stored procedure is executed
> and, utlimately, the text file is placed in the folder.
> I have a few details to work out before this project is fully
> functional. The major detail at this juncture is getting the stored
> procedure to run without presenting dialog boxes or the process status
> screen. Which brings me to the point of this topic.
> I need some guidelines or pointers to the way a stored procedure is
> designed so that when it runs, the process stays in the background and
> dialog boxes or process screens do not appear.
It sounds as if the simplest in your case, you would set up a job that
is run from SQL Server Agent. You do this under Management->Jobs in
Enterprise Manager. A job consists of one or more steps and has a schedule.
A step can be as simple as a T-SQL command. In your case it would be
run the VB script. I assume, then, that VB script is the part that receives
the data and creates the file.
If the procedure runs BCP from xp_cmdshell to create the file, then
there is no need for the VBscript thing - you can run the procedure
directly from the job step.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx