November 29, 2007 at 3:55 pm
I am trying to delete a foreign key dynamically in my package.
I have come up with this so far but i keep on getting an "Incorrect syntax near 'parent_object_id" error. Can someone please tell me whats going on here..
declare @dk int
declare @table varchar(50)
declare @name varchar(50)
declare @sql nvarchar(1500)
set @dk= 20
set @table = 'abc' + ltrim(str(datepart(yyyy,dateadd(dd,-@DK,getdate())))) + ltrim(str(datepart(mm,dateadd(dd,-@DK,getdate()))))
set @nm = 'FK_P1' + ltrim(str(datepart(yyyy,dateadd(dd,-@DK,getdate())))) + ltrim(str(datepart(mm,dateadd(dd,-@DK,getdate()))))
select @sql = 'IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID' + @nm + 'AND parent_object_id = OBJECT_ID' + @table + ')'+ char(13) + 'Alter table' +char(13)+ @table+ char(13)+ 'drop constraint' +char(13)+ @nm
EXEC sp_executesql @sqql
They are going to give me a value for DK (daysback) because all the tables are created monthly with their corresponding FK's.
I am done with almost everything just stuck here...
November 29, 2007 at 4:33 pm
Cannot read your code to figure out what your executing
Try printing the @sqql variable to see what is actually being executed.
Print @sqql
EXEC sp_executesql @sqql
take the data that is returned to the messages tab and try copy/paste to a new windo and try to execute manually
November 29, 2007 at 7:13 pm
AFTER YOU PRINT YOUR VARIABLE, I THINK YOU'LL SEE HTE MISSING OPEN PARENTHESIS FOR OBJECT_ID FUNCTION:
parent_object_id = OBJECT_ID(' + @table + ')'
Lowell
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply