%VOSWARNING%
%META:TOPICPARENT{name="VirtLinkRemoteTables"}%

---+ Linking Microsoft SQL Server Database Objects into Virtuoso

---++ Prerequisites

The following must be in place to complete this exercise.

   * <b>Virtuoso Universal Server, Commercial Edition</b>%BR%
Virtuoso Universal Server Release 6.x or later is recommended, optimally the latest version available.%BR%%BR%
   * <b>ODBC Driver for Microsoft SQL Server</b>%BR%
The Virtuoso Server uses an ODBC Driver for Microsoft SQL Server to link in the Microsoft SQL Server Schema Objects. The <b><nop>OpenLink Multi-Tier ODBC Driver for Microsoft SQL Server</b> will be used in this document, and a functional ODBC Data Source Name (DSN) of <b><code>sql25ma</code></b> will be assumed to exist on the machine hosting the Virtuoso Server.  Any ODBC compliant driver for Microsoft SQL Server should work.%BR%%BR%
   * <b>Microsoft SQL Server DBMS</b>%BR%
A Microsoft SQL Server DBMS hosting the required Schema Objects needs to be available. This may be on any host which is TCP/IP accessible from the Virtuoso Server host. In this document, the Microsoft SQL Server <b>Northwind</b> sample database will be used to demonstrate the process.%BR%%BR%

---++ Steps

   1 Load the HTTP-based Virtuoso User Interface in your browser.
%BR%%BR%<a href="%ATTACHURLPATH%/VirtWebHome.png"><img src="%ATTACHURLPATH%/VirtWebHome.png" style="wikiautogen" width="600px" alt="" /></a>%BR%%BR%
   1 Click the <b>Conductor</b> link in the left-side navbar, and log in as a user with DBA privileges, such as the default <b><code>dba</code></b> user.
%BR%%BR%<a href="%ATTACHURLPATH%/VirtConductor.png"><img src="%ATTACHURLPATH%/VirtConductor.png" style="wikiautogen" width="600px" alt="VirtConductor.png" /></a>%BR%%BR%
   1 Drill down to the <b>Databases</b> -> <b>External Data Source</b> -> <b>Data Sources</b> tab.
%BR%%BR%<a href="%ATTACHURLPATH%/VirtLinkSQL01.png"><img src="%ATTACHURLPATH%/VirtLinkSQL01.png" style="wikiautogen" width="600px" alt="VirtLinkSQL01.png" /></a>%BR%%BR%
   1 Locate the DSN for the desired remote data source (we're using <b><code>sql25ma</code></b> for this example).  If not yet connected to the 
DSN, click <b>Connect</b>, and input your user credentials for the remote data source.
%BR%%BR%<a href="%ATTACHURLPATH%/VirtLinkSQL02.png"><img src="%ATTACHURLPATH%/VirtLinkSQL02.png" style="wikiautogen" width="600px" alt="VirtLinkSQL02.png" /></a>%BR%%BR%
   1 Once connected to the DSN, click the associated <b>Link objects</b> to get a list of available objects (tables, views, stored procedures).
%BR%%BR%<a href="%ATTACHURLPATH%/VirtLinkSQL03.png"><img src="%ATTACHURLPATH%/VirtLinkSQL03.png" style="wikiautogen" width="600px" alt="VirtLinkSQL03.png" /></a>%BR%%BR%
   1 Locate and select the desired objects.  
%BR%%BR%<a href="%ATTACHURLPATH%/VirtLinkSQL04.png"><img src="%ATTACHURLPATH%/VirtLinkSQL04.png" style="wikiautogen" width="600px" alt="VirtLinkSQL04.png" /></a>%BR%%BR%
      * You can use the Catalog and Schema drop-down selectors and the table name test-entry box to construct a filter to shorten the visible list.  
      * For this example, we'll choose the main tables from Microsoft's sample <b>Northwind.dbo</b> schema -- <nowiki><b><code>Categories</code>, <code>CustomerCustomerDemo</code>, <code>CustomerDemographics</code>, <code>Customers</code>, <code>Employees</code>, <code>EmployeeTerritories</code>, <code>Order Details</code>, <code>Orders</code>, <code>Products</code>, <code>Region</code>, <code>Shippers</code>, <code>Suppliers</code>, <code>Territories</code></b>.</nowiki> 
      * When all desired objects are selected, click the <b>Link...</b> button.
   1 Virtuoso will interrogate the remote data source to learn about primary keys, and present you with a default linkage setup.
%BR%%BR%<a href="%ATTACHURLPATH%/VirtLinkSQL05.png"><img src="%ATTACHURLPATH%/VirtLinkSQL05.png" style="wikiautogen" width="600px" alt="VirtLinkSQL05.png" /></a>%BR%%BR%
   1 You can adjust the Virtuoso catalog, schema/owner, and object identifiers to suit your goals. The <b>Set To All</b> button can be used to speed the process.  In this example, we'll put all linked objects into the <b><code>sqlserver</code></b> catalog, and the <b><code>northwind</code></b> schema.  We'll leave the object identifiers unchanged.  
%BR%%BR%<a href="%ATTACHURLPATH%/VirtLinkSQL06.png"><img src="%ATTACHURLPATH%/VirtLinkSQL06.png" style="wikiautogen" width="600px" alt="VirtLinkSQL06.png" /></a>%BR%%BR%
      * Sometimes a primary key is not reported properly, or you want to use a different key, either simple or compound.  Click <b>Edit</b> next to the primary key you want to change, and select the columns that Virtuoso should use for the purpose --
%BR%%BR%<a href="%ATTACHURLPATH%/VirtLinkSQL07.png"><img src="%ATTACHURLPATH%/VirtLinkSQL07.png" style="wikiautogen" width="600px" alt="VirtLinkSQL07.png" /></a>%BR%%BR%
   1 When all are set as desired, click the <b>Link</b> button to link the objects into Virtuoso.  <i>Virtuoso may take some time to complete this step,</i> depending on network infrastructure, DBMS load, etc.
   1 The Link process will complete by listing the linked objects in the <b>Databases</b> -> <b>External Data Source</b> -> <b>External Linked Objects</b> tab.
%BR%%BR%<a href="%ATTACHURLPATH%/VirtLinkSQL08.png"><img src="%ATTACHURLPATH%/VirtLinkSQL08.png" style="wikiautogen" width="600px" alt="VirtLinkSQL08.png" /></a>%BR%%BR%

---++ Confirmation and Testing

---+++ Working with External Linked Objects

   1 Linked object are shown in the <b>Databases</b> -> <b>External Data Source</b> -> <b>External Linked Objects</b> tab.
%BR%%BR%<a href="%ATTACHURLPATH%/VirtLinkSQL08.png"><img src="%ATTACHURLPATH%/VirtLinkSQL08.png" style="wikiautogen" width="600px" alt="VirtLinkSQL08.png" /></a>%BR%%BR%
   1 Clicking the hyperlinked <b>Local Name</b> of the <b>External Linked Objects</b> will load the Virtuoso <b>Interactive SQL</b> (iSQL) HTTP interface (in a new browser tab or window) with a default <b><code>SELECT *</code><b> SQL query. 
For instance, clicking on <b><code>sqlserver.northwind.Employees</code></b> brings --
%BR%%BR%<a href="%ATTACHURLPATH%/VirtLinkSQL09.png"><img src="%ATTACHURLPATH%/VirtLinkSQL09.png" style="wikiautogen" width="600px" alt="VirtLinkSQL09.png" /></a>%BR%%BR%
   1 Click the <b>Execute</b> button to run the query.  Virtuoso will retrieve the results from the remote table.  <i><b>Note:</b> By default, iSQL will "<b>Show no more than 100 rows </b>."  You can change this value as desired.</i>
%BR%%BR%<a href="%ATTACHURLPATH%/VirtLinkSQL10.png"><img src="%ATTACHURLPATH%/VirtLinkSQL10.png" style="wikiautogen" width="600px" alt="VirtLinkSQL10.png" /></a>%BR%%BR%

---+++ Working with SQL Database Objects

   1 Linked objects are also visible through the <b>Database</b> -> <b>SQL Database Object</b> tab, just as if they were local Virtuoso data.  
%BR%%BR%<a href="%ATTACHURLPATH%/VirtLinkSQL11.png"><img src="%ATTACHURLPATH%/VirtLinkSQL11.png" style="wikiautogen" width="600px" alt="VirtLinkSQL11.png" /></a>%BR%%BR%
   1 Simply drill down to the Catalog you set while linking --
%BR%%BR%<a href="%ATTACHURLPATH%/VirtLinkSQL12.png"><img src="%ATTACHURLPATH%/VirtLinkSQL12.png" style="wikiautogen" width="600px" alt="VirtLinkSQL12.png" /></a>%BR%%BR%
   1 As you might expect, clicking on any of the <b>Action</b> links will reveal the table definition, indexes, triggers, constraints, or privileges.  For example, clicking <b>Definition</b> for <b><code>sqlserver.northwind.Employees</code></b> --
%BR%%BR%<a href="%ATTACHURLPATH%/VirtLinkSQL13.png"><img src="%ATTACHURLPATH%/VirtLinkSQL13.png" style="wikiautogen" width="600px" alt="VirtLinkSQL13.png" /></a>%BR%%BR%


---++ Related

   * [[VOSSQL2RDF][Mapping SQL Data to Linked Data Views]]
   * [[VirtMSSQLServerEntityFrameworkUsage][Using Microsoft Entity Frameworks to Access Microsoft SQL Server Schema Objects with Virtuoso]]


CategoryDocumentation CategoryVirtuoso CategoryDotNET CategoryEntityFrameworks CategoryWebSite