Solved

T-SQL to CSV file

Posted on 2014-02-20
3
465 Views
Last Modified: 2014-02-21
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
Comment
Question by:jtbrown1111
3 Comments
 
LVL 18

Accepted Solution

by:
x-men earned 400 total points
ID: 39876341
Use the SQL Server Import / Export wizard to get the data to the csv. Save the package for later execution.
0
 
LVL 26

Assisted Solution

by:Zberteoc
Zberteoc earned 100 total points
ID: 39876495
What is t^|? If the separator is | you should have t|. If you use both characters wrap them in quotes: t"^|"
0
 

Author Comment

by:jtbrown1111
ID: 39878530
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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Query 2 34
SQL USE DATABASE VARIABLE 5 31
RAISERROR WITH NOWAIT 2 18
SQL - Rewrite statement 4 26
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question