X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V < 1 2  (Go to first unread post)
   Reply to this topicStart new topic
> Make Linked SQL Server Table Read Only, SQL Server 2012    
post Jan 16 2018, 01:31 AM

Posts: 2,316
Joined: 27-February 09

Maybe if the SQL Admins granted read-only access to a bunch of tables, and maybe created a schema for him where he could create tables? Then he can query against the read-only tables (because he's explicitly denied UPDATE and DELETE against them or the entire other schema?)
Go to the top of the page
post Yesterday, 03:47 AM

Posts: 183
Joined: 8-August 07
From: Doha, Qatar

yes, data has to be current.
I have a linked SQL server table now and have an inner join query to a ms access table but it's not as slow as joining it with pass thru query.
Thanks. Let me see if IT will let me have access to ssms. They say all I needed is datareader and datawriter access to create a view in SQL server. Is this correct?

That seems odd. My guess would be that the pass-thru *appears* slower because all the data is loading all at once (pass thrus, being read-only should be using snapshot) whereas the linked table is returning the first *batch* of records quickly for you to see while the remainder load in the background (an updateable data source will be using dynaset).

You may want to check by doing a
Select count(*) from [yourquery]

And substituting yourquery for the linked and pass-thru versions of your query and timing the results. The count(*) will force the sub query to process all records before returning a result.

As others have suggested, if you can move the Access data up to SQL or the SQL data down to Access then your join will perform much better. But if that is really not an option, then consider the below:

In your pass-thru you should avoid select * and only select the columns you need. Depending on the data types of the columns (especially varchar(max) and varbinary(max)) they may take a long time to transfer across the network. Try limiting the number of columns you return in the pass-thru to see if that has a positive impact on performance.

Consider modifying your application interface to fetch and display smaller chunks of data at a time. E.g. by passing a parameter in the pass thru query so you are only returning a subset of records (e.g. for a particular product, customer or time period). If the dataset is large, this is recommended practice anyway (reduces locking and network traffic) and there is little point in displaying thousands of rows of data at once when a user can only usefully consume a few hundred.

Go to the top of the page
2 Pages V < 1 2

Custom Search
RSSSearch   Top   Lo-Fi    22nd January 2018 - 08:56 AM