Having 2 CALCULATE statements no longer works after installing SP2!!!

I received the following email from a collegue of mine:

I think we may have found an issue with SQL SERVER 2005 SP2

1. Some of the Analysis Services Properties when you click the advanced tab are missing!

2. The MDX scrip behaviour is not the same.

In our cubes we had two CALCULATE statements - first to aggregate the data as normal, then some code to calculate MTD and YTD figures and then another CALCULATE statement. This enabled us to calculate percentages later on in the script without having to sum up all the data at leaf levels first.

With SP2, this no longer works!!! All our percentages are now being added up (like all other measures) which is clearly wrong!

I am not sure how to deal with this. Do we need to inform the clients not to upgrade to SP2 OR can Microsoft resolve this?

Any ideas?

Actually, two CALCULATE statements are the same as one CALCULATE statement w.r.t. aggregating data. The only difference could be if you have unary operators/custom rollups etc. So I suspect the use of two CALCULATE in your scenario was redundant. There is probably something else going on. If you will paste your MDX Script here, perhaps somebody will be able to figure it out.|||Thanks for the reply Mosha. I will get my collegue to post an example.|||

Hello Mosha,

Attached is the MDX Script.

All it does is - FIRST calculates MTD and YTD values

Then calcualtes some percentages.

Prior to Service Pack 2, these percentage calculations displayed correct number when MTD was selected on the FLOW dimension.

Post Service Pack 2, its seems that the percentages are getting calculated first and then the MTD logic is being applied resulting in the addition of all percentages from the lowest level which is not the desired behaviour.

I have tried removing the second calculate statement and it still does not work in sp2.

thanks for your help.

/*
The CALCULATE command controls the aggregation of leaf cells in the cube.
If the CALCULATE command is deleted or modified, the data within the cube is affected.
You should edit this command only if you manually specify how the cube is aggregated.
*/
CALCULATE;

/* Calculate Values for Flow Dimension */
SCOPE([Flow].[KeyFlow].&[MTD]);
THIS = (Sum(PeriodsToDate([Period].[Calendar].[Month],[Period].[Calendar].CurrentMember),([Measures].CurrentMember, [Flow].[KeyFlow].&[P])));
END SCOPE;

SCOPE([Flow].[KeyFlow].&[YTD]);
THIS = (Sum(PeriodsToDate([Period].[Calendar].[Year],[Period].[Calendar].CurrentMember),([Measures].CurrentMember, [Flow].[KeyFlow].&[P])));
END SCOPE;

/*Calculate again for the flow calculations to aggregate */
CALCULATE;

/*Scope Total Traffic */
SCOPE([Measure Type].[Measure Type].[Measure].&[14]);
[Measures].[Dealership Measure Value]= [Measure Type].[Measure Type].[Measure].&[1] + [Measure Type].[Measure Type].[Measure].&[2] + [Measure Type].[Measure Type].[Measure].&[3];
END SCOPE;

/*Scope %Leads From Traffic */
SCOPE([Measure Type].[Measure Type].[Measure].&[16]);
[Measures].[Dealership Measure Value]=
CASE [Measure Type].[Measure Type].[Measure].&[14]
WHEN 0 THEN null
WHEN null THEN null
ELSE [Measure Type].[Measure Type].[Measure].&[4]/[Measure Type].[Measure Type].[Measure].&[14]
END;
END SCOPE;

|||IN THIs MDX Script, the second CALCULATE does nothing and can be safely removed with either SP2 or SP1 or RTM. The problem is somewhere else. It does look like a regression in SP2 to me, because according to this script, it really should compute percentages after MTD and YTD. I recommend opening a case with product support.|||

Just to add to the issue.

The percentages are not even added up. I am not sure what values it is calculating. We will raise a call with support.

Punita

|||I am having a problem with MTD() and YTD() after installing SP2. I have had to repair alot of queries since we went to SP2. I replaced the MTD and YTD functions with the PeriodsToDate() function and I was back in business. Hope that helps.

Having 2 CALCULATE statements no longer works after installing SP2!!!

I received the following email from a collegue of mine:

I think we may have found an issue with SQL SERVER 2005 SP2

1. Some of the Analysis Services Properties when you click the advanced tab are missing!

2. The MDX scrip behaviour is not the same.

In our cubes we had two CALCULATE statements - first to aggregate the data as normal, then some code to calculate MTD and YTD figures and then another CALCULATE statement. This enabled us to calculate percentages later on in the script without having to sum up all the data at leaf levels first.

With SP2, this no longer works!!! All our percentages are now being added up (like all other measures) which is clearly wrong!

I am not sure how to deal with this. Do we need to inform the clients not to upgrade to SP2 OR can Microsoft resolve this?

Any ideas?

Actually, two CALCULATE statements are the same as one CALCULATE statement w.r.t. aggregating data. The only difference could be if you have unary operators/custom rollups etc. So I suspect the use of two CALCULATE in your scenario was redundant. There is probably something else going on. If you will paste your MDX Script here, perhaps somebody will be able to figure it out.|||Thanks for the reply Mosha. I will get my collegue to post an example.|||

Hello Mosha,

Attached is the MDX Script.

All it does is - FIRST calculates MTD and YTD values

Then calcualtes some percentages.

Prior to Service Pack 2, these percentage calculations displayed correct number when MTD was selected on the FLOW dimension.

Post Service Pack 2, its seems that the percentages are getting calculated first and then the MTD logic is being applied resulting in the addition of all percentages from the lowest level which is not the desired behaviour.

I have tried removing the second calculate statement and it still does not work in sp2.

thanks for your help.

/*
The CALCULATE command controls the aggregation of leaf cells in the cube.
If the CALCULATE command is deleted or modified, the data within the cube is affected.
You should edit this command only if you manually specify how the cube is aggregated.
*/
CALCULATE;

/* Calculate Values for Flow Dimension */
SCOPE([Flow].[KeyFlow].&[MTD]);
THIS = (Sum(PeriodsToDate([Period].[Calendar].[Month],[Period].[Calendar].CurrentMember),([Measures].CurrentMember, [Flow].[KeyFlow].&[P])));
END SCOPE;

SCOPE([Flow].[KeyFlow].&[YTD]);
THIS = (Sum(PeriodsToDate([Period].[Calendar].[Year],[Period].[Calendar].CurrentMember),([Measures].CurrentMember, [Flow].[KeyFlow].&[P])));
END SCOPE;

/*Calculate again for the flow calculations to aggregate */
CALCULATE;

/*Scope Total Traffic */
SCOPE([Measure Type].[Measure Type].[Measure].&[14]);
[Measures].[Dealership Measure Value]= [Measure Type].[Measure Type].[Measure].&[1] + [Measure Type].[Measure Type].[Measure].&[2] + [Measure Type].[Measure Type].[Measure].&[3];
END SCOPE;

/*Scope %Leads From Traffic */
SCOPE([Measure Type].[Measure Type].[Measure].&[16]);
[Measures].[Dealership Measure Value]=
CASE [Measure Type].[Measure Type].[Measure].&[14]
WHEN 0 THEN null
WHEN null THEN null
ELSE [Measure Type].[Measure Type].[Measure].&[4]/[Measure Type].[Measure Type].[Measure].&[14]
END;
END SCOPE;

|||IN THIs MDX Script, the second CALCULATE does nothing and can be safely removed with either SP2 or SP1 or RTM. The problem is somewhere else. It does look like a regression in SP2 to me, because according to this script, it really should compute percentages after MTD and YTD. I recommend opening a case with product support.|||

Just to add to the issue.

The percentages are not even added up. I am not sure what values it is calculating. We will raise a call with support.

Punita

|||I am having a problem with MTD() and YTD() after installing SP2. I have had to repair alot of queries since we went to SP2. I replaced the MTD and YTD functions with the PeriodsToDate() function and I was back in business. Hope that helps.

Having 2 CALCULATE statements no longer works after installing SP2!!!

I received the following email from a collegue of mine:

I think we may have found an issue with SQL SERVER 2005 SP2

1. Some of the Analysis Services Properties when you click the advanced tab are missing!

2. The MDX scrip behaviour is not the same.

In our cubes we had two CALCULATE statements - first to aggregate the data as normal, then some code to calculate MTD and YTD figures and then another CALCULATE statement. This enabled us to calculate percentages later on in the script without having to sum up all the data at leaf levels first.

With SP2, this no longer works!!! All our percentages are now being added up (like all other measures) which is clearly wrong!

I am not sure how to deal with this. Do we need to inform the clients not to upgrade to SP2 OR can Microsoft resolve this?

Any ideas?

Actually, two CALCULATE statements are the same as one CALCULATE statement w.r.t. aggregating data. The only difference could be if you have unary operators/custom rollups etc. So I suspect the use of two CALCULATE in your scenario was redundant. There is probably something else going on. If you will paste your MDX Script here, perhaps somebody will be able to figure it out.|||Thanks for the reply Mosha. I will get my collegue to post an example.|||

Hello Mosha,

Attached is the MDX Script.

All it does is - FIRST calculates MTD and YTD values

Then calcualtes some percentages.

Prior to Service Pack 2, these percentage calculations displayed correct number when MTD was selected on the FLOW dimension.

Post Service Pack 2, its seems that the percentages are getting calculated first and then the MTD logic is being applied resulting in the addition of all percentages from the lowest level which is not the desired behaviour.

I have tried removing the second calculate statement and it still does not work in sp2.

thanks for your help.

/*
The CALCULATE command controls the aggregation of leaf cells in the cube.
If the CALCULATE command is deleted or modified, the data within the cube is affected.
You should edit this command only if you manually specify how the cube is aggregated.
*/
CALCULATE;

/* Calculate Values for Flow Dimension */
SCOPE([Flow].[KeyFlow].&[MTD]);
THIS = (Sum(PeriodsToDate([Period].[Calendar].[Month],[Period].[Calendar].CurrentMember),([Measures].CurrentMember, [Flow].[KeyFlow].&[P])));
END SCOPE;

SCOPE([Flow].[KeyFlow].&[YTD]);
THIS = (Sum(PeriodsToDate([Period].[Calendar].[Year],[Period].[Calendar].CurrentMember),([Measures].CurrentMember, [Flow].[KeyFlow].&[P])));
END SCOPE;

/*Calculate again for the flow calculations to aggregate */
CALCULATE;

/*Scope Total Traffic */
SCOPE([Measure Type].[Measure Type].[Measure].&[14]);
[Measures].[Dealership Measure Value]= [Measure Type].[Measure Type].[Measure].&[1] + [Measure Type].[Measure Type].[Measure].&[2] + [Measure Type].[Measure Type].[Measure].&[3];
END SCOPE;

/*Scope %Leads From Traffic */
SCOPE([Measure Type].[Measure Type].[Measure].&[16]);
[Measures].[Dealership Measure Value]=
CASE [Measure Type].[Measure Type].[Measure].&[14]
WHEN 0 THEN null
WHEN null THEN null
ELSE [Measure Type].[Measure Type].[Measure].&[4]/[Measure Type].[Measure Type].[Measure].&[14]
END;
END SCOPE;

|||IN THIs MDX Script, the second CALCULATE does nothing and can be safely removed with either SP2 or SP1 or RTM. The problem is somewhere else. It does look like a regression in SP2 to me, because according to this script, it really should compute percentages after MTD and YTD. I recommend opening a case with product support.|||

Just to add to the issue.

The percentages are not even added up. I am not sure what values it is calculating. We will raise a call with support.

Punita

|||I am having a problem with MTD() and YTD() after installing SP2. I have had to repair alot of queries since we went to SP2. I replaced the MTD and YTD functions with the PeriodsToDate() function and I was back in business. Hope that helps.

Having 2 CALCULATE statements no longer works after installing SP2!!!

I received the following email from a collegue of mine:

I think we may have found an issue with SQL SERVER 2005 SP2

1. Some of the Analysis Services Properties when you click the advanced tab are missing!

2. The MDX scrip behaviour is not the same.

In our cubes we had two CALCULATE statements - first to aggregate the data as normal, then some code to calculate MTD and YTD figures and then another CALCULATE statement. This enabled us to calculate percentages later on in the script without having to sum up all the data at leaf levels first.

With SP2, this no longer works!!! All our percentages are now being added up (like all other measures) which is clearly wrong!

I am not sure how to deal with this. Do we need to inform the clients not to upgrade to SP2 OR can Microsoft resolve this?

Any ideas?

Actually, two CALCULATE statements are the same as one CALCULATE statement w.r.t. aggregating data. The only difference could be if you have unary operators/custom rollups etc. So I suspect the use of two CALCULATE in your scenario was redundant. There is probably something else going on. If you will paste your MDX Script here, perhaps somebody will be able to figure it out.|||Thanks for the reply Mosha. I will get my collegue to post an example.|||

Hello Mosha,

Attached is the MDX Script.

All it does is - FIRST calculates MTD and YTD values

Then calcualtes some percentages.

Prior to Service Pack 2, these percentage calculations displayed correct number when MTD was selected on the FLOW dimension.

Post Service Pack 2, its seems that the percentages are getting calculated first and then the MTD logic is being applied resulting in the addition of all percentages from the lowest level which is not the desired behaviour.

I have tried removing the second calculate statement and it still does not work in sp2.

thanks for your help.

/*
The CALCULATE command controls the aggregation of leaf cells in the cube.
If the CALCULATE command is deleted or modified, the data within the cube is affected.
You should edit this command only if you manually specify how the cube is aggregated.
*/
CALCULATE;

/* Calculate Values for Flow Dimension */
SCOPE([Flow].[KeyFlow].&[MTD]);
THIS = (Sum(PeriodsToDate([Period].[Calendar].[Month],[Period].[Calendar].CurrentMember),([Measures].CurrentMember, [Flow].[KeyFlow].&[P])));
END SCOPE;

SCOPE([Flow].[KeyFlow].&[YTD]);
THIS = (Sum(PeriodsToDate([Period].[Calendar].[Year],[Period].[Calendar].CurrentMember),([Measures].CurrentMember, [Flow].[KeyFlow].&[P])));
END SCOPE;

/*Calculate again for the flow calculations to aggregate */
CALCULATE;

/*Scope Total Traffic */
SCOPE([Measure Type].[Measure Type].[Measure].&[14]);
[Measures].[Dealership Measure Value]= [Measure Type].[Measure Type].[Measure].&[1] + [Measure Type].[Measure Type].[Measure].&[2] + [Measure Type].[Measure Type].[Measure].&[3];
END SCOPE;

/*Scope %Leads From Traffic */
SCOPE([Measure Type].[Measure Type].[Measure].&[16]);
[Measures].[Dealership Measure Value]=
CASE [Measure Type].[Measure Type].[Measure].&[14]
WHEN 0 THEN null
WHEN null THEN null
ELSE [Measure Type].[Measure Type].[Measure].&[4]/[Measure Type].[Measure Type].[Measure].&[14]
END;
END SCOPE;

|||IN THIs MDX Script, the second CALCULATE does nothing and can be safely removed with either SP2 or SP1 or RTM. The problem is somewhere else. It does look like a regression in SP2 to me, because according to this script, it really should compute percentages after MTD and YTD. I recommend opening a case with product support.|||

Just to add to the issue.

The percentages are not even added up. I am not sure what values it is calculating. We will raise a call with support.

Punita

|||I am having a problem with MTD() and YTD() after installing SP2. I have had to repair alot of queries since we went to SP2. I replaced the MTD and YTD functions with the PeriodsToDate() function and I was back in business. Hope that helps.

Having 2 CALCULATE statements no longer works after installing SP2!!!

I received the following email from a collegue of mine:

I think we may have found an issue with SQL SERVER 2005 SP2

1. Some of the Analysis Services Properties when you click the advanced tab are missing!

2. The MDX scrip behaviour is not the same.

In our cubes we had two CALCULATE statements - first to aggregate the data as normal, then some code to calculate MTD and YTD figures and then another CALCULATE statement. This enabled us to calculate percentages later on in the script without having to sum up all the data at leaf levels first.

With SP2, this no longer works!!! All our percentages are now being added up (like all other measures) which is clearly wrong!

I am not sure how to deal with this. Do we need to inform the clients not to upgrade to SP2 OR can Microsoft resolve this?

Any ideas?

Actually, two CALCULATE statements are the same as one CALCULATE statement w.r.t. aggregating data. The only difference could be if you have unary operators/custom rollups etc. So I suspect the use of two CALCULATE in your scenario was redundant. There is probably something else going on. If you will paste your MDX Script here, perhaps somebody will be able to figure it out.|||Thanks for the reply Mosha. I will get my collegue to post an example.|||

Hello Mosha,

Attached is the MDX Script.

All it does is - FIRST calculates MTD and YTD values

Then calcualtes some percentages.

Prior to Service Pack 2, these percentage calculations displayed correct number when MTD was selected on the FLOW dimension.

Post Service Pack 2, its seems that the percentages are getting calculated first and then the MTD logic is being applied resulting in the addition of all percentages from the lowest level which is not the desired behaviour.

I have tried removing the second calculate statement and it still does not work in sp2.

thanks for your help.

/*
The CALCULATE command controls the aggregation of leaf cells in the cube.
If the CALCULATE command is deleted or modified, the data within the cube is affected.
You should edit this command only if you manually specify how the cube is aggregated.
*/
CALCULATE;

/* Calculate Values for Flow Dimension */
SCOPE([Flow].[KeyFlow].&[MTD]);
THIS = (Sum(PeriodsToDate([Period].[Calendar].[Month],[Period].[Calendar].CurrentMember),([Measures].CurrentMember, [Flow].[KeyFlow].&[P])));
END SCOPE;

SCOPE([Flow].[KeyFlow].&[YTD]);
THIS = (Sum(PeriodsToDate([Period].[Calendar].[Year],[Period].[Calendar].CurrentMember),([Measures].CurrentMember, [Flow].[KeyFlow].&[P])));
END SCOPE;

/*Calculate again for the flow calculations to aggregate */
CALCULATE;

/*Scope Total Traffic */
SCOPE([Measure Type].[Measure Type].[Measure].&[14]);
[Measures].[Dealership Measure Value]= [Measure Type].[Measure Type].[Measure].&[1] + [Measure Type].[Measure Type].[Measure].&[2] + [Measure Type].[Measure Type].[Measure].&[3];
END SCOPE;

/*Scope %Leads From Traffic */
SCOPE([Measure Type].[Measure Type].[Measure].&[16]);
[Measures].[Dealership Measure Value]=
CASE [Measure Type].[Measure Type].[Measure].&[14]
WHEN 0 THEN null
WHEN null THEN null
ELSE [Measure Type].[Measure Type].[Measure].&[4]/[Measure Type].[Measure Type].[Measure].&[14]
END;
END SCOPE;

|||IN THIs MDX Script, the second CALCULATE does nothing and can be safely removed with either SP2 or SP1 or RTM. The problem is somewhere else. It does look like a regression in SP2 to me, because according to this script, it really should compute percentages after MTD and YTD. I recommend opening a case with product support.|||

Just to add to the issue.

The percentages are not even added up. I am not sure what values it is calculating. We will raise a call with support.

Punita

|||I am having a problem with MTD() and YTD() after installing SP2. I have had to repair alot of queries since we went to SP2. I replaced the MTD and YTD functions with the PeriodsToDate() function and I was back in business. Hope that helps.sql

have problem with service broker please help me

i'm new with service broker and need to develop mail site to send mass email and decide to use sevice broker i'm make aqueue ,sevice and all function for run service borker and creat databasie mail profile

then test it but it's don't work please help me to fine what's the problem ?

thanks very mcuh for every one read my request and response to me thanks very mcuh

Can you be more specific? What error messages do you see in the initiator queue, target queue and/or transmission queue?

Rushi

|||

ok thankx for your responce

but it's give me -6 what don't it mean i don't know

have problem with service broker please help me

i'm new with service broker and need to develop mail site to send mass email and decide to use sevice broker i'm make aqueue ,sevice and all function for run service borker and creat databasie mail profile

then test it but it's don't work please help me to fine what's the problem ?

thanks very mcuh for every one read my request and response to me thanks very mcuh

Can you be more specific? What error messages do you see in the initiator queue, target queue and/or transmission queue?

Rushi

|||

ok thankx for your responce

but it's give me -6 what don't it mean i don't know

sql

Handling Bounced Emails

