April 3, 2009 at 3:54 am
I have a sql database with various views and tables which users access via linked odbc in MS Access. They have no problem viewing the tables but they access the views. The sql for one of the view is as follows
ALTER VIEW [dbo].[vw_SUS_Challenge_AandE_OUTPUT]
AS
SELECT dbo.SUS_Challenge_AandE.AANDE_ID, dbo.SUS_Challenge_AandE.HOSPITAL_NO, Practice.PRACTICE_LONG_NAME, AE.Practice_Code, GP.GP_NAME,
dbo.SUS_Challenge_AandE.NHS_NUMBER, Patient.FORENAME, Patient.SURNAME, Patient.POSTCODE, dbo.SUS_Challenge_AandE.PROVIDER_CODE,
dbo.PBC_Providers.providerName, 'AE' AS APC_OP, dbo.SUS_Challenge_AandE.POD, AE.Sex, dbo.udf_calculateAge(AE.Date_of_Birth,
dbo.SUS_Challenge_AandE.ATTENDANCE_DATE) AS Age, dbo.SUS_Challenge_AandE.COST, dbo.SUS_Challenge_AandE.NOTES,
dbo.SUS_Challenge_AandE.MONTH_OF_ATTENDANCE,AE.Arrival_Date,dbo.SUS_Challenge_AandE.PRICE_SOURCE, dbo.SUS_Challenge_Reasons.QUERY_STATUS,
dbo.SUS_Challenge_AandE.CHALLENGE_GROUP, dbo.SUS_Challenge_Groups.GROUP_NAME, dbo.SUS_Challenge_Reasons.REASON
FROM dbo.PBC_Providers RIGHT OUTER JOIN
BRENTREPODB02.Olympus.dbo.GP_PRACTICE Practice RIGHT OUTER JOIN
nwcscmdsdata.dbo.Acc_and_Emer_CMDS_Data AE INNER JOIN
dbo.SUS_Challenge_AandE ON AE.AandE_ID = dbo.SUS_Challenge_AandE.AANDE_ID INNER JOIN
dbo.SUS_Challenge_Groups ON dbo.SUS_Challenge_AandE.CHALLENGE_GROUP = dbo.SUS_Challenge_Groups.CHALLENGE_GROUP INNER JOIN
dbo.SUS_Challenge_Reasons ON dbo.SUS_Challenge_AandE.CHALLENGE_GROUP = dbo.SUS_Challenge_Reasons.CHALLENGE_GROUP AND
dbo.SUS_Challenge_AandE.CHALLENGE_REASON = dbo.SUS_Challenge_Reasons.CHALLENGE_REASON LEFT OUTER JOIN
BRENTREPODB02.Olympus.dbo.NACS_GENERAL_PRACTITIONER GP ON AE.GP_Code = GP.GP_CODE COLLATE SQL_Latin1_General_CP1_CI_AS LEFT OUTER JOIN
BRENTREPODB02.Olympus.dbo.NSTS_PATIENT Patient ON dbo.SUS_Challenge_AandE.NHS_NUMBER = Patient.NHS_NUMBER COLLATE SQL_Latin1_General_CP1_CI_AS ON
Practice.GP_PRACTICE_CODE COLLATE SQL_Latin1_General_CP1_CI_AS = AE.Practice_Code ON
dbo.PBC_Providers.providerCode = dbo.SUS_Challenge_AandE.PROVIDER_CODE
WHERE (dbo.SUS_Challenge_Reasons.QUERY_STATUS = 'AA') OR (dbo.SUS_Challenge_Reasons.QUERY_STATUS = 'BB')
I have attached an image of the error message that the users get when they try and access the view in MS Access.
Any ideas on how to solve this would be very much appreciated.
April 3, 2009 at 11:10 am
I think the error message you are getting indicates a timeout. If you are using a linked table in Access then by default it is a DAO connection object and you cannot set the same breadth of tunable parameters that you can with an ADO connection. Specifically with an ADO connection in code you can set the connection and command timeout values to 0 (zero) which means no timeout, so it waits forever for rows to be returned. However with a default DAO connection you have no timeout values to set and so it gives up after a relatively short time.
If this is the case, then you could fix the situation by:
- creating an ADO connection inside a code module and connecting to the server via code
or
- put a clustered index on the view, which would have the effect of persisting the data in the indexed fields, causing the view to be constructed much more quickly
or
- simplify the view so it has fewer joins and takes less time and memory to build
Hope this helps.
--KC
April 3, 2009 at 12:11 pm
Hi
I think there is an authentication problem. If you have a look to your attached error message you see in last row that your Access application either tries to connect with windows authentication or without any log on information. Try to reconnect the views.
Greets
Flo
How to Post Data/Code to get the best Help How to Post Performance Problems
April 3, 2009 at 1:51 pm
indeed ... an authorzation issue to the linked server BRENTREPODB02.
How did you set up the linked server usage security ?
BTW: nice view ... using the mixture of right and inner joins.
Probably no technical problem with that, just your dba may have
a usage problem analysing it from right to left. (so from bottom to top)
Most of us prefer the combination with left joins because of the ease of reading and interpreting.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 3, 2009 at 2:11 pm
I'm confused. I was assuming it was only the standard MS Access linked-tables connection. If there is nothing wrong with the table links, could there be a separate security problem with the views? Maybe if the views are owned by a different schema that his user doesn't have access to?
--KC
April 4, 2009 at 4:04 am
After looking into the scenario more closely I have found it to be an authentication issue in a double hop scenario. By deploying delegation with configured Kerberos I managed to solve the problem
April 6, 2009 at 12:38 am
Thank you for the feedback.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply