In most of our more recent tools we have a single layer in the ETL Staging solution that understands all of the connections to other systems / databases that need to be made.
This layer is comprised of synonyms. For those that do not know, all a synonym is in SQL server is a pointer to another object. In addition to being useful for many backward compatibility issues over time, they offer us a layer of abstraction for site specific connection information while keeping all of the downstream code consistent.
Now the downstream code can simple reference a standard named synonym such as SOURCE.TableOrView_Connection. If the source connection changes over time due to server consolidation, or infrastructure lifecycle initiatives all that needs to be changed is our abstraction layer / synonyms. One nice thing about synonyms is that SSDT allows you to use project variables in them (for the most part) that you can change at time of deployment.
Normally our connection synonyms reference a linked server using 4 part naming. Here are a couple examples:
- To another SQL Server Database
- To an Oracle Database
However, we have seen instances when connecting to Oracle where an error was thrown when using the 4 part linked server naming above.
“inconsistent metadata for a column. Metadata information was changed at execution time”
There are many articles out there describing the ways to correct the issue. One is to change the partition sizes on the oracle database which would require the Oracle DBA to perform the administration. This is rarely possible for us. Another is making sure you have the appropriate oracle driver version installed. While ideal it does not always solve the issue.
Instead we utilize another workaround – OpenQuery.
Since synonyms are just a pointer to another object we cannot write a query inside of it. To remain consistent with most solutions we still want to use the synonyms as that is where support personnel will likely look first.
The solution we utilize is to create a view using the OpenQuery statement and then just have the synonym reference the view.
The code would look like this.
One thing to note is that SSDT does not play nice with project variables and openquery. I will write up a post in the future on what that looks like and a way you can try to deal with it.
Do you find this information helpful?
Let us know in the comments below.