/quickstart
QUICK START
SQL Commands Primer
Login
Because Enzo Server emulates parts of the TDS protocol, you can connect to Enzo just like you would connect to a SQL Server database: by providing the IP address (or server name) and port of Enzo Server, and a login account/password. In addition, since Enzo Server emulates SQL Server, it has its own 'sa' account/password as well. Last but not least you can create additional logins using Enzo Manager, the management interface for Enzo Server.NOTE: Starting with Enzo Server version 2.1 you can login with a Windows / AD account
Enter Enzo's IP Address and port (IP,Port) in the Server Name field; this can also
be a server name (localhost if Enzo runs locally).
WARNING: Do not enter a SQL Server name; this must be an Enzo Server.
Keep the SQL Server Authentication option for this test, and enter the 'sa' account and password that you provided during the installation of Enzo. If you have created other logins with the Enzo Manager, you can use those credentials here.
The Standard edition of Enzo Server only allows the use of the 'sa' account
Click on Connect and you will be connected to an Enzo Server instance.
Run the Help command
Once logged in to Enzo Server, you can start running SQL commands. Let's start with a command unique to Enzo Server: the help command. Enzo Server provides an online help mechanism allowing you to explore what you can do. Let's discover the list of adapters that are loaded on the Enzo Server you are connected to by executing this command:
EXEC HelpBy default, all commands execute against the BSC database, the default database for an Enzo Server, so the above command is equivalent to EXEC bsc.help
The output of this command shows the list of adapters currently loaded in Enzo Server. Your output may show a different list. If you are not seeing an adapter you expected to see in this list, the adapter failed to load and you should look at the Enzo log or the Windows Event Log to see why.
You could run this command to explore the SharePoint adapter:
EXEC sharepoint.helpHowever, let's use the _handlers table so we can use a SELECT command that we can further filter:
SELECT * FROM SharePoint._handlers
In Enzo, a handler is equivalent to an Object in SQL Server; it is usually a
Stored Procedure and/or a Table. Some of the Procedures can be called as a Table using
a SELECT statement providing filtering and ordering options that the Procedure equivalent
may not provide.
For example, the GetListItemsEx procedure found in the SharePoint adapter is the handler
that can be used with an EXEC command to return list items from SharePoint. If you look at the
'SELECT' column of the _handlers output you will see 0; this means this specific handler
does not support a SELECT equivalent.
To figure out how to call the GetListItemsEx handler, and see what input parameters and
returned columns are, run this command:
EXEC SharePoint.GetListItemsEx help
Let's look at the lists handler; its Procedure and TableName are the same
and it is a SELECT operator. This means we can call this handler this way:
SELECT * FROM SharePoint.lists
Note that this command will only succeed if you have configured the Enzo Adapter to connect to a SharePoint site. The simplest way to configure Enzo adapters is to use Enzo Manager.
To configure Enzo adapters using SQL commands directly you can use the _configCreate handler. For example to see the list of parameters needed to create a SharePoint connection, use this command:
EXEC sharepoint._configCreate help
Enzo Manager
When you start Enzo Manager you must first login; you can only login using an administrative account into Enzo Manager (the 'sa' account). Once logged in, you will see a list of adapters loaded in the Enzo Server you connected to. To configure an adapter, click on the adapter name. For example clicking on the SharePoint adapter brings up the configuration section of SharePoint.
Create a Configuration Setting
In order to communicate with underlying services most adapters require creating at least one configuration setting. Each login defined in Enzo Manager can optionally have one default configuration setting that will automatically be loaded when running SQL commands.
The Standard edition of Enzo Server only provides the 'sa' account
In this screenshot you can see the SharePoint configuration settings defined for the 'sa' account.
From this screen you can create multiple configuration settings for a login. However only one setting can be marked as the default configuration. The user interface allows you to copy settings from a login to another. Changes to configuration settings take effect immediately even for currently logged in users.
The first configuration in the list was named _default, although it could have been given any other name. The star (*) next to the configuration name means this is the configuration that is automatically loaded when the user 'sa' logs in.
To create a configuration setting, simply click on the NEW icon and enter the necessary settings.
If you do not have a default configuration setting, or would like to switch the configuration setting to use during a session, use the _configUse SQL command. Note that using Linked Server to call Enzo requires the use of a default configuration setting.
View Adapter Activity Log
Each adapter stores its own log information to disk in a specific directory. Enzo Manager allows you to view the current day's worth of activity log which includes successful and failed calls. To view the log, click on Environment->View Current Adapter Log
This screen provides a view into the current adapter log (SharePoint in this case). You can choose another adapter if desired, and filter by message severity.
Create a View
Some adapters allow you to create a view that defines a schema for a remote object, such as a SalesForce table or a SharePoint List. Doing so makes is much easier to query remote objects using a natural SELECT syntax and allows you to query those remote objects through Linked Server.
Most views in Enzo allow both read and write operations (SELECT, UPDATE, DELETE, INSERT).
The usStatesTest view shown here points to the US States SharePoint list, and defines a subset of the fields available from the view. Clicking on the ellipses near the SQL Command allows you to see the command that will be executed against Enzo to return the data.
Editing the SQL Command allows you to select the columns you would like to return and see the actual command Enzo will be executing behind the scenes. You can also run a test query to return a single row so you can test the view.
Enzo Manager simplifies the creation of views through a simple user interface.
However you can also programmatically create views using the _createVirtualTable
handler on the adapter. For example for the SharePoint adapter the call would look
like this:
EXEC SharePoint._createVirtualTable@SPObject 'usStatesTest', 'US States', 'int ID,string Title,...', 'sa'
Calling the View with SQL
Now that the view has been created you can simply call the view directly as such:
SELECT * FROM SharePoint.usStatesTest
As mentioned previously, Enzo views can be used to modify data; in this screenshot the SharePoint list is first updated, then the SELECT statement returns the data from SharePoint in real-time.
Calling the view through Linked Server
One of the main benefits of using Enzo is that most of the functionality of the adapters is available through Linked Server. This means that you can leverage the power of T-SQL to program processes and integrate your systems.
Assuming you have created a linked server to Enzo called localhost,9550 and you are connected to a SQL Server database, you can run the following command:
SELECT * FROM [localhost,9550].bsc.sharepoint.usStatesTest
By leveraging Linked Server you can also call Enzo adapters from Stored Procedures, Views, Triggers, Functions and even from SQL Server Agent jobs.
See the Linked Server section for more information.