Table based function using Row_Number() in SQL Server fails Linked Table connection (1 Viewer)

Rx_

Nothing In Moderation
Local time
Today, 00:57
Joined
Oct 22, 2009
Messages
2,803
A view with no unique data (part of a cube) didn't have unique columns to select with the Linked Table Manager.
So, we used the Row_Number() function to create unique values in the view.
Using Linked Table, and selecting the Entity_name column with the unique number returns #Deleted# in all rows and all columns.

If the ProductID in the inserted table has been modified, the joins won't work. One way to work around this limitation in SQL Server 2005 is to take advantage of the new ROW_NUMBER() function to create a row identifier in the view that won't be affected by changes to the ProductID. The following shows how you could alter the view to add a new durable RowID.

Answer: Started to read this and got to thinking.
The DBA set the Row_Number() return type as a bigInt. In Access linked tables, this comes back as text.
So, I made new view on the existing view using SSMS create View and added
In place of the RowNumber field, the following was substituted for a row in the SSMS View Designer:
(SELECT CAST(dbo.VW_EssBaseUploadStr.RowNumber AS integer) AS MyUnique) and made an Alias of MyUnique
Ran this, it looked the same from a linked table view.
But... it worked!
In create view SQL Server Management Studio designer, the open parens SELECT CAST...
This will create the subquery for the data type conversion. It also works efficiently in the execution plan for indexing.
The SQL window returned:
Code:
 SELECT        (SELECT        CAST(dbo.VW_EssBaseUploadStr.RowNumber AS integer) AS MyUnique) AS MyUnique, entity_name, clarity_id, LOB, expenditure_type, JAN, FEB, MAR, APR, MAY, JUN, JUL, AUG, SEP, OCT, NOV, DEC
FROM            dbo.VW_EBUploadStr

The DBA is experienced in SQL and Access Linked Tables. He is sure that the BigInt was not a problem in SQL Server 2008 r2 for Access 2007. I don't have time to verify that.

This is Microsoft SQL Server 2014 (version 12.0.2.8) using MS Office 2016.
After searching the internet, this was mentioned in a MS table showing data types in SQL Server and how they are consumed in MSAccess.

My guess is that a BigInt is so big, that Access ODBC consumes it as text that can include Null. This disqualifies a BigInt as an ODBC index.


Sorry to post a question then answer it. It is an 11 hour workday, and perhaps someone won't waste 3 hours on this as I did!
Happy 2017!
 
Last edited:

Minty

AWF VIP
Local time
Today, 06:57
Joined
Jul 26, 2013
Messages
10,355
I had a similar issue on a linked SQL table in a database designed by an outside agency. Took me ages to fathom it out and to be honest i'd forgotten about it.

Anyway to the good news - it's obviously a burning issue as M$ have decided to fix it https://fasttrack.microsoft.com/roadmap?filters=access scroll down and expand in development.

Shame they can't fix/improve some of the more important broken/missing stuff first...
 
  • Like
Reactions: Rx_

Users who are viewing this thread

Top Bottom