Can this be done using TSQL ?!

Hello
im writing an inventory application for a customer that needs to calculate
item cost by Moving Average method which requires calculating the cost after
each operation, i have a good experience with TSQL but so far i failed to
write the statement that can do this WITHOUT writing cursors
im trying to avoid calculating cost after each transaction to the inventory
. and by writing a stored procedure , get a list showing transactions and
item avg in a period
here is a description of moving avg method , also available here for those
who cant read html
http://www.fms.indiana.edu/auxiliary/inventory.asp
Moving Average--Perpetual
Continuous or moving average assigns a unit value to cost of goods available
for sale. In this scenario, the average cost determines cost of goods sold
at the time of each sale. This method requires a calculation of average unit
cost after each purchase as illustrated below.
# of Units Cost per Unit Total Cost Moving Avg. Cost
Beginning inventory, 7/1 200
$5,000
$25.00
Purchase, 8/10 100
$26.00
2,600
Inv. Balance 300
7,600
25.33
Sale, 9/15 (100)
25.33
(2,533)
Inv. Balance 200
5,067
Purchase, 12/7 600
27.00
16,200
Inv. Balance 800
21,267
26.58
Sale, 12/18 (300)
26.58
(7,975)
Inv. Balance 500
13,292
Sale, 2/22 (250)
26.58
(6,645
Inv. Balance 250
6,647
Purchase, 3/20 300
28.00
8,400
Inv. Balance 550
15,047
27.36
Sale, 5/15 (150)
27.36
(4,104)
Inv. Balance 400
27.36
10,943
Ending Inventory 400
10,943
Cost of Goods Sold 100
2,533
300
7,975
250
6,645
150
4,104
800
$21,257
Regards
Bassamcan you post DDL and some data..and also the example..pasted correctly..
Av.
http://dotnetjunkies.com/WebLog/avnrao
http://www28.brinkster.com/avdotnet
"Bassam" <egbas@.yahoo.com> wrote in message
news:ORrEOZODFHA.3596@.TK2MSFTNGP12.phx.gbl...
> Hello
> im writing an inventory application for a customer that needs to calculate
> item cost by Moving Average method which requires calculating the cost
after
> each operation, i have a good experience with TSQL but so far i failed to
> write the statement that can do this WITHOUT writing cursors
> im trying to avoid calculating cost after each transaction to the
inventory
> . and by writing a stored procedure , get a list showing transactions and
> item avg in a period
> here is a description of moving avg method , also available here for those
> who cant read html
> http://www.fms.indiana.edu/auxiliary/inventory.asp
> Moving Average--Perpetual
> Continuous or moving average assigns a unit value to cost of goods
available
> for sale. In this scenario, the average cost determines cost of goods sold
> at the time of each sale. This method requires a calculation of average
unit
> cost after each purchase as illustrated below.
> # of Units Cost per Unit Total Cost Moving Avg. Cost
> Beginning inventory, 7/1 200
> $5,000
> $25.00
> Purchase, 8/10 100
> $26.00
> 2,600
>
> Inv. Balance 300
> 7,600
> 25.33
> Sale, 9/15 (100)
> 25.33
> (2,533)
>
> Inv. Balance 200
> 5,067
>
> Purchase, 12/7 600
> 27.00
> 16,200
>
> Inv. Balance 800
> 21,267
> 26.58
> Sale, 12/18 (300)
> 26.58
> (7,975)
>
> Inv. Balance 500
> 13,292
>
> Sale, 2/22 (250)
> 26.58
> (6,645
>
> Inv. Balance 250
> 6,647
>
> Purchase, 3/20 300
> 28.00
> 8,400
>
> Inv. Balance 550
> 15,047
> 27.36
> Sale, 5/15 (150)
> 27.36
> (4,104)
>
> Inv. Balance 400
> 27.36
> 10,943
>
> Ending Inventory 400
> 10,943
>
> Cost of Goods Sold 100
> 2,533
>
> 300
> 7,975
>
> 250
> 6,645
>
> 150
> 4,104
>
> 800
> $21,257
>
>
> Regards
> Bassam
>|||Please post DDL and some INSERT statements of your sample data:
http://www.aspfaq.com/etiquette.asp?id=5006
--
David Portas
SQL Server MVP
--|||On Mon, 7 Feb 2005 09:27:19 +0200, Bassam wrote:

>im writing an inventory application for a customer that needs to calculate
>item cost by Moving Average method which requires calculating the cost afte
r
>each operation, i have a good experience with TSQL but so far i failed to
>write the statement that can do this WITHOUT writing cursors
Hi Bassam,
I think you can get the moving average by a simple self-join with group
by. Check the following example:
-- First, create a table to hold all transactions
-- Opening balance is considered a transaction in this simplified example
CREATE TABLE Operations
(OpDate smalldatetime not null primary key,
Amount int not null, -- >0 purchase <0 sale
UnitPrice money not null)
go
-- Insert all data (same as on web page you mentioned)
INSERT Operations (OpDate, Amount, UnitPrice)
SELECT '20040701', 200, 25
UNION ALL
SELECT '20040810', 100, 26
UNION ALL
SELECT '20040915', -100, 25.33
UNION ALL
SELECT '20041207', 600, 27
UNION ALL
SELECT '20041218', -300, 26.58
UNION ALL
SELECT '20050222', -250, 26.58
UNION ALL
SELECT '20050320', 300, 28
UNION ALL
SELECT '20050515', -150, 27.36
go
-- Here's the statement that will calculate amount, value and moving
-- average after each of the transaction.
SELECT a.OpDate AS InvDate,
SUM(b.Amount) AS Amount,
SUM(b.Amount * b.UnitPrice) AS Value,
SUM(b.Amount * b.UnitPrice) / SUM(b.Amount) AS MovingAvg
FROM Operations AS a
INNER JOIN Operations AS b
ON b.OpDate <= a.OpDate
GROUP BY a.OpDate
ORDER BY a.OpDate
go
-- Done. Now clean up the mess.
DROP TABLE Operations
go
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hello Hugo,
Thank you for your input but result of your statement will calculate
"Weighted Average" not "Moving Average"
difference is shown in examples in this link
http://www.fms.indiana.edu/auxiliary/inventory.asp
if you open this page and search for weighted average you fill find the
example which works with your statement but the just below example which is
for moving average won't work
i will post DDL and some data here to clear the case
Regards
Bassam
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:hhme01diqfdiohvb2rj3t896k5deoc47e9@.
4ax.com...
> On Mon, 7 Feb 2005 09:27:19 +0200, Bassam wrote:
>
calculate
after
> Hi Bassam,
> I think you can get the moving average by a simple self-join with group
> by. Check the following example:
> -- First, create a table to hold all transactions
> -- Opening balance is considered a transaction in this simplified example
> CREATE TABLE Operations
> (OpDate smalldatetime not null primary key,
> Amount int not null, -- >0 purchase <0 sale
> UnitPrice money not null)
> go
> -- Insert all data (same as on web page you mentioned)
> INSERT Operations (OpDate, Amount, UnitPrice)
> SELECT '20040701', 200, 25
> UNION ALL
> SELECT '20040810', 100, 26
> UNION ALL
> SELECT '20040915', -100, 25.33
> UNION ALL
> SELECT '20041207', 600, 27
> UNION ALL
> SELECT '20041218', -300, 26.58
> UNION ALL
> SELECT '20050222', -250, 26.58
> UNION ALL
> SELECT '20050320', 300, 28
> UNION ALL
> SELECT '20050515', -150, 27.36
> go
> -- Here's the statement that will calculate amount, value and moving
> -- average after each of the transaction.
> SELECT a.OpDate AS InvDate,
> SUM(b.Amount) AS Amount,
> SUM(b.Amount * b.UnitPrice) AS Value,
> SUM(b.Amount * b.UnitPrice) / SUM(b.Amount) AS MovingAvg
> FROM Operations AS a
> INNER JOIN Operations AS b
> ON b.OpDate <= a.OpDate
> GROUP BY a.OpDate
> ORDER BY a.OpDate
> go
> -- Done. Now clean up the mess.
> DROP TABLE Operations
> go
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||On Mon, 7 Feb 2005 14:32:20 +0200, Bassam wrote:

>Hello Hugo,
>Thank you for your input but result of your statement will calculate
>"Weighted Average" not "Moving Average"
>difference is shown in examples in this link
>http://www.fms.indiana.edu/auxiliary/inventory.asp
>if you open this page and search for weighted average you fill find the
>example which works with your statement but the just below example which is
>for moving average won't work
Hi Bassam,
I did check that page, and the results of my query were equal to the
moving average quoted on that page (the table directly after the heading
"Moving Average--Perpetual").

>i will post DDL and some data here to clear the case
Excellent idea!
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hello Hugo,
I tested your statement, great , it works to a great detail !! , i found a
problem in the moving avg in date 2/22/05 , it should be exactly as the one
done on 12/18/04 (sales also) to be 26.5860 , but the one on 2/22/05 is
26.5920 (it is exactly 26.5860 in table) so i will make my tests what if i
put 20 sales transactions and see the result
but your statement gave max accurate to date to table.do you know a way to
overcome this small shift ?
thank you and welcome to any comments
Bassam|||On Mon, 7 Feb 2005 15:24:41 +0200, Bassam wrote:

>Hello Hugo,
>I tested your statement, great , it works to a great detail !! , i found a
>problem in the moving avg in date 2/22/05 , it should be exactly as the one
>done on 12/18/04 (sales also) to be 26.5860 , but the one on 2/22/05 is
>26.5920 (it is exactly 26.5860 in table) so i will make my tests what if i
>put 20 sales transactions and see the result
Hi Bassam,
I noted the difference as well. This is caused by rounding errors.
Consider the first few rows in the sample data. The beginning inventory
shows 200 units at a total cost of $ 5,000 - exactle $ 25.00 on average.
After the first purchase, there are 300 units on stock and the total cost
is equal to $ 7,600. The average price is $ 25.333333333333333 (etc), but
it is rounded down to $ 25.33. This would mean that if the following sale
would not be for 100 units (as listed in the example), but for 300 units,
the total sale price would be $ 7,599 and the remaining stock would be 0
units, for a total price of $ 1.
The example on the web page graciously avoids this anomaly by only
including a new price after each purchase. It doesn't list the moving avg
cost after a sale, so I could not verify if the values given by my query
are correct or not.
If you need the moving average cost to reflect the situation after the
last purchase instead of after the last sale, try this (slightly more
complicated) query:
SELECT a.OpDate AS InvDate,
SUM(b.Amount) AS Amount,
SUM(b.Amount * b.UnitPrice) AS Value,
(SELECT SUM(c.Amount * c.UnitPrice) / SUM(c.Amount)
FROM Operations AS c
WHERE c.OpDate <= (SELECT MAX(d.OpDate)
FROM Operations AS d
WHERE d.OpDate <= a.OpDate
AND d.Amount > 0)) AS MovingAvg
FROM Operations AS a
INNER JOIN Operations AS b
ON b.OpDate <= a.OpDate
GROUP BY a.OpDate
ORDER BY a.OpDate
(Note: if you only need the date and the moving average, not the amount
and value of the inventory, you can remove the group by and the join to
"Operations AS b" - IOW, you can simplify to:
SELECT a.OpDate AS InvDate,
(SELECT SUM(c.Amount * c.UnitPrice) / SUM(c.Amount)
FROM Operations AS c
WHERE c.OpDate <= (SELECT MAX(d.OpDate)
FROM Operations AS d
WHERE d.OpDate <= a.OpDate
AND d.Amount > 0)) AS MovingAvg
FROM Operations AS a
ORDER BY a.OpDate
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Bassam,
Thanks to Hugo for posting the DDL for the table, I will assume it is
correct. Here is a try, but do not compare to the table in the link, there i
s
an error there.
Error in the link:
Sale, 12/18 (300) 26.58 (7,975)
well, -300 * 26.58 should be 7,974.
select
a.OpDate,
sum(b.Amount) as number_of_units,
sum(b.Amount * b.UnitPrice) as total_cost,
(
select
sum(c.Amount * c.UnitPrice) / sum(c.Amount)
from
Operations as c
where
c.OpDate <= (
select
max(d.OpDate)
from
Operations as d
where
sign(d.Amount) >= 0 and d.OpDate <= a.OpDate
)
) as moving_avg_cost
from
Operations as a
inner join
Operations as b
on a.OpDate >= b.OpDate
group by
a.OpDate
order by
a.OpDate
go
AMB
"Bassam" wrote:

> Hello Hugo,
> Thank you for your input but result of your statement will calculate
> "Weighted Average" not "Moving Average"
> difference is shown in examples in this link
> http://www.fms.indiana.edu/auxiliary/inventory.asp
> if you open this page and search for weighted average you fill find the
> example which works with your statement but the just below example which i
s
> for moving average won't work
> i will post DDL and some data here to clear the case
> Regards
> Bassam
>
> "Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
> news:hhme01diqfdiohvb2rj3t896k5deoc47e9@.
4ax.com...
> calculate
> after
>
>|||Hello Hugo,
Thank you, Clear and efficient ! , only last problem , what if a user need
to delete a purchasing happened at beginning of month - or adjust its unit
price value, that means all next averages used in next sales is wrong and
need to be recalculated, is there a way to recalculate unit price for sales
transactions ' i need to adjust that before using your statement again or
result will be wrong
to make situation more complicated is that it might also be more purchasing
down there with sales, i mean suppose user deleted 1 of 5 purchasing done at
beginning of the month , on day 1 , while other purchasing happened on day 5
, 8 , 12 , 14 remains , and there are sales in between. , how then i can
recalculate unit price (which is the moving average) for sales
transactions.in between ?
Thank you
Bassam

Can the Job Notifications be configured to use a different transport mechanism?

(SQL Server 2000, SP3a)
Hello all!
In our Production environment, we're moving towards a configuration that will preclude the
use of SQL Mail. As far as we know, Job Notifications have three transport mechanisms:
E-Mail, Pager, and Net Send. From what we understand, E-Mail and Pager both require SQL
Mail, which would leave Net Send as our only available transport.
Is there any way to integrate an additional Notification transport into SQL Server?
Ideally, we'd like to use something like xp_smtp_sendmail. But, we're not sure how to
make the Notifications leverage something outside the default transports.
Thanks!
John PetersonFor xp_smtp_sendmail, you can just add another step that
sends the email. So if you wanted to notify on failure, just
add a step to the job that you branch to on failure. This
job step can handle the email. You won't have support for
operators but you can write your own - have a table with
whatever email, rules then query this table to find who
should be notified. You can then pass this value to
xp_smtp_sendmail.
-Sue
On Mon, 3 Nov 2003 14:06:18 -0700, "John Peterson"
<j0hnp@.comcast.net> wrote:
>(SQL Server 2000, SP3a)
>Hello all!
>In our Production environment, we're moving towards a configuration that will preclude the
>use of SQL Mail. As far as we know, Job Notifications have three transport mechanisms:
>E-Mail, Pager, and Net Send. From what we understand, E-Mail and Pager both require SQL
>Mail, which would leave Net Send as our only available transport.
>Is there any way to integrate an additional Notification transport into SQL Server?
>Ideally, we'd like to use something like xp_smtp_sendmail. But, we're not sure how to
>make the Notifications leverage something outside the default transports.
>Thanks!
>John Peterson
>|||Thanks, Sue!
I had hoped there was a way to integrate the existing Operator aspect, but introduce a new
transport mechanism. It sounds like it won't be that straightforward. :-(
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:85hdqvse38r38os3ul9b6rsvgi600nlt1v@.4ax.com...
> For xp_smtp_sendmail, you can just add another step that
> sends the email. So if you wanted to notify on failure, just
> add a step to the job that you branch to on failure. This
> job step can handle the email. You won't have support for
> operators but you can write your own - have a table with
> whatever email, rules then query this table to find who
> should be notified. You can then pass this value to
> xp_smtp_sendmail.
> -Sue
> On Mon, 3 Nov 2003 14:06:18 -0700, "John Peterson"
> <j0hnp@.comcast.net> wrote:
> >(SQL Server 2000, SP3a)
> >
> >Hello all!
> >
> >In our Production environment, we're moving towards a configuration that will preclude
the
> >use of SQL Mail. As far as we know, Job Notifications have three transport mechanisms:
> >E-Mail, Pager, and Net Send. From what we understand, E-Mail and Pager both require
SQL
> >Mail, which would leave Net Send as our only available transport.
> >
> >Is there any way to integrate an additional Notification transport into SQL Server?
> >Ideally, we'd like to use something like xp_smtp_sendmail. But, we're not sure how to
> >make the Notifications leverage something outside the default transports.
> >
> >Thanks!
> >
> >John Peterson
> >
>|||No, it's a bit more of a PITA than that, but at the same time, it gives you
a lot more control. We use this mechanism and we have customized
subjects/bodies, inclusion lists based on various error levels, etc.
A
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:#glZ0DloDHA.3024@.tk2msftngp13.phx.gbl...
> Thanks, Sue!
> I had hoped there was a way to integrate the existing Operator aspect, but
introduce a new
> transport mechanism. It sounds like it won't be that straightforward.
:-(|||Thanks Aaron (and Sue)!
Out of curiosity, do you know whether the Notifications is making a SQL call to
xp_sendmail? If so, would it be possible to "swap out" the underlying xp_sendmail .DLL
and replace it with a xp_smtp_sendmail "wrapper" with the same name as xp_sendmail?
I doubt that the Notifications is making a SQL call to "do its business", but I thought
I'd check. :-)
Thanks!
John Peterson
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:uJREwHloDHA.684@.TK2MSFTNGP09.phx.gbl...
> No, it's a bit more of a PITA than that, but at the same time, it gives you
> a lot more control. We use this mechanism and we have customized
> subjects/bodies, inclusion lists based on various error levels, etc.
> A
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:#glZ0DloDHA.3024@.tk2msftngp13.phx.gbl...
> > Thanks, Sue!
> >
> > I had hoped there was a way to integrate the existing Operator aspect, but
> introduce a new
> > transport mechanism. It sounds like it won't be that straightforward.
> :-(
>|||Alerts does not call xp_sendmail as it did in 6.5 and earlier. Nowadays,
Agent does its own MAPI calls.
I'm writing a utility that does xp_smtp_sendmail calls based on your current
alert configuration. The idea is to configure alerts as desired, and then
schedule my .exe every x minutes. My .exe reads off of the alert config
table and reads the eventlog. Based on when each configured alert was fired
the last time, the exe will xp_smtp_sendmail. The current idea is to use
disabled operators (if you enable them, then Agent will complain that it
can't send email).
I'm finishing up this util this week, then I have to wait for it to go on
the web (which might take a few days to a few weeks - I don't have direct
access to the web). Send me an email if you want to give it a try earlier.
I will put it on www.dbmaint.com, free utilities.
--
Tibor Karaszi
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:eqa6mhloDHA.3024@.tk2msftngp13.phx.gbl...
> Thanks Aaron (and Sue)!
> Out of curiosity, do you know whether the Notifications is making a SQL
call to
> xp_sendmail? If so, would it be possible to "swap out" the underlying
xp_sendmail .DLL
> and replace it with a xp_smtp_sendmail "wrapper" with the same name as
xp_sendmail?
> I doubt that the Notifications is making a SQL call to "do its business",
but I thought
> I'd check. :-)
> Thanks!
> John Peterson
>
> "Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
> news:uJREwHloDHA.684@.TK2MSFTNGP09.phx.gbl...
> > No, it's a bit more of a PITA than that, but at the same time, it gives
you
> > a lot more control. We use this mechanism and we have customized
> > subjects/bodies, inclusion lists based on various error levels, etc.
> >
> > A
> >
> >
> > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > news:#glZ0DloDHA.3024@.tk2msftngp13.phx.gbl...
> > > Thanks, Sue!
> > >
> > > I had hoped there was a way to integrate the existing Operator aspect,
but
> > introduce a new
> > > transport mechanism. It sounds like it won't be that straightforward.
> > :-(
> >
> >
>|||Hello, Tibor! That sounds almost *exactly* what I'd be pining for! If you don't mind an
early beta tester, I'd be delighted to try it out earlier! :-)
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se> wrote in
message news:QYApb.36350$dP1.121349@.newsc.telia.net...
> Alerts does not call xp_sendmail as it did in 6.5 and earlier. Nowadays,
> Agent does its own MAPI calls.
> I'm writing a utility that does xp_smtp_sendmail calls based on your current
> alert configuration. The idea is to configure alerts as desired, and then
> schedule my .exe every x minutes. My .exe reads off of the alert config
> table and reads the eventlog. Based on when each configured alert was fired
> the last time, the exe will xp_smtp_sendmail. The current idea is to use
> disabled operators (if you enable them, then Agent will complain that it
> can't send email).
> I'm finishing up this util this week, then I have to wait for it to go on
> the web (which might take a few days to a few weeks - I don't have direct
> access to the web). Send me an email if you want to give it a try earlier.
> I will put it on www.dbmaint.com, free utilities.
> --
> Tibor Karaszi
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:eqa6mhloDHA.3024@.tk2msftngp13.phx.gbl...
> > Thanks Aaron (and Sue)!
> >
> > Out of curiosity, do you know whether the Notifications is making a SQL
> call to
> > xp_sendmail? If so, would it be possible to "swap out" the underlying
> xp_sendmail .DLL
> > and replace it with a xp_smtp_sendmail "wrapper" with the same name as
> xp_sendmail?
> >
> > I doubt that the Notifications is making a SQL call to "do its business",
> but I thought
> > I'd check. :-)
> >
> > Thanks!
> >
> > John Peterson
> >
> >
> > "Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
> > news:uJREwHloDHA.684@.TK2MSFTNGP09.phx.gbl...
> > > No, it's a bit more of a PITA than that, but at the same time, it gives
> you
> > > a lot more control. We use this mechanism and we have customized
> > > subjects/bodies, inclusion lists based on various error levels, etc.
> > >
> > > A
> > >
> > >
> > > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > > news:#glZ0DloDHA.3024@.tk2msftngp13.phx.gbl...
> > > > Thanks, Sue!
> > > >
> > > > I had hoped there was a way to integrate the existing Operator aspect,
> but
> > > introduce a new
> > > > transport mechanism. It sounds like it won't be that straightforward.
> > > :-(
> > >
> > >
> >
> >
>|||Great! Send me an email, John (remove the obvious parts), and I can send you
the bits when I'm done with it.
--
Tibor Karaszi
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:%23PfBOLmoDHA.3320@.tk2msftngp13.phx.gbl...
> Hello, Tibor! That sounds almost *exactly* what I'd be pining for! If
you don't mind an
> early beta tester, I'd be delighted to try it out earlier! :-)
>
> "Tibor Karaszi"
<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se> wrote in
> message news:QYApb.36350$dP1.121349@.newsc.telia.net...
> > Alerts does not call xp_sendmail as it did in 6.5 and earlier. Nowadays,
> > Agent does its own MAPI calls.
> > I'm writing a utility that does xp_smtp_sendmail calls based on your
current
> > alert configuration. The idea is to configure alerts as desired, and
then
> > schedule my .exe every x minutes. My .exe reads off of the alert config
> > table and reads the eventlog. Based on when each configured alert was
fired
> > the last time, the exe will xp_smtp_sendmail. The current idea is to use
> > disabled operators (if you enable them, then Agent will complain that it
> > can't send email).
> > I'm finishing up this util this week, then I have to wait for it to go
on
> > the web (which might take a few days to a few weeks - I don't have
direct
> > access to the web). Send me an email if you want to give it a try
earlier.
> > I will put it on www.dbmaint.com, free utilities.
> > --
> > Tibor Karaszi
> >
> >
> > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > news:eqa6mhloDHA.3024@.tk2msftngp13.phx.gbl...
> > > Thanks Aaron (and Sue)!
> > >
> > > Out of curiosity, do you know whether the Notifications is making a
SQL
> > call to
> > > xp_sendmail? If so, would it be possible to "swap out" the underlying
> > xp_sendmail .DLL
> > > and replace it with a xp_smtp_sendmail "wrapper" with the same name as
> > xp_sendmail?
> > >
> > > I doubt that the Notifications is making a SQL call to "do its
business",
> > but I thought
> > > I'd check. :-)
> > >
> > > Thanks!
> > >
> > > John Peterson
> > >
> > >
> > > "Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
> > > news:uJREwHloDHA.684@.TK2MSFTNGP09.phx.gbl...
> > > > No, it's a bit more of a PITA than that, but at the same time, it
gives
> > you
> > > > a lot more control. We use this mechanism and we have customized
> > > > subjects/bodies, inclusion lists based on various error levels, etc.
> > > >
> > > > A
> > > >
> > > >
> > > > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > > > news:#glZ0DloDHA.3024@.tk2msftngp13.phx.gbl...
> > > > > Thanks, Sue!
> > > > >
> > > > > I had hoped there was a way to integrate the existing Operator
aspect,
> > but
> > > > introduce a new
> > > > > transport mechanism. It sounds like it won't be that
straightforward.
> > > > :-(
> > > >
> > > >
> > >
> > >
> >
> >
>|||Good thinkin' Tibor.
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:ZZJpb.36406$dP1.121601@.newsc.telia.net...
> Great! Send me an email, John (remove the obvious parts), and I can send
you
> the bits when I'm done with it.
> --
> Tibor Karaszi
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:%23PfBOLmoDHA.3320@.tk2msftngp13.phx.gbl...
> > Hello, Tibor! That sounds almost *exactly* what I'd be pining for! If
> you don't mind an
> > early beta tester, I'd be delighted to try it out earlier! :-)
> >
> >
> > "Tibor Karaszi"
> <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se> wrote in
> > message news:QYApb.36350$dP1.121349@.newsc.telia.net...
> > > Alerts does not call xp_sendmail as it did in 6.5 and earlier.
Nowadays,
> > > Agent does its own MAPI calls.
> > > I'm writing a utility that does xp_smtp_sendmail calls based on your
> current
> > > alert configuration. The idea is to configure alerts as desired, and
> then
> > > schedule my .exe every x minutes. My .exe reads off of the alert
config
> > > table and reads the eventlog. Based on when each configured alert was
> fired
> > > the last time, the exe will xp_smtp_sendmail. The current idea is to
use
> > > disabled operators (if you enable them, then Agent will complain that
it
> > > can't send email).
> > > I'm finishing up this util this week, then I have to wait for it to go
> on
> > > the web (which might take a few days to a few weeks - I don't have
> direct
> > > access to the web). Send me an email if you want to give it a try
> earlier.
> > > I will put it on www.dbmaint.com, free utilities.
> > > --
> > > Tibor Karaszi
> > >
> > >
> > > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > > news:eqa6mhloDHA.3024@.tk2msftngp13.phx.gbl...
> > > > Thanks Aaron (and Sue)!
> > > >
> > > > Out of curiosity, do you know whether the Notifications is making a
> SQL
> > > call to
> > > > xp_sendmail? If so, would it be possible to "swap out" the
underlying
> > > xp_sendmail .DLL
> > > > and replace it with a xp_smtp_sendmail "wrapper" with the same name
as
> > > xp_sendmail?
> > > >
> > > > I doubt that the Notifications is making a SQL call to "do its
> business",
> > > but I thought
> > > > I'd check. :-)
> > > >
> > > > Thanks!
> > > >
> > > > John Peterson
> > > >
> > > >
> > > > "Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
> > > > news:uJREwHloDHA.684@.TK2MSFTNGP09.phx.gbl...
> > > > > No, it's a bit more of a PITA than that, but at the same time, it
> gives
> > > you
> > > > > a lot more control. We use this mechanism and we have customized
> > > > > subjects/bodies, inclusion lists based on various error levels,
etc.
> > > > >
> > > > > A
> > > > >
> > > > >
> > > > > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > > > > news:#glZ0DloDHA.3024@.tk2msftngp13.phx.gbl...
> > > > > > Thanks, Sue!
> > > > > >
> > > > > > I had hoped there was a way to integrate the existing Operator
> aspect,
> > > but
> > > > > introduce a new
> > > > > > transport mechanism. It sounds like it won't be that
> straightforward.
> > > > > :-(
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||Thanks John. :-)
Perhaps I should say that anyone who like to look at this and expect to
install it to provide some feedback are welcome to drop me an email.
(It'll be a freebie when released, so unless you expect to install and play,
you might as well wait a couple of weeks).
I'll post something here when it is on the web.
--
Tibor Karaszi
"John Sitka" <johnsitka@.REMOVEhotmail.com> wrote in message
news:e3eTqjtoDHA.964@.TK2MSFTNGP10.phx.gbl...
> Good thinkin' Tibor.
>
> "Tibor Karaszi"
<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> wrote in message news:ZZJpb.36406$dP1.121601@.newsc.telia.net...
> > Great! Send me an email, John (remove the obvious parts), and I can send
> you
> > the bits when I'm done with it.
> >
> > --
> > Tibor Karaszi
> >
> >
> > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > news:%23PfBOLmoDHA.3320@.tk2msftngp13.phx.gbl...
> > > Hello, Tibor! That sounds almost *exactly* what I'd be pining for!
If
> > you don't mind an
> > > early beta tester, I'd be delighted to try it out earlier! :-)
> > >
> > >
> > > "Tibor Karaszi"
> > <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se> wrote in
> > > message news:QYApb.36350$dP1.121349@.newsc.telia.net...
> > > > Alerts does not call xp_sendmail as it did in 6.5 and earlier.
> Nowadays,
> > > > Agent does its own MAPI calls.
> > > > I'm writing a utility that does xp_smtp_sendmail calls based on your
> > current
> > > > alert configuration. The idea is to configure alerts as desired, and
> > then
> > > > schedule my .exe every x minutes. My .exe reads off of the alert
> config
> > > > table and reads the eventlog. Based on when each configured alert
was
> > fired
> > > > the last time, the exe will xp_smtp_sendmail. The current idea is to
> use
> > > > disabled operators (if you enable them, then Agent will complain
that
> it
> > > > can't send email).
> > > > I'm finishing up this util this week, then I have to wait for it to
go
> > on
> > > > the web (which might take a few days to a few weeks - I don't have
> > direct
> > > > access to the web). Send me an email if you want to give it a try
> > earlier.
> > > > I will put it on www.dbmaint.com, free utilities.
> > > > --
> > > > Tibor Karaszi
> > > >
> > > >
> > > > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > > > news:eqa6mhloDHA.3024@.tk2msftngp13.phx.gbl...
> > > > > Thanks Aaron (and Sue)!
> > > > >
> > > > > Out of curiosity, do you know whether the Notifications is making
a
> > SQL
> > > > call to
> > > > > xp_sendmail? If so, would it be possible to "swap out" the
> underlying
> > > > xp_sendmail .DLL
> > > > > and replace it with a xp_smtp_sendmail "wrapper" with the same
name
> as
> > > > xp_sendmail?
> > > > >
> > > > > I doubt that the Notifications is making a SQL call to "do its
> > business",
> > > > but I thought
> > > > > I'd check. :-)
> > > > >
> > > > > Thanks!
> > > > >
> > > > > John Peterson
> > > > >
> > > > >
> > > > > "Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
> > > > > news:uJREwHloDHA.684@.TK2MSFTNGP09.phx.gbl...
> > > > > > No, it's a bit more of a PITA than that, but at the same time,
it
> > gives
> > > > you
> > > > > > a lot more control. We use this mechanism and we have
customized
> > > > > > subjects/bodies, inclusion lists based on various error levels,
> etc.
> > > > > >
> > > > > > A
> > > > > >
> > > > > >
> > > > > > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > > > > > news:#glZ0DloDHA.3024@.tk2msftngp13.phx.gbl...
> > > > > > > Thanks, Sue!
> > > > > > >
> > > > > > > I had hoped there was a way to integrate the existing Operator
> > aspect,
> > > > but
> > > > > > introduce a new
> > > > > > > transport mechanism. It sounds like it won't be that
> > straightforward.
> > > > > > :-(
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>

Can the distribution database be moved from once SQL server to another ?

Hello,
We are decommissioning a cluster and moving all the databases to a new
cluster. The issue is, the distribution database. Is it possible to move the
distribution database without dropping the publications and subscriptions ?
If it is possible, please let me know how to do it.
Thanking you in anticipation,
Yoga
The only way it can be done is to stop all log reader agents. Backup up the
master, msdb, and distribution database(s). Install a new server,
decomission the old one, rename the new server the name of the old
distributor, restore the databases, logs and tail with the keep_replication
switch, run sp_replrestart and use the continue on data consistency error
profile.
If you are lucky this should work.
"Yoganarasimha Prasad" <yogakiran@.gmail.com> wrote in message
news:unYHMx3hHHA.5048@.TK2MSFTNGP03.phx.gbl...
> Hello,
> We are decommissioning a cluster and moving all the databases to a new
> cluster. The issue is, the distribution database. Is it possible to move
> the distribution database without dropping the publications and
> subscriptions ? If it is possible, please let me know how to do it.
> Thanking you in anticipation,
> Yoga
>