November 3, 2004 at 4:14 pm
How do I use a view or query that references two different data sources ie 1 Oracle (APPPCT_PROJECT_TYPE ) and 1 table within my Sql. (ProjectTypes)
I want to use a query that looks and the data in the Oracle table compares it against my current table to determine if there are differenes and if so update current table with the differences in the data.
This is the query I would have used in Access: Help me to convert it use using DTS.
INSERT INTO ProjectTypes ( ProjectTypeCode, ProjectTypeDescrip, ProjectCatagory, ProjectClass, CapitalProjectNum, DateCreated, UserCreated, DateModified, UserModified )
SELECT APPPCT_PROJECT_TYPE.ID, APPPCT_PROJECT_TYPE.DESCR, APPPCT_PROJECT_TYPE.PROJ_DESCR_CATEGORY, APPPCT_PROJECT_TYPE.PROJ_CLASS, APPPCT_PROJECT_TYPE.CPN, Now() AS Expr1, "import" AS Expr2, Now() AS Expr3, "import" AS Expr4
FROM APPPCT_PROJECT_TYPE LEFT JOIN ProjectTypes ON APPPCT_PROJECT_TYPE.ID = ProjectTypes.ProjectTypeCode
WHERE (((ProjectTypes.ProjectTypeCode) Is Null));
Any assistance in this mater is greatly appreciated.
Sincerely,
Karen
November 3, 2004 at 4:18 pm
Hi Karen
I think you'll have to add the Oracle server as a "linked server" - this can be done from Enterprise Manager in SQL Server - and then you'll be able to write your queries (which will need to use fully qualified names). Check out linked servers in BOL and see how you get on.
By the way, I'm not convinced that DTS will be necessary for what you are trying to do, which sounds like straight T-SQL from your post.
Regards
Phil
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
November 3, 2004 at 4:22 pm
Unforunately, linking the Oracle server is not an option I need to use a UdL to reference the server - since this is the case is there a work around?
If this is an option - How do I go about linking the oracle tables into my sql database.
what is BOL?
November 4, 2004 at 6:27 am
BOL = Books online (Microsoft help file for sql server).
If you can't use a linked server you can do something like this (this is for dbase IV but I'm sure you can find the string for Oracle on google) :
Select A.pconum, CLIENT.Nom, ... from openrowset('MSDASQL',
'Driver=Microsoft Visual FoxPro Driver;
SourceDB=O:\Implic\AVA01;SourceType=DBF',
'
SELECT C.COCLI, PB.PCONUM, PB.PGL10, PB.PMONT10, L.LNOM, C.CODATEF, PB.PMONT1 AS ENTREES, L.LNOM, F.FNOM FROM CONTRA C INNER JOIN PYBBIL PB INNER JOIN LIVRE L ON PB.PGL10 = L.LNUM ON C.CONUM = PB.PCONUM INNER JOIN FOURNI F ON PB.PFOUR = F.FNUM WHERE PB.PCONUM IS NOT NULL AND PB.PCONUM "" AND PB.PGL10 > "30000" AND PB.PMONT10 > 0
') A INNER JOIN dbo.CLIENT ON A.COCLI = CLIENT.[no client]
You can find more information in BOL under "openrowset"
November 4, 2004 at 11:20 am
Thanks for your reply, however, I am quite new to SQL if you could help point me in the right direction, I would be greatful.
Here are my requirements for a query I need to create - this query needs to look at a linked Oracle table.
See attachments for setup detail and the error I am getting when I try to execute my simple query.
Thanks again.
Karen
November 4, 2004 at 12:21 pm
that's the only usefull information I found on oracle linked server in combinaison with the books online : http://www.sqlpass.org/Forums/messageview.cfm?catid=376&threadid=12178
near (provider='oraoledb';data source='oradbname', user id=scott;password=tiger)
I would help you further but I have 0 experience with Oracle and no test server to try the connection string on. I would assume that the connection string would look like this but any outside help is welcome :
Select A.* from OPENROWSET ('oraoledb', 'oradbname', 'Your query goes here... but fetch only information from Oracle') A inner join Yourtable on A. ...
November 4, 2004 at 12:31 pm
I found another article but it seems to be only using linked server.
http://www.winntmag.com/Windows/Articles/ArticleID/22264/pg/1/1.html
BTW why can't you add a linked server to Oracle in your environement?
November 4, 2004 at 12:38 pm
I just found out that I can link the Oracle tables to my sql - however I need assistance in the proper syntax to reference the Oracle tables within my queries.
can you help. I would like to send you an attachment - however I don't see that option on this forum.
November 4, 2004 at 12:47 pm
If you are talking about a linked server then it's gonna be something like OracleServerName.DbName.Owner.Table.
How did you linked the tables?
November 4, 2004 at 12:55 pm
ran store procedure to set up link tables - however, I am unable to view the data in these linked tables.
So can I send you the attachments I am talking about via email or is there a way to attach docs in this forum?
November 4, 2004 at 12:58 pm
I sent you my e-mail in your private messages section of your account.
November 4, 2004 at 1:12 pm
I think the first thing to do is to see if the link to oracle is working. In entreprise manager open the server where you added the linked server, then go under security, linked server, SiteManager, then click tables or views and check if you see the tables of oracle there, if you don't then the linked server doesn't work, if it does work then try querying like this :
Select A.* from OPENROWSET (SiteManager, 'your query goes here') A
November 4, 2004 at 1:32 pm
I can see the tables in the list of tables - however, I am unable to view the tables data. Does this mean I am not connected?
November 4, 2004 at 1:37 pm
Not that's normal. What it means is that the syntaxe in your query is wrong. Have you tried my query in the previous message?
I also thaught you could try this : Select * from SiteManager.DbName..TableName (really not sure it could work but just in case you didn't specify the right owner...).
November 4, 2004 at 3:14 pm
I am still quite confused - Please help me with the correct syntax(order) of the query when using a linked Database server
ServerName = "SiteManager"
Database = SMODBC
User = WindowNT Login
Password = WindowNT Password
TableName = Project_Type
SCHEMA = APPCT
Catalog = empty
User = User
Please create a simple example query where you use the above information.
I am a visual person so if you could give me a specific example it would help me.
Thanks
Karen
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply