How to query a published  TDV (TIBCO Data Virtualization) procedure using a SQL Server Linked Server

How to query a published TIBCO Data Virtualization procedure using a SQL Server Linked Server

While Microsoft SQL Server displays published TDV views under a SQL Server Linked server, it does not display published TDV procedures However, the procedures can still be queried using Microsoft's OPENQUERY syntax as shown in the example below.

1.   Create a procedure in TDV.

           PROCEDURE p_add(IN a INTEGER , IN b INTEGER, OUT c INTEGER )
                BEGIN
                        set c = a + b;
               END 

2.   Publish the Procedure.



3.   Create a Linked Server TDVLINKEDSERVER in SQL Server Management Studio.



4.   Run a query in SQL Server Management Studio using the OPENQUERY syntax.

     SELECT * FROM OPENQUERY([ TDVLINKEDSERVER], 'SELECT * FROM p_add(2,15)')



Note that the procedure may not be displayed in SQL Server; however, it can still be executed.

If upon executing the procedure an error is displayed, the error is likely to be caused by RPC settings. Try changing the linked server's RPC settings under Properties >> Server Options  from false to true.