0

I'm trying to debug an application issue and I need to compare the results of two stored procedures. From digging around on here I found out about OPENROWSET, which works great when the params can be hard coded but when the param is a table valued parameter, I get an error. Here's a simple example that reproduces my problem.

 create type mytype as table ( id int )
 go
 create procedure myproctvp
    @mytype mytype readonly
as
    select * from sys.objects
    inner join @mytype on id = object_id
go
create procedure myproc
    @p1 int 
as
    select * from sys.objects
    where @p1 = object_id
go



SELECT * INTO #mine FROM OPENROWSET('SQLNCLI', 'Server=.;Trusted_Connection=yes',
     'EXEC mydb.dbo.myproc 846')
     go

SELECT * INTO #minetvp FROM OPENROWSET('SQLNCLI', 'Server=.;Trusted_Connection=yes;',
     'use mydb; declare @tvp mytype; insert into @tvp values (345645); exec mydb.dbo.myproctvp @mytype=@tvp')

Results of first query:

(0 row(s) affected)

Second one fails:

Msg 7357, Level 16, State 2, Line 2
Cannot process the object "use mydb; declare @tvp mytype; insert into @tvp values (345645); exec mydb.dbo.myproctvp @mytype=@tvp". The OLE DB provider "SQLNCLI10" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object.

EDIT:

Doing it this way...

create proc doTVP as
declare @dt mytype
insert into @dt values (163)
exec myproctvp @dt  
go

SELECT * INTO #minetvp FROM OPENROWSET('SQLNCLI', 'Server=.;Trusted_Connection=yes;',
     'exec mydb.dbo.doTVP')

...doesn't work either. I get the same message.

EDIT:

Getting a little closer with this approach:

SELECT * INTO #minetvp FROM OPENROWSET('SQLNCLI', 'Server=.;Trusted_Connection=yes;',
 'create type mydb..mytype as table (id int);
 declare @tvp mytype; 
 insert into @tvp values (345645); 
 exec sp_executesql "mydb.dbo.myproctvp", "@tvp mytype readonly", @tvp')

Now the error I get is:

OLE DB provider "SQLNCLI10" for linked server "(null)" returned message "Deferred prepare could not be completed.".
Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
Msg 117, Level 15, State 2, Line 1
The type name 'mydb..mytype' contains more than the maximum number of prefixes. The maximum is 1.

Anybody got any ideas?

stardotstar
  • 318
  • 2
  • 18
  • Do you *need* to use `OPENROWSET`? Could you populate temp tables using `INSERT ... EXEC` and then just compare the tables? – Damien_The_Unbeliever May 01 '14 at 12:48
  • but i'd have to do the table definition and there's 30 or 40 columns being returned by these sprocs so trying to avoid that really. – stardotstar May 01 '14 at 12:51
  • but now that i think about it, i can temporarily kludge the sprocs to return only the columns i'm interested in so that'll do i guess. thanks. – stardotstar May 01 '14 at 12:58

0 Answers0