Updating SQL Server Table from Oracle Table

[AngelB]

Hi All,
We are in the process of creating a data warehouse on SQL Server 2000 to centralise our data as we have several different databases and some quite fragmented data. I'm constructing a DTS package as part of this process, which needs to copy a products table from another SQL Server (this works fine) and then update certain fields with data from an Oracle database, specifically the unit cost. I'm struggling to find a way to do this as I can't get the syntax right. I have tried doing an update in a SQL task, but get the 'An error result was returned without an error message' when I try and parse the query. This is the query I am attempting:

UPDATE [Data_Warehouse].[dbo].[Sales_Products]
SET [Latest_Price]= "LIVEDB"."UNITCOSTS"."UNIT_COST"
WHERE [Sales_Product_Code]= "LIVEDB".RM_I01_UNITCOST"."ITEM"

The parts in quotes are the database, table and field name on the oracle database. This is the first time we've tried doing this so please ask if I haven't given enough detail.

Thanks


Geraint

The lights are on but nobody's home, my elevator doesn't go to the top. I'm not playing with a full deck, I've lost my marbles. Barenaked Ladies - Crazy

 

[ecobb]

It's been a while since I've done this, but I've set up almost the exact same thing before. First, you'll need to set up a Linked Server on the SQL Server back to the Oracle server. Then, you can reference it as "LinkedServerName[red]..[/red]DatabaseOwnerName.TableName"
Notice that when you're using a Linked Server, you reference it with 2 "dots" instead of 1. Try that and let us know how it works.

Hope This Helps!

ECAR
ECAR Technologies

http://www.ecartech.com

"My work is a game, a very serious game." - M.C. Escher

 

[mutley1]

Hi ECAR / Geraint,

Shouldn't that be

select LinkedServerName.[COLOR=red]DatabaseName..TableName.field[/color]

The DBO refers to the table (replaced by double period). There was no reference to the database name in previous code.

HTH,

M.

 

[mutley1]

Looking back at the update you posted, you want to update sales_products.latest_price. You are setting it to unitcosts.unit_cost where sales_product_code = RM_I01_unitcost_item. 1 quick Q - are unitcosts and RM_I01_unitcost supposed to be the same table (typo?)? If they are 2 seperate tables then there will need to be a link between the 2 tables on the linked server. Let me know if there are 2 tables you are referencing on the Linked server or if it is really just the 1 then will try to post something for you.

Cheers,

M

 

[ecobb]

No, you shouldn't need the Database Name, I think that's all done when you set up the Linked Server. Like I said, it's been a few years since I've fooled with it, but that's how I remember it working (at least with Oracle, anyway). You provide the database server info, database, username and password, and any other connection info needed. Then, you can just reference the Linked Server name, and SQL Server already has this info and knows what to connect to.

Hope This Helps!

ECAR
ECAR Technologies

http://www.ecartech.com

"My work is a game, a very serious game." - M.C. Escher

 

[AngelB]

mutley1, yes you are right. That's a type on my part. I renamed the table UNITCOSTS for it to make more sense for people reading the post and didn't rename it in the where clause. Thanks for your reply. Thanks to ECAR too.


Geraint

The lights are on but nobody's home, my elevator doesn't go to the top. I'm not playing with a full deck, I've lost my marbles. Barenaked Ladies - Crazy

 

[mutley1]

ECAR,

Yeah - good point, I remember now, same as you, all those years ago installing OTG and setting everything up. Don't have it at my current company so nothing to look at...

Cheers for jogging the old grey matter!

M.

 

[AngelB]

Hi Guys

I've now tried using the sp_addlinkedserver procedure, but am having a little difficulty with the exact syntax for it. Following BOL, the first argument is what I want to call my local server, the second is 'Oracle' since that's the type of database I'll be using. The third is 'MSDAORA' which is the OLE DB provider for Oracle and the fourth is the alias for the database. What I'm stuck on is how to tell the procedure exactly which database to use and which server it is on. I would have thought that just using the database name was not enough, since it lives on a different server and is one of many databases on that server. Any ideas?

Thanks


Geraint

The lights are on but nobody's home, my elevator doesn't go to the top. I'm not playing with a full deck, I've lost my marbles. Barenaked Ladies - Crazy