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