Microsoft Dynamics CRM is a prominent Client Relationship Management tool. QuerySurge requires a JDBC driver to connect to Dynamics CRM (as do other tools), and the need for a JDBC compliant driver has been filled by different commercial vendors. One commercial driver that QuerySurge users have used to connect is available from CData. Setup details for using the CData JDBC driver to connect to QuerySurge follow.
Note: RTTS, the vendor of QuerySurge, does not have distribution rights for this driver, and has no relationship with the driver vendor. If you download a driver, your license for the driver is with the driver vendor and not with RTTS. Support for this driver is not provided by RTTS.
Setting up a Connection to Dynamics CRM with the CData JDBC Driver
Connecting to Dynamics CRM with the CData JDBC driver is done using the Connection Extensibility feature of the QuerySurge Connection Wizard. What follows are the details you'll need to set up your QuerySurge Connection to Dynamics CRM with this driver.
- Download the driver from CData here.
- Deploy the Dynamics CRM JDBC driver to your Agent(s). The procedure for deploying a new driver to a QuerySurge Agent is here (for Agents on Windows) and here (for Agents on Linux).
Note: CData requires that you run their driver installer in order for their JDBC driver to function properly, so the CData installer will have to run on every Agent box that you plan to use the driver on. However, once the driver is installed (regardless of where it is installed), you can copy the driver file(s) to the standard QuerySurge Agent jdbc folder, per the directions in the links above.
- Log into QuerySurge as a QuerySurge Admin user, and navigate to the Admin view. Steps for using the Connection Extensibility feature can be found here. To use the Connection Extensibility option in the Connection Wizard with the CData driver, you'll need the following information:
Driver Class: cdata.jdbc.dynamicscrm.DynamicsCRMDriver
Connection URL: jdbc:dynamicscrm:CRM Version="<crmversion>";URL="https://<myOrg>.crm.dynamics.com/";
Note that you'll need to provide the Dynamics CRM URL with the proper values for the organization (in the "URL" key-value pair), the user, and the password. When you've entered your information, the Connection Wizard will look similar to this:
- If you have a Test Query for Dynamics CRM, feel free to enter it to help verify that your Connection parameters are correct. It should be a standard query that returns a small amount of information - one row/one column is enough. Make sure that your login has permission to query the table that you use.
Note: If you need debug information on your URL, you can set a log with a verbosity level by modifying your URL as follows (additions in bold):
jdbc:dynamicscrm:User=<myuseraccount>;Password=<mypassword>;URL=https://<myOrg>.crm.dynamics.com/;CRM Version=CRM Online;Logfile=C:\Log.txt;Verbosity=4
These additional settings dump logging information to the specified file, and can be helpful when you first attempt a connection. Once connection issues are resolved, it is advisable to remove the logging settings from the URL, to avoid generating large logs.
You may add any additional driver properties that may be necessary using this format. A full list of available properties for the CData JDBC Driver for Dynamics CRM can be found in the CData documentation.
- If you entered a Test Query, you can use the Test Connection button to test whether your Connection is set up properly:
- Click the Save button.
Once your driver is set up, you should be able to write SQL queries against your Dynamics CRM data in QuerySurge.
SQL syntax documentation for the Dynamics CRM CData JDBC driver can be reviewed here.