Discussion Locked

About sql server 2k trigger/procedure

0 helpful
1 reply
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

When a table is modified, I want a trigger to export the data of table to a file,such as *.txt or other.
I have tried to realize that by using following codes, but failed.
create trigger tr_ActionLog on ActionLog for update,insert, delete
AS
SET NOCOUNT ON
--declare @strSql varchar(255)
exec dbo.ExportData2Csv 'liulzh', 'TCDB', 'ActionLog', 'sa', '', 'e:\ActionLog.csv', ','

create procedure ExportData2Csv
@serverName varchar(255),
@dbName varchar(100),
@tblName varchar(100), --name of the table to be export
@username varchar(100),
@password varchar(100),
@csvFilename varchar(255), --the abosolute cvs file nmae
@splitChar char --the char to split the data of different column
AS
SET NOCOUNT ON
declare @err int, @strSql varchar(255);
set @strSql = 'bcp ' + @dbName + '..' + @tblName + ' out ' + @csvFilename
+ ' -c -S ' + @serverName + ' -t ' + @splitChar + ' -U ' + @username + ' -P ' + @password
exec master..xp_cmdshell @strSql

Pls help me as soon as possible, thank you very much!

1 reply

by sjohnson175

Not sure how helpful this is but poking around SQL Server Books Online it seems to me you need to call the bcp utility with cmdshell.

bcp is specifically designed to offload data from a table to a file and vice versa.

This discussion is closed to new replies.