Specified Cast is not Valid
POWilliams
Posts: 3
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
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
This discussion has been closed.
Comments
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?
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
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!