bcp utility

I'm using the bcp utility to export data to a network file. Is there a way to
output the field names in addition to the data? According to Books Online,
there isn't a switch for this.
Hi Mike
Look at using the import/export wizard, DTS or SSIS to do this, you can do
it with BCP using something like:
BCP "SELECT au_id, au_lname, au_fname, phone, address, city, state, zip,
contract FROM ( SELECT CAST(au_id as varchar(30)) as au_id, au_lname,
au_fname, phone, address, city, state, zip, CAST(contract as varchar(30)) AS
contract, 1 AS orderby FROM PUBS..Authors UNION ALL SELECT 'au_id',
'au_lname', 'au_fname', 'phone', 'address', 'city', 'state', 'zip',
'contract', 0 ) A ORDER BY orderby" QUERYOUT authors.txt -c -T -S (local)
but it's a bit of a cludge!
John
"mike" wrote:

> I'm using the bcp utility to export data to a network file. Is there a way to
> output the field names in addition to the data? According to Books Online,
> there isn't a switch for this.
sql

bcp utility

I'm using the bcp utility to export data to a network file. Is there a way to
output the field names in addition to the data? According to Books Online,
there isn't a switch for this.Hi Mike
Look at using the import/export wizard, DTS or SSIS to do this, you can do
it with BCP using something like:
BCP "SELECT au_id, au_lname, au_fname, phone, address, city, state, zip,
contract FROM ( SELECT CAST(au_id as varchar(30)) as au_id, au_lname,
au_fname, phone, address, city, state, zip, CAST(contract as varchar(30)) AS
contract, 1 AS orderby FROM PUBS..Authors UNION ALL SELECT 'au_id',
'au_lname', 'au_fname', 'phone', 'address', 'city', 'state', 'zip',
'contract', 0 ) A ORDER BY orderby" QUERYOUT authors.txt -c -T -S (local)
but it's a bit of a cludge!
John
"mike" wrote:
> I'm using the bcp utility to export data to a network file. Is there a way to
> output the field names in addition to the data? According to Books Online,
> there isn't a switch for this.sql

bcp header

Is there any way of including headers when we export a table using bcp
? Can't find the argument in bcp utility books online ?

Thanks for the helpPatrik (patrik.maheux@.umontreal.ca) writes:
> Is there any way of including headers when we export a table using bcp
> ? Can't find the argument in bcp utility books online ?

BCP does not unfortunately not support headers very well. If they can
be molded into the format of the rest of the file, you can sneak by.

Here are some examples:

value1,value2,value3
12,23,234

This is easy, use the -F option to start on row 2. Here is a tricker one:

value1,value2,value3
"text",12,"more test"

For this format you need a format file anyway for the data. This format
file will specify that each record begins with an empty column with " as
terminator. The result is that the header slips into that empty column
for the first row. You should not use -F.

Here is a dead end:

value1,value2,value3
12,"my text",12

I can't find a way to bulk-load this file, because the header does not
match the format of the data rows, and you can hide it an empty column.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

bcp Format files help?

I am using bcp to execute a query and would like to use the -f option for format_file so I am going thourgh the books on line and trying to make some sense out of it,no quite clear on it at the moment.

How do I find out the version of bcp utility and would somebody have an example with this options just to see how it works.

ThanksFormat files? Jeez, it's been a while...

As I recall there is an option you can include that will create a format file for you. Run the BCP once with this option and then edit the file it creates. That's the LAZY DBA way...|||You can "cook your own" sample using something like:bcp master.dbo.sysdatabases format sd.dat -Usa -c -SC1126582-B -fsd.fmtThis will create a format file that is properly formatted for the source table, using conventional character output.

A lazy dba is a good dba!

-PatP|||What the hell is -B?|||What the hell is -B?Part of the workstation name, not a parameter!

-PatP

Batch Size

Hi
I'm trying to improve a crawl's performance. Online books refers to
changing the default batch size from 1600 to a number associated with the
number of processors on my server.
For the life of me, I have no idea how to go about changing the batch size
for full-text search. Any and all pointers apprciated.
Rob
Adding to my post. I thought this was a 2005 specific newsgroup - sorry.
I'm running sql2005 Sept. CTP Standard Edition. on a 4 way box with
hyperthreading. fast XEON procesors (don't exactly remember how fast).
Table is 4000000 rows and growing, and taking a LONG time (8 hours) to
populate. I've done the other hardware things suggested by books online.
Thanks.
"Robert G." wrote:

> Hi
> I'm trying to improve a crawl's performance. Online books refers to
> changing the default batch size from 1600 to a number associated with the
> number of processors on my server.
> For the life of me, I have no idea how to go about changing the batch size
> for full-text search. Any and all pointers apprciated.
> Rob
|||Robert,
Not to worry, the private "SQL Server 2005" newsgroup has been taken down as
SQL Server 2005 RTM'ed today (10/27/05), so now this fulltext newsgroup is
officially both SQL Server 2000 FTS and SQL Server 2005 FTS (SQL2005FTS),
checkout my blog entry from today for some of the initial details: "SQL
Server 2005 has RTM'ed !!"
http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!552.entry
You're reading the SQL 2005 BOL title "Performance Tuning and Optimization
(Full-Text Search)"
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/fulltxt9/html/ef39ef1f-f0b7-4582-8e9c-31d4bd0ad35d.htm
Can I assume that you have already done the following?
1. Ensure the base table has a clustered index.
2. Place SQL [database] log (*.ldf), database files (*.mdf & *.ndf), and the
full-text catalog on separate disks.
Have you reviewed the MSFTESQL<$Instance_Name>:Service "Batches in ready
queue" perfmon counter values? I'm not sure what constitutes "low" for your
server, but could you reply back with the range of values you are seeing now
while the FT Indexing is ongoing?
FYI, the explain for this performance counter: "Number of batches in the
ready queue. This queue buffers work that will be given to the filter
daemons."
Thanks,
John
SQL 2005 Full Text Search
http://spaces.msn.com/members/jtkane/
"Robert G." <RobertG@.discussions.microsoft.com> wrote in message
news:E854B496-382F-494F-B198-2F283E83D907@.microsoft.com...[vbcol=seagreen]
> Adding to my post. I thought this was a 2005 specific newsgroup - sorry.
> I'm running sql2005 Sept. CTP Standard Edition. on a 4 way box with
> hyperthreading. fast XEON procesors (don't exactly remember how fast).
> Table is 4000000 rows and growing, and taking a LONG time (8 hours) to
> populate. I've done the other hardware things suggested by books online.
> Thanks.
> "Robert G." wrote:
|||Robert,
Not to worry, the private "SQL Server 2005" newsgroup has been taken down as
SQL Server 2005 RTM'ed today (10/27/05), so now this fulltext newsgroup is
officially both SQL Server 2000 FTS and SQL Server 2005 FTS (SQL2005FTS),
checkout my blog entry from today for some of the initial details: "SQL
Server 2005 has RTM'ed !!"
http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!552.entry
You're reading the SQL 2005 BOL title "Performance Tuning and Optimization
(Full-Text Search)"
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/fulltxt9/html/ef39ef1f-f0b7-4582-8e9c-31d4bd0ad35d.htm
Can I assume that you have already done the following?
1. Ensure the base table has a clustered index.
2. Place SQL [database] log (*.ldf), database files (*.mdf & *.ndf), and the
full-text catalog on separate disks.
Have you reviewed the MSFTESQL<$Instance_Name>:Service "Batches in ready
queue" perfmon counter values? I'm not sure what constitutes "low" for your
server, but could you reply back with the range of values you are seeing now
while the FT Indexing is ongoing?
FYI, the explain for this performance counter: "Number of batches in the
ready queue. This queue buffers work that will be given to the filter
daemons."
Thanks,
John
SQL 2005 Full Text Search
http://spaces.msn.com/members/jtkane/
"Robert G." <RobertG@.discussions.microsoft.com> wrote in message
news:E854B496-382F-494F-B198-2F283E83D907@.microsoft.com...[vbcol=seagreen]
> Adding to my post. I thought this was a 2005 specific newsgroup - sorry.
> I'm running sql2005 Sept. CTP Standard Edition. on a 4 way box with
> hyperthreading. fast XEON procesors (don't exactly remember how fast).
> Table is 4000000 rows and growing, and taking a LONG time (8 hours) to
> populate. I've done the other hardware things suggested by books online.
> Thanks.
> "Robert G." wrote:
|||Hi
Your assumption is correct. Clustered index, files spread over 3 logical
disks (and two channels, for good measure).
Low is 1. According to that article, I should be seeing batches in the 4 -
8 range (maybe 16, since SQL believes I have 8 processors (hyperthreading)).
Low CPU is 0 - 5%, sometimes peaking at 30%, but rarely.
The article states "if the number of batches is low ... Increase full-text
batch size". That's what I'm trying to figure out.
They even give a suggested range - "default is 1600 rows per batch. For an
8-way computer 700Mhz CPU, the batch size recommend is 5000 rows."
Sounds like a great configuration change, if I could figure out how to
change the configuration!
Thanks again.
Rob
to quote the article:
"John Kane" wrote:

> Robert,
> Not to worry, the private "SQL Server 2005" newsgroup has been taken down as
> SQL Server 2005 RTM'ed today (10/27/05), so now this fulltext newsgroup is
> officially both SQL Server 2000 FTS and SQL Server 2005 FTS (SQL2005FTS),
> checkout my blog entry from today for some of the initial details: "SQL
> Server 2005 has RTM'ed !!"
> http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!552.entry
> You're reading the SQL 2005 BOL title "Performance Tuning and Optimization
> (Full-Text Search)"
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/fulltxt9/html/ef39ef1f-f0b7-4582-8e9c-31d4bd0ad35d.htm
> Can I assume that you have already done the following?
> 1. Ensure the base table has a clustered index.
> 2. Place SQL [database] log (*.ldf), database files (*.mdf & *.ndf), and the
> full-text catalog on separate disks.
> Have you reviewed the MSFTESQL<$Instance_Name>:Service "Batches in ready
> queue" perfmon counter values? I'm not sure what constitutes "low" for your
> server, but could you reply back with the range of values you are seeing now
> while the FT Indexing is ongoing?
> FYI, the explain for this performance counter: "Number of batches in the
> ready queue. This queue buffers work that will be given to the filter
> daemons."
> Thanks,
> John
> --
> SQL 2005 Full Text Search
> http://spaces.msn.com/members/jtkane/
>
> "Robert G." <RobertG@.discussions.microsoft.com> wrote in message
> news:E854B496-382F-494F-B198-2F283E83D907@.microsoft.com...
>
>