Hi Stephen,
I definitely will use this once I get it to work. I now am able to get
the bcp utility to read the fixed format but it is not formatting
correctly. For some reason it is truncating spaces. I need to RETAIN
the SPACES.
COMMENT: Here is the bcp.fmt file I am using:
8.0
3
1 SQLCHAR 0 100 " " 1 COACCTNAME
SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 50 " " 2 FIRST_NAME
SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 50 " " 3 LAST_NAME
SQL_Latin1_General_CP1_CI_AS
COMMENT: The output is wrong I want to keep the spaces (see below)
COMMENT: This is output (no good doesn't retain spaces)
A B Plumbing & Heating Alfred Bornstein
COMMENT: This is how I want the output:
A B Plumbing &
Heating01234567890123456789012345678901234567890123456789012345678901234
5678901234567ALFRED0123456789012345678901234567890123456789012345BORNSTE
IN
COMMENT:
I don't really want numbers , I want to retain the space. Under stand?
-----Original Message-----
From: Stephen Davey
[mailto:mssqldba-ezmlmshield-x51492098.[Email address protected]
Sent: Wednesday, February 28, 2007 8:04 AM
To: LazyDBA Discussion
Subject: RE: [Spam] I am unable to bcp using fixed format file
Importance: Low
James,
We regularly BCP a large history table to a flat file using the
following command structure:
bcp dbname.dbo.tblName_h out
"\\share_nas\h_arch\Script\Save\tblName_h_ccyymmdd.bcp" -m 30 -e
"\\share_nas\h_arch\Script\Log\err_out_mbrrcd_h_ccyymmdd.txt" -b
150000 -n -o
"\\share_nas\h_arch\Script\Log\log_out_mbrrcd_h_ccyymmdd.txt" -a
61440 -S servername -T
It works very well.
S. Davey (2/28)
MI DIT\Agency Services
7:30 A.M. - 4:00 P.M. ET
517.335.4237 (M- F CCC )
>>> "Avery James "
<mssqldba-ezmlmshield-x82925289.[Email address protected] 02/27/07
9:37 PM >>>
Anyone ever used bcp out with a format file?
-----Original Message-----
From: Avery James
[mailto:mssqldba-ezmlmshield-x34010941.[Email address protected]
Sent: Tuesday, February 27, 2007 2:23 PM
To: LazyDBA Discussion
Subject: [Spam] I am unable to bcp using fixed format file
Importance: Low
This is the command:
declare @sql varchar(8000)
select @sql = 'bcp DataDiscovery..bcptst1 out
\\maymowk020\bcp\bcptst1.txt -f\\maymowk020\bcp\bcptst1.fmt
-UExtract_USER -[Email Address Removed] master..xp_cmdshell @sql
This is the bcptst1.fmt
8.0
1
1 SQLCHAR 0 100 "\n "1 COACCTNAME SQL_Latin1_General_CP1_C1_AS
This is the Output
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]I/O error while reading BCP
format file
NULL
NOTICE:
This message may contain privileged or otherwise confidential
information. If you are not the intended recipient, please immediately
advise the sender by reply email and delete the message and any
attachments without using, copying or disclosing the contents. (FE01)
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
NOTICE:
This message may contain privileged or otherwise confidential
information. If you are not the intended recipient, please immediately
advise the sender by reply email and delete the message and any
attachments without using, copying or disclosing the contents. (FE1)
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
NOTICE:
This message may contain privileged or otherwise confidential information. If you are not the intended recipient, please immediately advise the sender by reply email and delete the message and any attachments without using, copying or disclosing the contents. (FE01)
MS Sql Server LazyDBA home page