SQL Query - SQL Server Forums

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

Transact-SQL (2005)

SQL Query

Author

Topic

mshsilver
Posting Yak Master

112 Posts

Posted - 2009-11-09 : 05:55:43

Hi,

I am trying to resolve an issue wit a query that i use on one server for one client that doesn't work on another sever with the same table structure.

I'm selecting data from one table and where a duplicate edrs number is found only show it once and insert that row in another table and edrs as the primary key (uniquieid)

Like i say this works fine on another server but here i get this error:

Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'pk_wce_contact_uid'. Cannot insert duplicate key in object 'dbo.wce_contact'.
The statement has been terminated.

The select query brings me 193 rows and i have looked and cannot see a duplicated edrs and this should be impossible anyway. The only difference with this query to the one on the other server is i had to add "COLLATE database_default" to get the select part of the query working.

I dont see how i can set an example of tis as it does work and maybe it's server specific. If anyone can see anything obvious i would be very grateful. Thanks for looking

SQL Query:



with cteEmployers( EDRS, Employer_Name,  Emp_Phone, Emp_Address_1, Emp_Address_2, Emp_Address_3, Emp_Address_4, Emp_Address_Pcode1, Emp_Address_Pcode2, rown) as(Select  wce_ilr.EDRS, wce_ilr.Employer_Name,  wce_ilr.Emp_Phone, wce_ilr.Emp_Address_1, wce_ilr.Emp_Address_2, wce_ilr.Emp_Address_3, wce_ilr.Emp_Address_4, wce_ilr.Emp_Address_Pcode1, wce_ilr.Emp_Address_Pcode2, ROW_NUMBER() OVER (PARTITION BY wce_ilr.edrs ORDER BY wce_ilr.edrs) from  wce_ilr  left join wce_contact 
   on WCE_ILR.edrs =wce_contact.edrs COLLATE database_default
where WCE_contact.edrs is null)

INSERT INTO wce_contact (UNIQUEID, EDRS, COMPANY, PHONE, ADDRESS1, ADDRESS2, CITY, COUNTY, POSTALCODE, Record_Type)

Select edrs, edrs, Employer_Name, Emp_Phone, Emp_Address_1, Emp_Address_2, Emp_Address_3, Emp_Address_4, Emp_Address_Pcode1 + ' ' + Emp_Address_Pcode2 AS Postcode, 'Company' AS Record_Type  from cteEmployers  where (rown = 1 and not edrs is null) order by cteEmployers.edrs

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2009-11-09 : 12:39:31

As an investigating tool, try this:


with cteEmployers( EDRS, Employer_Name,  Emp_Phone, Emp_Address_1, Emp_Address_2, Emp_Address_3, Emp_Address_4, Emp_Address_Pcode1, Emp_Address_Pcode2, rown) as(Select  wce_ilr.EDRS, wce_ilr.Employer_Name,  wce_ilr.Emp_Phone, wce_ilr.Emp_Address_1, wce_ilr.Emp_Address_2, wce_ilr.Emp_Address_3, wce_ilr.Emp_Address_4, wce_ilr.Emp_Address_Pcode1, wce_ilr.Emp_Address_Pcode2, ROW_NUMBER() OVER (PARTITION BY wce_ilr.edrs ORDER BY wce_ilr.edrs) from  wce_ilr  left join wce_contact 
   on WCE_ILR.edrs =wce_contact.edrs COLLATE database_default
where WCE_contact.edrs is null)
SELECT *
from wce_contact
where edrs in (
  select edrs
  from cteEmployers
  )

=======================================
Few things are harder to put up with than the annoyance of a good example. (Mark Twain)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-11-09 : 12:41:26

why are inserting explicit values to uniqueid column? is it not an identity field?

mshsilver
Posting Yak Master

112 Posts

Posted - 2009-11-10 : 10:24:17

Hi Yes it is an identity field but this is what i have to do and providing tehre is no dups then it should be fine.

I ran teh above query and get the error, just trying to work out how to get rid of that:

Msg 468, Level 16, State 9, Line 1
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-11-10 : 12:59:55

try on both sides

on WCE_ILR.edrs COLLATE database_default =wce_contact.edrs COLLATE database_default

mshsilver
Posting Yak Master

112 Posts

Posted - 2009-11-20 : 08:46:56

I have tried COLLATE database_default and COLLATE Latin1_General_CI_AS but i must be doing something wrong as i keep getting the error:

Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

Here is my SQL, maybe someone can see clearly where i am going wrong.



with cteEmployers( EDRS, Employer_Name,  Emp_Phone, Emp_Address_1, Emp_Address_2, Emp_Address_3, Emp_Address_4, Emp_Address_Pcode1, Emp_Address_Pcode2, rown) as(Select  wce_ilr.EDRS, wce_ilr.Employer_Name,  wce_ilr.Emp_Phone, wce_ilr.Emp_Address_1, wce_ilr.Emp_Address_2, wce_ilr.Emp_Address_3, wce_ilr.Emp_Address_4, wce_ilr.Emp_Address_Pcode1, wce_ilr.Emp_Address_Pcode2, ROW_NUMBER() OVER (PARTITION BY wce_ilr.edrs ORDER BY wce_ilr.edrs) from  wce_ilr  left join wce_contact 
   on WCE_ILR.edrs =wce_contact.edrs COLLATE database_default
where WCE_contact.edrs is null)
SELECT *
from wce_contact
where edrs in (
  select edrs
  from cteEmployers
  )

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-11-22 : 01:29:09

did you try it on both sides?

on WCE_ILR.edrs COLLATE database_default =wce_contact.edrs COLLATE database_default

mshsilver
Posting Yak Master

112 Posts

Posted - 2009-11-24 : 04:23:27

I tried this, is this what you mean? If so i still get this error:

Msg 468, Level 16, State 9, Line 1
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation



with cteEmployers( EDRS, Employer_Name,  Emp_Phone, Emp_Address_1, Emp_Address_2, Emp_Address_3, Emp_Address_4, Emp_Address_Pcode1, Emp_Address_Pcode2, rown) as(Select  wce_ilr.EDRS, wce_ilr.Employer_Name,  wce_ilr.Emp_Phone, wce_ilr.Emp_Address_1, wce_ilr.Emp_Address_2, wce_ilr.Emp_Address_3, wce_ilr.Emp_Address_4, wce_ilr.Emp_Address_Pcode1, wce_ilr.Emp_Address_Pcode2, ROW_NUMBER() OVER (PARTITION BY wce_ilr.edrs ORDER BY wce_ilr.edrs) from  wce_ilr  left join wce_contact 
   on WCE_ILR.edrs COLLATE database_default =wce_contact.edrs COLLATE database_default
where WCE_contact.edrs is null)
SELECT *
from wce_contact
where edrs  in (
  select edrs
  from cteEmployers
  )

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources