Đô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