Where am i going wrong?

Question

Where am i going wrong?

gavin.duncan-770062

Ten Centuries

Points: 1066
More actions
June 5, 2008 at 4:48 am

#191748

Hello all.
Ive written the following code to check if a foreign key exists, and if it doesnt to add it to a table. The code i have is:
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblProductStockNote_tblLookup]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblProductStock]'))
BEGIN
PRINT N'Adding foreign keys to [dbo].[tblProductStock]'
ALTER TABLE [dbo].[tblProductStock] ADD
CONSTRAINT [FK_tblProductStockNote_tblLookup] FOREIGN KEY ([ProductStockNoteType]) REFERENCES [dbo].[tblLookup] ([ID])
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
END
However i keep getting the following error:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '?'.

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

dva2007 SSCertifiable Points: 7707 More actions · Answer 1

Hi,

i think it might be problem with ur table name sys.foreign_keys. please check the name of table. there is no obvious ? sysntax error anywhere.

regards,

vijay

gavin.duncan-770062 Ten Centuries Points: 1066 More actions · Answer 2

Hi im not sure why the ? appeared there. In the error message it just ''.

Anyway, i ran the following:

SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblProductStockNote_tblLookup]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblProductStock]')

And it returns the following:

FK_tblProductStockNote_tblLookup825106030NULL1793105916F FOREIGN_KEY_CONSTRAINT2008-06-05 14:08:59.7632008-06-05 14:08:59.76300019757574210000NO_ACTION0NO_ACTION0

Thanks again.

noeld SSC Guru Points: 96590 More actions · Answer 3

gavin.duncan (6/5/2008)
Hello all.
Ive written the following code to check if a foreign key exists, and if it doesnt to add it to a table. The code i have is:
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblProductStockNote_tblLookup]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblProductStock]'))
BEGIN
PRINT N'Adding foreign keys to [dbo].[tblProductStock]'
ALTER TABLE [dbo].[tblProductStock] ADD
CONSTRAINT [FK_tblProductStockNote_tblLookup] FOREIGN KEY ([ProductStockNoteType]) REFERENCES [dbo].[tblLookup] ([ID])
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
END
However i keep getting the following error:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '?'.

Can you comment that last line ?

---- IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END

* Noel

gavin.duncan-770062 Ten Centuries Points: 1066 More actions · Answer 4

Hey dude.

OK ive removed the line and my code is such:

?IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblProductStockNote_tblLookup]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblProductStock]'))

BEGIN

PRINT N'Adding foreign keys to [dbo].[tblProductStock]'

ALTER TABLE [dbo].[tblProductStock] ADD

CONSTRAINT [FK_tblProductStockNote_tblLookup] FOREIGN KEY ([ProductStockNoteType]) REFERENCES [dbo].[tblLookup] ([ID])

END

Im still getting the same error though.

Thanks again.

TheRedneckDBA SSChampion Points: 14001 More actions · Answer 5

Are you sure there aren't any stray characters in your query window? Might try copying the code to a fresh window to rule that out.

The Redneck DBA

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

Could you provide the DDL for the two tables involved?

😎

gavin.duncan-770062 Ten Centuries Points: 1066 More actions · Answer 7

Jason Shadonix (6/5/2008)
Are you sure there aren't any stray characters in your query window? Might try copying the code to a fresh window to rule that out.

Hi.

Yeah 100% sure there are no stray characters. Ive copied and pasted the above exactly..........and into a new query window. :unsure:

gavin.duncan-770062 Ten Centuries Points: 1066 More actions · Answer 8

Hmmm im now wondering if it is something to do with putting an ALTER command within a BEGIN......END.

Im getting similar weird errors when trying to alter views and stored procs!

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 9

Copy and paste will move stray characters. You'd have to retype it.

If you run various sections alone, do they work? Can you run the IF only?

gavin.duncan-770062 Ten Centuries Points: 1066 More actions · Answer 10

Hi mate.

Yes ive broken it down and ran the indivdual elements and they all run correctly. This one really has got me stumped!

gavin.duncan-770062 Ten Centuries Points: 1066 More actions · Answer 11

gavin.duncan-770062

Ten Centuries

Points: 1066

June 6, 2008 at 2:29 am

#825325

bump

Vladan SSC-Insane Points: 22072 More actions · Answer 12

I created dummy tables with proper name as used in the script and just 2 columns each, and your code worked without any problems or errors. I copied the script from your post, without any editing.

create table #tmperrors(error int)

create table tblProductStock(stid int, ProductStockNoteType int)

create table tblLookup(id int primary key, something varchar(10))

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblProductStockNote_tblLookup]')

AND parent_object_id = OBJECT_ID(N'[dbo].[tblProductStock]'))

BEGIN

PRINT N'Adding foreign keys to [dbo].[tblProductStock]'

ALTER TABLE [dbo].[tblProductStock] ADD

CONSTRAINT [FK_tblProductStockNote_tblLookup] FOREIGN KEY ([ProductStockNoteType])

REFERENCES [dbo].[tblLookup] ([ID])

IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION

IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END

END

--cleanup

drop table tblProductStock

drop table tblLookup