openrowset is giving error "OLE DB provider "SQLNCLI10" for linked server "(null)" returned message "Deferred prepare could not be completed."

1
votes

I have a table which is passed into stored procedure. now i want to insert stored procedure results in temp table #b using 'select * into #b from openrowset. My sql code is as follows:

create type add1 as table
(score int)

alter proc qwe123
@table add1 readonly
as
begin
select score,score + 2 as cal
from @table
end

go

declare @t add1 
insert into @t
select score
from abc

select * into #b from
openrowset('sqlncli','server=localhost;trusted_connection=yes','exec qwe123 @t')

Can someone tell me why am I getting error as :

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 137, Level 15, State 2, Line 1

Must declare the scalar variable "@t".

1
sql-server-2008

1 Answers

1
votes

You have to declare your variable @t with the execution statement:

openrowset('sqlncli','server=localhost;trusted_connection=yes',
    'DECLARE @t add1 = 1 exec qwe123 @t')