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,
lqLauren Quantrell (laurenquantrell@.hotmail.com) writes:
> 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.
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
> SELECT
> myText, myFileName
, OrderID
> FROM
> myOrders
> WHERE
> (isOutput = 0)
OPEN your_cur
WHILE 1 = 1
BEGIN
FETCH your_cur INTO @.FileText, @.myFileName, @.orderID
IF @.@.fetch_status <> 0
BREAK
> SET @.ExportCommand =
> 'BCP @.FileText queryout "c:\' +
> @.FileName +
> '" -T -c -S ' + @.@.SERVERNAME
> EXEC @.ReturnCode = master..xp_cmdshell @.ExportCommand
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@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../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@.sommarskog.se> wrote in message news:<Xns95316883662FYazorman@.127.0.0.1>...
> Lauren Quantrell (laurenquantrell@.hotmail.com) writes:
> > 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.
> 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
> > SELECT
> > myText, myFileName
> , OrderID
> > FROM
> > myOrders
> > WHERE
> > (isOutput = 0)
> OPEN your_cur
> WHILE 1 = 1
> BEGIN
> FETCH your_cur INTO @.FileText, @.myFileName, @.orderID
> IF @.@.fetch_status <> 0
> BREAK
> > SET @.ExportCommand =
> > 'BCP @.FileText queryout "c:\' +
> > @.FileName +
> > '" -T -c -S ' + @.@.SERVERNAME
> > EXEC @.ReturnCode = master..xp_cmdshell @.ExportCommand
> 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, 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
>>WHatI need here is to figure out how to extract the value of
myFileName and myText and pass it to the BCP Utility<<
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@.sommarskog.se> wrote in message news:<Xns95316883662FYazorman@.127.0.0.1>...
> Lauren Quantrell (laurenquantrell@.hotmail.com) writes:
> > 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.
> 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
> > SELECT
> > myText, myFileName
> , OrderID
> > FROM
> > myOrders
> > WHERE
> > (isOutput = 0)
> OPEN your_cur
> WHILE 1 = 1
> BEGIN
> FETCH your_cur INTO @.FileText, @.myFileName, @.orderID
> IF @.@.fetch_status <> 0
> BREAK
> > SET @.ExportCommand =
> > 'BCP @.FileText queryout "c:\' +
> > @.FileName +
> > '" -T -c -S ' + @.@.SERVERNAME
> > EXEC @.ReturnCode = master..xp_cmdshell @.ExportCommand
> 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, '''', ''') + ''''|||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...
> 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
WHILE 1=1 BEGIN
SELECT TOP 1 @.OrderID=OrderID,@.MyText=myText,@.MyFileName=myFile Name
from tblOrders where isOutput = 0
IF @.@.ROWCOUNT = 0 BREAK
> 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
END -- end of loop|||[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@.countrywide.com) writes:
> 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...
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:
> SELECT TOP 1 @.OrderID=OrderID,@.MyText=myText,@.MyFileName=myFile Name
> from tblOrders where isOutput = 0
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@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../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@.sommarskog.se> wrote in message news:<Xns9534EF744A28AYazorman@.127.0.0.1>...
> [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@.countrywide.com) writes:
> > 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...
> 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:
> > SELECT TOP 1 @.OrderID=OrderID,@.MyText=myText,@.MyFileName=myFile Name
> > from tblOrders where isOutput = 0
> 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.|||Lauren Quantrell (laurenquantrell@.hotmail.com) writes:
> 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!
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@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql
No comments:
Post a Comment