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 12»»

Collation Conflict Expand / Collapse
Author
Message
Posted Wednesday, October 1, 2008 9:39 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, August 26, 2011 11:27 AM
Points: 38, Visits: 249
Can 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
,referaltime
from mcommopenorders a
join
(select phone ,referaltime
from occ_archives.dbo.tbl_R15analysis
where (replace(left(referaltime,8),'-','') >= convert(varchar,getdate()-1,12))
union
select custphoneno, eventdatetime
from occ.dbo.tbl_R15OrdersToday) as b
on a.custphoneno = b.phone
left join zones c
on a.Da collate Latin1_General_CI_AS = c.Dispatchareas collate Latin1_General_CI_AS
where (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)
-------------------------------------------------------------------------------------------------

Msg 468, Level 16, State 9, Procedure vu_R15_Potential2ndMisses, Line 3
Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the UNION operation.
Post #579177
Posted Wednesday, October 1, 2008 9:45 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 2:18 PM
Points: 20,755, Visits: 32,571
Can you provide the DDL (create statement) for the tables including collation for character columns?




Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #579190
Posted Wednesday, October 1, 2008 10:07 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, August 26, 2011 11:27 AM
Points: 38, Visits: 249
Hi,

It's actually a View, not a table.
Post #579211
Posted Wednesday, October 1, 2008 10:12 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 2:18 PM
Points: 20,755, Visits: 32,571
Still need the DDL for the under lying tables to the view.




Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #579218
Posted Wednesday, October 1, 2008 10:23 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, August 26, 2011 11:27 AM
Points: 38, Visits: 249
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE 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]

GO
SET ANSI_PADDING OFF
-------------------------------------------------------------------------------------------------
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE 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]

GO
SET ANSI_PADDING OFF
-------------------------------------------------------------------------------------------------
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE 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]

GO
SET ANSI_PADDING OFF
Post #579229
Posted Thursday, October 2, 2008 2:12 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 11:55 AM
Points: 2,832, Visits: 8,519
What about "zones" ?


Post #579939
Posted Friday, October 3, 2008 1:17 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, August 26, 2011 11:27 AM
Points: 38, Visits: 249
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE 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]

GO
SET ANSI_PADDING OFF
Post #580534
Posted Friday, October 3, 2008 1:42 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 11:55 AM
Points: 2,832, Visits: 8,519
What are the collation settings for all the databases involved ?


Post #580549
Posted Tuesday, October 7, 2008 9:21 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, August 26, 2011 11:27 AM
Points: 38, Visits: 249
There 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 database

OCC_ARCHIVES has the collation setting Latin1_General_CI_AI
- table tbl_R15_analysis falls under this database
Post #581919
Posted Tuesday, October 7, 2008 9:41 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, October 26, 2014 9:58 AM
Points: 1,667, Visits: 1,378
Try COLLATE function in your query,
Post #581934
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse