July 1, 2009 at 10:18 am
I am processing some data and storing it in a staging table before exporting.
At the beginning, I called ALTER TABLE to remove composite Primary Key, from the staging table, composed of 3 columns.
Then I call ALTER TABLE ALTER COLUMN to make one column nullable. This column was part of the PK.
Then after processing and saving data to this table, I call ALTER TABLE ALTER COLUMN to make the same column NOT NULL.
Immediately after I call ALTER TABLE to create the PK as it was before I removed it.
The error I get is:
Cannot define PRIMARY KEY constraint on nullable column in table 'tmp_ManloadExport'.
Msg 1750, Level 16, State 0, Procedure sp_PrepareManloadExportData, Line 53
Could not create constraint. See previous errors.
It works fine if I run individual commands one by one, in quesry analyzer. And it also works fine in SQL2K5. But it is not working in an SP on SQL 2K. Any ideas?
Thanks.
kr
July 1, 2009 at 10:22 am
In SQL Server 2000, you may need to make those DDL statements dynamc sql and run them with the EXEC command in your stored proc.
July 1, 2009 at 3:27 pm
Thanks. I will try that approach.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply