Specified cast is not valid

Matthieu

I get this error message connecting to a SQL2005 database. If I connect with a user that has db_owner the problem goes away.

Do I have to use SQL Compare with a user that has db_owner on the database?

Thanks

JonathanWatts

Mattieu,

Whilst we do suggest that you use connect to databases as users who are in the db_owner role when you use SQL Compare, you should not be getting this error even if you aren't a dbo. May I ask which version of SQL Comare you are using please?

Regards,

Jonathan

Matthieu

5.2.0.32

JonathanWatts

Mattieu,

Due to the fact that you are on the latest version it looks like that you have found a new issue.

So that we can look into this issue in more detail will it be possible for you to supply us with a copy of your database schema or an example database of when this occurs?

If that is not possible can you run a trace using SQL Server Profiler against the database that is failing. Then whilst the trace is run can you reproduce the failure with SQL Compare. This should find out which query SQL Compare is failing on, if you can email the query to jonathan [dot] watts [at] red-gate [dot] com that would be great.

Regards,

Jonathan

Matthieu

This happens only in a shared environment where I don't have sysadmin. I tried to reproduce on my desktop with no luck. I'll see what I can do but it might not be possible to use either option.

Thanks

JonathanWatts

Mattieu,

My colleague has just pointed out to me that if you can connect as the db_owner and then take a snapshot we should be able recreate the problem using the snapshot.

Regards,

Jonathan

Matthieu

I created a snapshot of the database with SQL Compare. When I try to use the snapshot I can't specify a username so the problem does not appear.

At this time the DBA is restoring a more recent backup of our database (which we need to do to compare against) I'll let you know if the problem is still around with that newer backup.

Matthieu

Hi Jonathan,

I sent the trace as you requested. Can you confirm you got the email.

Thanks

JonathanWatts

Matthieu,

I should have just replied to your email.

Regards,

Jonahan

Matthieu

Jonathan Watts wrote:

Matthieu,

I should have just replied to your email.

Regards,

Jonahan

Thanks I got it. I was sick 4 days last week. let me know of the next steps.

rphillips99

Any resolution on this. I'm having the same issue. I haven't tried it with DBO yet.

Matthieu

rphillips99 wrote:

Any resolution on this. I'm having the same issue. I haven't tried it with DBO yet.

Waiting on Jonathan. He was on vacation last week.

JonathanWatts

Hi there,

I have just looked at the trace Matthieu sent us (many thanks for that ) and it appears that the problem occurs when SQL Compare reads check constraints and the logged in user has limited permissions, for example, if the user is a member of only the db_datareader role.

Whilst we recommend that you use SQL Compare as member of the dbo role we appreciate that this is not possible in every organisation and clearly this error should not be occurring.

We are looking into a fix for this problem, and I will post here shortly to give you an update as to when we hope to have a fix by.

Kind Regards,

Jonathan

Matthieu

Thanks for the update!

JonathanWatts

An update,

I have just had a chat with the lead SQL Compare developer.

It looks like we are going to have to insist that users are dbo in this circumstance, so that SQL Compare is able to access all the data from the system tables/views.

If the user is not dbo then SQL Server will return nulls from the system tables/views that the user doesn't have permission to. SQL Compare is then left with partial data with which it has to construct an object, which could lead to users seeing a table definition with unknown columns, data types or constraints.

As it would not be safe to present partial information, remove columns or take a guess what the columns in question are we are really going to need the correct permissions to get the information out of the system tables/views.

The documentation and the error message will hopefully be updated to this effect in the near future.

Sorry about this but we are constrained by the system here,

Regards,

Jonathan

Matthieu

Thanks for the update.