Separate SQL Server Instances

When the database(s) for the ERP system and Bizview is installed in different SQL Server instances, there are a few more tasks needed to enable data integration from the ERP system database to the Bizview database.

This topic contains the following sections:

• Create a New Login in the DB Instance of the ERP Database(s)

• Create a "Linked Server" Connection to the ERP DB Instance

Create a New Login (e.g. bvreader) in the DB Instance of the ERP Database(s)

use erp_database

go

create login [bvreader] WITH PASSWORD=N'mypassword',

default_database=[erp_database]

go

Example script to grant read access:

erp_database

go

createuser bvreader from login bvreader

go

exec sp_addrolemember db_datareader, bvreader

go

Create a "Linked Server" Connection to the ERP DB Instance (with bvreader as login)

First, login to the Bizview database, with sysadmin rights.

Then, execute the following script (example):

exec master.dbo.sp_addlinkedsrvlogin

@rmtsrvname = N'172.18.0.20\SQL2008DEV', @useself = N'false',

@locallogin = null, @rmtuser = N'bvreader', @rmtpassword = 'mypassword'

Bizview should now be able to connect via the linked server to retrieve data from the ERP System stored in another database instance.

Next step:Tasks for Bizview Integrator Consultant