SQL Server 2008 数据库同步的两种方式（SQL JOB）

最新推荐文章于 2024-09-19 09:43:47 发布

xgf415

最新推荐文章于 2024-09-19 09:43:47 发布

阅读量1.9w

点赞数 4

数据库专栏收录该内容

11 篇文章 1 订阅

订阅专栏

sql Job的方式同步数据库就是通过SQL语句，将一个数据源中的数据同步到目标数据库中。特点是它可以灵活的通过SQL的方式进行数据库之间的同步操作。可以在制定的时间时间作为任务计划自动执行。缺点是需要写SQL来进行操作。

既然是数据库之间的同步就涉及到数据库之间的连接。建立连接是同步的第一步。SQL Server建立连接可以通过系统存储过程建立。存储过程有以下几个：

sp_droplinkedsrvlogin

sp_dropserver

sp_addlinkedserver

sp_addlinkedsrvlogin

前面两个是删除数据库之间连接的，后两个是建立数据库之间连接的。

a）删除连接存储过程参数用法

1、sp_droplinkedsrvlogin

语法：

sp_addlinkedsrvlogin [ @rmtsrvname = ] 'rmtsrvname'

    [ , [ @useself = ] 'useself' ]

    [ , [ @locallogin = ] 'locallogin' ]

    [ , [ @rmtuser = ] 'rmtuser' ]

    [ , [ @rmtpassword = ] 'rmtpassword' ]

参数：

[@rmtsrvname =] 'rmtsrvname'

是应用登录映射的链接服务器名称。rmtsrvname 的数据类型为 sysname，没有默认设置。

[@useself =] 'useself'

决定用于连接到远程服务器的登录名称。useself 的数据类型为 varchar(8)，默认设置为TRUE。true 值指定SQL Server 验证的登录使用其自己的凭据以到 rmtsrvname，而忽略 rmtuser 和 rmtpassword 参数。false 值指定rmtuser 和 rmtpassword 参数用来连接到定 locallogin 的 rmtsrvname。

如果 rmtuser 和 rmtpassword 也设置为NULL，则连接链接服务器不需使用任何登录或密码。对于Windows NT 身份验证登录言，useself 为 true 则无效，除非Microsoft Windows NT® 环境支持安全帐户委托，并且，提供程序支持Windows身份验证（此时不再需要创建值为 true 的映射，不过创建仍然有效）。

[ @locallogin =] 'locallogin'

本地服务器上的登录。locallogin 的数据类型为 sysname，默认设置为NULL。NULL值指定此条目将应用于所有连接到 rmtsrvname 的本地登录。如果值不为NULL，则locallogin 可以是一个SQL Server 登录或Windows NT用户。必须授予Windows NT用户直接访问SQL Server或通过其作为已授予访问权限的组的成员来访问SQL Server的权限。

[@rmtuser =] 'rmtuser'

当 useself 为 false 时，用来连接 rmtsrvname 的用户名，rmtuser 的数据类型为 sysname，默认设置为NULL。

[@rmtpassword =] 'rmtpassword'

与 rmtuser 相关的密码。rmtpassword 的数据类型为 sysname，默认设置为NULL

代码返回值：

0（成功）或 1（失败）

注释：

当用户登录到本地服务器并执行分布式查询，以访问链接服务器上的表时，本地服务器必须登录链接服务器上，代表该用户访问该表。用 sp_addlinkedsrvlogin 来指定本地服务器登录链接服务器的登录凭据。

本地服务器上所有登录和链接服务器上的远程登录之间的默认映射通过执行 sp_addlinkedserver 自动创建。默认映射说明连接到链接服务器代表的登录时，SQL Server 使用本地登录的用户凭据（等同于将链接服务器的 @useself 设置为true 时执行 sp_addlinkedsrvlogin）。使用 sp_addlinkedsrvlogin 只可以更改特定的本地服务器的默认映射或添加新映射。若要删除默认映射或任何其它映射，请使用 sp_droplinkedsrvlogin。

当所有下列条件存在时，SQL Server可以自动地使用正在发出查询的用户的Windows NT安全凭据（Windows NT用户名称和密码），以连接到链接服务器，而不是必须使用 sp_addlinkedsrvlogin 创建一个预设的登录映射。

用法示例：

EXEC sp_droplinkedsrvlogin 'DBLink',Null

2、sp_dropserver

从本地 Microsoft® SQL Server™ 上的已知远程和链接服务器列表中删除服务器。

语法：

sp_dropserver [ @server = ] 'server'

[ , [ @droplogins = ] { 'droplogins' | NULL} ]

是应用登录映射的链接服务器名称。rmtsrvname 的数据类型为sysname，没有默认设置。
决定用于连接到远程服务器的登录名称。useself的数据类型为varchar(8)，默认设置为TRUE。true 值指定SQL Server 验证的登录使用其自己的凭据以连接到 rmtsrvname，而忽略rmtuser 和rmtpassword 参数。false 值指定rmtuser 和rmtpassword 参数用来连接到特定locallogin 的rmtsrvname。
介绍完建立连接的方式，下面正式介绍SQL JOB的开发。直接用步骤介绍了。
a)新建作业。如下图：

[@server =] 'server'

将被删除的服务器。server 的数据类型为 sysname，没有默认值。server 必须已经存在。

[@droplogins =] 'droplogins' | NULL

指明如果指定了 droplogins，那么对于 server，那些相关的远程及链接服务器登录也将被删除。@droplogins 的数据类型为 char(10)，带有默认值NULL。

返回代码值：

0（成功）或1（失败）

注释：

如果某个服务器有相关的远程及链接服务器登录条目，当在该服务器上运行 sp_dropserver 时，会导致一条错误信息，该消息说明：在删除远程或链接服务器之前，必须先删除相关的登录。当删除服务器时，为了删除服务器的所有远程及链接服务器登录，请使用 droplogins 参数。

不能在用户定义的事务内执行 sp_dropserver。

权限：

只有 sysadmin 或 setupadmin 固定服务器角色的成员才可以行 sp_dropserver。

用法示例：

EXEC sp_dropserver 'DBLink'

b）建立连接存储过程参数用法

3、sp_addlinkedserver

创建一个链接的服务器，使其允许对分布式的、针对OLE DB数据源的异类查询进行访问。在使用 sp_addlinkedserver 创建链接的服务器之后，此服务器就可以执行分布式查询。如果链接服务器定义为Microsoft® SQL Server™，则可执行远程存储过程。

语法：

sp_addlinkedserver [ @server = ] 'server'

    [ , [ @srvproduct = ] 'product_name' ]

    [ , [ @provider = ] 'provider_name' ]

    [ , [ @datasrc = ] 'data_source' ]

    [ , [ @location = ] 'location' ]

    [ , [ @provstr = ] 'provider_string' ]

    [ , [ @catalog = ] 'catalog' ]

[ @server = ] 'server'

要创建的链接服务器的本地名称，server 的数据类型为 sysname，没有默认设置。

如果有多个 SQL Server 实例，server 可以为 servername\instancename。此链接的服务器可能会被引用为下面示例的数据源：

SELECT *FROM [servername\instancename.]pubs.dbo.authors.

如果未指定 data_source，则服务器为该实例的实际名称。

[ @srvproduct = ] 'product_name'

要添加为链接服务器的 OLE DB 数据源的产品名称。product_name 的数据类型为 nvarchar(128)，默认设置为NULL。如果是 SQL Server，则不需要指定 provider_name、data_source、location、provider_string 以及目录。

[ @provider = ] 'provider_name'

与此数据源相对应的 OLE DB 提供程序的唯一程序标识符 (PROGID)。provider_name 对于安装在当前计算机上指定的OLE DB 提供程序必须是唯一的。provider_name 的数据类型为nvarchar(128)，默认设置为NULL。OLE DB提供程序应该用给定的 PROGID在注册表中注册。

[ @datasrc = ] 'data_source'

由 OLE DB 提供程序解释的数据源名称。data_source 的数据类型为 nvarchar(4000)，默认设置为NULL。data_source 被作 DBPROP_INIT_DATASOURCE属性传递以便初始化OLE DB提供程序。

当链接的服务器针对于 SQL Server OLE DB 提供程序创建时，可以按照 servername\instancename 的形式指定 data_source，它可以用来连接到运行于特定计算机上的 SQL Server 的特定实例上。servername 是运行SQL Server 的计算机名称，instancename 是用户将被连接到的特定SQL Server 实例的名称。

[ @location = ] 'location'

OLE DB 提供程序所解释的数据库的位置。location 的数据类型为 nvarchar(4000)，默认设置为NULL。location 作为DBPROP_INIT_LOCATION属性传递以便初始化OLE DB提供程序。

[ @provstr = ] 'provider_string'

OLE DB 提供程序特定的连接字符串，它可标识唯一的数据源。provider_string 的数据类型为 nvarchar(4000)，默认设置为NULL。Provstr 作为DBPROP_INIT_PROVIDERSTRING属性传递以便初始化OLE DB提供程序。

当针对 Server OLE DB 提供程序提供了链接服务器后，可将 SERVER关键字用作SERVER=servername\instancename 来指定实例，以指定特定的SQL Server实例。servername 是SQL Server在其上运行的计算机名称，instancename 是用户连接到的特定的SQL Server实例名称。

[ @catalog = ] 'catalog'

建立 OLE DB 提供程序的连接时所使用的目录。catalog 的数据类型为sysname，默认设置为NULL。catalog 作为DBPROP_INIT_CATALOG属性传递以便初始化OLE DB提供程序。

返回代码值：

0（成功）或1（失败）

结果集：

如果没有指定参数，则 sp_addlinkedserver 返回此消息：

Procedure 'sp_addlinkedserver' expects parameter '@server', which was not supplied.

使用适当 OLE DB 提供程序和参数的 sp_addlinkedserver 返回此消息：Server added.

用法示例：

EXEC sp_addlinkedserver 'DBLink', ' ', 'SQLOLEDB', "10.0.222.12"

4、sp_addlinkedsrvlogin

创建或更新本地 Microsoft® SQL Server™ 实例上的登录与链接服务器上远程登录之间的映射。

语法：

sp_addlinkedsrvlogin [@rmtsrvname =]'rmtsrvname'

    [,[@useself =]'useself']

    [ , [@locallogin =]'locallogin']

    [,[@rmtuser =]'rmtuser']

    [,[@rmtpassword =]'rmtpassword']

[@rmtsrvname =]'rmtsrvname'

[@useself =]'useself'

如果 rmtuser 和rmtpassword 也设置为NULL，则连接链接服务器不需使用任何登录或密码。对于Windows NT 身份验证登录而言，useself为true 则无效，除非Microsoft Windows NT®环境支持安全帐户委托，并且，提供程序支持Windows身份验证（此时不再需要创建值为true 的映射，不过创建仍然有效）。

[ @locallogin =]'locallogin'

本地服务器上的登录。locallogin的数据类型为sysname，默认设置为NULL。NULL值指定此条目将应用于所有连接到rmtsrvname的本地登录。如果值不为NULL，则 locallogin 可以是一个SQL Server登录或Windows NT用户。必须授予Windows NT用户直接访问SQL Server或通过其作为已授予访问权限的组的成员来访问SQL Server的权限。

[@rmtuser =]'rmtuser'

当 useself 为false时，用来连接rmtsrvname的用户名，rmtuser 的数据类型为sysname，默认设置为NULL。

[@rmtpassword =]'rmtpassword'

与 rmtuser 相关的密码。rmtpassword 的数据类型为sysname，默认设置为NULL。

返回代码值：

0（成功）或1（失败）

注释：

当用户登录到本地服务器并执行分布式查询，以访问链接服务器上的表时，本地服务器必须登录链接服务器上，代表该用户访问该表。使用sp_addlinkedsrvlogin 来指定本地服务器登录链接服务器的登录凭据。本地服务器上所有登录和链接服务器上的远程登录之间的默认映射通过执行sp_addlinkedserver 自动创建。默认映射说明连接到链接服务器代表的登录时，SQL Server使用本地登录的用户凭据（等同于将链接服务器的 @useself 设置为true 时执行sp_addlinkedsrvlogin）。使用sp_addlinkedsrvlogin只可以更改特定的本地服务器的默认映射或添加新映射。若要删除默认映射或任何其它映射，请使用sp_droplinkedsrvlogin。