Red Gate forums :: View topic - SQL Invalid Check
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Prompt 6
SQL Prompt 6 forum

SQL Invalid Check

Search in SQL Prompt 6 forum
Post new topic   Reply to topic
Jump to:  
Author Message
danfountain



Joined: 25 Jun 2012
Posts: 8

PostPosted: Thu Jul 17, 2014 2:16 pm    Post subject: SQL Invalid Check Reply with quote

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
Back to top
View user's profile Send private message
Aaron Law



Joined: 28 Jun 2013
Posts: 244
Location: Red Gate Software

PostPosted: Thu Jul 17, 2014 2:35 pm    Post subject: Reply with quote

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:

%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.
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group