What are the challenges you face when working across database platforms? Take the survey
Options

Specified cast is not valid

MatthieuMatthieu Posts: 16
edited December 4, 2006 11:58AM in SQL Compare Previous Versions
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

Comments

  • Options
    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
    Jonathan Watts

    -Project Manager
    -Red Gate Software Ltd
  • Options
    5.2.0.32
  • Options
    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
    Jonathan Watts

    -Project Manager
    -Red Gate Software Ltd
  • Options
    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
  • Options
    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
    Jonathan Watts

    -Project Manager
    -Red Gate Software Ltd
  • Options
    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.
  • Options
    Hi Jonathan,

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

    Thanks
  • Options
    Matthieu,

    I should have just replied to your email.

    Regards,

    Jonahan
    Jonathan Watts

    -Project Manager
    -Red Gate Software Ltd
  • Options
    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.
  • Options
    Any resolution on this. I'm having the same issue. I haven't tried it with DBO yet.
  • Options
    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.
  • Options
    JonathanWattsJonathanWatts Posts: 418
    edited November 29, 2006 5:05AM
    Hi there,

    I have just looked at the trace Matthieu sent us (many thanks for that :D) 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
    Jonathan Watts

    -Project Manager
    -Red Gate Software Ltd
  • Options
    Thanks for the update!
  • Options
    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
    Jonathan Watts

    -Project Manager
    -Red Gate Software Ltd
  • Options
    Thanks for the update.
Sign In or Register to comment.