none
Unresolved References to Temp Table on Linked Server RRS feed

  • Question

  • 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).

    My 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 staging database into the temporary table in order to perform a join to a much larger source table (~20m rows) in the Oracle database to avoid marshalling all the rows over the network. Also, the Oracle database is for a third-party application and we don't have permission to create permanent tables in the 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]

    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. The tooling should be smart enough to know that I intend to create the temporary object and resolve the reference.

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

    Monday, May 9, 2016 11:00 AM