Where am I going wrong - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Where am I going wrong

Author

Topic

Gavin1977
Starting Member

16 Posts

Posted - 2008-06-05 : 06:45:55

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 '?'.

Thanks for reading.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-05 : 06:56:33

I dont think error is in posted code as i cant see a ? anywhere

Gavin1977
Starting Member

16 Posts

Posted - 2008-06-05 : 06:58:02

Apologies.

The error is:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '?'.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-05 : 07:02:08

quote:
Originally posted by Gavin1977

Apologies.

The error is:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '?'.

That i understood. i told this error is not in code you posted above as there is no ? appearing in code you posted.Its probably in some other code inside full batch you're excuting

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-06-05 : 07:02:27

How is it different than first error message you posted?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Gavin1977
Starting Member

16 Posts

Posted - 2008-06-05 : 07:04:30

Sorry guys. When posting for some reason the ? is mysteriously appearing between the ' '. Not sure why.

In terms of the error. Even when i open a new quey window and only have the following code in it:

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

I still get that error message.

Thanks again.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-06-05 : 07:07:23

Why is there question mark before IF NOT EXISTS ?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Gavin1977
Starting Member

16 Posts

Posted - 2008-06-05 : 07:08:55

To be honest im not sure why that has appeared there. It certainly isnt in my code.

Gavin1977
Starting Member

16 Posts

Posted - 2008-06-05 : 11:57:59

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!

Gavin1977
Starting Member

16 Posts

Posted - 2008-06-06 : 04:29:41

bump.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-06 : 04:32:01

quote:
Originally posted by Gavin1977

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!

Are you trying to open them from Object explorer in SSMS?

Gavin1977
Starting Member

16 Posts

Posted - 2008-06-06 : 04:53:19

Hi visakh16.

No the code in question is part of a larger script. However i have stipped the areas causing me problems (as above) out and run them in isolation in their own query window.

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-06 : 04:59:05

quote:
Originally posted by Gavin1977

Hi visakh16.

No the code in question is part of a larger script. However i have stipped the areas causing me problems (as above) out and run them in isolation in their own query window.

Thanks.

then where's the ? appearing on your code?

Gavin1977
Starting Member

16 Posts

Posted - 2008-06-06 : 05:29:23

The ? isnt appearing anywhere in my code, i explained this above. When i copied and pasted the error message from SSMS into the browser, it mysteriously added the ?.

The exact error message is:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ''.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-06 : 05:43:15

quote:
Originally posted by Gavin1977

The ? isnt appearing anywhere in my code, i explained this above. When i copied and pasted the error message from SSMS into the browser, it mysteriously added the ?.

The exact error message is:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ''.

not sure this error comes in code you posted as its executing sucessfully for me

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources