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 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply