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\Login s) 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?