Error on sp execute! - pls help

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

General SQL Server Forums

Database Design and Application Architecture

Error on sp execute! - pls help

Author

Topic

DaveC11
Starting Member

43 Posts

Posted - 2008-04-11 : 03:49:30

Message Preview
Hi,

I'm not sure if this post is in the correct section but here goes.

I have a sp that when executed inserts data into two tables(shown below). The sp works fine when the correct information is inserted into the tables but when you try and insert data that breaks the constraints in the table it obviously errors, but it seems to inert a new row in to the tmptimesheet table(error message shown below). When you open the tmptimesheet table there isn’t the row of a data there but if you run this (SELECT IDENT_CURRENT('tmptimmesheets') after the error it will come back with a id one higher than what’s in the table. Then the next time you run the sp a record will be inserted into the tmptimesheet table and not in to the tmptimsheethours table!? I’m at a total lost at what to do can someone please help!?

Thanks in advance Dave



CREATE TABLE [dbo].[tmptimesheets](
	[TimesheetID] [int] IDENTITY(1,1) NOT NULL,
	[Placementid] [int] NOT NULL,
	[Periodstarting] [datetime] NOT NULL,
	[createdon] [datetime] NOT NULL,
	[createduserid] [int] NOT NULL,
	[Issued] [nchar](1) COLLATE Latin1_General_CI_AS NOT NULL,
	[ReadyForBilling] [nchar](1) COLLATE Latin1_General_CI_AS NOT NULL,
	[Reject] [nchar](1) COLLATE Latin1_General_CI_AS NULL,
	[Comments] [text] COLLATE Latin1_General_CI_AS NULL,
	[Rate] [nchar](1) COLLATE Latin1_General_CI_AS NOT NULL,
 CONSTRAINT [PK_tmptimesheets] PRIMARY KEY CLUSTERED 
(
	[TimesheetID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY],
 CONSTRAINT [IX_tmptimesheets_1] UNIQUE NONCLUSTERED 
(
	[Placementid] ASC,
	[Periodstarting] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]



CREATE TABLE [dbo].[tmptimesheethours](
[TmpTimesheethourid] [int] IDENTITY(1,1) NOT NULL,
[Timesheetid] [int] NOT NULL,
[applicantid] [int] NOT NULL,
[Workedon] [datetime] NOT NULL,
[Hoursworked] [numeric](10, 2) NOT NULL,
[performancevalueid] [int] NOT NULL,
[Reject] [ntext] COLLATE Latin1_General_CI_AS NULL,
[Breaks] [numeric](10, 2) NOT NULL,
CONSTRAINT [PK_tmptimesheethours] PRIMARY KEY CLUSTERED 
(
[TmpTimesheethourid] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
USE [Pronet_TS]
GO
ALTER TABLE [dbo].[tmptimesheethours] WITH NOCHECK ADD CONSTRAINT [FK_TimesheetHours_Timesheets] FOREIGN KEY([Timesheetid])
REFERENCES [dbo].[tmptimesheets] ([TimesheetID])
GO
ALTER TABLE [dbo].[tmptimesheethours] CHECK CONSTRAINT [FK_TimesheetHours_Timesheets]



Error Message
Msg 2627, Level 14, State 1, Procedure sp_tmptimesheet_insert_day, Line 40
Violation of UNIQUE KEY constraint 'IX_tmptimesheets_1'. Cannot insert duplicate key in object 'dbo.tmptimesheets'.
The statement has been terminated.
Msg 547, Level 16, State 0, Procedure sp_tmptimesheet_insert_day, Line 65
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_TimesheetHours_Timesheets". The conflict occurred in database "Pronet_TS", table "dbo.tmptimesheets", column 'TimesheetID'.
The statement has been terminated.
Msg 547, Level 16, State 0, Procedure sp_tmptimesheet_insert_day, Line 86
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_TimesheetHours_Timesheets". The conflict occurred in database "Pronet_TS", table "dbo.tmptimesheets", column 'TimesheetID'.
The statement has been terminated.
Msg 547, Level 16, State 0, Procedure sp_tmptimesheet_insert_day, Line 108
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_TimesheetHours_Timesheets". The conflict occurred in database "Pronet_TS", table "dbo.tmptimesheets", column 'TimesheetID'.
The statement has been terminated.
Msg 547, Level 16, State 0, Procedure sp_tmptimesheet_insert_day, Line 130
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_TimesheetHours_Timesheets". The conflict occurred in database "Pronet_TS", table "dbo.tmptimesheets", column 'TimesheetID'.
The statement has been terminated.
Msg 547, Level 16, State 0, Procedure sp_tmptimesheet_insert_day, Line 153
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_TimesheetHours_Timesheets". The conflict occurred in database "Pronet_TS", table "dbo.tmptimesheets", column 'TimesheetID'.
The statement has been terminated.
Msg 547, Level 16, State 0, Procedure sp_tmptimesheet_insert_day, Line 178
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_TimesheetHours_Timesheets". The conflict occurred in database "Pronet_TS", table "dbo.tmptimesheets", column 'TimesheetID'.
The statement has been terminated.
Msg 547, Level 16, State 0, Procedure sp_tmptimesheet_insert_day, Line 200
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_TimesheetHours_Timesheets". The conflict occurred in database "Pronet_TS", table "dbo.tmptimesheets", column 'TimesheetID'.
The statement has been terminated.
Msg 3902, Level 16, State 1, Procedure sp_tmptimesheet_insert_day, Line 223
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.

(1 row(s) affected)

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-04-11 : 04:38:24

Unless you post your SP contents, its really hard to tell what's going wrong.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

DaveC11
Starting Member

43 Posts

Posted - 2008-04-11 : 04:43:19

quote:
Originally posted by harsh_athalye

Unless you post your SP contents, its really hard to tell what's going wrong.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

















ALTER procedure [dbo].[sp_tmptimesheet_insert_day]

(@weektotal int,
@workedon datetime,
@hoursworked numeric(10, 2),
@UserNM int,
@hoursworkedtue numeric(10, 2),
@hoursworkedwed numeric(10, 2),
@hoursworkedthurs numeric(10, 2),
@hoursworkedfri numeric(10, 2),
@hoursworkedsat numeric(10, 2),
@hoursworkedsun numeric(10, 2),
@createduserid int,
@issued Nvarchar(1),
@readyforbilling Nvarchar(1),
@performancevalueid int,
@rate Nvarchar)

as

begin

insert INTO tmptimesheets 
     ( PlacementID
      , Periodstarting
	   , createdon
		, createduserid
		,issued
		,readyforbilling
		,rate)
SELECT placementid 
      , @workedon
	  , getdate()
	  ,@createduserid
	  ,@issued
	 ,@readyforbilling
	 ,@rate
  FROM sql03.pronet_ts.dbo.placements
 WHERE applicantid = @userNM 
  AND enddate > dateadd(week,-2,getdate()) and @weektotal > '0' and @weektotal is not null





-----Monday-----

Insert into tmptimesheethours 
			(timesheetid, 
			applicantid,
			workedon,
			hoursworked,
			performancevalueid,
			breaks)
select  IDENT_CURRENT('tmptimesheets'), 
		@userNM,
		@workedon, 
		@hoursworked,
		@performancevalueid,
		'0'
  FROM sql03.pronet_ts.dbo.placements p, sql03.pronet_ts.dbo.tmptimesheets t 
 WHERE p.placementid = t.placementid and p.applicantid = @userNM
   AND t.createdon < getdate() and t.createdon > dateadd(day,-1,getdate()) 
   and @hoursworked > '0'  and @workedon = t.periodstarting

 

-----Tuesday------
Insert into tmptimesheethours 
			(timesheetid, 
			applicantid,
			workedon,
			hoursworked,
			performancevalueid,
			breaks)
select  IDENT_CURRENT('tmptimesheets'), 
		@userNM,
		dateadd(day,1,@workedon), 
		@hoursworkedtue,
		@performancevalueid,
		'0'
  FROM sql03.pronet_ts.dbo.placements p, sql03.pronet_ts.dbo.tmptimesheets t 
 WHERE p.placementid = t.placementid and p.applicantid = @userNM
   AND t.createdon < getdate() and t.createdon > dateadd(day,-1,getdate()) 
	and @hoursworkedtue > '0'
	and dateadd(day,1,@workedon) = dateadd(day,1,t.periodstarting)

 
----Wednesday------

Insert into tmptimesheethours 
			(timesheetid, 
			applicantid,
			workedon,
			hoursworked,
			performancevalueid,
			breaks)
select  IDENT_CURRENT('tmptimesheets'), 
		@userNM,
		dateadd(day,2,@workedon), 
		@hoursworkedwed,
		@performancevalueid,
		'0'
  FROM sql03.pronet_ts.dbo.placements p, sql03.pronet_ts.dbo.tmptimesheets t
 WHERE p.placementid = t.placementid and p.applicantid = @userNM
   AND t.createdon < getdate() and t.createdon > dateadd(day,-1,getdate()) 
	and @hoursworkedwed > '0'
and dateadd(day,2,@workedon) = dateadd(day,2,t.periodstarting)


 
--------Thurs-----
Insert into tmptimesheethours 
			(timesheetid, 
			applicantid,
			workedon,
			hoursworked,
			performancevalueid,
			breaks)
select  IDENT_CURRENT('tmptimesheets'), 
		@userNM,
		dateadd(day,3,@workedon), 
		@hoursworkedthurs,
		@performancevalueid,
		'0'
  FROM sql03.pronet_ts.dbo.placements p, sql03.pronet_ts.dbo.tmptimesheets t 
 WHERE p.placementid = t.placementid and p.applicantid = @userNM
   AND t.createdon < getdate() and t.createdon > dateadd(day,-1,getdate()) 
	and @hoursworkedthurs > '0'
and dateadd(day,3,@workedon) = dateadd(day,3,t.periodstarting)

 
-------Friday------


Insert into tmptimesheethours 
			(timesheetid, 
			applicantid,
			workedon,
			hoursworked,
			performancevalueid,
			breaks)
select  IDENT_CURRENT('tmptimesheets'), 
		@userNM,
		dateadd(day,4,@workedon), 
		@hoursworkedfri,
		@performancevalueid,
		'0'
		
  FROM sql03.pronet_ts.dbo.placements p, sql03.pronet_ts.dbo.tmptimesheets t 
 WHERE p.placementid = t.placementid and p.applicantid = @userNM
   AND t.createdon < getdate() and t.createdon > dateadd(day,-1,getdate()) 
	and @hoursworkedfri > '0'
and dateadd(day,4,@workedon) = dateadd(day,4,t.periodstarting)

 

---------sat--------


Insert into tmptimesheethours 
			(timesheetid, 
			applicantid,
			workedon,
			hoursworked,
			performancevalueid,
			breaks)
select  IDENT_CURRENT('tmptimesheets'), 
		@userNM,
		dateadd(day,5,@workedon), 
		@hoursworkedsat,
		@performancevalueid,
		'0'
		
  FROM sql03.pronet_ts.dbo.placements p, sql03.pronet_ts.dbo.tmptimesheets t 
 WHERE p.placementid = t.placementid and p.applicantid = @userNM
   AND t.createdon < getdate() and t.createdon > dateadd(day,-1,getdate()) 
 and @hoursworkedsat >'0'and dateadd(day,5,@workedon) = dateadd(day,5,t.periodstarting)

 

--------sunday-------
Insert into tmptimesheethours 
			(timesheetid, 
			applicantid,
			workedon,
			hoursworked,
			performancevalueid,
			breaks)
select  IDENT_CURRENT('tmptimesheets'), 
		@userNM,
		dateadd(day,6,@workedon), 
		@hoursworkedsun,
		@performancevalueid,
		'0'
		
  FROM sql03.pronet_ts.dbo.placements p, sql03.pronet_ts.dbo.tmptimesheets t 
 WHERE p.placementid = t.placementid and p.applicantid = @userNM
   AND t.createdon < getdate() and t.createdon > dateadd(day,-1,getdate()) 
 and @hoursworkedsun > '0'
and dateadd(day,6,@workedon) = dateadd(day,6,t.periodstarting)

end

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-04-11 : 04:50:44

If you want to insert in both tables or none, why don't you put all the inserts inside a transaction?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

DaveC11
Starting Member

43 Posts

Posted - 2008-04-11 : 04:54:52

You'll have the bear with me as I'm quite new to this but do you mean somthing like this -


begin tran

(sql stored procedure)


if @@error in ('2627','2601')
rollback tran
else
commit tran
end

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-04-11 : 05:03:37

Yes. You can do it for specific errors as in your example code or for any error:

If @@error <> 0
Rollback Transaction
else
Commit Transaction

But you will have to do this check after each and every insert, not just last one.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

DaveC11
Starting Member

43 Posts

Posted - 2008-04-11 : 05:09:25

If I didn't want to check for errrors and just wanted to run the transaction so that it inserted into both tables or none would I just need to put it like this???

begin tran

sp goes here

commit tran
end

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-04-11 : 05:18:01

No. It won't work in all or none fashion.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

DaveC11
Starting Member

43 Posts

Posted - 2008-04-11 : 05:23:00

How would I make it work in an all or none fashion?

Thanks for the help!

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-04-11 : 05:27:50

I already shown you. You need to check for error after each statement and in case of error, rollback the whole transaction.
Only when there are no errors till end, you can commit the transaction.

insert into tablel1...

if @@error <> 0
begin
rollback tran
return
end

insert into tablel2...

if @@error <> 0
begin
rollback tran
return
end
..
..
..
-- Last Insert
insert into tableln...

if @@error <> 0
begin
rollback tran
else
commit tran
end

EDIT: I assume you are using SQL Server 2000 here. In case of 2005, you can use new Try..Catch construct.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

DaveC11
Starting Member

43 Posts

Posted - 2008-04-11 : 05:48:20

I've tried that but it tells be I've a syntax error near the key work else after the last insert statment.

Any ideas what I've done wrong???

DaveC11
Starting Member

43 Posts

Posted - 2008-04-11 : 05:49:23

just seen the end of your last post. I'm using sql2005!!!

Where would I use the try and catch???

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-04-11 : 05:54:20

See this:
http://msdn2.microsoft.com/en-us/library/ms175976.aspx

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

DaveC11
Starting Member

43 Posts

Posted - 2008-04-11 : 06:30:24

Thanks for the point in the right direction. I've had a read through and I thought I'd got it sussed but when I run my sp it still only inserts just in to the tmptimesheet table. please find the sp below. Any ideas on where I've gone wrong?


ALTER procedure [dbo].[sp_tmptimesheet_insert_day]

(@weektotal int,
@workedon datetime,
@hoursworked numeric(10, 2),
@UserNM int,
@hoursworkedtue numeric(10, 2),
@hoursworkedwed numeric(10, 2),
@hoursworkedthurs numeric(10, 2),
@hoursworkedfri numeric(10, 2),
@hoursworkedsat numeric(10, 2),
@hoursworkedsun numeric(10, 2),
@createduserid int,
@issued Nvarchar(1),
@readyforbilling Nvarchar(1),
@performancevalueid int,
@rate Nvarchar)

as

BEGIN TRY
   BEGIN TRANSACTION


insert INTO tmptimesheets 
     ( PlacementID
      , Periodstarting
	   , createdon
		, createduserid
		,issued
		,readyforbilling
		,rate)
SELECT placementid 
      , @workedon
	  , getdate()
	  ,@createduserid
	  ,@issued
	 ,@readyforbilling
	 ,@rate
  FROM sql03.pronet_ts.dbo.placements
 WHERE applicantid = @userNM 
  AND enddate > dateadd(week,-2,getdate()) and @weektotal > '0' and @weektotal is not null




-----Monday-----

Insert into tmptimesheethours 
			(timesheetid, 
			applicantid,
			workedon,
			hoursworked,
			performancevalueid,
			breaks)
select  IDENT_CURRENT('tmptimesheets'), 
		@userNM,
		@workedon, 
		@hoursworked,
		@performancevalueid,
		'0'
  FROM sql03.pronet_ts.dbo.placements p, sql03.pronet_ts.dbo.tmptimesheets t 
 WHERE p.placementid = t.placementid and p.applicantid = @userNM
   AND t.createdon < getdate() and t.createdon > dateadd(day,-1,getdate()) 
   and @hoursworked > '0'  and @workedon = t.periodstarting

 
-----Tuesday------
Insert into tmptimesheethours 
			(timesheetid, 
			applicantid,
			workedon,
			hoursworked,
			performancevalueid,
			breaks)
select  IDENT_CURRENT('tmptimesheets'), 
		@userNM,
		dateadd(day,1,@workedon), 
		@hoursworkedtue,
		@performancevalueid,
		'0'
  FROM sql03.pronet_ts.dbo.placements p, sql03.pronet_ts.dbo.tmptimesheets t 
 WHERE p.placementid = t.placementid and p.applicantid = @userNM
   AND t.createdon < getdate() and t.createdon > dateadd(day,-1,getdate()) 
	and @hoursworkedtue > '0'
	and dateadd(day,1,@workedon) = dateadd(day,1,t.periodstarting)


----Wednesday------

Insert into tmptimesheethours 
			(timesheetid, 
			applicantid,
			workedon,
			hoursworked,
			performancevalueid,
			breaks)
select  IDENT_CURRENT('tmptimesheets'), 
		@userNM,
		dateadd(day,2,@workedon), 
		@hoursworkedwed,
		@performancevalueid,
		'0'
  FROM sql03.pronet_ts.dbo.placements p, sql03.pronet_ts.dbo.tmptimesheets t
 WHERE p.placementid = t.placementid and p.applicantid = @userNM
   AND t.createdon < getdate() and t.createdon > dateadd(day,-1,getdate()) 
	and @hoursworkedwed > '0'
and dateadd(day,2,@workedon) = dateadd(day,2,t.periodstarting)


 
--------Thurs-----
Insert into tmptimesheethours 
			(timesheetid, 
			applicantid,
			workedon,
			hoursworked,
			performancevalueid,
			breaks)
select  IDENT_CURRENT('tmptimesheets'), 
		@userNM,
		dateadd(day,3,@workedon), 
		@hoursworkedthurs,
		@performancevalueid,
		'0'
  FROM sql03.pronet_ts.dbo.placements p, sql03.pronet_ts.dbo.tmptimesheets t 
 WHERE p.placementid = t.placementid and p.applicantid = @userNM
   AND t.createdon < getdate() and t.createdon > dateadd(day,-1,getdate()) 
	and @hoursworkedthurs > '0'
and dateadd(day,3,@workedon) = dateadd(day,3,t.periodstarting)


-------Friday------


Insert into tmptimesheethours 
			(timesheetid, 
			applicantid,
			workedon,
			hoursworked,
			performancevalueid,
			breaks)
select  IDENT_CURRENT('tmptimesheets'), 
		@userNM,
		dateadd(day,4,@workedon), 
		@hoursworkedfri,
		@performancevalueid,
		'0'
		
  FROM sql03.pronet_ts.dbo.placements p, sql03.pronet_ts.dbo.tmptimesheets t 
 WHERE p.placementid = t.placementid and p.applicantid = @userNM
   AND t.createdon < getdate() and t.createdon > dateadd(day,-1,getdate()) 
	and @hoursworkedfri > '0'
and dateadd(day,4,@workedon) = dateadd(day,4,t.periodstarting)



---------sat--------


Insert into tmptimesheethours 
			(timesheetid, 
			applicantid,
			workedon,
			hoursworked,
			performancevalueid,
			breaks)
select  IDENT_CURRENT('tmptimesheets'), 
		@userNM,
		dateadd(day,5,@workedon), 
		@hoursworkedsat,
		@performancevalueid,
		'0'
		
  FROM sql03.pronet_ts.dbo.placements p, sql03.pronet_ts.dbo.tmptimesheets t 
 WHERE p.placementid = t.placementid and p.applicantid = @userNM
   AND t.createdon < getdate() and t.createdon > dateadd(day,-1,getdate()) 
 and @hoursworkedsat >'0'and dateadd(day,5,@workedon) = dateadd(day,5,t.periodstarting)

 

--------sunday-------
Insert into tmptimesheethours 
			(timesheetid, 
			applicantid,
			workedon,
			hoursworked,
			performancevalueid,
			breaks)
select  IDENT_CURRENT('tmptimesheets'), 
		@userNM,
		dateadd(day,6,@workedon), 
		@hoursworkedsun,
		@performancevalueid,
		'0'
		
  FROM sql03.pronet_ts.dbo.placements p, sql03.pronet_ts.dbo.tmptimesheets t 
 WHERE p.placementid = t.placementid and p.applicantid = @userNM
   AND t.createdon < getdate() and t.createdon > dateadd(day,-1,getdate()) 
 and @hoursworkedsun > '0'
and dateadd(day,6,@workedon) = dateadd(day,6,t.periodstarting)


 COMMIT
END TRY
BEGIN CATCH
  -- Whoops, there was an error
  IF @@TRANCOUNT > 0
     ROLLBACK
 -- Raise an error with the details of the exception
  DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
  SELECT @ErrMsg = ERROR_MESSAGE(),
         @ErrSeverity = ERROR_SEVERITY()

  RAISERROR(@ErrMsg, @ErrSeverity, 1)
END CATCH

m_k_s@hotmail.com
Insecure what ??

38 Posts

Posted - 2008-04-27 : 15:52:17

read your error message

Violation of UNIQUE KEY constraint 'IX_tmptimesheets_1'. Cannot insert duplicate key in object 'dbo.tmptimesheets'

read your code

insert INTO tmptimesheets
( PlacementID
, Periodstarting
, createdon
, createduserid
,issued
,readyforbilling
,rate)
SELECT placementid
, @workedon
, getdate()
,@createduserid
,@issued
,@readyforbilling
,@rate
FROM sql03.pronet_ts.dbo.placements
WHERE applicantid = @userNM
AND enddate > dateadd(week,-2,getdate()) and @weektotal > '0' and @weektotal is not null

Try executing just the select portion of your statement with hardcoded test values. You will see that your query is pulling more than you think.

DateTime values are not always what you think...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources