June 10, 2010 at 1:17 pm
I have a stored procedure that drops constraints (supposed to, anyway) truncates the table and re-populates it from production to a staging server. This bit of code will NOT work when executing the stored procedure but will run via query as a stand-alone bit of code. The database is on a SQL2005, sp3 server but is running in 2000 mode. I am logged in as sa so all rights should be fine.
This will run outside the proc but not withing. When I attempt to truncate the table, it tells me the foreign key constaint exists. I don't get it.
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_GLU_GOVERNMENT_SERVICES_PROVIDER_GLU_PROVIDER]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[GLU_GOVERNMENT_SERVICES_PROVIDER] DROP CONSTRAINT FK_GLU_GOVERNMENT_SERVICES_PROVIDER_GLU_PROVIDER
Anyone with any thoughts on this? The longer I stare at it, the more frustrated I get because it seems like it should be so easy. TIA.
-- You can't be late until you show up.
June 10, 2010 at 1:28 pm
No errors coming back? Could it be security context somehow?
June 10, 2010 at 1:31 pm
could it be possible that ANOTHER foreign key, a duplicate the specific FK you are looking for, but with a different, dyamically create name exists?
it's possible, unfortunately, to have duplicate foreign keys.
check the table [dbo].[GLU_GOVERNMENT_SERVICES_PROVIDER] for more than one, maybe?
Lowell
June 10, 2010 at 1:38 pm
here's a proc i have laying around for that situation with dyn named FK's: drops all the FK's on a specific column in a table
usage is simply EXEC DROP_FK_FROM_SPECIFIC_COLUMN TableName,ColumnName
if exists(select * from sysobjects where name = 'DROP_FK_FROM_SPECIFIC_COLUMN')
drop procedure DROP_FK_FROM_SPECIFIC_COLUMN
GO
CREATE PROCEDURE DROP_FK_FROM_SPECIFIC_COLUMN
@TableName varchar(30),
@ColumnName varchar(30)
AS
BEGIN
DECLARE @Constraint_to_Delete varchar(100)
DECLARE Constraint_Cursor CURSOR FOR
SELECT name AS ConstraintName FROM dbo.sysobjects
WHERE OBJECTPROPERTY(id, N'IsForeignKey') = 1
and id in
(SELECT constid FROM sysforeignkeys
INNER JOIN syscolumns ON sysforeignkeys.fkeyid = syscolumns.id
WHERE fkeyid IN (SELECT id FROM sysobjects
WHERE name = @Tablename AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
AND syscolumns.name = @ColumnName and fkey =colid)
OPEN Constraint_Cursor
FETCH NEXT FROM Constraint_Cursor INTO @Constraint_to_Delete
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'ALTER TABLE ' + @TableName + ' DROP CONSTRAINT '+@Constraint_to_Delete
EXEC ( 'ALTER TABLE ' + @TableName + ' DROP CONSTRAINT '+@Constraint_to_Delete )
FETCH NEXT FROM Constraint_Cursor INTO @Constraint_to_delete
END
CLOSE Constraint_Cursor
DEALLOCATE Constraint_Cursor
END
Lowell
June 10, 2010 at 1:40 pm
Steve Jones - Editor (6/10/2010)
No errors coming back? Could it be security context somehow?
That's what I'm thinking but I don't see it. The tables and stored proc are owned by dbo, bot the apps DB and the data DB are owned by sa. I'm logged in as sa. If I simply do the select in query, it's there. If I excute the two lines of code within query, it does what I expect it to do.
-- You can't be late until you show up.
June 10, 2010 at 2:00 pm
Nice code Lowell, thanks. It shows my foreign keys, with the name as to what should be being dropped. Still think it's security because I can execute it in query, just not during the stoed procedure execution. Thanks to both you and Steve. I'll keep looking!!
-- You can't be late until you show up.
June 10, 2010 at 2:24 pm
This may sound stupid, and I'm starting to reach a bit here, but is it possibly because there isn't a go after the command, such as:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_GLU_GOVERNMENT_SERVICES_PROVIDER_GLU_PROVIDER]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[GLU_GOVERNMENT_SERVICES_PROVIDER] DROP CONSTRAINT FK_GLU_GOVERNMENT_SERVICES_PROVIDER_GLU_PROVIDER
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_GLU_SHOPPING_PROVIDER_GLU_PROVIDER]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[GLU_SHOPPING_PROVIDER] DROP CONSTRAINT FK_GLU_SHOPPING_PROVIDER_GLU_PROVIDER
GO
-- You can't be late until you show up.
June 10, 2010 at 2:30 pm
if the stored proc is doing ALTER TABLE and then anything else, i think you have to switch to dynamic sql for the ALTER commands.....that fixes the GO statement, since a stored proc cannot have GO statements inside of it.
Lowell
June 10, 2010 at 2:40 pm
Thats kind of what just hit me. It's worth a shot. Thanks.
-- You can't be late until you show up.
June 11, 2010 at 12:50 pm
:blush: It was the "Go". What a dope - I couldn't see the forest for the trees!!! I should've walked away, cleared my head and figured it out much sooner instead of assuming security issues. It worked from query but not within the stored proc so I thought, no, it can't be the lousy code..... :blush:
Edit - I guess I was the one that needed advice!!
-- You can't be late until you show up.
June 11, 2010 at 10:28 pm
tosscrosby (6/11/2010)
:blush: It was the "Go". What a dope - I couldn't see the forest for the trees!!! I should've walked away, cleared my head and figured it out much sooner instead of assuming security issues. It worked from query but not within the stored proc so I thought, no, it can't be the lousy code..... :blush:Edit - I guess I was the one that needed advice!!
Heh... "GO figure". 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply