我相信大家也遇,知道的指一下,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