从Excel运行的SQL不能使用临时表 [英] SQL run from Excel cannot use a temporary table

问题描述

我已经能够创建从Excel到SQL Server的数据连接，并且可以成功执行许多SQL查询。但是如果包含一个临时表，我不能让任何TSQL工作。例如：

I have been able to create a data connection from Excel to SQL Server and execute many SQL queries successfully. But I cannot get any TSQL to work if it includes a temporary table. For example:

select * into #t from compass3.dbo.freq
select * from #t where freq_id>2

（显然，在这种情况下没有必要使用#t：我只是给最简单的例子）在SSMS中工作正常，但是当通过Excel执行时，我收到错误消息我们无法刷新连接'audbbicube'，离线Query1可能不存在。

(Clearly there is no need to use #t in this case: I'm just giving the most simple example.) This work fine in SSMS but when executed via Excel I get the error message "We couldn't refresh the connection 'audbbicube'. The table 'ion Query1' may not exist."

在其他一些SO帖子中，人们建议在上添加 set nocount，但在这种情况下没有任何区别。 / p>


In some other SO posts people suggested adding set nocount on, but that made no difference in this case.

推荐答案

以下内容似乎正常工作...

The following appears to work ...

set nocount on
declare @t table(fid int)  -- I'm sure I could add the rest of the columns if I wanted to
insert @t select freq_id from compass3.dbo.freq
select * from @t where fid>2

所以只要我转过 nocount 并使用表变量而不是临时表，我可以实现我需要的。

So as long as I turn nocount on and use a table variable rather than a temporary table, I can achieve what I need.

这篇关于从Excel运行的SQL不能使用临时表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！