SSDT – Resolving Unresolved Reference to Temporary Table in Linked Server

business-intelligencelinked-serveroraclessdtvisual-studio-2013

Here's the scenario.

I am extracting data from a table in an Oracle database via a linked server as part of an ETL process. I am developing the solution in Visual Studio 2013 with SQL Server Data Tools (SSDT).

The ETL procedure uses a temporary table which is dynamically created in the Oracle database via EXECUTE AT syntax. This is so that I can insert a small number of records from my SQL Server database into the temporary table in order to perform a join to a much larger source table (~20m rows) in the Oracle database. The insert statement uses four-part naming as follows:

INSERT INTO [linked-server]..[owner-name].[table-name]
SELECT a, b FROM [stage].[table-name];

However, because the table is temporary, my project contains a warning about an unresolved reference:

SQL71562: Procedure: [schema].[procedure-name] has an unresolved
reference to object [linked-server].[].[owner-name].[table-name]

Also, when I publish the database project, the publish operation fails because the temporary table does not exist. The error message reads:

The OLE DB provider "OraOLEDB.Oracle" for linked server
"[linked-server]" does not contain the table
""[owner-name]"."[table-name]"". The table either does not exist or
the current user does not have permissions on that table.

I can publish the project by creating the temporary table first via SQL Management Studio then dropping it after the project has been published. But this seems to go against how database projects should work.

I've seen suggestions about suppressing T-SQL warnings for unresolved references, but that doesn't enable me to publish the project.

Notes:

The Oracle database belongs to a 3rd party application so I cannot
create permanent tables in the database
I have created the linked server object in the SSDT project

Best Answer

I know this is an old question, but I want to throw this out there in case anyone else happens upon this post. Create a "scripts" folder in your SQL Database Project, and add a Pre-Deploy script that will create the temporary table in Oracle; right click on the script and set the Build Action to PreDeploy. I know it's not the most elegant solution, but it's quick and dirty, and will get the job done.

You'll have to "Build" the project before you publish it, which may still generate the unresolved object error; in that case you'll have to create the table before you build, and then drop it after, but it should work.

Related Solutions

SQL Server 2008 – Using Linked Server with OPENQUERY in Database Project

I've managed to get it working:

I created a new database project master. In there I created a folder Server Object and a file LinkedServer.sql. In the SQL file i added the linked server:

GO
EXECUTE sp_addlinkedserver @server = N'LinkedServer', @srvproduct = N'sqlserver', @provider = N'SQLNCLI', @datasrc = N'LinkedServer.domain';

After adding the database Project master to my solution and referencing it in my original databse project, I was able to build the project using the initial syntax;

CREATE VIEW dbo.vwStatus
AS
SELECT     StatusID, StatusName
FROM       OPENQUERY(LinkedServer, 'SELECT * FROM [DB].[dbo].tbStatus') AS derivedtbl_1

Sql-server – SSDT project with working linked server references gets SQL71562 warning

According to the message shown in the Question, this is actually a warning ("warnung") and not an error. An error will stop the build process and prevent the project from being published to the server. Warnings merely show up in the "Error List" window, but don't prevent the build or publish processes from completing.

I created a Database project in Visual Studio 2013 and added a stored procedure that selected from a Linked Server. If the Linked Server name did not match a valid Linked Server on SQL Server, then there was a publication error as reported by SQL Server (not Visual Studio). Otherwise, everything worked as expected. But in both cases, my Linked Server reference in that Stored Procedure in Visual Studio was underlined in blue AND the following message appeared in the "Error List" window after the build finished:

Warning 2   SQL71562: Procedure: [dbo].[GetExpressDBs] has an unresolved reference to
            object [LStoSqlExpress2014].[master].[sys].[databases].

If you don't want to see these warnings, there are two things you can do:

You can simply not show them in the "Error List" window by clicking the button at the top of the "Error List" window that says "X of Y Warning(s)". If you do this, you won't see ANY warnings here (even non-Linked Server-related warnings), but your code will still show the blue underlines that will display the warning if you hover over them.
You can tell Visual Studio / SSDT to ignore just these specific warnings (but still show the others) by doing the following:
1. Go to Project Properties
2. Go to the "Build" tab
3. Enter the following into the "Suppress Transact-SQL warnings:" text field:
  71562
Doing the steps noted above will prevent just this one specific warning from showing up in the "Error List" window as well as the blue underlines in the code using the Linked Servers.

Best Answer

Related Solutions

SQL Server 2008 – Using Linked Server with OPENQUERY in Database Project

Sql-server – SSDT project with working linked server references gets SQL71562 warning

Related Question