Menu

Creating your own ConnectWise database reference

Google+0
LinkedIn0

21 Apr 2014: Check out the most recent post on this topic.

When I develop custom ConnectWise reports and applications that connect to the ConnectWise database, I frequently find myself launching SQL Server Management Studio and navigating through the long list of tables in order to answer a simple question like which fields are available in the Company table.

To save myself time, I built a down and dirty database reference tool using a simple SQL query and Excel. While there’s a ton of information that one could reference, but for my purposes, I just needed a list of tables, all of their fields and some basic information about data types.

To start, I first built a simple SQL query that retrieves the schema information for the ConnectWise database. Here’s the code:

SELECT     sys.objects.name AS [Table Name], sys.columns.name AS [Column Name], sys.types.name AS [Data Type], sys.columns.max_length AS Length,
                      sys.columns.is_nullable AS [Allow Nulls], sys.columns.is_identity AS [Identity]
FROM         sys.objects INNER JOIN
                      sys.columns ON sys.objects.object_id = sys.columns.object_id INNER JOIN
                      sys.types ON sys.columns.user_type_id = sys.types.user_type_id
WHERE     (sys.objects.type = 'u')
ORDER BY [Table Name]

The next question was what to do with the information it returned. I briefly thought about building a simple .NET application, but decided Excel was a better solution, with it’s ability to query SQL and built-in sorting and filtering capabilities (which become important when working with more than 500 tables, and 6,000 columns).

You can certainly build your own tool to work with the schema information by using the SQL query I’ve included above, or you can download the ConnectWise Database Reference I built. You may receive a few errors when opening the XLS file for the first time. To fix these go to the Data tab, click the Connections button, select ConnectWise Database from the list of connections and click the Properties button. The click the Definition tab and edit the Connection String:

Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;
Initial Catalog=cwwebapp_nexnow;Data Source=SQLSERVER;
Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;

Be sure to update the connection string to reference the name of your ConnectWise database (Initial Catalog) as well as your SQL server (Data Source). Click the OK button to save your changes, then click the Refresh All button from the Data tab.

The other option is to just remove the connection altogether and use this as a static reference guide. But do keep in mind that it will become outdated over time as ConnectWise release updates to the application.

Related Posts