Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

linked server return from Oracle into table variable Expand / Collapse
Author
Message
Posted Tuesday, December 3, 2013 1:55 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, August 8, 2014 1:01 PM
Points: 61, Visits: 207
<<previously posted to wrong forum>
I'm trying to return the contents of a select statement on Oracle into a table variable in SQL Server stored procedure. I can get the query to return a single value into multiple variables, but getting a result set to return to a table variable has been tricky.

DECLARE @vlparent varchar(10)
,@sqlstring nvarchar(4000);

DECLARE @rset TABLE
(ln varchar(64)
,fn varchar(64)
,bd varchar(10)
,vlname varchar(10)
);

SET @sqlstring = N' DECLARE CURSOR mb IS SELECT CUST_LASTNAME, CUST_FIRSTNAME,CUST_DOB,VOL_NAME
FROM ORASCHEMA.TABNAME WHERE VOL_PARENT = ?;
TYPE mb_array IS TABLE OF mb%ROWTYPE;
cur_array mb_array;
BEGIN OPEN mb;
LOOP
FETCH mb BULK COLLECT INTO cur_array LIMIT 1000;
EXIT WHEN mb%NOTFOUND;
END LOOP;
CLOSE mb;
END;'
EXEC(@sqlstring,@vlparent,@rset OUTPUT)AT ORA_LINKSERVER

With this method I receive an error about undefined scalar value. With a INSERT INTO ..EXEC receive an error about MSDTC not recognizing my server. Any ideas?
Post #1519404
Posted Wednesday, December 4, 2013 11:15 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, August 8, 2014 1:01 PM
Points: 61, Visits: 207
Went about it in a completely different fashion and solved it.
Note, EXEC <stmt> AT far_server didn't yield what was needed. Had to make dynamic sql with OPENQUERY.
Post #1519732
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse