title: Is there a way to query SQL Server linked server without hardcoding database name?
body: <p>I found samples where the code:</p>
  
  <pre><code>SELECT * FROM [legacyserver].[database].[schema].[table]
  </code></pre>
  
  <p>was expressed as:</p>
  
  <pre><code>SELECT * FROM [legacyserver]...[table]
  </code></pre>
  
  <p>but isn't working for me.</p>
  
  <p>It gives me the error:</p>
  
  <blockquote>
    <p>An invalid schema or catalog was specified for the provider "MSDASQL" for linked server "legacyserver".</p>
  </blockquote>
  
  <p>I'm using for legacy server SQL SERVER 2000 and for the new server SQL SERVER 2012.</p>
  
  <p>I tried creating the linked server using:</p>
  
  <pre><code>EXEC sp_addlinkedserver 
     @server = 'legacyserver', 
     @srvproduct = '',
     @provider = 'MSDASQL',
     @provstr = 'DRIVER={SQL Server};SERVER=legacyserver;DATABASE=database;Trusted_Connection=Yes;',
  </code></pre>
  
  <p>and:</p>
  
  <pre><code>EXEC sp_addlinkedserver 
     @server = 'legacyserver', 
     @srvproduct = '',
     @provider = 'MSDASQL',
     @provstr = 'DRIVER={SQL Server};SERVER=legacyserver;Trusted_Connection=Yes;',
     @catalog = 'database';
  </code></pre>
  
  <p>Is it possible to create the script without hard coding the database name?</p>
  
  <p>I need to create a really big migration script and it need to use a development, acceptance and production databases, and it will be to much work and error prone to change it using replace in the text editor.</p>
  
  <p><strong>UPDATE:</strong></p>
  
  <p>The legacy development, acceptance and production databases are exactly the same (except for the data) and each one have it's own server/instance and database name.</p>
  
  <p>Due to segregation of duties, I can't develop something and deploy it, so I have no way to touch this script after acceptance. I will need to instruct someone else to do so, and if he/she will need to replace every occurrence of [legacyserver].[database], the chances of mistakes are very high.</p>