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 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply