I have a table [myOrders] with three columns. One of the columns
contains text output data [myText] nvarchar(500), one of them contains
a filename [myFileName] nvarchar(50), one of the columns is a bit to
record if it has been output yet[isOutput] bit default value = 0.

I am creating a SQL Agent job that needs to look at a recordset of
[myOrders] where [isOutput] = 0 and create a seperate text file for
each row using [myFileName] as the filename.


Then I need to mark [isOutput] of each record in [myOrders] as 1.

Ok, so that's the task...

What I'm thinking is I construct a stored procedure that starts with a
select statement:

Create PROCEDURE JustDoIt
AS
set nocount on

SELECT
myText, myFileName
FROM
myOrders
WHERE
(isOutput = 0)

THEN I USE BCP to create the file looping through the recordset above.
THIS IS THE PART I AM CLUELESS ABOUT!

/* NEED TO LOOP HERE */

DECLARE @ReturnCode int
DECLARE @ExportCommand varchar(255)
DECLARE @FileName nvarchar(50)
DECLARE @FileText nvarchar (500)

SELECT @FileName = myFileName

/*THIS SYNTAX IS PROBABLY TOTALLY OUTA WHACK

SET @ExportCommand =
'BCP @FileText queryout "c:\' +
@FileName +
'" -T -c -S ' + @@SERVERNAME
EXEC @ReturnCode = master..xp_cmdshell @ExportCommand

/* NEED TO EXIT LOOP HERE */


Then I update all records in [myOrders] to 1

BEGIN TRANSACTION

UPDATE
myOrders
SET isOutput = 1
WHERE
(isOutput = 0)

/* err checking here */

COMMIT TRANSACTION



I'm hoping someone can help me construct this.
Thanks,
lq

Lauren Quantrell (laurenquantrell (AT) hotmail (DOT) com) writes:
I'm glad to see that you are exploring Agent!

Did you ever consider of making it an Active-X job step? You could then
use VBscript for the task, and it may be easier to write files from
VBscript. (Then again, I have never used VB-script myself.) You could
also write a command-line program in whatever language you fancy, and
run the step as as CmdExec.

You could do this in T-SQL, by setting up a cursor, but since you
would have to fork out with xp_cmdshell for BCP for each file, there
may be a performance cost. VBscript (or whatever language) would be
more effective.

The cusror solution is fairly straightforward:

DECLARE your_cur INSENSITIVE CURSOR FOR
OPEN your_cur

WHILE 1 = 1
BEGIN
FETCH your_cur INTO @FileText, @myFileName, @orderID
IF @@fetch_status <> 0
BREAK

IF @ReturnCode = 0
UPDATE myOrders SET isOutput = 1 WHERE orderID= @orderID

END

DEALLOCATE your_cur

Now, as it written above, it assumes that @FileText is a query, but
from your narrative, I believe it is just a file. You could make it
a query by

SELECT @filetext = 'SELECT ''' + replace(@filetext, '''', '''''') + ''''

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Erland,
Thanks very much for this code. Hopefully I can construct this from
this foundation.
Question though, can I avoid using cursors by taking advantage of BCP
parameters firstrow, lastrow and batchsize parameters so that I output
one row at a time of a variable row recordset??? This would be my
first option.
Thanks,
LQ


Erland Sommarskog <esquel (AT) sommarskog (DOT) se> wrote

Erland, Thanks again for your time.
I want to do this without using a cursor, instead use select top 1 of
the recordset and loop through BCP until there are no more records.

Looks something like

SELECT top1 OrderID, myText, myFileName from tblOrders where isOutput
= 0


Do until there's no more records in select statement above:

SET @ExportCommand = 'BCP myText queryout "c:\' + myFileName+ '" -T -c
-S ' + @@SERVERNAME
EXEC @ReturnCode = master..xp_cmdshell @ExportCommand

UPDATE myOrders SET isOutput = 1 WHERE orderID= @orderID

Loop


Sorry for being so dumb about this. I have never used this sort of
construction before.




Erland Sommarskog <esquel (AT) sommarskog (DOT) se> wrote

I realize there is a cost to using cursors, but since you're going
to launch the command shell for BCP on every record, the cost of the
cursor is probably insignificant. But if you insist....

WHILE 1=1 BEGIN
from tblOrders where isOutput = 0

IF @@ROWCOUNT = 0 BREAK

[I'm answering to Jim's post, since Lauren's has not made it here yet. My
ISP reconfigured the news server and it took them two days to realize
that it was no longer working.]

Jim Geissman (jim_geissman (AT) countrywide (DOT) com) writes:
Why Laruen does not want to use a cursor I don't know, but since he
has to iterate anyway, cursor is the best solution for iteration anyway.

Say that you instead do:

If there is no index on isOutput (and one would not expect that),
and the table is huge, this can be very expensive. I have no benchmarks,
but I would suggest that for an iteration a cursor is the best way to
go, although it depends on the cursor type. FAST_FORWARD may be the
fastest, but I always use INSENSITIVE.

Anyway, Laruen should not do this in T-SQL at all, he should use VBscript
or similar as I suggested in my previous post. It will be easier to
program and execute faster.

Also, it occurred to me that if tblOrders.myText is the text that is
to be written to the file, the QueryOut thing will not work, since the
newlines in myText causes problem. Then again if the query for queryout is

'SELECT myText FROM tblOrders = ' + str(@orderid)

that will work.



--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Erland (and Jim)
You guys have gone above and beyond the call of duty in your response
to my problem and because of your repsonses I have been able to roll
this out. Thanks a million, and I have only one correction for
Erland's post - substitute "she" for "he" and it's 100% correct!
Thanks,
lq


Erland Sommarskog <esquel (AT) sommarskog (DOT) se> wrote

Lauren Quantrell (laurenquantrell (AT) hotmail (DOT) com) writes:
Glad to hear that you got it working! And my cheeks blossom in embarrassment
for calling you a man. I remember it to next time.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp