Global temporary table scope behaves differently inside a stored procedure

Question

The following code executes successfully, unless I add create procedure dbo.proc_name ... as to it:

use db_name
go

declare @sp_date date;
select @sp_date = getdate();

if object_id('tempdb..##global_tmp_tbl') is not null drop table ##global_tmp_tbl;

begin transaction

  set xact_abort on

  declare @query varchar(250), @exec_stmnt varchar(500);
  set @query = 'exec remote_db.dbo.remote_sp'  + ' ''''' + cast(@sp_date as varchar(10)) + ''''' ';
  set @query = '''' + @query + '''';

  set @exec_stmnt = 'select * into ##global_tmp_tbl from openquery(LS_RMT,' +  @query + ')';

  exec (@exec_stmnt);

commit transaction
go

if object_id('tempdb..#local_tmp_tbl') is not null drop table #local_tmp_tbl;
select * into #local_tmp_tbl from ##global_tmp_tbl;

Here LS_RMT is a linked server, and remote_sp is a stored procedure on the database remote_db on that linked server.

When I try to put this code into a stored procedure, SQL Server complains that ##global_tmp_tbl is an invalid name when trying to read from it after executing the stored procedure on the linked server which loads it.

I'm guessing that the scope of the global temporary table changes once within the context of a stored procedure, but I can't find any documentation on why that might be the case so I'm not sure.

Is this a scope issue, or is it actually possible to use the global temporary table within a stored procedure after it has been created inside a transaction that loads it from an openquery statement and I am just doing it wrong?

Answer 1

Although I am not entirely sure why the code above works when outside the context of a stored procedure, I did determine that embedding all references to the global temporary table within the committed transaction allowed the stored procedure to work. So something like the following:

use db_name
go

create procedure dbo.proc_name
@sp_date date = NULL

as
if isnull(@sp_date,'') = ''
begin
  select @sp_date = getdate();
end


if object_id('tempdb..##global_tmp_tbl') is not null drop table ##global_tmp_tbl;

begin transaction

  set xact_abort on

  declare @query varchar(250), @exec_stmnt varchar(500);
  set @query = 'exec remote_db.dbo.remote_sp'  + ' ''''' + cast(@sp_date as varchar(10)) + ''''' ';
  set @query = '''' + @query + '''';

  set @exec_stmnt = 'select * into ##global_tmp_tbl from openquery(LS_RMT,' +  @query + ')';

  exec (@exec_stmnt);

if object_id('tempdb..#local_tmp_tbl') is not null drop table #local_tmp_tbl;
select * into #local_tmp_tbl from ##global_tmp_tbl;

commit transaction
go