writing data to textfile from sql server 6.5

[karun]

hi,

DataBase i am using is Sql Server6.5.
In a trigger i had written code to transfer updated records from one table to
other table.These updated records needs to be written into a text file.
I had used xp_cmdshell but it is taking time.Is there a way to write data to
flat file.


thanks in advance
karuna

[Garten]

Karun,

You may want to write a batch program to traverse the table that has the updated records and write to a flat file. Let's call the table that is holding the updated records upd_records. If you add the insert date to upd_records table, you can use the date to know which records to traverse. I'm not sure what you used with xp_cmdshell, but here's a simple script I threw together that may help you:

use pubs
go
-- This creates a batch script from the output
select "exec master..xp_cmdshell 'echo " + au_lname + ' ' + address + " >> e:au_address.txt'" from authors

-- This is the first line from the script above that can be ran.
exec master..xp_cmdshell 'echo White 10932 Bigge Rd. >> e:au_address.txt'

-- This is to check the results
exec master..xp_cmdshell 'type e:au_address.txt'

Depending on the amount of data, this may not be the most efficient. Perhaps using bcp using character datatypes with tab delimiters?

I hope this helps.
Regards,

Garten

------------
karun at 8/1/01 5:27:04 AM

hi,

DataBase i am using is Sql Server6.5.
In a trigger i had written code to transfer updated records from one table to
other table.These updated records needs to be written into a text file.
I had used xp_cmdshell but it is taking time.Is there a way to write data to
flat file.


thanks in advance
karuna

[Patrick]

Performance appears to be an issue for your application.
If the flat file could be generated at the end of the job or time to time, your trigger could insert the rows in a temporary table and when the event arises to transfer to file, you could do a BCP out. Then the global overhead would be lower.
Patrick


------------
karun at 8/1/01 5:27:04 AM

hi,

DataBase i am using is Sql Server6.5.
In a trigger i had written code to transfer updated records from one table to
other table.These updated records needs to be written into a text file.
I had used xp_cmdshell but it is taking time.Is there a way to write data to
flat file.


thanks in advance
karuna