Creating a Linked Server for a SQL Server Database

Before you import an application, if you are using a SQL Server database, you must create a linked server between the destination and source database.

Perform these steps from the destination database.

To create a linked server to source SQLServer instances:

Execute the following four commands in order (each starting with exec) on the destination HFM database:

exec sp_addlinkedserver N'<linked server name>',@srvproduct=N"", @provider=N'SQLNCLI', @datasrc=N'<host name>'

exec sp_addlinkedsrvlogin N'<linked server name>',@useself=N'FALSE',@rmtuser=N'<hfm db user>',@rmtpassword=N'<hfm db password>'

exec sp_serveroption @server=N'<linked server name>',@optname=N'rpc',@optvalue=N'true'

exec sp_serveroption @server=N'<linked server name>',@optname=N'rpc out',@optvalue=N'true'

Ensure that the <hfm db user> has database owner access on the target database.

For example, to create a linked server with the name ToTestSystem to sqlserver on stm10082, and the source sqlserver HFM database credentials are hfm/hyperion:

exec sp_addlinkedserver N'ToTestSystem',@srvproduct=N"",@provider=N'SQLNCLI',@datasrc=N'STM10082.xxx.COM'

exec sp_addlinkedsrvlogin N'ToTestSystem',@useself=NFALSE',@rmtuser=N'hfm', @rmtpassword=N'password'

exec sp_serveroption@server=N'ToTestSystem',@optname=N'rpc',@optvalue=N'true'

exec sp_serveroption @server=N'ToTestSystem',@optname=N'rpc out',@optvalue=N'true'

Validation step: The following command should list the applications in the source SQL Server:
```
select * from [ToTestsystem].[hfm].[dbo].HSX_DATASOURCES;
```

Command to drop linked server:

sp_dropserver'<linked server name>','droplogins'

Example:  sp_dropserver ‘ToTestSystem’,'droplogins'

Command to list all linked servers:
```
sp_linkedservers
```