<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2005
»
SQL Server 2005 General Discussion
»
Collation Conflict
12 posts, Page 1 of 2
1
2
»»
Collation Conflict
Rate Topic
Display Mode
Topic Options
Author
Message
SQLbeginner
SQLbeginner
Posted Wednesday, October 01, 2008 9:39 AM
SSC 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
Lynn Pettis
Lynn Pettis
Posted Wednesday, October 01, 2008 9:45 AM
SSC-Insane
Group: General Forum Members
Last Login: Today @ 11:18 AM
Points: 21,633,
Visits: 27,490
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
SQLbeginner
SQLbeginner
Posted Wednesday, October 01, 2008 10:07 AM
SSC 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
Lynn Pettis
Lynn Pettis
Posted Wednesday, October 01, 2008 10:12 AM
SSC-Insane
Group: General Forum Members
Last Login: Today @ 11:18 AM
Points: 21,633,
Visits: 27,490
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
SQLbeginner
SQLbeginner
Posted Wednesday, October 01, 2008 10:23 AM
SSC 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
homebrew01
homebrew01
Posted Thursday, October 02, 2008 2:12 PM
SSCrazy
Group: General Forum Members
Last Login: Today @ 8:14 AM
Points: 2,555,
Visits: 7,214
What about "zones" ?
Post #579939
SQLbeginner
SQLbeginner
Posted Friday, October 03, 2008 1:17 PM
SSC 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
homebrew01
homebrew01
Posted Friday, October 03, 2008 1:42 PM
SSCrazy
Group: General Forum Members
Last Login: Today @ 8:14 AM
Points: 2,555,
Visits: 7,214
What are the collation settings for all the databases involved ?
Post #580549
SQLbeginner
SQLbeginner
Posted Tuesday, October 07, 2008 9:21 AM
SSC 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
nary
nary
Posted Tuesday, October 07, 2008 9:41 AM
SSCommitted
Group: General Forum Members
Last Login: 2 days ago @ 12:24 PM
Points: 1,625,
Visits: 1,314
Try COLLATE function in your query,
Post #581934
« Prev Topic
|
Next Topic »
12 posts, Page 1 of 2
1
2
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
