Share via

How to avoid 4-part naming to linked server to an Oracle database in SQL Server while using DBPro

  • Article

 

 Scenario

1.  You have set up a linked server to an Oracle database in SQL Server

2.  You have created a synonym for an Object from the Linked Server

CREATE SYNONYM [dbo].[SynonymProducts] FOR [Oracle_DATABASE]..[PRODRHDW].[Products]

3. Attempt to access the synonym:

Create View [dbo].[ViewProducts]
AS SELECT * FROM SynonymProducts

causes - TSD03006: unresolved reference errors. As expected DBPro fails to resolve the reference to the linked server.

Cause:

Linked servers for Oracle are not supported in the SQL DBPro Project.

 

 Workaround: You can work around this by recreating the synonym in the post deployment script. To modify the post-deployment script 1.  In Solution Explorer, expand your database project to display the Scripts folder.2.  Expand the Scripts folder so that the Post-deployment subfolder appears.3.  Expand the Post-deployment subfolder, and click the script.postdeployment.sql file.4.  On the View menu, click Open.The Transact-SQL (T-SQL) editor opens, displaying the contents of the script.postdeployment.sql file. For information about SQLCMD mode and how to include your own scripts as part of the post-deployment script, see Editing SQLCMD Scripts in the Transact-SQL Editor. Included scripts are executed in the order in which you include them, so you can control the running order of your post-deployment scripts by including them in a specific order.Add your script in the file