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

Specified Cast is not Valid

POWilliamsPOWilliams Posts: 3
edited May 20, 2005 11:09AM in SQL Compare Previous Versions
Greetings.

I too am receiving the following error when trying to compare our test database to our production database. I am on version 3.2.0.5. I tried to create a snapshot of either database and receive the error immediately.

I reviewed the thread for SQL Compare Bug and am receiving very similar results. I also ran the code attached by Brian Donahue to look for any identity fields with a NULL precision or scale value. All tables have a precision and scale.

The last code sent to the server by SQL Compare was:


SELECT o.name, USER_NAME(o.uid) AS owner, o.id, CONVERT(bit, OBJECTPROPERTY(o.id, 'TableHasActiveFulltextIndex')) AS FullTextIndexed,
sysfulltextcatalogs.name AS FullTextCatalogName,
sysfilegroups.groupname AS FileGroup,
(SELECT groupname FROM sysfilegroups INNER JOIN sysindexes ON sysindexes.groupid=sysfilegroups.groupid WHERE sysindexes.indid=255 and sysindexes.id=o.id) AS TextFileGroup

FROM dbo.sysobjects o WITH (NOLOCK)
LEFT JOIN sysfulltextcatalogs WITH (NOLOCK) ON sysfulltextcatalogs.ftcatid=o.ftcatid
LEFT JOIN sysindexes WITH (NOLOCK) ON sysindexes.id=o.id
LEFT JOIN sysfilegroups WITH (NOLOCK) ON sysfilegroups.groupid=sysindexes.groupid
WHERE OBJECTPROPERTY(o.id, N'IsTable')=1 AND OBJECTPROPERTY(o.id, N'IsMSShipped')=0 AND OBJECTPROPERTY(o.id, N'IsSystemTable')=0 AND OBJECTPROPERTY(o.id, N'TableIsFake')=0
AND sysindexes.indid<2
ORDER BY o.name

After running it manually, the only discrepancies i can find are:
1.) A couple objects have a TestFileGroup assigned as PRIMARY, but the FullTextIndexed column is 0 and the FullTextCatalogName is NULL. (There are a few of these.)
2.) There is one item that has a FullTextIndexed item = 1 but the FullTextCatalogName is NULL.

Could that be the problem?

Please advise.

Thank you.
Pete

Comments

  • Options
    Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Hello Pete,

    That could well be the problem. It's impossible to create a full-text index in SQL Server without assigning a name to it. What does the full-text catalogs container look like in Enterprise Manager when you open it for this database?
  • Options
    Thanks for the response Brian.

    I contacted the vendor for this app, as they were setting it up. It appears they screwed something up on the full-text index and it never got set up completely. The table was identified to be indexed, but a name, location, etc. was never provided.

    To resolve the problem, I needed to recreate an FT index for the table in question and then I removed it.

    Now, SQL Compare is able to read the database without a problem.

    As a suggestion, I would highly encourage the couple of scripts that have been posted in the forums to be placed somewhere on the site with a better description of what they are doing, so the users can easily decipher the type of problem they may be encountering. Just a thought.

    thank you.
    Pete
  • Options
    Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Hi Pete,

    Thanks for letting us know what's going on there. I'd be hesitant to post the queries that SQL Compare uses to get schema information from the database for the reason that the queries are subject to change. Plus, there is a whole different set of queries used in the case of SQL Server 2005!
This discussion has been closed.