SQL语句如下:
但此SQL有问题,不能执行
CREATE VIEW v_Report AS
select * from (
sp_Report '2006-01-01 00:00:00','2006-01-01 23:59:59'
)sp_Report 是存储过程,返回一个记录集
要求将sp_Report 返回的记录集直接生成视图v_Report ,就是一句SQL实现这个功能
不要告诉这个答案:先把记录集存放入临时表,再从临时表取,现在是需要一句SQL实现这个功能谢谢下面是zjcxc(邹建)的答案CREATE VIEW v_Report AS
SELECT * FROM openrowset('sqloledb', 'Trusted_Connection=yes','exec sp_Report ''2006-01-01 00:00:00'',''2006-01-01 23:59:59''')可是执行是提示错误:
服务器: 消息 2812,级别 16,状态 62,过程 v_Report,行 2
Could not find stored procedure 'dbo.sp_Report'.而exec sp_Report ''2006-01-01 00:00:00'',''2006-01-01 23:59:59''
是可以执行的
但此SQL有问题,不能执行
CREATE VIEW v_Report AS
select * from (
sp_Report '2006-01-01 00:00:00','2006-01-01 23:59:59'
)sp_Report 是存储过程,返回一个记录集
要求将sp_Report 返回的记录集直接生成视图v_Report ,就是一句SQL实现这个功能
不要告诉这个答案:先把记录集存放入临时表,再从临时表取,现在是需要一句SQL实现这个功能谢谢下面是zjcxc(邹建)的答案CREATE VIEW v_Report AS
SELECT * FROM openrowset('sqloledb', 'Trusted_Connection=yes','exec sp_Report ''2006-01-01 00:00:00'',''2006-01-01 23:59:59''')可是执行是提示错误:
服务器: 消息 2812,级别 16,状态 62,过程 v_Report,行 2
Could not find stored procedure 'dbo.sp_Report'.而exec sp_Report ''2006-01-01 00:00:00'',''2006-01-01 23:59:59''
是可以执行的
Could not process object 'exec dbName.dbo.sp_Report '2006-01-01 00:00:00','2006-01-01 23:59:59''. The OLE DB provider 'sqloledb' indicates that the object has no columns.
OLE DB error trace [Non-interface error: OLE DB provider unable to process object, since the object has no columnsProviderName='sqloledb', Query=exec dbName.dbo.sp_Report '2006-01-01 00:00:00','2006-01-01 23:59:59',5'].
SELECT * FROM openrowset('sqloledb', 'Trusted_Connection=yes','exec DBReportServer.dbo.sp_Report ''2006-01-01 00:00:00'',''2006-01-01 23:59:59''')
openrowset('sqloledb', '计算机名;Trusted_Connection=yes','exec 库名.dbo. sp_Report ''2006-01-01 00:00:00'',''2006-01-01 23:59:59''')
或
openrowset('sqloledb', '计算机名;用户名;密码','exec 库名.dbo.sp_Report ''2006-01-01 00:00:00'',''2006-01-01 23:59:59''')
也是报一样的错误服务器: 消息 7357,级别 16,状态 2,过程 v_Report,行 2
Could not process object 'exec dbName.dbo.sp_Report '2006-01-01 00:00:00','2006-01-01 23:59:59''. The OLE DB provider 'sqloledb' indicates that the object has no columns.
OLE DB error trace [Non-interface error: OLE DB provider unable to process object, since the object has no columnsProviderName='sqloledb', Query=exec dbName.dbo.sp_Report '2006-01-01 00:00:00','2006-01-01 23:59:59',5'].
DBReportServer.dbo.sp_Report '2006-01-01 00:00:00','2006-01-01 23:59:59'在查询分析器里面是返回一个记录集的
(
@P1 datetime,
@P2 datetime
)
ASselect top 10 * from syscolumns
GO
试一下。
'exec DBReportServer.dbo.sp_Report ''2006-01-01 00:00:00'',''2006-01-01 23:59:59'''
换成
'select * from DBReportServer.dbo.表名'
试试能否成功,若成功则表示的确是调用存储过程的问题.一点点排除吧.
试过,可以的
(
@P1 datetime,
@P2 datetime
)
ASselect top 10 * from syscolumns
GO
试一下。如果没有问题,就检查你的存储过程。
AsBegin
if exists(select 1 from tempdb..sysobjects where xtype='u' and name like '#Temp%') drop table #Temp Select TOP 8000 ID=Identity(Int,0,1) Into #TempIdentity From syscolumns,SysObjects Select DateAdd(mi,@IMinute*ID,@BeginDate) As StartTime,DateAdd(mi,@IMinute*(ID+1),@BeginDate) As EndTime Into #TempMinute From #TempIdentity
Where DateAdd(mi,@IMinute*(ID+1),@BeginDate)<=@EndDate
Select
Time_Begin= Convert(Varchar(19),A.StartTime,120),
Time_End = Convert(Varchar(19),A.EndTime,120),
Call_Time = convert(datetime,convert(char(10),Time_In,120)),
Operator_No,
Station_Address,
Hour_Name = DATEPART(hh,Time_In) ,
Week_Name = DATEPART(wk,Time_In),
Month_Name = DATEPART(mm,Time_In) ,
From #TempMinute A Left Join User_Calldetail B
On B.[Time_In] Between A.StartTime And A.EndTime
Group By
Convert(Varchar(19),A.StartTime,120),
Convert(Varchar(19),A.EndTime,120),
convert(datetime,convert(char(10),Time_In,120)),Operator_NO,Station_Address,
DATEPART(hh,Time_In),
DATEPART(wk,Time_In) , DATEPART(mm,Time_In) ,VDN
End
GO
Could not process object 'exec DBReportServer.dbo.sp_Report '2006-01-01 00:00:00','2006-01-01 23:59:59''. The OLE DB provider 'sqloledb' indicates that the object has no columns.
OLE DB error trace [Non-interface error: OLE DB provider unable to process object, since the object has no columnsProviderName='sqloledb', Query=exec DBReportServer.dbo.sp_Report '2006-01-01 00:00:00','2006-01-01 23:59:59',5'].
create proc lxzm
as
select * from orders然后再去试试看
SELECT * FROM openrowset('sqloledb', 'Trusted_Connection=yes',
'exec psalary.dbo.sp_Report ''2006-01-01 00:00:00'',''2006-01-01 23:59:59''')
这句SQL是可以实现 ,问题是这个存储过程里面好像不允许使用临时表,如果使用临时表就会提示如下错误:
服务器: 消息 7357,级别 16,状态 2,过程 v_Report,行 2
Could not process object 'exec DBReportServer.dbo.sp_Report '2006-01-01 00:00:00','2006-01-01 23:59:59''. The OLE DB provider 'sqloledb' indicates that the object has no columns.
OLE DB error trace [Non-interface error: OLE DB provider unable to process object, since the object has no columnsProviderName='sqloledb', Query=exec DBReportServer.dbo.sp_Report '2006-01-01 00:00:00','2006-01-01 23:59:59',5'].
select * from PROCEDURE
好像sqlserver的帮助中没有说到可以这么用,一般都是exec PROCEDURE,学习学习select * from FUNCTION 是可以的,楼主不妨麻烦点,把sp_Report改写成一个返回table的函数,问题就解决了。
没有什么好办法,除非像你最开始说的,先追加到临时表,再select。SQL Server 2000对于Openrowset中执行存储过程的功能还有待加强。不知道2005是否有所改进。
将Select TOP 8000 ID=Identity(Int,0,1) Into #TempIdentity From syscolumns,SysObjects
替换为:
declare @t table(id int identity,n int)
insert @t(n) Select TOP 8000 NULL Into #TempIdentity From syscolumns,SysObjects
insert @t(n) Select TOP 8000 NULL Into #TempIdentity From syscolumns,SysObjects
执行这句话报错:into附近有语法错误执行这句话
Select TOP 8000 NULL Into #TempIdentity From syscolumns,SysObjects
报无法从函数中访问临时表
insert @t(n) Select TOP 8000 NULL From syscolumns,SysObjects
即把Into #TempIdentity 去掉试试.
在其作用域内,table 变量可像常规表那样使用。该变量可应用于 SELECT、INSERT、UPDATE 和 DELETE 语句中用到表或表的表达式的地方。但是,table 不能用在下列语句中:INSERT INTO table_variable EXEC 存储过程。SELECT select_list INTO table_variable 语句。在定义 table 变量的函数、存储过程或批处理结束时,自动清除 table 变量。(2)在存储过程中使用表变量与使用临时表相比,减少了存储过程的重新编译量。
(3)涉及表变量的事务只在表变量更新期间存在。这样就减少了表变量对锁定和记录资源的需求。
不支持在表变量之间进行赋值操作。另外,由于表变量作用域有限,并且不是持久数据库的一部分,因而不受事务回滚的影响。
GO
SELECT a.*
FROM OPENROWSET('SQLOLEDB','seattle1';'sa';'MyPass',
'SELECT * FROM pubs.dbo.authors ORDER BY au_lname, au_fname') AS a
GO
我用我写的存储过程成功了
FROM OPENROWSET('SQLOLEDB','HIS';'HIS';'MANAGER',
' exec his.ardata.his.bajk ''2006-01-01'',''2006-01-01'' ')
"Select TOP 8000 ID=Identity(Int,0,1) Into #TempIdentity From syscolumns,SysObjects"
何不建个固定表在数据库内? 把"#Temp"换成 "NumTable"这样建视图,一句就可以出来了.效率一定要比你的存储过程+视图高得多!方法是死的,人是活的.
SELECT * FROM openrowset('sqloledb', 'Trusted_Connection=yes','set fmtonly off exec sp_Report ''2006-01-01 00:00:00'',''2006-01-01 23:59:59'' set fmtonly on ')
===
按你方法试了 好像不行
= CSDN助手 全面支持CSDN论坛 =
= 监视、收藏、历史、签名走马灯 =
==================================