November 4, 2013 at 10:11 am
Sorry to be such a newbie. What would be the best options here?
The Linked Server query time even using a With Statement is 2 minutes on SQL Server Management Studio Query window.
The objective is to compare Name fields to match new entries on either server and build a Primary Key ID lookup table.
Then, use the two table's primary key ID to run a query and compare a field-by-field difference for Quality Assurance.
Why I thought a SQL View to the Linked Server Table would be best:
My Access Database uses SQL Server Native Client (DSN-Less connection) to my SQL Server 2008r2 database.
With the new Oracle Linked Server - table(s) my goal is to join a SQL Table with the Linked Server Oracle 11g Table in a view.
Please note: The Oracle DB is remote (across country) on a fairly slow VPN connection. A query from SSMS takes 2 minutes.
My Database with tables and views. RegulatoryDB
My Linked Servers includes an Oracle DB(s) with a Linked Server Views: Created a SQL Statement that successfully returns all the records
Select (the field Names)
FROM [NAV.RESOURCES.COM]..[NAV_DBA].[NV_VIEW] -- note this linked Server view from Oracle is Read Only
Note: the SQL Create View - takes out the square brackets shown in the line above.
Error Creating a View to Linked Server Table
If I run the Select Query from RegulatoryDB, it works. Tried to create a View using this query - SQL Execution Error - The object Name contains more than the maximum number of prefixes. The maximum is 3.
Alternative: Since this delay prevents a near real-time view - Should a procedure to Make Table be run every hour? The total table is about 0.5 MB.
November 4, 2013 at 10:54 am
What a nubie! :blush: Solved creating a Veiw against the Linked Server Table
CREATE VIEW XFiles
AS
SELECT [All the field names],
FROM [NAV.RESOURCES.COM]..[NAV_DBA].[NV_VIEW]
The SSMS would remove the square brackets, this worked fine.
OK, it still takes 2:25 min:sec to return 450,000 rows.
That is not going to work well in a QA query against a local SQL Table with 8,000 records
Just to return one unique ID with a Where Clause still takes 2:01 Min:Sec
Would creating a local table from this query be a solution? What would be the best way to accomplish this?
February 12, 2014 at 8:29 am
http://www.access-programmers.co.uk/forums/showthread.php?t=260764
Have been creating views from the Oracle Linked Servers.
Interesting -
Trying to create View from SSMS interface failes because the editor removes the square brackests from the
[V2.EGG.COM].
SELECT top 100 [AFE_ID]
FROM [V2.EGG.COM]..[NAV_DBA].[NV_WELL_AFE]
However, it runs great if this is in a script to create view.
For other Newbies, I have posted a side-by-side example of what works and what doesn't work at the link above.
Once the script is used to create a view, the view is connected to an Access 2010 using SQL Server Native Client with DSN Less scripting code, the records run much, much faster. Still a few seconds. But most of that can be attributed to a low-bandwidth VPN.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy