How to make a published SQL script of TIBCO Data Virtualization accessible from SQL Server Management Studio using Linked Server?
Article ID: KB0073487
Updated On:
| Products | Versions |
|---|---|
| TIBCO Data Virtualization | 8.2 and higher |
Description
How to make a published SQL script of TIBCO Data Virtualization accessible from SQL Server Management Studio using Linked Server?
Issue/Introduction
How to make a published SQL script of TIBCO Data Virtualization accessible from SQL Server Management Studio using Linked Server?
Environment
Linux,Windows
Resolution
Follow the steps to achieve this use-case:
1. Open SQL Server Management Studio.
2. Right-click on Linked Server, Click on Properties, browse to the Server Options page & set the following properties to "True":
3. From the examples folder publish the LookupProduct procedure.
Note: SQL Server Management Studio does not display published scripts of TDV under linked server however, we can query published scripts
4. Right click on Linked Server you have created and click "Script Linked Server as=> CREATE To => New Query Editor Window"
5. We can now execute select query on LookupProduct SQL script as:
SELECT * from OPENQUERY(<TDVLinkedServerName>, 'select * from LookupProduct(2)');
1. Open SQL Server Management Studio.
2. Right-click on Linked Server, Click on Properties, browse to the Server Options page & set the following properties to "True":
1. Data Access
2. RPC
3. RPC Out
4. Use Remote Collation
5. Enable Promotion of Distributed Transaction for RPC
Note: Refer the attached screenshot "LinkedServerProperties_ServerOptions" 2. RPC
3. RPC Out
4. Use Remote Collation
5. Enable Promotion of Distributed Transaction for RPC
3. From the examples folder publish the LookupProduct procedure.
Note: SQL Server Management Studio does not display published scripts of TDV under linked server however, we can query published scripts
4. Right click on Linked Server you have created and click "Script Linked Server as=> CREATE To => New Query Editor Window"
5. We can now execute select query on LookupProduct SQL script as:
SELECT * from OPENQUERY(<TDVLinkedServerName>, 'select * from LookupProduct(2)');
Attachments
Feedback
Yes
No
Powered by
