August 31, 2006 at 3:05 pm
When running a stored procedure on server1 (SQL2000), which includes references to tables on server2 (a linked server -SQL2005), the Optimizer can not obtain a good plan because the account used does not have sufficient permissions to execute DBCC Show_Statistics on server2. This results in excessive run time, rendering the query useless. When run under an account with dbo rights on the database on server2, the query runs fine. This behavior seems like it may be a bug!
Has anyone had similar experiences?
Terry
September 1, 2006 at 6:48 am
I suspect you may have a serious misunderstanding of some SQL basics. Be aware that linked server queries can exhibit some very unfriendly habits - you're better off executing remote procedures to return result sets - does depend but you do have to be careful.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
September 1, 2006 at 7:03 am
Thanks for the help Colin, and the friendly words.
Terry
September 5, 2006 at 2:28 pm
Apparently this behavior is not new with SQL2005. I performed testing with SQL2000, and the optimizer encounters the same DBCC Show_Statistics failure on the linked server.
FYI…the failure and success of the DBCC are captured via a trace capturing event 116 (Audit DBCC Event). For auditing and best practices, all of our linked servers need to run with security set to "using the login's current security context.
Terry
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply