时间戳

首页 > 解决方案 > 全局临时表范围在存储过程中的行为不同

sql-server - 全局临时表范围在存储过程中的行为不同

问题描述

以下代码成功执行，除非我添加create procedure dbo.proc_name ... as它：

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;

这LS_RMT是一个链接服务器，并且remote_sp是该链接服务器上数据库上的存储过程remote_db。

当我尝试将此代码放入存储过程时，SQL Server##global_tmp_tbl在加载它的链接服务器上执行存储过程后尝试读取它时抱怨这是一个无效名称。

我猜全局临时表的范围在存储过程的上下文中会更改一次，但我找不到任何文档说明为什么会出现这种情况，所以我不确定。

这是一个范围问题，还是实际上可以在存储过程中使用全局临时表，然后在从 openquery 语句加载它的事务中创建全局临时表，而我只是做错了？

标签： sql-serverstored-proceduresscopetemp-tableslinked-server

解决方案

虽然我不完全确定为什么上面的代码在存储过程的上下文之外工作，但我确实确定在提交的事务中嵌入对全局临时表的所有引用允许存储过程工作。所以类似于以下内容：

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

推荐阅读