Join tables in two databases??? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2000 Forums

SQL Server Development (2000)

Join tables in two databases???

Author

Topic

adz2006
Starting Member

3 Posts

Posted - 2006-10-23 : 06:29:51

I am trying to query tables in two databases. They are both on the same sever and I basicly want to delete data in one table that does not exist in another. Heres what I have tried but keep getting an annoying collation error and don't know why!

DELETE FROM rock_webdb.dbo.web_ecust
SELECT
rock_webdb.dbo.web_ecust.customer,
rock_webdb.dbo.web_ecust.postcode,
rock_webdb.dbo.web_ecust.last_updated,
rock_webdb.dbo.web_ecust.dt_created

FROM rock_webdb.dbo.web_ecust

LEFT JOIN eluciddb.dbo.cust ON
rock_webdb.dbo.web_ecust.customer = eluciddb.dbo.cust.customer
WHERE rock_webdb.dbo.web_ecust.customer IS NULL;

this should join the table 'cust' in the 'eluciddb database' to the 'web_ecust' table in the 'rock_webdb' database and delete the rows in the rock_webdb.web_ecust that do not exist in the other one...

what am i doing wrong???

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-23 : 06:37:45

Take a backup first, and then try

DELETE
FROM		rock_webdb.dbo.web_ecust
LEFT JOIN	eluciddb.dbo.cust ON eluciddb.dbo.cust.customer = rock_webdb.dbo.web_ecust.customer
WHERE		eluciddb.dbo.cust.customer IS NULL

Peter Larsson
Helsingborg, Sweden

adz2006
Starting Member

3 Posts

Posted - 2006-10-23 : 06:40:01

quote:
Originally posted by Peso

Take a backup first, and then try
DELETE
FROM rock_webdb.dbo.web_ecust
LEFT JOIN eluciddb.dbo.cust ON eluciddb.dbo.cust.customer = rock_webdb.dbo.web_ecust.customer
WHERE eluciddb.dbo.cust.customer IS NULL

Peter Larsson
Helsingborg, Sweden

Server: Msg 446, Level 16, State 9, Line 1
Cannot resolve collation conflict for equal to operation.

Thanks for trying!

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2006-10-23 : 06:42:41

You can resolve the collation conflict....by putting the "collation" keyword on one or both sides of the "="....search here for examples of same.

try it with a pure "select" statement 1st to get thit working and then upgrade to the delete.
Again...use "begin transaction/commit transaction" to allow you to rollback if a mistake is made!!

adz2006
Starting Member

3 Posts

Posted - 2006-10-23 : 07:05:14

quote:
Originally posted by AndrewMurphy

You can resolve the collation conflict....by putting the "collation" keyword on one or both sides of the "="....search here for examples of same.

try it with a pure "select" statement 1st to get thit working and then upgrade to the delete.
Again...use "begin transaction/commit transaction" to allow you to rollback if a mistake is made!!

Still no go, i am going to have to give up!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-23 : 07:09:30

[code]DELETE
FROM rock_webdb.dbo.web_ecust
LEFT JOIN eluciddb.dbo.cust ON eluciddb.dbo.cust.customer COLLATE Finnish_Swedish_CI_AS = rock_webdb.dbo.web_ecust.customer COLLATE Finnish_Swedish_CI_AS
WHERE eluciddb.dbo.cust.customer IS NULL[/code]

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources