SQL server DB link

 

-- Add link

Exec sp_addmediaserver @ server = 'dblinkcompalaps ', @ srvproduct = '', @ provider = 'sqloledb', @ datasrc = 'cetebiddb', @ catalog = 'bccdb'

Exec sp_add1_srvlogin 'dblinkcompalaps ', false, null, 'ebid ','******'

 

EXEC master. dbo. sp_addmediaserver @ server = n' link _ cetebiddb ',

@ Srvproduct = n'sqldbm', @ provider = n'sqloledb', @ datasrc = n'cetebiddb ',

@ Provstr = n'user ID = ebid; Password = ******'

-- Drop link

Exec sp_droplinkedsrvlogin DBLinkCompalAPS, Null

Exec sp_dropserver DBLinkCompalAPS

 

-- Select

Select * from sys. servers

 

Select top 1 * from DBLinkCompalAPS. bccdb. dbo. BCC_Admin_CartStyle

 

 

--

Proc

 

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS ON

GO

 

 

/*************************************** ***********

Name:
Usp_Create_DBLink_DBLinkCompalAPS

Purpose: Created DBLik
ControlDB

Used table:
APSCN10.dbo. pl_controlDB_inf

**************************************** **********/

ALTER proc
[Dbo]. [usp_Create_DBLink_DBLinkCompalAPS]

@ Parm_plant
Nchar (04)

As

Begin

Set nocount
On

Declare @ server nvarchar (10), @ db
Nvarchar (10), @ uid nvarchar (10), @ pwd nvarchar (50), @ web_uid nvarchar (10), @ web_pwd
Nvarchar (20)

Select top 1
@ Server = ControlDB_Server_name

, @ Db = ControlDB_DB_name

, @ Uid = ControlDB_uid

, @ Pwd = ControlDB_password

, @ Web_uid = substring (';' + aps_db_connection + ';', charindex ('; Uid =', ';' + aps_db_connection + ';') + len ('; uid = '), charindex ('; ','; '+ aps_db_connection +'; ', charindex ('uid =', ';' + aps_db_connection + ';')) -(charindex ('; Uid =', ';' + aps_db_connection + ';') + len ('; Uid = ')))

, @ Web_pwd = substring (';' + aps_db_connection + ';', charindex ('; Pwd =', ';' + aps_db_connection + ';') + len ('; pwd = '), charindex ('; ','; '+ aps_db_connection +'; ', charindex ('pwd =', ';' + aps_db_connection + ';')) -(charindex ('; Pwd =', ';' + aps_db_connection + ';') + len ('; Pwd = ')))

From
Pl_controlDB_inf

Where
Plant = @ parm_plant

Declare @ SQL
Nvarchar (2000)

 

Exec sp_droplinkedsrvlogin
DBLinkCompalAPS, Null

 

Exec sp_dropserver
DBLinkCompalAPS

 

Set @ SQL = 'exec sp_addmediaserver
@ Server = ''dblinkcompalaps '', @ srvproduct = ''', @ provider = ''sqloledb'', @ datasrc = ''' + @ server + ''''

Exec
(@ SQL)

 

Set @ SQL = 'exec sp_addjavassrvlogin
''Dblinkcompalaps '', ''false'', null, ''' + @ web_uid + ''', ''' + @ web_pwd + ''''

Exec
(@ SQL)

Set @ SQL = 'exec sp_addjavassrvlogin
''Dblinkcompalaps '', ''false'', null, ''' + @ uid + ''', ''' + @ pwd + ''''

Exec
(@ SQL)

 

Set nocount
Off

End

 

 

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO