Suppose that you have two servers (CRM and GP) that are physically different servers with named instances. You have data in your CRM server that you want to expose through the GP server. In order to do this, I did some research that I found very interesting and very successful.
My goal in this process was to pull into a view, specific information from the Orders in CRM. The information, however, is stored in the Extension of Orders (meaning customized fields).
*** WARNING! *** DO NOT PROCEED WITHOUT THE CONSENT OF YOUR IT DEPARTMENT! Further, you assume all liability regarding the impact of the following recommendations.
Step 1 is to turn on the ability to do Ad Hoc Queries on the CRM server (Target Server). Execute the following commands against the Master database on the CRM (Target) server.
sp_configure ‘show advanced options’, 1;
GO
RECONFIGURE;
GO
sp_configure ‘Ad Hoc Distributed Queries’, 1;
RECONFIGURE;
Step 2 is to add a linked server on the GP server. Execute the following commands against the Master database on the GP (Source) server.
EXEC sp_addlinkedserver
@server=N’SQLCRMInstance’,
@srvproduct=N’SQL Server’
You should now see a linked server when you Select * from sys.servers in the Master database (should show as server_id 1 or anything higher than 0)
Step 3 is to test your connection. I did this with a simple query to begin with.
SELECT *
FROM [SQLCRMInstance].MSCRMDB.dbo.SalesOrder
Step 4 is to create your view as you would like it exposed in GP.
Create view v_GiftMessage as
Select so.Customeridname as [CustomerName],
so.OrderNumber as [CRMOrderNbr],
so.Name as [GPOrderNbr],
soext.cus_GiftMessage as [GiftMessage] — Note: this is the Custom Field in the Extension of Orders
from [SQLCRMInstance].MSCRMDB.dbo.SalesOrder SO
join [SQLCRMInstance].MSCRMDB.dbo.SalesOrderExtensionBase SOExt
on so.SalesOrderId = SOExt.SalesOrderId