none
Linked Server - Unresolved Reference RRS feed

  • Question

  • I am getting an unresolved reference error on references to a linked server in my database project.  I have created the linked server object in the database project (using Project right click, add, New Item, linked server).  The linked server uses a third party provider (dbAmp) to access data on SalesForce, thus I cannot create a dacpac for the db and simply add it as another project in the solution.

    Can someone please point me in the right direction?

    Thanks,

    Darwin Fisk

    Monday, November 17, 2014 8:13 PM

All replies

  • Have you also added a Linked Server login? 

    If not, please add it and see if that resolves the issue.

    Lonny

    Thursday, November 27, 2014 6:08 AM
  • Yes, I have added a login.  Still no resolution.
    Monday, December 1, 2014 2:49 PM
  • Can you show how you're using the linked server? If you have "Enable extended Transact-SQL verification for common objects" checked that prevents you from using OPENQUERY

    "Error:  SQL11614: OPENQUERY is not supported."

    This works for me... 

    EXEC dbo.sp_addlinkedserver 
        @server = N'SSASServer'
      , @provider=N'MSOLAP'
      , @datasrc=N'$(SSASServer)'
    GO
    
    EXEC dbo.sp_addlinkedsrvlogin 
        @rmtsrvname = N'SSASServer'
    GO
    
    CREATE VIEW [dbo].[SSASDatabases]
    AS 
    
    SELECT CAST(CATALOG_NAME AS VARCHAR(256)) AS CubeName 
    FROM   OPENQUERY([SSASServer], 'SELECT * FROM [$system].[dbschema_catalogs]') 
    
    GO


    Tuesday, December 2, 2014 11:31 AM
  • This is the Linked Server Script That I have added to my VS 2013 Database project:

    EXEC sp_addlinkedserver @server = N'SALESFORCE', @srvproduct=N'DBAmp', @provider=N'DBAmp.DBAmp', @location=N'valid location'
    GO
    EXEC sp_addlinkedsrvlogin @rmtsrvname=N'SALESFORCE',@useself=N'False',@locallogin=NULL,@rmtuser=N'valid user',@rmtpassword='valid password'
    GO
    EXEC sp_serveroption @server=N'SALESFORCE', @optname=N'collation compatible', @optvalue=N'true'
    GO
    EXEC sp_serveroption @server=N'SALESFORCE', @optname=N'data access', @optvalue=N'true'
    GO
    EXEC sp_serveroption @server=N'SALESFORCE', @optname=N'dist', @optvalue=N'false'
    GO
    EXEC sp_serveroption @server=N'SALESFORCE', @optname=N'pub', @optvalue=N'false'
    GO
    EXEC sp_serveroption @server=N'SALESFORCE', @optname=N'rpc', @optvalue=N'false'
    GO
    EXEC sp_serveroption @server=N'SALESFORCE', @optname=N'rpc out', @optvalue=N'true'
    GO
    EXEC sp_serveroption @server=N'SALESFORCE', @optname=N'sub', @optvalue=N'false'
    GO
    EXEC sp_serveroption @server=N'SALESFORCE', @optname=N'connect timeout', @optvalue=N'0'
    GO
    EXEC sp_serveroption @server=N'SALESFORCE', @optname=N'collation name', @optvalue=null
    GO
    EXEC sp_serveroption @server=N'SALESFORCE', @optname=N'lazy schema validation', @optvalue=N'false'
    GO
    EXEC sp_serveroption @server=N'SALESFORCE', @optname=N'query timeout', @optvalue=N'0'
    GO
    EXEC sp_serveroption @server=N'SALESFORCE', @optname=N'use remote collation', @optvalue=N'true'
    GO
    EXEC sp_serveroption @server=N'SALESFORCE', @optname=N'remote proc transaction promotion', @optvalue=N'true'

    This is the exact script I use on my server and the linked server works fine.  In the VS 2013 project, I just get errors on any other objects I create referring to the linked server.

    Thanks,

    Darwin

    Friday, December 5, 2014 7:16 PM
  • Can you share the error messages? Also, could you share the version SSDT you are using?

    Lonny

    Sunday, December 7, 2014 12:03 AM
  • Warning 3 SQL71562: Procedure: [sfdc].[uspUpdateOpportunityLineItemStatus] has an unresolved reference to object [SALESFORCE].[].[dbo].[OpportunityLineItem]. c:\xxx\xxx\sfdc\Stored Procedures\uspUpdateOpportunityLineItemStatus.sql 16 57 SemPlatform

    The same error is produced from any reference to the SALESFORCE linked server.

    Monday, December 8, 2014 4:27 PM
  • The issue is still not resolved.  Unfortunately this is keeping our team from any use of Visual Studio for Database Development.  Hopefully someone (redgate?) comes up with a competing tool that can handle linked servers in some form or fashion.

    For what its worth,

    Darwin

    Tuesday, February 24, 2015 4:55 PM
  • Hi Darwin. Could you explain how you are referencing the linked server? For example a snippet of the Procedure where it fails to work? Otherwise it's hard to spot what your error is and how to solve it. Thanks,

    Kevin

    Tuesday, February 24, 2015 6:15 PM
    Moderator
  • Here is an example of where it fails:

    CREATE FUNCTION [dbo].[SF_PickLists]()
    RETURNS @PickListTable TABLE 
    	(ObjectName nvarchar(48), 
    	 FieldName nvarchar(48),
    	 PickListValue nvarchar(256),
    	 PickListLabel nvarchar(256))
    AS
    BEGIN
    	INSERT @PickListTable
    	SELECT ObjectName, FieldName, PickListValue, PickListLabel
    	FROM SALESFORCE...sys_sfpicklists
    	RETURN 
    END
    


    This results in the following error: 

    Warning 7 SQL71562: Function: [dbo].[SF_PickLists] has an unresolved reference to object [SALESFORCE].[].[dbo].[sys_sfpicklists]. c:\AmpDb\Salesforce_Backups\dbo\Functions\SF_PickLists.sql 12 7 Salesforce_Backups

    The linked server is defined as previously posted.

    Thanks for your help,

    Darwin

    Tuesday, February 24, 2015 6:54 PM
  • I think the problem is you're trying to use a four part name for a non sql server environment. You need to use Open Query from memory
    Tuesday, February 24, 2015 8:29 PM
  • I think the problem is you're trying to use a four part name for a non sql server environment. You need to use Open Query from memory

    Hi Craig,

    I am having the same error.

    In my case the linked server reference is another Visual Studio Database Project referenced as "Different Server" (Linked Server).

    Using: Visual Studio Enterprise 2015

    I cannot see any reason why VS cannot resolve references such as:

    CREATE PROCEDURE  dbo.MyStoredProc AS BEGIN

    SELECT * 

    FROM[$(LINKED_SERVER)].[$(LINKED_DB)].[dbo].[Items] AS I 

    END


    • Edited by Ben_m1 Friday, February 12, 2016 5:01 PM
    Friday, February 12, 2016 4:59 PM
  • Hi all,

    I just found out why Visual Studio was not resolving the linked server references.

    This was my case:

    I have a Visual Studio solution with three database projects:

    • Two of those database projects contain the definition (Data-Tier Application) of two external SQL Server databases. Lets call these databases "LocalReferenceDB" and "LinkedServerReferenceDB"
    • The third database Project (let's call it MainDB) is my real Project where I am working on. This database Project has two database references pointing to the external databases. I used the previous database Projects to create the Database References.

    The MainDB Project refers to LocalReferenceDB through a local reference. This reference has only a database name ($(LOCAL_SERVER)). The referenc to the LinkedServerReferenceDB is made as an external reference which requires two names to point to the database ($(LINKED_SERVER) and $(LINKED_DB)).

    The three of these SQLCMD variables are defined on the MainDB Project with default values. If the default value assigned to $(LOCAL_SERVER) can be any value, meaning that we do not need any database named $(LOCAL_SERVER) in the SQL Server where MainDB Project is connected to.

    The Linked Server Database Reference doesn't work in the same way. Visual Studio needs a Linked Server to be actually created on the SQL Server instance where MainDB is connnected to. This linked server must be named the default value assigned to $(LINKED_SERVER) and it must have a database named as the default value assigned to $(LINKED_DB).

    My Project was not working because the default value assigned to $(LINKED_DB) was equal to the LinkedServerReferenceDB database Project while the actual database in the real linked server had a different name.

    I hope this helps someone else.

    Friday, February 12, 2016 5:27 PM