February 18, 2009 at 8:31 pm
There is an ORACLE server where there is a Narratives Table with at least 65 million records. I have no control over this server. They have created a Snap shot view of this table that I can connect to from my SQL Server 2005. The end user is accessing information through a Microsoft Access database for now. Unfortunately the end users don't have direct access to the ORACLE serve so all requests for information have to go through the SQL Server. I know that I have to create a stored procedure on the SQL Server and pass the variable with the ID field(NRR_CIV_ID) to retrieve the correct record. The problem is I don't quite understand how to create a stored procedure. My skills are in the access world so I am trying to port my knowledge over to SQL Server. In access I can create a linked Table without having to store the whole table in the database. In SQL Server I don't know how to create a linked table to the ORACLE server? The end user will have a value that will tell them that a narrative exists for the event. (NRR_CIV_ID) What I need to do is create the following stored procedure Select dbo.ENF.Narratives.* from dbo.ENF.Narratives where NRR_CIV_ID='[Variable here]'
Do I need to create a table on the SQL server to store these narratives temporarily so that I can pass this on to the Access database? I'm sure this is as clear as mud but I'm doing my best to explain the situation. Any suggestions would be greatly appreciated. Again, the end user can not access the ORACLE server directly so a passthrough is required. Thanks again for your help,
Tyler
February 23, 2009 at 10:48 am
You don't need to create a proc, but I would. Makes it easier. Most things you can run from SQL Server, you could send through from Access as well.
you don't need a table to store the results, but the whole Oracle table will come back.
February 23, 2009 at 1:23 pm
You might consider a view instead.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 23, 2009 at 1:27 pm
You're also going to need to make sure that your security mapping is correct (meaning - the SQL user being used to do the work from Access needs to be able to run the Oracle query over whatever linked server or OPENDATASOURCE you choose).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 24, 2009 at 9:46 pm
The end users don't have direct access to the ORACLE tables. That is why all queries have to go through the SQL server. I have coded my credentials into other queries that I run to keep the information on the SQL server accurate. The users can access the SQL Server without any credentials. The problem is that I don't know how to set up the query so that it will accept the variable specified by the end user. The query that I need to have ready on the SQL Server is as follows. SELECT NARRATIVES.Narrative, Narratives.Title, Narratives.NRR_CIV_ID FROM NARRATIVES WHERE NRR_ID='{variable}' From what I can read and understand, I could store this as a procedure? THen the user provides the variable and the query is run. The problem then is I don't know how to pass the results on to then end user withough having the stored procedure append records to a Table that the end user can then query off directly from Access. I appreciate someone straightening me out where my logic is wrong:)
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply