Bật RPC và RPC out cho tất cả LinkServer


Đôi khi sử dung Linkserver trong SQL bạn sẽ gặp một số lỗi về RPC, đây là đoạn Script để bật RPC và RPC Out cho tất cả các LinkServer. Script này áp dung cho SQL2000 và SQL2008

 

use master go

declare @isSQL2008 char(1) declare @SQL nvarchar(4000) IF OBJECT_ID (N’tempdb.dbo.##ServerList0001′) IS NOT NULL DROP TABLE ##ServerList0001

create table ##ServerList0001 (  Srv nvarchar(100)  null )

–Lay version SQL Server Select @isSQL2008= SUBSTRING(convert(nvarchar, SERVERPROPERTY(‘productversion’)), 1,1)

set @SQL = ” declare @SrvName nvarchar(100) set @SrvName = ”

–La SQL2008 if (@isSQL2008 = ‘1’) begin    exec(‘insert into ##ServerList0001(Srv) select Name from sys.servers’)  end

–La SQL2000 if (@isSQL2008 = ‘8’) begin    exec(‘insert into ##ServerList0001(Srv) select SrvName from sysservers’)  end

–Thiet lap RPC va RPC Out cho tung linkserver DECLARE db_cursor CURSOR FOR  select Srv from ##ServerList0001 OPEN db_cursor  FETCH NEXT FROM db_cursor INTO @SrvName

WHILE @@FETCH_STATUS = 0

BEGIN

set  @SQL = ‘ exec sp_serveroption @server = ‘ + ””+@SrvName + ””   + ‘,@optname =’ + ”’RPC”’+’,@optvalue =’+”’TRUE”;’  print @sql

Exec(@sql)  set  @SQL = ‘ exec sp_serveroption @server = ‘ + ””+@SrvName + ””   + ‘,@optname =’ + ”’RPC out”’+’,@optvalue =’+”’TRUE”;’

print @sql

Exec(@sql)

FETCH NEXT FROM db_cursor INTO @SrvName

END

CLOSE db_cursor

DEALLOCATE db_cursor

–Huy ban tam

IF OBJECT_ID (N’tempdb.dbo.##ServerList0001′) IS NOT NULL DROP TABLE ##ServerList0001

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s