SQL Invalid Check

Provides intelligent code completion for SQL Server editors.

SQL Invalid Check

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

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?



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)

This shouldnt time out - the server has no issues.

Posts: 8
Joined: Mon Jun 25, 2012 8:43 am

Postby Aaron Law » Thu Jul 17, 2014 1: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.

Aaron Law
Posts: 597
Joined: Fri Jun 28, 2013 9:56 am
Location: Red Gate Software

Return to SQL Prompt

Who is online

Users browsing this forum: No registered users and 0 guests