SQL Server Performance

Cannot resolve collation conflict for equal to OP.

Discussion in 'SQL Server 2005 General Developer Questions' started by rupeshjpatel, May 30, 2007.

  1. rupeshjpatel New Member

    Hi,
    I am executing following script and getting error,

    Script

    insert into purged_closed_asset select * from closed_asset where contract_id in (select contract_id from contract_list)

    Error:

    Cannot resolve collation conflict for equal to operation.

    What should i do?

    Plz respond.

    Thanks.

    Rupesh Patel
  2. rklimes New Member

    check if collation is same on both contract_id and contract_list. if it is not then the following should work.

    insert into purged_closed_asset select * from closed_asset where contract_id COLLATE <collation of contract_list> in (select contract_id from contract_list)

  3. rupeshjpatel New Member

    Thanks rklimes,<br />That previous issue was solved by modifying collation for the new column.<br /><br />Now i am also getting similar error for the bulk insert operation..<br /><br />Here is the Table Structure:<br /><br /><font color="blue">CREATE TABLE [dbo].[loan_fees](<br />[INP_SOURCE_CODE] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,<br />[pid_deal] [char](<img src='/community/emoticons/emotion-11.gif' alt='8)' /> COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,<br />[pid_facility] [char](<img src='/community/emoticons/emotion-11.gif' alt='8)' /> COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,<br />[accel_fasb] [numeric](28, 3) NOT NULL,<br />[accom_ammend_fees] [numeric](28, 3) NOT NULL,<br />[accrued_admin_fees] [numeric](28, 3) NOT NULL,<br />[accrued_commit_fees] [numeric](28, 3) NOT NULL,<br />[accrued_lc_fees] [numeric](28, 3) NOT NULL,<br />[accrued_unused_fees] [numeric](28, 3) NOT NULL,<br />[advisory_fees] [numeric](28, 3) NOT NULL,<br />[amortized_admin_fees] [numeric](28, 3) NOT NULL,<br />[collat_mgmt_fees] [numeric](28, 3) NOT NULL,<br />[discount_amount_fees] [numeric](28, 3) NOT NULL,<br />[event_fees] [numeric](28, 3) NOT NULL,<br />[MTD_SYNDICATION_FEES] [numeric](28, 3) NOT NULL CONSTRAINT [def_loan_fees_synd_fees] DEFAULT (0),<br /> CONSTRAINT [PK_loan_fees] PRIMARY KEY CLUSTERED <br />(<br />[pid_deal] ASC,<br />[pid_facility] ASC<br />) ON [PRIMARY]<br /></font id="blue"><br />and Here is the format File<br /><br /><font color="brown">8.0<br />15<br />1SQLCHAR01" "1inp_source_code""<br />2SQLCHAR08" "2pid_deal ""<br />3SQLCHAR08" "3pid_facility ""<br />4SQLCHAR013" "4accel_fasb ""<br />5SQLCHAR013" "5accom_ammend_fees ""<br />6SQLCHAR013" "6accrued_admin_fees ""<br />7SQLCHAR013" "7accrued_commit_fees ""<br />8SQLCHAR013" "8accrued_lc_fees ""<br />9SQLCHAR013" "9accrued_unused_fees ""<br />10SQLCHAR013" "10advisory_fees ""<br />11SQLCHAR013" "11amortized_admin_fees ""<br />12SQLCHAR013" "12collat_mgmt_fees ""<br />13SQLCHAR013" "13discount_amount_fees ""<br />14SQLCHAR013" "14event_fees ""<br />15SQLCHAR013"
    "15mtd_syndication_fees ""</font id="brown"><br /><br />Now when i run bulk insert operation on this, i am getting following error;<br /><font color="red"><br />Cannot perform bulk insert. Invalid collation name for source column 15 in format file 'J:feedsildump_cefinliq_loan_fees.fmt'.)</font id="red"><br /><br />What to do?<br /><br /><br />Rupesh Patel
  4. satya Moderator

    How did you generate the data and the format file?

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  5. rupeshjpatel New Member

    Satya,
    I generated Format File in Notepad, and I received Source file from the business in Text Format.

    Thanks.

    Rupesh Patel
  6. satya Moderator

    Ha, is this installation case-sensitive?
    If so then table schema refers {[MTD_SYNDICATION_FEES] [numeric](28, 3) } in caps and your format file refes in small letter, may try changing either way.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  7. rupeshjpatel New Member

    Thanks to all of you,<br /><br />This problem is solved,.. DBA didn't update format file that i sent him, that is why it was failing since last two days.. <img src='/community/emoticons/emotion-1.gif' alt=':)' />)<br /><br />thanks for all of your replies..<br /><br />Rupesh<br /><br />Rupesh Patel
  8. rupeshjpatel New Member

    But, I have this another issue related to BULK INSERT.

    I have this control data file and format file.

    Control File:
    contract1911
    customer701
    lookup264
    deal620
    deal_admin616
    deal_borrower711
    facility1296
    facility_admin1117
    fac_factype1295
    mis_code491
    data_availability1
    closed_contract0
    loan_fees1039

    Control Format File:

    8.0
    4
    1SYBCHAR015" "1table_name""
    2SYBCHAR025" "2num_of_rows""
    3SYBCHAR026" "0dummy1""
    4SYBCHAR016"
    "0dummy2""


    Here matter is, We don't have third and fourth column in the Control data file!!!.

    should this bulk insert run properly or will fail?? for me its failing!!!

    Please reply.

    Thanks.

    Rupesh Patel
  9. rupeshjpatel New Member

    and I am getting following error for this.

    Bulk insert data conversion error (type mismatch) for row 1, column 2 (num_of_rows). [SQLSTATE 42000] (Error 4864)



    Rupesh Patel
  10. rupeshjpatel New Member

    yaaaro....
    any solution for this???

    Thanks..

    -R

    Rupesh Patel
  11. MohammedU New Member

    Is data file has the column names? if yes then use FIRSTROW =2 by default it is 1.

    Is there any reason using format file?


    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.

Share This Page