Link to home
Start Free TrialLog in
Avatar of Joel Brown
Joel BrownFlag for United States of America

asked on

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
ASKER CERTIFIED SOLUTION
Avatar of x-men
x-men
Flag of Portugal image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Avatar of Zberteoc
Zberteoc
Flag of Canada image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Joel Brown
Joel Brown
Flag of United States of America image

ASKER

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 ...