none
Could not find server "Server" in sys.servers error and Database Reference question

    Question

  • Like most, I am very new to SSDT.   The one aspect of SSDT that I am somewhat confused about is Stored Procs or even Views that reference tables on other Servers via Linked Servers.

    I have a Stored Procedure that selects from another Database Server and when I try to deploy, I get the error: "Could not find server "ReferencedServer" in sys.servers etc etc".  Now, I created a new SSDT project for the ReferencedServer and I am referencing it in my main SSDT Project but still getting the error.

    Is the Stored Proc trying to find "Server.Database.Schema.Table" in the new localdb SQL Server 11 Database or is it connecting to the real instance?  I am completely lost and just can't find any solution to this.  Maybe I did the references wrong or there is another step I am missing.  As far as I can tell, when you add new SSDT Project Databases, it just puts them in the localdb, even if it is on a different Server, so if the Stored Proc is trying to access "Server.Database.Schema.Table" locally, that path will never exist because everything is in localdb.

    Windows 7/SP1

    Visual Studio 2010 Professional/SP1

    Main SSDT Database: SQL Server 2008R2

    Referenced Database: SQL Server 2005

    Friday, March 09, 2012 10:12 PM

Answers

  • You can override the use of localdb at anytime.  If you have a test server available you can configure SSDT to deploy to it during F5.  In the Build properties of the project it defaults to localdb, but you can change it if you wish.  Ultimately if you do want to disable F5 you'll find the ability under Build.Configuration Manager.
    Sunday, March 11, 2012 4:28 AM

All replies

  • So no one has any experience with Stored Procs in one SSDT databse project that pulls data from another database server?

    We really only want to use SSDT to put our database objects in Source Control, so since there seems to be no resolution, I will probably need to look into disabling SSDT "deployment" to localdb when F5 is pushed, so we can have the objects under Source Control but not for development inside VS.

    Sunday, March 11, 2012 2:12 AM
  • You can override the use of localdb at anytime.  If you have a test server available you can configure SSDT to deploy to it during F5.  In the Build properties of the project it defaults to localdb, but you can change it if you wish.  Ultimately if you do want to disable F5 you'll find the ability under Build.Configuration Manager.
    Sunday, March 11, 2012 4:28 AM
  • psirr, thanks for the response.

    In this case, it is not going to matter where you deploy too as the Stored Proc that references a table in a different database server is going to cause a deployment error.   I actually thought about this and I suspect you have run the built in Linked Server Stored Proc on the localdb for this problem to go away.  I may look into this for an hour or so tomorrow but must move on to other tasks.

    But as stated, I was only looking for a solution that would allow us to put our DB objects in Source Control and SSDT does this for us.  As of now, I will just disable deployment when F5 is pressed to prevent deployment errors.  If the team ever wants to start doing DB development inside of VS, then we can re-look at this.

    I played around with SSDT this weekend and I am glad that when you do Schema Compares, it is against your "Database Project" and not your localdb.  So if you have any issues with deployment, you are not forced to figure those out.  You can just manage your SSDT Project and not worry about anything else.

     

    Monday, March 12, 2012 1:43 AM
  • Because of the references to databases via linked servers, you may have to use a different database instance that supports such features. I had to do this on my project because we use the FILESTREAM feature, which isn't currently supported by localdb. Instead, I use a local instance of SQL Express 2008 R2. You can set the connection string for this deployment in Project Settings -> Debug -> Target Connection String.

    Keep in mind that deploying the project synchronizes all database objects with those in the project, which may conflict with other people that are also developing database objects.

    Wednesday, March 14, 2012 3:43 PM
  • maikeru-sama,

    You asked, "Is the Stored Proc trying to find "Server.Database.Schema.Table" in the new localdb SQL Server 11 Database or is it connecting to the real instance?"  The stored pocedure is trying to find the "Server" part of "Server.Database.Schema.Table" and cannot find it on the localdb instance.  You will need to add a Linked Server object to your referencing database project that has the same definition as the Linked Server on your full SQL Server instance (in addition to the database reference you have already created).  This will cause the linked server to be created on localdb when you hit F5.  This, in turn, will allow the stored procedure to deploy to localdb because it has a linked server defined with the name "Server".

    Now, if you were to execute the stored procedure from within the localdb instance, it would select data from the actual SQL Server instance defined by the Linked Server.  In other words, the Linked Server object lives on localdb, but the object itself is wired to connect to a full SQL Server instance.  Does this help?


    Anthony Warmoth

    Friday, August 24, 2012 8:19 PM