SQL server 添加字段或者记录到Linked Server的表

Linked Server Name : [dev]

需求: 向linked server [dev] 插入数据, 并在插入数据前匹配两个数据库表的字段差异. 并自动添加或者修改字段

这里将不再描述如何匹配两个数据库的表字段差异. 直接说明 遇到的问题

 

1.SET IDENTITY_INSERT 和 insert records 

正常情况下我们一般都会通过下列语句去插入一条数据:

set @ls_sql = 'SET IDENTITY_INSERT [dev].database.dbo.table

ON; insert into [dev].database.dbo.table (parcode1,parcode2,value,[des],status, createdate, createby) select ''111'',''222'',''333'',''22'',1,getdate(),''jahe''; 
SET IDENTITY_INSERT [dev].database.dbo.table OFF'

但是遇到以下错误信息:

The object name 'dev.database.dbo.table' contains more than the maximum number of prefixes. The maximum is 2　　

正确的语法如下

set @ls_sql = ' exec [dev].master.sys.sp_executesql @statement = N''
 SET IDENTITY_INSERT database.dbo.table ON;''

SET XACT_ABORT ON;
insert into [dev].database.dbo.table (parcode1,parcode2,value,[des],status, createdate, createby)
select ''111'',''222'',''333'',''22'',1,getdate(),''jahe'';

SET XACT_ABORT OFF;

exec [dev].master.sys.sp_executesql @statement = N'' SET IDENTITY_INSERT database.dbo.table OFF;''

'

　注意 这里要添加一句 

SET XACT_ABORT ON;

　如果不添加,SQL将会报如下错误

   Unable to start a nested transaction for OLE DB provider "SQLNCLI11" for linked server "dev". A nested transaction was required because the XACT_ABORT option was set to OFF.

　

2. 添加字段到 linked server

正常情况语句

   alter table [dev].database.dbo.table add test nvarchar(1) null

SQL将会报以下错误

The object name 'dev.database.dbo.table' contains more than the maximum number of prefixes. The maximum is 2.

正确的语法如下

exec [dev].master.sys.sp_executesql @statement = N' alter table database.dbo.table
add test nvarchar(1) null'

 

稍后晚点将对上述的具体原因做说明.