multiple instances of spid's

I have sql server 2000 setup on 2003. When users are running queries agains
t
SQL Server from MS Access it opens up 10 - 18 instances for that user instea
d
of just 1 instance. Why would this happen and how can I fix it.
Your help/suggestion is greatly appreciated.
Thank youhelpplease wrote:
> I have sql server 2000 setup on 2003. When users are running queries
> against SQL Server from MS Access it opens up 10 - 18 instances for
> that user instead of just 1 instance. Why would this happen and how
> can I fix it.
> Your help/suggestion is greatly appreciated.
> Thank you
Sounds like an application design issue, not a SQL Server issue. You're
not saying you are seeing the same spid in sysprocesses listed multiple
times, are you? Either the application is programmed to perform multiple
connections to the database or this is happening automatically; possibly
because of active result sets in the application. Not an Access expert.
Probably better to ask this question in an Access ng.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Yes, I am seeing the same spid in sysprocesses listed multiple times.
What could be the problem?
Thank you
Divya
"David Gugick" wrote:

> helpplease wrote:
> Sounds like an application design issue, not a SQL Server issue. You're
> not saying you are seeing the same spid in sysprocesses listed multiple
> times, are you? Either the application is programmed to perform multiple
> connections to the database or this is happening automatically; possibly
> because of active result sets in the application. Not an Access expert.
> Probably better to ask this question in an Access ng.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>|||helpplease wrote:
> Yes, I am seeing the same spid in sysprocesses listed multiple times.
> What could be the problem?
> Thank you
> Divya
>
That's probably because the same spid is running in multiple threads on
multiple CPUs. 10-18 instances of a SPID sounds high. Not sure what is
really going on. What version (and SP) of SQL Server are you using? How
many CPUs?
David Gugick
Quest Software
www.imceda.com
www.quest.com|||SQL Server 2000, Service Pack 3.
I have 4 Processors.
Thank you
"David Gugick" wrote:

> helpplease wrote:
> That's probably because the same spid is running in multiple threads on
> multiple CPUs. 10-18 instances of a SPID sounds high. Not sure what is
> really going on. What version (and SP) of SQL Server are you using? How
> many CPUs?
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>|||Any Suggestions on this one.
Thank you
"helpplease" wrote:
[vbcol=seagreen]
> SQL Server 2000, Service Pack 3.
> I have 4 Processors.
> Thank you
> "David Gugick" wrote:
>

multiple instances of spid's

I have sql server 2000 setup on 2003. When users are running queries against
SQL Server from MS Access it opens up 10 - 18 instances for that user instead
of just 1 instance. Why would this happen and how can I fix it.
Your help/suggestion is greatly appreciated.
Thank you
helpplease wrote:
> I have sql server 2000 setup on 2003. When users are running queries
> against SQL Server from MS Access it opens up 10 - 18 instances for
> that user instead of just 1 instance. Why would this happen and how
> can I fix it.
> Your help/suggestion is greatly appreciated.
> Thank you
Sounds like an application design issue, not a SQL Server issue. You're
not saying you are seeing the same spid in sysprocesses listed multiple
times, are you? Either the application is programmed to perform multiple
connections to the database or this is happening automatically; possibly
because of active result sets in the application. Not an Access expert.
Probably better to ask this question in an Access ng.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Yes, I am seeing the same spid in sysprocesses listed multiple times.
What could be the problem?
Thank you
Divya
"David Gugick" wrote:

> helpplease wrote:
> Sounds like an application design issue, not a SQL Server issue. You're
> not saying you are seeing the same spid in sysprocesses listed multiple
> times, are you? Either the application is programmed to perform multiple
> connections to the database or this is happening automatically; possibly
> because of active result sets in the application. Not an Access expert.
> Probably better to ask this question in an Access ng.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>
|||helpplease wrote:
> Yes, I am seeing the same spid in sysprocesses listed multiple times.
> What could be the problem?
> Thank you
> Divya
>
That's probably because the same spid is running in multiple threads on
multiple CPUs. 10-18 instances of a SPID sounds high. Not sure what is
really going on. What version (and SP) of SQL Server are you using? How
many CPUs?
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||SQL Server 2000, Service Pack 3.
I have 4 Processors.
Thank you
"David Gugick" wrote:

> helpplease wrote:
> That's probably because the same spid is running in multiple threads on
> multiple CPUs. 10-18 instances of a SPID sounds high. Not sure what is
> really going on. What version (and SP) of SQL Server are you using? How
> many CPUs?
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>
|||Any Suggestions on this one.
Thank you
"helpplease" wrote:
[vbcol=seagreen]
> SQL Server 2000, Service Pack 3.
> I have 4 Processors.
> Thank you
> "David Gugick" wrote:
sql

multiple instances of spid's

I have sql server 2000 setup on 2003. When users are running queries against
SQL Server from MS Access it opens up 10 - 18 instances for that user instead
of just 1 instance. Why would this happen and how can I fix it.
Your help/suggestion is greatly appreciated.
Thank youhelpplease wrote:
> I have sql server 2000 setup on 2003. When users are running queries
> against SQL Server from MS Access it opens up 10 - 18 instances for
> that user instead of just 1 instance. Why would this happen and how
> can I fix it.
> Your help/suggestion is greatly appreciated.
> Thank you
Sounds like an application design issue, not a SQL Server issue. You're
not saying you are seeing the same spid in sysprocesses listed multiple
times, are you? Either the application is programmed to perform multiple
connections to the database or this is happening automatically; possibly
because of active result sets in the application. Not an Access expert.
Probably better to ask this question in an Access ng.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Yes, I am seeing the same spid in sysprocesses listed multiple times.
What could be the problem?
Thank you
Divya
"David Gugick" wrote:
> helpplease wrote:
> > I have sql server 2000 setup on 2003. When users are running queries
> > against SQL Server from MS Access it opens up 10 - 18 instances for
> > that user instead of just 1 instance. Why would this happen and how
> > can I fix it.
> >
> > Your help/suggestion is greatly appreciated.
> >
> > Thank you
> Sounds like an application design issue, not a SQL Server issue. You're
> not saying you are seeing the same spid in sysprocesses listed multiple
> times, are you? Either the application is programmed to perform multiple
> connections to the database or this is happening automatically; possibly
> because of active result sets in the application. Not an Access expert.
> Probably better to ask this question in an Access ng.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>|||helpplease wrote:
> Yes, I am seeing the same spid in sysprocesses listed multiple times.
> What could be the problem?
> Thank you
> Divya
>
That's probably because the same spid is running in multiple threads on
multiple CPUs. 10-18 instances of a SPID sounds high. Not sure what is
really going on. What version (and SP) of SQL Server are you using? How
many CPUs?
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||SQL Server 2000, Service Pack 3.
I have 4 Processors.
Thank you
"David Gugick" wrote:
> helpplease wrote:
> > Yes, I am seeing the same spid in sysprocesses listed multiple times.
> >
> > What could be the problem?
> >
> > Thank you
> > Divya
> >
> That's probably because the same spid is running in multiple threads on
> multiple CPUs. 10-18 instances of a SPID sounds high. Not sure what is
> really going on. What version (and SP) of SQL Server are you using? How
> many CPUs?
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>|||Any Suggestions on this one.
Thank you
"helpplease" wrote:
> SQL Server 2000, Service Pack 3.
> I have 4 Processors.
> Thank you
> "David Gugick" wrote:
> > helpplease wrote:
> > > Yes, I am seeing the same spid in sysprocesses listed multiple times.
> > >
> > > What could be the problem?
> > >
> > > Thank you
> > > Divya
> > >
> >
> > That's probably because the same spid is running in multiple threads on
> > multiple CPUs. 10-18 instances of a SPID sounds high. Not sure what is
> > really going on. What version (and SP) of SQL Server are you using? How
> > many CPUs?
> >
> > --
> > David Gugick
> > Quest Software
> > www.imceda.com
> > www.quest.com
> >
> >

Multiple datasets in one report?

I am trying to create a report that runs four queries.
If I use:
=(Fields!LongValueName.Value, "MTReg") I get:
c:\documents and settings\me\my documents\visual studio
projects\registrations\regVsAct.rdl The value expression
for the textbox 'LongValueName' contains an error:
[BC30198] ')' expected.
If I change it to =(Fields!MTReg.LongValueName.Value)
I get this:
c:\documents and settings\me\my documents\visual studio
projects\registrations\regVsAct.rdl The value expression
for the textbox 'LongValueName' refers to the
field 'MTReg'. Report item expressions can only refer to
fields within the current data set scope or, if inside an
aggregate, the specified data set scope.
Is this possible or am I just messing up the syntax?Looks like you are trying to refer to the field outside of any data regions.
You can change the expression to =First(Fields!LongValueName.Value,
"MTReg").
Fang Wang (MSFT)
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"bmurtha" <bmurtha@.corp.sms.ac> wrote in message
news:363c01c471d2$063f5060$a401280a@.phx.gbl...
> I am trying to create a report that runs four queries.
> If I use:
> =(Fields!LongValueName.Value, "MTReg") I get:
> c:\documents and settings\me\my documents\visual studio
> projects\registrations\regVsAct.rdl The value expression
> for the textbox 'LongValueName' contains an error:
> [BC30198] ')' expected.
> If I change it to =(Fields!MTReg.LongValueName.Value)
> I get this:
> c:\documents and settings\me\my documents\visual studio
> projects\registrations\regVsAct.rdl The value expression
> for the textbox 'LongValueName' refers to the
> field 'MTReg'. Report item expressions can only refer to
> fields within the current data set scope or, if inside an
> aggregate, the specified data set scope.
> Is this possible or am I just messing up the syntax?
>|||Thanks Fang,
Actually, as it turned out, I was trying to call a
dataset , say b, when I was using a table that was bound
to databset a. I didn't get that what I'm really dealing
with is a datagrid and that it has a bound connections.
This is what happens when you start depending on the GUI
rather then thinking for yourself. Also it didn't occur to
me that iterators for each of these datasets would be
completely different.
Regards,
Bryan
>--Original Message--
>Looks like you are trying to refer to the field outside
of any data regions.
>You can change the expression to =First(Fields!
LongValueName.Value,
>"MTReg").
>Fang Wang (MSFT)
>Microsoft SQL Server Reporting Services
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>"bmurtha" <bmurtha@.corp.sms.ac> wrote in message
>news:363c01c471d2$063f5060$a401280a@.phx.gbl...
>> I am trying to create a report that runs four queries.
>> If I use:
>> =(Fields!LongValueName.Value, "MTReg") I get:
>> c:\documents and settings\me\my documents\visual studio
>> projects\registrations\regVsAct.rdl The value expression
>> for the textbox 'LongValueName' contains an error:
>> [BC30198] ')' expected.
>> If I change it to =(Fields!MTReg.LongValueName.Value)
>> I get this:
>> c:\documents and settings\me\my documents\visual studio
>> projects\registrations\regVsAct.rdl The value expression
>> for the textbox 'LongValueName' refers to the
>> field 'MTReg'. Report item expressions can only refer
to
>> fields within the current data set scope or, if inside
an
>> aggregate, the specified data set scope.
>> Is this possible or am I just messing up the syntax?
>>
>
>.
>

Multiple Columns from WHERE?

Hey all,
I was curious if this was possible...
I basically have 2 queries I'd like to combine into one. The only difference in the queries is one clause in the WHERE statement

so here is an idea of what I'm talking about

SELECT COUNT(*) as HighStock FROM products WHERE qty > 100

now lets say I needed to do one for low I would have to run that query 2 times with different alias's and change the qty...

is there a way to get all that in one result set? Something like

SELECT COUNT(*) as HighStock, COUNT(*) as LowStock FROM products WHERE qty > 100 AND LowStock = qty < 20

so then my result would be
HighStock LowStock
50 10

anyone have any clues on that? thanks! :)We're going to take what Bugs Bunny used to call "a left turn at Albuquerque" on you to get to the same place:SELECT Sum(CASE WHEN qty > 100 THEN 1 END) AS HighStock
, Sum(CASE WHEN qty < 20 THEN 1 END) AS LowStock
FROM products-PatP|||Pat Phelan ur logic is gr8!! hats off 2 u|||hey Pat!
thanks a million, it actually worked :)
appreciate your help on that one!|||No problem! Always glad to do what I can to confuse things. ;)

-PatP|||PAt. Ur logic is gr8..Hats off to u|||Oh, that wascally Pat! Ain't he a stinker?|||Oh, that wascally Pat! Ain't he a stinker?He he he he he!

-PatP|||Hey Pat...
is it possible to add a DISTINCT to that sum statement somewhere? like give me the Sum(CASE WHEN qty > 100 THEN 1 END WHERE DISTINCT(myfield))
?
I'm sure that syntax is way off but I hope you get my meaning. I Appreciate your help on this one :)|||Ummm... Nope, clear as mud.

-PatP|||Pat, do you have any possible solutions as a workaround? It seems that I have to split up my query into a temp table get the distinct values into it, then query against the temp table. I was hoping to avoid having to make 4 seperate queries. thanks again|||I don't have any clue what you are asking, so I can't answer. I strongly suspect that there is a way to solve your problem, once I understand what you want.

An example would help a lot, showing a dozen or less rows of data, and the result set that you expect to be returned based on that data.

-PatP