BCP Syntax

I am trying to use the BCP utility to copy a stored procedure out to a text
file.
1) Is there a way to include the field headers in the text file?
2) Is there a way to turn off the quotes between data?
So far, here is the syntax I have so far:
bcp "database..sp" queryout "c:\test.txt" -t, -Sserver -Uuser -Ppassword
Thank you,
JLFlemingOne option is to get the column names from the metadata & use UNION operator
to get a single resultset like:
SELECT -1 AS "sort_col",
MAX( CASE ORDINAL_POSITION WHEN 1 THEN col1 END ) AS "col1",
MAX( CASE ORDINAL_POSITION WHEN 2 THEN col2 END ) AS "col2",
..
MAX( CASE ORDINAL_POSITION WHEN n THEN coln END ) AS "coln"
FROM TABLE_NAME = 'tbl'
UNION
SELECT 0, col1, col2,... coln
FROM tbl ;
You can warp this into a view & BCP it out pretty easily, but watch out of
type mismatches with SYSNAME types. The above shows a general approach, but
you can avoid any reference to the meta-data by directly typing out your
column names like:
SELECT -1 AS "sort_col", "col1", "col2", ... "coln"
UNION
SELECT 0, col1, col2, ... coln
FROM tbl
ORDER BY "sort_col" ;
Another option is to create a ASCII file with the headers & then BCP out the
data to another file. Simply use the DOS COPY command like:
copy header.txt + body.txt data.txt
Anith

BCP queryout

Hello,
How can I include the columns titles in a bcp SELECT QUERYOUT command? (I
wish I could obtain the same result than in Query Analyzer)
Is there an other way to export the result of a select statement?
Best regardswhy don't use a view to bcp out!
Edwar Bishara
www.sqlcare.com
edwarb@.sqlcare.com|||BCP will not output the column headings. Only the data. You can get the
column headings by using command line osql with the -h parameter.
Rand
This posting is provided "as is" with no warranties and confers no rights.

BCP path problem

Why can't I include the path to the bcp utility when I use xp_cmdshell?
This works fine in the command window:
"C:\Program Files\Microsoft SQL Server\80\Tools\binn\bcp"
"ABData.dbo.tCMD_OutputToFile" out "c:\temp\abdata.txt" -c -U"chagus" -P"c"
but this don't work from within SQL Query Analyzer (or a stored procedure):
exec master..xp_cmdshell '"C:\Program Files\Microsoft SQL
Server\80\Tools\binn\bcp" "ABData.dbo.tCMD_OutputToFile" out
"c:\temp\abdata.txt" -c -U"chagus" -P"c"', no_output
Thanks in advance
/KarinIs the command window on your client PC or the SQL Server ...?
Default SQL Server install should add the path for BCP.EXE to the server
%PATH%. You shouldn't need to include it eg:-
EXEc master..xp_cmdshell "bcp ..." should suffice
HTH. Ryan
"Karin" <Karin@.discussions.microsoft.com> wrote in message
news:5637C112-37E6-4C4C-8A12-C9B3E63EE307@.microsoft.com...
> Why can't I include the path to the bcp utility when I use xp_cmdshell?
> This works fine in the command window:
> "C:\Program Files\Microsoft SQL Server\80\Tools\binn\bcp"
> "ABData.dbo.tCMD_OutputToFile" out
> "c:\temp\abdata.txt" -c -U"chagus" -P"c"
> but this don't work from within SQL Query Analyzer (or a stored
> procedure):
> exec master..xp_cmdshell '"C:\Program Files\Microsoft SQL
> Server\80\Tools\binn\bcp" "ABData.dbo.tCMD_OutputToFile" out
> "c:\temp\abdata.txt" -c -U"chagus" -P"c"', no_output
>
> Thanks in advance
> /Karin|||"Karin" <Karin@.discussions.microsoft.com> wrote in message
news:5637C112-37E6-4C4C-8A12-C9B3E63EE307@.microsoft.com...
> Why can't I include the path to the bcp utility when I use xp_cmdshell?
> This works fine in the command window:
> "C:\Program Files\Microsoft SQL Server\80\Tools\binn\bcp"
> "ABData.dbo.tCMD_OutputToFile" out
> "c:\temp\abdata.txt" -c -U"chagus" -P"c"
> but this don't work from within SQL Query Analyzer (or a stored
> procedure):
> exec master..xp_cmdshell '"C:\Program Files\Microsoft SQL
> Server\80\Tools\binn\bcp" "ABData.dbo.tCMD_OutputToFile" out
> "c:\temp\abdata.txt" -c -U"chagus" -P"c"', no_output
>
> Thanks in advance
> /Karin
What error are you getting?
When you xp_cmdshell, you are executing in the security context of either
the MSSQLServer service account (if you are an sa in the db), or in the
security account associated with the SQLExecutiveCmdExec if you are a
non-sa. Ensure that whichever account has the rights needed to execute in
that directory.
Rick Sawtell
MCT, MCSD, MCDBA|||I have both the client and server on my developer-PC so I run the usual
command window (Run cmd). The problem is that when I specify the path in
xp_cmdshell no file will be created and no error message appears anywhere,
I've looked in SQL Server's Log and Event Log. This is the only thing I get:
The command(s) completed successfully.
I know that I don't need to specify the path, but I have a customer which
probably have some other bcp installed (perhaps sybase I don't know) beause
she gets the following message:
CTLIB Message: - L6/O8/S5/N3/5/0:
ct_connect(): directory service layer: internal directory control layer
error: Requested server name not found.
Establishing connection failed.
I read that It should works even if you specify the path, so why doesn't it?
"Ryan" wrote:

> Is the command window on your client PC or the SQL Server ...?
> Default SQL Server install should add the path for BCP.EXE to the server
> %PATH%. You shouldn't need to include it eg:-
> EXEc master..xp_cmdshell "bcp ..." should suffice
> --
> HTH. Ryan
> "Karin" <Karin@.discussions.microsoft.com> wrote in message
> news:5637C112-37E6-4C4C-8A12-C9B3E63EE307@.microsoft.com...
>
>|||I don't get any error message, only: The command(s) completed successfully.
But no file creates in C:\temp
I am sa and administrator and SQL Server runs under local system account.
"Rick Sawtell" wrote:

> "Karin" <Karin@.discussions.microsoft.com> wrote in message
> news:5637C112-37E6-4C4C-8A12-C9B3E63EE307@.microsoft.com...
> What error are you getting?
> When you xp_cmdshell, you are executing in the security context of either
> the MSSQLServer service account (if you are an sa in the db), or in the
> security account associated with the SQLExecutiveCmdExec if you are a
> non-sa. Ensure that whichever account has the rights needed to execute i
n
> that directory.
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||Having read the error message and looked at your BCP cmd line i can't see
the -Sservername switch.
eg :- bcp ABData.dbo.tCMD_OutputToFile out
c:\temp\abdata.txt -c -Uchagus -Pc -Sservername
But if that were the case it wouldn't work on your dev machine either..
HTH. Ryan
"Karin" <Karin@.discussions.microsoft.com> wrote in message
news:32355673-659B-4BBA-B1D2-EB1228DF2980@.microsoft.com...
>I have both the client and server on my developer-PC so I run the usual
> command window (Run cmd). The problem is that when I specify the path in
> xp_cmdshell no file will be created and no error message appears anywhere,
> I've looked in SQL Server's Log and Event Log. This is the only thing I
> get:
> The command(s) completed successfully.
> I know that I don't need to specify the path, but I have a customer which
> probably have some other bcp installed (perhaps sybase I don't know)
> beause
> she gets the following message:
> CTLIB Message: - L6/O8/S5/N3/5/0:
> ct_connect(): directory service layer: internal directory control layer
> error: Requested server name not found.
> Establishing connection failed.
> I read that It should works even if you specify the path, so why doesn't
> it?
> "Ryan" wrote:
>|||No, the problem is that no file will be created when I include the path to
bcp.exe in xp_cmdshell command-string... All works fine until I include the
path.
"Ryan" wrote:

> Having read the error message and looked at your BCP cmd line i can't see
> the -Sservername switch.
> eg :- bcp ABData.dbo.tCMD_OutputToFile out
> c:\temp\abdata.txt -c -Uchagus -Pc -Sservername
> But if that were the case it wouldn't work on your dev machine either..
> --
> HTH. Ryan
> "Karin" <Karin@.discussions.microsoft.com> wrote in message
> news:32355673-659B-4BBA-B1D2-EB1228DF2980@.microsoft.com...
>
>|||I found the solution in SQL Server Books Online:
command_string cannot contain more than one set of double quotation marks. A
single pair of quotation marks is necessary if any spaces are present in the
file paths or program names referenced by command_string. If you have troubl
e
with embedded spaces, consider using FAT 8.3 file names as a workaround.
This doesn't work:
exec master..xp_cmdshell '"C:\Program Files\Microsoft SQL
Server\80\Tools\binn\bcp" "ABData.dbo.tCMD_OutputToFile" out
"c:\temp\authors.txt" -c -U"chagus" -P"c"', no_output
but this will:
exec master..xp_cmdshell '"C:\Program Files\Microsoft SQL
Server\80\Tools\binn\bcp" ABData.dbo.tCMD_OutputToFile out
c:\temp\authors.txt -c -Uchagus -Pc', no_output
:o)
So that means that the output file cannot be created in a path with spaces
in it.
:o(
/Karin
"Karin" wrote:
> No, the problem is that no file will be created when I include the path to
> bcp.exe in xp_cmdshell command-string... All works fine until I include th
e
> path.
> "Ryan" wrote:
>|||I'll make a note of that for future..
Thanks for posting your solution
HTH. Ryan
"Karin" <Karin@.discussions.microsoft.com> wrote in message
news:C0474E2C-9FAF-4E32-BB83-CF5DF46AF391@.microsoft.com...
>I found the solution in SQL Server Books Online:
> command_string cannot contain more than one set of double quotation marks.
> A
> single pair of quotation marks is necessary if any spaces are present in
> the
> file paths or program names referenced by command_string. If you have
> trouble
> with embedded spaces, consider using FAT 8.3 file names as a workaround.
> This doesn't work:
> exec master..xp_cmdshell '"C:\Program Files\Microsoft SQL
> Server\80\Tools\binn\bcp" "ABData.dbo.tCMD_OutputToFile" out
> "c:\temp\authors.txt" -c -U"chagus" -P"c"', no_output
> but this will:
> exec master..xp_cmdshell '"C:\Program Files\Microsoft SQL
> Server\80\Tools\binn\bcp" ABData.dbo.tCMD_OutputToFile out
> c:\temp\authors.txt -c -Uchagus -Pc', no_output
> :o)
> So that means that the output file cannot be created in a path with spaces
> in it.
> :o(
> /Karin
>
> "Karin" wrote:
>|||Karin (Karin@.discussions.microsoft.com) writes:
> I know that I don't need to specify the path, but I have a customer
> which probably have some other bcp installed (perhaps sybase I don't
> know) beause she gets the following message:
> CTLIB Message: - L6/O8/S5/N3/5/0:
> ct_connect(): directory service layer: internal directory control layer
> error: Requested server name not found.
> Establishing connection failed.
Yes, CTLIB is Sybase.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

bcp out file including all the columns names

How to bcp out file to also include all the table column names?

Thank you!

What is the purpose of you wanting to have the table column names inside the bcp file?

|||

Users ask for it. It was not my choice.

|||If you need to know the column names that exist for a given BCP file, then you can create a format file during your bcp out. For more information about format files, please see topic "Creating a Format File" in SQL Server 2005 Books Online.sql