SQL Server操作远程数据库示例

远程查询的关键词：
OpenDataSource ( provider_name, init_string )
参数：provider_name 数据源，如Microsoft.Jet.OLEDB.4.0，SqlOledb等 init_string 连接字符串，如 Data Source=C:\TestMdb.mdb; 或 Data Source=(local);user id=sa;password=123456;' 等
例
OpenDataSource('SqlOledb','Data Source=202.103.190.184,2433;user id=www_szlhhr_com;password=com_szlhhr_www;')

下面是一个真实的人才市场数据库SQL Server执行远程数据库操作查询的例子(当然帐号密码IP全是假的)，用于将本地数据发布到远程数据库，与之同步。（因为要求简单，这里没有用SqlServer的数据发布来分发数据和备份）



/* Author :天下第九 DEvelop Date :2006-8-14 */


CREATE Procedure xp_addLocaleJobs
@dtLocale datetime
As
--Select Top 1 * from OpenDataSource('SqlOledb','Data Source=202.103.190.184,2433;user id=www_szlhhr_com;password=com_szlhhr_www;').longguan.dbo.zj_CoLocale
--Select Top 1 * from zj_CoLocale
--Delete From OpenDataSource('SqlOledb','Data Source=59.36.98.248;User Id=www_160job_com;Password=com_160job_www;').www_160job_com.dbo.zj_CoLocale Where dtLocale like @dtlocale
--Insert Into OpenDataSource('SqlOledb','Data Source=59.36.98.248;User Id=www_160job_com;Password=com_160job_www;').www_160job_com.dbo.zj_CoLocale([idMedi], [strCoLocaleNum], [strCoNum], [dtLocale], [strStall], [intIsEntrance], [decCharge], [strRemark], [strOperationNum], [strOperatorNum], [dtEnrol], [intIsAuditing], [strAssessorNum], [intIsNewCompany], [decDeposit], [intReturnDeposit], [strStationery], [strReceiverNum], [intIsReceivered], [intSerialNumber], [intFlag_TC], [strZPZGZ], [intCHRS], [strYCZZL], [intFlag_DC], [dtTCSJ], [dtRCSJ], [strGGYQ], [strFKFS], [idZWLB], [strFSZL]) Select [idMedi], [strCoLocaleNum], [strCoNum], [dtLocale], [strStall], [intIsEntrance], [decCharge], [strRemark], [strOperationNum], [strOperatorNum], [dtEnrol], [intIsAuditing], [strAssessorNum], [intIsNewCompany], [decDeposit], [intReturnDeposit], [strStationery], [strReceiverNum], [intIsReceivered], [intSerialNumber], [intFlag_TC], [strZPZGZ], [intCHRS], [strYCZZL], [intFlag_DC], [dtTCSJ], [dtRCSJ], [strGGYQ], [strFKFS], [idZWLB], [strFSZL] from zj_CoLocale Where dtLocale like @dtlocale

Delete From OpenDataSource('SqlOledb','Data Source=59.36.98.42,2433;User Id=www_160job_com;Password=1qaz2wsx3edc4rfv;').www_160job_com.dbo.zj_CoLocale Where dtLocale = @dtlocale
Insert Into OpenDataSource('SqlOledb','Data Source=59.36.98.42,2433;User Id=www_160job_com;Password=1qaz2wsx3edc4rfv;').www_160job_com.dbo.zj_CoLocale([idMedi], [strCoLocaleNum], [strCoNum], [dtLocale], [strStall], [intIsEntrance], [decCharge], [strRemark], [strOperationNum], [strOperatorNum], [dtEnrol], [intIsAuditing], [strAssessorNum], [intIsNewCompany], [decDeposit], [intReturnDeposit], [strStationery], [strReceiverNum], [intIsReceivered], [intSerialNumber], [intFlag_TC], [strZPZGZ], [intCHRS], [strYCZZL], [intFlag_DC], [dtTCSJ], [dtRCSJ], [strGGYQ], [strFKFS], [idZWLB], [strFSZL]) Select [idMedi], [strCoLocaleNum], [strCoNum], [dtLocale], [strStall], [intIsEntrance], [decCharge], [strRemark], [strOperationNum], [strOperatorNum], [dtEnrol], [intIsAuditing], [strAssessorNum], [intIsNewCompany], [decDeposit], [intReturnDeposit], [strStationery], [strReceiverNum], [intIsReceivered], [intSerialNumber], [intFlag_TC], [strZPZGZ], [intCHRS], [strYCZZL], [intFlag_DC], [dtTCSJ], [dtRCSJ], [strGGYQ], [strFKFS], [idZWLB], [strFSZL] from zj_CoLocale Where dtLocale = @dtlocale
GO