Simple practical SQL Script part SQL Server 2005 linked servers _mssql2005

Applicable scenario: operate on a remote db.

Watts compared with 2005: SQL Server 2000 also has links to remote db , but the functionality is weak, the scalability is poor, and the supported queries are simpler. in the SQL Server 2005 version of SSMs already has the server object -> linked server function point, the user first creates a remote db the link object, You can then perform the table's DML as it was on the surface .

To Create a step: in SQL Server 2005 , SSMS is turned on, the server object -> linked server -> right click to create a new linked server, in Figure 2 is a way to set up, there are other ways to set up, such as:[ graphical]sqlserver to create a linked server , Figure 3 is the account and password for setting up a remote database in the security option.

(Figure 1: New link)

(Figure 2: Setting the link)

(Figure 3: Setup account)

Precautions : the default settings for Rpc in MSSQL2005, as shown in Figure 4, need to be set to Figure 5, right click on the remote link -> properties -> Server Options ->rpc and RPC out, These two values need to be set to True.

(Figure 4: Default settings)

(Figure 5: Correct settings)

However , under MSSQL2008 cannot directly modify the linked server ' Etv2_link ' RPC configuration into ture, you can modify the following statement:

--> Use [ Master ]
go
EXEC  master.dbo.sp_serveroption  @server = N ' etv2_link ' ,   @optname = n ' RPC ' ,   @optvalue = N ' true '
go
EXEC  master.dbo.sp_serveroption  @server = N ' etv2_link ' ,   @optname = N ' rpc out ' ,   @optvalue = N ' true '
go

Build Script: If you have generated a ' Etv2_link ' linked server through the interface, then if we need to migrate it to another database (deployment, update), we can produce SQL scripts in the following ways, and You can also modify SQL scripts quickly create or modify linked servers, such as modifying @server linked server names, and modifying database objects @datasrc remote links.

(Figure 6: Generating SQL scripts)

SQL Server 2005That generates the remote linked object.SqlScript:

--> /*Objects: LinkedServer [etv2_link] Script Date: 09/08/2010 17:36:11 * **/
ExecMaster.dbo.sp_addlinkedserver@server=N'Etv2_link',@srvproduct=N'Etv2_link',@provider=N'Sqlncli',@datasrc=N'Bwa035\bwa035_2k5'
Go
ExecMaster.dbo.sp_serveroption@server=N'Etv2_link',@optname=N'Collation compatible',@optvalue=N'False'
Go
ExecMaster.dbo.sp_serveroption@server=N'Etv2_link',@optname=N'Data access',@optvalue=N'True'
Go
ExecMaster.dbo.sp_serveroption@server=N'Etv2_link',@optname=N'Dist',@optvalue=N'False'
Go
ExecMaster.dbo.sp_serveroption@server=N'Etv2_link',@optname=N'Pub',@optvalue=N'False'
Go
ExecMaster.dbo.sp_serveroption@server=N'Etv2_link',@optname=N'Rpc',@optvalue=N'True'
Go
ExecMaster.dbo.sp_serveroption@server=N'Etv2_link',@optname=N'RPC out',@optvalue=N'True'
Go
ExecMaster.dbo.sp_serveroption@server=N'Etv2_link',@optname=N'Sub',@optvalue=N'False'
Go
ExecMaster.dbo.sp_serveroption@server=N'Etv2_link',@optname=N'Connect Timeout',@optvalue=N'0'
Go
ExecMaster.dbo.sp_serveroption@server=N'Etv2_link',@optname=N'Collation Name',@optvalue=Null
Go
ExecMaster.dbo.sp_serveroption@server=N'Etv2_link',@optname=N'Lazy schema validation',@optvalue=N ' false '
go
EXEC  master.dbo.sp_serveroption  @server = N ' etv2_link ' ,   @optname = n ' Query timeout ' ,   @optvalue = N ' 0 '
go
EXEC  master.dbo.sp_serveroption  @server = N ' etv2_ LINK ' ,   @optname = N ' use remote collation ' ,   @optvalue = N ' true '

Use : Assuming you have created a remote linked object named Etv2_link, you can manipulate remote DB with this object as follows .

Use Scenario 1: InquireEtv2_linkThis remote link object's[Etv2_online]In the databasevisitelog_20100629Table's data. Template shape like:Select * FROM [Linked server name]. [Remote Database name]. [Owner]. [Table name]

-- Query Remote DB table tablename
Select * from etv2_link. [etv2_online]. dbo. visitelog_20100629

Use Scenario 2: determine if a table named visitelog_20100629 exists in the [Etv2_online] database for this remote-linked object Etv2_link .

--> -- Note: It's sys.objects, not sysobjects.
-- determine if a table exists for a remote user
IF EXISTS (SELECT * from etv2_link. [etv2_online]. sys.objects WHERE name = N'visitelog_20100629' and type in (N'U'))
BEGIN
-- logical processing
print ' presence table '
End

Use Scenario 3: determines whether a table named visitelog_20100629 exists in the [Etv2_online] database for remote DB . Only the table name is parameterized and can be judged by the parameters passed in. This simply sets the value of the variable and uses out to return the variable.

--> --To determine whether a remote user has a table (parameterized table name), return a variable
DECLARE@IsExistTableVARCHAR(10)
DECLARE@TablenameVARCHAR(50)
DECLARE@sqlStringNVARCHAR(4000)
SET@IsExistTable='False'
SET@Tablename='Visitelog_'+Convert(varchar(9),GetDate()-1,112)--such as visitelog_20100629
SET@sqlString=
'IF EXISTS (SELECT * from etv2_link.[ Etv2_online].sys.objects WHERE name = N'''+@Tablename+'''and type in (N ' U ' )
    set  @IsExistTableOUT  = ' True '
EXEC  sp_executesql  @sqlString , N ' @ Isexisttableout varchar (a)  output ' , @IsExistTableOUT = @IsExistTable  output

IF   ( @IsExistTable   =   ' True ' ) -- exists
BEGIN
     -- logical processing
     print   ' presence table '
End /span>

Add: SQL Server version to connect to the remote server SQL script, more relevant steps can be referred to: access to remote databases in T-SQL statements (openrowset/opendatasource/ OPENQUERY)

--> --Method 1:
Select   *    from   OpenRowset ( ' SQLOLEDB ' , ' server=192.168.0.67;uid=sa;pwd=password ' , ' Select * from bcm2.dbo.tbappl ' )

- Method 2:
Select   *    from   OpenRowset ( ' SQLOLEDB ' , ' 192.168.0.67 ' ; ' sa ' ; ' password ' , ' select * from bcm2.dbo.tbappl ' )  
Author: Listen to the wind and rain