question

goldnhue avatar image
goldnhue asked

Update query works in 2005 but not in 2014

I backed up a SQL 2005 database and restored it on a different computer using SQL 2014. The following update query works fine on 2005 server, but gives the following error on the 2014. What might be causing this? Thank you in advance! ---------------Here is the Query: UPDATE Users SET LastOn='8/6/2016 6:43:13 PM' WHERE Users.UserID=17 ---------------Here is The Error message: Msg 102, Level 15, State 1, Procedure Users_UTrig, Line 9 Incorrect syntax near '44446'.
Msg 102, Level 15, State 1, Procedure Users_UTrig, Line 20 Incorrect syntax near '44446'. -----------------Here is the Users_UTrig stored trigger:
USE [Devalign]
GO
/****** Object: Trigger [dbo].[Users_UTrig] Script Date: 8/6/2016 8:27:32 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[Users_UTrig] ON [dbo].[Users] FOR UPDATE AS
SET NOCOUNT ON
/* * PREVENT UPDATES IF NO MATCHING KEY IN 'AccessLevel' */
IF UPDATE(AccessLevel)
BEGIN
IF (SELECT COUNT(*) FROM inserted) !=
(SELECT COUNT(*) FROM AccessLevel, inserted WHERE (AccessLevel.AccessLevel = inserted.AccessLevel))
BEGIN
RAISERROR 44446 'The record can''t be added or changed. Referential integrity rules require a related record in table ''AccessLevel''.'
ROLLBACK TRANSACTION
END
END

/* * PREVENT UPDATES IF NO MATCHING KEY IN 'TblJobTitle' */
IF UPDATE(JobTitleNum)
BEGIN
IF (SELECT COUNT(*) FROM inserted) !=
(SELECT COUNT(*) FROM TblJobTitle, inserted WHERE (TblJobTitle.JobTitleNum = inserted.JobTitleNum))
BEGIN
RAISERROR 44446 'The record can''t be added or changed. Referential integrity rules require a related record in table ''TblJobTitle''.'
ROLLBACK TRANSACTION
END
END

/* * CASCADE UPDATES TO 'UsersCP' */
IF UPDATE(UserID)
BEGIN
UPDATE UsersCP
SET UsersCP.UserID = inserted.UserID
FROM UsersCP, deleted, inserted
WHERE deleted.UserID = UsersCP.UserID
END
updatesql-server-2014syntax
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

JohnM avatar image
JohnM answered
The syntax for the RAISEERROR was changed in 2012 (I think that's the version). If you update those two lines to the newer syntax it might help solve your problem. Reference: https://msdn.microsoft.com/en-us/library/ms178592.aspx Another option would be to utilize THROW instead. Reference: https://technet.microsoft.com/en-us/library/ee677615.aspx Hope that helps!!
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

goldnhue avatar image
goldnhue answered
That was it, THANK YOU!!!
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

JohnM avatar image JohnM commented ·
Awesome!! Glad that solved your problem!
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.