InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 18 May 2013 02:51:56 GMT20http://www.sqlservercentral.com/Forums/Topic579177-149-1.aspx[quote][b]SQLbeginner (10/7/2008)[/b][hr]where specifically in the query should i use the Collate function?I already have it in the middle part of the query.[/quote]Anywhere you're comparing or joining between the 2 databases. Think of it like converting 1 collation to the other. So if DB_A is Collation_A, and DB_B is Collation_B then your code will be:select DB_A.dbo.TableA, DB_B.dbo.TableBfrom DB_A.dbo.tableA TbA join DB_B.dbo.tableB TbB on TbA.FldA collate Collation_B = TbB.FldBThat converts FldA to B's collationTue, 07 Oct 2008 13:37:07 GMThomebrew01http://www.sqlservercentral.com/Forums/Topic579177-149-1.aspxwhere specifically in the query should i use the Collate function?I already have it in the middle part of the query.Tue, 07 Oct 2008 09:44:08 GMTSQLbeginnerhttp://www.sqlservercentral.com/Forums/Topic579177-149-1.aspxTry COLLATE function in your query,Tue, 07 Oct 2008 09:41:04 GMTnaryhttp://www.sqlservercentral.com/Forums/Topic579177-149-1.aspxThere are two databases called OCC and OCC_ARCHIVES. OCC has the collation setting Latin1_General_CI_AS- tables MCommOpenOrders, tbl_R15OrdersToday and Zones are under this databaseOCC_ARCHIVES has the collation setting Latin1_General_CI_AI- table tbl_R15_analysis falls under this databaseTue, 07 Oct 2008 09:21:37 GMTSQLbeginnerhttp://www.sqlservercentral.com/Forums/Topic579177-149-1.aspxWhat are the collation settings for all the databases involved ?Fri, 03 Oct 2008 13:42:38 GMThomebrew01http://www.sqlservercentral.com/Forums/Topic579177-149-1.aspxSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [dbo].[ZONES]( [ZoneProfile] [varchar](20) COLLATE Latin1_General_CI_AS NULL, [Dispatchareas] [varchar](20) COLLATE Latin1_General_CI_AS NULL, [Dispatchregions] [varchar](20) COLLATE Latin1_General_CI_AS NULL, [Sector] [varchar](20) COLLATE Latin1_General_CI_AS NULL, [TierC_Group] [varchar](10) COLLATE Latin1_General_CI_AS NULL) ON [PRIMARY]GOSET ANSI_PADDING OFFFri, 03 Oct 2008 13:17:03 GMTSQLbeginnerhttp://www.sqlservercentral.com/Forums/Topic579177-149-1.aspxWhat about "zones" ?Thu, 02 Oct 2008 14:12:58 GMThomebrew01http://www.sqlservercentral.com/Forums/Topic579177-149-1.aspxSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [dbo].[MCommOpenOrders]( [JOBID] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [SERVICEORDERNUM] [varchar](6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [DUEDATE] [datetime] NULL, [ORIGINALDUEDATE] [datetime] NULL, [JOBSTATUS] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [DISPATCHUNITID] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [STATUSUPDATEDBY] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [JOBTYPE] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [DISPATCHREGIONID] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [WORKGROUP] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [DA] [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [DURATION] [numeric](20, 0) NULL, [APPTSTARTTIME] [datetime] NULL, [APPTENDTIME] [datetime] NULL, [NAME] [varchar](90) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [ASSIGNEDFORDATE] [datetime] NULL, [ADDRESS] [varchar](339) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [CUSTPHONENO] [varchar](42) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [BCRIS_CALENDER] [varchar](32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [CREATIONDATE] [datetime] NULL, [TROUBLECATEGORY] [varchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [DISPATCHTYPECATEGORY] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [WORKFORCEREQUIRED] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [PATTERN] [varchar](11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [SERVICEAFFECTING] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [DISPATCHDATETIME] [datetime] NULL, [TimeOfDump] [datetime] NULL) ON [PRIMARY]GOSET ANSI_PADDING OFF-------------------------------------------------------------------------------------------------SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [dbo].[tbl_R15analysis]( [OriginalCt] [varchar](22) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [R15Ct] [varchar](22) COLLATE Latin1_General_CI_AI NULL, [Phone] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [jobtype] [varchar](22) COLLATE Latin1_General_CI_AI NULL, [troublecategory] [varchar](22) COLLATE Latin1_General_CI_AI NULL, [wfr] [varchar](6) COLLATE Latin1_General_CI_AI NULL, [workgroup] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Region] [varchar](22) COLLATE Latin1_General_CI_AI NULL, [originalduedate] [datetime] NULL, [CurrentDuedate] [datetime] NULL, [CurrentTech] [varchar](22) COLLATE Latin1_General_CI_AI NULL, [Coded_By] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [referaltime] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Bell_Received] [datetime] NULL, [DP1id] [varchar](5) COLLATE Latin1_General_CI_AI NULL, [dp1tm] [datetime] NULL, [dp2id] [varchar](5) COLLATE Latin1_General_CI_AI NULL, [dp2tm] [datetime] NULL, [CT] [datetime] NULL, [DISP] [varchar](5) COLLATE Latin1_General_CI_AI NULL, [timeofdump] [datetime] NULL, [TroubleRcd] [varchar](10) COLLATE Latin1_General_CI_AI NULL) ON [PRIMARY]GOSET ANSI_PADDING OFF-------------------------------------------------------------------------------------------------SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [dbo].[tbl_R15OrdersToday]( [ID] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [NPA] [varchar](42) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [CUSTPHONENO] [varchar](42) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [DUEDATE] [datetime] NULL, [JOBTYPEID] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [TROUBLECATEGORY] [varchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [REASONCODE] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [TECHID] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [DISPATCHAREAID] [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [DISPATCHREGIONID] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [WORKGRPID] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [CREATIONDATE] [datetime] NULL, [TROUBLERECEIVEDDATE] [datetime] NULL, [eventDateTime] [datetime] NOT NULL) ON [PRIMARY]GOSET ANSI_PADDING OFFWed, 01 Oct 2008 10:23:18 GMTSQLbeginnerhttp://www.sqlservercentral.com/Forums/Topic579177-149-1.aspxStill need the DDL for the under lying tables to the view.:cool:Wed, 01 Oct 2008 10:12:56 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic579177-149-1.aspxHi,It's actually a View, not a table.Wed, 01 Oct 2008 10:07:01 GMTSQLbeginnerhttp://www.sqlservercentral.com/Forums/Topic579177-149-1.aspxCan you provide the DDL (create statement) for the tables including collation for character columns?:cool:Wed, 01 Oct 2008 09:45:17 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic579177-149-1.aspxCan anyone help explain a way to resolve the error I receive from the following statement:-------------------------------------------------------------------------------------------------select distinct a.jobid, a.custphoneno, a.dispatchregionid,a.originalduedate,a.timeofdump,c.sector,null as Action_Taken,referaltimefrom mcommopenorders ajoin (select phone ,referaltimefrom occ_archives.dbo.tbl_R15analysiswhere (replace(left(referaltime,8),'-','') >= convert(varchar,getdate()-1,12))unionselect custphoneno, eventdatetimefrom occ.dbo.tbl_R15OrdersToday) as bon a.custphoneno = b.phoneleft join zones con a.Da collate Latin1_General_CI_AS = c.Dispatchareas collate Latin1_General_CI_ASwhere (left(address,4)='!MC!')and (pattern is null)and (jobstatus in ('PENDING_DISPATCH','ASSIGNED'))and (dispatchtypecategory ='SA-Cable'or dispatchtypecategory is null)and (workforcerequired = 'g')and (convert(varchar,a.originalduedate,112) = convert(varchar,getdate(),112))and (sector not like '%Aliant')and (convert(varchar,a.assignedfordate,112) = convert(varchar,getdate(),112)or a.assignedfordate is null)-------------------------------------------------------------------------------------------------[b]Msg 468, Level 16, State 9, Procedure vu_R15_Potential2ndMisses, Line 3Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the UNION operation.[/b]Wed, 01 Oct 2008 09:39:09 GMTSQLbeginner