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

Trying to join 3 tables from different servers Expand / Collapse
Author
Message
Posted Tuesday, June 18, 2013 9:23 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 30, 2014 2:36 PM
Points: 12, Visits: 40
I have been trying to join 3 different tables from 2 different servers and I am having some success but have reached a wall.

Query (figured I show the actual)

Select *

FROM [VisNetic Mailflow].[dbo].[Tickets] [t]
left join
[VisNetic Mailflow].[dbo].[TicketHistory] [th]
on [t].[ticketid] = [th].[ticketid]
left join
[VisNetic Mailflow].[dbo].[Agents] [a]
on [th].[AgentID] = [a].[AgentID]
left join
[VisNetic Mailflow].[dbo].[TicketBoxes] [tb]
on [t].[TicketBoxID] = [tb].[TicketBoxID]
left join
[VisNetic Mailflow].[dbo].[TicketStates] [ts]
on [t].[TicketStateID] = [ts].[TicketStateID]
left join
[VisNetic Mailflow].[dbo].[TicketActions] [ta]
on [th].[TicketActionID] = [ta].[TicketActionID]
left join
[VisNetic Mailflow].[dbo].[AgentGroupings] [ag]
on [th].[AgentID] = [ag].[AgentID]
left join
[VisNetic Mailflow].[dbo].[Groups] [g]
on [g].[GroupID] = [ag].[GroupID]
left join
[VisNetic Mailflow].[dbo].[OutboundMessages] [om]
on [t].[TicketID] = [om].[TicketID]
left join
[PDO_Live].[dbo].[ACCOUNT] [ac]
on [t].[Contacts] = [ac].[STR_EMAIL_ADDRESS]
left join
[PDO_Live].[dbo].[vwAPPLICATION_SUMMARY] [vwapp]
on [t].[Contacts] = [vwapp].[STR_EMAIL_ADDRESS]
left join
[I3_IC].[dbo].[CallDetail] [cd]
on [t].[Contacts] = [cd].[RemoteNumberFmt]

where [t].[DateCreated] between '05-01-2013' and '05-02-2013'

and [th].[TicketBoxID] = '16'
Order by [t].[DateCreated]



and then take and join with [Server2].[db3].[arrival].


**The end result would be to show where the unique incident lead to the 1st call(HH:mm:ss) after the incident took place.

when I execute the query I get the error *** Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "SQL_Latin1_General_CP1_CI_AI" in the equal to operation. ***



Post #1464730
Posted Tuesday, June 18, 2013 9:47 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 3:04 PM
Points: 1,914, Visits: 19,146
does the following help you?

http://stackoverflow.com/questions/9393207/cannot-resolve-the-collation-conflict-between-sql-latin1-general-cp1-ci-as-and


______________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Post #1464746
Posted Tuesday, June 18, 2013 11:09 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 31, 2014 7:42 PM
Points: 39, Visits: 221
You need to use the same collation for both sides of your comparison.

In your situation, one server ignores accent marks when comparing, the other includes accent marks in the comparison.

If one server is set to 'HELENA' = 'HÉLÈNA' and the other is set to 'HELENA' <> 'HÉLÈNA', when you cross server boundaries using different collations with 'HELENA' = 'HÉLÈNA', the server doesn't know how to answer the question.

Set one side of your comparison to match the collation of the other.
http://msdn.microsoft.com/en-us/library/ms184391.aspx
Post #1464796
Posted Tuesday, June 18, 2013 11:21 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 30, 2014 2:36 PM
Points: 12, Visits: 40
Thank you--- Error is gone, but Runtime is long.

Any ideas on reducing runtime?
Post #1464802
Posted Tuesday, June 18, 2013 11:32 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 31, 2014 7:42 PM
Points: 39, Visits: 221
Assuming your indexes are appropriate for your query;

Create a view to pre-filter your data on the remote server. Ideally, create a single view that has all of the joins and data columns you need from the remote server. That way the remote server will take some of the load of filtering and joining. Use the view in your query to only retrive the data that you need from the remote server.

In a linked server join, the host server retrieves ALL of the rows from the source tables before attempting the join. It then performs the joins and filters locally.
Post #1464809
Posted Tuesday, June 18, 2013 12:30 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 30, 2014 2:36 PM
Points: 12, Visits: 40
whenriksen (6/18/2013)
Assuming your indexes are appropriate for your query;

Create a view to pre-filter your data on the remote server. Ideally, create a single view that has all of the joins and data columns you need from the remote server. That way the remote server will take some of the load of filtering and joining. Use the view in your query to only retrive the data that you need from the remote server.

In a linked server join, the host server retrieves ALL of the rows from the source tables before attempting the join. It then performs the joins and filters locally.


Ok. Thank you.

I am also wanting the unique events for each ticket and I am getting multiples. Why is that?
Post #1464832
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse