Comparing VIEWS returns error "Specified Cast is not valid"

pandiani

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 ?

JonathanWatts

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

pandiani

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

pandiani

Sorry,
I forgot to state the error message.

>>> "Specified cast is not valid."

pandiani

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