Change Identify/Primary Key Column Data Type?

Hi ...

I've

taken over a project where the tables were created with

identity/primary key columns of type DECIMAL(12,0). The latest addition

to the project is to replicate data down to Pocket PC applications.

Replication requires that identify/primary key columns be of type

INT/BIGINT.

I've attempted to ALTER TABLE xxx ALTER COLUMN yyy

BIGINT; and it fails. Failed due to all the foreign key constraints that have been created. While it didn't give any error messages associated with the the field being the PRIMARY KEY - I'm assuming I may get that error as well.

I then did an ALTER TABLE xxx NOCHECK CONSTRAINT

ALL; for every table in the system to disable checking of foreign keys

and then attempted to alter the column to a bigint and it still failed.

How

can I change the column from Decimal to BIGINT - or do I have to create

new tables, import all the data, get rid of the original tables? Please

tell me I don't have to do the latter.

Thanks ...Unfortunately the only way is to recreate the table or drop & recreate the column. This would require removing the existing constraints (PKs, FKs) and recreating them. Alternatively, you can create a view on the table(s) that does the conversion and replicate those. But this may or may not work depending on your replication scheme and you will have to ask in the Replication newsgroup.

Catastrophic failure

Hello there!

We have recently upgraded our SQL server to SQL server 2003.
Not entierly without problems i might ad..

The latest one is if you via Excel (2000 or 2003 is tested) try to make a local cube file, using the built-in query wizard, you get an errormessage that only say "Catastrophic failure" and nothing else.

If you instead of using the wizard, do the same thing directly in MsQuery (uncheck the box at the first "new database query window") you dont get the error.

Why!? Many of our users does not have the knowledge on how to write SQL querys and find the wizard very useful. So it is not an option to have them using MsQuery.

And ideas?

From the reference to cube I assume that you are trying to access Analysis Services. I will be moving the thread to the "Data Mining" forum, which has a higher chance to provide an answer.

|||

Well.. That is not correct..

You can create a "local cube" directly from any datasource in Excel..

Call it a lightversion.. ;)

//P?r

|||

[moving thread to the Analyis Services forum]

Did you install Excel or other Office components after you installed SQL Server 2005? In that case, you'll need to re-register "Program Files\Common Files\System\Ole DB\msolap90.dll" to regain connectivity to AS 2005 servers and local cubes.

FYI: The local cube functionality in Excel is provided by a "local server" version of Analysis Services.

|||

Well.. We first tried with the old installation of office 2000 and got the same error, but then we installed 2003 with no success.

But why does it work if you not use the query wizard?

|||If you've upgraded to 2005, the other thing you may want to do is download the SP1 update (which is available soon) as it fixes some issues with local cubes as well, although this may not be the issue you are seeing here.|||

We have now updated to SP1. But the problem remains.

Works without the wizard.. (using MsQuery)

|||

Not sure if you're seeing the same issue, but there was a recent fix made for the OLEDB provider in an upcoming service pack to accomodate an issue where the connection string was being limited to 256 characters when taking a cube offline from XL. You could try to choose a very short path for the local cube and see if this avoids the error.

Sorry you hit this issue and I hope that helps.