For Deletion..trigger Is Not Working

Sql Trigger Not Working
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 Replies !

---

Why Trigger Is Not Working
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 Replies !

---

Trigger Not Working...
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 Replies !

---

One Trigger In SQL Server 6.5 Not Working...
Howdy,

Question for the group....

When creating a new database from scripts I encounter a problem.

Everything else works after the database is created except the following trigger. (The code is shown below).
This trigger exists in the database. I verified this through different scripts available from TechNet. I assume if I see the trigger object in the database, it has compiled properly and is enabled. However, it never fires when a record is inserted into the table after the installation has completed. The trigger exists, but does not work at all.

I issued the following command in isql_w (sp_recompile <table_name>). According to the docs this should recompile the trigger the next time the table receives and insert. I tried it from the application.
This did not help either.

Here is a description of the table:
ScheduleId
PatientId
ProviderId
SpecialtyId
LocationId
VisitTypeId
ScheduleDate
ScheduleTime
Duration
Comments
ConfirmAppt
TimeNum
Deleted
Locked
PatientName
VisitType

Identity Seed Increment
------------------------------ ------------ ------------
ScheduleId 1 1

The only solution I have found is to copy the trigger code, paste into isql_w and re-execute this trigger code. From that point on, the trigger works flawlessly. But this install should be automatic.

Can you think of anything I may have missed?

Thanks
PJD


Here is the code I spoke of...

CREATE TRIGGER Schedule_ITrig ON Schedule FOR INSERT AS
DECLARE @TabName varchar(40), @PrimKey int, @TimeAmount smallint, @Unit varchar(20)
DECLARE @pstring char(50),@patid int,@provid int,@schedid int,@fulldate datetime
DECLARE @ordschedid int
SELECT @patid = inserted.patientid,@provid = inserted.providerid,@schedid = inserted.scheduleid
from inserted
Select @ordschedid = -1
While @ordschedid <> null
BEGIN
Select @ordschedid = max(OrderScheduleId)
FROM OrderSchedules
WHERE OrderSchedules.PatientId = @PatId and
OrderSchedules.ProviderId = @ProvId and
OrderSchedules.TableName = `ProvLabOrders` and
OrderSchedules.ScheduleId is Null
If @ordschedid <> null
BEGIN
SELECT @tabname = OrderSchedules.TableName,@Primkey = OrderSchedules.TablePrimKeyId,
@Timeamount = OrderSchedules.ExeRelativeAmount,@unit = TimeUnits.TimeUnit
FROM OrderSchedules, TimeUnits
WHERE OrderSchedules.TimeUnitId = TimeUnits.TimeUnitId and
OrderSchedules.OrderScheduleId = @ordschedid
SELECT @fulldate =convert(datetime,convert(char(10),inserted.schedu ledate,101)+ ` `+ convert(char(11),
inserted.scheduletime,108))
FROM inserted

If @tabname = `ProvLabOrders`
BEGIN
UPDATE ProvLabOrders
SET DateExpected =
CASE
WHEN (@Unit = `second`) then
(select DateAdd(second,@TimeAmount, @fulldate))


WHEN (@Unit = `minute`) then
(select DateAdd(minute,@TimeAmount, @fulldate))

WHEN (@Unit = `hour`) then
(select DateAdd(hour,@TimeAmount, @fulldate))


WHEN (@Unit = `day`) then
(select DateAdd(day,@TimeAmount, @fulldate))


WHEN (@Unit = `week`) then
(select DateAdd(week,@TimeAmount, @fulldate))


WHEN (@Unit = `month`) then
(select DateAdd(month,@TimeAmount, @fulldate))

WHEN (@Unit = `year`) then
(select DateAdd(year,@TimeAmount, @fulldate))
END
WHERE ProvLabOrderId = @PrimKey
UPDATE OrderSchedules
SET ScheduleId = @schedid
WHERE OrderScheduleId = @ordschedid
END
ELSE
If @tabname = `CROrders`
BEGIN
UPDATE CROrders
SET DateExpected =
CASE
WHEN (@Unit = `second`) then
(select DateAdd(second,@TimeAmount, @fulldate))


WHEN (@Unit = `minute`) then
(select DateAdd(minute,@TimeAmount, @fulldate))

WHEN (@Unit = `hour`) then
(select DateAdd(hour,@TimeAmount, @fulldate))


WHEN (@Unit = `day`) then
(select DateAdd(day,@TimeAmount, @fulldate))


WHEN (@Unit = `week`) then
(select DateAdd(week,@TimeAmount, @fulldate))


WHEN (@Unit = `month`) then
(select DateAdd(month,@TimeAmount, @fulldate))

WHEN (@Unit = `year`) then
(select DateAdd(year,@TimeAmount, @fulldate))
END
WHERE CROrderId = @PrimKey
UPDATE OrderSchedules
SET ScheduleId = @schedid
WHERE OrderScheduleId = @ordschedid
END
END
END
GO

Thanks in advance for your time...
PD

View Replies !

---

Trigger Is Not Working After 12/21/2007
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 Replies !

---

[ask] Trigger In Sqlserver Not Working, HELPP!!
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 Replies !

---

UPDATE Trigger Not Working Properly
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 Replies !

---

Using SQL SERVER IMPORT WIZARD, Why Trigger Not Working?
 

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 Replies !

---

Trigger Problem, Comparing Deleted/inserted Not Working :(
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 Replies !

---

Deletion
Hi all,

I have a table in xyz database and there is no column in table like creation_date or modified_date.

The problem is I want to delete records which has been added in the table before 1st jan 2007.

The size of table is 85 GB

Immediate help would be appriciable.

Regards,
Frozen

View Replies !

---

&"Update&" In Trigger Not Working
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 Replies !

---

Restrict Deletion
What would be the best practice to prevent users who didn't create a record in sql from deleting? When a record is created I have the username who created the record in one of the fields. I was thinking maybe a query?
 
Thank you in advance.

View Replies !

---

Deletion Of Duplicate Row
Hi Everyone,I have a table in which their is record which is exactly same.I want to delete all the duplicate keeping ony 1 record in a table.ExampleTable AEmpid currentmonth PreviousmonthSupplimentarydays basic158 2001-11-25 00:00:00.000 2001-10-01 00:00:00.000 2.004701.00158 2001-11-25 00:00:00.000 2001-10-01 00:00:00.000 2.004701.00158 2001-11-25 00:00:00.000 2001-10-01 00:00:00.000 2.004701.00I want to delete 2 rows of above table.How can I achieve that.Any suggestion how can i do that.Thank you in advanceRichard

View Replies !

---

Replication Without Deletion
Hello there,

We are currently setting up out production server to the following requirements:

1. Every month, delete records that haven't been changed in the last 90 days.
2. Replicate insert statements to a backup database which will keep track of all data, and act as an archive/data warehouse.

The first step is easy, as it is just a script that checks the date of the last change on each row. However, the second step is a bit more tricky. We tried setting up replication between two test databases, but we ran into the following problem: Whenever old data has been deleted in the production database, the replication agent deletes it in the data warehouse database too.

Is it possible to override or disable this, so data is only inserted/updated, and not deleted? No applications using the database deletes records, so database integrity should not be a problem.


Thanks for your time,

Ulrik Rasmussen

View Replies !

---

Deletion Problem
It is an option to set deletion without getting logged since I have problem to delete two years historical data and would like to keep this year data on my 80MB rows. Actually I create a new table to get copy one-year data and I truncated the old table. I am wondering if there is other better way to do this task.

TIA,

Stella Liu

View Replies !

---

Deletion Query
Ok, so I have an issue, was wondering if anybody else has any suggestions.

I have a table that is pretty large, in all regards.  It is a "message" table that holds text messages that users send to each other.

1.  Has some data fields, integers, dates, some bit columns, a message subject field (varchar(250)), and a message body field (field type = text)
2.  Table contains about 70 million records
3.  Table has 6 indexes associated to it
4.  Table has 2 views associated to it.
5.  Table has 8 foreign keys associated to it.

I need to delete, oh, about 90,000 records out of this 70 million record table.  I am able to disable the foreign keys to this table for deletion, but that does not seem to mitigate the problem.  I think the issue lies with having to update the indexes as well as the views.

When I execute the select statement to retrieve the records I need to delete, it executes pretty quickly, no problems there that I can see.

The issue comes when I try to delete the records, it takes way too long, and we know it.  We let it run for an hour and it didn't really get anywhere.  This is in a server environment, some pretty decent hardware, 8gig memory, fast SCSI drives, 8 core processors, i don't know the exact specifics, but they're not bad.

Here's a DBCC SHOWCONTIG on our table

DBCC SHOWCONTIG scanning 'message' table...
Table: 'message' (1448040590); index ID: 1, database ID: 13
TABLE level scan performed.
- Pages Scanned................................: 51602
- Extents Scanned..............................: 6486
- Extent Switches..............................: 6948
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 92.83% [6451:6949]
- Logical Scan Fragmentation ..................: 0.54%
- Extent Scan Fragmentation ...................: 0.93%
- Avg. Bytes Free per Page.....................: 93.5
- Avg. Page Density (full).....................: 98.85%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

This is from our dev environment which is but a portion of our production db- but I presume our production environment will have similar percentages (not necessarily the pages scanned)

Any suggestions on how to delete records efficiently?

View Replies !

---

User Deletion Log SQL
Im using SQL enterprise manager v8, a few days ago I got a report that a user account was deleted.  I was wondering what logs would point this out.  I've been through the event review and i am not seeing any usefull info.

View Replies !

---

0 KB File Deletion
 

Hello,
I want to delete a 0KB file on one of the disks in my server. Do any of you have any idea how to do it??
Thanks,
Narayan

View Replies !

---

How To Prevent Db Deletion
Hi

I want to try and protect myself from my own stupidity. I have a number of sql databases, but one is LIVE. It is easy to drop tables but I want to set something (e.g. a password) which will help prevent me from dropping tables on the live database.

Any help/direction here would be appreciated.

View Replies !

---

Database Deletion
While performing import actions I had a system freeze, when the system returned the sessions had been closed and the database had vanished, with the help of support we recovered the database only to find that the original project ID had a suffix attached ( Original 40/0110, New 40/0110-1 ), when I try to return it to it's original numbering convention it says it has to be a unique number which suggests to me it is not deleted but hiding in the background, can the original be recovered or is it possible to renumber the recovered database, I have searched the whole of the databases and the original is nowhere to be seen.

View Replies !

---

DB Deletion Time
Is there an option to find out the deleted DBs on a server?

------------------------
I think, therefore I am - Rene Descartes

View Replies !

---

Deletion And Identity Reset
Obviously to delete all records from DB table is simple, however, I would like to make my whole Live DB pretty much empty.  I've copied all my data from my test DB over to my live DB (didn't mean to but I did).  I would like to remove all the data and the identity values, resetting them back at their original values.  Is there a simple way or do I have to do it the hard way.  That being going in and removing Identity, saving and then placing identity back on the DB Table.

View Replies !

---

Alert On Data Deletion
We have an employee table that contains bank details and are experiencingproblems with account numbers being erased and lost. In order to track downwhy this is happening (either due to our application code or SQLreplication) we'd like to be able to prevent certain columns from beingdeleted if they already contain some data.Is it possible to setup a check constraint to prevent our ee_acct_no columnsfrom being set to NULL or blank strings if it contains an account number(i.e a 9 digit number)? We have setup the column to allow NULL's as we don'talways know employees bank details until later, so we do need to put them onour database without bank details initially.Also, if possible, can someone suggest a stored procedure or trigger i couldcreate that would fire a user-defined error message that would email anoperator if a bank account number changed?Many thanksDan Williams.

View Replies !

---

Recovering From Transaction Log Deletion In 6.5
I was trying to relocate my transaction log to a bigger drive usingsp_movedevice but I made a mistake in the syntax of the second parameterand put only the path, not the path and the file name.Now my database is marked as "suspect" and I get an error message in my logupon database start up saying that the log file cannot be open.Is there a way to have MS SQL 6.5 "forget" all the logs of this database,create new ones and restart the database? The logs contained nothingimportant, I had truncated them an hour or so before I made my mistake. Ijust want to make sure the data are still usable.When I look at the devices with sp_helpdevice, I can see a log that existand is hopefully in pristine condition and the one that doesn't existanymore.I looked in the archives of various newsgroups but couldn't find somethingthat correspond closely to my situation. I saw something similar but withMS SQL 7.0(http://groups.google.com/groups?hl=...om %26rnum%3D4)using sp_attach_db/sp_detach_db. What would be the equivalent with version6.5?Thanks!Charles--Charles-E. Nadeau Ph.Dhttp://radio.weblogs.com/0111823/

View Replies !

---

Database Still 'exists' After Deletion
hi

Basically, I create a database with sql, then I delete it manually(not via sql statment. This is a problem which I realise. In fact, you can't delete the database because the VS 2005 still is using it) I run the same code again,
then it says the database still exists, even it is physically destroied.

------Here is the errors:
System.Data.SqlClient.SqlException: Database 'riskDatabase' already exists.
at System.Data.SqlClient.SqlConnection.OnError(SqlExc eption exception, Boolea
n breakConnection)

------The evidence that the database doesn't exist physically:
Unhandled Exception: System.Data.SqlClient.SqlException: Cannot open database "riskDatabase" requested by the login. The login failed.

------The code:
/*
* C# code to programmically create
* database and table. It also inserts
* data into the table.
*/

using System;
using System.Collections.Generic;
using System.Text;

using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.IO;

namespace riskWizard
{
public class RiskWizard
{
// Sql
private string connectionString;
private SqlConnection connection;
private SqlCommand command;

// Database
private string databaseName;
private string currDatabasePath;
private string database_mdf;
private string database_ldf;

public RiskWizard(string databaseName, string currDatabasePath, string database_mdf, string database_ldf)
{
this.databaseName = databaseName;
this.currDatabasePath = currDatabasePath;
this.database_mdf = database_mdf;
this.database_ldf = database_ldf;
}

private void executeSql(string sql)
{
// Create a connection
connection = new SqlConnection(connectionString);

// Open the connection.
if (connection.State == ConnectionState.Open)
connection.Close();

connection.ConnectionString = connectionString;
connection.Open();

command = new SqlCommand(sql, connection);
try
{
command.ExecuteNonQuery();
}
catch (SqlException e)
{
Console.WriteLine(e.ToString());
}
}

public void createDatabase()
{
string database_data = databaseName + "_data";
string database_log = databaseName + "_log";

connectionString
= "Data Source=.\SQLExpress;Initial Catalog=;Integrated Security=SSPI;";

string sql = "CREATE DATABASE " + databaseName + " ON PRIMARY"
+ "(name=" + database_data + ",filename=" + database_mdf + ",size=3,"
+ "maxsize=5,filegrowth=10%)log on"
+ "(name=" + database_log + ",filename=" + database_ldf + ",size=3,"
+ "maxsize=20,filegrowth=1)";

executeSql(sql);
}

public void dropDatabase()
{
connectionString
= "Data Source=.\SQLExpress;Initial Catalog=" + databaseName + ";Integrated Security=SSPI;";

string sql = "DROP DATABASE " + databaseName;

executeSql(sql);
}

// Create table.
public void createTable(string tableName)
{
connectionString
= "Data Source=.\SQLExpress;Initial Catalog=" + databaseName + ";Integrated Security=SSPI;";

string sql = "CREATE TABLE " + tableName +
"(userId INTEGER IDENTITY(1, 1) CONSTRAINT PK_userID PRIMARY KEY," +
"name CHAR(50) NOT NULL, address CHAR(255) NOT NULL, employmentTitle TEXT NOT NULL)";

executeSql(sql);
}

// Insert data
public void insertData(string tableName)
{
string sql;

connectionString
= "Data Source=.\SQLExpress;Initial Catalog=" + databaseName + ";Integrated Security=SSPI;";

sql = "INSERT INTO " + tableName + "(userId, name, address, employmentTitle) " +
"VALUES (1001, 'Puneet Nehra', 'A 449 Sect 19, DELHI', 'project manager') ";
executeSql(sql);

sql = "INSERT INTO " + tableName + "(userId, name, address, employmentTitle) " +
"VALUES (1002, 'Anoop Singh', 'Lodi Road, DELHI', 'software admin') ";
executeSql(sql);

sql = "INSERT INTO " + tableName + "(userId, name, address, employmentTitle) " +
"VALUES (1003, 'Rakesh M', 'Nag Chowk, Jabalpur M.P.', 'tester') ";
executeSql(sql);

sql = "INSERT INTO " + tableName + "(userId, name, address, employmentTitle) " +
"VALUES (1004, 'Madan Kesh', '4th Street, Lane 3, DELHI', 'quality insurance mamager') ";
executeSql(sql);
}

public static void Main(String[] argv)
{
string databaseName = "riskDatabase";
string currDatabasePath = "E:\liveProgrammes\cSharpWorkplace\riskWizard\A pp_Data";
// Need to be more flexible.
string database_mdf = "'E:\liveProgrammes\cSharpWorkplace\riskWizard\ App_Data\riskDatabase.mdf'";
string database_ldf = "'E:\liveProgrammes\cSharpWorkplace\riskWizard\ App_Data\riskDatabase.ldf'";

RiskWizard riskWizard = new RiskWizard(databaseName, currDatabasePath, database_mdf, database_ldf);
riskWizard.createDatabase();
riskWizard.createTable("userTable");
riskWizard.insertData("userTable");
//riskWizard.dropDatabase();
}
}
}

View Replies !

---

Daily Deletion Of Records
Ladys, Gentlement, I have table that grows anywhere from 200,000 to 1,000,000 records perday. Besides that I need to keep at least 6 months historical data from this same table. The transaction log was purged after each batch when testing data monthly. I'm looking for some way of deleting just one day's data if it meets a criteria. It must remain within the 6 months period of historical data. This is what I've come up with so far"

select * FROM dbo.Temp_table WHERE datediff(day, DATE_TIME, getdate()) >= 180

If it meets this criteria I can change the select to a delete? Please Let me know what you think

View Replies !

---

Deletion Of Duplicate Values
hi,

i am trying to delete rows where a particular column (hours) has the same value for the same member (primary key) but where the effective dates are different. i want to delete the duplicate(s) rows which have the most recent effective date(s).

can you help?

View Replies !

---

How To Compare Data Before Deletion
 

SET identity_insert dbo.table1 on

GO

insert into dbo.table1(
PrimaryKeyCol,Col1, Col2 .....)
 

select

PrimaryKeyCol,Col1, Col2......
 
from [Sever].Database.dbo.table1 as ClientColumn

where not exists(

select * from dbo.table1 as ServerColumn

where ServerColumn.PrimaryKeyCol = ClientColumn.PrimaryKeyCol

)

DELETE FROM [Server].Database.dbo.table1

where exists(

where ServerColumn.PrimaryKeyCol = ClientColumn.PrimaryKeyCol

)

 

SET identity_insert dbo.table1 off

GO

 
I can't complie this code.. anybody see where I went wrong??
 
Thanks for all your help.

View Replies !

---

Table Deletion Error
hi

i am using sql server 2005 express edition , with asp.net

i am trying to delete a table programmatically

a button on a form , if the client clicked it , then a table
should be dropped .

but always i get an error message , that says "cannot drop table <table name> , becaust it does not exist or you do not have premissions to do that"

could any body help plz

thax

ghassan

View Replies !

---

Deletion Problem In Sql Table
i am using this statement for deleting a single row in sql table.
"DELETE FROM Random WHERE NewID= '" & strwinner & "'"

where "strwinner" is the variable which contains the row to be deleted.
the problem is that when i check the table in sql the row which was supposed to be deleted is sitll there.it does not give me any error statement or something.
iam executing this statement by using ExecuteNonQuery in my .aspx page.
please help

View Replies !

---

DOS Utility For File Deletion.
Does anyone has a script/exe for periodic file deletion, using date comparison?


------------------------
I think, therefore I am - Rene Descartes

View Replies !

---

Auto Deletion Of Records Sqlserver
 Hi I am not sure if I am at right place, anyhow I hope I am :) Now the question: I am using an ASP.net Application with SQL-Server. I want to make a page so that it set the expiration time (date)  for certain record and once that time reaches, it deletes those records, or make any updates to the record (what ever applicable). I also want to control this auto deletion from my application, means that turn this On/Off whenever needed. I am not sure how to start this. I was told by a friend that I need to use triggers from SQL-server but I need some help. Can anyone help me out on this? RegardsMykhan 

View Replies !

---

Deletion Old Data In Replication Environment
Hi to allI have a question about deletion of amount of data:My production environment is this one:- one publisher with a database (historycal events)- 50 subscribers with the prev database in unidirectional replicationunidirectional (from subscribers to publisher)My target was capturing events from the subscribers to send them topublisher (later I can do reports on it).Once the data is on the server i don't need them any more in subscribers.Now I would like to delete the oldest data (year 2003) of some table on thepublisher (remember that replication is unidirectional S->P).The tables contain about 6-7 millions of records.I delete one month per time. The process is about 30 minutes long and themerge agent subscribers changes in retry state.Can I use these queries to make faster this process? Eventually what kind ofproblems can I have ?DELETE FROM mydb WITH (PAGLOCK) WHERE mydb.dbo.mydate Between date1 anddate2orDELETE FROM mydb WITH (ROWLOCK) WHERE mydb.dbo.mydate Between date1 anddate2Thank you very much for your support.Marco

View Replies !

---

Rows Deletion Affected By Cursor
Hello,

I am using a cursor to navigate on data...of a table....
inside the while @@fetch_status = 0 command
I want to delete some rows from the table(temporary table)
in order to not be processed...
The problem is that I want this deletion to affect the rows the cursor has.

I declared a dynamic cursor but it does not work.

Does anyone know how I can do this??

Thanks :)

View Replies !

---

Retrival And Deletion Of Duplicate Rows.
I have a table...say tb1 of 20 columns which has 2.7 million rows. There is no PK and the only way of identifying a unique row can be done with combination of column1+column2+column3.
Can anyone help me how to idetify the duplicate rows and also delete the duplicate rows. And to commit after every 5000 rows.
ITS VERY URGENT....Thanks in advance.

View Replies !

---

Page 2 - Deletion Of Duplicate Values
Quote: Originally Posted by achoudry ps i am using sqlserver well, whaddya know, eh

do you realize you posted in the mysql forum?

i'm gonna move this thread to the sql server forum

View Replies !

---

SSIS Crash On Breakpoint Deletion
I'm having an issue with trying to delete breakpoints in my SSIS package.  I think the breakpoints were created when I added dataviewers through my process, however the breakpoints were not deleted when I removed the dataviewers.  It would then appear that my process would not halt on ANY breakpoints - orphaned or valid. 
 
Furthermore, whenever I tried deleting the breakpoints manually, my whole IDE would crash.  I got around the issue by closing the dtsx page first, then deleting the breakpoints, and reopening my dtsx. 
 
Are these valid bugs with SSIS?  Has anyone else experienced this?  I'm running this against a SQL Server 2005 database using VStudio 2005 as an IDE.
 
Thanks!
Chris P

View Replies !

---

Deletion/Rename Of Master Database.
Hi All,

Can we have an sql server installation where we dont have a master database. Can the complete data dictionary be stored in another database , or put it other way can master database be renamed.

I have a need to assume that there will always be a master database for any SQL server instance. Want to confirm whether this assumption is true or not.

Thanks in advance.

Chandrakant Karale.

View Replies !

---

Unique Columns And Logical Deletion
In tables where reporting or historical information needs to be maintained I find that logical deletion of rows is better than actually deleting the row and any FK relationships.  However when one of the columns in the table must be unique this introduces a problem because uniqueness only matters for "active" rows.  I'm hoping someone can provide some advice on how to best handle this situation.

 

Here is an example to work from.  Suppose we have a table that contains job positions.  The table contains an IDENTITY column for the PK (to avoid duplicating position names), a Name column that must be unique and an IsActive column that is set to 0 when the position has been "deleted".  This setup allows for historical reporting to properly pick up the position information even though it might no longer be used in a company.  You can substitute your own scenarios here as there are quite a few.

 

Now if the Name column is marked as a unique column then the DB will enforce integrity which is what we want.  However we will want to be able to "delete" a position but later add a new position with the same name.  One could argue that we should just "undelete" the original position but that is not often a good idea so assume "undelete" is not an option.  Given the unique constraint a new position could not be created if an existing position (active or otherwise) already existed with the same name.  We could set up the unique constraint to include the Name and IsActive columns but now we are prevented from ever deleting two positions with the same name.

 

My initial thought is to create a new UniqueName column that is unique and remove the uniqueness constraint on the Name column.  The UniqueName column is set (via a trigger) to the Name column when the position is active.  When the position is deleted the trigger changes the UniqueName to include a timestamp or something to ensure uniqueness.  This is handled by the system and not exposed to clients.

 

Any advice or alternatives on this approach (preferably with advantages and disadvantages)?  Thanks.

 

Michael Taylor - 5/31/07

http://p3net.mvps.org

 

View Replies !

---

Deletion Of Parent And Child Table
how can we delete parent table as well as child table using a single query applied on parent table, can someone please help me onn this topic? it will be very nice of you guys.

Rahul Arora
07 Batch
NCCE Israna,


######################
IMPOSSIBLE = I+M+POSSIBLE

View Replies !

---

MS SQL Server 7: Deletion Of Child Records.
Hello All,

I have been involved in a project wherein i have to delete old records from two table and the corresponding child records from the interlinked tables.
I try to find a SQL Procedure that deletes the child records, but that procedure uses the sysforeignkeys table for recursively deleting the child records.
When i looked at my sysoreignkeys table, it was empty(no rows).
Request you all if you can provide me the SQL Procedure that can delete record in the table with the child records.

Thanks in advance

Regards,
Manoj Kumar

View Replies !

---

Deletion Of Data Other Than English Language
Anil Chauhan writes "Hello experts,

I have a table in sql server 2000 which has over 94000 records.
I have to delete a record from table ,which record having a language other than english .
I need to clean the table by removing all the data which are in other language .
My main table has 12 fields .

Thanks in advance."

View Replies !

---

Capturing Mysterious Truncation/deletion Of A Table
I have a dts which creates a table which is utilized on my localintranet. The DTS runs without error and the table iscreated/populated/transfered to the appropriate db. Then it appearsthat there is an action on this table which truncates it. I have beenunable to determine the culprit. Can I create a trigger that willcapture truncation? I have tried to create a trigger to capture thisinformation but none that I attempt seem to work on capturing atruncation or a drop table and re-create.Any help would be greatly appreciated.MT.

View Replies !

---

File System Task-deletion Operation
I am using file system task for deletion of text file from a specified folder.

but i want that one text file with name ="ssis.txt" and "ssrs.txt "should not get delete.

Is there any option or any expression that we can set and avoide the deletion of ssis.txt and ssrs.txt and still delete all the remaining all text file from a folder.

I just wish to use the file system task and perform this.

Cannt we use some expression infile system task and disable the deletion for say 2 files and let the deletion happen for other remaining files.

Please suggest any method or expression by example.

 

 

View Replies !

---

Data Deletion On MS Access Table Hangs
Hello,
I have not been able to locate information on the following problem. The first step I have in a packge (Execute SQL Command) is to delete the data from an MS Access database table. The package hangs at this step after all validation is complete. In the package, once the table data is deleted, it is repopulated in a later step. The deletion step and the repopulation step use the same connection manager.
 
There is no information in the log about an error. At the time the package ran, there was a lock file on the database with about six users connected. I'm not sure what version of Access the database was created in, but I have 2003 on my machine, and I cannot open the database.
 
Any ideas?
 
Thank you for your help!
 
cdun2

View Replies !

---