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

Execute SQL Task not running?

Hi All,

I have a strange problem that I can't find any information about and was hoping someone could help me out.

Some background:

* I am calling an SSIS package from ASP.Net using the in-process, .net methods.
* I am importing a flat file into a staging SQL Server table using the OLE DB Destination.

* After I import the data, I call some Execute SQL Tasks that does some cleaning of the data.

* I then move the data from the staging table to a final table.

Here's the problem:

When the SQL Task like this:
WHILE @.@.rowcount > 0
UPDATE Staging_Table
SET [Test1] = REPLACE([Test1], SUBSTRING([Test1], PATINDEX('%[^a-zA-Z0-9 ]%', [Test1]), 1), ' ')
WHERE PATINDEX('%[^a-zA-Z0-9 ]%', [Test1]) <> 0

is supposed to be run, sometimes it will, sometimes it won't. The character replacement always works when I test the package through BIDS but when I call the package from code, sometimes the task doesn't seem to get called. Or, at least, it isn't updating the table correctly. The staging table is poplated and copied to the final table but the character replacement doesn't occur in-between.

Does anyone have any advice on the possible causes or where to investigate further?

Thanks,
s.

Turn on logging so you can find out what's going on.

-Jamie

|||Hi Jamie,

The log doesn't show anything different between a correct and incorrect run. I think I may have figured out an answer though. I finally figured out that the problem only occurs when I run multiple packages on multiple threads at the same time. Although none of the packages update the same table, perhaps it is something with the @.rowcount that is causing trouble? I was able to fix the problem (as far as I have seen so far) by changing the package executions to use dtexec.

s.