title: SQL Server - Stored procedure on remote catalog
body: <p>I have a stored procedure that setup a remote DB connection given the server name and default catalog. This is usually invoked during the deployment of our services in the datacenter, so none of these parameters is known in advanced.</p>
  
  <p>Here's the stored procedure:</p>
  
  <pre><code>CREATE PROCEDURE [dbo].[p_SetupRemoteAdStore]
      @Catalog nvarchar(50) = N'Catalog_A', 
      @Server nvarchar(50) = N'localhost'
  AS
  BEGIN
      SET NOCOUNT ON;
  
      if not exists( select * from sys.servers where name = @Server )
          exec sp_addlinkedserver @server=@Server, @srvproduct=N'', @provider=N'SQLNCLI', @datasrc=@Server, @catalog=@Catalog
  
      if not exists( select * from sys.servers where name = N'REMOTE_SERVER' )
          exec sp_addlinkedserver @server=N'REMOTE_SERVER', @srvproduct=N'SQL Server'
  
      EXEC master.dbo.sp_serveroption @server=N'REMOTE_SERVER', @optname=N'collation compatible', @optvalue=N'false'
      EXEC master.dbo.sp_serveroption @server=N'REMOTE_SERVER', @optname=N'data access', @optvalue=N'true'
      EXEC master.dbo.sp_serveroption @server=N'REMOTE_SERVER', @optname=N'dist', @optvalue=N'false'
      EXEC master.dbo.sp_serveroption @server=N'REMOTE_SERVER', @optname=N'pub', @optvalue=N'false'
      EXEC master.dbo.sp_serveroption @server=N'REMOTE_SERVER', @optname=N'rpc', @optvalue=N'true'
      EXEC master.dbo.sp_serveroption @server=N'REMOTE_SERVER', @optname=N'rpc out', @optvalue=N'true'
      EXEC master.dbo.sp_serveroption @server=N'REMOTE_SERVER', @optname=N'sub', @optvalue=N'false'
      EXEC master.dbo.sp_serveroption @server=N'REMOTE_SERVER', @optname=N'connect timeout', @optvalue=N'0'
      EXEC master.dbo.sp_serveroption @server=N'REMOTE_SERVER', @optname=N'collation name', @optvalue=null
      EXEC master.dbo.sp_serveroption @server=N'REMOTE_SERVER', @optname=N'lazy schema validation', @optvalue=N'false'
      EXEC master.dbo.sp_serveroption @server=N'REMOTE_SERVER', @optname=N'query timeout', @optvalue=N'0'
      EXEC master.dbo.sp_serveroption @server=N'REMOTE_SERVER', @optname=N'use remote collation', @optvalue=N'true'
      EXEC master.dbo.sp_serveroption @server=N'REMOTE_SERVER', @optname=N'remote proc transaction promotion', @optvalue=N'false'
      EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'REMOTE_SERVER', @locallogin = NULL , @useself = N'True'
      EXEC sp_setnetname 'REMOTE_SERVER', @Server
  END
  GO
  </code></pre>
  
  <p>This part works fine.</p>
  
  <p>Once deployment is done, I have a remote connection configured and I can call stored procedures like this:</p>
  
  <pre><code>EXECUTE [REMOTE_SERVER].[Catalog_A].[dbo].[p_SomeSProc] @Arg1 = @arg1, ...
  </code></pre>
  
  <p>The problem is that <code>Catalog_A</code> is hard coded here, and I don't actually use the default catalog that I've configured in the connection. </p>
  
  <p>I've tried different syntax but no luck.</p>
  
  <p>There's also the option of using <code>OPENQUERY</code> but that won't allow me to call stored procedures on the remote server.</p>
  
  <p>Any idea how I can invoke stored procedures on a remote connection using the configured initial catalog?</p>