Cannot resolve collation conflict for equal to OP.

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

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)

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

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

Thanks.

Rupesh Patel

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

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

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

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

Thanks..

-R

Rupesh Patel

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.