Implement heterogeneous database Data transformation schemes by creating a linked server from SQL Server 2005 to oracle10g _mssql2005

1. New Linked server

In Figure 1, select "Linked Server" and right click "New Linked Server", as shown in Figure 2, to configure the relevant parameters.

2, configure the relevant parameters

In the General option, fill in the link server name, and the provider selects

Microsoft OLE DB Provider for Oracle item. The product name, data source, and access interface string please fill in the locally configured NET service name of the configured Oracle client. As shown in Figure 3.

In the security key, select Use this security context to establish a connection, fill in the remote login user and password, that is, the user and password that is logged on to the distant Oracle server. As shown in Figure 4.

3, heterogeneous database access statement format

For example, we set up a linked server Oradbconn, and Oracle has a table WebUser under the Hrsoft user, so our SQL statement is:

--Clear data from the Oracle table

DELETE from Oradbconn ... Hrsoft. WEBUSER

--Write data from SQL Server to Oracle

INSERT into Oradbconn ... Hrsoft. WEBUSER

SELECT * from WEBUSER

If the report succeeds, our data is already written to Oracle.

With select * FROM Oradbconn ... Hrsoft. WEBUSER

See if there is already data in the Oracle database.

4, Linked server application

A, Query Oracle data table method (this way, when Oracle and SQL Server data types are inconsistent, often error, and slightly slower):

SELECT * FROM [link2oracle] ... [Oracle_user_name]. table_name;

I am executing this statement often reported as error message: The OLE DB provider "Msdaora" of the linked server "Link2oracle" provides inconsistent metadata for the column. Object "" CMCC "." The column "isopened" of the Os2_gis_cell "" (Compile-time ordinal 20) has 130 "DBTYPE" at compile time, but 5 at run time.

B, query Oracle data Table Mode II (tested, this method is used very smoothly, no error, and speed almost as fast as in Oralce):

SELECT * FROM OPENQUERY (link2oracle, ' select * from Oracleusername.tablename ')

You can use OPENQUERY () as a table.

C, for example (Import the user table WebUser under the Oralce user Hrsoft into the SQLServer2005 database):

SELECT * Into Os_gis_webuser to OPENQUERY (link2oracle, ' select * from Hrsoft. WEBUSER)

D, a more convenient way: By creating synonyms for easy query:

CREATE synonym Os_gis_cell for [oraclelk] ... [CMCC]. Os_gis_cell;

SELECT * from Os_gis_cell;

SELECT * from Os_gis_cell a where a.cellname is null;

NOTE: SQL statements involving Oracle parts, especially [Oraclelink] ... [Oracle_user_name]. TABLE_NAME must be capitalized, or you will report a similar error:

Message 7314, Level 16, State 1, line 1th

The OLE DB provider "Msdaora" for the linked server "Oraclelk" does not contain table "CMCC". Os2_gis_cell "". The table does not exist, or the current user does not have permission to access the table.

Configuration Data Source:

Engineering Inplan:

(Description= (address_list= address= (protocol=tcp) (host=192.168.2.6) (port=1521)) (Connect_data= (SID=SA) (SERVER =dedicated)))

Erp:

(Description= (address_list= (address= (PROTOCOL = TCP) (HOST = 192.168.3.25) (PORT = 1528))) (Connect_data = (sid= tjtest)))

(Description= (address_list= (address= (PROTOCOL = TCP) (HOST = 192.168.3.25) (PORT = 1528))) (Connect_data = (sid= tjtest)))

Code for the new business:

Delete from Xbld_information

INSERT into Xbld_information (job_name, Num_layers, Jb_pp1, JB_PC1, Jb_pp2, JB_PC2, JB_PP3, JB_PC3, Pp_pp1, PP_PC1, PP_PP2 , PP_PC2, PP_PP3, PP_PC3, PP_PP4,PP_PC4,BM)

SELECT * FROM OPENQUERY (Inplan, '

Select Mjs15.job_name, Mjs15.num_layers,mjs1. MM1 jb_pp1,mjs1. MC1 Jb_pc1,mjs2. MM2 Jb_pp2,mjs2. MC2 JB_PC2,MJS3. MM3 JB_PP3,MJS3. MC3 JB_PC3,MJS11. MM1 PP_PP1,MJS11. MC1 pp_pc1,mjs12. MM2 pp_pp2,mjs12. MC2 Pp_pc2,mjs13. MM3 Pp_pp3,mjs13. MC3 Pp_pc3,mjs14. MM4 Pp_pp4,mjs14. MC4 pp_pc4,substr (mjs15.job_name,5,2) BM

From

(Select Mjl.job_name,

Mjl.num_pcbs,

Mjl.num_arrays,

Mjl.num_panles_,

Mjl.num_layers,

substr (Mjl.op_size_y_string_,instr (mjl.op_size_y_string_, ' = ') +1,length (mjl.op_size_y_string_)-instr (mjl.op_ Size_y_string_, ' = ') +1 y_size,--only へ 糴

substr (Mjl.op_size_x_string_,instr (mjl.op_size_x_string_, ' = ') +1,length (mjl.op_size_x_string_)-instr (mjl.op_ Size_x_string_, ' = ') +1 x_size--only へ

From Tj.my_job_list MJL

) MJS15,

(SELECT *

From

(Select Mjs.job_name,

Decode (Dense_rank () over (partition by job_name order by Material_name,material_count), 1,material_name) MM1,

Decode (Dense_rank () over (partition by job_name order by Material_name,material_count), 1,material_count) MC1

From Tj.my_job_stackup_material_count MJS

where mjs.type_t= ' Core '

Where MM1 is not null

) MJS1,

(SELECT *

From

(Select Mjs.job_name,

Decode (Dense_rank () over (partition by job_name order by Material_name,material_count), 2,material_name) MM2,

Decode (Dense_rank () over (partition by job_name order by Material_name,material_count), 2,material_count) MC2

From Tj.my_job_stackup_material_count MJS

where mjs.type_t= ' Core '

Where MM2 is not null

) Mjs2,

(SELECT *

From

(Select Mjs.job_name,

Decode (Dense_rank () over (partition by job_name order by Material_name,material_count), 3,material_name) MM3,

Decode (Dense_rank () over (partition by job_name order by Material_name,material_count), 3,material_count) MC3

From Tj.my_job_stackup_material_count MJS

where mjs.type_t= ' Core '

Where MM3 is not null

) MJS3,

(SELECT *

From

(Select Mjs.job_name,

Decode (Dense_rank () over (partition by job_name order by Material_name,material_count), 1,material_name) MM1,

Decode (Dense_rank () over (partition by job_name order by Material_name,material_count), 1,material_count) MC1

From Tj.my_job_stackup_material_count MJS

where mjs.type_t= ' Prepreg '

Where MM1 is not null

) MJS11,

(SELECT *

From (

Select Mjs.job_name,

Decode (Dense_rank () over (partition by job_name order by Material_name,material_count), 2,material_name) MM2,

Decode (Dense_rank () over (partition by job_name order by Material_name,material_count), 2,material_count) MC2

From Tj.my_job_stackup_material_count MJS

where mjs.type_t= ' Prepreg '

Where MM2 is not null

) MJS12,

(SELECT *

From

(Select Mjs.job_name,

Decode (Dense_rank () over (partition by job_name order by Material_name,material_count), 3,material_name) MM3,

Decode (Dense_rank () over (partition by job_name order by Material_name,material_count), 3,material_count) MC3

From Tj.my_job_stackup_material_count MJS

where mjs.type_t= ' Prepreg '

)

Where MM3 is not null

) Mjs13,

(SELECT DISTINCT *

From

(Select Mjs.job_name,

Decode (Dense_rank () over (partition by job_name order by Material_name,material_count), 4,material_name) MM4,

Decode (Dense_rank () over (partition by job_name order by Material_name,material_count), 4,material_count) MC4

From Tj.my_job_stackup_material_count MJS

where mjs.type_t= ' Prepreg '

Where MM4 is not null

) MJS14

where Mjs15.job_name=mjs1.job_name (+)

and Mjs15.job_name=mjs2.job_name (+)

and Mjs15.job_name=mjs3.job_name (+)

and Mjs15.job_name=mjs11.job_name (+)

and Mjs15.job_name=mjs12.job_name (+)

and Mjs15.job_name=mjs13.job_name (+)

and Mjs15.job_name=mjs14.job_name (+)

')

UPDATE xbld_information SET Bm=b.describe from Xbld_information A, XBLD_BM b where a.bm=b.id