Problems with Fixed width Export using BCP

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Other SQL Server Topics (2005)

Problems with Fixed width Export using BCP

Author

Topic

Renee
Starting Member

2 Posts

Posted - 2010-04-19 : 12:50:46

I tried to use a xml format file that I created to export data to a fixed width file.

The command used is:
bcp "select dossid from sagicorcapsil.dbo.RepublicBankList where valid=''N''" queryout c:\republicbank\exceptionNation.txt -f c:\republicbank\republiclist2.xml

(omitted the server info for security reasons)

<?xml version="1.0" ?>
- <BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
- <RECORD>
<FIELD ID="1" xsi:type="CharFixed" LENGTH="9" />
<FIELD ID="2" xsi:type="CharFixed" LENGTH="21" />
<FIELD ID="3" xsi:type="CharFixed" LENGTH="21" />
<FIELD ID="4" xsi:type="CharFixed" LENGTH="30" />
<FIELD ID="5" xsi:type="CharFixed" LENGTH="20" />
<FIELD ID="6" xsi:type="CharFixed" LENGTH="12" />
<FIELD ID="7" xsi:type="CharFixed" LENGTH="12" />
<FIELD ID="8" xsi:type="CharFixed" LENGTH="12" />
<FIELD ID="9" xsi:type="CharFixed" LENGTH="12" />
<FIELD ID="10" xsi:type="CharFixed" LENGTH="11" />
<FIELD ID="11" xsi:type="CharFixed" LENGTH="2" />
<FIELD ID="12" xsi:type="CharFixed" LENGTH="9" />
<FIELD ID="13" xsi:type="CharFixed" LENGTH="56" />
<FIELD ID="14" xsi:type="CharFixed" LENGTH="11" />
<FIELD ID="15" xsi:type="CharTerm" TERMINATOR="\r\n" />
</RECORD>
- <ROW>
<COLUMN SOURCE="1" NAME="dossid" xsi:type="SQLVARYCHAR" />
<COLUMN SOURCE="2" NAME="secrefno" xsi:type="SQLVARYCHAR" />
<COLUMN SOURCE="3" NAME="policyno" xsi:type="SQLVARYCHAR" />
<COLUMN SOURCE="4" NAME="phsname" xsi:type="SQLVARYCHAR" />
<COLUMN SOURCE="5" NAME="phlname" xsi:type="SQLVARYCHAR" />
<COLUMN SOURCE="6" NAME="faceval" xsi:type="SQLVARYCHAR" />
<COLUMN SOURCE="7" NAME="cashval" xsi:type="SQLVARYCHAR" />
<COLUMN SOURCE="8" NAME="polamt" xsi:type="SQLVARYCHAR" />
<COLUMN SOURCE="9" NAME="aplamt" xsi:type="SQLVARYCHAR" />
<COLUMN SOURCE="10" NAME="paidtodate" xsi:type="SQLVARYCHAR" />
<COLUMN SOURCE="11" NAME="status" xsi:type="SQLVARYCHAR" />
<COLUMN SOURCE="12" NAME="currentdate" xsi:type="SQLVARYCHAR" />
<COLUMN SOURCE="13" NAME="combinedfields" xsi:type="SQLVARYCHAR" />
<COLUMN SOURCE="14" NAME="fundval" xsi:type="SQLVARYCHAR" />
</ROW>
</BCPFORMAT>

I used this same file when I imported the text file however I get the error below when I use it for export.

SQLState = S1000, NativeError = 0
Error = [Microsoft][SQL Native Client]Host-file columns may be skipped only when copying into the Server
NULL

HELP

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-20 : 15:19:33

I believe the error comes up because you are only selecting the column dossid in your select statement while the format file needs secrefno,policyno and so on.

No, you're never too old to Yak'n'Roll if you're too young to die.

Renee
Starting Member

2 Posts

Posted - 2010-04-21 : 08:59:37

That select statement was the wrong on that I included. I was testing something else with that statement.

The correct statement is

bcp "select dossid, secrefno,policyno,phsname,phfname,faceval,cashval,polamt,aplamt,paidtodate,status,currentdate,combinedfields,fundval from sagicorcapsil.dbo.RepublicBankList where valid=''N''" queryout c:\republicbank\exceptionNation.txt -f c:\republicbank\republiclist2.xml'

The same issue still applies.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources