sql server DB link

 

--add link

exec  sp_addlinkedserver @server='DBLinkCompalAPS',@srvproduct='',@provider='SQLOLEDB',@datasrc='cetebiddb',@catalog='bccdb'

exec  sp_addlinkedsrvlogin 'DBLinkCompalAPS',false,null,'ebid','******'

 

EXEC master.dbo.sp_addlinkedserver @server = N'link_cetebiddb', 

            @srvproduct=N'sqldbmo', @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 for
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_addlinkedserver
@server=''DBLinkCompalAPS'',@srvproduct='''',@provider=''SQLOLEDB'',@datasrc='''+@server+''''

exec
(@sql)

 

set @sql='exec  sp_addlinkedsrvlogin
''DBLinkCompalAPS'',''false'',null,'''+@web_uid+''','''+@web_pwd+''''

exec
(@sql)

  

set @sql='exec  sp_addlinkedsrvlogin
''DBLinkCompalAPS'',''false'',null,'''+@uid+''','''+@pwd+''''

exec
(@sql)

 

set nocount
off

end

 

 

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO