DB objects not being listed, Compatibility level 90

varunpuri

Hello,
I have started to evaluate the RedGate SQL Doc 2 recently, no doubt the tools looks great. But I have few issues working along with it.

I have a DB for quick ref. to it, its danish collation ( if it matters ) , SSMS v2008 R2 I am working on, DB size is 2.5 GB, I am able to expand and document many other databases with compatibility level as 80,90 but this one having 90 is not expanding objects at all.

I tried changing its level from 90 to 80 ( don't know why ) but its still not working either.

Am I wrong somewhere ? please put some light on it.

Best Regards,
Varun

peter.peart

Thanks for your post. Have you tried connecting to the DB using an SA account and seeing if you can expand the objects?

Generally we've seen this happen when there is a problem with permissions, so if it works using SA that will conclusively prove it is a permissions problem.

HTH!

Pete

varunpuri

Hello Peter,
Good to hear from you.

Tried with SA as well, just wont expand any object at all.

Just out of curiosity, I tried to use it with the same DB schema but with a blank DB instance and I see something happy to produce. It works fine now,

I guess the DB limit is an issue here, it just doesn't allows to expand the objects when big data is there ( still keeps me in question, as this tool talks on the schema level only, then why the data was a concern even, 1 thing RG may like to work on with this tool )

I will evaluate the tool further and get back if any flaws I would fine, additionally I also have a bit of fight with the way descriptions work, but that's the story all over RG boards

Regards,
Varun

peter.peart

Thanks for your reply. It shouldn't be having a problem with there being data in the DB at all, and it certainly shouldn't have an issue with something that's only 2.5GB in size.

We're more than happy to investigate this further, but we'd need a backup of the DB. Is that going to be possible at all?

Pete

nickmoores

Hi All,

Just to add my 2 cents to this, I've just experienced what I think is probably the same problem.

We have a few SQL Server 2000 servers, and SQL Doc 2.1.2.1 seems to be having trouble enumerating the objects in most (but not all) of the databases. It appears to work fine with the SQL Server 2005 databases that we have.

I've tried to do a bit of troubleshooting, so I downloaded SQL Doc 1.3 from the "old versions" section of the website - SQL Doc 1.3 works perfectly, and has no issues with these databases.

Back to 2.1.2.1, I set the minimum logs level to Verbose, and get the following entries in the log when I try to view the objects in the database:

15:31:02.671|Debug  |ProjectItemNode     |1  |MainThread:First expansion of ServerNode!
15:31:03.222|Info   |UI Controller       |13 |PreviewServerThread:Navigating preview pane to file:///C:/Documents and Settings/TestMachineUser/Local Settings/Temp/Red Gate/413aa1d6-8732-4be1-8106-3d58033b79f5.SD.html
15:31:05.195|Trace  |Engine              |14 |ExpandDatabaseThread:Registering databases
15:31:05.205|Trace  |Engine Internal     |14 |ExpandDatabaseThread:Registering WORKS
15:31:05.445|Trace  |Engine Internal     |14 |ExpandDatabaseThread:Create database WORKS
15:31:06.176|Trace  |Engine Internal     |14 |ExpandDatabaseThread:Finished registering WORKS
15:31:06.176|Trace  |Engine Internal     |14 |ExpandDatabaseThread:Begin LoadObjects
15:31:06.637|Info   |SQL Compare Engine  |14 |Populate Live Start, popoptions: Defaults
15:31:06.637|Info   |SQL Compare Engine  |14 |Populate Live Start, options: IgnoreWhiteSpace, IncludeDependencies, IgnoreUserProperties, IgnoreWithElementOrder, IgnoreDatabaseAndServerName, DecryptPost2kEncryptedObjects
15:31:06.968|Info   |SQL Compare Engine  |14 |Populate major version: SqlServer2000
15:31:06.968|Info   |SQL Compare Engine  |14 |Populate Case Sensitive: False
15:31:07.148|Info   |SQL Compare Engine  |14 |Populate FulltextCatalogs start.
15:31:07.238|Info   |SQL Compare Engine  |14 |Populate ObjectNames start.
15:31:07.398|Info   |SQL Compare Engine  |14 |Populate Users start.
15:31:07.518|Info   |SQL Compare Engine  |14 |Populate Tables start.
15:31:07.619|Info   |SQL Compare Engine  |14 |Populate Functions start.
15:31:07.859|Info   |SQL Compare Engine  |14 |Populate Defaults start.
15:31:07.909|Info   |SQL Compare Engine  |14 |Populate Rules start.
15:31:07.959|Info   |SQL Compare Engine  |14 |Populate UDTs start.
15:31:08.149|Info   |SQL Compare Engine  |14 |Populate Views start.
15:31:08.199|Info   |SQL Compare Engine  |14 |Populate View Cols start.
15:31:08.370|Info   |SQL Compare Engine  |14 |Populate Procs start.
15:31:08.490|Info   |SQL Compare Engine  |14 |Populate ObjectText start.
15:31:16.321|Info   |SQL Compare Engine  |14 |Populate Table Cols start.
15:31:17.272|Error  |Engine Internal     |14 |ExpandDatabaseThread:Asynchronous error occured 
System.InvalidCastException: Specified cast is not valid.
   at System.Data.SqlClient.SqlBuffer.get_SqlDecimal()
   at System.Data.SqlClient.SqlDataReader.GetSqlDecimal(Int32 i)
   at #8rg.#Ysg.#unh(Int32 #G8)
   at #8rg.#Ssg.#Vlh()
   at #8rg.#Ssg.#vl(DBPopulationOptions #WXrc)
   at RedGate.SQLCompare.Engine.Database.RegisterForDependencyViewer(ConnectionProperties connectionProperties, Options options)
   at RedGate.DependencyViewer.Engine.SqlCompareDatabaseWrapper.#crc()
   at RedGate.DependencyViewer.Engine.DefaultDatabase.LoadObjects(LoadBehaviour #Mpwc)
15:31:17.282|Error  |Engine Internal     |14 |ExpandDatabaseThread:Asynchronous error occured 
RedGate.DependencyViewer.Engine.ObjectLoadException: Failed to load objects from any database(s) on server 'SERVER.RemoteNetwork'. Check your login details are correct and that you have sufficient permissions on all databases.
   at #ZN3.#DtPb.LoadObjects(LoadBehaviour #Mpwc)
   at #ZN3.#RO3.LoadObjects(LoadBehaviour #Mpwc)
15:31:17.282|Trace  |Engine Internal     |14 |ExpandDatabaseThread:End LoadObjects
15:31:17.282|Trace  |Engine Internal     |14 |ExpandDatabaseThread:GetDatabase WORKS
15:31:17.282|Trace  |Engine Internal     |14 |ExpandDatabaseThread:Finsihed GetDatabase WORKS
15:31:17.282|Trace  |Engine Internal     |14 |ExpandDatabaseThread:Finished GetObjects WORKS
15:31:17.292|Debug  |ProjectItemNode     |14 |ExpandDatabaseThread:ExpandThread thinks it's finished

Hope this helps, as we would be interested in trialing this product further if it gets resolved. If any further information regarding the issue is required, we'd be keen to assist too.

Regards,
Nick