1

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:

  1. The Oracle database belongs to a 3rd party application so I cannot create permanent tables in the database
  2. I have created the linked server object in the SSDT project
  • You may need to add a database reference in the project to the linked server by either using DACPAC or new database project. Have you tried that yet? – Antoine Hernandez Apr 22 '16 at 18:56
  • I would have done that, except it's a temporary table, what database does that reside in? – Jazza Apr 23 '16 at 21:57
2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.