SQL Server Performance Forum – Threads Archive
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
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.
Satya,
I generated Format File in Notepad, and I received Source file from the business in Text Format. Thanks. Rupesh Patel
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.
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.
]]>