Link
SQL is very useful technique to connect database servers in same domain and
querying them. Using Link SQL we can connect different databases and those
details available in following article.
https://msdn.microsoft.com/en-us/library/ff772782.aspx?f=255&MSPPError=-2147217396
In
this we focus on link two SQL servers in same domain then querying and sync
data between those two databases. We follow following steps to query two SQL
databases in different database servers.
Step 1:
- Make sure can access both database servers from each other.
- Use SQL Management Studio to connect to the remote database
server
- If it unable to connect to the remote server then check it
enabled named pipe and TCP/IP using SQL server configuration manager
|
Set TCP/IP enabled and Named Pipes Enabled - 32 bit mode |
|
Set TCP/IP enabled and Named Pipes Enabled |
- If Management Studio still unable to access the SQL server remotely then check Windows firewall settings and gave necessary permission to access SQL server through.
- Setting Inbound Rule to access SQL server remotely
|
Create Inbound Rule in windows firewall |
|
Set port which configured in SQL server |
Step 2:
EXEC master.dbo.sp_addlinkedserver
@server = N'XXX-UG07',
@srvproduct=N'SQL Server' ;
- After add database server as link server then all databases in that server available in current context. Following TSQL code shows how to execute stored procedure remotely then get data from SQL server and finally sync those data with target table.
- Use “OPENQUERY” to execute stored procedure remotely and retrieve data.
DECLARE @BusinessUnit UNIQUEIDENTIFIER
DECLARE @CreatedBy UNIQUEIDENTIFIER
DECLARE @ModifiedBy UNIQUEIDENTIFIER
DECLARE @OwnerId UNIQUEIDENTIFIER
DECLARE @StateCode INT
DECLARE @StatusCode INT
set @BusinessUnit = '72921AD4-3025-E511-80BC-00155D156EA8'
set @CreatedBy = 'DDA81AD4-3025-E511-80BC-00155D156EA8'
set @ModifiedBy = 'DDA81AD4-3025-E511-80BC-00155D156EA8'
set @OwnerId = 'DDA81AD4-3025-E511-80BC-00155D156EA8'
set @StateCode = 0
set @StatusCode = 1
INSERT INTO [CC_MSCRM].[dbo].ContactBase
(Contactid,OwningBusinessUnit, CreatedBy,
CreatedOn, ModifiedBy, ModifiedOn,StateCode,StatusCode,
FirstName,LastName,new_Number,OwnerId,OwnerIdType)
SELECT C1.contactid,@BusinessUnit,@CreatedBy,
GETUTCDATE(),@ModifiedBy,GETUTCDATE(),@StateCode,@StatusCode,C1.FirstName,
C1.LastName, C1.new_AccountNumber,@OwnerId,8
FROM [XXX-UG07].CC_mscrm.dbo.contactbase C1
WHERE C1.contactid IN (SELECT contactid
FROM OPENQUERY([XXX-UG07],'CC_MSCRM.dbo.Test'))
No comments:
Post a Comment