Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2003
    Posts
    3

    Unanswered: ERROR : Host-file columns may be skipped only when copying into the Server

    Hi All,

    I need to make a query from the SQL Server 2K and save this data as an XML file. What i am trying to do is to execute a bcp utility as follows:

    EXEC master..xp_cmdshell 'bcp "SELECT CustID,CustName,CustSurname,CustEmail FROM myOwenDB..T_Customers FOR XML RAW" queryout "c:\customers.xml" -fc:\bcp.fmt -Sservername -Usa -Ppwd -C RAW -r -t'

    with bcp.fmt file formatted as

    8.0
    4
    1 SQLCHAR 0 9 "\t" 1 CustID Turkish_CI_AS
    2 SQLCHAR 0 100 "\t" 2 CustName Turkish_CI_AS
    3 SQLCHAR 0 100 "\t" 3 CustSurname Turkish_CI_AS
    4 SQLCHAR 0 100 "\t" 4 CustEmail Turkish_CI_AS

    The error is as follows:

    Error = [Microsoft][ODBC SQL Server Driver]Host-file columns may be skipped only when copying into the Server

    Does anyone have an idea about the problem? Also other techniques to generate XML file are also welcomed

    Thanks in advance,

    Bahtiyar KARANLIK

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    Your select statment only returns one column and you are using a format file that says bcp will be receiving four columns from the select statment.
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    Aug 2003
    Posts
    3
    Hi,
    How can my SQL statement can return only one column? When i execute the statement via Query Analyzer it displays the correct information?

    Am i missing a point??

    Bahtiyar KARANLIK

  4. #4
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    If you execute
    Code:
    SELECT CustID,CustName,CustSurname,CustEmail FROM myOwenDB..T_Customers
    In QA you will get four columns, CustID, CustName,CustSurname and CustEmail.

    If you execute
    Code:
    SELECT CustID,CustName,CustSurname,CustEmail FROM myOwenDB..T_Customers FOR XML RAW
    In QA you will have a resultset with one column. Within the one column you will have an XML string that contains the four above mentioned columns, but they will be contained in one column!


    If you execute
    Code:
    select au_id,au_lname,phone,contract from pubs.dbo.authors
    
    you should get something like:
    
    au_id       au_lname                                 phone        contract 
    ----------- ---------------------------------------- ------------ -------- 
    172-32-1176 White                                    408 496-7223 1
    If you execute
    Code:
    select au_id,au_lname,phone,contract from pubsdbo.authors for xml raw
    
    you should get something like:
    
    XML_F52E2B61-18A1-11d1-B105-00805F49916B                                                                                                                                                                                                                         
    -----------------------------------------------------------------------------
    <row au_id="172-32-1176" au_lname="White" phone="408 496-7223" contract="1"/>
    The first has four colums in the result set the second has only one.
    Paul Young
    (Knowledge is power! Get some!)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •