Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

COLLATION Between two databases, checking on which collate to use Expand / Collapse
Author
Message
Posted Thursday, May 2, 2013 2:10 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, July 31, 2014 7:04 PM
Points: 724, Visits: 1,002
DATABASE A SQL_Latin1_General_CP850_BIN
DATABASE B SQL_Latin1_General_CP1_CI_AS
TEMPDB SQL_Latin1_General_CP1_CI_AS

UPDATE DATABASEA.TABLE1
SET XXDT = A.COMPLT_DT -1
FROM DATABASEB.TABLE2 A
INNER JOIN DATABASEA.TABLE1 B ON A.ID = B.ID COLLATE Latin1_General_CI_AS
WHERE A.ID = B.ID COLLATE Latin1_General_CI_AS AND ISDATE(A.SYS_DT) = '1'
AND A.DATECOM= @DateCom

If i am writing back to a database table that is set to 850, does the check need to be COLLATE Latin1_General_CP850_BIN ?

How do we create temp tables with collation 850?

CREATE TABLE ##TS_ACCT (
[TS_ID] [nvarchar](100) NULL

)
Post #1448972
Posted Thursday, May 2, 2013 8:58 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, October 13, 2014 8:08 PM
Points: 4,573, Visits: 8,353
You may use COLLATE DATABASE_DEFAULT for columns coming from other databases.
Unless some tables in the database have not DB default collation.

Same for temp tables:

CREATE TABLE ##TS_ACCT (
[TS_ID] [nvarchar](100) COLLATE DATABASE_DEFAULT NULL
)

Post #1449032
Posted Friday, May 3, 2013 7:13 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, July 31, 2014 7:04 PM
Points: 724, Visits: 1,002
Would the temp table not be collate at 850 so i dont have to change my update code aswell.
Post #1449183
Posted Friday, May 3, 2013 7:14 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, July 31, 2014 7:04 PM
Points: 724, Visits: 1,002
All other tables are the defaults, not set to 850
Post #1449186
Posted Sunday, May 5, 2013 6:30 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, October 13, 2014 8:08 PM
Points: 4,573, Visits: 8,353
TRACEY-320982 (5/3/2013)
Would the temp table not be collate at 850 so i dont have to change my update code aswell.


If 850 is the default collation for the datanase then the columns in #Temp using DATABASE_DEFAULT collation will be obviously in 850.
Post #1449553
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse