SytelineMaster

SQL server provides way to connect to remote database (SQL, Oracle and many others).  Within your store procedure, you can dynamically connect to the remote DB, fetch data, process them into Syteline DB. 

Business Case:

Let’s say you have a web store database, in a remote database server.  You want to replicate the orders into Syteline

Syteline Technical Component:

SQL Store Procedure

Solution:

In your store procedure, you will need to first connect to the remote DB server, and login to the database.  Here is a example with server name DBSRVR.

if not exists(select 1 from sys.servers where name = ‘DBSRVR’)
    EXEC sp_addlinkedserver
        ‘DBSRVR’,
        N’SQL Server’

exec sp_addlinkedsrvlogin ‘DBSRVR’
                         ,’false’
                         ,null
                         ,’sa’
                         ,’sapassword’

After getting the connection, you can use the select statement to fetch data into a temp table for further process.

Insert @Orders (
            [OrderID]
           ,[OrderDate]
           ……
           )
    Select
            [OrderID]
           ,[OrderDate]

           ……

        from [DBSRVR].[WEBORDER].dbo.orders

Once you done with all the process, you may want to drop off the connection

— Drop off from DBSRVR 
if exists(select 1 from sys.servers where name = ‘DBSRVR’)
Exec sp_dropserver ‘DBSRVR’, ‘droplogins’

To check what Database Servers are currently link to your DB Server, just query the sys.servers table. 

Select * from sys.servers