Specified cast is not valid
Matthieu
Posts: 16
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
Do I have to use SQL Compare with a user that has db_owner on the database?
Thanks
Comments
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
-Project Manager
-Red Gate Software Ltd
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
-Project Manager
-Red Gate Software Ltd
Thanks
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
-Project Manager
-Red Gate Software Ltd
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.
I sent the trace as you requested. Can you confirm you got the email.
Thanks
I should have just replied to your email.
Regards,
Jonahan
-Project Manager
-Red Gate Software Ltd
Thanks I got it. I was sick 4 days last week. let me know of the next steps.
Waiting on Jonathan. He was on vacation last week.
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
-Project Manager
-Red Gate Software Ltd
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
-Project Manager
-Red Gate Software Ltd