Trigger Is Not Working After 12/21/2007

Jan 16, 2008

Hi Experts,
The trigger is not sucessfully calling the Stored Procedure. This was working up until last year (12/21/2007 @ 1600 hrs.)Here I am providing codes for both Trigger and Storedprocedure.Can you help me out where the problem is?

Trigger

USE [personnelreq]
GO
/****** Object: Trigger [tgrTracking] Script Date: 01/16/2008 11:55:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO



/*****************************************************************************************************************************************************************
* Trigger Name: tgrTracking
* Description: Calls the CalcTrackingHours stored procedure for replacement and net staff addition
* requisitions when the requisitions are emailed, transferred, or completed.
* History
* TKT/CO # Date Developer Description
* ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
* 01/03/2005 Zsofia Horompoli Created
* CO#1570 03/22/2005 Zsofia Horompoli Added originator email tracking for transfer events
*****************************************************************************************************************************************************************/
CREATE TRIGGER [tgrTracking] ON [dbo].[Log2]
FOR INSERT
AS

DECLARE @reqId int -- store requisition ID
DECLARE @eventId int -- event value inserted
DECLARE @parentId int -- store parent requisition ID
DECLARE @eventTime datetime -- store date/time of event
DECLARE @email varchar(50) -- store email address
DECLARE @logId int -- store ID assigned to the record in the Log2 table
DECLARE @location varchar(30) -- Location
DECLARE @title varchar(30) -- Job title
DECLARE @multiReq int -- > 0 = Multi NSA
DECLARE @origMail varchar(50) -- Originator's email address
DECLARE @subject varchar(200) -- Email subject line
DECLARE @from varchar(50) -- From email
DECLARE @body varchar(8000) -- Email body
DECLARE @reqType int -- Requisition type (0-replacement, 1-net staff addition, 6-Bonus)
DECLARE @sReqType varchar(30) -- Requisition type description
DECLARE @appEmail varchar(500) -- Approvers' email addresses

SELECT @from = 'compensationandbenefits@west.com'

DECLARE @inserted_rows AS CURSOR -- hold the inserted rows
SET @inserted_rows = CURSOR FOR
SELECT id,reqid, timestamp, event, stuff(notes,1,patindex('%:%',notes),'') AS email FROM inserted -- get the Log2 id,requisition id, event, timestamp, and email address from the updated/inserted rows

OPEN @inserted_rows
FETCH NEXT FROM @inserted_rows INTO @logId, @reqId, @eventTime, @eventId, @email
WHILE (@@FETCH_STATUS = 0)
BEGIN
--Is this an email/transfer/complete/deny event?
IF (@eventId = 4 OR @eventId = 6 OR @eventId = 7 OR @eventId = 0 OR @eventId = 10)
BEGIN -- Yes, is this a deny event?
IF (@eventId = 0)
BEGIN --Yes, is this an active replacement/net staff addition/parent multi NSA requisition?

SELECT @parentId = id
FROM reqs2 r
WHERE requisitiontype in (0,1) and id = @reqId and
(SELECT ParentReqId from ParentChild WHERE ChildReqId = @reqId) IS NULL and
NOT (markedfortransfer = 0 AND logging <> 1) AND markedfortransfer <> 2
END
ELSE IF (@eventId = 6)
BEGIN
SELECT @parentId = id
FROM reqs2 r
WHERE requisitiontype IN (0,1,6) AND id = @reqId AND
(SELECT ParentReqId FROM ParentChild WHERE ChildReqId = @reqId) IS NULL and
NOT (markedfortransfer = 0 AND logging <> 1) and
(SELECT id FROM Log2 WHERE reqId = @reqId AND event = 0) IS NULL
END
ELSE IF (@eventId = 7 OR @eventId = 10)
BEGIN
SELECT @parentId = id
FROM reqs2 r
WHERE requisitiontype IN (0,1) AND id = @reqId AND
(SELECT ParentReqId FROM ParentChild WHERE ChildReqId = @reqId) IS NULL and
markedfortransfer <> 2 and
(SELECT id FROM Log2 WHERE reqId = @reqId AND event = 0) IS NULL
END
ELSE
BEGIN -- No, is this an active replacement/net staff addition/parent multi NSA requisition?
SELECT @parentId = id
FROM reqs2 r
WHERE requisitiontype IN (0,1) AND id = @reqId AND
(SELECT ParentReqId FROM ParentChild WHERE ChildReqId = @reqId) IS NULL and
NOT (markedfortransfer = 0 AND logging <> 1) AND markedfortransfer <> 2 and
(SELECT id FROM Log2 WHERE reqId = @reqId AND event = 0) IS NULL
END

--Is this a requisition to be tracked?
IF @parentId is not NULL
BEGIN --Yes, is this a transfer event?
IF @eventId = 6
BEGIN --Yes, retrieve information for email
SELECT @location = RTRIM(L.DESCR), @title = RTRIM(PT.DESCR), @multiReq = (SELECT COUNT(*) FROM ParentChild WHERE ParentReqID = R.id), @reqType = R.requisitiontype
FROM reqs2 R
LEFT OUTER JOIN TDSdev.dbo.PS_LOCATION_TBL L ON R.sitename = L.LOCATION
LEFT OUTER JOIN TDSdev.dbo.PS_JOBCODE_TBL PT ON R.jobtitle = PT.JOBCODE
WHERE R.id = @reqId

-- Get email addresses to use
EXEC sel_EmailUsers @reqId = @reqId, @bApprover = 1, @currentUser = NULL, @origEmail = @origMail OUTPUT, @appEmail = @appEmail OUTPUT

--Set requisition type description
IF @reqType = 0
BEGIN --Replacement requisition
SELECT @sReqType = 'Replacement'
END
ELSE IF @reqType = 1 AND @multiReq > 0
BEGIN --Multi-NSA
SELECT @sReqType = 'Multiple Net Staff Addition'
END
ELSE IF @reqType = 1
BEGIN --NSA
SELECT @sReqType = 'Net Staff Addition'
END
ELSE IF @reqType = 6
BEGIN --Bonus
SELECT @sReqType = 'Bonus'
END

--Build subject line
--Do we have a location?
IF @location IS NULL
BEGIN --No, default to Nothing
SELECT @location = ''
END

--Do we have a job title?
IF @title IS NULL
BEGIN --No, default to Nothing
SELECT @title = ''
END

SELECT @subject = @location + ': ' + @sReqType + ': ' + CAST(@reqId AS varchar(15)) + ': ' + @title

--Set email body
IF @reqType = 6 -- If Bonus Req
BEGIN
SELECT @body = '<p>Bonus requisition number ' + CAST(@reqId AS varchar(15)) + ' has been processed by Compensation.</p>'
SELECT @body = @body + '<p>If you have any questions, please contact us at <a href=''mailto: compensationandbenefits@west.com'' target=''_blank''>compensationandbenefits@west.com</a> .</p>'
END
ELSE IF @reqType IN (0,1) -- If Net Staff or Replacement Req
BEGIN
SELECT @body = '<p>Requisition number ' + CAST(@reqId AS varchar(15)) + ' has been finalized and sent to Human Resources on ' + convert(char(10),@eventTime, 101) + ' at ' + substring(convert(char(19),@eventTime, 100), 12, 8) + ' for recruiting.</p>'
SELECT @body = @body + '<p>If you have any questions, please contact us at <a href=''mailto: compensationandbenefits@west.com'' target=''_blank''>compensationandbenefits@west.com</a> .</p>'
END

--Send email
EXEC usp_SMTPMail @SenderAddress = @from, @RecipientAddress = @origMail, @Subject = @subject, @Body = @body, @Cc = @appEmail
END

-- Call the CalcTrackingHours stored procedure
IF @reqType IN (0,1)
BEGIN
EXEC CalcTrackingHours @event = @eventId, @reqId = @reqId, @emailTo = @email, @endDateTime = @eventTime, @logId = @logId
END
END
END
FETCH NEXT FROM @inserted_rows INTO @logId, @reqId, @eventTime, @eventId, @email
END

--clean up
CLOSE @inserted_rows
DEALLOCATE @inserted_rows


Stored Procedure is

USE [personnelreq]
GO
/****** Object: StoredProcedure [dbo].[CalcTrackingHours] Script Date: 01/16/2008 12:01:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
/****** Object: Stored Procedure dbo.CalcTrackingHours Script Date: 4/12/2005 9:20:19 AM ******/


/*****************************************************************************************************************************************************************
* Stored Procedure Name: CalcTrackingHours
* Description: Determines the appropriate tracking level, the number of business hours the requisition
* spent at the current tracking level and inserts this information into the timeLog table
* History
* Date Developer Description
* ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
* 01/03/2005 syszxh Created
*****************************************************************************************************************************************************************/
CREATE procedure [dbo].[CalcTrackingHours]
@event int, -- 4=Emailed,6=Transferred,7=Completed
@reqId int, -- Requisition Id
@emailTo varchar(50), -- Email address requisition was forwarded to
@endDateTime datetime, -- Date/time of event
@logId int -- ID assigned to new record in the Log2 table
as
set nocount on

declare @currentUserType as int -- Current user type
declare @startDateTime as datetime -- Date/time requisition arrived at current step
declare @newUserType as int -- New user type
declare @currentTrackLevel as varchar(5) -- Tracking level to be inserted into the timeLog table
declare @newTrackLevel as varchar(5) -- New tracking level
declare @trackEmail as varchar(50) -- Email address at current tracking level
declare @count as int -- counter variable
declare @errorValue as int -- Error flag (1 = start date/time missing)
declare @trackHours as decimal(10,2)

select @errorValue = 0
select @startDateTime = NULL

-- Retrieve last email's user type, date/time stamp, and email address
select top 1 @currentUserType = userType, @startDateTime = timestamp, @trackEmail = n.userid
from personnelreq.dbo.Log2 l left outer join personnelreq.dbo.names2 n on stuff(notes,1,patindex('%:%',notes),'') = n.userid
where (l.event = 4 or l.event = 10) and timestamp <= @endDateTime and reqid = @reqId and l.id < @logId order by l.id desc

-- Last email found?
if @startDateTime is NULL
begin
-- No, this is the very first email, retrieve new user type
select @newUserType = userType
from personnelreq.dbo.names2 n
where userid = @emailTo

-- Is new user @C&B?
if @newUserType = 2
begin -- Yes, set new tracking level
select @newTrackLevel = 'cb1'
end
else
begin -- No, set new tracking level
select @newTrackLevel = 'dept'
end
end
else
begin
-- Is this event = email?
if @event = 4
begin
-- Retrieve new user type
select @newUserType = userType
from personnelreq.dbo.names2 n
where userid = @emailTo

-- Is the current user the same as the new user?
if @currentUserType = @newUserType and @emailTo = @trackEmail
begin
-- Yes, get latest level
select top 1 @newTrackLevel = trackLevel
from timeLog
where reqID = @reqId and trackEmail = @trackEmail and dateIn = @startDateTime
order by dateIn desc

select @currentTrackLevel = @newTrackLevel
end
-- Is the current user type @C&B?
else if @currentUserType <> 2 or @currentUserType is NULL
begin
-- No, retrieve current tracking level
exec GetTrackLevel @userType = @currentUserType, @reqID = @reqID, @email = @trackEmail, @trackLevel = @currentTrackLevel output
-- Is the new user type @C&B?
if @newUserType = 2
begin -- Yes, retrieve new tracking level
exec GetCBLevel @trackLevel = @currentTrackLevel, @reqID = @reqID, @currentTrackLevel = @newTrackLevel output
end
else -- No, retrieve new tracking level
begin
exec GetTrackLevel @userType = @newUserType, @reqID = @reqID, @email = @emailTo, @trackLevel = @newTrackLevel output
end
end
else
begin
-- Yes, retrieve new tracking level
exec GetTrackLevel @userType = @newUserType, @reqID = @reqID, @email = @emailTo, @trackLevel = @newTrackLevel output
-- Retrieve current tracking level
exec GetCBLevel @trackLevel = @newTrackLevel, @reqID = @reqID, @currentTrackLevel = @currentTrackLevel output
end
end
-- Is this event = transfer/completed?
else if @event = 6 or @event = 7 or @event = 10
begin
-- No, any existing entries for exec3?
if (select count(*) from timeLog where trackLevel = 'exec3' and reqID = @reqID) > 0
begin
-- Yes, set current tracking level to cb5
select @currentTrackLevel = 'cb5'
end
-- Any existing entries for exec2?
else if (select count(*) from timeLog where trackLevel = 'exec2' and reqID = @reqID) > 0
begin
-- Yes, set current tracking level to cb4
select @currentTrackLevel = 'cb4'
end
-- Any existing entries for exec1?
else if (select count(*) from timeLog where trackLevel = 'exec1' and reqID = @reqID) > 0
begin
-- Yes, set current tracking level to cb3
select @currentTrackLevel = 'cb3'
end
-- Any existing entries for acct?
else if (select count(*) from timeLog where trackLevel = 'acct' and reqID = @reqID) > 0
begin
-- Yes, set current tracking level to cb2
select @currentTrackLevel = 'cb2'
end
else
begin
-- Set current tracking level to cb1
select @currentTrackLevel = 'cb1'
end
end

-- Calculate hours worked
if @event <> 10
begin
exec CalcBusinessHours @startDateT = @startDateTime, @endDateT = @endDateTime, @totalHours = @trackHours output

-- Update information in the timeLog table
update timeLog
set dateOut = @endDateTime, trackHours = @trackHours
where reqID = @reqID and trackHours = 0 and dateOut = '1990-12-31'
end
end

-- Is this event = email?
if @event = 4 and @newTrackLevel is not NULL
begin
-- Yes, insert new row for new track step
insert into timeLog (reqID, trackLevel, dateIn, trackEmail, trackHours, dateOut)
values (@reqID, @newTrackLevel, @endDateTime, @emailTo, 0, '1990-12-31')
end
-- Is this event = reopen?
else if @event = 10 and @trackEmail is not NULL
begin -- Yes, insert new row for new track step
insert into timeLog (reqID, trackLevel, dateIn, trackEmail, trackHours, dateOut)
values (@reqID, @currentTrackLevel, @endDateTime, @trackEmail, 0, '1990-12-31')
end


endProcedure:
Return @errorValue

View 2 Replies


ADVERTISEMENT

Performance Problems Working In BIDS With Excel 2007 Connection

Mar 12, 2008



Hello everybody,

I have some problems of performance in my machine when i try to work whit a ETL that contains one connection of a excel 2007 file. These file has 3 page and it's 19MB. when i try to edit some data flow, my bids work very slow around 3 or 5 minutes, openning the Data flow source.

My machine configuration is:
Processor: Core2Duo of 1.66Ghz
Ram: 2GB
D.D:80GB
I'm working over a DB engine Sql Server 2005 Developer edition.

So, the excel file has thes load:
Page1: 5 columns X 62000 rows
Page2: 14 columns X 62000 rows
Page3: 12 columns X 123000 rows.



If i work with a file with the same structure but few rows, they work better. How can i correct these situation?.

Thank you!!


JULIAN CASTIBLANCO P.
Bogotà , Colombia.

View 3 Replies View Related

How To Get Receipients Email Address In Access 2007 Table From Outlook 2007 Using VBA?

Mar 3, 2008

Hi
everybody.
I am new to VBA,.,, I Have created a folder under inbox in Outlook 2007 which stored the receipients information
like name, E-mail, which is not present in the address book of outlook.
I have imported this folder in access 2007. My problem is ... it does not display the email address.
Rest of the informations is displayed... Is there any way i could do this.??
is there any way i could get the email addresses to appear(import/get) in to the access table???
Is there any module i mite have to create??

Plz help...
thanks a million

View 1 Replies View Related

Trigger Not Working...

Aug 6, 2000

Hi,
Am having problems creating trigger. I want to have a trigger update a
table (tblClaimItemID) with the value of ClaimItemID + 1 and insert this
value as the primary key of tblClaimItemsDaily(to field ClaimItemID) on
insert of a record into tblClaimItemsDaily (so that when data is appended to
tblClaimsItems we have a unique primary key). tblClaimItemsDaily is the day
sheet, so need to have another table generate the primary key since there
will be multiple tblClaimItemsDaily tables open at any given time, but the
key must be unique. I've been looking at deja and in my books, but they
don't seem to indicate how to do this.

Here's the code. Any ideas?
------------begin code-------

CREATE TRIGGER trgClaimItemID
ON [tblClaimItemsDaily]
FOR INSERT,UPDATE
AS

DECLARE @Item int

IF UPDATE(ClaimID)
BEGIN
IF @@ROWCOUNT = 1
Update dbo.tblClaimItemID
SET dbo.tblClaimItemID.ClaimItemID = dbo.tblClaimItemID.ClaimItemID +
1
FROM dbo.tblClaimItemID
Select @Item = dbo.tblClaimItemID.ClaimItemID FROM dbo.tblClaimItemID

END
UPdate tblClaimItemID set ClaimItemID = @Item

------------end code-------

View 1 Replies View Related

Sql Trigger Not Working

Nov 3, 2004

i am having an issue with my trigger code
i believe that i should be able to use the multiple if else statements
and i just threw in an update in each begin end statement
but when i check the syntax it fails

any reason why that is
CREATE TRIGGER [tr_update_conveyor_interface] ON [dbo].[t_conveyor_interface]
FOR UPDATE
AS


declare
@id integer,
@barcode varchar(25),
@epc_tag varchar(24),
@reject_flag char(1),
@hold_flag char(1),
@pe_3_flag char(1),
@old_id integer,
@old_barcode varchar(25),
@old_epc_tag varchar(24),
@old_reject_flag char(1),
@old_hold_flag char(1),
@old_pe_3_flag char(1)

select @barcode = c.barcode,
@id = c.id,
@epc_tag = c.epc_tag,
@reject_flag = c.reject_flag,
@hold_flag = c.hold_flag,
@pe_3_flag = c.pe_3_flag
from t_conveyor_interface c
inner join deleted d
on c.barcode = d.barcode

select @old_barcode = c.barcode,
@old_id = c.id,
@old_epc_tag = c.epc_tag,
@old_reject_flag = c.reject_flag,
@old_hold_flag = c.hold_flag,
@old_pe_3_flag = c.pe_3_flag
from t_conveyor_interface c
inner join inserted i
on c.barcode = i.barcode

if(@old_epc_tag <> @epc_tag)
begin
update t_load_audit
set id = 1
end

else
if(@old_reject_flag <> @reject_flag)
begin
update t_load_audit
set id = 1
end

else
if(@old_hold_flag <> @hold_flag)
begin
update t_load_audit
set id = 1
end

else
if(@old_pe_3_flag <> @pe_3_flag
begin
update t_load_audit
set id = 1
end

View 1 Replies View Related

Why Trigger Is Not Working

Dec 16, 2004

Hi,
here is my code for a trigger,

--------------------------------
CREATE TRIGGER trIns_Test
ON tblTest
FOR INSERT
AS

DECLARE @Message VARCHAR(100),
@Num INT

SELECT @Num = user_id from INSERTED
SELECT @Message = 'NET SEND NICK '+' Here is the new # - ' + CONVERT(VARCHAR(100), @Num)
EXEC master.dbo.xp_cmdshell @Message
--------------------------------

But when i insert a row into tblTest table i'am supposed to receive a NET SEND message. But its not happening if i hardcode the value @Num i.e

SELECT @Num = 321

Then i receive a message, but why this INSERTED clause does't work i'm unable to trace out.

Version of SQL is 2K SP3

Thanks,

View 2 Replies View Related

Update In Trigger Not Working

Sep 24, 2007

Hi,
I am trying to concatenate the columns (PrevEmp01, PrevEmp02, PrevEmp03, PrevEmp04, PrevEmp05) into column (ft) using trigger:
CREATE TRIGGER [tg_prevemp_ft_update] ON [tStaffDir_PrevEmp] FOR INSERT, UPDATEASUPDATE tStaffDir_PrevEmp SET ft = PrevEmp01 + ' ' + PrevEmp02 + ' ' + PrevEmp03 + ' ' + PrevEmp04 + ' ' + PrevEmp05
I would expect the (ft) column will be populated accordingly regardless if any of the columns are (Null).But the Trigger will only work when all the 5 columns are populated. If one of the column is (Null), the (ft) column will be (Null) too.Please advise. Many Thanks.

View 2 Replies View Related

For Deletion..trigger Is Not Working

May 4, 2004

Hi,
I have this trigger, it is working fine when i add new data but it doesn't work when I delete data from the table?

Any idea?

Any help will be highly appreciated.


CREATE TRIGGER [PROP_AMT] ON [dbo].[cqe_item]
FOR INSERT, UPDATE, DELETE
AS
DECLARE
@var_DB_contract INTEGER,
@var_CQE INTEGER,
@var_PC INTEGER,
@var_item VARCHAR(7),
@var_AMT_PAID INTEGER,
@var_AMT_RET INTEGER,
@var_ITEM_NEW VARCHAR(1),
@var_quant DECIMAL,
@var_fiyr INTEGER,
@var_amt_result INTEGER,
@var_amt_ret_result INTEGER,
@var_amt_old INTEGER,
@var_amt_ret_old INTEGER,
@var_quant_result INTEGER,
@var_quant_new INTEGER,
@var_quant_old INTEGER,
@Item_new VARCHAR(7),
@var_chk varchar(1)

--If Exists (Select 1 From Inserted) And Exists (Select 1 From Deleted)
set @var_db_contract =(SELECT a.db_contract FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no )
IF @var_db_contract IS NOT NULL
BEGIN
SET @var_db_contract=(SELECT a.db_contract FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no )
SET @var_cqe=(SELECT a.cqe_numb FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no )
SET @var_pc=(SELECT a.pc_code FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no )
SET @var_item=(SELECT a.item_no FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no )
SET @var_fiyr=(SELECT a.fy_item FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no )
set @var_chk ="Y"
END
ELSE
BEGIN
SET @var_db_contract=(SELECT a.db_contract FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no )
SET @var_cqe=(SELECT a.cqe_numb FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no )
SET @var_pc=(SELECT a.pc_code FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no )
SET @var_item=(SELECT a.item_no FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no )
SET @var_fiyr=(SELECT b.fy_item FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no )
set @var_chk="N"
END
SET @var_amt_paid=(SELECT a.amt_paid_item FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no )
SET @var_amt_old=(SELECT b.amt_paid_item FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no )
SET @var_amt_result =ISNULL(@var_amt_paid,0) - ISNULL(@var_amt_old,0)

SET @var_amt_ret = (SELECT a.amt_ret_item from inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no)
SET @var_amt_ret_old=(SELECT b.amt_ret_item from inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no)
SET @var_amt_ret_result = isnull(@var_amt_ret,0) - isnull(@var_amt_ret_old,0)

SET @var_quant_new = (SELECT a.quantity from inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no)
SET @var_quant_old =(SELECT b.quantity from inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no)
SET @var_quant_result = isnull(@var_quant_new,0) - isnull(@var_quant_old,0)
SELECT @item_new = new_item
FROM VALID_ITEM
WHERE DB_CONTRACT = @var_db_contract
AND PC_CODE = @var_PC
AND ITEM_NO = @var_ITEM

UPDATE ae_contract
set amt_paid_contr = isnull(amt_paid_contr,0) +@var_amt_result,
amt_ret_contr = isnull(amt_ret_contr,0) + @var_amt_ret_result
where db_contract = @var_db_contract

IF @item_new = 'N'
BEGIN
update vendor
set used_amt = isnull(used_amt,0) + @var_amt_result + @var_amt_ret_result
where db_vendor = (select gen_contr from ae_contract
where ae_contract.db_contract=@var_db_contract);
END
UPDATE enc_det
set amt_paid_fy = isnull(amt_paid_fy,0) + @var_amt_result,
amt_ret_fy = isnull(amt_ret_fy,0) + @var_amt_ret_result
where db_contract = @var_db_contract
and pc_code = @var_pc
and fy = @var_fiyr

UPDATE valid_item
set tamt_ret_item = isnull(tamt_ret_item,0) + @var_amt_ret_result,
tamt_paid_item = isnull(tamt_paid_item,0) + @var_amt_result,
qtd = isnull(qtd,0) + @var_quant_result
where db_contract = @var_db_contract
and pc_code = @var_pc
and item_no = @var_item

View 1 Replies View Related

[ask] Trigger In Sqlserver Not Working, HELPP!!

May 9, 2007

i have this trigger in my database : ALTER TRIGGER dbo.AddVoucher    ON  dbo.User_AddVoucher    AFTER INSERTAS     SET NOCOUNT ON;DECLARE @UserId int,     @Add_id int,     @voucher_id char,     @Kredit money,     @date smalldatetime,     @last_balance money,     @voucher_status charSELECT @UserId = UserId,     @voucher_id = Voucher_ID,     @Add_id = Add_id,     @date = Deposit_Date    FROM InsertedSELECT @Kredit= Voucher_Value,     @voucher_status = Voucher_Status     FROM Voucher     WHERE Voucher_ID = @voucher_idINSERT INTO User_Balance(AddVoucher_ID, UserId, Update_Type, Update_Date)    VALUES     (@Add_id,@UserId, 'Kredit',@date)select @last_balance = Balance    from User_Balance     WHERE UserId = @UserId and Balance = (select TOP 1 Balance User_Balance where UserId = @UserId order by Update_Id DESC) if (@voucher_status = 'active')    -- SET NOCOUNT ON added to prevent extra result sets from    -- interfering with SELECT statements.    -- Insert statements for trigger hereBEGIN    update User_Balance    set Balance = @last_balance + @Kredit    where AddVoucher_ID = @Add_id    update Voucher    set Sold_Date = @date    where Voucher_ID = @voucher_idENDELSE BEGIN    raiserror ('Voucher is not valid',0,1)    rollback transaction ENDgo  the problem is the update function is not working and the if statement always put to 'FALSE' do you think anything wrong with the code 

View 2 Replies View Related

UPDATE Trigger Not Working Properly

Oct 4, 2004

We've altered an UPDATE trigger - now it doesn't work properly. When updating a record (via a web application) by clearing the value for a particlar column, we get error:

UPDATE failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'.

Code = 800a0c93
Source = ADODB.Recordset
Description = Operation is not allowed in this context.

What the trigger does is prevent a zero-length string from being inserted into the database, which violates a check constraint, and instead inserts NULL. The web app - whose code we cannot modify - apparently does this. This was working fine, until we altered the trigger to add an additional column for update. Our SQL developer person has left, so us less knowledgable (about SQL) folks are trying to pick up the slack.

trigger code:

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO




ALTER TRIGGER trigViaNetCardholderUpdateEmptyStrings
ON viewViaNetCardholder
INSTEAD OF UPDATE AS
UPDATE tblIDCross
SET
tblIDCross.chUSCId = CASE WHEN ins.chUSCId = '' THEN NULL ELSE ins.chUSCId END,
tblIDCross.chNineDigit = CASE WHEN ins.chNineDigit = '' THEN NULL ELSE ins.chNineDigit END,
tblIDCross.chPID = CASE WHEN ins.chPID = '' THEN NULL ELSE ins.chPID END,
tblIDCross.chEmployeeId = CASE WHEN ins.chEmployeeId = '' THEN NULL ELSE ins.chEmployeeId END,
tblIDCross.chAIMSNumber = CASE WHEN ins.chAIMSNumber = '' THEN NULL ELSE ins.chAIMSNumber END,
tblIDCross.intCustomerType = ins.intCustomerType
FROM INSERTED ins
WHERE tblIDCross.intUSCardId = ins.intUSCardId




GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Please help...

View 1 Replies View Related

Using SQL SERVER IMPORT WIZARD, Why Trigger Not Working?

Mar 21, 2008



Hi SQL Proz,

I am trying to important some data in the an existing table with trigger. Unfortunately, the data were successfully inserted into the said table but the trigger did not work out. Why? How to do the tricks?

Many Thanks!

View 2 Replies View Related

Data Mining :: Calculation Not Working Trigger

Apr 20, 2015

In My Table , I have Three Column like Qty,Price and Total, I create a Trigger for this . 

Table:
CREATE TABLE [dbo].[tbl_SO](
[SoNo] [varchar](50) NULL,
[Qty] [int] NULL,
[price] [numeric](18, 2) NULL,
[total] [numeric](18, 2) NULL
) ON [PRIMARY]

[code]...

Input :
1) UPDATE TBL_SO SET QTY='10',Price='100' Where SONo='10'
2)UPDATE TBL_SO SET QTY='10',Price='100'  Total=Qty*Price Where SONo='10'

Output:
What I get? I tried both 1 and 2, It(Query) does not work on first time,I executed (Query) second time It works.Why?What I need?   How to work on First Time?

View 6 Replies View Related

Trigger Problem, Comparing Deleted/inserted Not Working :(

Sep 14, 2007

Hello all,

I have I trigger where I want to insert all _changed_ rows from the INSERTED table into
a table called tempProducts.

If I put this query inside my trigger, I selects exactly the rows I want: rows changed




Code SnippetSELECT * FROM INSERTED
EXCEPT SELECT * FROM DELETED




I the current trigger I have




Code SnippetINSERT INTO dbo.TempProducts (LBTyp, CountryOfOrigin)
SELECT LBTyp, CountryOfOrigin
FROM INSERTED




but this inserts ALL updated rows, not only the changed ones.

So I thought hey, I´ll just combine the two querys and the problem will be solved, like so:



Code Snippet

INSERT INTO dbo.TempProducts (LBTyp, CountryOfOrigin)
SELECT LBTyp, CountryOfOrigin
FROM (SELECT * FROM INSERTED
EXCEPT SELECT * FROM DELETED) as Temp




But for some reason, this won´t work! Why is this? What am I doing wrong?

View 10 Replies View Related

Some Things Not Working In 2005 And Working In 2000

Mar 3, 2006

hi

I had a view in which I did something like this
isnull(fld,val) as 'alias'

when I assign a value to this in the client (vb 6.0) it works ok in sql2000 but fails in 2005.
When I change the query to fld as 'alias' then it works ok in sql 2005 .
why ?? I still have sql 2000 (8.0) compatability.

Also some queries which are pretty badly written run on sql 2000 but dont run at all in sql 2005 ???

any clues or answers ?? it is some configuration issue ?

Thanks in advance.

View 5 Replies View Related

SQL 2007 Error

Feb 20, 2007

Hi everyone,
I am new to SQL Server. I have SQL 2007 installed on win 2003 server, my problem is when I try to start the Sql 2007 server I get the error message "The service control action cannot be completed as no service has been selected"
Thanks in advance
Arunchu from London

View 4 Replies View Related

RS In Sharepoint 2007

Jan 22, 2007

Sharepoint Server Portal 2007, MSSQL 2005, Reporting Services

When I try to integrate Reporting Services in Adminitsrator, for url of RS I write this: http://servername/ReportServer/ and click OK.

But when I try to go to 'Set Server defaults' or 'Managed Shared Shedules' I get this:

Server was unable to process request. ---> Client found response content type of '', but expected 'text/xml'. The request failed with an empty responseWhat should I Do ?

View 1 Replies View Related

BCM With Accounting 2007

Feb 1, 2007

I have bcm with outlook 2007 and accounting express. when i create an opportunity in bcm, everything is fine. when i click the convert to quote button, it dumps all of the item information (quantity, name, description, price, tax....) into the description field in accounting. Then I have to copy each piece out of the description field into it's appropriate field in accounting 2007 express. Where is the mapping for these fields, so that I can map each one correctly? Is there something else i should be doing?

View 1 Replies View Related

Vba Access 2007

Mar 12, 2008

I want to user 'acCmdExportFixedFormat' in ms-access to save reports as PDF and XPS.
when i use the code runcommand acCmdExportFixedFormat i have to set manually the filename.
I want to give the filename in my vba code. How do i user this

View 4 Replies View Related

ExecuteNonQuery - Add Working/Update Not Working

Jan 7, 2004

I am writing a pgm that attaches to a SQL Server database. I have an Add stored procedure and an Update stored procedure. The two are almost identical, except for a couple parameters. However, the Add function works and the Update does not. Can anyone see why? I can't seem to find what the problem is...

This was my test:


Dim cmd As New SqlCommand("pContact_Update", cn)
'Dim cmd As New SqlCommand("pContact_Add", cn)

Try
cmd.CommandType = CommandType.StoredProcedure

cmd.Parameters.Add("@UserId", SqlDbType.VarChar).Value = UserId
cmd.Parameters.Add("@FirstName", SqlDbType.VarChar).Value = TextBox1.Text
[...etc more parameters...]
cmd.Parameters.Add("@Id", SqlDbType.VarChar).Value = ContactId

cn.Open()
cmd.ExecuteNonQuery()

Label1.Text = "done"
cn.Close()

Catch ex As Exception
Label1.Text = ex.Message
End Try


When I use the Add procedure, a record is added correctly and I receive the "done" message. When I use the Update procedure, the record is not updated, but I still receive the "done" message.

I have looked at the stored procedures and the syntax is correct according to SQL Server.

Please I would appreciate any advice...

View 2 Replies View Related

Moneycount 9 To Quickbooks 2007

Mar 18, 2007

Can Moneycounts 9.0 (data) be imported to Quickbooks 2007? If so How?

View 2 Replies View Related

Backup Sharepoint 2007

Jun 12, 2008

I know this is not SQL server related. Do anyone know good Third-party tools to backup sharepoint 2007? I have looked at www.avepoint.com and ideras tools but didn't find price listings. I am looking which has reasonable price and efficient.
Only xpurt ownly

View 4 Replies View Related

Access07 To SQL Server 2007 Through The...

Dec 2, 2007

hi everybody
I've been working in the database field for 9 months
I started from very simple tables, to some sql stuff
And then I started studying SQL Server 2005 extensively until I took MCTS SQL Server 2005
During that time, I was building a database for a new company from scratch, and while I build it for them I keep on reading books and learning, and using this knowledge to upgrade the database
Recently, I could successfully migrate the back-end to SQL Server 2005, and after days of struggling I could connect the front end to it via ODBC in a LAN
My next step is connecting the database through the internet, so I just changed the server name in the DSN file to the address of the server, and opened the ports, but it didn't work....
Can you tell me what's the problem?
Is there a book that can help me do this thing?
I can buy any book and I have the will to learn, provided that I'll apply for MCITP Database Developer next months

Thank you..

View 20 Replies View Related

SCE 2007 And Server 2000

Mar 24, 2008

I have installed System Center Essentials on our network. Everything seems to be working but we are now getting Perflib errors (event id 1008) on our windows 2000 servers. This error indicates there is a problem with our ASP.net v2.xx.xx.xx. We have been unable to resolve this and are looking for more things to try.



View 5 Replies View Related

Openrowset Excel 2007

Jul 31, 2007

Haven't been able to find much information on using openrowset with excel 2007 xlsx files. I've tried the following with no success so far.

SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0 Xml;Database=\serverfolderfile.xlsx;HDR=No;IMEX=1',
'SELECT f1 FROM [Raw Keywords$] WHERE f1 IS NOT NULL')

If anyone knows the correct way to do this please tell me.

View 3 Replies View Related

RS Web Parts On SharePoint 2007

Feb 20, 2007

Hi,

I'm having troubles connecting the webparts on SharePoint 2007. I installed the RSWebParts package from SQL Server 2005 Reporting Services on my MOSS 2007 box. The installation went smoothly and I can add both Report Explorer and Report Viewer on a page. But when I try to connect RE to RV I get the following error:


An unexpected error has occurred.

Web Parts Maintenance Page: If you have permission, you can use this page to temporarily close Web Parts or remove personal settings. For more information, contact your site administrator.

So I have to go to the maintenance page and remove the web parts.

Have anyone managed to bypass this and make them work together?

Thanks,

View 1 Replies View Related

SQL For Equipment Up Time During 2007

Dec 20, 2007



I have a table with equipment ID, transaction ID, date of transaction. How can I write a sql so that the result is a list of
equipment with % up time during 2007.
Transaction ID: 100 is down, 200 is up. Example is below the table

Equipment ID Transaction ID Date
1 200 1/12/2007
2 100 2/12/2007
1 100 2/25/2007
3 100 3/10/2007
2 200 3/14/2007

From the table I know that equiment 2 was down for 32 days and up time % during 2007 is (365-32)/365*100

How can I write a sql in order to achieve that

Daniel
sql learner

View 8 Replies View Related

Mirroring With MOSS 2007

Oct 31, 2007





Is anyone attempting or has anyone successfully deployed MOSS2007 with a SQL 2005 Database Mirror on the backend? We are doing it in our lab with the automatic failover (principal, mirror & witness) and the SQL portion works great. The only issue is we have is that we have to manually switch MOSS over (via Sharepoint 3.0 Central Admin or stsadm) to the new content database on the other server. Thank you


We have reviewed the White Paper that Microsoft offered with no help to our issue.

View 1 Replies View Related

Excel 2007 And SqlServer Sp2

Feb 21, 2007

hellow all,

i installed SqlServer 2005 sp2 on my server.

now, how do i import excel 2007 files in SSIS? i don't have office 2007 installed on my computer, but i've got some .xslx that i would like to try ro import. i tried to create an OLEDB connection, but i don't know which provider to choose.

anyway, can i do it without the sp2 or office 2007 installed on my server?

thanks for tour reply!

View 5 Replies View Related

Connecting Access 2007 Via .NET

Oct 26, 2006

Is there any way that I can connect an Access 200? front end through .NET?

View 1 Replies View Related

Importing Excel 2007

May 15, 2008



When importing an .xlsx file the number of columns stops at 255. Does anybody know how to get all columns imported?

Tried saving the file as text and that allows me to import all the columns but it would be much easier to do this directly from Excel.

Thanks

View 4 Replies View Related

Export To Excel 2007

Jul 17, 2007

Hello Everyone,



Is there any way in RS2005 to export to Excel 2007 format ?



The reason I'm asking is that we have a report that can potentially have more than 256 columns but for now, there is no way of directly importing to excel 2003 (.xls).



Is microsoft going to come out with an improvement to RS2005 or is there already a way to export to Excel 2007 to bypass the 256 column limit?





Regards,

Joseph

View 1 Replies View Related

Excel 2007 And SSIS

Feb 28, 2008



Hi Folks,
I have got a quick question, Is it possible to use office 2007 in SSIS, If so what would be the driver?
or Could we use JET 4.0 itself?

Any thoughts and help in this regard would be appreciated.

Zulfi.

View 3 Replies View Related

Sharepoint 2007 &&amp; DB Mirroring

May 8, 2007

I am trying to work through a test on database mirroring I am following the whitepaper that Micrososft published on how to configure mirroring and sahrepoint, but when i failover the config database and admin content database it doesnt appear to work. I execute the following commands after both are failed over

----

stsadm.exe -o renameserver -oldservername <OldPrincipalServer> -newservername <NewPrincipalServer>

iisreset

----

It doesnt work it get the following error.

Unable to connect to database. Check database connection information and make sure the database server is running.

When i bring the admin content databse back to the original principle the admin site works great.

What am I missing?

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved