ATL smart pointers with SQL-DMO

I can't believe that nobody knows this? And what does the "commitment to
respond within two business days" mean? I am reposting this after three days
:
According to the comments in Smartptr.cpp (the SQL-DMO smartpointer sample
application), it should be possible to use CComPtr or CComQIPtr instead.
However, I get the error message: "error C2787: 'IWSQLDMOApplication' : no
GUID has been associated with this object" when I try to compile the
following:
#include <initguid.h>
#include "C:\Program Files\Microsoft SQL
Server\80\Tools\Devtools\Include\Sqldmoi
d.h"
#include "C:\Program Files\Microsoft SQL
Server\80\Tools\Devtools\Include\Sqldmo.h"
void CSQLDMOManager::FillDatabaseList()
{
CComPtr<ISQLDMOApplication> pSQLDMOApplication;
pSQLDMOApplication. CoCreateInstance(CLSID_SQLDMOApplication
); // error here!
What do I need to do to make this work?
Thanks,
-PeterFor the "commitment to respond within two business days", you must be a
registered MSDN user and use your registered MSDN email account to post your
message. Maybe it's just that jpstewart@.discussion.microsoft.com is not
your registered MSDN email account.
S. L.
"jpstewart" <jpstewart@.discussions.microsoft.com> wrote in message
news:2E8ED74C-1873-493E-9CB5-4F821A642E97@.microsoft.com...
>I can't believe that nobody knows this? And what does the "commitment to
> respond within two business days" mean? I am reposting this after three
> days:
> According to the comments in Smartptr.cpp (the SQL-DMO smartpointer sample
> application), it should be possible to use CComPtr or CComQIPtr instead.
> However, I get the error message: "error C2787: 'IWSQLDMOApplication' : no
> GUID has been associated with this object" when I try to compile the
> following:
> #include <initguid.h>
> #include "C:\Program Files\Microsoft SQL
> Server\80\Tools\Devtools\Include\Sqldmoi
d.h"
> #include "C:\Program Files\Microsoft SQL
> Server\80\Tools\Devtools\Include\Sqldmo.h"
> void CSQLDMOManager::FillDatabaseList()
> {
> CComPtr<ISQLDMOApplication> pSQLDMOApplication;
> pSQLDMOApplication. CoCreateInstance(CLSID_SQLDMOApplication
); // error
> here!
> What do I need to do to make this work?
> Thanks,
> -Peter
>|||Hmmm... Maybe I've missed something somewhere, but does this mean that I
can't both take advantage of the spam protection of a a false display
e-mail address and get my answers within two days?
-Peter
P.S. And I am a registered MSDN user.
"Sylvain Lafontaine" wrote:

> For the "commitment to respond within two business days", you must be a
> registered MSDN user and use your registered MSDN email account to post yo
ur
> message. Maybe it's just that jpstewart@.discussion.microsoft.com is not
> your registered MSDN email account.
> S. L.
> "jpstewart" <jpstewart@.discussions.microsoft.com> wrote in message
> news:2E8ED74C-1873-493E-9CB5-4F821A642E97@.microsoft.com...
>
>|||How else do you expect MS to know you are a MSDN subscriber or not? I
understand you not wanting to get spam but short of them being mind
readers<g> they do need a way to identify you. Unfortunately I don't know
the answer to your original question or I would have responded sooner.
Hopefully someone from MSDN team will see this and respond to you.
Andrew J. Kelly SQL MVP
"jpstewart" <jpstewart@.discussions.microsoft.com> wrote in message
news:AE1783B2-D226-479D-B2D0-3D0922A49FB6@.microsoft.com...
> Hmmm... Maybe I've missed something somewhere, but does this mean that I
> can't both take advantage of the spam protection of a a false display
> e-mail address and get my answers within two days?
> -Peter
> P.S. And I am a registered MSDN user.
> "Sylvain Lafontaine" wrote:
>|||Go to the MSDN Subscribers Download section, then click on the word "here"
that you will find at the end of the following section:
...
Get Unlimited Free PSS Support for Technical Issues!
Unlimited free support is available now to MSDN subscribers via the MSDN
Managed Newsgroups. In over 200 developer newsgroups, Microsoft will assure
that you receive a response to your posts within 2 business days. This
services is included as a benefit of your active subscription. You can get
started by registering here.
...
From there, register your personal false display e-mail address for MSDN
subscribers. I suggest that under Outlook Express, you create a second
account to use with this e-mail address so that only your must important
posting will get monitored.
S. L.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23yBCL24DFHA.464@.TK2MSFTNGP15.phx.gbl...
> How else do you expect MS to know you are a MSDN subscriber or not? I
> understand you not wanting to get spam but short of them being mind
> readers<g> they do need a way to identify you. Unfortunately I don't know
> the answer to your original question or I would have responded sooner.
> Hopefully someone from MSDN team will see this and respond to you.
> --
> Andrew J. Kelly SQL MVP
>
> "jpstewart" <jpstewart@.discussions.microsoft.com> wrote in message
> news:AE1783B2-D226-479D-B2D0-3D0922A49FB6@.microsoft.com...
>|||Cool. I din't know they had that. Thanks
Andrew J. Kelly SQL MVP
"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:Oxir1v5DFHA.3256@.tk2msftngp13.phx.gbl...
> Go to the MSDN Subscribers Download section, then click on the word "here"
> that you will find at the end of the following section:
> ...
> Get Unlimited Free PSS Support for Technical Issues!
> Unlimited free support is available now to MSDN subscribers via the MSDN
> Managed Newsgroups. In over 200 developer newsgroups, Microsoft will
> assure that you receive a response to your posts within 2 business days.
> This services is included as a benefit of your active subscription. You
> can get started by registering here.
> ...
> From there, register your personal false display e-mail address for MSDN
> subscribers. I suggest that under Outlook Express, you create a second
> account to use with this e-mail address so that only your must important
> posting will get monitored.
> S. L.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23yBCL24DFHA.464@.TK2MSFTNGP15.phx.gbl...
>|||A little thing before finishing: you are better to wait one or two days
before using your new account, otherwise their system may got mixed about
you. Some people had this problem some months ago and I don't know if it
has been repaired since then.
S. L.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uuxUG87DFHA.3992@.tk2msftngp13.phx.gbl...
> Cool. I din't know they had that. Thanks
> --
> Andrew J. Kelly SQL MVP
>
> "Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
> wrote in message news:Oxir1v5DFHA.3256@.tk2msftngp13.phx.gbl...
>

Association algorithm - Importance of a rule

Can anyone tell me, how the Business íntelligence Studio calculates the importance of a rule. I can't find the formula. I know some formulas, but the result in SQL Server is completly different.

Thanks!

For rules, the importance is calculated using the following formula:

Importance (A=>B) = log ( p(a|b) / p(a|not b) )

An importance of 0 means there is no association between A and B. A positive

importance score means that the probability of B goes up when A is true. A

negative importance score means that the probability of B goes down when A

is true.

Below is an example of the correlation counts of donut and muffin derived

from a purchase database. Each cell value represents the number of

transactions. For example, 15 out of 100 transactions include a customer

purchasing both donuts and muffins.

Donut Not Donut Total

Muffin 15 5 20

Not muffin 75 5 80

Total 90 10 100

The support, probability, and importance of related itemsets and rules for

donut and muffin:

Support({Donut}) = 90

Support({Muffin}) = 20

Support ({Donut, Muffin}) = 15

Probability({Donut}) = 90/100 = 0.9

Probability({Muffin}) = 20/100 = 0.2

Probability({Donut, Muffin}) = 15/100 = 0.15

Probability(Donut|Muffin) = 15/20 = 0.75

Probability(Muffin|Donut) = 15/90 = 0.167

Importance({Donut, Muffin}) = 0.15/(0.2*0.9) = 0.833

Importance (Donut=>Muffin) = ln(Probability(Donut|Muffin)

/Probability(Donult|Not Muffin))= ln(0.8) = -0.223

Importance(Muffin=>Donut) = ln(Probability(Muffin|Donut)

/Probability(Muffin| Not Donut)) = ln(0.33) = -1.100

From the importance of the itemset {Donut, Muffin}, we can see Donut and

Muffin are negatively correlated; it is rather unlikely for someone who buys

a Muffin to also buy a Donut.

The Importance score is also known as Weight of Evidence (WOE).

|||

Hi,thanks a lot for your answer!

I recalculated the importance with your formulas and compared this with the results of the microsoft association algorithm.

Your formula for the importance is almost right, but it calculates the importance for

Muffin =>Donut and not Donut => Muffin

and it must be "log" and not "ln" !!

So at the end, this must be the right formula:

Importance(Muffin =>Donut) = log(Probability(Donut|Muffin) / Probability(Donut|Not Muffin) )

and for

Importance(Donut=> Muffin) = log(Probability(Muffin|Donut) / Probability(Muffin|Not Donut) )

UllaH

|||

Acutually at the beginning of Jamie's answer, the formular has been already there correctly:

Importance (A=>B) = log ( p(a|b) / p(a|not b) )

Regards,

|||

Importance (A=>B) = log ( p(a|b) / p(a|not b) )

It makes more sense to me if a and b are switched in the log function

Can some one point me to a Microsoft Research Paper "With all due respect to all" not just odiscussion Onions that discusses the theoretical background for calculating Rule importance?

Musa

|||Dear all,

I try to run the "donuts and muffins" example by using SQL 2005 BI but I didnot have the results as the formula you instruct (Importance (A=>B) = log ( p(a|b) / p(a|not b) ) ). Please explain me more detail.

probability importance
0.938 0.105302438 F3 = NotMuffin -> F2 = Donut








0.833 0.218055761 F2 = Donut -> F3 = NotMuffin


0.75 -0.105302438 F3 = Muffin -> F2 = Donut


0.5 -0.218055761 F2 = NotDonut -> F3 = NotMuffin


0.5 0.458637849 F2 = NotDonut -> F3 = Muffin

Thank you very much.
Your truthly,

Association algorithm - Importance of a rule

Can anyone tell me, how the Business íntelligence Studio calculates the importance of a rule. I can't find the formula. I know some formulas, but the result in SQL Server is completly different.

Thanks!

For rules, the importance is calculated using the following formula:

Importance (A=>B) = log ( p(a|b) / p(a|not b) )

An importance of 0 means there is no association between A and B. A positive

importance score means that the probability of B goes up when A is true. A

negative importance score means that the probability of B goes down when A

is true.

Below is an example of the correlation counts of donut and muffin derived

from a purchase database. Each cell value represents the number of

transactions. For example, 15 out of 100 transactions include a customer

purchasing both donuts and muffins.

Donut Not Donut Total

Muffin 15 5 20

Not muffin 75 5 80

Total 90 10 100

The support, probability, and importance of related itemsets and rules for

donut and muffin:

Support({Donut}) = 90

Support({Muffin}) = 20

Support ({Donut, Muffin}) = 15

Probability({Donut}) = 90/100 = 0.9

Probability({Muffin}) = 20/100 = 0.2

Probability({Donut, Muffin}) = 15/100 = 0.15

Probability(Donut|Muffin) = 15/20 = 0.75

Probability(Muffin|Donut) = 15/90 = 0.167

Importance({Donut, Muffin}) = 0.15/(0.2*0.9) = 0.833

Importance (Donut=>Muffin) = ln(Probability(Donut|Muffin)

/Probability(Donult|Not Muffin))= ln(0.8) = -0.223

Importance(Muffin=>Donut) = ln(Probability(Muffin|Donut)

/Probability(Muffin| Not Donut)) = ln(0.33) = -1.100

From the importance of the itemset {Donut, Muffin}, we can see Donut and

Muffin are negatively correlated; it is rather unlikely for someone who buys

a Muffin to also buy a Donut.

The Importance score is also known as Weight of Evidence (WOE).

|||

Hi,thanks a lot for your answer!

I recalculated the importance with your formulas and compared this with the results of the microsoft association algorithm.

Your formula for the importance is almost right, but it calculates the importance for

Muffin =>Donut and not Donut => Muffin

and it must be "log" and not "ln" !!

So at the end, this must be the right formula:

Importance(Muffin =>Donut) = log(Probability(Donut|Muffin) / Probability(Donut|Not Muffin) )

and for

Importance(Donut=> Muffin) = log(Probability(Muffin|Donut) / Probability(Muffin|Not Donut) )

UllaH

|||

Acutually at the beginning of Jamie's answer, the formular has been already there correctly:

Importance (A=>B) = log ( p(a|b) / p(a|not b) )

Regards,

|||

Importance (A=>B) = log ( p(a|b) / p(a|not b) )

It makes more sense to me if a and b are switched in the log function

Can some one point me to a Microsoft Research Paper "With all due respect to all" not just odiscussion Onions that discusses the theoretical background for calculating Rule importance?

Musa

|||Dear all,

I try to run the "donuts and muffins" example by using SQL 2005 BI but I didnot have the results as the formula you instruct (Importance (A=>B) = log ( p(a|b) / p(a|not b) ) ). Please explain me more detail.

probability importance
0.938 0.105302438 F3 = NotMuffin -> F2 = Donut








0.833 0.218055761 F2 = Donut -> F3 = NotMuffin


0.75 -0.105302438 F3 = Muffin -> F2 = Donut


0.5 -0.218055761 F2 = NotDonut -> F3 = NotMuffin


0.5 0.458637849 F2 = NotDonut -> F3 = Muffin

Thank you very much.
Your truthly,

Association algorithm - Importance of a rule

Can anyone tell me, how the Business íntelligence Studio calculates the importance of a rule. I can't find the formula. I know some formulas, but the result in SQL Server is completly different.

Thanks!

For rules, the importance is calculated using the following formula:

Importance (A=>B) = log ( p(a|b) / p(a|not b) )

An importance of 0 means there is no association between A and B. A positive

importance score means that the probability of B goes up when A is true. A

negative importance score means that the probability of B goes down when A

is true.

Below is an example of the correlation counts of donut and muffin derived

from a purchase database. Each cell value represents the number of

transactions. For example, 15 out of 100 transactions include a customer

purchasing both donuts and muffins.

Donut Not Donut Total

Muffin 15 5 20

Not muffin 75 5 80

Total 90 10 100

The support, probability, and importance of related itemsets and rules for

donut and muffin:

Support({Donut}) = 90

Support({Muffin}) = 20

Support ({Donut, Muffin}) = 15

Probability({Donut}) = 90/100 = 0.9

Probability({Muffin}) = 20/100 = 0.2

Probability({Donut, Muffin}) = 15/100 = 0.15

Probability(Donut|Muffin) = 15/20 = 0.75

Probability(Muffin|Donut) = 15/90 = 0.167

Importance({Donut, Muffin}) = 0.15/(0.2*0.9) = 0.833

Importance (Donut=>Muffin) = ln(Probability(Donut|Muffin)

/Probability(Donult|Not Muffin))= ln(0.8) = -0.223

Importance(Muffin=>Donut) = ln(Probability(Muffin|Donut)

/Probability(Muffin| Not Donut)) = ln(0.33) = -1.100

From the importance of the itemset {Donut, Muffin}, we can see Donut and

Muffin are negatively correlated; it is rather unlikely for someone who buys

a Muffin to also buy a Donut.

The Importance score is also known as Weight of Evidence (WOE).

|||

Hi,thanks a lot for your answer!

I recalculated the importance with your formulas and compared this with the results of the microsoft association algorithm.

Your formula for the importance is almost right, but it calculates the importance for

Muffin =>Donut and not Donut => Muffin

and it must be "log" and not "ln" !!

So at the end, this must be the right formula:

Importance(Muffin =>Donut) = log(Probability(Donut|Muffin) / Probability(Donut|Not Muffin) )

and for

Importance(Donut=> Muffin) = log(Probability(Muffin|Donut) / Probability(Muffin|Not Donut) )

UllaH

|||

Acutually at the beginning of Jamie's answer, the formular has been already there correctly:

Importance (A=>B) = log ( p(a|b) / p(a|not b) )

Regards,

|||

Importance (A=>B) = log ( p(a|b) / p(a|not b) )

It makes more sense to me if a and b are switched in the log function

Can some one point me to a Microsoft Research Paper "With all due respect to all" not just odiscussion Onions that discusses the theoretical background for calculating Rule importance?

Musa

|||Dear all,

I try to run the "donuts and muffins" example by using SQL 2005 BI but I didnot have the results as the formula you instruct (Importance (A=>B) = log ( p(a|b) / p(a|not b) ) ). Please explain me more detail.

probability

importance

0.938

0.105302438

F3 = NotMuffin -> F2 = Donut

0.833

0.218055761

F2 = Donut -> F3 = NotMuffin

0.75

-0.105302438

F3 = Muffin -> F2 = Donut

0.5

-0.218055761

F2 = NotDonut -> F3 = NotMuffin

0.5

0.458637849

F2 = NotDonut -> F3 = Muffin

Thank you very much.
Your truthly,

Association algorithm - Importance of a rule

Can anyone tell me, how the Business íntelligence Studio calculates the importance of a rule. I can't find the formula. I know some formulas, but the result in SQL Server is completly different.

Thanks!

For rules, the importance is calculated using the following formula:

Importance (A=>B) = log ( p(a|b) / p(a|not b) )

An importance of 0 means there is no association between A and B. A positive

importance score means that the probability of B goes up when A is true. A

negative importance score means that the probability of B goes down when A

is true.

Below is an example of the correlation counts of donut and muffin derived

from a purchase database. Each cell value represents the number of

transactions. For example, 15 out of 100 transactions include a customer

purchasing both donuts and muffins.

Donut Not Donut Total

Muffin 15 5 20

Not muffin 75 5 80

Total 90 10 100

The support, probability, and importance of related itemsets and rules for

donut and muffin:

Support({Donut}) = 90

Support({Muffin}) = 20

Support ({Donut, Muffin}) = 15

Probability({Donut}) = 90/100 = 0.9

Probability({Muffin}) = 20/100 = 0.2

Probability({Donut, Muffin}) = 15/100 = 0.15

Probability(Donut|Muffin) = 15/20 = 0.75

Probability(Muffin|Donut) = 15/90 = 0.167

Importance({Donut, Muffin}) = 0.15/(0.2*0.9) = 0.833

Importance (Donut=>Muffin) = ln(Probability(Donut|Muffin)

/Probability(Donult|Not Muffin))= ln(0.8) = -0.223

Importance(Muffin=>Donut) = ln(Probability(Muffin|Donut)

/Probability(Muffin| Not Donut)) = ln(0.33) = -1.100

From the importance of the itemset {Donut, Muffin}, we can see Donut and

Muffin are negatively correlated; it is rather unlikely for someone who buys

a Muffin to also buy a Donut.

The Importance score is also known as Weight of Evidence (WOE).

|||

Hi,thanks a lot for your answer!

I recalculated the importance with your formulas and compared this with the results of the microsoft association algorithm.

Your formula for the importance is almost right, but it calculates the importance for

Muffin =>Donut and not Donut => Muffin

and it must be "log" and not "ln" !!

So at the end, this must be the right formula:

Importance(Muffin =>Donut) = log(Probability(Donut|Muffin) / Probability(Donut|Not Muffin) )

and for

Importance(Donut=> Muffin) = log(Probability(Muffin|Donut) / Probability(Muffin|Not Donut) )

UllaH

|||

Acutually at the beginning of Jamie's answer, the formular has been already there correctly:

Importance (A=>B) = log ( p(a|b) / p(a|not b) )

Regards,

|||

Importance (A=>B) = log ( p(a|b) / p(a|not b) )

It makes more sense to me if a and b are switched in the log function

Can some one point me to a Microsoft Research Paper "With all due respect to all" not just odiscussion Onions that discusses the theoretical background for calculating Rule importance?

Musa

|||Dear all,

I try to run the "donuts and muffins" example by using SQL 2005 BI but I didnot have the results as the formula you instruct (Importance (A=>B) = log ( p(a|b) / p(a|not b) ) ). Please explain me more detail.

probability importance
0.938 0.105302438 F3 = NotMuffin -> F2 = Donut








0.833 0.218055761 F2 = Donut -> F3 = NotMuffin


0.75 -0.105302438 F3 = Muffin -> F2 = Donut


0.5 -0.218055761 F2 = NotDonut -> F3 = NotMuffin


0.5 0.458637849 F2 = NotDonut -> F3 = Muffin

Thank you very much.
Your truthly,
sql

Assigning Group Numbers for millions of row

I have a table with first name, last name, SSN(social security number)
and other columns.
I want to assign group number according to this business logic.
1. Records with equal SSN and (similar first name or last name) belong
to the same group.
John Smith 1234
Smith John 1234
S John 1234
J Smith 1234
John Smith and Smith John falls in the same group Number as long as
they have similar SSN.
This is because I have a record of equal SSN but the first name and
last name is switched because of people who make error inserting last
name as first name and vice versa. John Smith and Smith John will have
equal group Name if they have equal SSN.
2. There are records with equal SSN but different first name and last
name. These belong to different group numbers.
Equal SSN doesn't guarantee equal group number, at least one of the
first name or last name should be the same. John Smith and Dan Brown
with equal SSN=1234 shouldn't fall in the same group number.
Sample data:
Id Fname lname SSN grpNum
1 John Smith 1234 1
2 Smith John 1234 1
3 S John 1234 1
4 J Smith 1234 1
5 J S 1234 1
6 Dan Brown 1234 2
7 John Smith 1111 3
I have tried this code for 65,000 rows. It took 20 minute. I have to
run it for 21 million row data. I now that this is not an efficient
code.
INSERT into temp_FnLnSSN_grp
SELECT c1.fname, c1.lname, c1.ssn AS ssn, c3.tu_id,
(SELECT 1 + count(*)
FROM distFLS AS c2
WHERE c2.ssn < c1.ssn
or (c2.ssn = c1.ssn and (substring(c2.fname,1,1) =
substring(c1.fname,1,1) or substring(c2.lname,1,1) =
substring(c1.lname,1,1)
or substring(c2.fname,1,1) =
substring(c1.lname,1,1) or substring(c2.lname,1,1) =
substring(c1.fname,1,1))
)) AS group_number
FROM distFLS AS c1
JOIN tu_people_data AS c3
ON (c1.ssn = c3.ssn and
c1.fname = c3.fname and
c1.lname= c3.lname)
dist FLS is distinct First Name, last Name and SSN table from the
people table.
I have posted part of this question, schema one w ago. Please refer
this thread.
http://groups.google.com/group/comp...6eb380b5f2e6de6Basically, this is just a query that sorts or groups on a CASE function.
However, the catch is how we want to classify different names as "similar".
I would say that two rows should be considered similar if they have the same
SSN and the names start with the same first letter. Instead of a group
number, let's do a group code which conists of those 2 characters. Since
fname and lname may be transposed, the lowest of the 2 characters will be
encoded first followed by the highest character.
fname lname SSN grpCode
-- -- -- --
J S 1234 JS
J Smith 1234 JS
S John 1234 JS
John Smith 1111 JS
John Smith 1234 JS
Smith John 1234 JS
Dan Brown 1234 BD
select lname, fname, SSN, grpCode
from
(
select
fname,
lname,
SSN,
-- Here we calculate the grpCode:
case
when left(fname,1) <= left(lname,1) then left(fname,1)
else left(lname,1)
end as grpCode
--
from
distFLS
) as x
order by
SSN,
grpCode,
fname,
lname
<jacob.dba@.gmail.com> wrote in message
news:1143482451.181115.64620@.v46g2000cwv.googlegroups.com...
>I have a table with first name, last name, SSN(social security number)
> and other columns.
> I want to assign group number according to this business logic.
> 1. Records with equal SSN and (similar first name or last name) belong
> to the same group.
> John Smith 1234
> Smith John 1234
> S John 1234
> J Smith 1234
> John Smith and Smith John falls in the same group Number as long as
> they have similar SSN.
> This is because I have a record of equal SSN but the first name and
> last name is switched because of people who make error inserting last
> name as first name and vice versa. John Smith and Smith John will have
> equal group Name if they have equal SSN.
> 2. There are records with equal SSN but different first name and last
> name. These belong to different group numbers.
> Equal SSN doesn't guarantee equal group number, at least one of the
> first name or last name should be the same. John Smith and Dan Brown
> with equal SSN=1234 shouldn't fall in the same group number.
>
> Sample data:
> Id Fname lname SSN grpNum
> 1 John Smith 1234 1
> 2 Smith John 1234 1
> 3 S John 1234 1
> 4 J Smith 1234 1
> 5 J S 1234 1
> 6 Dan Brown 1234 2
> 7 John Smith 1111 3
>
> I have tried this code for 65,000 rows. It took 20 minute. I have to
> run it for 21 million row data. I now that this is not an efficient
> code.
>
> INSERT into temp_FnLnSSN_grp
> SELECT c1.fname, c1.lname, c1.ssn AS ssn, c3.tu_id,
> (SELECT 1 + count(*)
> FROM distFLS AS c2
> WHERE c2.ssn < c1.ssn
> or (c2.ssn = c1.ssn and (substring(c2.fname,1,1) =
> substring(c1.fname,1,1) or substring(c2.lname,1,1) =
> substring(c1.lname,1,1)
> or substring(c2.fname,1,1) =
> substring(c1.lname,1,1) or substring(c2.lname,1,1) =
> substring(c1.fname,1,1))
> )) AS group_number
> FROM distFLS AS c1
> JOIN tu_people_data AS c3
> ON (c1.ssn = c3.ssn and
> c1.fname = c3.fname and
> c1.lname= c3.lname)
>
> dist FLS is distinct First Name, last Name and SSN table from the
> people table.
>
> I have posted part of this question, schema one w ago. Please refer
> this thread.
>
> http://groups.google.com/group/comp...6eb380b5f2e6de6
>|||The group code calculation returns only with one letter.
I have added this code on it.
-- Here we calculate the grpCode:
case
when left(fname,1) <= left(lname,1) then left(fname,1) +
left(lname,1)
else left(lname,1) +left(fname,1)
end as grpCode
--|||I didn't run it on my end.
Thanks.
<jacob.dba@.gmail.com> wrote in message
news:1143487218.339033.116420@.v46g2000cwv.googlegroups.com...
> The group code calculation returns only with one letter.
> I have added this code on it.
> -- Here we calculate the grpCode:
> case
> when left(fname,1) <= left(lname,1) then left(fname,1) +
> left(lname,1)
> else left(lname,1) +left(fname,1)
> end as grpCode
> --
>|||I fogot to mention that some of the records have middle name entered
in place of first name or last name.
fname mname lname ssn
John coleman smith 1234
john smith coleman 1234
john S coleman 1234
John C Smith 1234
John Smith 1234
John-coleman Smith 1234
Smith John 1234
During the grouping process I am concerned only about fname,lname,
ssn.(no need of middle name). If there is other suggestion to include
columns I am happy to accept.
I have the idea to assign groups if one of the initial of the names is
similar with the others considering that the SSN is the same. that
means if SSN is equal and if J or S or C are there as an initial in the
names, we can say they are in the same group.|||Just revise the case function as needed, but the concept is the same.
<jacob.dba@.gmail.com> wrote in message
news:1143490139.170226.286890@.g10g2000cwb.googlegroups.com...
> I fogot to mention that some of the records have middle name entered
> in place of first name or last name.
> fname mname lname ssn
> John coleman smith 1234
> john smith coleman 1234
> john S coleman 1234
> John C Smith 1234
> John Smith 1234
> John-coleman Smith 1234
> Smith John 1234
> During the grouping process I am concerned only about fname,lname,
> ssn.(no need of middle name). If there is other suggestion to include
> columns I am happy to accept.
> I have the idea to assign groups if one of the initial of the names is
> similar with the others considering that the SSN is the same. that
> means if SSN is equal and if J or S or C are there as an initial in the
> names, we can say they are in the same group.
>|||Consider using Integration Services as that tool has a Fuzzy Lookup and
Fuzzy Grouping tasks that were specifically designed for this type of work.
<jacob.dba@.gmail.com> wrote in message
news:1143482451.181115.64620@.v46g2000cwv.googlegroups.com...
>I have a table with first name, last name, SSN(social security number)
> and other columns.
> I want to assign group number according to this business logic.
> 1. Records with equal SSN and (similar first name or last name) belong
> to the same group.
> John Smith 1234
> Smith John 1234
> S John 1234
> J Smith 1234
> John Smith and Smith John falls in the same group Number as long as
> they have similar SSN.
> This is because I have a record of equal SSN but the first name and
> last name is switched because of people who make error inserting last
> name as first name and vice versa. John Smith and Smith John will have
> equal group Name if they have equal SSN.
> 2. There are records with equal SSN but different first name and last
> name. These belong to different group numbers.
> Equal SSN doesn't guarantee equal group number, at least one of the
> first name or last name should be the same. John Smith and Dan Brown
> with equal SSN=1234 shouldn't fall in the same group number.
>
> Sample data:
> Id Fname lname SSN grpNum
> 1 John Smith 1234 1
> 2 Smith John 1234 1
> 3 S John 1234 1
> 4 J Smith 1234 1
> 5 J S 1234 1
> 6 Dan Brown 1234 2
> 7 John Smith 1111 3
>
> I have tried this code for 65,000 rows. It took 20 minute. I have to
> run it for 21 million row data. I now that this is not an efficient
> code.
>
> INSERT into temp_FnLnSSN_grp
> SELECT c1.fname, c1.lname, c1.ssn AS ssn, c3.tu_id,
> (SELECT 1 + count(*)
> FROM distFLS AS c2
> WHERE c2.ssn < c1.ssn
> or (c2.ssn = c1.ssn and (substring(c2.fname,1,1) =
> substring(c1.fname,1,1) or substring(c2.lname,1,1) =
> substring(c1.lname,1,1)
> or substring(c2.fname,1,1) =
> substring(c1.lname,1,1) or substring(c2.lname,1,1) =
> substring(c1.fname,1,1))
> )) AS group_number
> FROM distFLS AS c1
> JOIN tu_people_data AS c3
> ON (c1.ssn = c3.ssn and
> c1.fname = c3.fname and
> c1.lname= c3.lname)
>
> dist FLS is distinct First Name, last Name and SSN table from the
> people table.
>
> I have posted part of this question, schema one w ago. Please refer
> this thread.
>
> http://groups.google.com/group/comp...6eb380b5f2e6de6
>

Assigning group numbers for millions of data

I have a table with first name, last name, SSN(social security number)
and other columns.
I want to assign group number according to this business logic.
1. Records with equal SSN and (similar first name or last name) belong
to the same group.
John Smith 1234
Smith John 1234
S John 1234
J Smith 1234
John Smith and Smith John falls in the same group Number as long as
they have similar SSN.
This is because I have a record of equal SSN but the first name and
last name is switched because of people who make error inserting last
name as first name and vice versa. John Smith and Smith John will have
equal group Name if they have equal SSN.
2. There are records with equal SSN but different first name and last
name. These belong to different group numbers.
Equal SSN doesn't guarantee equal group number, at least one of the
first name or last name should be the same. John Smith and Dan Brown
with equal SSN=1234 shouldn't fall in the same group number.

Sample data:
Id Fname lname SSN grpNum
1 John Smith 1234 1
2 Smith John 1234 1
3 S John 1234 1
4 J Smith 1234 1
5 J S 1234 1
6 Dan Brown 1234 2
7 John Smith 1111 3

I have tried this code for 65,000 rows. It took 20 minute. I have to
run it for 21 million row data. I now that this is not an efficient
code.

INSERT into temp_FnLnSSN_grp
SELECT c1.fname, c1.lname, c1.ssn AS ssn, c3.tu_id,
(SELECT 1 + count(*)
FROM distFLS AS c2
WHERE c2.ssn < c1.ssn
or (c2.ssn = c1.ssn and (substring(c2.fname,1,1) =
substring(c1.fname,1,1) or substring(c2.lname,1,1) =
substring(c1.lname,1,1)
or substring(c2.fname,1,1) =
substring(c1.lname,1,1) or substring(c2.lname,1,1) =
substring(c1.fname,1,1))
)) AS group_number
FROM distFLS AS c1
JOIN tu_people_data AS c3
ON (c1.ssn = c3.ssn and
c1.fname = c3.fname and
c1.lname= c3.lname)

dist FLS is distinct First Name, last Name and SSN table from the
people table.

I have posted part of this question, schema one week ago. Please refer
this thread.

http://groups.google.com/group/comp...6eb380b5f2e6de6I forgot to mention that some of the records have middle name entered
in place of first name or last name.
fname mname lname ssn

John coleman smith 1234
john smith coleman 1234
john S coleman 1234
John C Smith 1234
John Smith 1234
John-coleman Smith 1234
Smith John 1234

During the grouping process I am concerned only about fname,lname,
ssn.(no need of middle name). If there is other suggestion to include
columns I am happy to accept.
I have the idea to assign groups if one of the initial of the names is

similar with the others considering that the SSN is the same. that
means if SSN is equal and if J or S or C are there as an initial in the

names, we can say they are in the same group.

Reply

jacob.dba@.gmail.com wrote:
> I have a table with first name, last name, SSN(social security number)
> and other columns.
> I want to assign group number according to this business logic.
> 1. Records with equal SSN and (similar first name or last name) belong
> to the same group.
> John Smith 1234
> Smith John 1234
> S John 1234
> J Smith 1234
> John Smith and Smith John falls in the same group Number as long as
> they have similar SSN.
> This is because I have a record of equal SSN but the first name and
> last name is switched because of people who make error inserting last
> name as first name and vice versa. John Smith and Smith John will have
> equal group Name if they have equal SSN.
> 2. There are records with equal SSN but different first name and last
> name. These belong to different group numbers.
> Equal SSN doesn't guarantee equal group number, at least one of the
> first name or last name should be the same. John Smith and Dan Brown
> with equal SSN=1234 shouldn't fall in the same group number.
> Sample data:
> Id Fname lname SSN grpNum
> 1 John Smith 1234 1
> 2 Smith John 1234 1
> 3 S John 1234 1
> 4 J Smith 1234 1
> 5 J S 1234 1
> 6 Dan Brown 1234 2
> 7 John Smith 1111 3
>
> I have tried this code for 65,000 rows. It took 20 minute. I have to
> run it for 21 million row data. I now that this is not an efficient
> code.
>
> INSERT into temp_FnLnSSN_grp
> SELECT c1.fname, c1.lname, c1.ssn AS ssn, c3.tu_id,
> (SELECT 1 + count(*)
> FROM distFLS AS c2
> WHERE c2.ssn < c1.ssn
> or (c2.ssn = c1.ssn and (substring(c2.fname,1,1) =
> substring(c1.fname,1,1) or substring(c2.lname,1,1) =
> substring(c1.lname,1,1)
> or substring(c2.fname,1,1) =
> substring(c1.lname,1,1) or substring(c2.lname,1,1) =
> substring(c1.fname,1,1))
> )) AS group_number
> FROM distFLS AS c1
> JOIN tu_people_data AS c3
> ON (c1.ssn = c3.ssn and
> c1.fname = c3.fname and
> c1.lname= c3.lname)
>
> dist FLS is distinct First Name, last Name and SSN table from the
> people table.
> I have posted part of this question, schema one week ago. Please refer
> this thread.
> http://groups.google.com/group/comp...6eb380b5f2e6de6|||(jacob.dba@.gmail.com) writes:
> I want to assign group number according to this business logic.
> 1. Records with equal SSN and (similar first name or last name) belong
> to the same group.
> John Smith 1234
> Smith John 1234
> S John 1234
> J Smith 1234
> John Smith and Smith John falls in the same group Number as long as
> they have similar SSN.
> This is because I have a record of equal SSN but the first name and
> last name is switched because of people who make error inserting last
> name as first name and vice versa. John Smith and Smith John will have
> equal group Name if they have equal SSN.
> 2. There are records with equal SSN but different first name and last
> name. These belong to different group numbers.
> Equal SSN doesn't guarantee equal group number, at least one of the
> first name or last name should be the same. John Smith and Dan Brown
> with equal SSN=1234 shouldn't fall in the same group number.

What if you have both John Smith and Southerland Jane? Are the
same person or not?

This looks like a very difficult task, and the fact that you have
800 million rows certainly does not help to make it easier.

I think you need to scrap the idea you got from Itzik. My gut feeling
say that it will not scale.

Here is a very simple-minded solution where I've assumed that as
long as any combination of initials match, it's the same group.

CREATE TABLE [TU_People_Data] (
[tu_id] [bigint] NOT NULL ,
[count_id] [int] NOT NULL ,
[fname] [varchar] (32) COLLATE Latin1_General_CI_AS NULL ,
[lname] [varchar] (32) COLLATE Latin1_General_CI_AS NULL ,
[ssn] [int] NULL ,
CONSTRAINT [PK_tu_bulk_people] PRIMARY KEY CLUSTERED
(
[tu_id],
[count_id]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE #initials (ssn int NOT NULL,
fname varchar(32) NOT NULL,
lname varchar(32) NOT NULL,
initials char(2) NOT NULL)
go
CREATE TABLE #ssnmania (ident int NOT NULL,
ssn int NOT NULL,
initials char(2) NOT NULL,
PRIMARY KEY(ssn, initials))
go
INSERT #initals (ssn, fname, lname, initials)
SELECT DISTINCT ssn, fname, lname,
CASE WHEN fname < lname
THEN substring(fname, 1, 1) + substring(lname, 1, 1)
ELSE substring(lname, 1, 1) + substring(fname, 1, 1)
END
FROM TU_People_Data
go
INSERT #ssnmania (ssn, initials)
SELECT DISTINCT ssn, initials
FROM #initials
go
SELECT i.ssn, i.fname, i.lname, i.initials, groupno = s.ident
FROM #initials i
JOIN #ssnmania s ON i.ssn = s.ssn
AND s.initials = i.initials
go
DROP TABLE #initials, #ssnmania, TU_People_Data

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks Erland.
I have tried this procedure in the morning and it solves half of my
problem.
let me start by answering your question.
>What if you have both John Smith and Southerland Jane? Are the
> same person or not?
If these guys' SSN is the same, they are considered to be in the the
same group.
I am willing to take the chance that John Smith, Southerland Jane and
Jack Sam with similar SSN has slim chance to occur. if they exist,
they are gouped in one group number.
>>regarding your solution
In my table some of the rows for one person are displayed like this.
1.John Coleman Smith 1111 JS
2.John Smith Coleman 1111 CJ
3.Coleman John Smith 1111 CS
4.John-coleman Smith 1111 JS
5. Smith John 1111 JS
6.John Smith 2222 JS
7.J Smith 1111 JS
8 Jack Sam 3333 JS
you can see that all this guys can be grouped in the same group
name(except the 6th and 8th). I see that SSN is the major factor to
identify the groups.
So once SSN is the same then the intitals has to be one or two of the
three J or S or C.

Erland Sommarskog wrote:
> (jacob.dba@.gmail.com) writes:
> > I want to assign group number according to this business logic.
> > 1. Records with equal SSN and (similar first name or last name) belong
> > to the same group.
> > John Smith 1234
> > Smith John 1234
> > S John 1234
> > J Smith 1234
> > John Smith and Smith John falls in the same group Number as long as
> > they have similar SSN.
> > This is because I have a record of equal SSN but the first name and
> > last name is switched because of people who make error inserting last
> > name as first name and vice versa. John Smith and Smith John will have
> > equal group Name if they have equal SSN.
> > 2. There are records with equal SSN but different first name and last
> > name. These belong to different group numbers.
> > Equal SSN doesn't guarantee equal group number, at least one of the
> > first name or last name should be the same. John Smith and Dan Brown
> > with equal SSN=1234 shouldn't fall in the same group number.
> What if you have both John Smith and Southerland Jane? Are the
> same person or not?
> This looks like a very difficult task, and the fact that you have
> 800 million rows certainly does not help to make it easier.
> I think you need to scrap the idea you got from Itzik. My gut feeling
> say that it will not scale.
> Here is a very simple-minded solution where I've assumed that as
> long as any combination of initials match, it's the same group.
>
> CREATE TABLE [TU_People_Data] (
> [tu_id] [bigint] NOT NULL ,
> [count_id] [int] NOT NULL ,
> [fname] [varchar] (32) COLLATE Latin1_General_CI_AS NULL ,
> [lname] [varchar] (32) COLLATE Latin1_General_CI_AS NULL ,
> [ssn] [int] NULL ,
> CONSTRAINT [PK_tu_bulk_people] PRIMARY KEY CLUSTERED
> (
> [tu_id],
> [count_id]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
> CREATE TABLE #initials (ssn int NOT NULL,
> fname varchar(32) NOT NULL,
> lname varchar(32) NOT NULL,
> initials char(2) NOT NULL)
> go
> CREATE TABLE #ssnmania (ident int NOT NULL,
> ssn int NOT NULL,
> initials char(2) NOT NULL,
> PRIMARY KEY(ssn, initials))
> go
> INSERT #initals (ssn, fname, lname, initials)
> SELECT DISTINCT ssn, fname, lname,
> CASE WHEN fname < lname
> THEN substring(fname, 1, 1) + substring(lname, 1, 1)
> ELSE substring(lname, 1, 1) + substring(fname, 1, 1)
> END
> FROM TU_People_Data
> go
> INSERT #ssnmania (ssn, initials)
> SELECT DISTINCT ssn, initials
> FROM #initials
> go
> SELECT i.ssn, i.fname, i.lname, i.initials, groupno = s.ident
> FROM #initials i
> JOIN #ssnmania s ON i.ssn = s.ssn
> AND s.initials = i.initials
> go
> DROP TABLE #initials, #ssnmania, TU_People_Data
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||(jacob.dba@.gmail.com) writes:
> I have tried this procedure in the morning and it solves half of my
> problem.

And the other half is? :-) I did not include the middle initial, because
I did not see that post until later.

But I guess that you could extend the logic that I posted to handle
the middle initial as well.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||you need a function that takes the first character from first name,
last name, and middle initial, and sorts them. Call it "SortInit"
So, pass "Sam Alfred Jones" and it passes back "AJS". Likewise,
"Jones Alfred Sam" is returned as "AJS".

then, create your temp table and populate it with SSN and Sortinit().
then alter table on your temp table and add an identity column.
Then make your "temp table" a permanent one, as your business rules
will change, and fundamentally what you are doing is looking for
"duplicate rows" and grouping them, and this is almost always a
multiple pass project.

Asp.net session has expired

Hi

I have designed reports using SQL Server Business Intelligence Development Studio tool (Sql Server reporting service). I have uploaded these reports to report manager.

I am displaying list of reports in tree view control, in my application. I am viewing report in Report Viewer control as per the report selection in tree view control, in same page. I am getting

Server Error in '/Application_name' Application or

ASP.NET session has expired error frequently while switching between various reports. Kindly provide me solution

.

Regards

Sagayaraj Rayappan

Are you reseting the Report Path each time they click on an item or are you saving the ExecutionID off? You will need to set the report path each time and not try and use the existed execution ID unless you can keep the session alive yourself.|||

Thanks for the information. I am resetting Report Path each time when we clicked on an item. How to keep the session alive myself?. What is ExecutionID and how to use it?. Kindly give more details on this.

|||

You do not want to use the ExecutionID, I just wanted to rule that out first.

Is the error occuring from within the report viewer or the entire page?

|||This error occurs for entire page.|||Since the error occurs on the entire page, this points to an issue in your app. If it was the report server session that had the timeout, the error would occur within the viewer (you would still see the toolbar. Try removing the viewer control and replacing it with a simple control and see if the problem persists.|||

Hi i am also getting the same error.In my web page i refer the reportviewer and showing different reports.While navigate to different reports randomly i am getting this error.

Can anyone suggest how to solve this?

Microsoft.Reporting.WebForms.AspNetSessionExpiredException: ASP.NET session has expired

[AspNetSessionExpiredException: ASP.NET session has expired]
Microsoft.Reporting.WebForms.ReportDataOperation..ctor() +683
Microsoft.Reporting.WebForms.HttpHandler.GetHandler() +553
Microsoft.Reporting.WebForms.HttpHandler.ProcessRequest(HttpContext context) +10
System.Web.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +154
System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +64

Thanks in advance,Tom

|||

Daniel Reib wrote:

Since the error occurs on the entire page, this points to an issue in your app. If it was the report server session that had the timeout, the error would occur within the viewer (you would still see the toolbar. Try removing the viewer control and replacing it with a simple control and see if the problem persists.

Hi I'm getting this error from with the ReportViewer control... I've tried to change the session timeout with the following code but it still times out earlier then the timeout value stated...? Is there some other attribute that needs to be changed also?

rs -i sessionTimeout.rss -s http://localhost/reportserver -v timeout="6000"

Many thanks in advance,

Rob.

|||

Did you get a solution to this? The setting you have changed is on the reportserver, your problem is on the app that contains the reportviewer. Look at my post here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=371287&SiteID=1&PageID=2

|||

Hi Mark,

My issue was slightly different. Because of the nature of our reporting system, users may leave a report open for a long period of time (maybe hours) and then come back to it and attempt to run a drillthrouhgh report from where they left off.

My solution to this was to increase the session timeout in the application pool which has worked well. I'm lucky I guess that the user base for the system is very small, i.e. no more than twenty users, so we should run into any resource issues (finger crossed).

Regards,

Rob.

Asp.net session has expired

Hi

I have designed reports using SQL Server Business Intelligence Development Studio tool (Sql Server reporting service). I have uploaded these reports to report manager.

I am displaying list of reports in tree view control, in my application. I am viewing report in Report Viewer control as per the report selection in tree view control, in same page. I am getting

Server Error in '/Application_name' Application or

ASP.NET session has expired error frequently while switching between various reports. Kindly provide me solution

.

Regards

Sagayaraj Rayappan

Are you reseting the Report Path each time they click on an item or are you saving the ExecutionID off? You will need to set the report path each time and not try and use the existed execution ID unless you can keep the session alive yourself.|||

Thanks for the information. I am resetting Report Path each time when we clicked on an item. How to keep the session alive myself?. What is ExecutionID and how to use it?. Kindly give more details on this.

|||

You do not want to use the ExecutionID, I just wanted to rule that out first.

Is the error occuring from within the report viewer or the entire page?

|||This error occurs for entire page.|||Since the error occurs on the entire page, this points to an issue in your app. If it was the report server session that had the timeout, the error would occur within the viewer (you would still see the toolbar. Try removing the viewer control and replacing it with a simple control and see if the problem persists.|||

Hi i am also getting the same error.In my web page i refer the reportviewer and showing different reports.While navigate to different reports randomly i am getting this error.

Can anyone suggest how to solve this?

Microsoft.Reporting.WebForms.AspNetSessionExpiredException: ASP.NET session has expired

[AspNetSessionExpiredException: ASP.NET session has expired]
Microsoft.Reporting.WebForms.ReportDataOperation..ctor() +683
Microsoft.Reporting.WebForms.HttpHandler.GetHandler() +553
Microsoft.Reporting.WebForms.HttpHandler.ProcessRequest(HttpContext context) +10
System.Web.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +154
System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +64

Thanks in advance,Tom

|||

Daniel Reib wrote:

Since the error occurs on the entire page, this points to an issue in your app. If it was the report server session that had the timeout, the error would occur within the viewer (you would still see the toolbar. Try removing the viewer control and replacing it with a simple control and see if the problem persists.

Hi I'm getting this error from with the ReportViewer control... I've tried to change the session timeout with the following code but it still times out earlier then the timeout value stated...? Is there some other attribute that needs to be changed also?

rs -i sessionTimeout.rss -s http://localhost/reportserver -v timeout="6000"

Many thanks in advance,

Rob.

|||

Did you get a solution to this? The setting you have changed is on the reportserver, your problem is on the app that contains the reportviewer. Look at my post here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=371287&SiteID=1&PageID=2

|||

Hi Mark,

My issue was slightly different. Because of the nature of our reporting system, users may leave a report open for a long period of time (maybe hours) and then come back to it and attempt to run a drillthrouhgh report from where they left off.

My solution to this was to increase the session timeout in the application pool which has worked well. I'm lucky I guess that the user base for the system is very small, i.e. no more than twenty users, so we should run into any resource issues (finger crossed).

Regards,

Rob.

Asp.net session has expired

Hi

I have designed reports using SQL Server Business Intelligence Development Studio tool (Sql Server reporting service). I have uploaded these reports to report manager.

I am displaying list of reports in tree view control, in my application. I am viewing report in Report Viewer control as per the report selection in tree view control, in same page. I am getting

Server Error in '/Application_name' Application or

ASP.NET session has expired error frequently while switching between various reports. Kindly provide me solution

.

Regards

Sagayaraj Rayappan

Are you reseting the Report Path each time they click on an item or are you saving the ExecutionID off? You will need to set the report path each time and not try and use the existed execution ID unless you can keep the session alive yourself.|||

Thanks for the information. I am resetting Report Path each time when we clicked on an item. How to keep the session alive myself?. What is ExecutionID and how to use it?. Kindly give more details on this.

|||

You do not want to use the ExecutionID, I just wanted to rule that out first.

Is the error occuring from within the report viewer or the entire page?

|||This error occurs for entire page.|||Since the error occurs on the entire page, this points to an issue in your app. If it was the report server session that had the timeout, the error would occur within the viewer (you would still see the toolbar. Try removing the viewer control and replacing it with a simple control and see if the problem persists.|||

Hi i am also getting the same error.In my web page i refer the reportviewer and showing different reports.While navigate to different reports randomly i am getting this error.

Can anyone suggest how to solve this?

Microsoft.Reporting.WebForms.AspNetSessionExpiredException: ASP.NET session has expired

[AspNetSessionExpiredException: ASP.NET session has expired]
Microsoft.Reporting.WebForms.ReportDataOperation..ctor() +683
Microsoft.Reporting.WebForms.HttpHandler.GetHandler() +553
Microsoft.Reporting.WebForms.HttpHandler.ProcessRequest(HttpContext context) +10
System.Web.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +154
System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +64

Thanks in advance,Tom

|||

Daniel Reib wrote:

Since the error occurs on the entire page, this points to an issue in your app. If it was the report server session that had the timeout, the error would occur within the viewer (you would still see the toolbar. Try removing the viewer control and replacing it with a simple control and see if the problem persists.

Hi I'm getting this error from with the ReportViewer control... I've tried to change the session timeout with the following code but it still times out earlier then the timeout value stated...? Is there some other attribute that needs to be changed also?

rs -i sessionTimeout.rss -s http://localhost/reportserver -v timeout="6000"

Many thanks in advance,

Rob.

|||

Did you get a solution to this? The setting you have changed is on the reportserver, your problem is on the app that contains the reportviewer. Look at my post here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=371287&SiteID=1&PageID=2

|||

Hi Mark,

My issue was slightly different. Because of the nature of our reporting system, users may leave a report open for a long period of time (maybe hours) and then come back to it and attempt to run a drillthrouhgh report from where they left off.

My solution to this was to increase the session timeout in the application pool which has worked well. I'm lucky I guess that the user base for the system is very small, i.e. no more than twenty users, so we should run into any resource issues (finger crossed).

Regards,

Rob.