Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 Import/Export (DTS) and Replication (2000)
 DTS to dump data to text file - update - BCP?

Author  Topic 

Starting Member

25 Posts

Posted - 2004-04-19 : 10:56:32
Update - OK, I'm trying BCP instead...but I want the columns to be pipe-delimited and quoted...and I'm not a bcp guru. Right now I'm using the command:

set @cmd = 'bcp "SELECT * FROM test..activities" queryout \\server\testSQL\activities.txt -c -t"|" -Ssqlprod'
EXEC master..xp_cmdshell @cmd

...and that seems to work, except I can't get it double-quoted - help?

Original question:
I am trying to use a dts package to dump about 45 tables out to pipe-delimited text files...I can create the first one fine using the wizard, but I want all of them to be in one dts package. When I try to manually create the transformations, Ent Mgr barfs on me - says the memory could not be read - and exits. I can select the source, but the destination comes up empty - when I try to define the columns on the destination it gives me the memory error. After playing with this all morning, it appears to work ok on some tables - but not others! and I can't determine what the magic condition is (number/type of columns, etc.) Anybody have this happen to them? Any ideas?

Not Just a Number

15586 Posts

Posted - 2004-04-19 : 17:03:48
OK, stupid question time....(I'm very good at that)

Why do you want he quotes?

Did you try ""|""



Go to Top of Page

Starting Member

25 Posts

Posted - 2004-04-19 : 17:07:17
The person that was going to use the output txt files wanted them quoted...but after I whined, he now I'm just using the pipe, and it seems to be working ok for him. Thanks!
Go to Top of Page

Starting Member

1 Post

Posted - 2009-04-07 : 20:45:11
How can I insert field labels to the output text file?
Go to Top of Page

Starting Member

1 Post

Posted - 2009-06-11 : 15:10:10
just wanted to provide another example that works... thanks for all the posts.... (by the way, this reports on ssrs usage)

exec master..xp_cmdshell 'bcp "Select UserName, name as ReportName, timestart as ReportStart, timeend as ReportEnd, TimeRendering, TimeDataRetrieval, status as ReportStatus, c.[Name], Parameters From reportserver.dbo.ExecutionLog e inner join reportserver.dbo.Catalog c on e.reportid = c.ItemID where timestart > getdate()-30 order by timestart desc" queryout d:\perflogs\ssrsreport.txt -T -c -U sa -S server\instance'
Go to Top of Page

- Advertisement -