Thanks for getting back to me. I need the fixed format file because my
output goes to an autodialer that requires fixed length. Otherwise, the
import to the dialer (UNIX BOX) fails.
My original problem of getting the bcp to read the format file is
resolved (column coallations were wrong).
Thanks,
James
-----Original Message-----
From: Anwar Sonday
[mailto:mssqldba-ezmlmshield-x23101209.[Email address protected]
Sent: Wednesday, February 28, 2007 8:13 AM
To: LazyDBA Discussion
Subject: RE: [Spam] I am unable to bcp using fixed format file
Importance: Low
Yes, we also do this but without using a fmt file,
use a query and selcet the exact columns you need
eg
bcp "SELECT col1, col2, col3 FROM ##Data ORDER BY DataId" queryout
'c:\test.txt' -SDataBaseName -c -q -T -k
-----Original Message-----
From: Stephen Davey
[mailto:mssqldba-ezmlmshield-x51492098.[Email address protected]
Sent: Wednesday, February 28, 2007 3:04 PM
To: LazyDBA Discussion
Subject: RE: [Spam] I am unable to bcp using fixed format file
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
Disclaimer
http://www.shoprite.co.za/disclaimer.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. (FE1)
MS Sql Server LazyDBA home page