We use CDonts to send email from SQL 2k & IIS 6.0. All the email is "From:
server@.myserver.com", it has a "Reply To: ourcustomer@.thieremail.com" that
is the user we are sending on behalf of, so to speak.
I need to process undeliverable emails (bounced email is the term yes?).
Looking for suggestions, using ASP or SQL is preferred.
Thanks,
Dan Foxley
Hi
If you are not returning the emails to a profile that is set up so that SQL
Server can read the mailbox then you can not do it from SQL Server. If you
can access the emails using SQL Mail, then you may be able to read then with
xp_readmail and parse and process them accordingly, unfortunately you may
have to deal with a multitude of formats.
John
"Dan Foxley" <dfoxley@.nospampacificdatavision.com> wrote in message
news:uwL30Ls5EHA.1524@.TK2MSFTNGP09.phx.gbl...
> We use CDonts to send email from SQL 2k & IIS 6.0. All the email is
> "From: server@.myserver.com", it has a "Reply To:
> ourcustomer@.thieremail.com" that is the user we are sending on behalf of,
> so to speak.
> I need to process undeliverable emails (bounced email is the term yes?).
> Looking for suggestions, using ASP or SQL is preferred.
> Thanks,
> Dan Foxley
>
|||Are there options to using SQL Mail without an Exchange server? Looks like
SQLiMail in SQL 2005 will not use a MAPI client.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:OrhAbMt5EHA.2600@.TK2MSFTNGP09.phx.gbl...
> Hi
> If you are not returning the emails to a profile that is set up so that
> SQL Server can read the mailbox then you can not do it from SQL Server. If
> you can access the emails using SQL Mail, then you may be able to read
> then with xp_readmail and parse and process them accordingly,
> unfortunately you may have to deal with a multitude of formats.
> John
> "Dan Foxley" <dfoxley@.nospampacificdatavision.com> wrote in message
> news:uwL30Ls5EHA.1524@.TK2MSFTNGP09.phx.gbl...
>
|||Hi
SQLMail can be configured to use a non-exchange email account, but I have
never done it. Looking at CDONTS you can access a SMTP login (logonSMTP)
http://msdn.microsoft.com/library/de...s_library_.asp
and from there access the inbox and messages within it.
John
"Dan Foxley" <dfoxley@.nospampacificdatavision.com> wrote in message
news:%23hUyOZu5EHA.4040@.TK2MSFTNGP14.phx.gbl...
> Are there options to using SQL Mail without an Exchange server? Looks
> like SQLiMail in SQL 2005 will not use a MAPI client.
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:OrhAbMt5EHA.2600@.TK2MSFTNGP09.phx.gbl...
>
|||This seems like the solution. I've heard of VERP Variable Envelope Return
Path, mainly used in the Unix world for prepending each email recipient with
a unique "From:", have you seem implementations using SQL?
Thanks,
Dan
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:%23s2NQ3x5EHA.3472@.TK2MSFTNGP09.phx.gbl...
> Hi
> SQLMail can be configured to use a non-exchange email account, but I have
> never done it. Looking at CDONTS you can access a SMTP login (logonSMTP)
> http://msdn.microsoft.com/library/de...s_library_.asp
> and from there access the inbox and messages within it.
> John
> "Dan Foxley" <dfoxley@.nospampacificdatavision.com> wrote in message
> news:%23hUyOZu5EHA.4040@.TK2MSFTNGP14.phx.gbl...
>
|||If I understand what you are doing.......
What you want to do is defined the "Return Path" or "Mail From" field for
the SMTP state that defines the sender address (not the author address) as
the return path for any notification (bounce) purposes.
So if you can, set the Return Path (MAIL FROM) in CDONTS, prepare a real
address that will bounce mail to a specific bin. I don't use CDON'T so I
can't tell you how to do that, but it is PART of the normal SMTP system.
This would be the MAIL FROM address in the SMTP state machine:
connection
HELO client domain name
MAIL FROM: return address
RCPT TO: recipient address
DATA
email body
QUIT
It is not the RFC 822 header fields "From: " or "Reply-To:" These are
completely meaningless when it comes to the transport system. Those are
"user fields." Reply-To: just tells the user where to send a reply to. It
is not used for bouncing mail.
Once you do this, then you can use some IIS Event hook to trigger inbound
mail handling for a specific address.
Hector Santos
WINSERVER "Wildcat! Interactive Net Server"
support: http://www.winserver.com
sales: http://www.santronics.com
"Dan Foxley" <dfoxley@.nospampacificdatavision.com> wrote in message
news:uwL30Ls5EHA.1524@.TK2MSFTNGP09.phx.gbl...
> We use CDonts to send email from SQL 2k & IIS 6.0. All the email is
"From:
> server@.myserver.com", it has a "Reply To: ourcustomer@.thieremail.com" that
> is the user we are sending on behalf of, so to speak.
> I need to process undeliverable emails (bounced email is the term yes?).
> Looking for suggestions, using ASP or SQL is preferred.
> Thanks,
> Dan Foxley
>
|||Hi
CDONTS and the processing of SMTP mail is not really anything to do with SQL
Server. Creating unique return addresses should not be that difficult, but
handled the returned emails is a funtion of your mail server. I think would
still have to parse the content of the email to try and determine why it was
returned.
John
"Dan Foxley" <dfoxley@.nospampacificdatavision.com> wrote in message
news:OQeJJk55EHA.3472@.TK2MSFTNGP09.phx.gbl...
> This seems like the solution. I've heard of VERP Variable Envelope Return
> Path, mainly used in the Unix world for prepending each email recipient
> with a unique "From:", have you seem implementations using SQL?
> Thanks,
> Dan
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:%23s2NQ3x5EHA.3472@.TK2MSFTNGP09.phx.gbl...
>

Handling Bounced Emails

We use CDonts to send email from SQL 2k & IIS 6.0. All the email is "From:
server@.myserver.com", it has a "Reply To: ourcustomer@.thieremail.com" that
is the user we are sending on behalf of, so to speak.
I need to process undeliverable emails (bounced email is the term yes?).
Looking for suggestions, using ASP or SQL is preferred.
Thanks,
Dan FoxleyHi
If you are not returning the emails to a profile that is set up so that SQL
Server can read the mailbox then you can not do it from SQL Server. If you
can access the emails using SQL Mail, then you may be able to read then with
xp_readmail and parse and process them accordingly, unfortunately you may
have to deal with a multitude of formats.
John
"Dan Foxley" <dfoxley@.nospampacificdatavision.com> wrote in message
news:uwL30Ls5EHA.1524@.TK2MSFTNGP09.phx.gbl...
> We use CDonts to send email from SQL 2k & IIS 6.0. All the email is
> "From: server@.myserver.com", it has a "Reply To:
> ourcustomer@.thieremail.com" that is the user we are sending on behalf of,
> so to speak.
> I need to process undeliverable emails (bounced email is the term yes?).
> Looking for suggestions, using ASP or SQL is preferred.
> Thanks,
> Dan Foxley
>|||Are there options to using SQL Mail without an Exchange server? Looks like
SQLiMail in SQL 2005 will not use a MAPI client.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:OrhAbMt5EHA.2600@.TK2MSFTNGP09.phx.gbl...
> Hi
> If you are not returning the emails to a profile that is set up so that
> SQL Server can read the mailbox then you can not do it from SQL Server. If
> you can access the emails using SQL Mail, then you may be able to read
> then with xp_readmail and parse and process them accordingly,
> unfortunately you may have to deal with a multitude of formats.
> John
> "Dan Foxley" <dfoxley@.nospampacificdatavision.com> wrote in message
> news:uwL30Ls5EHA.1524@.TK2MSFTNGP09.phx.gbl...
>> We use CDonts to send email from SQL 2k & IIS 6.0. All the email is
>> "From: server@.myserver.com", it has a "Reply To:
>> ourcustomer@.thieremail.com" that is the user we are sending on behalf of,
>> so to speak.
>> I need to process undeliverable emails (bounced email is the term yes?).
>> Looking for suggestions, using ASP or SQL is preferred.
>> Thanks,
>> Dan Foxley
>|||Hi
SQLMail can be configured to use a non-exchange email account, but I have
never done it. Looking at CDONTS you can access a SMTP login (logonSMTP)
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdo/html/_denali_session_object_cdonts_library_.asp
and from there access the inbox and messages within it.
John
"Dan Foxley" <dfoxley@.nospampacificdatavision.com> wrote in message
news:%23hUyOZu5EHA.4040@.TK2MSFTNGP14.phx.gbl...
> Are there options to using SQL Mail without an Exchange server? Looks
> like SQLiMail in SQL 2005 will not use a MAPI client.
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:OrhAbMt5EHA.2600@.TK2MSFTNGP09.phx.gbl...
>> Hi
>> If you are not returning the emails to a profile that is set up so that
>> SQL Server can read the mailbox then you can not do it from SQL Server.
>> If you can access the emails using SQL Mail, then you may be able to read
>> then with xp_readmail and parse and process them accordingly,
>> unfortunately you may have to deal with a multitude of formats.
>> John
>> "Dan Foxley" <dfoxley@.nospampacificdatavision.com> wrote in message
>> news:uwL30Ls5EHA.1524@.TK2MSFTNGP09.phx.gbl...
>> We use CDonts to send email from SQL 2k & IIS 6.0. All the email is
>> "From: server@.myserver.com", it has a "Reply To:
>> ourcustomer@.thieremail.com" that is the user we are sending on behalf
>> of, so to speak.
>> I need to process undeliverable emails (bounced email is the term yes?).
>> Looking for suggestions, using ASP or SQL is preferred.
>> Thanks,
>> Dan Foxley
>>
>|||This seems like the solution. I've heard of VERP Variable Envelope Return
Path, mainly used in the Unix world for prepending each email recipient with
a unique "From:", have you seem implementations using SQL?
Thanks,
Dan
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:%23s2NQ3x5EHA.3472@.TK2MSFTNGP09.phx.gbl...
> Hi
> SQLMail can be configured to use a non-exchange email account, but I have
> never done it. Looking at CDONTS you can access a SMTP login (logonSMTP)
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdo/html/_denali_session_object_cdonts_library_.asp
> and from there access the inbox and messages within it.
> John
> "Dan Foxley" <dfoxley@.nospampacificdatavision.com> wrote in message
> news:%23hUyOZu5EHA.4040@.TK2MSFTNGP14.phx.gbl...
>> Are there options to using SQL Mail without an Exchange server? Looks
>> like SQLiMail in SQL 2005 will not use a MAPI client.
>>
>> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
>> news:OrhAbMt5EHA.2600@.TK2MSFTNGP09.phx.gbl...
>> Hi
>> If you are not returning the emails to a profile that is set up so that
>> SQL Server can read the mailbox then you can not do it from SQL Server.
>> If you can access the emails using SQL Mail, then you may be able to
>> read then with xp_readmail and parse and process them accordingly,
>> unfortunately you may have to deal with a multitude of formats.
>> John
>> "Dan Foxley" <dfoxley@.nospampacificdatavision.com> wrote in message
>> news:uwL30Ls5EHA.1524@.TK2MSFTNGP09.phx.gbl...
>> We use CDonts to send email from SQL 2k & IIS 6.0. All the email is
>> "From: server@.myserver.com", it has a "Reply To:
>> ourcustomer@.thieremail.com" that is the user we are sending on behalf
>> of, so to speak.
>> I need to process undeliverable emails (bounced email is the term
>> yes?). Looking for suggestions, using ASP or SQL is preferred.
>> Thanks,
>> Dan Foxley
>>
>>
>|||If I understand what you are doing.......
What you want to do is defined the "Return Path" or "Mail From" field for
the SMTP state that defines the sender address (not the author address) as
the return path for any notification (bounce) purposes.
So if you can, set the Return Path (MAIL FROM) in CDONTS, prepare a real
address that will bounce mail to a specific bin. I don't use CDON'T so I
can't tell you how to do that, but it is PART of the normal SMTP system.
This would be the MAIL FROM address in the SMTP state machine:
connection
HELO client domain name
MAIL FROM: return address
RCPT TO: recipient address
DATA
email body
QUIT
It is not the RFC 822 header fields "From: " or "Reply-To:" These are
completely meaningless when it comes to the transport system. Those are
"user fields." Reply-To: just tells the user where to send a reply to. It
is not used for bouncing mail.
Once you do this, then you can use some IIS Event hook to trigger inbound
mail handling for a specific address.
--
Hector Santos
WINSERVER "Wildcat! Interactive Net Server"
support: http://www.winserver.com
sales: http://www.santronics.com
"Dan Foxley" <dfoxley@.nospampacificdatavision.com> wrote in message
news:uwL30Ls5EHA.1524@.TK2MSFTNGP09.phx.gbl...
> We use CDonts to send email from SQL 2k & IIS 6.0. All the email is
"From:
> server@.myserver.com", it has a "Reply To: ourcustomer@.thieremail.com" that
> is the user we are sending on behalf of, so to speak.
> I need to process undeliverable emails (bounced email is the term yes?).
> Looking for suggestions, using ASP or SQL is preferred.
> Thanks,
> Dan Foxley
>|||Hi
CDONTS and the processing of SMTP mail is not really anything to do with SQL
Server. Creating unique return addresses should not be that difficult, but
handled the returned emails is a funtion of your mail server. I think would
still have to parse the content of the email to try and determine why it was
returned.
John
"Dan Foxley" <dfoxley@.nospampacificdatavision.com> wrote in message
news:OQeJJk55EHA.3472@.TK2MSFTNGP09.phx.gbl...
> This seems like the solution. I've heard of VERP Variable Envelope Return
> Path, mainly used in the Unix world for prepending each email recipient
> with a unique "From:", have you seem implementations using SQL?
> Thanks,
> Dan
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:%23s2NQ3x5EHA.3472@.TK2MSFTNGP09.phx.gbl...
>> Hi
>> SQLMail can be configured to use a non-exchange email account, but I have
>> never done it. Looking at CDONTS you can access a SMTP login (logonSMTP)
>> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdo/html/_denali_session_object_cdonts_library_.asp
>> and from there access the inbox and messages within it.
>> John
>> "Dan Foxley" <dfoxley@.nospampacificdatavision.com> wrote in message
>> news:%23hUyOZu5EHA.4040@.TK2MSFTNGP14.phx.gbl...
>> Are there options to using SQL Mail without an Exchange server? Looks
>> like SQLiMail in SQL 2005 will not use a MAPI client.
>>
>> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
>> news:OrhAbMt5EHA.2600@.TK2MSFTNGP09.phx.gbl...
>> Hi
>> If you are not returning the emails to a profile that is set up so that
>> SQL Server can read the mailbox then you can not do it from SQL Server.
>> If you can access the emails using SQL Mail, then you may be able to
>> read then with xp_readmail and parse and process them accordingly,
>> unfortunately you may have to deal with a multitude of formats.
>> John
>> "Dan Foxley" <dfoxley@.nospampacificdatavision.com> wrote in message
>> news:uwL30Ls5EHA.1524@.TK2MSFTNGP09.phx.gbl...
>> We use CDonts to send email from SQL 2k & IIS 6.0. All the email is
>> "From: server@.myserver.com", it has a "Reply To:
>> ourcustomer@.thieremail.com" that is the user we are sending on behalf
>> of, so to speak.
>> I need to process undeliverable emails (bounced email is the term
>> yes?). Looking for suggestions, using ASP or SQL is preferred.
>> Thanks,
>> Dan Foxley
>>
>>
>>
>