我相信大家也遇見過這種問題,知道的請指點一下,3q!
create proc emp(@date1 varchar(10),@date2 varchar(10))
as
set nocount on
select top 10* into #t from employee
select * from #t
where convert(varchar(10),hire_date,120) between @date1 and @date2
drop table #t
set nocount off
exec emp '1990-01-01 ', '1992-01-01 '--這樣有結果,是正確的呀
--但下面這樣就不行了
select *
from openrowset
( 'msdasql ', 'driver={sql server};server=ACCOUNTREPORT;uid=sa;pwd= ', 'exec pubs.dbo.emp ' '1990-01-01 ' ', ' '1992-01-01 ' ' ') AS a
--報錯
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'msdasql ' reported an error.
[OLE/DB provider returned message: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name '#t '.]
OLE DB error trace [OLE/DB Provider 'msdasql ' IColumnsInfo::GetColumnsInfo returned 0x80004005: ].
--如果我的存儲過程是這樣寫，
create proc emp(@date1 varchar(10),@date2 varchar(10))
as
set nocount on
select top 10* from employee
where convert(varchar(10),hire_date,120) between @date1 and @date2
set nocount off
select *
from openrowset
( 'msdasql ', 'driver={sql server};server=ACCOUNTREPORT;uid=sa;pwd= ', 'exec pubs.dbo.emp ' '1990-01-01 ' ', ' '1992-01-01 ' ' ') AS a
--這樣是有正確的結果的.也就是說我的存儲過程裡面不能借用臨時表，有臨時表上面寫的語句就會報錯
--但是一個存儲過程，是很復雜的，不可能不用臨時表啊
[解决办法]
try ##
[解决办法]
不要加#号.不用临时表.
用临时使用的表T,用完后删除表T
[解决办法]
将臨時表换成普通表应该有结果
[解决办法]
說個題外話,個人認為
where convert(varchar(10),hire_date,120) between @date1 and @date2
这種寫法不提倡,
還是都加個convert(datetime,xx)來得好些.
[解决办法]
用表变量代替
[解决办法]
TRY SET FMTONLY OFF
[解决办法]
用临时使用的表T,用完后删除表T
