SQL Invalid Check

Provides intelligent code completion for SQL Server editors.

Moderators: Anu Deshpande, Aaron Law, David Priddle

SQL Invalid Check

Postby danfountain » Thu Jul 17, 2014 2:16 pm

Hi
When i do a Invalid check on a database it seems to get half way through the stored procs then gives this error:

Login failed when attempting to find invalid objects in DATABASE.
To find invalid objects, your user must have:
• access to the database
• the VIEW DEFINITION permission granted on the database

I am a sysadmin on this sql server, and to be safe i have also granted view definition ALL.

Yet i am still getting this error on one database. Other databases on this same instance seem to work fine.

Could you assist?

Dan


------------------ADDITIONAL

I have just noticed its actually generating an error and the above is a red herring. Here is the error:

17 Jul 2014 13:05:22,508 [1] ERROR RedGate.SQLPrompt.SSMSUI.Tabs.InvalidObjects.ObjectErrorList - Error fetching list of invalid objects
System.Data.SqlClient.SqlException (0x80131904): Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at RedGate.InvalidObjects.ScriptExecutionValidator.GetErrorsForObject(IDbObjectWithUnadornedCreateScript objectToTest)
at RedGate.InvalidObjects.DatabaseValidator.GetErrors(IDatabaseCandidate database, CancelSignal cancelSignal, ProgressMeter progressMeter, IDbObjectValidator[] validators)
at RedGate.InvalidObjects.DatabaseValidator.GetErrors(ConnectionProperties connectionProperties, IMetadataCacheOptions cacheOptions, CancelSignal cancelSignal, ProgressMeter progressMeter)
at RedGate.SQLPrompt.SSMSUI.Tabs.InvalidObjects.ObjectErrorList..?(CancelSignal , ProgressMeter )
at RedGate.SQLPrompt.CommonUI.Utils.ReplaceableBackgroundWorker.<>c__DisplayClassf`1.<RunCancellableTask>b__b(Object param0)
ClientConnectionId:b20edece-6237-41b1-81ee-a376e468a5b2


This shouldnt time out - the server has no issues.

Dan
danfountain
 
Posts: 8
Joined: Mon Jun 25, 2012 9:43 am

Postby Aaron Law » Thu Jul 17, 2014 2:35 pm

Hi Dan,

I think you're correct in that the first error is a red herring and it's actually the timeout, but it's pretty strange that only that one database would time out and not the others. Perhaps it has a stored procedure that uses a synonym to another server?

To help figure out what's causing it, you can try increasing your log level by editing:
Code: Select all
%localappdata%\\Red Gate\\SQL Prompt 6\\LoggingConfiguration.xml

And change the <root> level from "WARN" to "INFO"
Running find invalid objects again should log something like "Checking validity of <objectname>" which might help narrow down the object causing the issue.

Thanks,
Aaron.
Aaron Law
 
Posts: 260
Joined: Fri Jun 28, 2013 10:56 am
Location: Red Gate Software


Return to SQL Prompt 6

Who is online

Users browsing this forum: Bing [Bot] and 0 guests