July 27, 2004 at 4:49 am
Hi
We're trying to do the following in a Stored Procedure, but it fails to execute the final part (recreating the PKs)
If we split it into 2 SPs (with the PK bit being in the 2nd one) and then exec them both, it's fine.
Why won't it allow us to do it all in one SP?
It it to do with when things are committed or something like that ?
Any ideas ???
Colin
-----
DELETE FROM dbo.tblStatProBiSysMasterInput WHERE QuasarID IS NULL
ALTER TABLE dbo.tblStatProBiSysFeederInput
ALTER COLUMN FeeDate SMALLDATETIME NOT NULL
ALTER TABLE dbo.tblStatProBiSysFeederInput
ALTER COLUMN QuasarID INT NOT NULL
ALTER TABLE dbo.tblStatProBiSysMasterInput
ALTER COLUMN FeeDate SMALLDATETIME NOT NULL
ALTER TABLE dbo.tblStatProBiSysMasterInput
ALTER COLUMN QuasarID INT NOT NULL
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[PK_tblStatProBiSysFeederInput]'))
BEGIN
ALTER TABLE dbo.tblStatProBiSysFeederInput ADD CONSTRAINT
PK_tblStatProBiSysFeederInput PRIMARY KEY CLUSTERED
(
FeeDate,
QuasarID
) ON [PRIMARY]
END
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[PK_tblStatProBiSysMasterInput]'))
BEGIN
ALTER TABLE dbo.tblStatProBiSysMasterInput ADD CONSTRAINT
PK_tblStatProBiSysMasterInput PRIMARY KEY CLUSTERED
(
FeeDate,
QuasarID
) ON [PRIMARY]
END
July 27, 2004 at 5:56 am
Try adding GO between the PK re-creates and each other and the ALTER TABLE/COLUMN statements:
ALTER TABLE....
GO
RECREATE PK
GO
RECREATE PK
GO
The sp might just want some attention....
Good Hunting!
AJ Ahrens
webmaster@kritter.net
July 27, 2004 at 6:15 am
Tried things like that
However, when you save the SP in Enterprise Manager, it then removes everything that you keyed AFTER the first GO that it finds.
July 27, 2004 at 3:33 pm
Have you tried opening the sp via EM and pasting the code in then saving ???
Good Hunting!
AJ Ahrens
webmaster@kritter.net
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply