SQLTeam.com | Weblogs | Forums

Full-Qualifier in T-SQL and Databases on diffrent Server


#1

Hi,

A question about distributed databases. After a migration to SQL2016, we used a DWH project, the Staging / Extraction DB on a separate server. DW + DM is also located on a server. The dtsx packages are also on the DWH / DM server (package deployment).
In some dataflows, there is an SQL join with full qualification.
Example:
SELECT c1, c2
FROM [DWH]. [Dbo]. [TableA] AS a,
JOIN [staging]. [Dbo]. [TableB] as b ....

The problem: The table from DB "DWH" is local. The table from DB "Staging" on another server.
How can I access this join in SSIS-DTSX (DFT, Lookups, SQL, etc.)?

Best regards
Nicole;)


#2

I expect that I am answering the wrong question, but you can use 3-part and 4-part naming (dunno specifically in SSIS if that is possible, as I don't use that).

FROM [localDatabase].dbo.[LocalTable]
FROM [RemoteServer].[RemoteDatabase].dbo.[RemoteTable]

We NEVER use 3-part and 4-part naming but, instead, create a SYNONYM. That means if the darn thing changes in the future (it will!) we can just change teh SYNONYM, and not have to modify (and re-test) all the code.

Also, we never use a JOIN between RemoteServer and LocalServer (JOIN between two different local databases is OK). I don't know whether "never" is too strong a rule, but we avoid it because of the risk of SQL pulling ALL the data from Remote and THEN figuring out the JOIN locally.

We do keep a local staging-table copy of remote data, locally, to then JOIN, and we freshen up the staging table (as efficiently as we can / with minimum amount of data moving across the WAN) once a day / whatever.


#3

Great ! :slight_smile:


#4

Great what? If you're using 3 or 4 part naming, not so great. Use synonyms as Kristen suggested instead.