When developing enterprise applications, you often need to directly call data from other databases in sqlserver2000 to share and update data. After creating a linked server, there are two solutions for calling data:
1. When the data volume of a remote table is smaller than several hundred or smaller, you can use the direct call scheme to directly query the data by using SQL. For example: Select * From newe .. cap. ep_ite
2. Some tables have a large amount of data. If you use a large number of tables, each time you call the database directly, this not only increases the speed of data transmission, in addition, local usage is affected when a problem occurs in the network or remote database. Therefore, we recommend that you create a local table and regularly check for updates.
The procedure is as follows:
1. First, the Oracle8i client needs to be installed on the sqlserver2000 server, and the network service name for oracle8 is created through net8 configuration assistant in net8 administration or net easy config, then, create a connection server in sqlserver, and fill in the "Data Source", that is, the network service name created earlier. In "security", use the "security context of remote Logon" method;
2. To create a job, we recommend that you call a dedicated procedure to check and update the data. For Oracle databases, we mainly increase the data volume by comparing the remote table and the total number of local tables in advance, insert new data if different. For ORACLE data updates, the local table can only be updated on a regular basis. We recommend that you use the delete and insert methods as little as possible, and use the temporary table update method.
3. In a job, the following errors always occur when you execute a call to update the linked server and the local data,
Execution User: xzc. Remote Access to Windows NT users activated by using setuser is not allowed. [Sqlstate 42000] (error 7410). Step failed.
Generally, you can change the "owner" of the update job to the local system administrator. In addition, pay attention to the optimization analysis during data update, insert and delete operations on SQL Server databases should be minimized, and a large number of updated data tables are often required. We recommend that you do not create an automatically added ID.
The above is my personal application experience. You are welcome to discuss and correct it!