title: Run dynamic SQL Server stored procedure with linked server
body: <p>This is a dynamic stored procedure that will pass the database, linked server and state. When executing this stored procedure, it runs the stored procedure on the database on the linked server and gives the results back.</p>
  <p>Working code - here the linked server is absolute and not passed as a variable</p>
  <pre><code>EXECUTE MYPROC 'CA','MYDB'
  </code></pre>
  <pre><code>CREATE PROCEDURE [dbo].[MYPROC]
  (
      @state varchar(2),
      @DATABASE char(20)
  )
  AS
      DECLARE @SQL @VARCHAR(MAX)
   
      SELECT @SQL = 'use ' + @DATABASE + ';
      SELECT * FROM pubs.dbo.authors WHERE state = @state'
  
      EXEC MYLINKSERVER.master.dbo.sp_executesql
                @SQL, N'@state char(2)', @state
  </code></pre>
  <p>Not working code: here the linked server is passed through a variable.</p>
  <p>I get a &quot;Syntax error&quot; at <code>@LINKEDSERVER**.**master</code></p>
  <pre><code>EXECUTE MYPROC 'CA','MYDB','MYLINKSERVER'
  </code></pre>
  <pre><code>CREATE PROCEDURE [dbo].[MYPROC]
  (
      @state varchar(2),
      @DATABASE char(20),
      @LINKEDSERVER VARCHAR(20)
  )
  AS
      DECLARE @SQL @VARCHAR(MAX)
  
      SELECT @SQL = 'use ' + @DATABASE + ';
      SELECT * FROM pubs.dbo.authors WHERE state = @state'
  
      EXEC @LINKEDSERVER.master.dbo.sp_executesql
              @SQL, N'@state char(2)', @state
  </code></pre>
  <p>Thanks in advance</p>