You may need to reference data held outside of Sage CRM and within another application's database for a variety of business requirements including

• Reporting
• Graphing

Reports within CRM are based on views and so if the SQL within a view can provide a way of accessing data in an external database then that external data can be reported on within Sage CRM. I am assuming here use of MS SQL Server.

To do this within a view the SQL would need to use the fully qualified four-part identifier syntax.

select * from [ServerName].[database name].[user name].table name

As the square brackets show, different parts of this syntax can be omitted, as long as you supply enough to unambiguously identify what you're attempting to reference. Depending on where the SQL orginates the following all may provide enough information to reference the Employees table within a "Northwind" database.

richardsj-lt.northwind.dbo.employee
northwind.dbo.employee
northwind..employee
employee

By default, the context of all objects is the local database where your SQL statements are executing, so within CRM when wanting to refer to an external database table in a view it would typically look like:

select vusers.user_firstname,vusers.user_lastname, northwind..orders.* from vusers
left join northwind.dbo.orders on user_userid = employeeid;

For the most part, you can get by with three-part names, omitting the server name - unless you're dealing with objects on a linked server. In the example above the Northwind database is on the same server.

Note: You may need to link to the external table within CRM to avoid meta data problems.

But the usage of the external database data in Views is NOT strictly dependent on the external database being described in meta data. (Although it is advisable).

Consider the requirement to report the names of users accessing different systems. We do not need the connection to the external system to be described in meta data. As you can see for the image below there is no link to the Northwind database.

The view can then be built under

Administration -> Customization -> Users

image: userview.png

The SQL used in this case has unioned the information from the two different database tables.

select user_userid, user_firstname, user_lastname, user_location from users where user_resource = 'false' and user_istemplate = 'N'
union
select null, firstname as user_firstname, lastname as user_lastname, city as user_location from northwind.dbo.employees where lastname is not null

A report can then be built that uses the view

Note: If your view includes entities that are covered by Sage CRM security (Opportunity, Company, Lead etc) then you will need to test the reporting with all users to ensure the view includes the correct field information. See the article "Sage CRM and Security Territories".

See also the article "Database Case-Sensitivity and Connecting to an External Case Sensitive Database".