August 3, 2017 at 5:26 am
Hi All,
I have a requirement where #temp table is available in one server lets say "A" and I need to join to another server "B"
without any linked server.
Note:- There is no permission to create temp table in Server "B" .
August 3, 2017 at 5:37 am
subratnayak09 - Thursday, August 3, 2017 5:26 AMHi All,I have a requirement where #temp table is available in one server lets say "A" and I need to join to another server "B"
without any linked server.
Note:- There is no permission to create temp table in Server "B" .
OK, SSIS is good at connecting to multiple servers (but not so good at handling temp tables).
What is your question?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
August 3, 2017 at 5:47 am
Yes, you haven't provided enough information for us to answer properly. But if you can't or won't use a linked server, then try SSIS or OPENROWSET.
John
August 3, 2017 at 6:25 am
Phil Parkin - Thursday, August 3, 2017 5:37 AMsubratnayak09 - Thursday, August 3, 2017 5:26 AMHi All,I have a requirement where #temp table is available in one server lets say "A" and I need to join to another server "B"
without any linked server.
Note:- There is no permission to create temp table in Server "B" .OK, SSIS is good at connecting to multiple servers (but not so good at handling temp tables).
What is your question?
Hi,
I placed Execute SQL task like "Select * INSERT INTO Temp#" in Server "A" . I need to join temp# from ServerA with Table X in Server B in OleDB Source.
How can achieve this ?
August 3, 2017 at 6:43 am
subratnayak09 - Thursday, August 3, 2017 6:25 AMHi,I placed Execute SQL task like "Select * INSERT INTO Temp#" in Server "A" . I need to join temp# from ServerA with Table X in Server B in OleDB Source.
How can achieve this ?
It won't be fast, but you could use a MERGE JOIN.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
August 3, 2017 at 6:54 am
subratnayak09 - Thursday, August 3, 2017 6:25 AMHi,I placed Execute SQL task like "Select * INSERT INTO Temp#" in Server "A" . I need to join temp# from ServerA with Table X in Server B in OleDB Source.
How can achieve this ?
It won't be fast, but you could use a MERGE JOIN.
August 3, 2017 at 7:03 am
Phil Parkin - Thursday, August 3, 2017 6:43 AMsubratnayak09 - Thursday, August 3, 2017 6:25 AMHi,I placed Execute SQL task like "Select * INSERT INTO Temp#" in Server "A" . I need to join temp# from ServerA with Table X in Server B in OleDB Source.
How can achieve this ?It won't be fast, but you could use a MERGE JOIN.
As we are dealing with Huge data in DWH tables will have performance issue. Do you have any better approach ?
August 3, 2017 at 7:24 am
subratnayak09 - Thursday, August 3, 2017 7:03 AMAs we are dealing with Huge data in DWH tables will have performance issue. Do you have any better approach ?
If SSIS isn't going to work then, like John Suggested, you could try OPENROWSET.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
August 3, 2017 at 7:40 am
How can you not have permission to create a temp table?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 3, 2017 at 7:58 am
subratnayak09 - Thursday, August 3, 2017 5:26 AMHi All,I have a requirement where #temp table is available in one server lets say "A" and I need to join to another server "B"
without any linked server.
Note:- There is no permission to create temp table in Server "B" .
Is doing it as you described really a "requirement" ? Temp tables (#) are scoped only for the duration of a stored procedure or session, so it's not something you can select from another session. Global temp tables (##) are at server level, and maybe would work as a hack, but my advice is not approach your problem that way.
If what you're actually trying to do is join a local rowset with a remote rowset, and you don't or can't use a linked server, then I'd say forget about the temp table part and instead leverage OPENROWSET. It works sort of like a linked server, except the connection properties and credentials are coded into the statement itself.
For example, let's assume you have a local table called Employees and you want to join that with a remte table called Department on a server called Seattle1.
SELECT a.DepartmentName, b.EmployeeName
FROM OPENROWSET('SQLNCLI', 'Server=Seattle1;Trusted_Connection=yes;',
'SELECT GroupName, Name AS DepartmentName, DepartmentID
FROM AdventureWorks2012.HumanResources.Department'
) AS a
JOIN Employees AS b
ON b.DepartmentID = a.DepartmentID;
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
August 3, 2017 at 8:56 am
Why not use SSIS to get the required data from ServerA, and save it to a staging table on ServerB. Then join to the staging table on ServerB, and truncate the table.
August 3, 2017 at 9:43 am
Or simply create a linked server.
It's a common misconception that usage of linked servers create performance issues. Actually, no. A linked server is nothing more than a set of connection properties and authentication credentials contained in an easily accessible named object.
The root cause of the performance issue is the architectural decision (assuming there was planning behind the decision) to horizontally distribute your application databases across separate servers in the first place, which creates the need for distributed queries or ETL staging processes, which creates unnecessary network latency, table scans, and hash joins. In many scenarios linked servers are actually the most efficient method of joining rowsets across servers, more efficient than SSIS.
Of course, ideally there would be no need for joining remote tables in the first place.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
August 22, 2017 at 8:43 pm
Heh... or just do it all in T-SQL.
--Jeff Moden
August 22, 2017 at 10:45 pm
I am going to blame Jeff for reviving this old thread.
I sit here and wonder how it is that you don't have permission to create a temp table on one of the servers. Is this a political issue? It certainly isn't a technical limitation.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply