February 9, 2004 at 10:34 am
I have a query from SS 2000 table, and need to output to a text file. How to use DTS to get it work?
declare @msg as varchar(50)
declare @area as char(2)
declare @store as char(7)
-- Work variables....
declare @lastmsg as varchar(50)
declare @lastarea as char(2)
declare csrMissing cursor for
Select Message, Area, Store_num
from micros.Portal_MissingSales_Tble
order by Message, Area, Store_num
open csrMissing
fetch next from csrMissing into
@msg
,@area
,@store
while @@FETCH_STATUS = 0
begin
set @lastmsg = @msg
set @lastarea = NULL
print ' '
print @Msg
print ' '
while @lastmsg = @msg and @@FETCH_STATUS = 0
begin
if @area = @lastarea
begin
print ' ' + @store
end
else
begin
print @area + ' ' + @store
set @lastarea = @area
end
fetch next from csrMissing into
@msg
,@area
,@store
end
end
close csrMissing
deallocate csrMissing
Thank you.
February 9, 2004 at 12:34 pm
create a temporary table (or a standard table that you can re-create each time the DTS runs), replace the print statements with inserts into that table, direct DTS to output to your text file destination.
I hope this makes sense, if it doesn't hit me back and I'll try to elaborate.
Jim
February 9, 2004 at 1:13 pm
mmm.....I think I didn't make my question clear.
I have a table which changes every 15 minutes.
In DTS, I use "Microsoft OLEDB Provider for SQL Server" as the source and a "Text file (Destination)" as the destination. I tried to put the cursor query into the Transform data Task, but seems it won't work because there's no output column name in this query.
I need help to find out how to output the cursor query into a text file.
Thank you.
February 10, 2004 at 8:14 am
Try taking a look at sp_makewebtask in BOL.
I have used the following example in an application that creates a small text file when data changes from a trigger.
sp_makewebtask @outputfile = @out, @query = @sql, @templatefile = @template, @colheaders = 1, @lastupdated = 0
February 10, 2004 at 9:46 am
Try this, it works for me.
declare @filename varchar(200),
@ole INT,
@file INT,
@fs INT,
@archivo VARCHAR(100),
@trenutniRed varchar(200)
set @archivo='e:\apps\mssql7\backup\testtxt.txt'
SELECT @trenutniRed = 'del '+ @archivo
EXEC master..xp_cmdshell @trenutniRed, NO_OUTPUT
EXECUTE @ole = sp_OACreate 'Scripting.FileSystemObject', @fs OUT
EXECUTE @ole = sp_OAMethod @fs, 'OpenTextFile', @file OUT, @archivo, 8, 1
DECLARE TxtKursor INSENSITIVE SCROLL CURSOR
FOR SELECT field1 + filed2 FROM ivavtas_temp
FOR READ ONLY
OPEN TxtKursor
FETCH NEXT FROM TxtKursor INTO @trenutniRed
WHILE @@Fetch_Status = 0
BEGIN
EXECUTE @ole = sp_OAMethod @file, 'WriteLine', Null, @trenutniRed
FETCH NEXT FROM TxtKursor INTO @trenutniRed
END
CLOSE TxtKursor
DEALLOCATE TxtKursor
EXECUTE @ole = sp_OADestroy @file
EXECUTE @ole = sp_OADestroy @fs
You must be sa or has admin priv.
bye, Eduardo
February 10, 2004 at 1:19 pm
My task is to output the text from "MissingSales_Tble" which changes every 15 minutes based on how many stores didn't transfer in their sales file.
| 1 or 2 areas has missing sales | ||
| Area | Message | Store_Num |
| CN | Missing Sales as of 2/10/2004 11:05:00 A | BK00522 |
| WI | No Missing Stores | |
| CS | No Missing Stores | |
| all areas have missing sales | ||
| Area | Message | Store_Num |
| CN | Missing Sales as of 2/10/2004 11:05:00 A | BK00522 |
| WI | Missing Sales as of 2/10/2004 11:05:00 A | BK02164 |
| WI | Missing Sales as of 2/10/2004 11:05:00 A | BK04143 |
| CS | Missing Sales as of 2/10/2004 11:05:00 A | BK00213 |
| CS | Missing Sales as of 2/10/2004 11:05:00 A | BK01470 |
| no missing sales | ||
| Area | Message | Store_Num |
| No Missing Stores |
CN BK00522
No Missing Stores
WI No Missing Stores
CS No Missing Stores
CN BK00522
WI No Missing Stores
CS No Missing Stores
CREATE PROCEDURE SP_MISSINGSALES(@filenm varchar(300)) AS
declare @vfile as varchar(300)
declare @msg as varchar(50)
declare @area as char(2)
declare @store as char(6)
declare @lastmsg as varchar(50)
declare @lastarea as char(2)
declare @fnlprint varchar(60)
declare csrMissing cursor for
Select Message, Area, Store_num
from dbo.MissingSales_Tble
order by Message, Area, Store_num
open csrMissing
fetch next from csrMissing into
@msg
,@area
,@store
while @@FETCH_STATUS = 0
begin
set @lastmsg = @msg
set @lastarea = NULL
print ' '
print @Msg
print ' '
exec dbo.SP_WRITE_TO_FILE @vfile=@filenm, @MSG=@MSG
while @lastmsg = @msg and @@FETCH_STATUS = 0
begin
if @area = @lastarea
begin
print ' ' + @store
if @store = ''
set @fnlprint = ' ' + 'No store missing'
else
set @fnlprint = ' ' + @store
exec dbo.SP_WRITE_TO_FILE @vfile=@filenm, @MSG=@fnlprint
end
else
begin
print @area + ' ' + @store
if @store = ''
begin
set @fnlprint=@area + ' ' + 'No store missing'
end
else
begin
set @fnlprint=@area + ' ' + @store
end
set @lastarea = @area
exec dbo.SP_WRITE_TO_FILE @vfile=@filenm, @MSG=@fnlprint
end
fetch next from csrMissing into
@msg
,@area
,@store
end
end
close csrMissing
deallocate csrMissing
GO
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy