Yesterday, we moved a SQL Server 2005 database to a new SQL Server 2005 server build to improve the performance of a Microsoft CRM installation. Everything went smoothly, but we did face one challenge. We had custom code that used sql joins on data in the CRM database and data in databases running on the local database server.
Here's the scenario:
We have 2 SQL Server 2005 Database Servers and need to use sql joins to join data from databases in the 2 distinct servers.
SERVER 1 - SQL Server 2005 server that runs multiple databases for many different applications and systems
SERVER 2 - SQL Server 2005 server that runs a Microsoft CRM database (the database that we moved)
Here's what we did to meet the challenge:
Step 1: Create a Linked server on SERVER 1 to access data on SERVER 2
If you don't know how to create a linked server a good reference is
http://msdn.microsoft.com/en-us/library/ms188279.aspx )
**You need to make sure that the linked server passes through the correct user credentials for your database connection string and that you have the correct permissions set-up for the user on both of the servers.
Step 2: Modify the sql joins syntax on SERVER 1 and in any code that utilizes sql joins on data pulling from both servers
If you don't have the correct syntax in your joins you will receive errors. You need to add the Linked Server Name to your sql join and use an alias for the linked server. If you don't do this correctly, you will get an error similar to "The multi-part identifier "LinkedServerName.DatabaseName.dbo.TableName.ColumnName" could not be bound" We are not sure if it is a bug in SQL Server or if there is a reason for the required table name alias. But, if you don't use the alias you will get an error.
Here's the correct sql syntax for joining data from your local SQL Server and the Linked SQL Server:
SELECT Tbl.Column1 as Col1,
LinkedTblAlias.Column2 as Col2
FROM Server1DatabaseName.dbo.TableName Tbl
INNER JOIN LinkedServer2Name.DatabaseName.dbo.TableName LinkedTblAlias
ON Tbl.Column1 = LinkedTblAlias.Column1
If you need help with your SQL Server database be sure to
contact us or request a
TechPoint quote:
Click here to request a quote >