Do I trust what my SMS agents are telling me?

This XML update that was originally released Oct 10th and then updated Oct
19th has got me .
I have roughly 600 SMS clients, 150 of which would be XP machines and 450
Windows 2000.
Right now I have a little over 400 SMS clients (a mix of XP and 2000)
requesting 924191 which SMS describes as a Security update for Windows (but
updates the XML parser and Core Services), and another 532 clients
requesting 925672, described as MSXML4.0 SP2 Security update.
Obviously, I've got a lot of clients requesting both (or requesting the same
update under two different names'). Is this simply because they have
multiple versions of these XML components on their machines, all of which
need updating? These updates aren't going to stomp on each other? Should I
be deploying both?
Advice for an SMS admin (not an XML guy) appreciated.
SMS 2003 SP1 on W2K3 SP1> Obviously, I've got a lot of clients requesting both (or requesting the
> same update under two different names').
The update released for MSXML3 was seperate from the update released for
MSXML4, even though the issue that each of these updates fixed was the same.

> Is this simply because they have multiple versions of these XML components
> on their machines, all of which need updating?
Yes

>These updates aren't going to stomp on each other? Should I be deploying
>both?
No they are not going to stomp on each other, yes, deploy both.
"Phil McNeill" <philmcneill@.NOSPAM4MEhydroottawa.com> wrote in message
news:uhwgP$G%23GHA.1224@.TK2MSFTNGP04.phx.gbl...
> This XML update that was originally released Oct 10th and then updated Oct
> 19th has got me .
> I have roughly 600 SMS clients, 150 of which would be XP machines and 450
> Windows 2000.
> Right now I have a little over 400 SMS clients (a mix of XP and 2000)
> requesting 924191 which SMS describes as a Security update for Windows
> (but updates the XML parser and Core Services), and another 532 clients
> requesting 925672, described as MSXML4.0 SP2 Security update.
> Obviously, I've got a lot of clients requesting both (or requesting the
> same update under two different names'). Is this simply because they
> have multiple versions of these XML components on their machines, all of
> which need updating? These updates aren't going to stomp on each other?
> Should I be deploying both?
> Advice for an SMS admin (not an XML guy) appreciated.
> SMS 2003 SP1 on W2K3 SP1
>
>|||Thanks for the reply Alex. I was fairly sure that was the case, but already
deployed to my test group prior to the Oct 19th update that added updates
for Windows 2000. I'll deploy the additional updates to that group and see
how it goes.
Thanks again,
Phil
"Alex Krawarik[MSFT]" <alexkr@.microsoft.com> wrote in message
news:eC2DC0I%23GHA.3456@.TK2MSFTNGP02.phx.gbl...
> The update released for MSXML3 was seperate from the update released for
> MSXML4, even though the issue that each of these updates fixed was the
> same.
>
> Yes
>
> No they are not going to stomp on each other, yes, deploy both.
>

Do I need to examine locking for this?

I'm using SQL Server 2000.
I have a situation where I need to select a variable amount of records
for a specific type_id and status_id and update some fields. Now
multiple users are going to be using this at the same time, and I want
to prevent multiple users from updating the same records.
Here's what I have so far:
CREATE TABLE [dbo].[label] (
[label_id] [int] NOT NULL ,
[label_status_id] [smallint] NOT NULL ,
[label_type_id] [smallint] NOT NULL ,
[master_job_id] [int] NULL,
[label_reprint_index] [int] NULL
) ON [PRIMARY]
CREATE PROCEDURE dbo.z_sp_AssignLabelsToJob_no_cursor
(
@.lt_id bigint,
@.mj_id bigint,
@.num_labels bigint
)
AS
declare @.t table
(
label_id int Primary Key
)
set rowcount @.numlabels
insert into @.t
select
label_id
from
label
where
label_type_id = @.lt_id
and label_status_id = 1
set rowcount 0
declare @.count int
set @.count = 0
update
label
set
master_job_id = @.mj_id,
label_status_id = 2,
@.count = label_reprint_index = @.count + 1
where
label_id in (
select label_id
from @.t
)
I have to use a FIFO update for the labels, so do I need to build in
protection to keep multiple users from updating the same records?1) Can't you combine your sproc into a single statement instead of using a
table variable?
2) If you do the above, a simple begin tran/update/error
check/rollback-commit sequence will ensure each record only gets updated by
a single process (which ever fires off first). This could lead to
contention if you are doing large ranges of rows.
3) Timestamping is another mechanism used to ensure rows are not changed
underneath you between your initial grab and the actual update.
TheSQLGuru
President
Indicium Resources, Inc.
"Jason Lepack" <jlepack@.gmail.com> wrote in message
news:1181566796.016917.115820@.w5g2000hsg.googlegroups.com...
> I'm using SQL Server 2000.
> I have a situation where I need to select a variable amount of records
> for a specific type_id and status_id and update some fields. Now
> multiple users are going to be using this at the same time, and I want
> to prevent multiple users from updating the same records.
> Here's what I have so far:
> CREATE TABLE [dbo].[label] (
> [label_id] [int] NOT NULL ,
> [label_status_id] [smallint] NOT NULL ,
> [label_type_id] [smallint] NOT NULL ,
> [master_job_id] [int] NULL,
> [label_reprint_index] [int] NULL
> ) ON [PRIMARY]
> CREATE PROCEDURE dbo.z_sp_AssignLabelsToJob_no_cursor
> (
> @.lt_id bigint,
> @.mj_id bigint,
> @.num_labels bigint
> )
> AS
> declare @.t table
> (
> label_id int Primary Key
> )
> set rowcount @.numlabels
> insert into @.t
> select
> label_id
> from
> label
> where
> label_type_id = @.lt_id
> and label_status_id = 1
> set rowcount 0
>
> declare @.count int
> set @.count = 0
> update
> label
> set
> master_job_id = @.mj_id,
> label_status_id = 2,
> @.count = label_reprint_index = @.count + 1
> where
> label_id in (
> select label_id
> from @.t
> )
> I have to use a FIFO update for the labels, so do I need to build in
> protection to keep multiple users from updating the same records?
>|||Could you plese explain how I would use timestamping? I've looked it
up, but I'm not quite sure how to go about it.
Quote:
2) If you do the above, a simple begin tran/update/error
check/rollback-commit sequence will ensure each record only gets
updated by
a single process (which ever fires off first). This could lead to
contention if you are doing large ranges of rows.
So if I just use the update statement then if multiple users are
updating 300000 records at a time, I may run into the situation where
they are trying to update the same pages right?
On Jun 11, 9:39 am, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
> 1) Can't you combine your sproc into a single statement instead of using a
> table variable?
> 2) If you do the above, a simple begin tran/update/error
> check/rollback-commit sequence will ensure each record only gets updated b
y
> a single process (which ever fires off first). This could lead to
> contention if you are doing large ranges of rows.
> 3) Timestamping is another mechanism used to ensure rows are not changed
> underneath you between your initial grab and the actual update.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Jason Lepack" <jlep...@.gmail.com> wrote in message
> news:1181566796.016917.115820@.w5g2000hsg.googlegroups.com...
>
>
>
>
>
>
>
>
>
>
>
> - Show quoted text -|||I'm slowly renovating this database from using cursors to using set
based logic.
The reason for the table variable is that user parameters
(workstation, username, etc) that are passed to this stored procedure
must be used to update a log table because Windows Domain Security is
not used and if I just used master..sysprocesses.loginname every
transaction would have the username "aspnet"
If I were to begin a transaction and select the records into @.t using
the UPDLOCK hint would that then hold the lock on those records until
the update of the rows was done?
I expect this transaction to take about 1-2 seconds and the amount of
transactions will not be large, so I don't expect too much contention.
Cheers,
Jason Lepack
On Jun 11, 10:00 am, Jason Lepack <jlep...@.gmail.com> wrote:
> Could you plese explain how I would use timestamping? I've looked it
> up, but I'm not quite sure how to go about it.
> Quote:
> 2) If you do the above, a simple begin tran/update/error
> check/rollback-commit sequence will ensure each record only gets
> updated by
> a single process (which ever fires off first). This could lead to
> contention if you are doing large ranges of rows.
> So if I just use the update statement then if multiple users are
> updating 300000 records at a time, I may run into the situation where
> they are trying to update the same pages right?
> On Jun 11, 9:39 am, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> - Show quoted text -|||1) Yes, begin tran, selecting records using updlock/holdlock would prevent
other from getting those records for change until after your commit
2) You may be surprised about performance if you have 300K-rows-per-updates
going on.
TheSQLGuru
President
Indicium Resources, Inc.
"Jason Lepack" <jlepack@.gmail.com> wrote in message
news:1181574986.867864.46950@.p47g2000hsd.googlegroups.com...
> I'm slowly renovating this database from using cursors to using set
> based logic.
> The reason for the table variable is that user parameters
> (workstation, username, etc) that are passed to this stored procedure
> must be used to update a log table because Windows Domain Security is
> not used and if I just used master..sysprocesses.loginname every
> transaction would have the username "aspnet"
> If I were to begin a transaction and select the records into @.t using
> the UPDLOCK hint would that then hold the lock on those records until
> the update of the rows was done?
> I expect this transaction to take about 1-2 seconds and the amount of
> transactions will not be large, so I don't expect too much contention.
> Cheers,
> Jason Lepack
> On Jun 11, 10:00 am, Jason Lepack <jlep...@.gmail.com> wrote:
>|||1) Add a timestamp to the table. Then on your grab you can get the
timestamp and do a comparison during the update. This will allow others to
grab the row and update it underneath you, but does allow you to NOT update
it twice if that is the desired intent. Most often used for disconnected
processing of one or a few rows at a time.
2) Yes, single statement activity will immeditately take locks on the
updated rows/pages (or even escalate to a table lock). Indexes will be
locked as well.
TheSQLGuru
President
Indicium Resources, Inc.
"Jason Lepack" <jlepack@.gmail.com> wrote in message
news:1181570421.325108.57400@.n4g2000hsb.googlegroups.com...
> Could you plese explain how I would use timestamping? I've looked it
> up, but I'm not quite sure how to go about it.
> Quote:
> 2) If you do the above, a simple begin tran/update/error
> check/rollback-commit sequence will ensure each record only gets
> updated by
> a single process (which ever fires off first). This could lead to
> contention if you are doing large ranges of rows.
> So if I just use the update statement then if multiple users are
> updating 300000 records at a time, I may run into the situation where
> they are trying to update the same pages right?
>
> On Jun 11, 9:39 am, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
>

Do I need to examine locking for this?

I'm using SQL Server 2000.
I have a situation where I need to select a variable amount of records
for a specific type_id and status_id and update some fields. Now
multiple users are going to be using this at the same time, and I want
to prevent multiple users from updating the same records.
Here's what I have so far:
CREATE TABLE [dbo].[label] (
[label_id] [int] NOT NULL ,
[label_status_id] [smallint] NOT NULL ,
[label_type_id] [smallint] NOT NULL ,
[master_job_id] [int] NULL,
[label_reprint_index] [int] NULL
) ON [PRIMARY]
CREATE PROCEDURE dbo.z_sp_AssignLabelsToJob_no_cursor
(
@.lt_id bigint,
@.mj_id bigint,
@.num_labels bigint
)
AS
declare @.t table
(
label_id int Primary Key
)
set rowcount @.numlabels
insert into @.t
select
label_id
from
label
where
label_type_id = @.lt_id
and label_status_id = 1
set rowcount 0
declare @.count int
set @.count = 0
update
label
set
master_job_id = @.mj_id,
label_status_id = 2,
@.count = label_reprint_index = @.count + 1
where
label_id in (
select label_id
from @.t
)
I have to use a FIFO update for the labels, so do I need to build in
protection to keep multiple users from updating the same records?
1) Can't you combine your sproc into a single statement instead of using a
table variable?
2) If you do the above, a simple begin tran/update/error
check/rollback-commit sequence will ensure each record only gets updated by
a single process (which ever fires off first). This could lead to
contention if you are doing large ranges of rows.
3) Timestamping is another mechanism used to ensure rows are not changed
underneath you between your initial grab and the actual update.
TheSQLGuru
President
Indicium Resources, Inc.
"Jason Lepack" <jlepack@.gmail.com> wrote in message
news:1181566796.016917.115820@.w5g2000hsg.googlegro ups.com...
> I'm using SQL Server 2000.
> I have a situation where I need to select a variable amount of records
> for a specific type_id and status_id and update some fields. Now
> multiple users are going to be using this at the same time, and I want
> to prevent multiple users from updating the same records.
> Here's what I have so far:
> CREATE TABLE [dbo].[label] (
> [label_id] [int] NOT NULL ,
> [label_status_id] [smallint] NOT NULL ,
> [label_type_id] [smallint] NOT NULL ,
> [master_job_id] [int] NULL,
> [label_reprint_index] [int] NULL
> ) ON [PRIMARY]
> CREATE PROCEDURE dbo.z_sp_AssignLabelsToJob_no_cursor
> (
> @.lt_id bigint,
> @.mj_id bigint,
> @.num_labels bigint
> )
> AS
> declare @.t table
> (
> label_id int Primary Key
> )
> set rowcount @.numlabels
> insert into @.t
> select
> label_id
> from
> label
> where
> label_type_id = @.lt_id
> and label_status_id = 1
> set rowcount 0
>
> declare @.count int
> set @.count = 0
> update
> label
> set
> master_job_id = @.mj_id,
> label_status_id = 2,
> @.count = label_reprint_index = @.count + 1
> where
> label_id in (
> select label_id
> from @.t
> )
> I have to use a FIFO update for the labels, so do I need to build in
> protection to keep multiple users from updating the same records?
>
|||I'm slowly renovating this database from using cursors to using set
based logic.
The reason for the table variable is that user parameters
(workstation, username, etc) that are passed to this stored procedure
must be used to update a log table because Windows Domain Security is
not used and if I just used master..sysprocesses.loginname every
transaction would have the username "aspnet"
If I were to begin a transaction and select the records into @.t using
the UPDLOCK hint would that then hold the lock on those records until
the update of the rows was done?
I expect this transaction to take about 1-2 seconds and the amount of
transactions will not be large, so I don't expect too much contention.
Cheers,
Jason Lepack
On Jun 11, 10:00 am, Jason Lepack <jlep...@.gmail.com> wrote:
> Could you plese explain how I would use timestamping? I've looked it
> up, but I'm not quite sure how to go about it.
> Quote:
> 2) If you do the above, a simple begin tran/update/error
> check/rollback-commit sequence will ensure each record only gets
> updated by
> a single process (which ever fires off first). This could lead to
> contention if you are doing large ranges of rows.
> So if I just use the update statement then if multiple users are
> updating 300000 records at a time, I may run into the situation where
> they are trying to update the same pages right?
> On Jun 11, 9:39 am, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
>
>
>
>
>
>
>
>
>
>
> - Show quoted text -
|||1) Yes, begin tran, selecting records using updlock/holdlock would prevent
other from getting those records for change until after your commit
2) You may be surprised about performance if you have 300K-rows-per-updates
going on.
TheSQLGuru
President
Indicium Resources, Inc.
"Jason Lepack" <jlepack@.gmail.com> wrote in message
news:1181574986.867864.46950@.p47g2000hsd.googlegro ups.com...
> I'm slowly renovating this database from using cursors to using set
> based logic.
> The reason for the table variable is that user parameters
> (workstation, username, etc) that are passed to this stored procedure
> must be used to update a log table because Windows Domain Security is
> not used and if I just used master..sysprocesses.loginname every
> transaction would have the username "aspnet"
> If I were to begin a transaction and select the records into @.t using
> the UPDLOCK hint would that then hold the lock on those records until
> the update of the rows was done?
> I expect this transaction to take about 1-2 seconds and the amount of
> transactions will not be large, so I don't expect too much contention.
> Cheers,
> Jason Lepack
> On Jun 11, 10:00 am, Jason Lepack <jlep...@.gmail.com> wrote:
>
|||1) Add a timestamp to the table. Then on your grab you can get the
timestamp and do a comparison during the update. This will allow others to
grab the row and update it underneath you, but does allow you to NOT update
it twice if that is the desired intent. Most often used for disconnected
processing of one or a few rows at a time.
2) Yes, single statement activity will immeditately take locks on the
updated rows/pages (or even escalate to a table lock). Indexes will be
locked as well.
TheSQLGuru
President
Indicium Resources, Inc.
"Jason Lepack" <jlepack@.gmail.com> wrote in message
news:1181570421.325108.57400@.n4g2000hsb.googlegrou ps.com...
> Could you plese explain how I would use timestamping? I've looked it
> up, but I'm not quite sure how to go about it.
> Quote:
> 2) If you do the above, a simple begin tran/update/error
> check/rollback-commit sequence will ensure each record only gets
> updated by
> a single process (which ever fires off first). This could lead to
> contention if you are doing large ranges of rows.
> So if I just use the update statement then if multiple users are
> updating 300000 records at a time, I may run into the situation where
> they are trying to update the same pages right?
>
> On Jun 11, 9:39 am, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
>

do anyone have an idea?

Hi there,

I have number of tasks in my control flow most of them are execute sql task. I want to update one of the column in my table when anyone of the task in the control get fails?

Please let me know if anyone have an idea how to do this.

Thanks and Regards

I think 'event handlers' can do that for you. I dont have specific examples now; but this forum has a lot of info on that.|||

Hi Salas,

You are right.I have done it by using event handler.

Thanks a lot.

Do a lot of linked tables cause block?

Hello, everyone:
There are a lot of Access and Excel tables linked to my SQL Server (SQL2K SP3 on W2K). The end users update those likned tables. I am wondering if there is the block problem. If yes, how to prevent that? Thanks.
ZYTNo, It should not cause any problems. How did you bring it into sql2k

DNN DAL SqlDataProvider Passing NULL to SQL Stored Procedure

Hello,

I'm trying to pass a null object to a stored procedure to update a SQL Table boolean field with a null value. My SQL Table boolean column allows nulls.

I'm using an InfoObject which has several properties all corresponding to fields in the SQL Table. One of those fields is a boolean. I create an instance of the InfoObject in my code and assigns values to the various properties. The boolean property in question (call it InfoOjbect.BooleanProperty) is not assigned anything. I then call my StoredProcedure passing the InfoObject to it (using the DotNetNuke DAL architecture) and the final result is the Table's boolean column is populated with a 0 and not a NULL. If I explicitly define the InfoObject.BooleanProperty = null.nullboolean before passing it to the Stored Procedure, the same thing happens. How do I pass a null to the SQL database for a boolean field? I've tried making InfoObject.BooleanProperty = dbnull.value but it won't let me do this saying "dbnull cannot be converted to a boolean." Do I have to explicitly create my InfoObject properties to allow for a null to be assigned to it?

Any help would be greatly appreciated. I'm using the DotNetNuke DAL architecture passing my InfoObject through a dataprovider to the sqldataprovider which calls the SQL Stored Procedure to add the new record to the Table.

Thanks in advance for any help.Please help?!|||The issue was with my InfoObject construction. DNN Core Team provided the solution. You can see it athttp://www.dotnetnuke.com/Community/ForumsDotNetNuke/tabid/795/forumid/118/threadid/41618/threadpage/3/scope/posts/Default.aspx

DML against remote tables (MSSQL to DB2)

I created a linked server to a DB2 database and I can pull data fine, but when I try to insert/update/delete it tells me "SQL0471N Invocation of routine "SYSIBM.SQLTABLES" failed due to reason "00E7900C"" when trying: DELETE FROM DB2LinkedServer..SPACENAME.TABLENAME

I believe I need to send a clear string to DB2 that doesn't get compiled on the sql server side. Is there something like openquery that I can use for DML statements in SQL Server?Upon further review I found this site: http://support.microsoft.com/kb/270119/EN-US/

It shows how to use openquery to execute DML statements. nifty.

divide by zero error

Hi, I have a query like this. don't look at from part i have a problem
with the SET part.
UPDATE DSREA
Set HostAmt = ROUND(CONVERT(MONEY,(((CONVERT(FLOAT, DSREA.HostAmt)) /
ISNULL(ER1.ExchangeRate,1)) * ISNULL(ER2.ExchangeRate,0))), 4) * CASE
DSREA.DebitFlg WHEN 1 THEN 1
ELSE -1
END
FROM DW_Source_RAS_Expense_Addl DSREA(NOLOCK)
INNER JOIN #ExchangeRates ER2(NOLOCK) on DSREA.HostCurrencyCd =
ER2.CurrencyCd
INNER JOIN
(SELECT Currenycd, MAX(ExchangeRateDate) As ExchangeRateDate
FROM ExchangeRates ER ,DW_Source_RAS_Expense_Addl
DSREA(NOLOCK) Where ER.ExchangeRateDate <= DSREA.lockdt
GROUP BY ER.Currenycode
) T2
ON T2.ExchangeRateDate = ER2.ExchangeRateDate AND T2.CurrencyCd =
ER2.CurrencyCd
E.g. DSREA.hostamt is 500
ER1.Exchchangerate is 50
and Er2.echangerate is 80
Now what we are doing is (500 / 50) * 80 = 800
Suppose ER2.Exchnagerate is 0 then (500/50) * 0 = 0
Now the problem is.. the #Exchangerate table is modified. now it
contains the reciprocals of the orinal value for e.g
50 contains 1/50 i.e. 0.02
and 80 contains 1/80 0.0125
So i modified my query to
Set HostAmt = ROUND(CONVERT(MONEY,(((CONVERT(FLOAT, DSREA.HostAmt)) *
ISNULL(ER1.ExchangeRate,1)) / ISNULL(ER2.ExchangeRate,1))), 4) * CASE
DSREA.DebitFlg WHEN 1 THEN 1
ELSE -1
END
Now everything is working fine except when Er2.exchangrate is 0. if 0
then
(500 * 0.02) / 0
Now i have two problems..
1.It is giving divide by 0 error
2.My Host amount should come as 0 if Er2.exchangerate is 0
How do i achieve it.
Can anyone help me with this ?
Regards,
RajeevHi, Rajeev
Instead of:
[...] SET x=a/b [...]
you can use:
[...] SET x=ISNULL(a/NULLIF(b,0),0) [...]
Razvan|||hI
DECLARE @.d INT
SET @.d=0
SELECT (500 / 50) * CASE WHEN @.d =0 THEN 1 ELSE @.d END
As well you can check fro NULL's
"Rajeev" <rajeev.rajput@.gmail.com> wrote in message
news:1150272582.451330.187380@.h76g2000cwa.googlegroups.com...
> Hi, I have a query like this. don't look at from part i have a problem
> with the SET part.
>
> UPDATE DSREA
> Set HostAmt = ROUND(CONVERT(MONEY,(((CONVERT(FLOAT, DSREA.HostAmt)) /
> ISNULL(ER1.ExchangeRate,1)) * ISNULL(ER2.ExchangeRate,0))), 4) * CASE
> DSREA.DebitFlg WHEN 1 THEN 1
> ELSE -1
> END
> FROM DW_Source_RAS_Expense_Addl DSREA(NOLOCK)
> INNER JOIN #ExchangeRates ER2(NOLOCK) on DSREA.HostCurrencyCd =
> ER2.CurrencyCd
> INNER JOIN
> (SELECT Currenycd, MAX(ExchangeRateDate) As ExchangeRateDate
> FROM ExchangeRates ER ,DW_Source_RAS_Expense_Addl
> DSREA(NOLOCK) Where ER.ExchangeRateDate <= DSREA.lockdt
> GROUP BY ER.Currenycode
> ) T2
> ON T2.ExchangeRateDate = ER2.ExchangeRateDate AND T2.CurrencyCd =
> ER2.CurrencyCd
> E.g. DSREA.hostamt is 500
> ER1.Exchchangerate is 50
> and Er2.echangerate is 80
> Now what we are doing is (500 / 50) * 80 = 800
> Suppose ER2.Exchnagerate is 0 then (500/50) * 0 = 0
> Now the problem is.. the #Exchangerate table is modified. now it
> contains the reciprocals of the orinal value for e.g
> 50 contains 1/50 i.e. 0.02
> and 80 contains 1/80 0.0125
> So i modified my query to
> Set HostAmt = ROUND(CONVERT(MONEY,(((CONVERT(FLOAT, DSREA.HostAmt)) *
> ISNULL(ER1.ExchangeRate,1)) / ISNULL(ER2.ExchangeRate,1))), 4) * CASE
> DSREA.DebitFlg WHEN 1 THEN 1
> ELSE -1
> END
>
> Now everything is working fine except when Er2.exchangrate is 0. if 0
> then
> (500 * 0.02) / 0
> Now i have two problems..
> 1.It is giving divide by 0 error
> 2.My Host amount should come as 0 if Er2.exchangerate is 0
> How do i achieve it.
> Can anyone help me with this ?
> Regards,
> Rajeev
>|||However... having an ExchangeRate of 0 is usually wrong.
It's better to correct the data and modify the DDL so it doesn't accept
wrong values (i.e. use a check constraint to verify that
ExchangeRate>0). If you need to store rows where the value for the
ExchangeRate is unknown, I think it's better to store NULL in that
column instead of zero.
Razvan

Distrubuted Transactions with AS400

Hi All
I have created stored procs that update SQL and AS400 (Linked server)
tables. I want to wrap both updates in a transaction, however when I do, I
get the error
"The operation could not be performed because OLE DB provider "MSDASQL" for
linked server "LinkedServerName" was unable to begin a distributed
transaction".
We are using Client Access ODBC driver (V5R2, SI06631).
All of the documents that I have read talk about MTS and MS DTC.
MS DTC is running on the SQL Server.
Is there any way of using Distributed Transaction processing without
requiring MTS ?
Thanks in advance.not enough info on the error.
here:
http://support.microsoft.com/kb/306212
you may want to look at the full error when you talk to DB2. like so
DBCC TRACEON (3604, 7300)
then run your sp and seewhat it does.
did you try just issue
BEGIN DISTRIBUTED TRAN
and see if the 2 data sources you are working with can be accessed?
and you the same authentication your application is going to call your sp
with.
Thanks, Liliya
"Jane" wrote:
> Hi All
> I have created stored procs that update SQL and AS400 (Linked server)
> tables. I want to wrap both updates in a transaction, however when I do, I
> get the error
> "The operation could not be performed because OLE DB provider "MSDASQL" for
> linked server "LinkedServerName" was unable to begin a distributed
> transaction".
> We are using Client Access ODBC driver (V5R2, SI06631).
> All of the documents that I have read talk about MTS and MS DTC.
> MS DTC is running on the SQL Server.
> Is there any way of using Distributed Transaction processing without
> requiring MTS ?
> Thanks in advance.|||Thanks for the response.
Yes - used the same auth as the app will use (windows auth)
Yes - just used BEGIN DISTRIBUTED TRAN
Used DBCC TRACEON (3604, 7300) & SET XACT_ABORT ON as per the link in a
Query Window then ran the updates again.
Ran DBCC TRACESTATUS (3604,7300) to check that the flags for the session
were set.
Still failing & still no extra error information.
Under "Configuration Issues" the link states ;
"Start the Distributed Transaction Coordinator (DTC or MSDTC) on all servers
that are involved in the distributed transaction".
I am not sure what needs to running on the AS400 for this to work.
"l" wrote:
> not enough info on the error.
> here:
> http://support.microsoft.com/kb/306212
> you may want to look at the full error when you talk to DB2. like so
> DBCC TRACEON (3604, 7300)
> then run your sp and seewhat it does.
> did you try just issue
> BEGIN DISTRIBUTED TRAN
> and see if the 2 data sources you are working with can be accessed?
> and you the same authentication your application is going to call your sp
> with.
> Thanks, Liliya
>
> "Jane" wrote:
> > Hi All
> >
> > I have created stored procs that update SQL and AS400 (Linked server)
> > tables. I want to wrap both updates in a transaction, however when I do, I
> > get the error
> > "The operation could not be performed because OLE DB provider "MSDASQL" for
> > linked server "LinkedServerName" was unable to begin a distributed
> > transaction".
> > We are using Client Access ODBC driver (V5R2, SI06631).
> > All of the documents that I have read talk about MTS and MS DTC.
> > MS DTC is running on the SQL Server.
> >
> > Is there any way of using Distributed Transaction processing without
> > requiring MTS ?
> >
> > Thanks in advance.|||OLEDB Errors in SQL Profiler shows ;
<hresult>-2147168246</hresult>
<inputs>
<punkTransactionCoord>0x624A0060</punkTransactionCoord>
<isoLevel>4096</isoLevel>
<isoFlags>0</isoFlags>
<pOtherOptions>0x00000000</pOtherOptions>
</inputs>|||> I am not sure what needs to running on the AS400 for this to work.
depend on what are you running on AS400. DB2 version. Here. how to set up
mts (0you have mentioned this one earlier in this thread)
http://www-03.ibm.com/servers/enable/site/db2/mts/mts.pdf
as about ms sql server side, it does not hurt to test if your sql side is
ok. Easy enough as long as you have couple ms sql's on the same domain etc.
if not, can install one on your ws, configure it and see if you can issue
distributed transactions. After all, it will give you a 'lab rat' s well to
experiment if you in need of one.
Thanks, Liliya

Distribution Agent error

distribution agent giving error like unable to update sp...
can any one suggest which data will delete from which talbles and replication should work normally.
John,
please can you post up the exact error message you receive, and any
'special' configuration settings you have.
TIA,
Paul Ibison

Distributed transaction error, need help please!

Hi, I have configured a linked server, and i have a procedure which makes an UPDATE in a local table using the data in the linked server.

Specifically, I have a function which checks if a given code exists in a linked server's table. The UPDATE changes the value of a column in a local table, if the function returns 1.

I've run the procedure and it gave an error after a few hours cause a simple conversion error inside the function. I solved the error. After this, the procedure did not work more. It gives me the following message:

Server: Msg 7391, Level 16, State 1, Procedure EXISTEONC, Line 16
The operation could not be performed because the OLE DB provider 'MSDASQL'
was unable to begin a distributed transaction.

(EXISTEONC is the function, and in the line 16 there is an OPENQUERY)
Im sure MSDTC is working... i'm lost because i dont know why it worked the first time and not now. Ive also wrote the function again as it was before, but it still doesent works.

Thanks a lot...don't mean to ask a silly question but have you recompiled the stroed procedure ?|||Do you mean opening the procedure and closing it again??
Yes, I did that...|||i'm trying to understand - is the function being called from a stroed proc

wording is not too clear|||i'll try to explain me...

I have a stored proc, it looks like this:

BEGIN TRANS
UPDATE CodeTable SET Found='Yes' WHERE EXISTEONC(ONCCode)=1
COMMIT

the function EXISTEONC takes a code (ONCCode is a field in CodeTable) and searchs it in a table in the linked server. If it was found, it returns 1.

The error appears in line 16 of the function (in the openquery statement to search the code), but only if i execute the Procedure:
If i write this in the Analyzer:
PRINT EXISTEONC('1234')
there is no error, i think, the error appears only if the function is executed inside a transaction ...|||First thing is I would be 100% sure that DTC is running ok

If this is OK go backa and drop and recreate everything i.e. stroed proc and the function

If this is still not working then it may be some sort of corruption in the data access components on your server thats messed up the DLL's responsible for OLE DB.

Not sure how to approach this - Reinstall the MDAC?

Any ideas folks ??|||I'm 100% sure DTC is running (i stopped it, and restarted it about 500000 times).
I compiled the function and the procedure again...
With SQL server I can see the data in the linked server. Remember: if I execute the function outside the UPDATE, for example:
PRINT dbo.EXISTEONC('1111')
it works, so, the connection is working and the data is not corrupted.

but, the *&#%#@. procedure still doesent work...

I forgot to say this: the server database is a Sybase Adaptiver Server Anywhere 6.0, and I connect to it through an ODBC... i access the ODBC in Sql Server 2000.

distributed transaction error

Hi,
I am excuting an insert query (in an update trigger) from a database
on ServerA to insert records in another database on different server,
ServerB. It was running fine until i moved it to different machine
where it is giving Distributed Transaction Error. I've narrowed down
the problem and now when i run the following query in Query Analyzer
it gives that error:
SET REMOTE_PROC_TRANSACTIONS OFF
SET XACT_ABORT ON
BEGIN DISTRIBUTED TRAN
INSERT INTO ServerB.MyDatabase.dbo.MyTable
(Field1, Field2)
VALUES ('value1', 'value2')
COMMIT TRAN
Error is:
Server: Msg 7391, Level 16, State 1, Line 4
The operation could not be performed because the OLE DB provider
'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in
the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
BTW, when run only this part in Query Analyser:
INSERT INTO ServerB.MyDatabase.dbo.MyTable
(Field1, Field2)
VALUES ('value1', 'value2')
it runs fine.
Any Help'
ThanksWhat is the OS of ServerA and Server B?
======= .NETXpert ==========
url: http://www.dotnetxpert.com
eml : kevin@.dotnetxpert.com
msn: kevin025@.magicn.com
==========================
"TF" <faridt@.coned.com> wrote in message
news:ae1ce536.0402031242.381d223b@.posting.google.com...

quote:

---

> Hi,
> I am excuting an insert query (in an update trigger) from a database
> on ServerA to insert records in another database on different server,
> ServerB. It was running fine until i moved it to different machine
> where it is giving Distributed Transaction Error. I've narrowed down
> the problem and now when i run the following query in Query Analyzer
> it gives that error:
> SET REMOTE_PROC_TRANSACTIONS OFF
> SET XACT_ABORT ON
> BEGIN DISTRIBUTED TRAN
> INSERT INTO ServerB.MyDatabase.dbo.MyTable
> (Field1, Field2)
> VALUES ('value1', 'value2')
> COMMIT TRAN
> Error is:
> Server: Msg 7391, Level 16, State 1, Line 4
> The operation could not be performed because the OLE DB provider
> 'SQLOLEDB' was unable to begin a distributed transaction.
> [OLE/DB provider returned message: New transaction cannot enlist in
> the specified transaction coordinator. ]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a].
> BTW, when run only this part in Query Analyser:
> INSERT INTO ServerB.MyDatabase.dbo.MyTable
> (Field1, Field2)
> VALUES ('value1', 'value2')
> it runs fine.
> Any Help'
> Thanks

---

|||> What is the OS of ServerA and Server B?

quote:

---

>

---

The OS on both ServerA and ServerB is Windows 2000 with SQL Server 2000|||maybe, I think that your problem will be solved with this article.
http://support.microsoft.com/defaul...kb;en-us;306843
If not solved with that, reply one more.
======= .NETXpert ==========
url: http://www.dotnetxpert.com
eml : kevin@.dotnetxpert.com
msn: kevin025@.magicn.com
==========================
"TF" <faridt@.coned.com> wrote in message
news:ae1ce536.0402040451.1bac44c8@.posting.google.com...

quote:

---

> The OS on both ServerA and ServerB is Windows 2000 with SQL Server 2000

---

distributed transaction error

Hi,
I am excuting an insert query (in an update trigger) from a database
on ServerA to insert records in another database on different server,
ServerB. It was running fine until i moved it to different machine
where it is giving Distributed Transaction Error. I've narrowed down
the problem and now when i run the following query in Query Analyzer
it gives that error:
SET REMOTE_PROC_TRANSACTIONS OFF
SET XACT_ABORT ON
BEGIN DISTRIBUTED TRAN
INSERT INTO ServerB.MyDatabase.dbo.MyTable
(Field1, Field2)
VALUES ('value1', 'value2')
COMMIT TRAN
Error is:
Server: Msg 7391, Level 16, State 1, Line 4
The operation could not be performed because the OLE DB provider
'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in
the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
BTW, when run only this part in Query Analyser:
INSERT INTO ServerB.MyDatabase.dbo.MyTable
(Field1, Field2)
VALUES ('value1', 'value2')
it runs fine.
Any Help'
ThanksWhat is the OS of ServerA and Server B?
--
======= .NETXpert ==========url: http://www.dotnetxpert.com
eml : kevin@.dotnetxpert.com
msn: kevin025@.magicn.com
==========================
"TF" <faridt@.coned.com> wrote in message
news:ae1ce536.0402031242.381d223b@.posting.google.com...
> Hi,
> I am excuting an insert query (in an update trigger) from a database
> on ServerA to insert records in another database on different server,
> ServerB. It was running fine until i moved it to different machine
> where it is giving Distributed Transaction Error. I've narrowed down
> the problem and now when i run the following query in Query Analyzer
> it gives that error:
> SET REMOTE_PROC_TRANSACTIONS OFF
> SET XACT_ABORT ON
> BEGIN DISTRIBUTED TRAN
> INSERT INTO ServerB.MyDatabase.dbo.MyTable
> (Field1, Field2)
> VALUES ('value1', 'value2')
> COMMIT TRAN
> Error is:
> Server: Msg 7391, Level 16, State 1, Line 4
> The operation could not be performed because the OLE DB provider
> 'SQLOLEDB' was unable to begin a distributed transaction.
> [OLE/DB provider returned message: New transaction cannot enlist in
> the specified transaction coordinator. ]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a].
> BTW, when run only this part in Query Analyser:
> INSERT INTO ServerB.MyDatabase.dbo.MyTable
> (Field1, Field2)
> VALUES ('value1', 'value2')
> it runs fine.
> Any Help'
> Thanks|||> What is the OS of ServerA and Server B?
>
The OS on both ServerA and ServerB is Windows 2000 with SQL Server 2000|||maybe, I think that your problem will be solved with this article.
http://support.microsoft.com/default.aspx?scid=kb;en-us;306843
If not solved with that, reply one more.
--
======= .NETXpert ==========url: http://www.dotnetxpert.com
eml : kevin@.dotnetxpert.com
msn: kevin025@.magicn.com
==========================
"TF" <faridt@.coned.com> wrote in message
news:ae1ce536.0402040451.1bac44c8@.posting.google.com...
> > What is the OS of ServerA and Server B?
> >
> The OS on both ServerA and ServerB is Windows 2000 with SQL Server 2000

Distributed Transaction Coordinator

Hi,
I understand if I use Immediate update from Subscriber to Pulisher, i will
use the function of DTC, how about if I make a schedule e.g. every 1 hour,
when it runs (Push/Pull), does that use DTC also?
Second ? is do i have to use Remove Server to run or I can use Linked
Server?
Thanks so much
Ed
Ed,
the schedule refers to the distribution agent and applies to commands going
from the publisher to the subscriber. if you want to 'postpone' the relay of
commands from the subscriber to the publisher, you can use queued updating
subscribers. Replication uses remote servers, but linked servers can also be
used for the DTC (have a look at :
http://support.microsoft.com/default...b;en-us;274098 )
HTH,
Paul Ibison, SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Distributed transaction aborted by MSDTC

hi,
i'm implementing a distributed database(partitioned view) system using 3
linked MSSQL 2000 server(SP3),
when I try update the distributed table within a BEGIN TRAN & COMMIT TRAN, I
get the error "Distributed transaction aborted by MSDTC", or sometime
"Distributed transaction completed. Either enlist this session in a new
transaction or the NULL transaction."
the http://support.microsoft.com/?kbid=834849 says this only happened if one
of the linked server is MSSQL Server 7.0, but all my servers are 2000 Server
with SP3, so can somebody help to resolve this?
If you are running Windows XP or Windows Server 2003 you can use DTC tracing
to find out why the transaction aborts, otherwise you can use SQL Profiler
and include the DTC transaction event class.
Do you have a trigger on one of the tables that is involved in the
transaction?
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2004 All rights reserved.
"eugeneng" <eugeneng@.discussions.microsoft.com> wrote in message
news:C4FC5E8E-B128-459D-9914-78B8CE2AD795@.microsoft.com...
> hi,
> i'm implementing a distributed database(partitioned view) system using 3
> linked MSSQL 2000 server(SP3),
> when I try update the distributed table within a BEGIN TRAN & COMMIT TRAN,
> I
> get the error "Distributed transaction aborted by MSDTC", or sometime
> "Distributed transaction completed. Either enlist this session in a new
> transaction or the NULL transaction."
> the http://support.microsoft.com/?kbid=834849 says this only happened if
> one
> of the linked server is MSSQL Server 7.0, but all my servers are 2000
> Server
> with SP3, so can somebody help to resolve this?
>
>

Distributed transaction aborted by MSDTC

hi,
i'm implementing a distributed database(partitioned view) system using 3
linked MSSQL 2000 server(SP3),
when I try update the distributed table within a BEGIN TRAN & COMMIT TRAN, I
get the error "Distributed transaction aborted by MSDTC", or sometime
"Distributed transaction completed. Either enlist this session in a new
transaction or the NULL transaction."
the http://support.microsoft.com/?kbid=834849 says this only happened if one
of the linked server is MSSQL Server 7.0, but all my servers are 2000 Server
with SP3, so can somebody help to resolve this?If you are running Windows XP or Windows Server 2003 you can use DTC tracing
to find out why the transaction aborts, otherwise you can use SQL Profiler
and include the DTC transaction event class.
Do you have a trigger on one of the tables that is involved in the
transaction?
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2004 All rights reserved.
"eugeneng" <eugeneng@.discussions.microsoft.com> wrote in message
news:C4FC5E8E-B128-459D-9914-78B8CE2AD795@.microsoft.com...
> hi,
> i'm implementing a distributed database(partitioned view) system using 3
> linked MSSQL 2000 server(SP3),
> when I try update the distributed table within a BEGIN TRAN & COMMIT TRAN,
> I
> get the error "Distributed transaction aborted by MSDTC", or sometime
> "Distributed transaction completed. Either enlist this session in a new
> transaction or the NULL transaction."
> the http://support.microsoft.com/?kbid=834849 says this only happened if
> one
> of the linked server is MSSQL Server 7.0, but all my servers are 2000
> Server
> with SP3, so can somebody help to resolve this?
>
>

Distributed transaction aborted by MSDTC

hi,
i'm implementing a distributed database(partitioned view) system using 3
linked MSSQL 2000 server(SP3),
when I try update the distributed table within a BEGIN TRAN & COMMIT TRAN, I
get the error "Distributed transaction aborted by MSDTC", or sometime
"Distributed transaction completed. Either enlist this session in a new
transaction or the NULL transaction."
the http://support.microsoft.com/?kbid=834849 says this only happened if one
of the linked server is MSSQL Server 7.0, but all my servers are 2000 Server
with SP3, so can somebody help to resolve this?If you are running Windows XP or Windows Server 2003 you can use DTC tracing
to find out why the transaction aborts, otherwise you can use SQL Profiler
and include the DTC transaction event class.
Do you have a trigger on one of the tables that is involved in the
transaction?
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright © SQLDev.Net 1991-2004 All rights reserved.
"eugeneng" <eugeneng@.discussions.microsoft.com> wrote in message
news:C4FC5E8E-B128-459D-9914-78B8CE2AD795@.microsoft.com...
> hi,
> i'm implementing a distributed database(partitioned view) system using 3
> linked MSSQL 2000 server(SP3),
> when I try update the distributed table within a BEGIN TRAN & COMMIT TRAN,
> I
> get the error "Distributed transaction aborted by MSDTC", or sometime
> "Distributed transaction completed. Either enlist this session in a new
> transaction or the NULL transaction."
> the http://support.microsoft.com/?kbid=834849 says this only happened if
> one
> of the linked server is MSSQL Server 7.0, but all my servers are 2000
> Server
> with SP3, so can somebody help to resolve this?
>
>