Object cache lecture fails

stan

Hello, I'm having a problem while using SQL Prompt 3.8.0.244 in SQL Server 2000, it's happening with 2 of my databases when refreshing cache it shows:

RedGate.SqlPrompt.Engine.Cache.DatabaseRefreshException ---> System.InvalidCastException: Unable to cast object of type 'System.DBNull' to type 'System.String'.
at k.q(SqlConnection )
at k.a(j )
at RedGate.SQLCompare.Engine.Database.RegisterForSqlPrompt(ConnectionProperties connectionProperties, Options options, Boolean includeSystemObjects)
at cy.b(IAuthenticationTokenProvider )
--- End of inner exception stack trace ---
at cy.b(IAuthenticationTokenProvider )
at cy.Refresh(IAuthenticationTokenProvider provider)
at ak.DoTask()

I saw an older post where you found a solution for SQL 2005 granting view definition to the user, but this doesn't works in SQL 2000, the user I'm using to authenticate is a sysadmin user.

Thanks

Ben Pinchen

Do you have SQL Compare installed on your machine? If so could you create a comparison using the database that you're having trouble connecting to? Could you firstly use the 'sa' password to connect to the database and try the comparison? Does this work? If it does could you try changing it to your sysadmin user and recomparing?

Do you get the same error as given in SQL Prompt?

If you don't have SQL Compare installed you can download a 14 day trial from www.red-gate.com

Let me know how it goes.

tanya

stan wrote:

Hello, I'm having a problem while using SQL Prompt 3.8.0.244 in SQL Server 2000, it's happening with 2 of my databases when refreshing cache it shows:

RedGate.SqlPrompt.Engine.Cache.DatabaseRefreshException ---> System.InvalidCastException: Unable to cast object of type 'System.DBNull' to type 'System.String'.
at k.q(SqlConnection )
at k.a(j )
at RedGate.SQLCompare.Engine.Database.RegisterForSqlPrompt(ConnectionProperties connectionProperties, Options options, Boolean includeSystemObjects)
at cy.b(IAuthenticationTokenProvider )
--- End of inner exception stack trace ---
at cy.b(IAuthenticationTokenProvider )
at cy.Refresh(IAuthenticationTokenProvider provider)
at ak.DoTask()

I saw an older post where you found a solution for SQL 2005 granting view definition to the user, but this doesn't works in SQL 2000, the user I'm using to authenticate is a sysadmin user.

Thanks

Hi,

Can you check if you have dbo permissions on those 2 databases?
Ideally, if you have sysadmin server role you would be mapped as dbo by default for the databases in the server unless someone manually sets different role permissions. But it’s worth checking as that could be the potential cause of this problem. Also, I am taking that the databases were not taken offline or something like that when this error happened?

Thanks,
Tanya
Project Manager
Red Gate Software Ltd

stan

Hi

Ben, i tried using SQL Compare and it shows me the same error "Unable to cast object of type 'System.DBNull' to type 'System.String'.

Tanya, i checked and the dbo user is mapped with my user 'dba' which is sysadmin and still it doesn't works, also databases as you said were not taken offline.

I forgot to tell the title from the error message, it says "SQL Prompt could not retrieve the database schema", the other text i posted was the error details.

Thanks for the help in advance.

Brian Donahue

Hi stan and Tanya,

FYI the stack trace indicates that this problem is happening whilst retrieving stored procedures. As well as a permission issue, it could be some errant bit of metadata such as a stored procedure in the sysobjects table and no corresponding entries in the syscomments table. With the limited information presented by the program, however, it's not possible to find the exact stored procedure without debugging SQL Prompt against your own schema.

Andras B

stan wrote:

Hi

Ben, i tried using SQL Compare and it shows me the same error "Unable to cast object of type 'System.DBNull' to type 'System.String'.

Tanya, i checked and the dbo user is mapped with my user 'dba' which is sysadmin and still it doesn't works, also databases as you said were not taken offline.

I forgot to tell the title from the error message, it says "SQL Prompt could not retrieve the database schema", the other text i posted was the error details.

Thanks for the help in advance.

Hi Stan,

The error you have seen usually means that the database system tables contained a null value in a column where our tools were not expecting it. There could be many reasons for this, like a fulltext catalog that has not been properly removed. As a first step, could you run DBCC CHECKDB on this database. This will find any corruptions in the database. If this does not find anything, could you upgrade to SQL Prompt 3.9 please? (We have included some improvements to handle broken database schema files, like the above mentioned fulltext catalogs). Also, could you tell me the compatibility level of the database pleas?

Regards,
Andras

stan

Hi Andras

CheckDB found 0 errors, here's the text:

CHECKDB found 0 allocation errors and 0 consistency errors in database 'Registro'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

My compatibility level for databases is SQL Server 2000 (80), I'm running with Service pack 3 (I don't know if this helps), I downloaded SQL Prompt 3.9 but it still finds problems while refreshing cache, it shows now:

Unable to refresh or cache database [urldb1].[Registro] due to the following error: An unexpected null value has been encountered while querying the database system tables. Please make sure that the database is in a consistent state by running DBCC CHECKDB. If the problem persists, please contact our support..

RedGate.SqlPrompt.Engine.Cache.DatabaseRefreshException: Unable to refresh or cache database [urldb1].[Registro] due to the following error: An unexpected null value has been encountered while querying the database system tables. Please make sure that the database is in a consistent state by running DBCC CHECKDB. If the problem persists, please contact our support.. ---> RedGate.SQLCompare.Engine.SqlCompareException: An unexpected null value has been encountered while querying the database system tables. Please make sure that the database is in a consistent state by running DBCC CHECKDB. If the problem persists, please contact our support. ---> System.InvalidCastException: Unable to cast object of type 'System.DBNull' to type 'System.String'.
at o.a(Boolean )
at o.a(n )
--- End of inner exception stack trace ---
at o.a(n )
at RedGate.SQLCompare.Engine.Database.RegisterForSqlPrompt(ConnectionProperties connectionProperties, Options options, Boolean includeSystemObjects, Boolean includeDependencies)
at cy.b(IAuthenticationTokenProvider )
--- End of inner exception stack trace ---
at cy.b(IAuthenticationTokenProvider )
at cy.Refresh(IAuthenticationTokenProvider provider)
at ao.DoTask()

Thanks

Andras B

stan wrote:

Hi Andras

CheckDB found 0 errors, here's the text:

CHECKDB found 0 allocation errors and 0 consistency errors in database 'Registro'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

...
Thanks

Sorry for the delay. Could you tell me the message displayed in the dialog box when you run SQL Compare and get the error message? Usually it is something like populating object text or stored procedures. (or a screenshot of the error dialog in SQL Compare should do as well) This information will help to find out which system views may have null values where we are not expecting them. I would send you an SQL Query to run to see where the null values are. Alternatively, you can run SQL Profiler, and look at the statement that is being executed when SQL Compare failes during the registration. If you can do this, could you rerun this query on the database in question, and tell me which columns contain null values/send me the result of this query. If you send the information directly to me (Andras.Belokosztolszki (at) red-gate.com) I'll try to get back to you the same/next day.

Regards,
Andras