Comparing VIEWS returns error "Specified Cast is not valid"

pandianipandiani Posts: 10
edited May 10, 2006 7:36PM in SQL Compare Previous Versions
The Compare STOPS and returns an error when it reads my only View.



SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER VIEW [MSDW].[Branch_GetList]
AS
SELECT 1 as One FROM dbo.Branches
WHERE Branches.CustomerID = 2
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO


any idea ?

Comments

  • Hi there,

    Is there any way that we can get the complete schema for your database, that way we can narrow down the problem as unfortunately the script that you have provided doesn't cause any problems.

    Can I ask what permissions the user that you are using to connect SQL Compare to the database has? Are they dbo, sa or are they more limited?

    Regards,

    Jonathan
    Jonathan Watts

    -Project Manager
    -Red Gate Software Ltd
  • Jonathan,
    Unforntunately I cannot send you the DB schema (for obvious reasons).

    The user is SA, and the only thing that comes to mind is that we have 2 schemas on the DB (dbo & MSDW). The views are running under the schema MSDW and they refer to some dbo tables.

    Other than that, I don't know.

    When I remove the view, it works.
    If I change the view to "select * from table1" it also fails, so that's why I think the issue could be that it runs under a different SCHEMA.

    Let me know,
    filippo
  • Sorry,
    I forgot to state the error message.

    >>> "Specified cast is not valid."
  • I fix the issue by delete the schema and recreating it using something like

    CREATE SCHEMA [MSDW]
    AUTHORIZATION [dbo]
    GO

    Now SQLCompare works perfectly.

    Thanks,
    Filippo
This discussion has been closed.