T-SQL
-
Can't I write a select statement that pulls data from two different tables even if the tables reside on two different dB's that are on the same server?
I'm using SQL2K without the service pack. I get the following error when I try:
Server: Msg 446, Level 16, State 9, Line 1
Cannot resolve collation conflict for equal to operation. Here is the T-SQL:
SELECT b.LastName, b.FirstName, a.ExpenseNo, a.EmployeeNo
FROM devExpense.dbo.ExpenseHeader AS a INNER JOIN devCorp.dbo.OrganizationEmployee AS b
ON a.EmployeeNo = b.EmployeeNo
where a.ExpenseNo = '1913-22-099-76441'
-
Sounds like the two databases are defined with different collations and the server can't decide what equals means between them.
Cursors never.
DTS - only when needed and never to control. -
You can probably do it with an exlicit collation convertion - something like
SELECT b.LastName, b.FirstName, a.ExpenseNo, a.EmployeeNo
FROM devExpense.dbo.ExpenseHeader AS a INNER JOIN devCorp.dbo.OrganizationEmployee AS b
ON a.EmployeeNo collate Latin1_General_BIN = b.EmployeeNo collate Latin1_General_BIN
Cursors never.
DTS - only when needed and never to control. -
Thank you nigelrivet. It works fantastic!! Many, many thanks and have a wonderful holiday...
-
Man... U save my work ! Thankz !!!
-
Since you got a collation error with the equals part of the JOIN, I'm wondering if SQL Server is thinking that was a date.
For example: If I want to check for a specific date/time I would do this:
WHERE mydatetime = '2004-09-10 12:00:00'
So it might be possible that SQL Server is thinking that you are looking for a date and time in the year 1913, but your format doesn't match any collation.
-SQLBill
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply