Count

I am trying to count entries into each column heading by zip code, but when I
run the script below I get the same count across all columns. Can someone
tell/show me how to make these unquie counts?
Thanks in advance.
SELECT DISTINCT P.ZIP,
COUNT (evC.day_care_center) AS 'Day Care',
COUNT (evC.drop_care) AS 'Drop-In Care',
COUNT (evC.family_day_care) AS 'Family day care',
COUNT (evC.financial) AS 'Financial',
COUNT (evC.montessori_program2)AS 'Montessori program',
COUNT (evC.nanny_service) AS 'Nanny service',
COUNT (evC.nursery_schl_lrn_ct) AS 'Nursery school - learn centers',
COUNT (evC.parenting_info) AS 'Parenting information',
COUNT (evC.sick_child_care) AS 'Sick child care',
COUNT (evC.special_needs)AS 'Special needs',
COUNT (evC.summer_camp_care)AS 'Summer camps/care',
COUNT (evC.temporary) AS 'Temporary',
COUNT (evC.transportation2)AS 'Transportation',
COUNT (evC.support_groups) AS 'Support groups',
COUNT (evC.other) AS 'Other'
FROM Patient_Elg pe
INNER JOIN Patient p ON pe.Patient_Key = p.Patient_Key
INNER JOIN evChildcareintakea evC ON pe.Patient_Key = evC.Patient_Key
WHERE (pe.Payor_Key = 59)
AND p.create_date BETWEEN '1/1/07' AND '12/31/07'
GROUP BY P.Zip
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200801/1Put the DISTINCT keyword inside the COUNT():
SELECT P.ZIP,
COUNT (DISTINCT evC.day_care_center) AS 'Day Care',
COUNT (DISTINCT evC.drop_care) AS 'Drop-In Care',
...
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Jay via SQLMonster.com" <u7124@.uwe> wrote in message
news:7efd1fc265539@.uwe...
I am trying to count entries into each column heading by zip code, but when
I
run the script below I get the same count across all columns. Can someone
tell/show me how to make these unquie counts?
Thanks in advance.
SELECT DISTINCT P.ZIP,
COUNT (evC.day_care_center) AS 'Day Care',
COUNT (evC.drop_care) AS 'Drop-In Care',
COUNT (evC.family_day_care) AS 'Family day care',
COUNT (evC.financial) AS 'Financial',
COUNT (evC.montessori_program2)AS 'Montessori program',
COUNT (evC.nanny_service) AS 'Nanny service',
COUNT (evC.nursery_schl_lrn_ct) AS 'Nursery school - learn centers',
COUNT (evC.parenting_info) AS 'Parenting information',
COUNT (evC.sick_child_care) AS 'Sick child care',
COUNT (evC.special_needs)AS 'Special needs',
COUNT (evC.summer_camp_care)AS 'Summer camps/care',
COUNT (evC.temporary) AS 'Temporary',
COUNT (evC.transportation2)AS 'Transportation',
COUNT (evC.support_groups) AS 'Support groups',
COUNT (evC.other) AS 'Other'
FROM Patient_Elg pe
INNER JOIN Patient p ON pe.Patient_Key = p.Patient_Key
INNER JOIN evChildcareintakea evC ON pe.Patient_Key = evC.Patient_Key
WHERE (pe.Payor_Key = 59)
AND p.create_date BETWEEN '1/1/07' AND '12/31/07'
GROUP BY P.Zip
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200801/1|||I'm guessing you might need to COUNT the distinct values for each
column. If that is the case:
SELECT P.ZIP,
COUNT (DISTINCT evC.day_care_center) AS 'Day Care',
COUNT (DISTINCT evC.drop_care) AS 'Drop-In Care',
etc.
If that is not what you need please elaborate on what you actually
hope to see.
Note that since you were already doing a GROUP BY there was no purpose
to having a DISTINCT for the entire query.
Roy Harvey
Beacon Falls, CT
On Wed, 30 Jan 2008 15:49:59 GMT, "Jay via SQLMonster.com" <u7124@.uwe>
wrote:
>I am trying to count entries into each column heading by zip code, but when I
>run the script below I get the same count across all columns. Can someone
>tell/show me how to make these unquie counts?
>Thanks in advance.
>SELECT DISTINCT P.ZIP,
> COUNT (evC.day_care_center) AS 'Day Care',
> COUNT (evC.drop_care) AS 'Drop-In Care',
> COUNT (evC.family_day_care) AS 'Family day care',
> COUNT (evC.financial) AS 'Financial',
> COUNT (evC.montessori_program2)AS 'Montessori program',
> COUNT (evC.nanny_service) AS 'Nanny service',
> COUNT (evC.nursery_schl_lrn_ct) AS 'Nursery school - learn centers',
> COUNT (evC.parenting_info) AS 'Parenting information',
> COUNT (evC.sick_child_care) AS 'Sick child care',
> COUNT (evC.special_needs)AS 'Special needs',
> COUNT (evC.summer_camp_care)AS 'Summer camps/care',
> COUNT (evC.temporary) AS 'Temporary',
> COUNT (evC.transportation2)AS 'Transportation',
> COUNT (evC.support_groups) AS 'Support groups',
> COUNT (evC.other) AS 'Other'
>FROM Patient_Elg pe
> INNER JOIN Patient p ON pe.Patient_Key = p.Patient_Key
> INNER JOIN evChildcareintakea evC ON pe.Patient_Key = evC.Patient_Key
>WHERE (pe.Payor_Key = 59)
> AND p.create_date BETWEEN '1/1/07' AND '12/31/07'
>GROUP BY P.Zip|||I made the changes you suggested and it did make the counts unique but they
are not correct by column heading/category. To check I displayed all the
information for zip code 55901 under the column 'Day Care' for '07. I came up
with a count of 200, but for this script I only get 11. How can I get all
records to count?
Thanks again for your help in advance
Roy Harvey (SQL Server MVP) wrote:
>I'm guessing you might need to COUNT the distinct values for each
>column. If that is the case:
>SELECT P.ZIP,
> COUNT (DISTINCT evC.day_care_center) AS 'Day Care',
> COUNT (DISTINCT evC.drop_care) AS 'Drop-In Care',
>etc.
>If that is not what you need please elaborate on what you actually
>hope to see.
>Note that since you were already doing a GROUP BY there was no purpose
>to having a DISTINCT for the entire query.
>Roy Harvey
>Beacon Falls, CT
>>I am trying to count entries into each column heading by zip code, but when I
>>run the script below I get the same count across all columns. Can someone
>[quoted text clipped - 25 lines]
>> AND p.create_date BETWEEN '1/1/07' AND '12/31/07'
>>GROUP BY P.Zip
--
Message posted via http://www.sqlmonster.com|||Usually the fastest way to get answers to questions like this is to give us
the SQL statements to create sample tables and load those tables with sample
data (see www.aspfaq.com/5006 for how to do this). Then tell us the result
you would want to see from that sample data. That keeps us from having to
guess what you want, and you will get an answer that has been tested against
your sample data.
Tom
"jpettigrew via SQLMonster.com" <u7124@.uwe> wrote in message
news:7efdb43406fee@.uwe...
>I made the changes you suggested and it did make the counts unique but they
> are not correct by column heading/category. To check I displayed all the
> information for zip code 55901 under the column 'Day Care' for '07. I came
> up
> with a count of 200, but for this script I only get 11. How can I get all
> records to count?
> Thanks again for your help in advance
> Roy Harvey (SQL Server MVP) wrote:
>>I'm guessing you might need to COUNT the distinct values for each
>>column. If that is the case:
>>SELECT P.ZIP,
>> COUNT (DISTINCT evC.day_care_center) AS 'Day Care',
>> COUNT (DISTINCT evC.drop_care) AS 'Drop-In Care',
>>etc.
>>If that is not what you need please elaborate on what you actually
>>hope to see.
>>Note that since you were already doing a GROUP BY there was no purpose
>>to having a DISTINCT for the entire query.
>>Roy Harvey
>>Beacon Falls, CT
>>I am trying to count entries into each column heading by zip code, but
>>when I
>>run the script below I get the same count across all columns. Can someone
>>[quoted text clipped - 25 lines]
>> AND p.create_date BETWEEN '1/1/07' AND '12/31/07'
>>GROUP BY P.Zip
> --
> Message posted via http://www.sqlmonster.com
>|||What Tom Cooper said. SHOW us what you have, and what you want.
Your original query counted all the rows for the zip code. All the
columns were the same because COUNT(columnname) returns a count of all
the rows with non-null values for that column, and apparently every
row was non-null. If there are 200 rows for a zip code, why would you
expect any number except 200 for day_care_center or drop_care?
Roy Harvey
Beacon Falls, CT
On Wed, 30 Jan 2008 16:56:33 GMT, "jpettigrew via SQLMonster.com"
<u7124@.uwe> wrote:
>I made the changes you suggested and it did make the counts unique but they
>are not correct by column heading/category. To check I displayed all the
>information for zip code 55901 under the column 'Day Care' for '07. I came up
>with a count of 200, but for this script I only get 11. How can I get all
>records to count?
>Thanks again for your help in advance
>Roy Harvey (SQL Server MVP) wrote:
>>I'm guessing you might need to COUNT the distinct values for each
>>column. If that is the case:
>>SELECT P.ZIP,
>> COUNT (DISTINCT evC.day_care_center) AS 'Day Care',
>> COUNT (DISTINCT evC.drop_care) AS 'Drop-In Care',
>>etc.
>>If that is not what you need please elaborate on what you actually
>>hope to see.
>>Note that since you were already doing a GROUP BY there was no purpose
>>to having a DISTINCT for the entire query.
>>Roy Harvey
>>Beacon Falls, CT
>>I am trying to count entries into each column heading by zip code, but when I
>>run the script below I get the same count across all columns. Can someone
>>[quoted text clipped - 25 lines]
>> AND p.create_date BETWEEN '1/1/07' AND '12/31/07'
>>GROUP BY P.Zip|||Because of HIPAA compliance it is difficult to provide information contained
in one of the tables. How do you suggest I provide the information? If i was
not specific in my previous posts I apologize I am trying to total the
columns in the script by zip code, but do not seem to be getting all the
information.
Tom Cooper wrote:
>Usually the fastest way to get answers to questions like this is to give us
>the SQL statements to create sample tables and load those tables with sample
>data (see www.aspfaq.com/5006 for how to do this). Then tell us the result
>you would want to see from that sample data. That keeps us from having to
>guess what you want, and you will get an answer that has been tested against
>your sample data.
>Tom
>>I made the changes you suggested and it did make the counts unique but they
>> are not correct by column heading/category. To check I displayed all the
>[quoted text clipped - 28 lines]
>> AND p.create_date BETWEEN '1/1/07' AND '12/31/07'
>>GROUP BY P.Zip
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200801/1|||On Wed, 30 Jan 2008 20:11:22 GMT, Jay via SQLMonster.com wrote:
>Because of HIPAA compliance it is difficult to provide information contained
>in one of the tables. How do you suggest I provide the information?
Hi Jay,
* Post the table structure as CREATE TABLE statements, including all
constraints, properties, and indexes.
* Post the data as INSERT statements. It doesn't need to be the real
data, a made-up sample that illustrates the problem is just as well
(probably even better, as there is no need to posts thousands or even
millions of rows when you can illustrate the problem with ten). I don't
think HIPAA disallows the posting of made-up data.
* Post the expected results.
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||On Jan 30, 9:49=A0pm, "Jay via SQLMonster.com" <u7124@.uwe> wrote:
> I am trying to count entries into each column heading by zip code, but whe=n I
> run the script below I get the same count across all columns. Can someone
> tell/show me how to make these unquie counts?
> Thanks in advance.
> SELECT =A0DISTINCT P.ZIP,
> =A0 =A0 =A0 =A0 COUNT (evC.day_care_center) AS 'Day Care',
> =A0 =A0 =A0 =A0 COUNT (evC.drop_care) AS 'Drop-In Care',
> =A0 =A0 =A0 =A0 COUNT (evC.family_day_care) AS 'Family day care',
> =A0 =A0 =A0 =A0 COUNT (evC.financial) AS 'Financial',
> =A0 =A0 =A0 =A0 COUNT (evC.montessori_program2)AS 'Montessori program',
> =A0 =A0 =A0 =A0 COUNT (evC.nanny_service) AS 'Nanny service',
> =A0 =A0 =A0 =A0 COUNT (evC.nursery_schl_lrn_ct) AS 'Nursery school - learn= centers',
> =A0 =A0 =A0 =A0 COUNT (evC.parenting_info) AS 'Parenting information',
> =A0 =A0 =A0 =A0 COUNT (evC.sick_child_care) AS 'Sick child care',
> =A0 =A0 =A0 =A0 COUNT (evC.special_needs)AS 'Special needs',
> =A0 =A0 =A0 =A0 COUNT (evC.summer_camp_care)AS 'Summer camps/care',
> =A0 =A0 =A0 =A0 COUNT (evC.temporary) AS 'Temporary',
> =A0 =A0 =A0 =A0 COUNT (evC.transportation2)AS 'Transportation',
> =A0 =A0 =A0 =A0 COUNT (evC.support_groups) AS 'Support groups',
> =A0 =A0 =A0 =A0 COUNT (evC.other) AS 'Other'
> FROM =A0 =A0Patient_Elg pe
> =A0 =A0 =A0 =A0 INNER JOIN Patient p ON pe.Patient_Key =3D p.Patient_Key
> =A0 =A0 =A0 =A0 INNER JOIN evChildcareintakea evC ON pe.Patient_Key =3D ev=C.Patient_Key
> WHERE =A0 =A0 (pe.Payor_Key =3D 59)
> =A0 =A0 =A0 =A0 AND p.create_date BETWEEN '1/1/07' AND '12/31/07'
> GROUP BY P.Zip
> --
> Message posted via SQLMonster.comhttp://www.sqlmonster.com/Uwe/Forums.aspx=
/sql-server/200801/1
Hi Jay,
I think you need to do each count(distinct field) separately and store
them (maybe into a variable). For example:
Declare @.zip int, @.center int, etc
Select @.zip =3D select count(distinct p.zip)
FROM Patient_Elg pe
INNER JOIN Patient p ON pe.Patient_Key =3D p.Patient_Key
INNER JOIN evChildcareintakea evC ON pe.Patient_Key =3D
evC.Patient_Key
WHERE (pe.Payor_Key =3D 59)
AND p.create_date BETWEEN '1/1/07' AND '12/31/07'
GROUP BY P.Zip
Select @.center int =3D select count(distinct evC.day_care_center)
FROM Patient_Elg pe
INNER JOIN Patient p ON pe.Patient_Key =3D p.Patient_Key
INNER JOIN evChildcareintakea evC ON pe.Patient_Key =3D
evC.Patient_Key
WHERE (pe.Payor_Key =3D 59)
AND p.create_date BETWEEN '1/1/07' AND '12/31/07'
GROUP BY P.Zip
etc.
HTH|||On Wed, 30 Jan 2008 20:40:12 -0800 (PST), SB <othellomy@.yahoo.com>
wrote:
>I think you need to do each count(distinct field) separately and store
>them (maybe into a variable).
All at once or individually, if they are always grouped by the same
thing there will be no difference.
Roy Harvey
Beacon Falls, CT

Could not automatically reinitialize

I have a series of publisher/ distributors that replicat to one subscriber.
The replication was off-line across the weekend and now most of the
subscriptions are marked inactive / needs reinitialization.
When I try to reinitialize I get a message box saying "SQL Server Enterprise
Manager could not automatically reinitialize this subscription. To
reinitialize this subscription, you must drop and recreate it."
Any suggestions as to places to start looking for a solution will be greatly
appreciated.
You likely have no-sync subscribers - these always need to be dropped and
recreated, or converted to sync subscribers.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Ron Sellers" <RonSellers@.discussions.microsoft.com> wrote in message
news:2DB36478-12BA-431A-A5D7-10952637A116@.microsoft.com...
> I have a series of publisher/ distributors that replicat to one
subscriber.
> The replication was off-line across the weekend and now most of the
> subscriptions are marked inactive / needs reinitialization.
> When I try to reinitialize I get a message box saying "SQL Server
Enterprise
> Manager could not automatically reinitialize this subscription. To
> reinitialize this subscription, you must drop and recreate it."
> Any suggestions as to places to start looking for a solution will be
greatly
> appreciated.
>

could i have missed Reporting services during installation

I recently installed SQL Express advanced and came across something thats confusing me. When i look at my running services i see the advanced features like SQL Server Full Text Search and the like ,but cant seem to find the reporting service. Is there a chance i skipped over it in the insall? I had assumed i would have a service running that would say something along the lines of SQL Reporting Services. I havent come across directions on how to turn it on, so i assume that i missed it during installation?I definitely messed up the first install. I went back and re-ran the SQL express advanced install file and got the reporting services to show up. It walked me through setup options for the reporting services that i didnt recall from the first time, so hope this reply helps anyone that has a missing feature after their first run...|||Have you installed Business Intelligence Design Studio (BIDS) from the 2005 express tool kit? If so, were you able to start the application? When I try, I get what seems to be sucessful install except when I select BIDS it complains about not being able to find devenv.exe. I even tried to point the short cut to the file in my VS 2003 install but that didi nothing. I understand that BIDS is required to use report services in sql 2005 express. Have yoou had any luck with this?|||funny you mention that problem. Firing up that was what first got me rolling onto looking for my reporting services.. I have installed the toolkit twice now and have the same result in it looking for the .exe file. Im going to dig into this issue and maybe start a new thread when i come up with a solution.... Also i think its worth pointing out that i used the sql express toolkit executable to do the install and not the sql express advanced as i mentioned above...

could i have missed Reporting services during installation

I recently installed SQL Express advanced and came across something thats confusing me. When i look at my running services i see the advanced features like SQL Server Full Text Search and the like ,but cant seem to find the reporting service. Is there a chance i skipped over it in the insall? I had assumed i would have a service running that would say something along the lines of SQL Reporting Services. I havent come across directions on how to turn it on, so i assume that i missed it during installation?
I definitely messed up the first install. I went back and re-ran the SQL express advanced install file and got the reporting services to show up. It walked me through setup options for the reporting services that i didnt recall from the first time, so hope this reply helps anyone that has a missing feature after their first run...
|||Have you installed Business Intelligence Design Studio (BIDS) from the 2005 express tool kit? If so, were you able to start the application? When I try, I get what seems to be sucessful install except when I select BIDS it complains about not being able to find devenv.exe. I even tried to point the short cut to the file in my VS 2003 install but that didi nothing. I understand that BIDS is required to use report services in sql 2005 express. Have yoou had any luck with this?|||funny you mention that problem. Firing up that was what first got me rolling onto looking for my reporting services.. I have installed the toolkit twice now and have the same result in it looking for the .exe file. Im going to dig into this issue and maybe start a new thread when i come up with a solution.... Also i think its worth pointing out that i used the sql express toolkit executable to do the install and not the sql express advanced as i mentioned above...

Could CONTEXT_INFO persist across connections because of pooling?

i am issuing
SET CONTEXT_INFO @.BinarySoftwareUserIdentification
when i connect to the database so that later during my insert/update/delete
audit logging triggers i can get the user of the software that did the
modification.
Is it possible that a new connection could inherit the CONTEXT_INFO from an
old connection?
The situation is that the web-server itself is issuing all the connections,
so any connections made and dropped happen from the same machine. An
automated process on the server doesn't issue a call to SET CONTEXT_INFO
(for whatever reason). Could the pooled database connection that the process
gets have the same CONTEXT_INFO as the previous person who used that
connection?
Would SQL Server clear CONTEXT_INFO when a user using ADO closes their
connection? Or would it only get rid of it when the pool timeout finally
happens?
Put it another way, can one connection be impersonating the user who
previously owned that connection?Good question, Ian...
I never doubt the correctness of the author of this article:
http://www.sqldev.net/misc/sp_reset_connection.htm
It does say that sp_reset_connection does reset all SET options. However, CO
NTEXT_INFO could just be
a special case... I'd test it just to be certain. Note that the article spec
ifies that the reset is
performed when the connection is *re-used* (lazy...).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ian Boyd" <ian.msnews010@.avatopia.com> wrote in message
news:%23KReMJceGHA.3388@.TK2MSFTNGP05.phx.gbl...
>i am issuing
> SET CONTEXT_INFO @.BinarySoftwareUserIdentification
> when i connect to the database so that later during my insert/update/delet
e audit logging triggers
> i can get the user of the software that did the modification.
> Is it possible that a new connection could inherit the CONTEXT_INFO from a
n old connection?
> The situation is that the web-server itself is issuing all the connections
, so any connections
> made and dropped happen from the same machine. An automated process on the
server doesn't issue a
> call to SET CONTEXT_INFO (for whatever reason). Could the pooled database
connection that the
> process gets have the same CONTEXT_INFO as the previous person who used th
at connection?
> Would SQL Server clear CONTEXT_INFO when a user using ADO closes their con
nection? Or would it
> only get rid of it when the pool timeout finally happens?
>
> Put it another way, can one connection be impersonating the user who previ
ously owned that
> connection?
>|||> I'd test it just to be certain.
i thought that trying to trick ADO into giving me the same pooled connection
would be like trying to get cats to walk in a line.
But my test program was
1. Open connection
2. Set Context info
3. Close connection
4. Open connection
5. Get Context info
i got the original context info back, and it turns out i (obviously) got the
same SPID both times. So, Microsoft, if you could update sp_reset_connection
in SQL Server X, that would be great.
And a service pack for SQL2000 would be nice too.|||> would be like trying to get cats to walk in a line.
I like the analogy... :-)
Thanks for letting us know that sp_reset_connection does not clear out CONTE
XT_INFO. Seems like MS
missed that (I'd guess). Possibly because CONTEXT_INFO doesn't seem to be us
ed very much.

> So, Microsoft, if you could update sp_reset_connection in SQL Server X, that would
be great.
Did you do the feedback?
http://lab.msdn.microsoft.com/productfeedback/
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ian Boyd" <ian.msnews010@.avatopia.com> wrote in message
news:ukyJH5deGHA.3556@.TK2MSFTNGP02.phx.gbl...
> i thought that trying to trick ADO into giving me the same pooled connecti
on would be like trying
> to get cats to walk in a line.
> But my test program was
> 1. Open connection
> 2. Set Context info
> 3. Close connection
> 4. Open connection
> 5. Get Context info
> i got the original context info back, and it turns out i (obviously) got t
he same SPID both times.
> So, Microsoft, if you could update sp_reset_connection in SQL Server X, th
at would be great.
> And a service pack for SQL2000 would be nice too.
>

Could CONTEXT_INFO persist across connections because of pooling?

i am issuing
SET CONTEXT_INFO @.BinarySoftwareUserIdentification
when i connect to the database so that later during my insert/update/delete
audit logging triggers i can get the user of the software that did the
modification.
Is it possible that a new connection could inherit the CONTEXT_INFO from an
old connection?
The situation is that the web-server itself is issuing all the connections,
so any connections made and dropped happen from the same machine. An
automated process on the server doesn't issue a call to SET CONTEXT_INFO
(for whatever reason). Could the pooled database connection that the process
gets have the same CONTEXT_INFO as the previous person who used that
connection?
Would SQL Server clear CONTEXT_INFO when a user using ADO closes their
connection? Or would it only get rid of it when the pool timeout finally
happens?
Put it another way, can one connection be impersonating the user who
previously owned that connection?Good question, Ian...
I never doubt the correctness of the author of this article:
http://www.sqldev.net/misc/sp_reset_connection.htm
It does say that sp_reset_connection does reset all SET options. However, CONTEXT_INFO could just be
a special case... I'd test it just to be certain. Note that the article specifies that the reset is
performed when the connection is *re-used* (lazy...).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ian Boyd" <ian.msnews010@.avatopia.com> wrote in message
news:%23KReMJceGHA.3388@.TK2MSFTNGP05.phx.gbl...
>i am issuing
> SET CONTEXT_INFO @.BinarySoftwareUserIdentification
> when i connect to the database so that later during my insert/update/delete audit logging triggers
> i can get the user of the software that did the modification.
> Is it possible that a new connection could inherit the CONTEXT_INFO from an old connection?
> The situation is that the web-server itself is issuing all the connections, so any connections
> made and dropped happen from the same machine. An automated process on the server doesn't issue a
> call to SET CONTEXT_INFO (for whatever reason). Could the pooled database connection that the
> process gets have the same CONTEXT_INFO as the previous person who used that connection?
> Would SQL Server clear CONTEXT_INFO when a user using ADO closes their connection? Or would it
> only get rid of it when the pool timeout finally happens?
>
> Put it another way, can one connection be impersonating the user who previously owned that
> connection?
>|||> I'd test it just to be certain.
i thought that trying to trick ADO into giving me the same pooled connection
would be like trying to get cats to walk in a line.
But my test program was
1. Open connection
2. Set Context info
3. Close connection
4. Open connection
5. Get Context info
i got the original context info back, and it turns out i (obviously) got the
same SPID both times. So, Microsoft, if you could update sp_reset_connection
in SQL Server X, that would be great.
And a service pack for SQL2000 would be nice too.|||> would be like trying to get cats to walk in a line.
I like the analogy... :-)
Thanks for letting us know that sp_reset_connection does not clear out CONTEXT_INFO. Seems like MS
missed that (I'd guess). Possibly because CONTEXT_INFO doesn't seem to be used very much.
> So, Microsoft, if you could update sp_reset_connection in SQL Server X, that would be great.
Did you do the feedback?
http://lab.msdn.microsoft.com/productfeedback/
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ian Boyd" <ian.msnews010@.avatopia.com> wrote in message
news:ukyJH5deGHA.3556@.TK2MSFTNGP02.phx.gbl...
>> I'd test it just to be certain.
> i thought that trying to trick ADO into giving me the same pooled connection would be like trying
> to get cats to walk in a line.
> But my test program was
> 1. Open connection
> 2. Set Context info
> 3. Close connection
> 4. Open connection
> 5. Get Context info
> i got the original context info back, and it turns out i (obviously) got the same SPID both times.
> So, Microsoft, if you could update sp_reset_connection in SQL Server X, that would be great.
> And a service pack for SQL2000 would be nice too.
>