June 29, 2011 at 9:50 am
I'm attempting to call a stored procedure using openrowset() but it failes with the message
Cannot process the object "exec TEST.dbo.openrowset2". The OLE DB provider "SQLNCLI" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object.
I believe this is caused because the stored procedure creates and selects from a temporary table. Maybe it can't send the format of the result set?
Aside from fixing the original stored procedures temporary table usage, does anybody know how to fix/avoid this?
Code to reproduce problem:
CREATE DATABASE TEST
GO
USE TEST
GO
create table openrowsettest (
CUSTNO numeric( 10, 0 )
)
go
insert into openrowsettest ( CUSTNO ) VALUES ( 1 )
insert into openrowsettest ( CUSTNO ) VALUES ( 2 )
insert into openrowsettest ( CUSTNO ) VALUES ( 3 )
insert into openrowsettest ( CUSTNO ) VALUES ( 4 )
insert into openrowsettest ( CUSTNO ) VALUES ( 5 )
insert into openrowsettest ( CUSTNO ) VALUES ( 6 )
insert into openrowsettest ( CUSTNO ) VALUES ( 7 )
insert into openrowsettest ( CUSTNO ) VALUES ( 8 )
insert into openrowsettest ( CUSTNO ) VALUES ( 9 )
insert into openrowsettest ( CUSTNO ) VALUES ( 0 )
insert into openrowsettest ( CUSTNO ) VALUES ( 1 )
insert into openrowsettest ( CUSTNO ) VALUES ( 2 )
insert into openrowsettest ( CUSTNO ) VALUES ( 3 )
go
create proc openrowset1
as
begin
select top 100 * from openrowsettest
end
GO
CREATE proc openrowset2
as
begin
declare @custnos table (CUSTNO numeric (10,0 ))
insert into @custnos
select top 100 CUSTNO from dbo.openrowsettest
select * from @custnos
end
go
create proc openrowset3
as
begin
create table #custnos (
CUSTNO numeric( 10, 0 )
)
insert into #custnos
select top 100 CUSTNO from dbo.openrowsettest
select * from #custnos
end
go
create proc openrowset4
as
begin
exec openrowset3
end
go
create proc openrowset5
as
begin
exec openrowset1
end
go
-- works
select * from openrowset( 'SQLOLEDB', 'Server=(local);TRUSTED_CONNECTION=yes;','exec TEST.dbo.openrowset1')
go
-- don't work
select * from openrowset( 'SQLOLEDB', 'Server=(local);TRUSTED_CONNECTION=yes;','exec TEST.dbo.openrowset2')
go
-- don't work
select * from openrowset( 'SQLOLEDB', 'Server=(local);TRUSTED_CONNECTION=yes;','exec TEST.dbo.openrowset3')
go
-- don't work
select * from openrowset( 'SQLOLEDB', 'Server=(local);TRUSTED_CONNECTION=yes;','exec TEST.dbo.openrowset4')
go
-- works
select * from openrowset( 'SQLOLEDB', 'Server=(local);TRUSTED_CONNECTION=yes;','exec TEST.dbo.openrowset5')
go
use master
drop DATABASE TEST
June 29, 2011 at 12:45 pm
Try this please and see what happens:
create proc openrowset4
as
begin
if 1=0 -- no-op
begin
select top 100 * from openrowsettest where 1=0
end
exec openrowset3
end
go
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 29, 2011 at 1:51 pm
Thanks, but same result as last time.
Oddly, if you add 'set nocount on' to the procedures, all but the procedures that deal with #temp tables work.
CREATE DATABASE TEST
GO
USE TEST
GO
create table openrowsettest (
CUSTNO numeric( 10, 0 )
)
go
insert into openrowsettest ( CUSTNO ) VALUES ( 1 )
insert into openrowsettest ( CUSTNO ) VALUES ( 2 )
insert into openrowsettest ( CUSTNO ) VALUES ( 3 )
insert into openrowsettest ( CUSTNO ) VALUES ( 4 )
insert into openrowsettest ( CUSTNO ) VALUES ( 5 )
insert into openrowsettest ( CUSTNO ) VALUES ( 6 )
insert into openrowsettest ( CUSTNO ) VALUES ( 7 )
insert into openrowsettest ( CUSTNO ) VALUES ( 8 )
insert into openrowsettest ( CUSTNO ) VALUES ( 9 )
insert into openrowsettest ( CUSTNO ) VALUES ( 0 )
insert into openrowsettest ( CUSTNO ) VALUES ( 1 )
insert into openrowsettest ( CUSTNO ) VALUES ( 2 )
insert into openrowsettest ( CUSTNO ) VALUES ( 3 )
go
create proc openrowset1
as
begin
select top 10 * from openrowsettest
end
GO
CREATE proc openrowset2
as
begin
SET NOCOUNT ON
declare @custnos table ( CUSTNO numeric (10,0 ))
insert into @custnos
select top 10 CUSTNO from dbo.openrowsettest
select * from @custnos
end
go
create proc openrowset3
as
begin
SET NOCOUNT ON
create table #custnos (
CUSTNO numeric( 10, 0 )
)
insert into #custnos
select top 10 CUSTNO from dbo.openrowsettest
select * from #custnos
end
go
create proc openrowset4
as
begin
SET NOCOUNT ON
exec openrowset3
end
go
create proc openrowset5
as
begin
SET NOCOUNT ON
exec openrowset1
end
go
create proc openrowset6
as
begin
SET NOCOUNT ON
if 1=0 -- no-op
begin
select top 10 * from openrowsettest where 1=0
end
exec openrowset3
end
go
create proc openrowset7
as
begin
SET NOCOUNT ON
select top 10 CUSTNO into #custnos from dbo.openrowsettest
select * from #custnos
end
go
-- works
select * from openrowset( 'SQLOLEDB', 'Server=(local);TRUSTED_CONNECTION=yes;','exec TEST.dbo.openrowset1')
go
-- works with set no count
select * from openrowset( 'SQLOLEDB', 'Server=(local);TRUSTED_CONNECTION=yes;','exec TEST.dbo.openrowset2')
go
-- don't work
select * from openrowset( 'SQLOLEDB', 'Server=(local);TRUSTED_CONNECTION=yes;','exec TEST.dbo.openrowset3')
go
-- don't work
select * from openrowset( 'SQLOLEDB', 'Server=(local);TRUSTED_CONNECTION=yes;','exec TEST.dbo.openrowset4')
go
-- works
select * from openrowset( 'SQLOLEDB', 'Server=(local);TRUSTED_CONNECTION=yes;','exec TEST.dbo.openrowset5')
go
-- works with set no count
select * from openrowset( 'SQLOLEDB', 'Server=(local);TRUSTED_CONNECTION=yes;','exec TEST.dbo.openrowset6')
go
select * from openrowset( 'SQLOLEDB', 'Server=(local);TRUSTED_CONNECTION=yes;','exec TEST.dbo.openrowset7')
go
use master
drop DATABASE TEST
June 29, 2011 at 2:04 pm
I think we narrowed it down...here is one more update...only the procs that make direct use of temp tables need the no-op:
CREATE DATABASE test_openrowset
GO
USE test_openrowset
GO
create table openrowsettest (
CUSTNO numeric( 10, 0 )
)
go
insert into openrowsettest ( CUSTNO ) VALUES ( 1 )
insert into openrowsettest ( CUSTNO ) VALUES ( 2 )
insert into openrowsettest ( CUSTNO ) VALUES ( 3 )
insert into openrowsettest ( CUSTNO ) VALUES ( 4 )
insert into openrowsettest ( CUSTNO ) VALUES ( 5 )
insert into openrowsettest ( CUSTNO ) VALUES ( 6 )
insert into openrowsettest ( CUSTNO ) VALUES ( 7 )
insert into openrowsettest ( CUSTNO ) VALUES ( 8 )
insert into openrowsettest ( CUSTNO ) VALUES ( 9 )
insert into openrowsettest ( CUSTNO ) VALUES ( 0 )
insert into openrowsettest ( CUSTNO ) VALUES ( 1 )
insert into openrowsettest ( CUSTNO ) VALUES ( 2 )
insert into openrowsettest ( CUSTNO ) VALUES ( 3 )
go
create proc openrowset1
as
begin
select top 10 * from openrowsettest
end
GO
CREATE proc openrowset2
as
begin
SET NOCOUNT ON
declare @custnos table ( CUSTNO numeric (10,0 ))
insert into @custnos
select top 10 CUSTNO from dbo.openrowsettest
select * from @custnos
end
go
create proc openrowset3
as
BEGIN
SET NOCOUNT ON
IF 1=0
BEGIN
SELECT CAST ( NULL AS numeric( 10, 0 )) AS CUSTNO
END
create table #custnos (
CUSTNO numeric( 10, 0 )
)
insert into #custnos
select top 10 CUSTNO from dbo.openrowsettest
select * from #custnos
end
go
create proc openrowset4
as
begin
SET NOCOUNT ON
exec openrowset3
end
go
create proc openrowset5
as
begin
SET NOCOUNT ON
exec openrowset1
end
go
create proc openrowset6
as
begin
SET NOCOUNT ON
exec openrowset3
end
go
create proc openrowset7
as
begin
SET NOCOUNT ON
IF 1=0
BEGIN
SELECT CAST ( NULL AS numeric( 10, 0 )) AS CUSTNO
END
select top 10 CUSTNO into #custnos from dbo.openrowsettest
select * from #custnos
end
go
-- works
select * from openrowset( 'SQLOLEDB', 'Server=.\STD2008R2;TRUSTED_CONNECTION=yes;','exec test_openrowset.dbo.openrowset1')
go
-- works with set no count
select * from openrowset( 'SQLOLEDB', 'Server=.\STD2008R2;TRUSTED_CONNECTION=yes;','exec test_openrowset.dbo.openrowset2')
go
-- works
select * from openrowset( 'SQLOLEDB', 'Server=.\STD2008R2;TRUSTED_CONNECTION=yes;','exec test_openrowset.dbo.openrowset3')
go
-- works
select * from openrowset( 'SQLOLEDB', 'Server=.\STD2008R2;TRUSTED_CONNECTION=yes;','exec test_openrowset.dbo.openrowset4')
go
-- works
select * from openrowset( 'SQLOLEDB', 'Server=.\STD2008R2;TRUSTED_CONNECTION=yes;','exec test_openrowset.dbo.openrowset5')
go
-- works with set no count
select * from openrowset( 'SQLOLEDB', 'Server=.\STD2008R2;TRUSTED_CONNECTION=yes;','exec test_openrowset.dbo.openrowset6')
go
select * from openrowset( 'SQLOLEDB', 'Server=.\STD2008R2;TRUSTED_CONNECTION=yes;','exec test_openrowset.dbo.openrowset7')
go
use master
drop DATABASE test_openrowset
GO
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 29, 2011 at 3:17 pm
Thanks!
This works, although it sounds like a pain to have to create that no-op just to set the meta data.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply