OPTIMIZER LOCK HINTS

I want to populate ULF with all the records from ULF_Backup in my License
database.
I can't get around this error, "OPTIMIZER LOCK HINTS"
Here is the SQL code I'm running from Query Analyzer.
sp_dbcmptlevel 'license', 70
GO
SET IDENTITY_INSERT ULF ON
INSERT INTO ULF (SPORTSMAN_KEY, LICENSE_YEAR, LICENSE_NUM, CONFIRMATION,
ULF_FORM_TYPE, BOOK_FORM_TYPE, BOOK_NUM, DEALER_BATCH_KEY, HARVEST_FLAG,
AMOUNT, COUNTY_OF_DEALER, HARVEST_DATE, HARVEST_TIME, TRANSACTION_DATE,
NO_PENALTY_FLAG, BP_CONFIRM_NUM, DUPLICATION_DATE, LICENSE_STATUS,
STATUS_DATE)
SELECT * FROM ULF_Backup (SPORTSMAN_KEY, LICENSE_YEAR, LICENSE_NUM,
CONFIRMATION, ULF_FORM_TYPE, BOOK_FORM_TYPE, BOOK_NUM, DEALER_BATCH_KEY,
HARVEST_FLAG, AMOUNT, COUNTY_OF_DEALER, HARVEST_DATE, HARVEST_TIME,
TRANSACTION_DATE, NO_PENALTY_FLAG, BP_CONFIRM_NUM, DUPLICATION_DATE,
LICENSE_STATUS, STATUS_DATE)
WITH (NO LOCK)
SET IDENTITY_INSERT ULF OFFPull out the select statement by itself and try to execute it. Before you
do that, do you see anything wrong with the sytax you are using? Hint -
there is something very wrong.|||The SELECT statement column was misplaced. Also, there was an extraneous
space in the NOLOCK hint. Try:
SET IDENTITY_INSERT ULF ON
GO
INSERT INTO ULF (
SPORTSMAN_KEY, LICENSE_YEAR, LICENSE_NUM, CONFIRMATION,
ULF_FORM_TYPE, BOOK_FORM_TYPE, BOOK_NUM, DEALER_BATCH_KEY,
HARVEST_FLAG, AMOUNT, COUNTY_OF_DEALER, HARVEST_DATE,
HARVEST_TIME, TRANSACTION_DATE, NO_PENALTY_FLAG, BP_CONFIRM_NUM,
DUPLICATION_DATE, LICENSE_STATUS, STATUS_DATE)
SELECT
SPORTSMAN_KEY, LICENSE_YEAR, LICENSE_NUM, CONFIRMATION,
ULF_FORM_TYPE, BOOK_FORM_TYPE, BOOK_NUM, DEALER_BATCH_KEY,
HARVEST_FLAG, AMOUNT, COUNTY_OF_DEALER, HARVEST_DATE,
HARVEST_TIME, TRANSACTION_DATE, NO_PENALTY_FLAG, BP_CONFIRM_NUM,
DUPLICATION_DATE, LICENSE_STATUS, STATUS_DATE
FROM ULF_Backup WITH (NOLOCK)
GO
SET IDENTITY_INSERT ULF OFF
GO
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Mike L" <Cadel@.nospam.nospam> wrote in message
news:9AFF21C9-C89A-4051-9C2F-9E5C4D91B3B4@.microsoft.com...
>I want to populate ULF with all the records from ULF_Backup in my License
> database.
> I can't get around this error, "OPTIMIZER LOCK HINTS"
> Here is the SQL code I'm running from Query Analyzer.
> sp_dbcmptlevel 'license', 70
> GO
> SET IDENTITY_INSERT ULF ON
> INSERT INTO ULF (SPORTSMAN_KEY, LICENSE_YEAR, LICENSE_NUM, CONFIRMATION,
> ULF_FORM_TYPE, BOOK_FORM_TYPE, BOOK_NUM, DEALER_BATCH_KEY, HARVEST_FLAG,
> AMOUNT, COUNTY_OF_DEALER, HARVEST_DATE, HARVEST_TIME, TRANSACTION_DATE,
> NO_PENALTY_FLAG, BP_CONFIRM_NUM, DUPLICATION_DATE, LICENSE_STATUS,
> STATUS_DATE)
> SELECT * FROM ULF_Backup (SPORTSMAN_KEY, LICENSE_YEAR, LICENSE_NUM,
> CONFIRMATION, ULF_FORM_TYPE, BOOK_FORM_TYPE, BOOK_NUM, DEALER_BATCH_KEY,
> HARVEST_FLAG, AMOUNT, COUNTY_OF_DEALER, HARVEST_DATE, HARVEST_TIME,
> TRANSACTION_DATE, NO_PENALTY_FLAG, BP_CONFIRM_NUM, DUPLICATION_DATE,
> LICENSE_STATUS, STATUS_DATE)
> WITH (NO LOCK)
> SET IDENTITY_INSERT ULF OFF|||no sure what that error is, as there are multiple errors for locking hints
but there are more than that here:
1. mixed INSERT..SELECT and INSERT..VALUES syntax.
2. NOLOCK hint is one word
3. are you truncating the ULF table first? if not, you'll need to check
for existing rows in the ULF table, based on the key. [as no DDL posted,
assuming SPORTSMAN_KEY, LICENSE_NUM is the key)
e.g.
INSERT INTO ULF (SPORTSMAN_KEY, LICENSE_YEAR, LICENSE_NUM, CONFIRMATION,
ULF_FORM_TYPE, BOOK_FORM_TYPE, BOOK_NUM, DEALER_BATCH_KEY, HARVEST_FLAG,
AMOUNT, COUNTY_OF_DEALER, HARVEST_DATE, HARVEST_TIME, TRANSACTION_DATE,
NO_PENALTY_FLAG, BP_CONFIRM_NUM, DUPLICATION_DATE, LICENSE_STATUS,
STATUS_DATE)
SELECT SPORTSMAN_KEY, LICENSE_YEAR, LICENSE_NUM,
CONFIRMATION, ULF_FORM_TYPE, BOOK_FORM_TYPE, BOOK_NUM, DEALER_BATCH_KEY,
HARVEST_FLAG, AMOUNT, COUNTY_OF_DEALER, HARVEST_DATE, HARVEST_TIME,
TRANSACTION_DATE, NO_PENALTY_FLAG, BP_CONFIRM_NUM, DUPLICATION_DATE,
LICENSE_STATUS, STATUS_DATE
FROM ULF_Backup WITH (NOLOCK)
WHERE NOT EXISTS (
SELECT * FROM ULF
WHERE SPORTSMAN_KEY = ULF_Backup.SPORTSMAN_KEY
AND LICENSE_NUM = ULF_Backup.LICENSE_NUM
)
Mike L wrote:
> I want to populate ULF with all the records from ULF_Backup in my License
> database.
> I can't get around this error, "OPTIMIZER LOCK HINTS"
> Here is the SQL code I'm running from Query Analyzer.
> sp_dbcmptlevel 'license', 70
> GO
> SET IDENTITY_INSERT ULF ON
> INSERT INTO ULF (SPORTSMAN_KEY, LICENSE_YEAR, LICENSE_NUM, CONFIRMATION,
> ULF_FORM_TYPE, BOOK_FORM_TYPE, BOOK_NUM, DEALER_BATCH_KEY, HARVEST_FLAG,
> AMOUNT, COUNTY_OF_DEALER, HARVEST_DATE, HARVEST_TIME, TRANSACTION_DATE,
> NO_PENALTY_FLAG, BP_CONFIRM_NUM, DUPLICATION_DATE, LICENSE_STATUS,
> STATUS_DATE)
> SELECT * FROM ULF_Backup (SPORTSMAN_KEY, LICENSE_YEAR, LICENSE_NUM,
> CONFIRMATION, ULF_FORM_TYPE, BOOK_FORM_TYPE, BOOK_NUM, DEALER_BATCH_KEY,
> HARVEST_FLAG, AMOUNT, COUNTY_OF_DEALER, HARVEST_DATE, HARVEST_TIME,
> TRANSACTION_DATE, NO_PENALTY_FLAG, BP_CONFIRM_NUM, DUPLICATION_DATE,
> LICENSE_STATUS, STATUS_DATE)
> WITH (NO LOCK)
> SET IDENTITY_INSERT ULF OFF|||Mike,
My guess is that you are passing a column name as a parameter to
a user-defined function (or something the parser thinks is one). What
is FROM ULF_Backup(column names here?) supposed to mean?
Generally the only time there is a parameter list in this part of a FROM
clause is when the source is a table-valued user-defined function, and
in that case the parameters cannot be column names.
Steve Kass
Drew University
Mike L wrote:

>I want to populate ULF with all the records from ULF_Backup in my License
>database.
>I can't get around this error, "OPTIMIZER LOCK HINTS"
>Here is the SQL code I'm running from Query Analyzer.
>sp_dbcmptlevel 'license', 70
>GO
>SET IDENTITY_INSERT ULF ON
>INSERT INTO ULF (SPORTSMAN_KEY, LICENSE_YEAR, LICENSE_NUM, CONFIRMATION,
>ULF_FORM_TYPE, BOOK_FORM_TYPE, BOOK_NUM, DEALER_BATCH_KEY, HARVEST_FLAG,
>AMOUNT, COUNTY_OF_DEALER, HARVEST_DATE, HARVEST_TIME, TRANSACTION_DATE,
>NO_PENALTY_FLAG, BP_CONFIRM_NUM, DUPLICATION_DATE, LICENSE_STATUS,
>STATUS_DATE)
>SELECT * FROM ULF_Backup (SPORTSMAN_KEY, LICENSE_YEAR, LICENSE_NUM,
>CONFIRMATION, ULF_FORM_TYPE, BOOK_FORM_TYPE, BOOK_NUM, DEALER_BATCH_KEY,
>HARVEST_FLAG, AMOUNT, COUNTY_OF_DEALER, HARVEST_DATE, HARVEST_TIME,
>TRANSACTION_DATE, NO_PENALTY_FLAG, BP_CONFIRM_NUM, DUPLICATION_DATE,
>LICENSE_STATUS, STATUS_DATE)
>WITH (NO LOCK)
>SET IDENTITY_INSERT ULF OFF
>|||I get the following error message when I run your code. Why and how do I
correct this error?
Server: Msg 545, Level 16, State 1, Line 1
Explicit value must be specified for identity column in table 'ULF' when
IDENTITY_INSERT is set to ON.
"Dan Guzman" wrote:

> The SELECT statement column was misplaced. Also, there was an extraneous
> space in the NOLOCK hint. Try:
> SET IDENTITY_INSERT ULF ON
> GO
> INSERT INTO ULF (
> SPORTSMAN_KEY, LICENSE_YEAR, LICENSE_NUM, CONFIRMATION,
> ULF_FORM_TYPE, BOOK_FORM_TYPE, BOOK_NUM, DEALER_BATCH_KEY,
> HARVEST_FLAG, AMOUNT, COUNTY_OF_DEALER, HARVEST_DATE,
> HARVEST_TIME, TRANSACTION_DATE, NO_PENALTY_FLAG, BP_CONFIRM_NUM,
> DUPLICATION_DATE, LICENSE_STATUS, STATUS_DATE)
> SELECT
> SPORTSMAN_KEY, LICENSE_YEAR, LICENSE_NUM, CONFIRMATION,
> ULF_FORM_TYPE, BOOK_FORM_TYPE, BOOK_NUM, DEALER_BATCH_KEY,
> HARVEST_FLAG, AMOUNT, COUNTY_OF_DEALER, HARVEST_DATE,
> HARVEST_TIME, TRANSACTION_DATE, NO_PENALTY_FLAG, BP_CONFIRM_NUM,
> DUPLICATION_DATE, LICENSE_STATUS, STATUS_DATE
> FROM ULF_Backup WITH (NOLOCK)
> GO
> SET IDENTITY_INSERT ULF OFF
> GO
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Mike L" <Cadel@.nospam.nospam> wrote in message
> news:9AFF21C9-C89A-4051-9C2F-9E5C4D91B3B4@.microsoft.com...
>
>|||I don't get an error. Can you post your CREATE TABLE statements?
Hope this helps.
Dan Guzman
SQL Server MVP
"Mike L" <Cadel@.nospam.nospam> wrote in message
news:0B0CF504-D831-4B47-A44A-49387B11514B@.microsoft.com...
>I get the following error message when I run your code. Why and how do I
> correct this error?
> Server: Msg 545, Level 16, State 1, Line 1
> Explicit value must be specified for identity column in table 'ULF' when
> IDENTITY_INSERT is set to ON.
> "Dan Guzman" wrote:
>|||The code is long because I created a script of the table. Here is the scrip
t.
CREATE TABLE [dbo].[ULF] (
[ULF_KEY] [Surrogate_Key] IDENTITY (1, 1) NOT NULL ,
[SPORTSMAN_KEY] [int] NULL ,
[LICENSE_YEAR] [smallint] NULL ,
[LICENSE_NUM] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CONFIRMATION] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ULF_FORM_TYPE] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[BOOK_FORM_TYPE] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BOOK_NUM] [int] NULL ,
[DEALER_BATCH_KEY] [int] NULL ,
[HARVEST_FLAG] [tinyint] NULL ,
[AMOUNT] [money] NULL ,
[COUNTY_OF_DEALER] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HARVEST_DATE] [datetime] NULL ,
[HARVEST_TIME] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TRANSACTION_DATE] [datetime] NULL ,
[NO_PENALTY_FLAG] [tinyint] NULL ,
[BP_CONFIRM_NUM] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DUPLICATION_DATE] [datetime] NULL ,
[LICENSE_STATUS] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[STATUS_DATE] [datetime] NOT NULL ,
[Lchg_Userid] [Last_Chg_Userid] NOT NULL ,
[Lchg_Date] [Last_Chg_Date] NOT NULL
) ON [PRIMARY]
GO
CREATE CLUSTERED INDEX [XIF418ULF] ON [dbo].[ULF]([BOOK_FORM_TYPE],
[BOOK_NUM]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[ULF] ADD
CONSTRAINT [DF__ULF__Lchg_Userid__789EE131] DEFAULT (user_name()) FOR
[Lchg_Userid],
CONSTRAINT [DF__ULF__Lchg_Date__7993056A] DEFAULT (getdate()) FOR
[Lchg_Date],
CONSTRAINT [PK__ULF__7A8729A3] PRIMARY KEY NONCLUSTERED
(
[ULF_KEY]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE UNIQUE INDEX [Num_Form_Type_Idx] ON [dbo].[ULF]([LICENSE_NUM],
[ULF_FORM_TYPE], [LICENSE_STATUS]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [XIF332ULF] ON [dbo].[ULF]([SPORTSMAN_KEY]) WITH FILLFACTOR
= 90 ON [PRIMARY]
GO
CREATE INDEX [XIF349ULF] ON [dbo].[ULF]([LICENSE_STATUS], [AMOUNT]) WITH
FILLFACTOR = 90, PAD_INDEX ON [PRIMARY]
GO
CREATE INDEX [XIF455ULF] ON [dbo].[ULF]([DEALER_BATCH_KEY]) WITH
FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [XIF421ULF] ON [dbo].[ULF]([COUNTY_OF_DEALER]) WITH
FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [XIF432ULF] ON [dbo].[ULF]([LICENSE_YEAR]) WITH FILLFACTOR =
90 ON [PRIMARY]
GO
CREATE INDEX [XIF440ULF] ON [dbo].[ULF]([ULF_FORM_TYPE]) WITH FILLFACTOR
= 90, PAD_INDEX ON [PRIMARY]
GO
CREATE INDEX [ULF_Num_Idx] ON [dbo].[ULF]([LICENSE_NUM]) WITH FILLFACTOR
= 90 ON [PRIMARY]
GO
CREATE INDEX [IX_ULF] ON [dbo].[ULF]([TRANSACTION_DATE]) WITH FILLFACTOR
= 90 ON [PRIMARY]
GO
CREATE INDEX [Index_BookNum] ON [dbo].[ULF]([BOOK_NUM]) WITH FILLFACTOR =
90 ON [PRIMARY]
GO
setuser
GO
EXEC sp_bindrule N'[dbo].[ZERO_ONE]', N'[ULF].[HARVEST_FLAG]'
GO
EXEC sp_bindrule N'[dbo].[ZERO_ONE]', N'[ULF].[NO_PENALTY_FLAG]'
GO
setuser
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
create trigger tD_ULF on dbo.ULF for DELETE as
/* ERwin Builtin Tue Nov 23 18:59:15 1999 */
/* DELETE trigger on ULF */
begin
declare @.errno int,
@.errmsg varchar(255)
/* ERwin Builtin Tue Nov 23 18:59:15 1999 */
/* ULF R/461 DEALER_TRAN ON PARENT DELETE RESTRICT */
if exists (
select * from deleted,DEALER_TRAN
where
/* %JoinFKPK(DEALER_TRAN,deleted," = "," and") */
DEALER_TRAN.LOST_ULF_KEY = deleted.ULF_KEY
)
begin
select @.errno = 30001,
@.errmsg = 'Cannot DELETE ULF because DEALER_TRAN exists.'
goto error
end
/* ERwin Builtin Tue Nov 23 18:59:15 1999 */
/* ULF R/451 REVENUE_DETAIL ON PARENT DELETE RESTRICT */
if exists (
select * from deleted,REVENUE_DETAIL
where
/* %JoinFKPK(REVENUE_DETAIL,deleted," = "," and") */
REVENUE_DETAIL.ULF_KEY = deleted.ULF_KEY
)
begin
select @.errno = 30001,
@.errmsg = 'Cannot DELETE ULF because REVENUE_DETAIL exists.'
goto error
end
/* ERwin Builtin Tue Nov 23 18:59:15 1999 */
/* ULF R/333 ULF_QUESTIONS ON PARENT DELETE RESTRICT */
if exists (
select * from deleted,ULF_QUESTIONS
where
/* %JoinFKPK(ULF_QUESTIONS,deleted," = "," and") */
ULF_QUESTIONS.ULF_KEY = deleted.ULF_KEY
)
begin
select @.errno = 30001,
@.errmsg = 'Cannot DELETE ULF because ULF_QUESTIONS exists.'
goto error
end
/* ERwin Builtin Tue Nov 23 18:59:15 1999 */
/* ULF R/4 ULF_PRIVILEGES ON PARENT DELETE RESTRICT */
if exists (
select * from deleted,ULF_PRIVILEGES
where
/* %JoinFKPK(ULF_PRIVILEGES,deleted," = "," and") */
ULF_PRIVILEGES.ULF_KEY = deleted.ULF_KEY
)
begin
select @.errno = 30001,
@.errmsg = 'Cannot DELETE ULF because ULF_PRIVILEGES exists.'
goto error
end
/* ERwin Builtin Tue Nov 23 18:59:15 1999 */
/* ULF R/335 HARVEST_TOTALS ON PARENT DELETE RESTRICT */
if exists (
select * from deleted,HARVEST_TOTALS
where
/* %JoinFKPK(HARVEST_TOTALS,deleted," = "," and") */
HARVEST_TOTALS.ULF_KEY = deleted.ULF_KEY
)
begin
select @.errno = 30001,
@.errmsg = 'Cannot DELETE ULF because HARVEST_TOTALS exists.'
goto error
end
/* ERwin Builtin Tue Nov 23 18:59:15 1999 */
return
error:
raiserror @.errno @.errmsg
rollback transaction
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
create trigger tI_ULF on dbo.ULF for INSERT as
/* BTG Incorporated - Builtin Trigger Tue Nov 23 18:59:15 1999 */
/* INSERT trigger on ULF */
begin
declare @.numrows int,
@.nullcnt int,
@.validcnt int,
@.errno int,
@.errmsg varchar(255)
/* BTG Incorporated - Builtin Trigger Tue Nov 23 18:59:15 1999 */
/* Update Last Change UserID and Last Change Date */
UPDATE ULF
SET lchg_date = GETDATE(),
lchg_userid = CURRENT_USER
FROM ULF, inserted
WHERE ULF.ULF_KEY = inserted.ULF_KEY
select @.numrows = @.@.rowcount
/* ERwin Builtin Tue Nov 23 18:59:15 1999 */
/* ULF_FORM_TYPE R/440 ULF ON CHILD INSERT RESTRICT */
if
/* %ChildFK(" or",update) */
update(ULF_FORM_TYPE)
begin
select @.nullcnt = 0
select @.validcnt = count(*)
from inserted,ULF_FORM_TYPE
where
/* %JoinFKPK(inserted,ULF_FORM_TYPE) */
inserted.ULF_FORM_TYPE = ULF_FORM_TYPE.ULF_FORM_TYPE
/* %NotnullFK(inserted," is null","select @.nullcnt = count(*) from
inserted where"," and") */
if @.validcnt + @.nullcnt != @.numrows
begin
select @.errno = 30002,
@.errmsg = 'Cannot INSERT ULF because ULF_FORM_TYPE does not
exist.'
goto error
end
end
/* ERwin Builtin Tue Nov 23 18:59:15 1999 */
/* LICENSE_YEAR R/432 ULF ON CHILD INSERT RESTRICT */
if
/* %ChildFK(" or",update) */
update(LICENSE_YEAR)
begin
select @.nullcnt = 0
select @.validcnt = count(*)
from inserted,LICENSE_YEAR
where
/* %JoinFKPK(inserted,LICENSE_YEAR) */
inserted.LICENSE_YEAR = LICENSE_YEAR.LICENSE_YEAR
/* %NotnullFK(inserted," is null","select @.nullcnt = count(*) from
inserted where"," and") */
select @.nullcnt = count(*) from inserted where
inserted.LICENSE_YEAR is null
if @.validcnt + @.nullcnt != @.numrows
begin
select @.errno = 30002,
@.errmsg = 'Cannot INSERT ULF because LICENSE_YEAR does not
exist.'
goto error
end
end
/* ERwin Builtin Tue Nov 23 18:59:15 1999 */
/* COUNTY R/421 ULF ON CHILD INSERT RESTRICT */
if
/* %ChildFK(" or",update) */
update(COUNTY_OF_DEALER)
begin
select @.nullcnt = 0
select @.validcnt = count(*)
from inserted,COUNTY
where
/* %JoinFKPK(inserted,COUNTY) */
inserted.COUNTY_OF_DEALER = COUNTY.COUNTY_CODE
/* %NotnullFK(inserted," is null","select @.nullcnt = count(*) from
inserted where"," and") */
select @.nullcnt = count(*) from inserted where
inserted.COUNTY_OF_DEALER is null
if @.validcnt + @.nullcnt != @.numrows
begin
select @.errno = 30002,
@.errmsg = 'Cannot INSERT ULF because COUNTY does not exist.'
goto error
end
end
/* ERwin Builtin Tue Nov 23 18:59:15 1999 */
/* DEALER_BATCH R/455 ULF ON CHILD INSERT RESTRICT */
if
/* %ChildFK(" or",update) */
update(DEALER_BATCH_KEY)
begin
select @.nullcnt = 0
select @.validcnt = count(*)
from inserted,DEALER_BATCH
where
/* %JoinFKPK(inserted,DEALER_BATCH) */
inserted.DEALER_BATCH_KEY = DEALER_BATCH.DEALER_BATCH_KEY
/* %NotnullFK(inserted," is null","select @.nullcnt = count(*) from
inserted where"," and") */
select @.nullcnt = count(*) from inserted where
inserted.DEALER_BATCH_KEY is null
if @.validcnt + @.nullcnt != @.numrows
begin
select @.errno = 30002,
@.errmsg = 'Cannot INSERT ULF because DEALER_BATCH does not
exist.'
goto error
end
end
/* ERwin Builtin Tue Nov 23 18:59:15 1999 */
/* LICENSE_BOOK R/418 ULF ON CHILD INSERT RESTRICT */
if
/* %ChildFK(" or",update) */
update(BOOK_FORM_TYPE) or
update(BOOK_NUM)
begin
select @.nullcnt = 0
select @.validcnt = count(*)
from inserted,LICENSE_BOOK
where
/* %JoinFKPK(inserted,LICENSE_BOOK) */
inserted.BOOK_FORM_TYPE = LICENSE_BOOK.BOOK_FORM_TYPE and
inserted.BOOK_NUM = LICENSE_BOOK.BOOK_NUM
/* %NotnullFK(inserted," is null","select @.nullcnt = count(*) from
inserted where"," and") */
select @.nullcnt = count(*) from inserted where
inserted.BOOK_FORM_TYPE is null and
inserted.BOOK_NUM is null
if @.validcnt + @.nullcnt != @.numrows
begin
select @.errno = 30002,
@.errmsg = 'Cannot INSERT ULF because LICENSE_BOOK does not
exist.'
goto error
end
end
/* ERwin Builtin Tue Nov 23 18:59:15 1999 */
/* LICENSE_STATUS R/349 ULF ON CHILD INSERT RESTRICT */
if
/* %ChildFK(" or",update) */
update(LICENSE_STATUS)
begin
select @.nullcnt = 0
select @.validcnt = count(*)
from inserted,LICENSE_STATUS
where
/* %JoinFKPK(inserted,LICENSE_STATUS) */
inserted.LICENSE_STATUS = LICENSE_STATUS.LICENSE_STATUS
/* %NotnullFK(inserted," is null","select @.nullcnt = count(*) from
inserted where"," and") */
if @.validcnt + @.nullcnt != @.numrows
begin
select @.errno = 30002,
@.errmsg = 'Cannot INSERT ULF because LICENSE_STATUS does not
exist.'
goto error
end
end
/* ERwin Builtin Tue Nov 23 18:59:15 1999 */
/* SPORTSMAN R/332 ULF ON CHILD INSERT RESTRICT */
if
/* %ChildFK(" or",update) */
update(SPORTSMAN_KEY)
begin
select @.nullcnt = 0
select @.validcnt = count(*)
from inserted,SPORTSMAN
where
/* %JoinFKPK(inserted,SPORTSMAN) */
inserted.SPORTSMAN_KEY = SPORTSMAN.SPORTSMAN_KEY
/* %NotnullFK(inserted," is null","select @.nullcnt = count(*) from
inserted where"," and") */
select @.nullcnt = count(*) from inserted where
inserted.SPORTSMAN_KEY is null
if @.validcnt + @.nullcnt != @.numrows
begin
select @.errno = 30002,
@.errmsg = 'Cannot INSERT ULF because SPORTSMAN does not exist.'
goto error
end
end
/* ERwin Builtin Tue Nov 23 18:59:15 1999 */
return
error:
raiserror @.errno @.errmsg
rollback transaction
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
create trigger tU_ULF on dbo.ULF for UPDATE as
/* BTG Incorporated - Builtin Trigger Tue Nov 23 18:59:15 1999 */
/* UPDATE trigger on ULF */
begin
declare @.numrows int,
@.nullcnt int,
@.validcnt int,
@.insULF_KEY Surrogate_Key,
@.errno int,
@.errmsg varchar(255)
/* BTG Incorporated - Builtin Trigger Tue Nov 23 18:59:15 1999 */
/* Update Last Change UserID and Last Change Date */
UPDATE ULF
SET lchg_date = GETDATE(),
lchg_userid = CURRENT_USER
FROM ULF, inserted
WHERE ULF.ULF_KEY = inserted.ULF_KEY
select @.numrows = @.@.rowcount
/* ERwin Builtin Tue Nov 23 18:59:15 1999 */
/* ULF R/461 DEALER_TRAN ON PARENT UPDATE RESTRICT */
if
/* %ParentPK(" or",update) */
update(ULF_KEY)
begin
if exists (
select * from deleted,DEALER_TRAN
where
/* %JoinFKPK(DEALER_TRAN,deleted," = "," and") */
DEALER_TRAN.LOST_ULF_KEY = deleted.ULF_KEY
)
begin
select @.errno = 30005,
@.errmsg = 'Cannot UPDATE ULF because DEALER_TRAN exists.'
goto error
end
end
/* ERwin Builtin Tue Nov 23 18:59:15 1999 */
/* ULF R/451 REVENUE_DETAIL ON PARENT UPDATE RESTRICT */
if
/* %ParentPK(" or",update) */
update(ULF_KEY)
begin
if exists (
select * from deleted,REVENUE_DETAIL
where
/* %JoinFKPK(REVENUE_DETAIL,deleted," = "," and") */
REVENUE_DETAIL.ULF_KEY = deleted.ULF_KEY
)
begin
select @.errno = 30005,
@.errmsg = 'Cannot UPDATE ULF because REVENUE_DETAIL exists.'
goto error
end
end
/* ERwin Builtin Tue Nov 23 18:59:15 1999 */
/* ULF R/333 ULF_QUESTIONS ON PARENT UPDATE RESTRICT */
if
/* %ParentPK(" or",update) */
update(ULF_KEY)
begin
if exists (
select * from deleted,ULF_QUESTIONS
where
/* %JoinFKPK(ULF_QUESTIONS,deleted," = "," and") */
ULF_QUESTIONS.ULF_KEY = deleted.ULF_KEY
)
begin
select @.errno = 30005,
@.errmsg = 'Cannot UPDATE ULF because ULF_QUESTIONS exists.'
goto error
end
end
/* ERwin Builtin Tue Nov 23 18:59:15 1999 */
/* ULF R/4 ULF_PRIVILEGES ON PARENT UPDATE RESTRICT */
if
/* %ParentPK(" or",update) */
update(ULF_KEY)
begin
if exists (
select * from deleted,ULF_PRIVILEGES
where
/* %JoinFKPK(ULF_PRIVILEGES,deleted," = "," and") */
ULF_PRIVILEGES.ULF_KEY = deleted.ULF_KEY
)
begin
select @.errno = 30005,
@.errmsg = 'Cannot UPDATE ULF because ULF_PRIVILEGES exists.'
goto error
end
end
/* ERwin Builtin Tue Nov 23 18:59:15 1999 */
/* ULF R/335 HARVEST_TOTALS ON PARENT UPDATE RESTRICT */
if
/* %ParentPK(" or",update) */
update(ULF_KEY)
begin
if exists (
select * from deleted,HARVEST_TOTALS
where
/* %JoinFKPK(HARVEST_TOTALS,deleted," = "," and") */
HARVEST_TOTALS.ULF_KEY = deleted.ULF_KEY
)
begin
select @.errno = 30005,
@.errmsg = 'Cannot UPDATE ULF because HARVEST_TOTALS exists.'
goto error
end
end
/* ERwin Builtin Tue Nov 23 18:59:15 1999 */
/* ULF_FORM_TYPE R/440 ULF ON CHILD UPDATE RESTRICT */
if
/* %ChildFK(" or",update) */
update(ULF_FORM_TYPE)
begin
select @.nullcnt = 0
select @.validcnt = count(*)
from inserted,ULF_FORM_TYPE
where
/* %JoinFKPK(inserted,ULF_FORM_TYPE) */
inserted.ULF_FORM_TYPE = ULF_FORM_TYPE.ULF_FORM_TYPE
/* %NotnullFK(inserted," is null","select @.nullcnt = count(*) from
inserted where"," and") */
if @.validcnt + @.nullcnt != @.numrows
begin
select @.errno = 30007,
@.errmsg = 'Cannot UPDATE ULF because ULF_FORM_TYPE does not
exist.'
goto error
end
end
/* ERwin Builtin Tue Nov 23 18:59:15 1999 */
/* LICENSE_YEAR R/432 ULF ON CHILD UPDATE RESTRICT */
if
/* %ChildFK(" or",update) */
update(LICENSE_YEAR)
begin
select @.nullcnt = 0
select @.validcnt = count(*)
from inserted,LICENSE_YEAR
where
/* %JoinFKPK(inserted,LICENSE_YEAR) */
inserted.LICENSE_YEAR = LICENSE_YEAR.LICENSE_YEAR
/* %NotnullFK(inserted," is null","select @.nullcnt = count(*) from
inserted where"," and") */
select @.nullcnt = count(*) from inserted where
inserted.LICENSE_YEAR is null
if @.validcnt + @.nullcnt != @.numrows
begin
select @.errno = 30007,
@.errmsg = 'Cannot UPDATE ULF because LICENSE_YEAR does not
exist.'
goto error
end
end
/* ERwin Builtin Tue Nov 23 18:59:15 1999 */
/* COUNTY R/421 ULF ON CHILD UPDATE RESTRICT */
if
/* %ChildFK(" or",update) */
update(COUNTY_OF_DEALER)
begin
select @.nullcnt = 0
select @.validcnt = count(*)
from inserted,COUNTY
where
/* %JoinFKPK(inserted,COUNTY) */
inserted.COUNTY_OF_DEALER = COUNTY.COUNTY_CODE
/* %NotnullFK(inserted," is null","select @.nullcnt = count(*) from
inserted where"," and") */
select @.nullcnt = count(*) from inserted where
inserted.COUNTY_OF_DEALER is null
if @.validcnt + @.nullcnt != @.numrows
begin
select @.errno = 30007,
@.errmsg = 'Cannot UPDATE ULF because COUNTY does not exist.'
goto error
end
end
/* ERwin Builtin Tue Nov 23 18:59:15 1999 */
/* DEALER_BATCH R/455 ULF ON CHILD UPDATE RESTRICT */
if
/* %ChildFK(" or",update) */
update(DEALER_BATCH_KEY)
begin
select @.nullcnt = 0
select @.validcnt = count(*)
from inserted,DEALER_BATCH
where
/* %JoinFKPK(inserted,DEALER_BATCH) */
inserted.DEALER_BATCH_KEY = DEALER_BATCH.DEALER_BATCH_KEY
/* %NotnullFK(inserted," is null","select @.nullcnt = count(*) from
inserted where"," and") */
select @.nullcnt = count(*) from inserted where
inserted.DEALER_BATCH_KEY is null
if @.validcnt + @.nullcnt != @.numrows
begin
select @.errno = 30007,
@.errmsg = 'Cannot UPDATE ULF because DEALER_BATCH does not
exist.'
goto error
end
end
/* ERwin Builtin Tue Nov 23 18:59:15 1999 */
/* LICENSE_BOOK R/418 ULF ON CHILD UPDATE RESTRICT */
if
/* %ChildFK(" or",update) */
update(BOOK_FORM_TYPE) or
update(BOOK_NUM)
begin
select @.nullcnt = 0
select @.validcnt = count(*)
from inserted,LICENSE_BOOK
where
/* %JoinFKPK(inserted,LICENSE_BOOK) */
inserted.BOOK_FORM_TYPE = LICENSE_BOOK.BOOK_FORM_TYPE and
inserted.BOOK_NUM = LICENSE_BOOK.BOOK_NUM
/* %NotnullFK(inserted," is null","select @.nullcnt = count(*) from
inserted where"," and") */
select @.nullcnt = count(*) from inserted where
inserted.BOOK_FORM_TYPE is null and
inserted.BOOK_NUM is null
if @.validcnt + @.nullcnt != @.numrows
begin
select @.errno = 30007,
@.errmsg = 'Cannot UPDATE ULF because LICENSE_BOOK does not
exist.'
goto error
end
end
/* ERwin Builtin Tue Nov 23 18:59:15 1999 */
/* LICENSE_STATUS R/349 ULF ON CHILD UPDATE RESTRICT */
if
/* %ChildFK(" or",update) */
update(LICENSE_STATUS)
begin
select @.nullcnt = 0
select @.validcnt = count(*)
from inserted,LICENSE_STATUS
where
/* %JoinFKPK(inserted,LICENSE_STATUS) */
inserted.LICENSE_STATUS = LICENSE_STATUS.LICENSE_STATUS
/* %NotnullFK(inserted," is null","select @.nullcnt = count(*) from
inserted where"," and") */
if @.validcnt + @.nullcnt != @.numrows
begin
select @.errno = 30007,
@.errmsg = 'Cannot UPDATE ULF because LICENSE_STATUS does not
exist.'
goto error
end
end
/* ERwin Builtin Tue Nov 23 18:59:15 1999 */
/* SPORTSMAN R/332 ULF ON CHILD UPDATE RESTRICT */
if
/* %ChildFK(" or",update) */
update(SPORTSMAN_KEY)
begin
select @.nullcnt = 0
select @.validcnt = count(*)
from inserted,SPORTSMAN
where
/* %JoinFKPK(inserted,SPORTSMAN) */
inserted.SPORTSMAN_KEY = SPORTSMAN.SPORTSMAN_KEY
/* %NotnullFK(inserted," is null","select @.nullcnt = count(*) from
inserted where"," and") */
select @.nullcnt = count(*) from inserted where
inserted.SPORTSMAN_KEY is null
if @.validcnt + @.nullcnt != @.numrows
begin
select @.errno = 30007,
@.errmsg = 'Cannot UPDATE ULF because SPORTSMAN does not exist.'
goto error
end
end
/* ERwin Builtin Tue Nov 23 18:59:15 1999 */
return
error:
raiserror @.errno @.errmsg
rollback transaction
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
"Dan Guzman" wrote:

> I don't get an error. Can you post your CREATE TABLE statements?
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Mike L" <Cadel@.nospam.nospam> wrote in message
> news:0B0CF504-D831-4B47-A44A-49387B11514B@.microsoft.com...
>
>|||Assuming the ULF_Backup backup table has the same schema as table ULF, all
you need to do is add the ULF_KEY column so that you can specify
IDENTITY_INSERT:
SET IDENTITY_INSERT ULF ON
GO
INSERT INTO ULF (
ULF_KEY,
SPORTSMAN_KEY, LICENSE_YEAR, LICENSE_NUM, CONFIRMATION,
ULF_FORM_TYPE, BOOK_FORM_TYPE, BOOK_NUM, DEALER_BATCH_KEY,
HARVEST_FLAG, AMOUNT, COUNTY_OF_DEALER, HARVEST_DATE,
HARVEST_TIME, TRANSACTION_DATE, NO_PENALTY_FLAG, BP_CONFIRM_NUM,
DUPLICATION_DATE, LICENSE_STATUS, STATUS_DATE)
SELECT
ULF_KEY,
SPORTSMAN_KEY, LICENSE_YEAR, LICENSE_NUM, CONFIRMATION,
ULF_FORM_TYPE, BOOK_FORM_TYPE, BOOK_NUM, DEALER_BATCH_KEY,
HARVEST_FLAG, AMOUNT, COUNTY_OF_DEALER, HARVEST_DATE,
HARVEST_TIME, TRANSACTION_DATE, NO_PENALTY_FLAG, BP_CONFIRM_NUM,
DUPLICATION_DATE, LICENSE_STATUS, STATUS_DATE
FROM ULF_Backup WITH (NOLOCK)
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Mike L" <Cadel@.nospam.nospam> wrote in message
news:F0077D93-CEA3-445E-A86A-688817B67352@.microsoft.com...
> The code is long because I created a script of the table. Here is the
> script.|||That fixed the problem.
Thank you.
"Dan Guzman" wrote:

> Assuming the ULF_Backup backup table has the same schema as table ULF, all
> you need to do is add the ULF_KEY column so that you can specify
> IDENTITY_INSERT:
> SET IDENTITY_INSERT ULF ON
> GO
> INSERT INTO ULF (
> ULF_KEY,
> SPORTSMAN_KEY, LICENSE_YEAR, LICENSE_NUM, CONFIRMATION,
> ULF_FORM_TYPE, BOOK_FORM_TYPE, BOOK_NUM, DEALER_BATCH_KEY,
> HARVEST_FLAG, AMOUNT, COUNTY_OF_DEALER, HARVEST_DATE,
> HARVEST_TIME, TRANSACTION_DATE, NO_PENALTY_FLAG, BP_CONFIRM_NUM,
> DUPLICATION_DATE, LICENSE_STATUS, STATUS_DATE)
> SELECT
> ULF_KEY,
> SPORTSMAN_KEY, LICENSE_YEAR, LICENSE_NUM, CONFIRMATION,
> ULF_FORM_TYPE, BOOK_FORM_TYPE, BOOK_NUM, DEALER_BATCH_KEY,
> HARVEST_FLAG, AMOUNT, COUNTY_OF_DEALER, HARVEST_DATE,
> HARVEST_TIME, TRANSACTION_DATE, NO_PENALTY_FLAG, BP_CONFIRM_NUM,
> DUPLICATION_DATE, LICENSE_STATUS, STATUS_DATE
> FROM ULF_Backup WITH (NOLOCK)
> GO
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Mike L" <Cadel@.nospam.nospam> wrote in message
> news:F0077D93-CEA3-445E-A86A-688817B67352@.microsoft.com...
>
>