Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Trying to join 3 tables from different servers


Trying to join 3 tables from different servers

Author
Message
bquintana
bquintana
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
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. ***
J Livingston SQL
J Livingston SQL
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3454 Visits: 33044
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

whenriksen
whenriksen
SSC-Enthusiastic
SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)

Group: General Forum Members
Points: 124 Visits: 477
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
bquintana
bquintana
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 40
Thank you--- Error is gone, but Runtime is long.

Any ideas on reducing runtime?
whenriksen
whenriksen
SSC-Enthusiastic
SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)

Group: General Forum Members
Points: 124 Visits: 477
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.
bquintana
bquintana
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
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?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search