Report / Query won't return under some user accounts

[Rabbit]

Access 2007 front end, SQL Server 2008 R2 back end, Windows 7 client computers

For some of the more complicated reports / queries, the front end will lock up and never return anything for some users, but will return in a few seconds for other users on the same computer. This happens regardless of which computer they go to. Permissions look the same for the different users on SQL Server. The only thing I can think of is some sort of misconfigured user specific setting that's replicated to the computers from the domain. But I have no idea what this setting could even be. Has anyone experienced this? Or have any other ideas about what might be causing it?

[Seth Schrock]

Are you using Windows authentication or SQL users to sign into the SQL server?

[Rabbit]

Windows authentication

[Seth Schrock]

Can the user run the query through SSMS?

[NeoPa]

Have you compared execution plans on the server for the two different scenarios? This can make an awful lot of a difference.

If the SQL is passed using actual literal values instead of variable placeholders it can make a good estimate in some circumstances and a less-good (Drastically awful) one in others.

If it's coming via the driver from Access the chances are it's passing literal values so won't be able to use the cached EPs.

[jforbes]

I've had some strange results, similar to what you are talking about, when using Windows Authentication and calling a Query that Joins to a View which pulls data from a different Database than the one the View is located in. The lesson I learned is, permissions can get hairy going across databases and easily lost.
Maybe, the users that are having trouble do not have permission to an object that the Query is referencing?

It's doubtful, but maybe check Access/Office's Trust settings and see if there is a difference between a working user and non-working user.

[Rabbit]

Here's the weirdest thing, and it slipped my mind earlier, for 2 of the reports, they used to be able to run it. They became unable to run it after I added additional criteria in the where clause. Yet the other users can run it just fine.

@Seth, they don't have SSMS installed, just the drivers.

@NeoPa, I've looked at the SQL that each user sends to the server and they look the same. But I haven't checked the execution plan.

@jforbes, if there were permission issues, shouldn't it error out right away? We are using a view, but it's in the same database.

[jforbes]

I would expect a permissions problem should error our right away as long as execution is taking place all in the same database. What I ran into was going across databases.

I've got a few Views where I am lazy and use * to Select from some of the base tables. When those base tables change, like adding a column, I need to run this script to refresh the View's Metadata and then refresh the Links in Access. If I don't Access can act really odd, like not returning data when it should, without error; Or running away when it tries to perform a Query. Maybe it will help.

--Source: http://blog.devart.com/refreshing-objects-of-sql-server-databases.html
 
--USE [your_db] 
--GO 
PRINT ' -- Refreshing all VIEWS in database ' + QUOTENAME(DB_NAME()) + ' :' 
DECLARE @stmt_refresh_object nvarchar(400) 
DECLARE c_refresh_object CURSOR FOR 
SELECT DISTINCT 
  'EXEC sp_refreshview '''+QUOTENAME(ss.name)+'.'+QUOTENAME(so.name)+'''' AS stmt_refresh_views 
FROM sys.objects AS so
INNER JOIN sys.sql_expression_dependencies AS sed
ON so.object_id = sed.referencing_id
INNER JOIN sys.schemas AS ss
ON so.schema_id = ss.schema_id 
WHERE so.type = 'V' AND sed.is_schema_bound_reference = 0 
 
OPEN c_refresh_object 
FETCH NEXT FROM c_refresh_object 
INTO @stmt_refresh_object 
WHILE @@FETCH_STATUS = 0    
BEGIN       
   PRINT @stmt_refresh_object
   exec sp_executesql @stmt_refresh_object
   FETCH NEXT FROM c_refresh_object 
   INTO @stmt_refresh_object    
END
CLOSE c_refresh_object 
DEALLOCATE c_refresh_object 
GO 
 
 
PRINT ' -- Refreshing all DML TRIGGERS in database ' + QUOTENAME(DB_NAME()) + ' :'
DECLARE @stmt_refresh_object nvarchar(400) 
DECLARE c_refresh_object CURSOR FOR 
SELECT DISTINCT 
  'EXEC sp_refreshsqlmodule '''+QUOTENAME(schemas.name)+'.'+QUOTENAME(triggers.name)+'''' AS stmt_refresh_dml_triggers 
FROM sys.triggers AS triggers WITH(NOLOCK)   
INNER JOIN sys.objects AS objects WITH(NOLOCK)       
ON objects.object_id = triggers.parent_id   
INNER JOIN sys.schemas AS schemas WITH(NOLOCK)       
ON schemas.schema_id = objects.schema_id   
LEFT JOIN sys.sql_modules AS sql_modules WITH(NOLOCK)       
ON sql_modules.object_id = triggers.object_id   
LEFT JOIN sys.assembly_modules AS assembly_modules WITH(NOLOCK)       
ON assembly_modules.object_id = triggers.object_id   
LEFT JOIN sys.assemblies AS assemblies WITH(NOLOCK)       
ON assemblies.assembly_id = assembly_modules.assembly_id   
LEFT JOIN sys.database_principals AS principals WITH(NOLOCK)       
ON principals.principal_id = assembly_modules.execute_as_principal_id         
OR principals.principal_id = sql_modules.execute_as_principal_id 
WHERE RTRIM(objects.type) IN ('U','V') and parent_class = 1      
AND sql_modules.is_schema_bound = 0 
 
OPEN c_refresh_object 
FETCH NEXT FROM c_refresh_object 
INTO @stmt_refresh_object 
WHILE @@FETCH_STATUS = 0    
BEGIN       
   print @stmt_refresh_object        
   exec sp_executesql @stmt_refresh_object       
   FETCH NEXT FROM c_refresh_object 
   INTO @stmt_refresh_object    
END
CLOSE c_refresh_object 
DEALLOCATE c_refresh_object 
GO 
 
 
PRINT ' -- Refreshing all PROCEDURES in database ' + QUOTENAME(DB_NAME()) + ' :'
DECLARE @stmt_refresh_object nvarchar(400) 
DECLARE c_refresh_object CURSOR FOR
SELECT DISTINCT 
  'EXEC sp_refreshsqlmodule '''+QUOTENAME(s.name)+'.'+QUOTENAME(p.name)+'''' AS stmt_refresh_procedures 
FROM  sys.procedures AS p WITH(NOLOCK) 
LEFT JOIN  sys.schemas AS s WITH(NOLOCK)      
ON p.schema_id = s.schema_id 
LEFT JOIN  sys.sql_modules AS sm WITH(NOLOCK)      
ON p.object_id = sm.object_id 
LEFT JOIN  sys.assembly_modules AS am WITH(NOLOCK)      
ON p.object_id = am.object_id 
LEFT JOIN  sys.assemblies AS a      
ON a.assembly_id = am.assembly_id 
LEFT JOIN  sys.objects AS o WITH(NOLOCK)      
ON sm.object_id = o.object_id 
LEFT JOIN  sys.database_principals AS dp WITH(NOLOCK)      
ON sm.execute_as_principal_id = dp.principal_id          
OR am.execute_as_principal_id = dp.principal_id 
LEFT JOIN  sys.database_principals AS dp1 WITH(NOLOCK)      
ON o.principal_id = dp1.principal_id 
WHERE (CAST(CASE WHEN p.is_ms_shipped = 1 THEN 1
                 WHEN (SELECT major_id 
                       FROM sys.extended_properties 
                       WHERE major_id = p.object_id AND minor_id = 0 AND class = 1 AND name = 'microsoft_database_tools_support'
                       ) IS NOT NULL THEN 1             
                 ELSE 0 END AS bit
            )=0
       )
OPEN c_refresh_object 
FETCH NEXT FROM c_refresh_object 
INTO @stmt_refresh_object 
WHILE @@FETCH_STATUS = 0    
BEGIN       
   PRINT @stmt_refresh_object        
   exec sp_executesql @stmt_refresh_object       
   FETCH NEXT FROM c_refresh_object INTO @stmt_refresh_object    
END
CLOSE c_refresh_object 
DEALLOCATE c_refresh_object 
GO 
 
PRINT ' -- Refreshing all FUNCTIONS in database ' + QUOTENAME(DB_NAME()) + ' :'
DECLARE @stmt_refresh_object nvarchar(400) 
DECLARE c_refresh_object CURSOR FOR
SELECT DISTINCT 'EXEC sp_refreshsqlmodule '''+QUOTENAME(SCHEMA_NAME(o.schema_id))+'.'+QUOTENAME(o.name)+'''' AS stmt_refresh_functions 
FROM sys.objects AS o WITH(NOLOCK)   
LEFT JOIN sys.sql_modules AS sm WITH(NOLOCK)        
ON o.object_id = sm.object_id   
LEFT JOIN sys.assembly_modules AS am WITH(NOLOCK)        
ON o.object_id = am.object_id   
LEFT JOIN sys.database_principals p1 WITH(NOLOCK)        
ON p1.principal_id = o.principal_id   
LEFT JOIN sys.database_principals p2 WITH(NOLOCK)        
ON p2.principal_id=am.execute_as_principal_id   
LEFT JOIN sys.database_principals p3 WITH(NOLOCK)        
ON p3.principal_id=sm.execute_as_principal_id   
LEFT JOIN sys.assemblies AS ass WITH(NOLOCK)        
ON ass.assembly_id = am.assembly_id 
WHERE o.type IN ('FN','IF','TF','AF','FS','FT') and sm.is_schema_bound = 0 
OPEN c_refresh_object 
FETCH NEXT FROM c_refresh_object 
INTO @stmt_refresh_object 
WHILE @@FETCH_STATUS = 0    
BEGIN       
   PRINT @stmt_refresh_object        
   exec sp_executesql @stmt_refresh_object       
   FETCH NEXT FROM c_refresh_object INTO @stmt_refresh_object    
END
CLOSE c_refresh_object 
DEALLOCATE c_refresh_object 
GO 
 
PRINT ' -- Refreshing all DDL TRIGGERS on database ' + QUOTENAME(DB_NAME()) + ' :'
DECLARE @stmt_refresh_object nvarchar(400) 
DECLARE c_refresh_object CURSOR FOR
SELECT DISTINCT 'EXEC sp_refreshsqlmodule '''+QUOTENAME(t.name)+''','+'''DATABASE_DDL_TRIGGER''' as stmt_refresh_ddl_triggers 
FROM sys.triggers AS t WITH(NOLOCK)   
LEFT JOIN sys.sql_modules AS sm WITH(NOLOCK)       
ON t.object_id = sm.object_id   
LEFT JOIN sys.assembly_modules AS am WITH(NOLOCK)       
ON t.object_id = am.object_id   
LEFT JOIN sys.assemblies AS assemblies WITH(NOLOCK)       
ON assemblies.assembly_id = am.assembly_id   
LEFT JOIN sys.database_principals AS principals WITH(NOLOCK)       
ON principals.principal_id = sm.execute_as_principal_id          
OR principals.principal_id = am.execute_as_principal_id 
WHERE parent_class = 0 
OPEN c_refresh_object 
FETCH NEXT FROM c_refresh_object 
INTO @stmt_refresh_object 
WHILE @@FETCH_STATUS = 0    
BEGIN       
   print @stmt_refresh_object        
   exec sp_executesql @stmt_refresh_object       
   FETCH NEXT FROM c_refresh_object INTO @stmt_refresh_object    
END
CLOSE c_refresh_object 
DEALLOCATE c_refresh_object 
 
PRINT 'Metadata update for non-schema-bound objects is done.'

[Rabbit]

Thanks. I'll check out all the suggestions when I get a chance. Something more important came up and in the mean time, they will have to have another user run the report for them until I get a chance to look into this some more.

[NeoPa]

Rabbit:
@NeoPa, I've looked at the SQL that each user sends to the server and they look the same. But I haven't checked the execution plan.

If all the pre-determinable values are exactly the same then I would expect them to be identical, mostly. There are other variables that can make a difference.

Such values would have to include anything in the JOINs, the GROUPing, the WHERE & HAVING clauses obviously.

It's actually very difficult, in SQL of any but extremely basic complexity, to rule out the execution plan without looking at it. Generally it does a great job of choosing the best one, but there are various situations where it can flip from one to another and this can cause extraordinary levels of performance difference.

Without wishing to Spam, I can suggest that you look up a particular book that covers such stuff which I suspect you would find very useful (Not merely for this current issue of course, but I know how much you love SQL). I won't provide a link, but the title is "SQL Performance Explained".

[Rabbit]

I got some time to return to this issue. And here's what I've found after some more digging.

I was wrong, the SQL run by each user is different. The one that runs is standard T-SQL passed to the SQL Server. The one that hangs is sending a parameterized query that has a terrible execution plan.

And I can't figure out what is causing it to send one query for one user and a different query for another user.

I have checked the following:

• The ODBC drivers are the same for both users
• Microsoft Access version is the same for both users
• The user with the query that runs can do so even when logged onto the same computer as the user that can't run the query

So it seems like some sort of setting or configuration that travels with the user regardless of which PC they are on. This makes me think that it is some sort of obscure group policy setting or active directory setting that is part of the users account that is being replicated from PC to PC. Which probably means it's in the registry somewhere. But I am having a difficult time tracking down what that registry key is.

[NeoPa]

Rabbit:
But I am having a difficult time tracking down what that registry key is.

I can't be any help there, I'm afraid.

[jforbes]

Are both users using the same SQL login, different SQL Logins, or Windows Authentication? If they are using Windows Authentication or separate user Logins to SQL, you might want to compare the users in SQL (Security\Logins) to see if there is a difference. You've probably already done this, but I thought I would mention it.

[Rabbit]

Windows authentication, and yes, I've had the DBA check their permissions. I don't think it's on the server side because Access is the one generating the SQL.

[NeoPa]

@Rabbit.
You say the one that runs is passed T-SQL but the other one uses a parameterised query. Is it based off a Pass-Thru? Otherwise, how can Access be sending T-SQL?

[Rabbit]

It's not a pass-through query. But the ODBC driver that is used to send a query to the SQL Server has to take the Microsoft Access SQL and convert it into SQL that SQL Server can understand. Therefore, if the query in Access is using something like iif(field = 1, 'x', 'y'), then the ODBC driver needs to convert it to case when field = 1 then 'x' else 'y' end.

Since the two users have the same version of the odbc driver and the same version of access, it should be generating the same SQL after passing it through the ODBC driver.

Since it's generating different SQL, my assumption is that there is some sort of user specific ODBC setting that is different between the two users that is being set by our company's group policy server or active directory server.

[NeoPa]

My understanding is that, while the SQL that eventually gets through to SQL Server is T-SQL, that formulated by Access itself is some form of standard for ODBC. The driver then converts this to what's required by the far end, with different ODBC drivers doing different translations depending on the far end the driver is designed for. I may be wrong, but that's how I understand things.

As T-SQL is probably much closer to the defined standards than Access is, I would expect the intermediate SQL to look more similar to T-SQL than it might to Jet/ACE SQL.

At what point(s) are you able to capture the different versions? I imagine SSMS will allow you to see the eventual SQL being executed, but what about the other steps?

[zmbd]

Curiosity, launch the ODBC manager, you'll see "System DSNs" and "User DSNs". Check the User DSNs for something different between the two.
I used to have a PDF on these settings on the PC that crashed, I'll have to see if the file is still in the drive backup.

[zmbd]

Sorry, looks like I lost that file :(
I do however have a reference to the ODBC connector for MySQL which is what my work now uses for one of the lab information data servers (migrated away from SQL-Servers for some reason ( $ ? ) http://dev.mysql.com/doc/connector-o...config-options If you look at the table I believe the [no_ssps] option maybe something to take a look at... I can't imagine that the SQL-Server would be too much different; however, honestly, I am out of my depth here, at work I normally have our IT-DBA configure these (actually, I have to, the group policy settings have this completely locked out at work :( )

[Rabbit]

@Neopa, I turned on Trace SQL Mode on ODBC connections in Access which writes out all SQL commands passed to the server. It's there where I see the difference in queries being passed to the server. I was able to force my Access to send parameterized queries to the SQL Server by turning on fast requery in the registry. And it was still quick. So it looks like that's not necessarily the issue. The difference between my log with fast requery on and the slow user's log is that for some reason, their log shows a couple of extra queries being run that shouldn't be run. And also, it's sending a lot of fetch single row commands to the SQL Server.

@zmdb, the DSNs and driver versions are the same. Unfortunately, I can't find that ssps option in the SQL Server ODBC configuration. But in the registry, I believe the fast requery mentioned above is a similar option.

[NeoPa]

It sounds like I'm coming to the end of the road of being any help at all. The latest makes me think about the decisions made in the optimising about when to get data from the far end and run with that locally, and when to send the local data to the far end to let it do all the work. I have no idea what data it uses to determine which approach to use. I only mention it in case something I say triggers something in you that leads you to something that helps. All very nebulous I'm afraid, but the best I can do unfortunately.

Best of luck with it anyway.

[zmbd]

Rabbit,
Doing a shotgun search on this issue and grasping at any straws that get in the way... have you tried anything suggested in this MS article in the section dealing with performance issues https://technet.microsoft.com/en-us/...taccsql_topic2

scroll down a ways, there is a section on the connection settings....

[Rabbit]

Unfortunately I have already read that one

[zmbd]

I know you've most likely done this already...

From the IT Guru I use in-house suggested that the scheme was corrupted for the bad user. Said he ran into something along the same lines a few times. His fix, and it is hit/miss kind of thing:

Delete the bad user's front end.
Cold-boot, (X2)
Fresh copy of front-end.
-- Bypass any startups/automacro
-- if using the MDE/ACCDE/ACCR - copy of non-compiled MDB/ACCDB first replace this afterwards with the correct format
Compact/Repair.
Cold-Boot.

I'll keep searching :)

[Rabbit]

Tried something similar

[Rabbit]

Welp, the DBAs decided to resolve this by adding more memory. Rather than figuring out what was causing different SQL to be sent to the server.

[TheSmileyCoder]

That does sound very weird indeed.

Have you considered turning the entire query into a passthrough query, to ensure its always send in "SQL server" syntax?

[Rabbit]

I had thought of rewriting the entire query but it's a very involved query with complicated business criteria. Our group inherited this database so we support it for them but we didn't originally create it for them. I didn't want to get into the whole rewriting the report query because it would have required a few meetings with the business folk on the original purpose of the report.

[zmbd]

Related thread:
https://bytes.com/topic/access/answe...ery#post724604

For Each qdf in db.QueryDefs
 qdf.SQL = qdf.SQL
 Next