Visual Studio Database Project using linked server database reference

Question

I am trying to use a linked Server database reference in a Visual Studio 2013 (using SQLServer 2008) project.

I am able to get it to build by making another project with the references as outlined in this link:

[Do I need a database reference for a linked server in a SQL Server database project?

But the project fails in the deploy step with the following error: Error 474 SQL72014: .Net SqlClient Data Provider: Msg 7202, Level 11, State 2, Procedure rs_ReportSubscriptions, Line 3 Could not find server 'TBSSRV07\TBSSQLDEVSRV' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers. For more information about this error, see the troubleshooting topics in the product documentation. C:\tfs2010\EtracsDatabase\Etracs\bin\Debug\Etracs.sql

On the deployed server this is working (using a linked Server... Link : TBSSRV07\TBSSQLDEVSRV)

This is happening in a view creating script .. I have tried to add the sp_addLinkedServer to the script...but in the deploy code my sp_addlinkedServer does not appear:

Here is the code for the view creation:

    EXEC sp_addlinkedserver   
   @server=N'TBSSRV07', 
   @srvproduct=N'',
   @provider=N'SQLNCLI', 
   @datasrc=N'TBSSRV07\TBSSQLDEVSRV';

GO
CREATE VIEW [dbo].[rs_ReportSubscriptions] AS 
SELECT  C.Name ,
        CAST(ExtensionSettings AS XML).value('(/ParameterValues/ParameterValue/Value)[1]',
                                             'varchar(250)') AS TargetSettingsTo ,
        CAST(ExtensionSettings AS XML).value('(/ParameterValues/ParameterValue/Value)[2]',
                                             'varchar(250)') AS TargetSettingsCC ,

        LastStatus ,
        LastRunTime
FROM    [$(TBSSRV07)].[$(ReportServer)].dbo.Subscriptions AS S
        JOIN [$(TBSSRV07)].[$(ReportServer)].dbo.Catalog AS C ON C.ItemID = S.Report_OID

Here is the resultant script that is having a problem:

GO
PRINT N'Creating [dbo].[rs_ReportSubscriptions]...';


GO
CREATE VIEW [dbo].[rs_ReportSubscriptions] AS 
SELECT  C.Name ,
        CAST(ExtensionSettings AS XML).value('(/ParameterValues/ParameterValue/Value)[1]',
                                             'varchar(250)') AS TargetSettingsTo ,
        CAST(ExtensionSettings AS XML).value('(/ParameterValues/ParameterValue/Value)[2]',
                                             'varchar(250)') AS TargetSettingsCC ,

        LastStatus ,
        LastRunTime
FROM    [$(TBSSRV07)].[$(ReportServer)].dbo.Subscriptions AS S
        JOIN [$(TBSSRV07)].[$(ReportServer)].dbo.Catalog AS C ON C.ItemID = S.Report_OID
GO
PRINT N'Update complete.';

Finally here is the value that I used for in the database reference: :setvar TBSSRV07 "TBSSRV07\TBSSQLDEVSRV"

Have you managed to solve your problem? If so, please add your solution as an answer to the question. — Hemario

Graeme Graeme · Accepted Answer · 2018-08-30T17:16:48

Not sure if this is the "best" answer, but in this situation, I add a pre-deployment script to create the linked server, if it is not present.

IF NOT EXISTS (SELECT 1 FROM sys.servers s WHERE s.[name] = N'$(TBSSRV07)' AND s.is_linked = 1)
BEGIN
    EXECUTE sp_addlinkedserver N'$(TBSSRV07)', ...

Visual Studio Database Project using linked server database reference

1 Answers