• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 540
  • Last Modified:

T-SQL to CSV file

I have the following stored procedure I'm running but it only outputs on records.  When I manually execute " exec Ntier_DesertOrtho.dbo.CreateAppointmentFile " I get all the records I'm expecting to get 100+ ,  but when I manually execute the entire below I get an output of 0 reported but the file is actually created with 1 record in it.

THank you for your help ....


DECLARE @sql varchar(8000)
SELECT @sql = 'bcp "exec Ntier_DesertOrtho.dbo.CreateAppointmentFile" '
+ 'queryout e:\bmc\extract\DOAppointments.csv -c -t^| -T -S'
+ @@servername
EXEC master..xp_cmdshell @sql
0
jtbrown1111
Asked:
jtbrown1111
2 Solutions
 
x-menIT super heroCommented:
Use the SQL Server Import / Export wizard to get the data to the csv. Save the package for later execution.
0
 
ZberteocCommented:
What is t^|? If the separator is | you should have t|. If you use both characters wrap them in quotes: t"^|"
0
 
jtbrown1111Author Commented:
What I ended up doing which worked really well was to use the Import/Export Wizard, created a package using a query then created an SQL Agent job to run it nightly.

Thanks everyone for your help ...
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now