Links and operations between operating systems and databases of different versions

Reason:The personal plan points Data Source of the knowledge base is 157 databases. However, based on Bi and other systems, a separate personal plan points of the knowledge base are copied on the 34, so each 157 change occurs, it is troublesome to manually change 34 databases.

Therefore, a trigger was written in 157 to synchronously update the personal plan points of 34 databases. But encountered various problems.

The overall idea of automation of this problem: Create a trigger in the 157 plan scoring table and synchronously update 34 databases (it seems very simple and troublesome to implement)

Problem 1: 64-bit Windows2003, sqlserver2005, link 32-bit Windows2003, sqlserver2000, cannot be linked
Error message:
The ole db access interface "sqlncli" of the linked server "Nebula" returns the message "unspecified error ".
The ole db access interface "sqlncli" Returned message "of the linked server" Nebula "cannot find the stored procedure required to complete the operation on the server. Contact the system administrator. ".
Message 7311, level 16, status 2, 1st rows
Unable to obtain the architecture row set "dbschema_tables_info" of the ole db access interface "sqlncli" of the linked server "Nebula ". This access interface supports this interface, but a failed code is returned when this interface is used.

Solution:
1. Ensure that SP4 is installed on the linked sqlserver2000; otherwise, install
2. After the installation is complete, run CMD and run the following command: osql-U sa-P ovenjackchain-s 192.168.0.34-I c: \ instcat. SQL
Pay attention to the user name and password, and the final c: \ instcat. SQL. If SP4 is installed, this file must exist and you can search for it yourself.
3. Press enter, and click "success!
4. After the execution is successful, you can establish a link to the two databases (how to create a link, please search the Knowledge Base)

Problem 2: failed to create a trigger
Error message:The prefix exceeds the maximum limit. Up to 2
The trigger is as follows:

View Source

Print?

01	create trigger T_Changescore

02	onY_TaskScoreplanning for update,delete,insert as

03

begin

04	drop table nebula.Nebula.dbo.taskplaning;

05	insert into nebula.Nebula.dbo.taskplaning

06

select case [Task Type]when 'Cast-Knowledge Base learn' then 'Learn' when 'Investment-Knowledge Base Development' then 'Demo' end tasktype,case Node ownerwhen 'Yao liang' then 'Yao liang' else substring(Node owner, 3,100)end Taskman, yearyear,isnull(m01,0) m01,isnull(m02,0) m02,isnull(m03,0) m03,isnull(m04,0) m04,isnull(m05,0) m05,isnull(m06,0) m06,isnull(m07,0) m07,isnull(m08,0) m08,isnull(m09,0) m09,isnull(m10,0) m10,isnull(m11,0) m11,isnull(M12, 0) M12, total

07	from Y_TaskScoreplanning

08	where ([Task Type] ='Cast-Knowledge Base learn' or [Task Type] ='Investment-Knowledge Base Development' ) and Year ='2011'

09	and substring(Node owner, 3,100)not in ('xxxx')

10

end

Solution:Change the code executed by the trigger to a stored procedure for execution. The stored procedure can be created successfully. Select * into and drop cannot be used in links.

View Source

Print?

01	create trigger T_Changescore

02	onY_TaskScoreplanning for update,delete,insert as

03

begin

04	exec P_UpdateNebulaScore

05

end

06

07	create proc P_UpdateNebulaScore as

08

begin

09

10	delete nebula.Nebula.dbo.taskplaning;-- Note

11	insert into nebula.Nebula.dbo.taskplaning-- Note

12

select case [Task Type]when 'Cast-Knowledge Base learn' then 'Learn' when 'Investment-Knowledge Base Development' then 'Demo' end tasktype,case Node ownerwhen 'Yao liang' then 'Yao liang' else substring(Node owner, 3,100)end Taskman, yearyear,isnull(m01,0) m01,isnull(m02,0) m02,isnull(m03,0) m03,isnull(m04,0) m04,isnull(m05,0) m05,isnull(m06,0) m06,isnull(m07,0) m07,isnull(m08,0) m08,isnull(m09,0) m09,isnull(m10,0) m10,isnull(m11,0) m11,isnull(M12, 0) M12, total

13	from Y_TaskScoreplanning

14	where ([Task Type] ='Cast-Knowledge Base learn' or [Task Type] ='Investment-Knowledge Base Development' ) and Year ='2011'

15	and substring(Node owner, 3,100)not in ('Quququand','Jiangmin','Sun Yafeng','Weiwen','Liang Xiaodong')

16

17

end

Question 3: update the 157 personal points plan for testing, but the cup is staged again
Error message:The partner Transaction Manager has disabled its support for remote/network transactions.
Solution: perform the following operations on both servers at the same time:

Test again after the execution is complete. OK

Three major problems are hidden behind a small problem !!!