Specific record could not be retrieved! WHY? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2000 Forums

SQL Server Administration (2000)

Specific record could not be retrieved! WHY?

Author

Topic

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2005-03-31 : 21:12:29

What causes a particular record could not be retrieved?

I run a particular stored proc. Specifically, it is an updation routine. I update one record but the moment i tried to retrieve that particular record using the select statement on a SQL Analyzer, SQL could not return it.

what causes it?

Want Philippines to become 1st World COuntry? Go for World War 3...

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-03-31 : 21:18:01

Give us an example. Your English is giving us all a little difficulty right now. It probably has something to do with transactions.

Show us with code what you are trying to communicate with us though, and we'll see if we can help you out.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-03-31 : 21:18:58

Based on all the information you supplied, my guess is there's an error in your code.

CODO ERGO SUM

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2005-03-31 : 23:19:07

heres my code....

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

ALTER PROCEDURE dbo.sp_ProcessDTR_5DaysCovered_V_1_1
@dtrFrom as smalldatetime,
@dtrTo as smalldatetime,
@ProcessID as int,
@emp_id as varchar(15)
AS

BEGIN TRANSACTION
SET NOCOUNT ON

--DECLARATIONS----------------------------------------------------------------------------------------
DECLARE @_7_530_600TimeIn AS SMALLDATETIME
DECLARE @_7_530_600TimeOutMonToThu AS SMALLDATETIME
DECLARE @_7_530_600TimeOutFri AS SMALLDATETIME
DECLARE @_7_400_TimeOut AS SMALLDATETIME

SET @_7_530_600TimeIn = '7:00:00 AM'
SET @_7_400_TimeOut = '4:00:00 PM'
SET @_7_530_600TimeOutMonToThu = '5:30:00 PM'
SET @_7_530_600TimeOutFri = '6:00:00 PM'

DECLARE @_9HrsToMinRegular AS INTEGER
DECLARE @_8HrsToMinRegular AS INTEGER

SET @_9HrsToMinRegular = 540 -- 9 Hrs regular
SET @_8HrsToMinRegular = 480 -- 8 Hrs regular

DECLARE @_7_530_600_1030HrsToMin AS INTEGER
DECLARE @_7_530_600_11HrsToMin AS INTEGER
DECLARE @_7_530_600HrsInFri AS INTEGER
DECLARE @_7_530_600HrsOnMonToFri AS INTEGER

SET @_7_530_600_1030HrsToMin = 630 -- 10.5 Hrs covered from 700am to 530pm
SET @_7_530_600_11HrsToMin = 660 -- 11 Hrs covered from 700am to 600pm
SET @_7_530_600HrsInFri = 120 -- 2 Hrs Excess on Fridays
SET @_7_530_600HrsOnMonToFri = 90 -- 1.5 Hrs Excess on days Mo
------------------------------------------------------------------------------------------------------

--#tempValidateTimeInOut------------------------------------------------------------------------------
SELECT dbo.dtr.emp_id
, dbo.employee.ag_control
, dbo.employee.emp_name
, dbo.employee.emp_pmode
, dbo.department.emp_dept
, dbo.department.dept_desc
, dbo.dtr.dtr_date
, dbo.dtr.dtr_type
, dbo.dtr.login_am
, CASE ISNULL(dbo.dtr.logout_am2, '')
WHEN '' THEN dbo.dtr.logout_am
ELSE dbo.dtr.logout_am2
END AS logout_am
, dtr_rt
, dtr_ot
INTO #tempValidateTimeInOut
FROM dbo.employee
LEFT OUTER JOIN dbo.dtr ON dbo.employee.emp_id = dbo.dtr.emp_id
LEFT OUTER JOIN dbo.department ON dbo.department.emp_dept = dbo.employee.emp_dept
WHERE (convert(smalldatetime, dbo.dtr.dtr_date, 101) >= convert(varchar, @dtrFrom, 101)
and convert(smalldatetime, dbo.dtr.dtr_date, 101) <= convert(varchar, @dtrTo, 101))
and dbo.employee.emp_fired = 0 and dbo.employee.ag_control = '5'
-- and dbo.dtr.dtr_type = 'SPHOL'
-- and dbo.employee.emp_name like '%laigo%'
/* and dbo.dtr.emp_id in
(
'2504-00191'
, '0207-00468'
, '1605-00203'
, '1605-00289'
)
*/
--('031605-00289', '030207-00468', '_1360U5PLA')

GROUP BY dbo.dtr.emp_id, dbo.employee.emp_name
, dbo.dtr.dtr_date, dbo.department.emp_dept
, dbo.department.dept_desc, dbo.dtr.login_am
, dbo.dtr.logout_am, dbo.dtr.logout_am2
, dbo.dtr.logout_am2, dbo.dtr.dtr_type
, dbo.dtr.emp_name, dbo.employee.ag_control
, dtr_rt, dtr_ot, dbo.employee.emp_pmode
ORDER BY dbo.employee.emp_name
------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------
SELECT emp_id
, ag_control
, emp_name
, emp_dept
, emp_pmode
, dept_desc
, dtr_date
, dtr_type
, login_am = CASE
WHEN ag_control = '5' THEN
CASE
-- WHEN datename(weekday, dtr_date) = 'Saturday' and dtr_type = 'ABSENT' THEN null
WHEN dtr_type = 'LEGHOL' and datename(weekday, dtr_date) <> 'Saturday' THEN CONVERT(VARCHAR, dtr_date, 101) + ' ' + @_7_530_600TimeIn
WHEN dtr_type = 'SPHOL' and datename(weekday, dtr_date) <> 'Saturday' THEN CONVERT(VARCHAR, dtr_date, 101) + ' ' + @_7_530_600TimeIn
ELSE login_am
END
ELSE login_am
END

, logout_am = CASE
WHEN ag_control = '5' THEN
CASE
-- WHEN datename(weekday, dtr_date) = 'Saturday' and dtr_type = 'ABSENT' THEN null
WHEN dtr_type = 'LEGHOL' and datename(weekday, dtr_date) <> 'Saturday' THEN
CASE
WHEN datename(weekday, dtr_date) = 'Friday' THEN CONVERT(VARCHAR, dtr_date, 101) + ' ' + @_7_530_600TimeOutFri
ELSE CONVERT(VARCHAR, dtr_date, 101) + ' ' + @_7_530_600TimeOutMonToThu
END
WHEN dtr_type = 'SPHOL' and datename(weekday, dtr_date) <> 'Saturday' THEN
CASE
WHEN datename(weekday, dtr_date) = 'Friday' THEN CONVERT(VARCHAR, dtr_date, 101) + ' ' + @_7_530_600TimeOutFri
ELSE CONVERT(VARCHAR, dtr_date, 101) + ' ' + @_7_530_600TimeOutMonToThu
END
ELSE logout_am
END
END
, dtr_rt
, dtr_ot
INTO #tempDTRforComputation
FROM #tempValidateTimeInOut
------------------------------------------------------------------------------------------------------

--DTR computation----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT emp_id
, emp_name
, emp_dept
, emp_pmode
, dept_desc
, dtr_date
, dtr_type
, login_am
, logout_am
, dtr_rt
, dtr_ot
, CreditedOnSaturday = CASE WHEN datename(weekday, dtr_date) = 'Saturday' then
CASE WHEN AddToSaturday >= @_8HrsToMinRegular THEN
convert(varchar(3), (@_8HrsToMinRegular) / 60) + ':' + right('00' + convert(varchar(2), (@_8HrsToMinRegular) % 60),2)
ELSE convert(varchar(3), (AddToSaturday) / 60) + ':' + right('00' + convert(varchar(2), (AddToSaturday) % 60),2)
END
ELSE convert(varchar(3), RegMinutes/60) + ':' + right('00' + convert(varchar(2), RegMinutes % 60),2)
END
into #tempForRequery
FROM
(
SELECT reg.emp_id
, reg.emp_name
, reg.emp_dept
, reg.emp_pmode
, reg.dept_desc
, reg.dtr_date
, reg.dtr_type
, reg.login_am
, reg.logout_am
, reg.dtr_rt
, reg.dtr_ot
, CASE WHEN dtr_rt = 8 THEN
CASE
WHEN dtr_type = 'DAYOFF' THEN 0
WHEN DATENAME(WEEKDAY, dtr_date) = 'Friday' THEN
CASE
WHEN DATEDIFF(MINUTE, cast(CONVERT(VARCHAR, dtr_date, 101) + ' ' + @_7_530_600TimeIn as datetime), logout_am) >= @_7_530_600_11HrsToMin THEN @_7_530_600HrsInFri --add the max 2 hrs alloted for Fridays
WHEN DATEDIFF(MINUTE, cast(CONVERT(VARCHAR, dtr_date, 101) + ' ' + @_7_530_600TimeIn as datetime), logout_am) >= @_9HrsToMinRegular
and DATEDIFF(MINUTE, cast(CONVERT(VARCHAR, dtr_date, 101) + ' ' + @_7_530_600TimeIn as datetime), logout_am) <= @_7_530_600_11HrsToMin
then DATEDIFF(MINUTE, cast(CONVERT(VARCHAR, dtr_date, 101) + ' ' + @_7_530_600TimeIn as datetime), logout_am) - @_9HrsToMinRegular --add total no of hrs less 9 regular working hours
END
WHEN DATENAME(WEEKDAY, dtr_date) = 'Saturday' THEN 0
ELSE --Monday to Thursday
CASE
WHEN DATEDIFF(MINUTE, cast(CONVERT(VARCHAR, dtr_date, 101) + ' ' + @_7_530_600TimeIn as datetime), logout_am) >= @_7_530_600_1030HrsToMin THEN @_7_530_600HrsOnMonToFri --add the max 2 hrs alloted for Fridays
WHEN DATEDIFF(MINUTE, cast(CONVERT(VARCHAR, dtr_date, 101) + ' ' + @_7_530_600TimeIn as datetime), logout_am) >= @_9HrsToMinRegular
and DATEDIFF(MINUTE, cast(CONVERT(VARCHAR, dtr_date, 101) + ' ' + @_7_530_600TimeIn as datetime), logout_am) <= @_7_530_600_1030HrsToMin
then DATEDIFF(MINUTE, cast(CONVERT(VARCHAR, dtr_date, 101) + ' ' + @_7_530_600TimeIn as datetime), logout_am) - @_9HrsToMinRegular --add total no of hrs less 9 regular working hours
END
END
ELSE 0
END as RegMinutes
, ot.AddToSaturday
FROM #tempDTRforComputation AS reg
JOIN (
SELECT emp_id
, datepart(week,dtr_date) as [week]
, AddToSaturday = sum(CASE
WHEN dtr_rt = 8 THEN
CASE
WHEN dtr_type = 'DAYOFF' THEN 0
WHEN DATENAME(WEEKDAY, dtr_date) = 'Friday' THEN
CASE
WHEN DATEDIFF(MINUTE, cast(CONVERT(VARCHAR, dtr_date, 101) + ' ' + @_7_530_600TimeIn as datetime), logout_am) >= @_7_530_600_11HrsToMin THEN @_7_530_600HrsInFri --add the max 2 hrs alloted for Fridays
WHEN DATEDIFF(MINUTE, cast(CONVERT(VARCHAR, dtr_date, 101) + ' ' + @_7_530_600TimeIn as datetime), logout_am) >= @_9HrsToMinRegular
and DATEDIFF(MINUTE, cast(CONVERT(VARCHAR, dtr_date, 101) + ' ' + @_7_530_600TimeIn as datetime), logout_am) <= @_7_530_600_11HrsToMin
then DATEDIFF(MINUTE, cast(CONVERT(VARCHAR, dtr_date, 101) + ' ' + @_7_530_600TimeIn as datetime), logout_am) - @_9HrsToMinRegular --add total no of hrs less 9 regular working hours
END
WHEN DATENAME(WEEKDAY, dtr_date) = 'Saturday' THEN 0
ELSE --Monday to Thursday
CASE
WHEN DATEDIFF(MINUTE, cast(CONVERT(VARCHAR, dtr_date, 101) + ' ' + @_7_530_600TimeIn as datetime), logout_am) >= @_7_530_600_1030HrsToMin THEN @_7_530_600HrsOnMonToFri --add the max 2 hrs alloted for Fridays
WHEN DATEDIFF(MINUTE, cast(CONVERT(VARCHAR, dtr_date, 101) + ' ' + @_7_530_600TimeIn as datetime), logout_am) >= @_9HrsToMinRegular
and DATEDIFF(MINUTE, cast(CONVERT(VARCHAR, dtr_date, 101) + ' ' + @_7_530_600TimeIn as datetime), logout_am) <= @_7_530_600_1030HrsToMin
then DATEDIFF(MINUTE, cast(CONVERT(VARCHAR, dtr_date, 101) + ' ' + @_7_530_600TimeIn as datetime), logout_am) - @_9HrsToMinRegular --add total no of hrs less 9 regular working hours
END
END
ELSE 0
END)
FROM #tempDTRforComputation
GROUP BY emp_id
, datepart(week,dtr_date)
) AS ot

ON reg.emp_id = ot.emp_id
AND datepart(week, reg.dtr_date) = ot.[week]
) m
ORDER BY emp_id, dtr_date

DROP TABLE #tempDTRforComputation
DROP TABLE #tempValidateTimeInOut
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

--Validation of dtr_type is done here-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

SELECT emp_id
, emp_name
, emp_dept
, emp_pmode
, dept_desc
, dtr_date
, dtr_type
/* = CASE
WHEN DATENAME(WEEKDAY, dtr_date) = 'Saturday' THEN
CASE
WHEN dtr_type = 'ABSENT' THEN 'REGULAR'
ELSE dtr_type
END
END
*/
, login_am = CASE
WHEN DATENAME(WEEKDAY, dtr_date) = 'Saturday' THEN
CASE
WHEN dtr_type = 'ABSENT' THEN CONVERT(VARCHAR, dtr_date, 101) + ' ' + @_7_530_600TimeIn
ELSE login_am
END
ELSE login_am
END
, logout_am = CASE
WHEN DATENAME(WEEKDAY, dtr_date) = 'Saturday' THEN
CASE
WHEN dtr_type = 'ABSENT' THEN CONVERT(VARCHAR, dtr_date, 101) + ' ' + (@_7_530_600TimeIn +
CASE
WHEN (cast(cast(CreditedOnSaturday as datetime) as float) * 24) >= 4 THEN CAST(CreditedOnSaturday as datetime) + CAST('1:00' as datetime)
ELSE CAST(cast(cast(CreditedOnSaturday as datetime) as float) * 24 AS DATETIME)
END)
ELSE logout_am
END
ELSE logout_am
END
, dtr_rt
, dtr_ot
, CreditedOnSaturday
, AsOT = CASE
WHEN DATENAME(WEEKDAY, dtr_date) = 'Saturday' THEN dtr_rt
else 0
END
/* , AsNew_DTR_Type = CASE
WHEN DATENAME(WEEKDAY, dtr_date) = 'Saturday' THEN
CASE
WHEN dtr_type = 'ABSENT' THEN 'REGULAR'
END
END
*/
into #tempForUploading
FROM #tempForRequery

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

IF @ProcessID = 0 --View in detailed computation
BEGIN
SELECT *
FROM #tempForUploading
WHERE datename(weekday, dtr_date) = 'Saturday'
-- and dtr_type in ('ABSENT', 'SPHOL', 'SHWREG')
ORDER BY emp_name
END
ELSE IF @ProcessID = 2
BEGIN
SELECT *
FROM #tempForUploading
WHERE emp_ID = rtrim(@emp_id)
ORDER BY dept_desc
END

ELSE IF @ProcessID = 22222 --Update dtr by single record
BEGIN
SELECT *
FROM #tempForUploading
WHERE datename(weekday, dtr_date) = 'Saturday'
and emp_ID = rtrim(@emp_id)
-- and dtr_type in ('ABSENT', 'SPHOL', 'SHWREG')
ORDER BY dept_desc

UPDATE dtr set dtr.login_am = case
when dtr.dtr_rt = 0 then up.login_am
else dtr.login_am
end
,dtr.logout_am = case
when dtr.dtr_rt = 0 then up.logout_am
else dtr.logout_am
end
,dtr.dtr_rt = (cast(cast(up.CreditedOnSaturday as datetime) as float) * 24)
-- ,dtr.emp_name = '_'
,dtr.edited = 1
,dtr.dtr_type =
case
when dtr.dtr_type = 'ABSENT' THEN 'REGULAR'
ELSE dtr.dtr_type
END
,dtr.dtr_ot = case
when len(isnull(dtr.login_am, '')) = 0 then 0
when dbo.employee.ag_control = '5' and len(isnull(dtr.login_am, '')) <> 0 then up.AsOT
end
--cast(cast(up.AsOT as datetime) as float) * 24
/* CASE
WHEN DATENAME(WEEKDAY, dtr.dtr_date) = 'Saturday' THEN
case
when dtr.dtr_type = 'SPHOL' and dtr.dtr_rt <> 0 then dtr.dtr_rt
else 0
end
else 0
END
*/
FROM dbo.employee
LEFT OUTER JOIN dbo.dtr as dtr ON employee.emp_id = dtr.emp_id
LEFT JOIN #tempForUploading as up ON up.emp_id = dbo.employee.emp_id
WHERE (convert(smalldatetime, dtr.dtr_date, 101) >= convert(varchar, @dtrFrom, 101)
and convert(smalldatetime, dtr.dtr_date, 101) <= convert(varchar, @dtrTo, 101))
and datename(weekday, dtr.dtr_date) = 'Saturday'
-- and datename(weekday, up.dtr_date) = 'Saturday'
and dbo.employee.emp_fired = 0 and dbo.employee.ag_control = '5'
-- and dtr.dtr_type in ('ABSENT', 'SPHOL', 'SHWREG', 'REGULAR')
and dtr.emp_id = rtrim(@emp_id)

END

/* ELSE IF @ProcessID = 11111 --Update dtr now
BEGIN
SELECT *
FROM #tempForUploading
WHERE datename(weekday, dtr_date) = 'Saturday' and
dtr_type in ('ABSENT', 'SPHOL', 'SHWREG')
ORDER BY dept_desc

UPDATE dtr
set dtr.login_am = up.login_am
,dtr.logout_am = up.logout_am
,dtr.dtr_rt = cast(cast(up.CreditedOnSaturday as datetime) as float) * 24
,dtr.emp_name = '_'
,dtr.edited = 1
,dtr.dtr_type = up.dtr_type
,dtr.dtr_ot = cast(cast(up.AsOT as datetime) as float) * 24
FROM dbo.employee
LEFT OUTER JOIN dbo.dtr as dtr ON employee.emp_id = dtr.emp_id
LEFT JOIN #tempForUploading as up ON up.emp_id = dbo.employee.emp_id
WHERE (convert(smalldatetime, dtr.dtr_date, 101) >= convert(varchar, @dtrFrom, 101)
and convert(smalldatetime, dtr.dtr_date, 101) <= convert(varchar, @dtrTo, 101))
and datename(weekday, dtr.dtr_date) = 'Saturday'
and dbo.employee.emp_fired = 0 and dbo.employee.ag_control = '5'
and dtr_type in ('ABSENT', 'SPHOL', 'SHWREG')
END
*/

DROP TABLE #tempForUploading

drop table #tempForRequery

IF @@ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
RAISERROR ('Error..........', 16, 1)
--RETURN -100
END

COMMIT TRANSACTION

/*
select * from dtr where dtr_date = '2005-03-26' and
datename(weekday, dtr_date) = 'Saturday' and emp_id = '1605-00289'
*/

/*
IF @ProcessID = 0 --View in detailed computation
BEGIN
SELECT * FROM #tempForRequery
END
ELSE IF @ProcessID = 1 --View total hours credited on saturdays
BEGIN
SELECT * FROM #tempForRequery
END

DROP TABLE #tempForRequery
*/

--exec sp_ProcessDTR_5DaysCovered_V_1_1 '3/21/2005', '3/26/2005', 0
--select * from dtr
--where (dtr_date >= '3/21/2005' and dtr_date <= '3/26/2005') and emp_id = '2603-00464'
--exec sp_ProcessDTR_5DaysCovered_V_1_1 '3/14/2005', '3/19/2005', 0

/*
select * from dtr
where --(dtr_date >= '3/21/2005' and dtr_date <= '3/26/2005')
--dtr_date = '3/26/2005' --and login_am is not null
--and emp_id in ('031605-00289', '030207-00468', '_1360U5PLA')
*/
/*
select * from dtr
where (dtr_date >= '3/14/2005' and dtr_date <= '3/19/2005')
--and dtr_type = 'SPHOL' AND login_am is not null
and emp_id = '_12K0JMSSK'
*/

/*
exec sp_ProcessDTR_5DaysCovered_V_1_1 '3/14/2005', '3/19/2005', 2, '_12K0JMSSK'
exec sp_ProcessDTR_5DaysCovered_V_1_1 '3/14/2005', '3/19/2005', 22222, '_12K0JMSSK'
exec sp_ProcessDTR_5DaysCovered_V_1_1 '3/14/2005', '3/19/2005', 2, '_12K0JMSSK'
*/

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

I update one record then the moment i tried to retrieve that specific record, my SQL hangs up. On the other hand, if i try an another record which i didnt updated, i could retrieve it.

Why this happen?

Want Philippines to become 1st World COuntry? Go for World War 3...

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-04-04 : 02:59:38

i believe no one's answering due to the length of the sp, unreadable column names.. it's too much

my wildest guess is that the criteria you are passing does not match the one you updated?

try a simple test, after the record has been updated, try to retrieve it using a simple select statement from QA and check if the update was successful,

if it's ok then check each part of your code and isolate the problem

HTH

--------------------
keeping it simple...

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2005-04-04 : 03:25:01

tnx JEN! i already got it...

Want Philippines to become 1st World COuntry? Go for World War 3...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources