Problem with Update Trigger

SQL2008

I have two databases, both with identical tables ( apart from TableNames ).
I have tried putting an update trigger on DB1.tbCBMOAccount to update the 
field country on DB2.tbBOSS_OAccount with the updated value from 
DB1.tbCBMOAccount

ALTER TRIGGER [dbo].[trig_UpdateOriginatongAccounts]
   ON  [DB1].[dbo].[tbCBMOAccount]
   AFTER UPDATE
AS 
BEGIN

Declare @OAccountID UniqueIdentifier
Declare @Country varchar(32)
Select @OAccountID = OAccountID , @Country = Country From Inserted

IF  UPDATE(Country)
 BEGIN
 UPDATE DB2.dbo.tbBOSS_OAccount
  SET Country = @Country
  WHERE OAccountID = @OAccountID
 END
END

I have double checked, there is only 1 record in each table with the same 
UniqueIdentifier. When I update country  DB1.tbCBMOAccount,  i get the 
following error;

No Row was updated

The data in row 4 was not committed
Error Message: The row value(s) updated or deleted either do not make the 
row unique or they alter multiple rows(2)
0
Utf
5/19/2010 12:43:01 PM
sqlserver.programming 1873 articles. 0 followers. Follow

10 Replies
1276 Views

Similar Articles

[PageSpeed] 35

"Peter Newman" <PeterNewman@discussions.microsoft.com> wrote in message 
news:AEEB0511-68E7-406E-A33D-C9DBDA34FFAE@microsoft.com...
> SQL2008
>
> I have two databases, both with identical tables ( apart from 
> TableNames ).
> I have tried putting an update trigger on DB1.tbCBMOAccount to update the
> field country on DB2.tbBOSS_OAccount with the updated value from
> DB1.tbCBMOAccount
>
> ALTER TRIGGER [dbo].[trig_UpdateOriginatongAccounts]
>   ON  [DB1].[dbo].[tbCBMOAccount]
>   AFTER UPDATE
> AS
> BEGIN
>
> Declare @OAccountID UniqueIdentifier
> Declare @Country varchar(32)
> Select @OAccountID = OAccountID , @Country = Country From Inserted
>
> IF  UPDATE(Country)
> BEGIN
> UPDATE DB2.dbo.tbBOSS_OAccount
>  SET Country = @Country
>  WHERE OAccountID = @OAccountID
> END
> END
>
> I have double checked, there is only 1 record in each table with the same
> UniqueIdentifier. When I update country  DB1.tbCBMOAccount,  i get the
> following error;
>
> No Row was updated
>
> The data in row 4 was not committed
> Error Message: The row value(s) updated or deleted either do not make the
> row unique or they alter multiple rows(2)


Don't write triggers that only handle single row updates. I think this might 
work:


 ALTER TRIGGER [dbo].[trig_UpdateOriginatongAccounts]
   ON  [DB1].[dbo].[tbCBMOAccount]
   AFTER UPDATE
 AS
 BEGIN

 IF  UPDATE(Country)
   BEGIN
     UPDATE a
     SET Country = b.Country
     FROM DB2.dbo.tbBOSS_OAccount a
     INNER JOIN [tbCBMOAccount] b ON a.OACcountID = b.OAccountID
   END
 END



-- 
Dan


 

0
Dan
5/19/2010 1:14:32 PM
On Wed, 19 May 2010 05:43:01 -0700, Peter Newman
<PeterNewman@discussions.microsoft.com> wrote:

>SQL2008
>
>I have two databases, both with identical tables ( apart from TableNames ).
>I have tried putting an update trigger on DB1.tbCBMOAccount to update the 
>field country on DB2.tbBOSS_OAccount with the updated value from 
>DB1.tbCBMOAccount
>
>ALTER TRIGGER [dbo].[trig_UpdateOriginatongAccounts]
>   ON  [DB1].[dbo].[tbCBMOAccount]
>   AFTER UPDATE
>AS 
>BEGIN
>
>Declare @OAccountID UniqueIdentifier
>Declare @Country varchar(32)
>Select @OAccountID = OAccountID , @Country = Country From Inserted
>
>IF  UPDATE(Country)
> BEGIN
> UPDATE DB2.dbo.tbBOSS_OAccount
>  SET Country = @Country
>  WHERE OAccountID = @OAccountID
> END
>END
>
>I have double checked, there is only 1 record in each table with the same 
>UniqueIdentifier. When I update country  DB1.tbCBMOAccount,  i get the 
>following error;
>
>No Row was updated
>
>The data in row 4 was not committed
>Error Message: The row value(s) updated or deleted either do not make the 
>row unique or they alter multiple rows(2)

Hi

Although there is only 1 row in the table now, I would always
recommend that a trigger is written in a way that would not fail if
there was multiple rows in the table.

ALTER TRIGGER [dbo].[trig_UpdateOriginatongAccounts]
	ON  [DB1].[dbo].[tbCBMOAccount]
	AFTER UPDATE
AS 
BEGIN

IF  UPDATE(Country)
 BEGIN
	 UPDATE t2
	  SET Country = i.Country
	FROM DB2.dbo.tbBOSS_OAccount
	JOIN inserted i ON i.OAccountID = t2.OAccountID
	AND  t2.Country <>  i.Country
	
END

END

You can run SQL profiler at statement level to see which if the
trigger gets executed. 

Please post DDL and example data as SQL statements if you still have
problems.

John
0
John
5/19/2010 1:37:02 PM
On Wed, 19 May 2010 14:37:02 +0100, John Bell
<jbellnewsposts@hotmail.com> wrote:

>On Wed, 19 May 2010 05:43:01 -0700, Peter Newman
><PeterNewman@discussions.microsoft.com> wrote:
>
>>SQL2008
>>
>>I have two databases, both with identical tables ( apart from TableNames ).
>>I have tried putting an update trigger on DB1.tbCBMOAccount to update the 
>>field country on DB2.tbBOSS_OAccount with the updated value from 
>>DB1.tbCBMOAccount
>>
>>ALTER TRIGGER [dbo].[trig_UpdateOriginatongAccounts]
>>   ON  [DB1].[dbo].[tbCBMOAccount]
>>   AFTER UPDATE
>>AS 
>>BEGIN
>>
>>Declare @OAccountID UniqueIdentifier
>>Declare @Country varchar(32)
>>Select @OAccountID = OAccountID , @Country = Country From Inserted
>>
>>IF  UPDATE(Country)
>> BEGIN
>> UPDATE DB2.dbo.tbBOSS_OAccount
>>  SET Country = @Country
>>  WHERE OAccountID = @OAccountID
>> END
>>END
>>
>>I have double checked, there is only 1 record in each table with the same 
>>UniqueIdentifier. When I update country  DB1.tbCBMOAccount,  i get the 
>>following error;
>>
>>No Row was updated
>>
>>The data in row 4 was not committed
>>Error Message: The row value(s) updated or deleted either do not make the 
>>row unique or they alter multiple rows(2)
>
>Hi
>
>Although there is only 1 row in the table now, I would always
>recommend that a trigger is written in a way that would not fail if
>there was multiple rows in the table.
>
>ALTER TRIGGER [dbo].[trig_UpdateOriginatongAccounts]
>	ON  [DB1].[dbo].[tbCBMOAccount]
>	AFTER UPDATE
>AS 
>BEGIN
>
>IF  UPDATE(Country)
> BEGIN
>	 UPDATE t2
>	  SET Country = i.Country
>	FROM DB2.dbo.tbBOSS_OAccount 
>	JOIN inserted i ON i.OAccountID = t2.OAccountID
>	AND  t2.Country <>  i.Country
>	
>END
>
>END
>
>You can run SQL profiler at statement level to see which if the
>trigger gets executed. 
>
>Please post DDL and example data as SQL statements if you still have
>problems.
>
>John


I missed the t2 alias out of the update statement!

John
0
John
5/19/2010 1:57:22 PM
Hi Dan,

Thanks for that, I have tried your suggestion but still it fails with same 
error but this time rows = 11.

I read your comment about not using triggers for single row updates, but 
why?. 
The scenerio i have is that our company has two DataBases, One used buy the 
shopfloor staff, and one by the management. This has been set like this for 
years. If a member of staff changes any details on the shopfloor db, they 
need to be reflected realtime to the management DB. I'm open to any other 
suggestions of how to do this 


"Dan" wrote:

> 
> "Peter Newman" <PeterNewman@discussions.microsoft.com> wrote in message 
> news:AEEB0511-68E7-406E-A33D-C9DBDA34FFAE@microsoft.com...
> > SQL2008
> >
> > I have two databases, both with identical tables ( apart from 
> > TableNames ).
> > I have tried putting an update trigger on DB1.tbCBMOAccount to update the
> > field country on DB2.tbBOSS_OAccount with the updated value from
> > DB1.tbCBMOAccount
> >
> > ALTER TRIGGER [dbo].[trig_UpdateOriginatongAccounts]
> >   ON  [DB1].[dbo].[tbCBMOAccount]
> >   AFTER UPDATE
> > AS
> > BEGIN
> >
> > Declare @OAccountID UniqueIdentifier
> > Declare @Country varchar(32)
> > Select @OAccountID = OAccountID , @Country = Country From Inserted
> >
> > IF  UPDATE(Country)
> > BEGIN
> > UPDATE DB2.dbo.tbBOSS_OAccount
> >  SET Country = @Country
> >  WHERE OAccountID = @OAccountID
> > END
> > END
> >
> > I have double checked, there is only 1 record in each table with the same
> > UniqueIdentifier. When I update country  DB1.tbCBMOAccount,  i get the
> > following error;
> >
> > No Row was updated
> >
> > The data in row 4 was not committed
> > Error Message: The row value(s) updated or deleted either do not make the
> > row unique or they alter multiple rows(2)
> 
> 
> Don't write triggers that only handle single row updates. I think this might 
> work:
> 
> 
>  ALTER TRIGGER [dbo].[trig_UpdateOriginatongAccounts]
>    ON  [DB1].[dbo].[tbCBMOAccount]
>    AFTER UPDATE
>  AS
>  BEGIN
> 
>  IF  UPDATE(Country)
>    BEGIN
>      UPDATE a
>      SET Country = b.Country
>      FROM DB2.dbo.tbBOSS_OAccount a
>      INNER JOIN [tbCBMOAccount] b ON a.OACcountID = b.OAccountID
>    END
>  END
> 
> 
> 
> -- 
> Dan
> 
> 
>  
> 
0
Utf
5/19/2010 2:05:01 PM
"Peter Newman" <PeterNewman@discussions.microsoft.com> wrote in message 
news:A5C820F3-4F16-434E-BE49-680A41C4B2A8@microsoft.com...
> Hi Dan,
>
> Thanks for that, I have tried your suggestion but still it fails with same
> error but this time rows = 11.

Have you got any other triggers on the table being updated?


> I read your comment about not using triggers for single row updates, but
> why?.

Because an update can affect more than one row - so for instance if you had

UPDATE [tbCBMOAccount] SET Country = 'UK' WHERE Country = 'GB'

and there were 15 rows in tbCBMOAccount with the Country column set to GB, 
then the update trigger would have 15 rows in the inserted and deleted 
tables and your code assigns columns to variables which will fail with more 
than one row.

> The scenerio i have is that our company has two DataBases, One used buy 
> the
> shopfloor staff, and one by the management. This has been set like this 
> for
> years. If a member of staff changes any details on the shopfloor db, they
> need to be reflected realtime to the management DB. I'm open to any other
> suggestions of how to do this

Personally, I use transactional replication for this sort of thing - if one 
database is designed to be a duplicate of the other, use replication and let 
the underlying distribution system deal with it. It might however be 
overkill depending on how small or large your database is.

Dan

>
> "Dan" wrote:
>
>>
>> "Peter Newman" <PeterNewman@discussions.microsoft.com> wrote in message
>> news:AEEB0511-68E7-406E-A33D-C9DBDA34FFAE@microsoft.com...
>> > SQL2008
>> >
>> > I have two databases, both with identical tables ( apart from
>> > TableNames ).
>> > I have tried putting an update trigger on DB1.tbCBMOAccount to update 
>> > the
>> > field country on DB2.tbBOSS_OAccount with the updated value from
>> > DB1.tbCBMOAccount
>> >
>> > ALTER TRIGGER [dbo].[trig_UpdateOriginatongAccounts]
>> >   ON  [DB1].[dbo].[tbCBMOAccount]
>> >   AFTER UPDATE
>> > AS
>> > BEGIN
>> >
>> > Declare @OAccountID UniqueIdentifier
>> > Declare @Country varchar(32)
>> > Select @OAccountID = OAccountID , @Country = Country From Inserted
>> >
>> > IF  UPDATE(Country)
>> > BEGIN
>> > UPDATE DB2.dbo.tbBOSS_OAccount
>> >  SET Country = @Country
>> >  WHERE OAccountID = @OAccountID
>> > END
>> > END
>> >
>> > I have double checked, there is only 1 record in each table with the 
>> > same
>> > UniqueIdentifier. When I update country  DB1.tbCBMOAccount,  i get the
>> > following error;
>> >
>> > No Row was updated
>> >
>> > The data in row 4 was not committed
>> > Error Message: The row value(s) updated or deleted either do not make 
>> > the
>> > row unique or they alter multiple rows(2)
>>
>>
>> Don't write triggers that only handle single row updates. I think this 
>> might
>> work:
>>
>>
>>  ALTER TRIGGER [dbo].[trig_UpdateOriginatongAccounts]
>>    ON  [DB1].[dbo].[tbCBMOAccount]
>>    AFTER UPDATE
>>  AS
>>  BEGIN
>>
>>  IF  UPDATE(Country)
>>    BEGIN
>>      UPDATE a
>>      SET Country = b.Country
>>      FROM DB2.dbo.tbBOSS_OAccount a
>>      INNER JOIN [tbCBMOAccount] b ON a.OACcountID = b.OAccountID
>>    END
>>  END
>>
>>
>>
>> -- 
>> Dan
>>
>>
>>
>>

 

0
Dan
5/19/2010 2:29:19 PM
Hi John,

Still struggling here.  Code below.  

CREATE TABLE [tbCBMOAccount](
	[Licence] [varchar](6) NOT NULL,
	[OAccountID] [uniqueidentifier] NOT NULL,
	[OAccountClientID] [int] NOT NULL,
	[Country] [varchar](32) NULL,
	[BACSID] [varchar](6) NOT NULL,
	[SortCode] [varchar](6) NOT NULL,
	[AccountNumber] [varchar](8) NOT NULL,
	[AccountName] [varchar](32) NOT NULL,
	[DailyLimit] [money] NULL,
	[WeeklyLimit] [money] NULL,
	[VarPeriodDays] [int] NULL,
	[VarPeriodLimit] [money] NULL,
	[MonthlyLimit] [money] NULL,
	[AllowPayments] [bit] NULL,
	[AllowDebits] [bit] NULL,
	[Active] [bit] NULL
) ON [PRIMARY]

CREATE TABLE [tbBOSS_OAccount](
	[Licence] [varchar](6) NOT NULL,
	[OAccountID] [uniqueidentifier] NOT NULL,
	[OAccountClientID] [int] NOT NULL,
	[Country] [varchar](32) NULL,
	[BACSID] [varchar](6) NOT NULL,
	[SortCode] [varchar](6) NOT NULL,
	[AccountNumber] [varchar](8) NOT NULL,
	[AccountName] [varchar](32) NOT NULL,
	[DailyLimit] [money] NULL,
	[WeeklyLimit] [money] NULL,
	[VarPeriodDays] [int] NULL,
	[VarPeriodLimit] [money] NULL,
	[MonthlyLimit] [money] NULL,
	[AllowPayments] [bit] NULL,
	[AllowDebits] [bit] NULL,
	[Active] [bit] NULL
) ON [PRIMARY]


INSERT INTO [tbBOSS_OAccount]
           ([Licence] ,[OAccountID] ,[OAccountClientID] ,[Country] ,[BACSID] 
,[SortCode] ,[AccountNumber] ,[AccountName] ,[DailyLimit] ,[WeeklyLimit] 
,[VarPeriodDays] ,[VarPeriodLimit] ,[MonthlyLimit] ,[AllowPayments] 
,[AllowDebits] ,[Active])
             VALUES
            
(111111,33fe22d4-4dd5-48f6-8fb6-c1d4d9cbdc62,2,UK,111111,222222,12345678,TEST 
COMPANY,1.0000,11.0000,111,23.0000,11111.0000,True,False,True)
INSERT INTO [tbBOSS_OAccount]
           ([Licence] ,[OAccountID] ,[OAccountClientID] ,[Country] ,[BACSID] 
,[SortCode] ,[AccountNumber] ,[AccountName] ,[DailyLimit] ,[WeeklyLimit] 
,[VarPeriodDays] ,[VarPeriodLimit] ,[MonthlyLimit] ,[AllowPayments] 
,[AllowDebits] ,[Active])
             VALUES
           
(222222,dc408d70-977b-4354-a6ec-9bec182e043f,1,UK,430639,832845,30989312,TEST 
COMPANY 2,0.0000,0.0000,5,0.0000,10000.0000,True,True,True)
INSERT INTO [tbCBMOAccount]
           ([Licence] ,[OAccountID] ,[OAccountClientID] ,[Country] ,[BACSID] 
,[SortCode] ,[AccountNumber] ,[AccountName] ,[DailyLimit] ,[WeeklyLimit] 
,[VarPeriodDays] ,[VarPeriodLimit] ,[MonthlyLimit] ,[AllowPayments] 
,[AllowDebits] ,[Active])
             VALUES
           
(111111,33fe22d4-4dd5-48f6-8fb6-c1d4d9cbdc62,2,GER,111111,222222,12345678,TEST COMPANY,1.0000,11.0000,111,23.0000,11111.0000,True,False,True)
 INSERT INTO [tbCBMOAccount]
           ([Licence] ,[OAccountID] ,[OAccountClientID] ,[Country] ,[BACSID] 
,[SortCode] ,[AccountNumber] ,[AccountName] ,[DailyLimit] ,[WeeklyLimit] 
,[VarPeriodDays] ,[VarPeriodLimit] ,[MonthlyLimit] ,[AllowPayments] 
,[AllowDebits] ,[Active])
             VALUES
           
(222222,dc408d70-977b-4354-a6ec-9bec182e043f,1,UK,430639,832845,30989312,TEST 
COMPANY 2,0.0000,0.0000,5,0.0000,10000.0000,True,True,True)



"John Bell" wrote:

> On Wed, 19 May 2010 14:37:02 +0100, John Bell
> <jbellnewsposts@hotmail.com> wrote:
> 
> >On Wed, 19 May 2010 05:43:01 -0700, Peter Newman
> ><PeterNewman@discussions.microsoft.com> wrote:
> >
> >>SQL2008
> >>
> >>I have two databases, both with identical tables ( apart from TableNames ).
> >>I have tried putting an update trigger on DB1.tbCBMOAccount to update the 
> >>field country on DB2.tbBOSS_OAccount with the updated value from 
> >>DB1.tbCBMOAccount
> >>
> >>ALTER TRIGGER [dbo].[trig_UpdateOriginatongAccounts]
> >>   ON  [DB1].[dbo].[tbCBMOAccount]
> >>   AFTER UPDATE
> >>AS 
> >>BEGIN
> >>
> >>Declare @OAccountID UniqueIdentifier
> >>Declare @Country varchar(32)
> >>Select @OAccountID = OAccountID , @Country = Country From Inserted
> >>
> >>IF  UPDATE(Country)
> >> BEGIN
> >> UPDATE DB2.dbo.tbBOSS_OAccount
> >>  SET Country = @Country
> >>  WHERE OAccountID = @OAccountID
> >> END
> >>END
> >>
> >>I have double checked, there is only 1 record in each table with the same 
> >>UniqueIdentifier. When I update country  DB1.tbCBMOAccount,  i get the 
> >>following error;
> >>
> >>No Row was updated
> >>
> >>The data in row 4 was not committed
> >>Error Message: The row value(s) updated or deleted either do not make the 
> >>row unique or they alter multiple rows(2)
> >
> >Hi
> >
> >Although there is only 1 row in the table now, I would always
> >recommend that a trigger is written in a way that would not fail if
> >there was multiple rows in the table.
> >
> >ALTER TRIGGER [dbo].[trig_UpdateOriginatongAccounts]
> >	ON  [DB1].[dbo].[tbCBMOAccount]
> >	AFTER UPDATE
> >AS 
> >BEGIN
> >
> >IF  UPDATE(Country)
> > BEGIN
> >	 UPDATE t2
> >	  SET Country = i.Country
> >	FROM DB2.dbo.tbBOSS_OAccount 
> >	JOIN inserted i ON i.OAccountID = t2.OAccountID
> >	AND  t2.Country <>  i.Country
> >	
> >END
> >
> >END
> >
> >You can run SQL profiler at statement level to see which if the
> >trigger gets executed. 
> >
> >Please post DDL and example data as SQL statements if you still have
> >problems.
> >
> >John
> 
> 
> I missed the t2 alias out of the update statement!
> 
> John
> .
> 
0
Utf
5/19/2010 3:27:01 PM
Hi Dan,

I thought of  transactional replication  but like you said, I think this 
will be a bit of overkill, pluse could result in problems if I need to update 
different fields from DB2 to DB1 . Thanks anyway for your help 

"Dan" wrote:

> 
> "Peter Newman" <PeterNewman@discussions.microsoft.com> wrote in message 
> news:A5C820F3-4F16-434E-BE49-680A41C4B2A8@microsoft.com...
> > Hi Dan,
> >
> > Thanks for that, I have tried your suggestion but still it fails with same
> > error but this time rows = 11.
> 
> Have you got any other triggers on the table being updated?
> 
> 
> > I read your comment about not using triggers for single row updates, but
> > why?.
> 
> Because an update can affect more than one row - so for instance if you had
> 
> UPDATE [tbCBMOAccount] SET Country = 'UK' WHERE Country = 'GB'
> 
> and there were 15 rows in tbCBMOAccount with the Country column set to GB, 
> then the update trigger would have 15 rows in the inserted and deleted 
> tables and your code assigns columns to variables which will fail with more 
> than one row.
> 
> > The scenerio i have is that our company has two DataBases, One used buy 
> > the
> > shopfloor staff, and one by the management. This has been set like this 
> > for
> > years. If a member of staff changes any details on the shopfloor db, they
> > need to be reflected realtime to the management DB. I'm open to any other
> > suggestions of how to do this
> 
> Personally, I use transactional replication for this sort of thing - if one 
> database is designed to be a duplicate of the other, use replication and let 
> the underlying distribution system deal with it. It might however be 
> overkill depending on how small or large your database is.
> 
> Dan
> 
> >
> > "Dan" wrote:
> >
> >>
> >> "Peter Newman" <PeterNewman@discussions.microsoft.com> wrote in message
> >> news:AEEB0511-68E7-406E-A33D-C9DBDA34FFAE@microsoft.com...
> >> > SQL2008
> >> >
> >> > I have two databases, both with identical tables ( apart from
> >> > TableNames ).
> >> > I have tried putting an update trigger on DB1.tbCBMOAccount to update 
> >> > the
> >> > field country on DB2.tbBOSS_OAccount with the updated value from
> >> > DB1.tbCBMOAccount
> >> >
> >> > ALTER TRIGGER [dbo].[trig_UpdateOriginatongAccounts]
> >> >   ON  [DB1].[dbo].[tbCBMOAccount]
> >> >   AFTER UPDATE
> >> > AS
> >> > BEGIN
> >> >
> >> > Declare @OAccountID UniqueIdentifier
> >> > Declare @Country varchar(32)
> >> > Select @OAccountID = OAccountID , @Country = Country From Inserted
> >> >
> >> > IF  UPDATE(Country)
> >> > BEGIN
> >> > UPDATE DB2.dbo.tbBOSS_OAccount
> >> >  SET Country = @Country
> >> >  WHERE OAccountID = @OAccountID
> >> > END
> >> > END
> >> >
> >> > I have double checked, there is only 1 record in each table with the 
> >> > same
> >> > UniqueIdentifier. When I update country  DB1.tbCBMOAccount,  i get the
> >> > following error;
> >> >
> >> > No Row was updated
> >> >
> >> > The data in row 4 was not committed
> >> > Error Message: The row value(s) updated or deleted either do not make 
> >> > the
> >> > row unique or they alter multiple rows(2)
> >>
> >>
> >> Don't write triggers that only handle single row updates. I think this 
> >> might
> >> work:
> >>
> >>
> >>  ALTER TRIGGER [dbo].[trig_UpdateOriginatongAccounts]
> >>    ON  [DB1].[dbo].[tbCBMOAccount]
> >>    AFTER UPDATE
> >>  AS
> >>  BEGIN
> >>
> >>  IF  UPDATE(Country)
> >>    BEGIN
> >>      UPDATE a
> >>      SET Country = b.Country
> >>      FROM DB2.dbo.tbBOSS_OAccount a
> >>      INNER JOIN [tbCBMOAccount] b ON a.OACcountID = b.OAccountID
> >>    END
> >>  END
> >>
> >>
> >>
> >> -- 
> >> Dan
> >>
> >>
> >>
> >>
> 
>  
> 
> .
> 
0
Utf
5/19/2010 3:29:01 PM
On Wed, 19 May 2010 07:05:01 -0700, Peter Newman
<PeterNewman@discussions.microsoft.com> wrote:

>Hi Dan,
>
>Thanks for that, I have tried your suggestion but still it fails with same 
>error but this time rows = 11.
>
>I read your comment about not using triggers for single row updates, but 
>why?. 
>The scenerio i have is that our company has two DataBases, One used buy the 
>shopfloor staff, and one by the management. This has been set like this for 
>years. If a member of staff changes any details on the shopfloor db, they 
>need to be reflected realtime to the management DB. I'm open to any other 
>suggestions of how to do this 
>
>
>"Dan" wrote:
>
>> 
>> "Peter Newman" <PeterNewman@discussions.microsoft.com> wrote in message 
>> news:AEEB0511-68E7-406E-A33D-C9DBDA34FFAE@microsoft.com...
>> > SQL2008
>> >
>> > I have two databases, both with identical tables ( apart from 
>> > TableNames ).
>> > I have tried putting an update trigger on DB1.tbCBMOAccount to update the
>> > field country on DB2.tbBOSS_OAccount with the updated value from
>> > DB1.tbCBMOAccount
>> >
>> > ALTER TRIGGER [dbo].[trig_UpdateOriginatongAccounts]
>> >   ON  [DB1].[dbo].[tbCBMOAccount]
>> >   AFTER UPDATE
>> > AS
>> > BEGIN
>> >
>> > Declare @OAccountID UniqueIdentifier
>> > Declare @Country varchar(32)
>> > Select @OAccountID = OAccountID , @Country = Country From Inserted
>> >
>> > IF  UPDATE(Country)
>> > BEGIN
>> > UPDATE DB2.dbo.tbBOSS_OAccount
>> >  SET Country = @Country
>> >  WHERE OAccountID = @OAccountID
>> > END
>> > END
>> >
>> > I have double checked, there is only 1 record in each table with the same
>> > UniqueIdentifier. When I update country  DB1.tbCBMOAccount,  i get the
>> > following error;
>> >
>> > No Row was updated
>> >
>> > The data in row 4 was not committed
>> > Error Message: The row value(s) updated or deleted either do not make the
>> > row unique or they alter multiple rows(2)
>> 
>> 
>> Don't write triggers that only handle single row updates. I think this might 
>> work:
>> 
>> 
>>  ALTER TRIGGER [dbo].[trig_UpdateOriginatongAccounts]
>>    ON  [DB1].[dbo].[tbCBMOAccount]
>>    AFTER UPDATE
>>  AS
>>  BEGIN
>> 
>>  IF  UPDATE(Country)
>>    BEGIN
>>      UPDATE a
>>      SET Country = b.Country
>>      FROM DB2.dbo.tbBOSS_OAccount a
>>      INNER JOIN [tbCBMOAccount] b ON a.OACcountID = b.OAccountID
>>    END
>>  END
>> 
>> 
>> 
>> -- 
>> Dan
>> 
>> 
Peter

Just because it has been that way for years it doesn't mean to say it
is correct or good practice to do it. This sounds like a typical
problem where the data has changed and the code doesn't work because
it has made such an assumption.

Normally I would expect the rowcounts returned to be like:

(0 row(s) affected)

(1 row(s) affected)

Which is not what you are indicating, therefore I guess something is
happening that you are not telling us about. See what you get in SQL
Profiler as I suggested in my other post.

If the transaction rolled back then the update to tbCBMOAccount would
not be changed and if they are out of sync then the logic would be
incorrect. You don't say what the data is after this update. Scripting
out the tables and data would remove some of the ambiguites and give
us far more information.

A wild guess would be that in the trigger there is a SET NOCOUNT ON
which is suppressing the result set and the application can not cope.

John
0
John
5/19/2010 4:13:46 PM
On Wed, 19 May 2010 08:27:01 -0700, Peter Newman
<PeterNewman@discussions.microsoft.com> wrote:

I've updated the SQL so the inserts work!

CREATE TABLE [tbCBMOAccount](
	[Licence] [varchar](6) NOT NULL,
	[OAccountID] [uniqueidentifier] NOT NULL,
	[OAccountClientID] [int] NOT NULL,
	[Country] [varchar](32) NULL,
	[BACSID] [varchar](6) NOT NULL,
	[SortCode] [varchar](6) NOT NULL,
	[AccountNumber] [varchar](8) NOT NULL,
	[AccountName] [varchar](32) NOT NULL,
	[DailyLimit] [money] NULL,
	[WeeklyLimit] [money] NULL,
	[VarPeriodDays] [int] NULL,
	[VarPeriodLimit] [money] NULL,
	[MonthlyLimit] [money] NULL,
	[AllowPayments] [bit] NULL,
	[AllowDebits] [bit] NULL,
	[Active] [bit] NULL
) ON [PRIMARY]

CREATE TABLE [tbBOSS_OAccount](
	[Licence] [varchar](6) NOT NULL,
	[OAccountID] [uniqueidentifier] NOT NULL,
	[OAccountClientID] [int] NOT NULL,
	[Country] [varchar](32) NULL,
	[BACSID] [varchar](6) NOT NULL,
	[SortCode] [varchar](6) NOT NULL,
	[AccountNumber] [varchar](8) NOT NULL,
	[AccountName] [varchar](32) NOT NULL,
	[DailyLimit] [money] NULL,
	[WeeklyLimit] [money] NULL,
	[VarPeriodDays] [int] NULL,
	[VarPeriodLimit] [money] NULL,
	[MonthlyLimit] [money] NULL,
	[AllowPayments] [bit] NULL,
	[AllowDebits] [bit] NULL,
	[Active] [bit] NULL
) ON [PRIMARY]


INSERT INTO [tbBOSS_OAccount]
           ([Licence] ,[OAccountID] ,[OAccountClientID] ,[Country]
,[BACSID] 
,[SortCode] ,[AccountNumber] ,[AccountName] ,[DailyLimit]
,[WeeklyLimit] 
,[VarPeriodDays] ,[VarPeriodLimit] ,[MonthlyLimit] ,[AllowPayments] 
,[AllowDebits] ,[Active])
             VALUES
(111111,'33fe22d4-4dd5-48f6-8fb6-c1d4d9cbdc62',2,'UK',
111111,222222,12345678,'TEST COMPANY',
1.0000,11.0000,111,23.0000,11111.0000,
'True','False','True');

INSERT INTO [tbBOSS_OAccount]
           ([Licence] ,[OAccountID] ,[OAccountClientID] ,[Country]
,[BACSID] 
,[SortCode] ,[AccountNumber] ,[AccountName] ,[DailyLimit]
,[WeeklyLimit] 
,[VarPeriodDays] ,[VarPeriodLimit] ,[MonthlyLimit] ,[AllowPayments] 
,[AllowDebits] ,[Active])
             VALUES
(222222,'dc408d70-977b-4354-a6ec-9bec182e043f',1,'UK',
430639,832845,30989312,'TEST COMPANY',
20.0000,0.0000,5,0.0000,10000.0000,
'True','True','True');

INSERT INTO [tbCBMOAccount]
           ([Licence] ,[OAccountID] ,[OAccountClientID] ,[Country]
,[BACSID] 
,[SortCode] ,[AccountNumber] ,[AccountName] ,[DailyLimit]
,[WeeklyLimit] 
,[VarPeriodDays] ,[VarPeriodLimit] ,[MonthlyLimit] ,[AllowPayments] 
,[AllowDebits] ,[Active])
             VALUES
(111111,'33fe22d4-4dd5-48f6-8fb6-c1d4d9cbdc62',2,'GER'
,111111,222222,12345678,'TEST COMPANY',
1.0000,11.0000,111,23.0000,11111.0000,
'True','False','True');

 INSERT INTO [tbCBMOAccount]
           ([Licence] ,[OAccountID] ,[OAccountClientID] ,[Country]
,[BACSID] 
,[SortCode] ,[AccountNumber] ,[AccountName] ,[DailyLimit]
,[WeeklyLimit] 
,[VarPeriodDays] ,[VarPeriodLimit] ,[MonthlyLimit] ,[AllowPayments] 
,[AllowDebits] ,[Active])
             VALUES
(222222,'dc408d70-977b-4354-a6ec-9bec182e043f',1,'UK',
430639,832845,30989312,'TEST COMPANY',
20.0000,0.0000,5,0.0000,10000.0000,
'True','True','True');

SELECT * FROM [tbCBMOAccount]

SELECT * FROM [tbBOSS_OAccount]


CREATE TRIGGER [dbo].[trig_UpdateOriginatongAccounts]
   ON  [dbo].[tbCBMOAccount]
   AFTER UPDATE
AS 
BEGIN

Declare @OAccountID UniqueIdentifier
Declare @Country varchar(32)
Select @OAccountID = OAccountID , @Country = Country From Inserted

IF  UPDATE(Country)
 BEGIN
 UPDATE dbo.tbBOSS_OAccount
  SET Country = @Country
  WHERE OAccountID = @OAccountID
 END
END

SELECT * FROM [tbCBMOAccount]

SELECT * FROM [tbBOSS_OAccount]

UPDATE [dbo].[tbCBMOAccount]
SET Country = 'GDR'
WHERE Country = 'GER'


ALTER  TRIGGER [dbo].[trig_UpdateOriginatongAccounts]
   ON  [dbo].[tbCBMOAccount]
   AFTER UPDATE
AS 
BEGIN

IF  UPDATE(Country)
 BEGIN
 UPDATE t2
  SET Country = i.Country
  FROM dbo.tbBOSS_OAccount t2
  JOIN inserted i ON i.OAccountID = t2.OAccountID 
	AND t2.Country <> i.Country
 END
END

UPDATE [dbo].[tbCBMOAccount]
SET Country = 'GDR'
WHERE Country = 'GER'

UPDATE [dbo].[tbCBMOAccount]
SET Country = 'GER'
WHERE Country = 'GDR'

I don't get any errors with this, so I assume something is different
in your environment.

John
0
John
5/19/2010 5:23:19 PM
Hi John,

I'm going to stand in the silly corner. Your update trigger works fine. The 
error i was getting ( and still get ) is if i am in SQL Manager and editing 
the table that way. Lesson learnt, Thanks for the help

Pete

"John Bell" wrote:

> On Wed, 19 May 2010 08:27:01 -0700, Peter Newman
> <PeterNewman@discussions.microsoft.com> wrote:
> 
> I've updated the SQL so the inserts work!
> 
> CREATE TABLE [tbCBMOAccount](
> 	[Licence] [varchar](6) NOT NULL,
> 	[OAccountID] [uniqueidentifier] NOT NULL,
> 	[OAccountClientID] [int] NOT NULL,
> 	[Country] [varchar](32) NULL,
> 	[BACSID] [varchar](6) NOT NULL,
> 	[SortCode] [varchar](6) NOT NULL,
> 	[AccountNumber] [varchar](8) NOT NULL,
> 	[AccountName] [varchar](32) NOT NULL,
> 	[DailyLimit] [money] NULL,
> 	[WeeklyLimit] [money] NULL,
> 	[VarPeriodDays] [int] NULL,
> 	[VarPeriodLimit] [money] NULL,
> 	[MonthlyLimit] [money] NULL,
> 	[AllowPayments] [bit] NULL,
> 	[AllowDebits] [bit] NULL,
> 	[Active] [bit] NULL
> ) ON [PRIMARY]
> 
> CREATE TABLE [tbBOSS_OAccount](
> 	[Licence] [varchar](6) NOT NULL,
> 	[OAccountID] [uniqueidentifier] NOT NULL,
> 	[OAccountClientID] [int] NOT NULL,
> 	[Country] [varchar](32) NULL,
> 	[BACSID] [varchar](6) NOT NULL,
> 	[SortCode] [varchar](6) NOT NULL,
> 	[AccountNumber] [varchar](8) NOT NULL,
> 	[AccountName] [varchar](32) NOT NULL,
> 	[DailyLimit] [money] NULL,
> 	[WeeklyLimit] [money] NULL,
> 	[VarPeriodDays] [int] NULL,
> 	[VarPeriodLimit] [money] NULL,
> 	[MonthlyLimit] [money] NULL,
> 	[AllowPayments] [bit] NULL,
> 	[AllowDebits] [bit] NULL,
> 	[Active] [bit] NULL
> ) ON [PRIMARY]
> 
> 
> INSERT INTO [tbBOSS_OAccount]
>            ([Licence] ,[OAccountID] ,[OAccountClientID] ,[Country]
> ,[BACSID] 
> ,[SortCode] ,[AccountNumber] ,[AccountName] ,[DailyLimit]
> ,[WeeklyLimit] 
> ,[VarPeriodDays] ,[VarPeriodLimit] ,[MonthlyLimit] ,[AllowPayments] 
> ,[AllowDebits] ,[Active])
>              VALUES
> (111111,'33fe22d4-4dd5-48f6-8fb6-c1d4d9cbdc62',2,'UK',
> 111111,222222,12345678,'TEST COMPANY',
> 1.0000,11.0000,111,23.0000,11111.0000,
> 'True','False','True');
> 
> INSERT INTO [tbBOSS_OAccount]
>            ([Licence] ,[OAccountID] ,[OAccountClientID] ,[Country]
> ,[BACSID] 
> ,[SortCode] ,[AccountNumber] ,[AccountName] ,[DailyLimit]
> ,[WeeklyLimit] 
> ,[VarPeriodDays] ,[VarPeriodLimit] ,[MonthlyLimit] ,[AllowPayments] 
> ,[AllowDebits] ,[Active])
>              VALUES
> (222222,'dc408d70-977b-4354-a6ec-9bec182e043f',1,'UK',
> 430639,832845,30989312,'TEST COMPANY',
> 20.0000,0.0000,5,0.0000,10000.0000,
> 'True','True','True');
> 
> INSERT INTO [tbCBMOAccount]
>            ([Licence] ,[OAccountID] ,[OAccountClientID] ,[Country]
> ,[BACSID] 
> ,[SortCode] ,[AccountNumber] ,[AccountName] ,[DailyLimit]
> ,[WeeklyLimit] 
> ,[VarPeriodDays] ,[VarPeriodLimit] ,[MonthlyLimit] ,[AllowPayments] 
> ,[AllowDebits] ,[Active])
>              VALUES
> (111111,'33fe22d4-4dd5-48f6-8fb6-c1d4d9cbdc62',2,'GER'
> ,111111,222222,12345678,'TEST COMPANY',
> 1.0000,11.0000,111,23.0000,11111.0000,
> 'True','False','True');
> 
>  INSERT INTO [tbCBMOAccount]
>            ([Licence] ,[OAccountID] ,[OAccountClientID] ,[Country]
> ,[BACSID] 
> ,[SortCode] ,[AccountNumber] ,[AccountName] ,[DailyLimit]
> ,[WeeklyLimit] 
> ,[VarPeriodDays] ,[VarPeriodLimit] ,[MonthlyLimit] ,[AllowPayments] 
> ,[AllowDebits] ,[Active])
>              VALUES
> (222222,'dc408d70-977b-4354-a6ec-9bec182e043f',1,'UK',
> 430639,832845,30989312,'TEST COMPANY',
> 20.0000,0.0000,5,0.0000,10000.0000,
> 'True','True','True');
> 
> SELECT * FROM [tbCBMOAccount]
> 
> SELECT * FROM [tbBOSS_OAccount]
> 
> 
> CREATE TRIGGER [dbo].[trig_UpdateOriginatongAccounts]
>    ON  [dbo].[tbCBMOAccount]
>    AFTER UPDATE
> AS 
> BEGIN
> 
> Declare @OAccountID UniqueIdentifier
> Declare @Country varchar(32)
> Select @OAccountID = OAccountID , @Country = Country From Inserted
> 
> IF  UPDATE(Country)
>  BEGIN
>  UPDATE dbo.tbBOSS_OAccount
>   SET Country = @Country
>   WHERE OAccountID = @OAccountID
>  END
> END
> 
> SELECT * FROM [tbCBMOAccount]
> 
> SELECT * FROM [tbBOSS_OAccount]
> 
> UPDATE [dbo].[tbCBMOAccount]
> SET Country = 'GDR'
> WHERE Country = 'GER'
> 
> 
> ALTER  TRIGGER [dbo].[trig_UpdateOriginatongAccounts]
>    ON  [dbo].[tbCBMOAccount]
>    AFTER UPDATE
> AS 
> BEGIN
> 
> IF  UPDATE(Country)
>  BEGIN
>  UPDATE t2
>   SET Country = i.Country
>   FROM dbo.tbBOSS_OAccount t2
>   JOIN inserted i ON i.OAccountID = t2.OAccountID 
> 	AND t2.Country <> i.Country
>  END
> END
> 
> UPDATE [dbo].[tbCBMOAccount]
> SET Country = 'GDR'
> WHERE Country = 'GER'
> 
> UPDATE [dbo].[tbCBMOAccount]
> SET Country = 'GER'
> WHERE Country = 'GDR'
> 
> I don't get any errors with this, so I assume something is different
> in your environment.
> 
> John
> .
> 
0
Utf
5/19/2010 6:50:01 PM
Reply:

Similar Artilces:

encoding problem in Outlook 2007 importing Outlook 2000 personal folders
I use Outlook 2007 to import the pst file of Outlook 2000. The Chinese characters in the subject became unreadable but it's ok to display in message body. In Outlook 2000, there's no problem to display the Chinese characters in the subject. What's wrong with Outlook 2007? What setttings should I change to fix the problem? Thanks. cyl <u8526505@gmail.com> wrote: > I use Outlook 2007 to import the pst file of Outlook 2000. The Chinese > characters in the subject became unreadable but it's ok to display in > message body. In Outlook 2000, there's no problem t...

Problem shortcuts in Word
Hello, I work with Word 2008. In the beginning there is no problem, but suddenly i have problem with shortcuts. When i want to do "cmd c" it make an other shortcuts same thing for "cmd s", "cmd v" and surly some other. But when i write there is no problem "c" is "c"... If somebody have an idea please help me. Thank you. Unless you indicate your specific update level of Office as well as OS X & describe what the keystrokes *are* doing contrary to what you expect there isn't much we can offer. -- Regards |:>)...

Outlook 2003 PST problems
I just installed Office 2003 professional on a new computer & am having problems with outlook. I copied my Outlook.pst file into the appropriate directory & although it is recognized the calendar is not working properly. I cannot attach any of the colored labels to my appointments - so everything is pretty much in black & white. I totally uninstalled & reinstalled office... first removing my pst file... but there appears to be an ini file... or something that is retaining some information... does anyone know of a fix for the labels... or how to TOTALLY uninstall office (so I...

Updated KB: Thin Line Appears and Is Printed Outside Object with Border and Fill Applied
http://support.microsoft.com/default.aspx?scid=kb;en-us;290911 -- Brian Kvalheim Microsoft Publisher MVP http://www.mvps.org/publisher This posting is provided "AS IS" with no warranties, and confers no rights. ...

Problem with Authentication
The CRM application was running smooth. But now I can't logging. It ask my credentials and than return a 401.1 error. I had already reinstalled the CRM server but it won't work. I'm accessing it in the same local network. I'm running CRM 3.0 in SBS. Best regards, Erico Hi When you open a Internet explorer and type the name of the CRM server, it prompts for user and password ? Are you logged in with domain credentials ( AD user ) ? If you are, so add url of the crm server in Local intranet. Enable "Automatic logon only in intranet", this is under security,c...

Vista blue screen problem...please help!
I've seen some other threads, here are the dump files: 'RapidShare: 1-CLICK Web hosting - Easy Filehosting' (http://rapidshare.com/files/347103149/Minidump.rar.html) Please, help me out here. -- ceVil It might be better to know at what point the bsod occurs and what the actual err.msg given is "ceVil" <guest@unknown-email.com> wrote in message news:0b9a0c60b90bf6c0be88bda30f2820ab@nntp-gateway.com... > > I've seen some other threads, here are the dump files: > 'RapidShare: 1-CLICK Web hosting - Easy Filehosting' > (...

calendar problem
Dear all, I am using both mail and calendar function in outlook 2002. Everything is ok until recently, when I try to make a new appointment or checking any old appointments, an error message "out of memory or resource, try to close some windows..." pop up. The mail function is still ok. Can anyone tell me how can I solve this? I have already installed sp2. The version of office is the professional one with frontpage. Thanks. tp ...

problem #4
Hi, I am facing this new problem... i.e a user has installed Office 2003 on win2k prof platform.. whenever she is working on a outlook.. she gets a Microsoft error message... saying to send the report or don't send the report... Usually I have seen this problem in IE... But for outlook it is new to me... after clicking on send or don't send report.. the outlook closes. And there are no IE open..... and if atall it is open.. the IE does not close... Need your help regards, KAH What is logged to the Event Viewer regarding this? Try one of the following already; http://www.howt...

95 and 97 problem
For some unknown reason there is Excel 95 and 97 on this pc.When i double click on an Excel file, the pc defaults to the 95 program (Which incidently doesn't work properly). So my question is how do i get the pc to default to 97. Cheers Craig Craig, Have a look at Windows Help. From Windows 2000 Help (Index under Programs Associating with File Types) To change which program starts when you open a file In My Computer or Windows Explorer, on the View menu, click Folder Options. Click the File Types tab. In the list of file types, click the one you want to change. Click Edit. In Act...

Subtotal doesn't update
-------------------------------------------------------------------------------- Hi, I have a spreadsheet with subtotals on and we have a problem inserting new rows. I thought that when a row was inserted above the subtotal row, the subtotal would update automatically - but it doesn't. Basically, we need to add news rows on a regular basis above the subtotal row and update the subtotal. This is what we want to happen - Before 2 3 4 0 2 5 2 2 0 4 7 9 (Subtotal) After 2 3 4 0 2 5 2 2 0 0 0 2 (new row) 4 7 11 (Subtotal) Hope this makes sense! How can we get the subtotals to update e...

sync problems
I have all of my info on an HP Ipac. I had to reformat the hard drive and I did not make a back-up file. I am trying to move everything back to the computer but it says there is an error with syncing it. How can I move everything from the PDA back to Outlook again? Please help- thanks ...

Date problem
I can't believe there's no post on this, but I can't find it. I've tried the solutions I could find (see below) and still get the error: "The expression is typed incorrectly or it is too complex to be evaluated. For example....Try simplifying...." I have a date field, formatted as Date/Time, General Date, default Value = Now(). I like having the date and the time - in case we need it. I want to run a report on calls taken just for one day. I have CallDate: CallDateAndTime in my query, with "criteria" as Between ([Forms]![frmReports]![txtStartDate]) A...

Installation Problem!!
I am having a problem installing MSCRM 1.2. My configuration consists of a Domain Controller running Win 2000 Server SP4 . I am trying to install MSCRM on a member of the domain running Windows 2000 Server SP4. There is a DNS Server on the DC which is integrated with AD, and a SQL Server 2000 running on the Win 2K machine. The install errors out when CRM creates the root business with the message: Setup was unable to install Microsoft CRM Server. Setup was unable to provision your organization. Setup was unable to create the root business. The configuration of ASP.NET seems to be ok, the pr...

Office 2007 - HELP
I have used Word for many years and love it - not too thrilled with the changes in Office 2007, but my bigger issue is with Publisher 2007. I have an image that I have used in Word just fine - meaning, the on screen colors are correct and when printed the colors are correct. Someone sent me a Publisher file and asked me to add the image - I did this - now the image is messed up - the colors are correct on screen, but when printed FROM MY computer the image colors are all wrong - but if I transport the file to a different computer it prints fine I have reinstalled Office - no good Any id...

Resource editor problems
Hello all, Under VC++ 7.1... Please consider these two lines: IDC_ARROW_ADD_CANCEL DISCARDABLE "res\\cur00004.cur" IDC_ARROW_ADD_CANCEL CURSOR DISCARDABLE "res\\cur00004.cur" The second is a hand-edited change to the first. Both will compile just fine in the resource compiler. However, after I use a text editor to make this change, I cannot open the .rc file in the resource editor. The resource editor issues the following error open attempting to open the .rc file: error RC2135 : file not found: CURSOR Can anyone shed light on what's going on here? Thanks, Dave ...

Directory Replication Problem #2
Hi, recently my Exchange Server directory database receives changes from other servers but does not send out its own changes. Check from the knowledge base, to correct the USN discrepancy need to use Authrest.exe (need to amke changes for about 100+ users). Does anybody know where to get this file 'Authrest.exe' for exchange server 5.5? Exchange server 5.5 CD only provide this file for ver.4.0. Have anyone use it before? Regards, "Sharon Tan" <sharon_tansk@yahoo.com.sg> wrote: >Hi, recently my Exchange Server directory database >receives changes from ot...

Problems Creating a disclaimer in Exchange 2003
I am trying to create a server based disclaimer that will stamp all of my smtp emails going to the internet. I have been following KB article 317680 with no luck. I get an error like the one below. Binding Display Name Specified: smtpscriptinghost ** Registration Failed ** Err.Number (HRESULT) = 0x1AD Err.Description = ActiveX component can't create object ProgID = cdo.ss_smtponarrival COM Category = {FF3CAA23-00B9-11d2-9DFB-00C04FA322BA} Corresponding Event = onarrival ** Have you registered your sink COM class on this machine? I am puttin...

User Defined Variables in MS query cause a problem to import data
If I use user defined variables in MS query to import data into excel sheet, the query executes but no data gets imported into the excel sheet. If I get rid of the variable the results can be imported into the Excel sheet. ...

Cell Format
I have a spreadsheet with cells that I'm trying to type the date into. I type in the date exactly like this: 09/26/03. When I hit "enter" MS Excel displays "37890" in the cell. No matter what I do I can't the cell to show: 09/26/03. I think that somehow I need to "strip" the cell of what it's original format is, but I can't figure out how. I try to use the Date option in the format cell screen but it doesn't work. Curtis I tried this and it didn't work. I also tried the "Text to columns" feature. No success. >...

outlook web access redirect problem
hi, I have been trying to redirect outlook web access (OWA) from the IIS root folder to the /exchange folder. I have followed the instructions on going into the web site and redirecting to a folder but I just get the following URL when I browse to the root of my webserver : http://webmail/exchange/exchange - it's like it's doubling up somehow. I have it set to : A redirection to a URL redirect to: /exchange client will be sent to: A directory below URL entered I just can't work it out... any ideas... cheers Baronne Which instructions did you follow? I wrote the below articl...

Problem after sorting
In my Excel 2007 workbook I have two sheets The "Master Names" sheet has columns: (A) First|(B) Last (C) First (B) Last (concatenated) The "Selected Names" sheet contains (A) cells which link to selected (C) First Last (concatenated) cells in the Master Names sheet. Everything links and displays fine except when I add names to the bottom of the Master Names sheet and then sort using (B) Last name column. When I do this I get a 0 (zero) in the (A) First Second cells in the Selected names sheet and other cells in this sheet have the wrong name. Obviously I am doin...

IF AND problem
Need to isolate problems on 1600 rows Worksheet Row Dept Hrs. Dollars A5 120500 600 9000 A6 120600 400 8000 A7 130600 240 A8 130400 160 A9 140600 320 6400 A10 140200 100 2000 A11 140600 240 (Found out the dept has 2 leading blanks) Isolate problems on 0600 criteria pointing out when b has value and c = 0. OK if both are 0. A7 and A11 should stand out.. =IF((MID(A1,5,4)="0600")=AND(b1>=0=AND(c1>=0)),"NO","ok") Does this work for you: =IF(RIGHT(A...

Chase Auto Loan not updated
I've had a Chase credit card for a while now and I use online updates in Money Plus Deluxe to update the register. When I took out a Chase auto loan this spring, Money automatically added an account for it. But it doesn't list it as a loan and it shows the balance as positive, not a negative or owed balance. It only shows the original loan balance and none of my payments ever show-up in the register. If I try to delete the account it says it has to delete both the auto loan and credit card account. I can't get rid of just one. How can I fix the account so that it functions correctl...

Problem SHAppBarMessage
Hi, I am creating an application bar which shall be displayed always at the top of the screen to show the user certain alerts. I am using the commands SHAppBarMessage(ABM_NEW, &abd); SHAppBarMessage(ABM_QUERYPOS, &abd); SHAppBarMessage(ABM_SETPOS, &abd); SetWindowPos(NULL, abd.rc.left, abd.rc.top, abd.rc.right - abd.rc.left, iClientHeight, SWP_NOACTIVATE); Works fine. The problem begins, when I start moving the taskbar. I am catching the event OnSettingChange and my application bar is resizing/moving correctly. BUT: If the taskbar moves to the top and my application bar ...

Lookup() problem
I set up two lists, 200 employees names and 200 employee numbers. A couple of the numbers match the correct names but the rest of the numbers all pull the same employee name. =LOOKUP(C4,List!C4:C204,List!B4:B204) The first time I tried it seemed to pull the correct matching names and numbers then kabui it gets stuck. I tried deleting the entire lists cells and all and starting over but same thing. Anyone ever run into this before? Wayman Don't forget! When using Lookup(), the vector (second argument) must be sorted in ascending order. If it's not, you'll get what you ca...