Multiple Servers in ODBC Client Setup

OK, something weird is happening.
I have a standard Windows XP client and I am trying to set up a simple ODBC
entry. So I go to Control Panel/Administrative Tools/ODBC/System Tab. From
there I create a new ODBC entry connecting to a SQL Server. I enter in the
name of my ODBC and a descripton. When I hit the drop-down button to select
a server I see my 2 SQL Server machines (local machine also named "Jim" and
sbserver) but they are listed twice or even 3 times (see below) and the local
machine has even more entries. What gives and how do I clean this up? Both
seem to work when I "test" at the last step but I have a local application
using this ODBC and it is failing to run so I think it is having "issues"
with these multiple entries.
Thanks!
-Richard
e.g.
(local)
(local)
Jim
Jim
jim
sbserver
sbserver
Make sure your MDAC installation isn't flaky - you can use
component checker to verity the MDAC version and
installation. You can download the tool from:
http://msdn2.microsoft.com/en-us/data/aa937730.aspx
How many instances do you have on each of those servers?
Do you get the same list if you execute the following from
the command prompt:
osql -L
-Sue
On Fri, 16 Mar 2007 04:47:05 -0700, Richard K
<RichardK@.discussions.microsoft.com> wrote:

>OK, something weird is happening.
>I have a standard Windows XP client and I am trying to set up a simple ODBC
>entry. So I go to Control Panel/Administrative Tools/ODBC/System Tab. From
>there I create a new ODBC entry connecting to a SQL Server. I enter in the
>name of my ODBC and a descripton. When I hit the drop-down button to select
>a server I see my 2 SQL Server machines (local machine also named "Jim" and
>sbserver) but they are listed twice or even 3 times (see below) and the local
>machine has even more entries. What gives and how do I clean this up? Both
>seem to work when I "test" at the last step but I have a local application
>using this ODBC and it is failing to run so I think it is having "issues"
>with these multiple entries.
>Thanks!
>-Richard
>e.g.
>(local)
>(local)
>Jim
>Jim
>jim
>sbserver
>sbserver

Multiple servers

Hi All,

I have recently published a website to our webserver and i get a sql error. We have a webserver that does not have sqlserver on it and and our database server which does. i have used the configuration utility to to setup my users and roles which created the ASPNETDB in my local App_Data folder. Is there a way to copy this database to our database server and change the references so the site refers to the new instance on the database server as apposed to the local instance when a user logs in?

Thanks

Bryan

Are you storing the database connection information in the web application's web.config file? Or did you hard-code it into each sql command?

|||

all my other connections are stored in the webconfig. But as for the ASPNETDB.mdf connection string i have no idea where it is stored by default. Does it matter if there is no sql server on the pc that the site resides on. To my knowledge it does, so i need to find away to have the database on the database server and the site on our webserver. I dont know if i am making myself clear... if you create a simple site with a login.aspx with a login control and a default.aspx with a simple "HELLO" on it and choose ASP.net configuration web utility and setup some users and roles, it created a folder called App_Data where the ASPNETDB database is stored.

Now if this project was a piece of electornic equipment seperated into distinct pieces, i would like to remove the database piece from where it is in my application and physically move it to another geographical place namelly my other server (database server), but if i remove it totally, clip all the wires and remove it then my equipment (website) does not work correctly. So what i want to do is to extend the cables so that they are able to reach the other server (databse server) so my equipment (website) still works fine, just with the extended cable ( some sort of connection string stored somewhere! ) :-) kind of a dumb ass analogy, but im sure you get what I mean now.

I dont know if i am missing something or the only person that has done something like this but all i do is create a simple website, which works fine on my pc, and deploy it and the login doesnt work...

ANY suggestions will be greatly appreciated, ive been pulling my hair out for almost 2 days now trying to figure this out.

thanks for all the help

Bryan

|||

Hello,

The solution was: mounted the default sql serverdatabase (ASPNETDB) on our database server and added a connectionstring in the webconfig to point to it. what i had to do was remove theconnection string and then recreate it as follows

<remove name="LocalSqlServer"/>
<add name="LocalSqlServer" connectionString="DataSource=CORE;Initial Catalog=ASPNETDB;Persist Security Info=True;UserID=user;Password=password" providerName="System.Data.SqlClient"/>

this sorted out all my issues.

Thanks

Bryan

Multiple Server Reporting

Hi There,
I got a generic DW question.
Let me preface this
I need to design a report that displays data for 3 branches of same
companies, Branch A ,Branch B and Branch C. The problem is that each Branch
has their own distinct server and database. The schema is identical between
them, just
different data in each.
The report is identical for each Branch, the only different is that the
report needs to pull data from all the three Databases A,B,C to display the
report data for that Company as an aggregated view and Also data for each
branch individually.
How can I design the report or setup the data source.
1) One possible option I guess would be to create a Linked Server and
Distributed view that combines data from identical tables in 3 databases, and
use that view as the datasource in the report.
2) Other option is moving data to the centralized server and report from
that,
which would cause data redundancy both at local branch and at centralized
server and huge vloume of data movement and keeping track of data.
Just looking for what others have done in similar circumstances so I don't
spend multiple days architecting the wrong approach.
Thanks!
S
Centralize data is the better solution.
maybe there is some data to copy, but any query is local, so the response
time is good.
also, you can retrieve data for your reports only instead-of detailed
information.
"ERS Developer" <ERSDeveloper@.discussions.microsoft.com> wrote in message
news:C501637B-D71F-4F72-AF62-B06BBED83A8E@.microsoft.com...
> Hi There,
> I got a generic DW question.
> Let me preface this
> I need to design a report that displays data for 3 branches of same
> companies, Branch A ,Branch B and Branch C. The problem is that each
> Branch
> has their own distinct server and database. The schema is identical
> between
> them, just
> different data in each.
> The report is identical for each Branch, the only different is that the
> report needs to pull data from all the three Databases A,B,C to display
> the
> report data for that Company as an aggregated view and Also data for each
> branch individually.
> How can I design the report or setup the data source.
> 1) One possible option I guess would be to create a Linked Server and
> Distributed view that combines data from identical tables in 3 databases,
> and
> use that view as the datasource in the report.
> 2) Other option is moving data to the centralized server and report from
> that,
> which would cause data redundancy both at local branch and at centralized
> server and huge vloume of data movement and keeping track of data.
> Just looking for what others have done in similar circumstances so I don't
> spend multiple days architecting the wrong approach.
> Thanks!
> S
>
|||Hi S,
the generic question is
'I have distributed data in operational systems, how do I perform
consolidated reporting?'
and the generic answer is:
'bring the data together and integrate it via ODS/DW style processing
system depending on what your needs are. Then, if the data needs to be
re-distributed from the centralised data store back out to various
areas then do that.'
You will almost certainly find that even though the systems are the
same the data in it will be different and will not 'automatically' line
up unless someone forced the user community to use some set of data
standards which almost never happens.
However, with todays line speeds and tools like RS presenting data in
web browsers the need for distributring that data back out has
dramatically reduced. I rarely re-distribute the data back out to so
called 'data marts'...I have been an advocate of 'if the data is
valuable and important it is worth centralising and managing it
properly' for over 20 years now... ;-)....I have seen many people talk
about 'fetch the data from the operational system when it is needed'
come and go....many are still coming along and selling that as 'the
way to do reporting and analysis'.....the folks who go that way
generally regret it very much and end up migrating to an ODS/DW style
solution sooner or later...especially as volumes increase...
Best Regards
Peter Nolan
www.peternolan.com
|||Hi Peter,
Thanks for the clarification always there has been a debate whether or not
to move the data around.Looks like unless we do that no way we can get
efficient reports Let me try the DW.
/
S
"Peter Nolan" wrote:

> Hi S,
> the generic question is
> 'I have distributed data in operational systems, how do I perform
> consolidated reporting?'
> and the generic answer is:
> 'bring the data together and integrate it via ODS/DW style processing
> system depending on what your needs are. Then, if the data needs to be
> re-distributed from the centralised data store back out to various
> areas then do that.'
> You will almost certainly find that even though the systems are the
> same the data in it will be different and will not 'automatically' line
> up unless someone forced the user community to use some set of data
> standards which almost never happens.
> However, with todays line speeds and tools like RS presenting data in
> web browsers the need for distributring that data back out has
> dramatically reduced. I rarely re-distribute the data back out to so
> called 'data marts'...I have been an advocate of 'if the data is
> valuable and important it is worth centralising and managing it
> properly' for over 20 years now... ;-)....I have seen many people talk
> about 'fetch the data from the operational system when it is needed'
> come and go....many are still coming along and selling that as 'the
> way to do reporting and analysis'.....the folks who go that way
> generally regret it very much and end up migrating to an ODS/DW style
> solution sooner or later...especially as volumes increase...
> Best Regards
> Peter Nolan
> www.peternolan.com
>
|||I think the answer depends on how much data, the query performance
requirement, and the frequency of the report. If there is a relatively large
amount of data that would need to be moved, the report is only run once per
month, and the user doesn't really care if the report takes 20 minutes vs. 2
minutes to run, then just setup a partitioned view.
If there are multiple reports that could make use of the localized data,
then the case for building a localized copy of the data would be somewhat
stronger.
"ERS Developer" <ERSDeveloper@.discussions.microsoft.com> wrote in message
news:C501637B-D71F-4F72-AF62-B06BBED83A8E@.microsoft.com...
> Hi There,
> I got a generic DW question.
> Let me preface this
> I need to design a report that displays data for 3 branches of same
> companies, Branch A ,Branch B and Branch C. The problem is that each
> Branch
> has their own distinct server and database. The schema is identical
> between
> them, just
> different data in each.
> The report is identical for each Branch, the only different is that the
> report needs to pull data from all the three Databases A,B,C to display
> the
> report data for that Company as an aggregated view and Also data for each
> branch individually.
> How can I design the report or setup the data source.
> 1) One possible option I guess would be to create a Linked Server and
> Distributed view that combines data from identical tables in 3 databases,
> and
> use that view as the datasource in the report.
> 2) Other option is moving data to the centralized server and report from
> that,
> which would cause data redundancy both at local branch and at centralized
> server and huge vloume of data movement and keeping track of data.
> Just looking for what others have done in similar circumstances so I don't
> spend multiple days architecting the wrong approach.
> Thanks!
> S
>

Multiple Server Reporting

Hi There,
I got a generic DW question.
Let me preface this
I need to design a report that displays data for 3 branches of same
companies, Branch A ,Branch B and Branch C. The problem is that each Branch
has their own distinct server and database. The schema is identical between
them, just
different data in each.
The report is identical for each Branch, the only different is that the
report needs to pull data from all the three Databases A,B,C to display the
report data for that Company as an aggregated view and Also data for each
branch individually.
How can I design the report or setup the data source.
1) One possible option I guess would be to create a Linked Server and
Distributed view that combines data from identical tables in 3 databases, an
d
use that view as the datasource in the report.
2) Other option is moving data to the centralized server and report from
that,
which would cause data redundancy both at local branch and at centralized
server and huge vloume of data movement and keeping track of data.
Just looking for what others have done in similar circumstances so I don't
spend multiple days architecting the wrong approach.
Thanks!
SCentralize data is the better solution.
maybe there is some data to copy, but any query is local, so the response
time is good.
also, you can retrieve data for your reports only instead-of detailed
information.
"ERS Developer" <ERSDeveloper@.discussions.microsoft.com> wrote in message
news:C501637B-D71F-4F72-AF62-B06BBED83A8E@.microsoft.com...
> Hi There,
> I got a generic DW question.
> Let me preface this
> I need to design a report that displays data for 3 branches of same
> companies, Branch A ,Branch B and Branch C. The problem is that each
> Branch
> has their own distinct server and database. The schema is identical
> between
> them, just
> different data in each.
> The report is identical for each Branch, the only different is that the
> report needs to pull data from all the three Databases A,B,C to display
> the
> report data for that Company as an aggregated view and Also data for each
> branch individually.
> How can I design the report or setup the data source.
> 1) One possible option I guess would be to create a Linked Server and
> Distributed view that combines data from identical tables in 3 databases,
> and
> use that view as the datasource in the report.
> 2) Other option is moving data to the centralized server and report from
> that,
> which would cause data redundancy both at local branch and at centralized
> server and huge vloume of data movement and keeping track of data.
> Just looking for what others have done in similar circumstances so I don't
> spend multiple days architecting the wrong approach.
> Thanks!
> S
>|||Hi S,
the generic question is
'I have distributed data in operational systems, how do I perform
consolidated reporting?'
and the generic answer is:
'bring the data together and integrate it via ODS/DW style processing
system depending on what your needs are. Then, if the data needs to be
re-distributed from the centralised data store back out to various
areas then do that.'
You will almost certainly find that even though the systems are the
same the data in it will be different and will not 'automatically' line
up unless someone forced the user community to use some set of data
standards which almost never happens.
However, with todays line speeds and tools like RS presenting data in
web browsers the need for distributring that data back out has
dramatically reduced. I rarely re-distribute the data back out to so
called 'data marts'...I have been an advocate of 'if the data is
valuable and important it is worth centralising and managing it
properly' for over 20 years now... ;-)....I have seen many people talk
about 'fetch the data from the operational system when it is needed'
come and go....many are still coming along and selling that as 'the
way to do reporting and analysis'.....the folks who go that way
generally regret it very much and end up migrating to an ODS/DW style
solution sooner or later...especially as volumes increase...
Best Regards
Peter Nolan
www.peternolan.com|||Hi Peter,
Thanks for the clarification always there has been a debate whether or not
to move the data around.Looks like unless we do that no way we can get
efficient reports Let me try the DW.
/
S
"Peter Nolan" wrote:

> Hi S,
> the generic question is
> 'I have distributed data in operational systems, how do I perform
> consolidated reporting?'
> and the generic answer is:
> 'bring the data together and integrate it via ODS/DW style processing
> system depending on what your needs are. Then, if the data needs to be
> re-distributed from the centralised data store back out to various
> areas then do that.'
> You will almost certainly find that even though the systems are the
> same the data in it will be different and will not 'automatically' line
> up unless someone forced the user community to use some set of data
> standards which almost never happens.
> However, with todays line speeds and tools like RS presenting data in
> web browsers the need for distributring that data back out has
> dramatically reduced. I rarely re-distribute the data back out to so
> called 'data marts'...I have been an advocate of 'if the data is
> valuable and important it is worth centralising and managing it
> properly' for over 20 years now... ;-)....I have seen many people talk
> about 'fetch the data from the operational system when it is needed'
> come and go....many are still coming along and selling that as 'the
> way to do reporting and analysis'.....the folks who go that way
> generally regret it very much and end up migrating to an ODS/DW style
> solution sooner or later...especially as volumes increase...
> Best Regards
> Peter Nolan
> www.peternolan.com
>|||I think the answer depends on how much data, the query performance
requirement, and the frequency of the report. If there is a relatively large
amount of data that would need to be moved, the report is only run once per
month, and the user doesn't really care if the report takes 20 minutes vs. 2
minutes to run, then just setup a partitioned view.
If there are multiple reports that could make use of the localized data,
then the case for building a localized copy of the data would be somewhat
stronger.
"ERS Developer" <ERSDeveloper@.discussions.microsoft.com> wrote in message
news:C501637B-D71F-4F72-AF62-B06BBED83A8E@.microsoft.com...
> Hi There,
> I got a generic DW question.
> Let me preface this
> I need to design a report that displays data for 3 branches of same
> companies, Branch A ,Branch B and Branch C. The problem is that each
> Branch
> has their own distinct server and database. The schema is identical
> between
> them, just
> different data in each.
> The report is identical for each Branch, the only different is that the
> report needs to pull data from all the three Databases A,B,C to display
> the
> report data for that Company as an aggregated view and Also data for each
> branch individually.
> How can I design the report or setup the data source.
> 1) One possible option I guess would be to create a Linked Server and
> Distributed view that combines data from identical tables in 3 databases,
> and
> use that view as the datasource in the report.
> 2) Other option is moving data to the centralized server and report from
> that,
> which would cause data redundancy both at local branch and at centralized
> server and huge vloume of data movement and keeping track of data.
> Just looking for what others have done in similar circumstances so I don't
> spend multiple days architecting the wrong approach.
> Thanks!
> S
>

Multiple Server Reporting

Let me preface this
I need to design a report that displays data for 3 branches of same
companies,
Branch A ,Branch B and Branch C. The problem is that each Branch has their
own
distinct server and database. The schema is identical between them, just
different data in each.
The report is identical for each Branch, the only different is that the
report needs to pull data from all the three Databases A,B,C to display the
report data for that Company as an aggregated view and Also data for each
branch
individually.
How can I design the report or setup the data source.
I really don't want to create and maintain 3 identical reports, the only
difference being the data source.
One possible option I guess would be to create a Linked Server and
Distributed view that combines data from identical tables in 3 databases, and
use that view as the datasource in the report.
Other option is moving data to the centralized server and report from that,
which would cause data redundancy both at local branch and at centralized
server and huge vloume of data movement and keeping track of data.
Just looking for what others have done in similar circumstances so I don't
spend multiple days architecting the wrong approach.
Thanks!
SHi There,
I was under impression that there are expertise here who has implemented a
valid and reliable solution to my similar issue. Now it makes me to beleive
that none has gone through such a senario.
Any help or suggestion are welcome
/
S
"ERS Developer" wrote:
> Let me preface this
> I need to design a report that displays data for 3 branches of same
> companies,
> Branch A ,Branch B and Branch C. The problem is that each Branch has their
> own
> distinct server and database. The schema is identical between them, just
> different data in each.
> The report is identical for each Branch, the only different is that the
> report needs to pull data from all the three Databases A,B,C to display the
> report data for that Company as an aggregated view and Also data for each
> branch
> individually.
> How can I design the report or setup the data source.
> I really don't want to create and maintain 3 identical reports, the only
> difference being the data source.
> One possible option I guess would be to create a Linked Server and
> Distributed view that combines data from identical tables in 3 databases, and
> use that view as the datasource in the report.
> Other option is moving data to the centralized server and report from that,
> which would cause data redundancy both at local branch and at centralized
> server and huge vloume of data movement and keeping track of data.
> Just looking for what others have done in similar circumstances so I don't
> spend multiple days architecting the wrong approach.
> Thanks!
> S|||ERS,
The simplest solution is linked servers (if you have high
availability), have one report and use IF statements based on a
parameter to decide the query to use.
The parameter would be the branch, so you could have;
If @.Branch = 'A'
Select * From BranchAServer.MyDB.dbo.MyTable
If @.Branch = 'B'
Select * From BranchBServer.MyDB.dbo.MyTable
If @.Branch = 'C'
Select * From BranchCServer.MyDB.dbo.MyTable
So the user could select their branch. This is very basic and could be
made more efficient placing the query in a stored procedure. Also there
is aperformance issue with distributed queries like this pulling all
data to the calling server before processing. Ideally you'd use an
OPENQUERY, check BOL for info on that.
Letting the user select a branch is obviously a security issue, but you
could deny access to the main report but create 'linked' reports for
each branch and set the branch parameter in properties of the linked
report and set it not to prompt the user. 'Linked' reports are merely
shortcuts, so when you change the main report, it's reflected in the
linked reports as well, but the parameter settings remain independant.
There is another option which we use here, which is to create a simple
form of data warehouse. This is more useful if you have disparate
datasources. Basically, create jobs that copies data from the branches
into a central database periodically, say every 20 minutes. To reduce
redundancy, just take the fields you need. You could use replication to
do this also. Not only will this make your report quicker, but it
should reduce the overhead on your OLTP servers.
Chris
ERS Developer wrote:
> Hi There,
> I was under impression that there are expertise here who has
> implemented a valid and reliable solution to my similar issue. Now it
> makes me to beleive that none has gone through such a senario.
> Any help or suggestion are welcome
> /
> S
> "ERS Developer" wrote:
> > Let me preface this
> > I need to design a report that displays data for 3 branches of same
> > companies,
> > Branch A ,Branch B and Branch C. The problem is that each Branch
> > has their own
> > distinct server and database. The schema is identical between them,
> > just different data in each.
> >
> > The report is identical for each Branch, the only different is that
> > the report needs to pull data from all the three Databases A,B,C to
> > display the report data for that Company as an aggregated view and
> > Also data for each branch
> > individually.
> >
> > How can I design the report or setup the data source.
> >
> > I really don't want to create and maintain 3 identical reports, the
> > only difference being the data source.
> >
> > One possible option I guess would be to create a Linked Server and
> > Distributed view that combines data from identical tables in 3
> > databases, and use that view as the datasource in the report.
> >
> > Other option is moving data to the centralized server and report
> > from that, which would cause data redundancy both at local branch
> > and at centralized server and huge vloume of data movement and
> > keeping track of data.
> >
> > Just looking for what others have done in similar circumstances so
> > I don't spend multiple days architecting the wrong approach.
> >
> > Thanks!
> > S|||Hi S/All,
just to let viewers here know the generic answer to the generic
question...
Lots of people, tens of thousands of them, have faced the issue of
reporting from multiple different oprational systems and the Corporate
Information Factory is one rather comprehensive solution to the
pervasive issue of consolitated analysis/reporting...
Best Regards
Peter Nolan
www.peternolan.com
http://groups-beta.google.com/group/microsoft.public.sqlserver.datawarehouse/browse_frm/thread/473404688a753292/64024bd1636f02e9?hl=en#64024bd1636f02e9

Multiple Server query

I'm trying to write a query that will be used in a stored
proc to grab data from a table on a different server.
So I have SVR1 trying to get data from SVR2, so I'm trying
to use this query:
SELECT * FROM SVR2..TESTDB.TESTTBL
but I get the following error:
OLE DB provider 'SVR2' does not contain
table '"dbo"."master"'. The table either does not exist
or the current user does not have permissions on that
table.
OLE DB error trace [Non-interface error: OLE DB provider
does not contain the table: ProviderName='SVR2',
TableName='"dbo"."master"'].
Can anyone help?
Thanks,
Chris
Chris,
It seems more likely that you would want
select * from SVR2.TESTDB..TESTTBL
though I'm not quite sure why you get this particular error. What did
you use to add the server SVR2?
Steve Kass
Drew University
Chris W wrote:

>I'm trying to write a query that will be used in a stored
>proc to grab data from a table on a different server.
>So I have SVR1 trying to get data from SVR2, so I'm trying
>to use this query:
>SELECT * FROM SVR2..TESTDB.TESTTBL
>but I get the following error:
>OLE DB provider 'SVR2' does not contain
>table '"dbo"."master"'. The table either does not exist
>or the current user does not have permissions on that
>table.
>OLE DB error trace [Non-interface error: OLE DB provider
>does not contain the table: ProviderName='SVR2',
>TableName='"dbo"."master"'].
>
>Can anyone help?
>Thanks,
>Chris
>
>

Multiple server deployment

I have a multiple report server deployment, and we removed one of the servers from the farm, however the name still appears in the list on the initialize screen in report server configurator.

We removed reporting services from this box, but the name still appears in the list.

Where is this server name being stored?

Thanks!!

BobP

Just for posterity... It is being stored in the Keys table in the ReportServer database.

BobP

Multiple server deployment

I have a multiple report server deployment, and we removed one of the servers from the farm, however the name still appears in the list on the initialize screen in report server configurator.

We removed reporting services from this box, but the name still appears in the list.

Where is this server name being stored?

Thanks!!

BobP

Just for posterity... It is being stored in the Keys table in the ReportServer database.

BobP

Multiple server connections with XP client to SQL server using Great Plains

We have 2 XP clients using Great Plains client connecting
to a Small Business Server running SQL 2000. When these
users start up Great Plains they each are using multiple
server connections/sessions which is eating up our
limited licenses. Our Windows 2000 users only use one
connection but the two XP users are taking up 4 and 7
licenses each. I'm not sure if it is an XP, Great
Plains, SQL or Small Business Server issue.
.SQL server cals are machine based, a single machine with
sql cal can open multiple connections to a sql server and
it can also connect to multiple sql servers at the same
time.
Are you getting an error for exceeding licenses ?
Use sp_who command in sql query analyzer to find out how
many connections are open.
>--Original Message--
>We have 2 XP clients using Great Plains client connecting
>to a Small Business Server running SQL 2000. When these
>users start up Great Plains they each are using multiple
>server connections/sessions which is eating up our
>limited licenses. Our Windows 2000 users only use one
>connection but the two XP users are taking up 4 and 7
>licenses each. I'm not sure if it is an XP, Great
>Plains, SQL or Small Business Server issue.
>..
>
>.
>|||We've got a 25 user Small Business Server CAL. As soon
as we get 25 connections (as seen through a NETSTAT
command) the server refuses to allow anymore. We
discovered that the two machines that are creating
multiple connections are XP and it appears to be ODBC
related.
>--Original Message--
>SQL server cals are machine based, a single machine with
>sql cal can open multiple connections to a sql server
and
>it can also connect to multiple sql servers at the same
>time.
>Are you getting an error for exceeding licenses ?
>Use sp_who command in sql query analyzer to find out how
>many connections are open.
>>--Original Message--
>>We have 2 XP clients using Great Plains client
connecting
>>to a Small Business Server running SQL 2000. When
these
>>users start up Great Plains they each are using
multiple
>>server connections/sessions which is eating up our
>>limited licenses. Our Windows 2000 users only use one
>>connection but the two XP users are taking up 4 and 7
>>licenses each. I'm not sure if it is an XP, Great
>>Plains, SQL or Small Business Server issue.
>>..
>>
>>.
>.
>

multiple series on line chart

Hello,

I want to display more than one series on a line chart. I'm producing a resultset from a stored procedure which is like this:

Type Value Date

TypeA 0.07 01/06/2006

TypeB 0.08 01/06/2006

TypeC 0.08 01/06/2006

TypeA 0.08 02/06/2006

TypeB 0.09 02/06/2006

TypeC 0.09 02/06/2006

TypeA 0.09 03/06/2006

TypeB 0.10 03/06/2006

TypeC 0.11 03/06/2006

I want a seperate series for each of TypeA, TypeB, TypeC . Is it possible to doctor the resultset to give each Type (and Value) as unique columns? Or is there another way? Or do I have to shell out for Dundas Charts :) !

Thanks in advance for any help.

Sorry, I'm new to RS - I had overlooked the 'Series' area on the layout tab. Can it just be a matter of dropping the 'Type' field there? I'm not at my desk just now but will try in the morning - if someone feels like confirming meantime that would be great.|||

Dragging the 'Type' field from the dataset onto the series area on the layout tab gives me curves for TypeA, TypeB,TypeC. However the dates on the horizontal axis are all wrong 'Year' and 'Month' groups are OK but 'Days' are back to front (I have 3 category groups) - I need this because sometimes the dataset crosses more than 1 year. Anyone ?

|||All problems solved. In case anyone has similar problems - the issue was the dataset - the dates were not uniform for each Type ie.each series had a different set of dates.Once I got the dates matching everything was OK.

Multiple selects on same column, same table, one query

I want to get a column count several times in one query using different
filters but can't work out how to do it - can anyone point me in the right
direction?

For example, how would combine these two selects into one query that will
list the total and filtered actions:

SELECT COUNT(actions) as actioncount, location
FROM mytable
GROUP BY location

SELECT COUNT(actions) as actioncount, location
FROM mytable
WHERE mycondition IS NULL
GROUP BY locationYou can use subqueries like this:

select location,
actioncount1 = (select count(actions)
from mytable T1
where T1.location = mytable.location
),
actioncount2 = (select count(actions)
from mytable T2
where mycondition is null
and T2.location = mytable.location
)
from mytable
group by location

Shervin

"JackT" <turnbull.jack@.ntlworld.com> wrote in message
news:FpGcb.208$%f1.164@.newsfep1-gui.server.ntli.net...
> I want to get a column count several times in one query using different
> filters but can't work out how to do it - can anyone point me in the right
> direction?
> For example, how would combine these two selects into one query that will
> list the total and filtered actions:
> SELECT COUNT(actions) as actioncount, location
> FROM mytable
> GROUP BY location
> SELECT COUNT(actions) as actioncount, location
> FROM mytable
> WHERE mycondition IS NULL
> GROUP BY location|||SELECT COUNT(CASE WHEN mycondition IS NULL THEN actions END) AS
actioncount1,
COUNT(actions) AS actioncount2,
location
FROM mytable
GROUP BY location

--
David Portas
----
Please reply only to the newsgroup|||Nice! :-)

"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:l7Cdncag0qX-3e6iRVn-ug@.giganews.com...
> SELECT COUNT(CASE WHEN mycondition IS NULL THEN actions END) AS
> actioncount1,
> COUNT(actions) AS actioncount2,
> location
> FROM mytable
> GROUP BY location
> --
> David Portas
> ----
> Please reply only to the newsgroup|||Thanks Shervin,
Works exactly as I need, I am indebted!
Cheers,
Jack
"Shervin Shapourian" <ShShapourian@.hotmail.com> wrote in message
news:vn6fu8bgg3qt64@.corp.supernews.com...
> You can use subqueries like this:
> select location,
> actioncount1 = (select count(actions)
> from mytable T1
> where T1.location = mytable.location
> ),
> actioncount2 = (select count(actions)
> from mytable T2
> where mycondition is null
> and T2.location = mytable.location
> )
> from mytable
> group by location
> Shervin
> "JackT" <turnbull.jack@.ntlworld.com> wrote in message
> news:FpGcb.208$%f1.164@.newsfep1-gui.server.ntli.net...
> > I want to get a column count several times in one query using different
> > filters but can't work out how to do it - can anyone point me in the
right
> > direction?
> > For example, how would combine these two selects into one query that
will
> > list the total and filtered actions:
> > SELECT COUNT(actions) as actioncount, location
> > FROM mytable
> > GROUP BY location
> > SELECT COUNT(actions) as actioncount, location
> > FROM mytable
> > WHERE mycondition IS NULL
> > GROUP BY location|||You're welcome my friend, but take a look at David's code. I prefer his way
:-) Subqueries are not the most efficient way to do thing in most cases. But
they are so easy to use. They are for lazy guys like me ;-)

Shervin

"JackT" <turnbull.jack@.ntlworld.com> wrote in message
news:vpHcb.266$%f1.56@.newsfep1-gui.server.ntli.net...
> Thanks Shervin,
> Works exactly as I need, I am indebted!
> Cheers,
> Jack
> "Shervin Shapourian" <ShShapourian@.hotmail.com> wrote in message
> news:vn6fu8bgg3qt64@.corp.supernews.com...
> > You can use subqueries like this:
> > select location,
> > actioncount1 = (select count(actions)
> > from mytable T1
> > where T1.location = mytable.location
> > ),
> > actioncount2 = (select count(actions)
> > from mytable T2
> > where mycondition is null
> > and T2.location = mytable.location
> > )
> > from mytable
> > group by location
> > Shervin
> > "JackT" <turnbull.jack@.ntlworld.com> wrote in message
> > news:FpGcb.208$%f1.164@.newsfep1-gui.server.ntli.net...
> > > I want to get a column count several times in one query using
different
> > > filters but can't work out how to do it - can anyone point me in the
> right
> > > direction?
> > > > For example, how would combine these two selects into one query that
> will
> > > list the total and filtered actions:
> > > > SELECT COUNT(actions) as actioncount, location
> > > FROM mytable
> > > GROUP BY location
> > > > SELECT COUNT(actions) as actioncount, location
> > > FROM mytable
> > > WHERE mycondition IS NULL
> > > GROUP BY location
> >|||Wow!
Cheers,
Jack

"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:l7Cdncag0qX-3e6iRVn-ug@.giganews.com...
> SELECT COUNT(CASE WHEN mycondition IS NULL THEN actions END) AS
> actioncount1,
> COUNT(actions) AS actioncount2,
> location
> FROM mytable
> GROUP BY location
> --
> David Portas
> ----
> Please reply only to the newsgroup

Multiple selects on PArameter drop down

I have a parameter dropdown as USERID.

I want a functionality that the user can selct multiple userid's to show more than one record as output for comparison purpose.

How can i achieve this multiple select (i.e cntrl+click) within parameter dropdown.

Thanks,

Kiran.

In the report parameters window, check the 'Multi-value' box on your UserID parameter. In the available values section, you can type the possible values, or query the database to get the list.

You will also need to take the multiple values into account on your data query, you'll need to use 'IN' instead of '=' on your where clause for UserID.

This will show on the report as a drop down, where the user can check which values to run the report with.

Hope this helps,

Jarret

|||

Thank you Jarret.

I cannot find teh checkbox for 'multi-value'' allowed . I am using reporting services 2000 on visual Studios.NET 2003 SQL Server 2000.

Is this an issue for me only. I have the checkboxes for allow null values and allow blank values for the userid parameter.

thanks.

|||

Sorry, I had assumed that you were running RS 2005. In Reporting Services 2000, a report parameter can only be defined to allow the user to enter a single value.

Jarret

|||

Thanks Jarret,

Is there a wor around that I should be trying.

Appreciate the time.

|||

Hmmm...

You could try changing the parameter type to string and make your data query use 'IN', then have the user type a comma delimited list. I haven't tried it, but you might be able to get it to work.

Jarret

|||Did this work for you?|||

This did not work for me. I shifted to 2005 and implemented it.

Thanks

Multiple selects on PArameter drop down

I have a parameter dropdown as USERID.

I want a functionality that the user can selct multiple userid's to show more than one record as output for comparison purpose.

How can i achieve this multiple select (i.e cntrl+click) within parameter dropdown.

Thanks,

Kiran.

In the report parameters window, check the 'Multi-value' box on your UserID parameter. In the available values section, you can type the possible values, or query the database to get the list.

You will also need to take the multiple values into account on your data query, you'll need to use 'IN' instead of '=' on your where clause for UserID.

This will show on the report as a drop down, where the user can check which values to run the report with.

Hope this helps,

Jarret

|||

Thank you Jarret.

I cannot find teh checkbox for 'multi-value'' allowed . I am using reporting services 2000 on visual Studios.NET 2003 SQL Server 2000.

Is this an issue for me only. I have the checkboxes for allow null values and allow blank values for the userid parameter.

thanks.

|||

Sorry, I had assumed that you were running RS 2005. In Reporting Services 2000, a report parameter can only be defined to allow the user to enter a single value.

Jarret

|||

Thanks Jarret,

Is there a wor around that I should be trying.

Appreciate the time.

|||

Hmmm...

You could try changing the parameter type to string and make your data query use 'IN', then have the user type a comma delimited list. I haven't tried it, but you might be able to get it to work.

Jarret

|||Did this work for you?|||

This did not work for me. I shifted to 2005 and implemented it.

Thanks

Multiple selects on PArameter drop down

I have a parameter dropdown as USERID.

I want a functionality that the user can selct multiple userid's to show more than one record as output for comparison purpose.

How can i achieve this multiple select (i.e cntrl+click) within parameter dropdown.

Thanks,

Kiran.

In the report parameters window, check the 'Multi-value' box on your UserID parameter. In the available values section, you can type the possible values, or query the database to get the list.

You will also need to take the multiple values into account on your data query, you'll need to use 'IN' instead of '=' on your where clause for UserID.

This will show on the report as a drop down, where the user can check which values to run the report with.

Hope this helps,

Jarret

|||

Thank you Jarret.

I cannot find teh checkbox for 'multi-value'' allowed . I am using reporting services 2000 on visual Studios.NET 2003 SQL Server 2000.

Is this an issue for me only. I have the checkboxes for allow null values and allow blank values for the userid parameter.

thanks.

|||

Sorry, I had assumed that you were running RS 2005. In Reporting Services 2000, a report parameter can only be defined to allow the user to enter a single value.

Jarret

|||

Thanks Jarret,

Is there a wor around that I should be trying.

Appreciate the time.

|||

Hmmm...

You could try changing the parameter type to string and make your data query use 'IN', then have the user type a comma delimited list. I haven't tried it, but you might be able to get it to work.

Jarret

|||Did this work for you?|||

This did not work for me. I shifted to 2005 and implemented it.

Thanks

Multiple selects in sp

Hi
In a stored procedure, is it possible to check if an input parameter value
has been supplied by user? Furthermore if a parameter's value is supplied
then execute one select statement, if no value is supplied then use a second
select?
Thanks
Regards
try this:
create proc myproc
param int null
as
declare @.sql varchar(100)
if param is null
set @.sql = '...' -- your first select
else
set @.sql = '...' -- your second select
exec (@.sql)
go
|||Ok, I have this sp (attached). Still can't make it work.
Thanks
Regards
"vuht2000" <vuht2000@.yahoo.com> wrote in message
news:1124415729.139947.214020@.g44g2000cwa.googlegr oups.com...
> try this:
> create proc myproc
> param int null
> as
> declare @.sql varchar(100)
> if param is null
> set @.sql = '...' -- your first select
> else
> set @.sql = '...' -- your second select
> exec (@.sql)
> go
>
begin 666 SQLQuery.sql
M__YS`&4`= `@.`$$`3@.!3`$D`7P!.`%4`3 !,`%,`( !/`$X`#0`*`',`90!T
M`" `40!5`$\`5 !%`$0`7P!)`$0`10!.`%0`20!&`$D`10!2`" `3P!.``T`
M"@.!G`&\`#0`*``T`"@.!!`$P`5 !%`%(`( !0`%(`3P!#`$4`1 !5`%(`10`@.
M`%L`9 !B`&\`70`N`%L`0P!L`&D`90!N`'0`<P!3`&4`; !E`&,`= !#`&\`
M;0!M`&$`;@.!D`%T`#0`*`$ `0P!O`&T`< !A`&X`>0!4`'D`< !E`" `=@.!A
M`'(`8P!H`&$`<@.`H`#4`, `I`"P`#0`*`$ `4P!T`&$`= !U`',`( !V`&$`
M<@.!C`&@.`80!R`"@.`-0`P`"D`+ `-``H`0 !"`'4`<P!I`&X`90!S`',`1 !I
M`'8`:0!S`&D`;P!N`" `=@.!A`'(`8P!H`&$`<@.`H`#4`, `I`" `3@.!5`$P`
M3 `-``H`00!3``T`"@.!3`$4`5 `@.`$X`3P!#`$\`50!.`%0`( !/`$X`.P`-
M``H`1 !%`$,`3 !!`%(`10`@.`$ `<P!Q`&P`( !V`&$`<@.!C`&@.`80!R`"@.`
M,0`P`# `*0`-``H`20!&`" `0 !"`'4`<P!I`&X`90!S`',`1 !I`'8`:0!S
M`&D`;P!N`" `:0!S`" `3@.!5`$P`3 `-``H`( `@.`%,`10!4`" `0 !S`'$`
M; `@.`#T`( `G`%,`10!,`$4`0P!4`" `20!$`"P`( !#`&\`;0!P`&$`;@.!Y
M`"P`( !0`&$`<@.!E`&X`= !#`&\`;0!P`&$`;@.!Y`"P`( !!`&0`9 !R`&4`
M<P!S`"P`( !#`&\`=0!N`'0`>0`L`" `4 !O`',`= !C`&\`9 !E`"P`( !#
M`&\`=0!N`'0`<@.!Y`"P`( !4`&4`; `L`" `1@.!A`'@.`+ `@.`$4`30!A`&D`
M; `L`" `5P!E`&(`+ `@.`$D`;@.!V`&\`:0!C`&4`0P!O`&T`< !A`&X`>0`L
M`" `20!N`'8`;P!I`&,`90!!`&0`9 !R`&4`<P!S`"P`( !)`&X`=@.!O`&D`
M8P!E`$,`;P!U`&X`= !Y`"P`( !)`&X`=@.!O`&D`8P!E`% `;P!S`'0`8P!O
M`&0`90`L`" `20!N`'8`;P!I`&,`90!#`&\`=0!N`'0`<@.!Y`"P`( !"`'4`
M<P!I`&X`90!S`',`5 !Y`' `90`L`" `4P!T`&$`= !U`',`+ `@.`%,`= !A
M`'0`=0!S`$0`80!T`&4`+ `@.`%,`;P!U`'(`8P!E`"P`( !3`&\`=0!R`&,`
M90!$`&$`= !E`"P`( !#`&\`;0!P`&$`;@.!Y`%0`>0!P`&4`+ `@.`$(`; !O
M`&,`:P!E`&0`+ `@.`$(`; !O`&,`:P!E`&0`1 !E`' `80!R`'0`;0!E`&X`
M= `L`" `0@.!L`&\`8P!K`&4`9 !$`&$`= !E`"P`( !"`&P`;P!C`&L`90!D
M`%(`90!A`',`;P!N`"P`( !"`'4`<P!I`&X`90!S`',`00!R`&4`80`L`" `
M4P!T`&$`9@.!F`$<`<@.!A`&0`90`Q`"P`( !3`'0`80!F`&8`1P!R`&$`9 !E
M`#(`+ `@.`%,`= !A`&8`9@.!'`'(`80!D`&4`,P`L`" `4P!T`&$`9@.!F`$<`
M<@.!A`&0`90`T`"P`( !#`&P`:0!E`&X`= !.`&\`= !E`',`+ `@.`$8`:0!N
M`&$`;@.!C`&D`80!L`$,`;P!M`&T`90!N`'0`<P`L`" `4P!P`&4`8P!I`&$`
M; !2`&4`<0!U`&D`<@.!M`&4`;@.!T`',`+ `@.`%4`;@.!I`&8`;P!R`&T`<P!.
M`&4`90!D`&4`9 `L`" `00!C`&,`;P!U`&X`= !2`&4`9@.!E`'(`90!N`&,`
M90!.`&\`+ `@.`$,`;P!M`' `80!N`'D`4@.!E`&<`:0!S`'0`<@.!A`'0`:0!O
M`&X`3@.!O`"P`( !3`'4`< !P`&P`:0!E`'(`3@.!O`"P`( !6`&4`;@.!T`'4`
M<@.!E`$0`90!B`'0`;P!R`$X`;P`L`" `4 !A`'D`;0!E`&X`= !4`&4`<@.!M
M`',`+ `@.`%,`= !A`'0`90!M`&4`;@.!T`$$`9 !D`'(`90!S`',`+ `@.`$4`
M=@.!E`&X`= !.`&\`= !E`',`+ `@.`$0`:0!R`&4`8P!T`&D`;P!N`',`+ `@.
M`$T`80!P`% `80!T`&@.`+ `@.`%,`;P!R`'0`1P!R`&\`=0!P`"P`( !3`&\`
M<@.!T`"P`( !$`&$`= !E`$,`<@.!E`&$`= !E`&0`+ `@.`$0`80!T`&4`30!O
M`&0`:0!F`&D`90!D`"P`( !#`'(`90!A`'0`90!D`$(`>0`L`" `30!O`&0`
M:0!F`&D`90!D`$(`>0`L`" `=0!P`',`:0!Z`&4`7P!T`',`( `G`" `)@.`@.
M``T`"@.`@.`" `( `@.`" `( `@.`" `( `@.`" `( `@.`"<`1@.!2`$\`30`@.`&0`
M8@.!O`"X`0P!L`&D`90!N`'0`<P`@.`"<`( `F`" `#0`*`" `( `@.`" `( `@.
M`" `( `@.`" `( `@.`" `)P!7`$@.`10!2`$4`( `H`$,`;P!M`' `80!N`'D`
M5 !Y`' `90`@.`&P`:0!K`&4`( ! `$,`;P!M`' `80!N`'D`5 !Y`' `90`@.
M`"8`( `B`"4`(@.`I`" `00!.`$0`( `G`" `)@.`@.``T`"@.`@.`" `( `@.`" `
M( `@.`" `( `@.`" `( `@.`" `( `@.`" `( `@.`"<`* !3`'0`80!T`'4`<P`@.
M`&P`:0!K`&4`( ! `%,`= !A`'0`=0!S`" `)@.`@.`"(`)0`B`"D`)P`-``H`
M10!,`%,`10`-``H`( `@.`%,`10!4`" `0 !S`'$`; `@.`#T`( `G`%,`10!,
M`$4`0P!4`" `20!$`"P`( !#`&\`;0!P`&$`;@.!Y`"P`( !0`&$`<@.!E`&X`
M= !#`&\`;0!P`&$`;@.!Y`"P`( !!`&0`9 !R`&4`<P!S`"P`( !#`&\`=0!N
M`'0`>0`L`" `4 !O`',`= !C`&\`9 !E`"P`( !#`&\`=0!N`'0`<@.!Y`"P`
M( !4`&4`; `L`" `1@.!A`'@.`+ `@.`$4`30!A`&D`; `L`" `5P!E`&(`+ `@.
M`$D`;@.!V`&\`:0!C`&4`0P!O`&T`< !A`&X`>0`L`" `20!N`'8`;P!I`&,`
M90!!`&0`9 !R`&4`<P!S`"P`( !)`&X`=@.!O`&D`8P!E`$,`;P!U`&X`= !Y
M`"P`( !)`&X`=@.!O`&D`8P!E`% `;P!S`'0`8P!O`&0`90`L`" `20!N`'8`
M;P!I`&,`90!#`&\`=0!N`'0`<@.!Y`"P`( !"`'4`<P!I`&X`90!S`',`5 !Y
M`' `90`L`" `4P!T`&$`= !U`',`+ `@.`%,`= !A`'0`=0!S`$0`80!T`&4`
M+ `@.`%,`;P!U`'(`8P!E`"P`( !3`&\`=0!R`&,`90!$`&$`= !E`"P`( !#
M`&\`;0!P`&$`;@.!Y`%0`>0!P`&4`+ `@.`$(`; !O`&,`:P!E`&0`+ `@.`$(`
M; !O`&,`:P!E`&0`1 !E`' `80!R`'0`;0!E`&X`= `L`" `0@.!L`&\`8P!K
M`&4`9 !$`&$`= !E`"P`( !"`&P`;P!C`&L`90!D`%(`90!A`',`;P!N`"P`
M( !"`'4`<P!I`&X`90!S`',`00!R`&4`80`L`" `4P!T`&$`9@.!F`$<`<@.!A
M`&0`90`Q`"P`( !3`'0`80!F`&8`1P!R`&$`9 !E`#(`+ `@.`%,`= !A`&8`
M9@.!'`'(`80!D`&4`,P`L`" `4P!T`&$`9@.!F`$<`<@.!A`&0`90`T`"P`( !#
M`&P`:0!E`&X`= !.`&\`= !E`',`+ `@.`$8`:0!N`&$`;@.!C`&D`80!L`$,`
M;P!M`&T`90!N`'0`<P`L`" `4P!P`&4`8P!I`&$`; !2`&4`<0!U`&D`<@.!M
M`&4`;@.!T`',`+ `@.`%4`;@.!I`&8`;P!R`&T`<P!.`&4`90!D`&4`9 `L`" `
M00!C`&,`;P!U`&X`= !2`&4`9@.!E`'(`90!N`&,`90!.`&\`+ `@.`$,`;P!M
M`' `80!N`'D`4@.!E`&<`:0!S`'0`<@.!A`'0`:0!O`&X`3@.!O`"P`( !3`'4`
M< !P`&P`:0!E`'(`3@.!O`"P`( !6`&4`;@.!T`'4`<@.!E`$0`90!B`'0`;P!R
M`$X`;P`L`" `4 !A`'D`;0!E`&X`= !4`&4`<@.!M`',`+ `@.`%,`= !A`'0`
M90!M`&4`;@.!T`$$`9 !D`'(`90!S`',`+ `@.`$4`=@.!E`&X`= !.`&\`= !E
M`',`+ `@.`$0`:0!R`&4`8P!T`&D`;P!N`',`+ `@.`$T`80!P`% `80!T`&@.`
M+ `@.`%,`;P!R`'0`1P!R`&\`=0!P`"P`( !3`&\`<@.!T`"P`( !$`&$`= !E
M`$,`<@.!E`&$`= !E`&0`+ `@.`$0`80!T`&4`30!O`&0`:0!F`&D`90!D`"P`
M( !#`'(`90!A`'0`90!D`$(`>0`L`" `30!O`&0`:0!F`&D`90!D`$(`>0`L
M`" `=0!P`',`:0!Z`&4`7P!T`',`( `G`" `)@.`@.`" `#0`*`" `( `@.`" `
M( `@.`" `( `@.`" `( `@.`" `)P!&`%(`3P!-`" `9 !B`&\`+@.!#`&P`:0!E
M`&X`= !S`" `)P`@.`"8`#0`*`" `( `@.`" `( `@.`" `( `@.`" `( `@.`" `
M)P!7`$@.`10!2`$4`( `H`$,`;P!M`' `80!N`'D`5 !Y`' `90`@.`&P`:0!K
M`&4`( ! `$,`;P!M`' `80!N`'D`5 !Y`' `90`@.`"8`( `B`"4`(@.`I`" `
M00!.`$0`( `G`" `)@.`-``H`( `@.`" `( `@.`" `( `@.`" `( `@.`" `( `@.
M`" `( `@.`" `( `G`"@.`4P!T`&$`= !U`',`( !L`&D`:P!E`" `0 !3`'0`
M80!T`'4`<P`@.`"8`( `B`"4`(@.`I`" `00!.`$0`( `G`" `)@.`-``H`"0`)
M`" `( `@.`" `( `@.`" `( `@.`" `( `G`"@.`20!$`" `20!.`" `* !3`&4`
M; !E`&,`= `@.`$,`;P!M`' `80!N`'D`20!$`" `9@.!R`&\`;0`@.`$,`;P!M
M`' `80!N`'D`0@.!U`',`:0!N`&4`<P!S`$0`:0!V`&D`<P!I`&\`; @.`@.`%<`
M2 !%`%(`10`@.`$(`=0!S`&D`;@.!E`',`<P!$`&D`=@.!I`',`:0!O `&X`/0!
M`$(`=0!S`&D`;@.!E`',`<P!$`&D`=@.!I`',`:0!O`&X`*0`I` "<`#0`*``T`
D"@.!%`%@.`10!#`" `* ! `',`<0!L`"D`#0`*`&<`;P`-``H`
`
end
|||There is no & string operator in T-SQL.
Change to + and your proc "might" work
You don't have to use exec to do this, you can do:
if @.param is null
select ...
else
select ...
Although I would suggest (in order to keep the benefits of stored
procedures:
if @.param is null
exec procedureName
else
exec procedureName @.param = @.param
Then you write two other procedures. The if block with optionally executed
queries can cause poor plans, but having single procedures like this can
really improve performance.
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"John" <John@.nospam.infovis.co.uk> wrote in message
news:exulEIGpFHA.2720@.TK2MSFTNGP10.phx.gbl...
> Ok, I have this sp (attached). Still can't make it work.
> Thanks
> Regards
>
> "vuht2000" <vuht2000@.yahoo.com> wrote in message
> news:1124415729.139947.214020@.g44g2000cwa.googlegr oups.com...
>
>

Multiple selects in sp

Hi
In a stored procedure, is it possible to check if an input parameter value
has been supplied by user? Furthermore if a parameter's value is supplied
then execute one select statement, if no value is supplied then use a second
select?
Thanks
Regardstry this:
create proc myproc
param int null
as
declare @.sql varchar(100)
if param is null
set @.sql = '...' -- your first select
else
set @.sql = '...' -- your second select
exec (@.sql)
go|||There is no & string operator in T-SQL.
Change to + and your proc "might" work
You don't have to use exec to do this, you can do:
if @.param is null
select ...
else
select ...
Although I would suggest (in order to keep the benefits of stored
procedures:
if @.param is null
exec procedureName
else
exec procedureName @.param = @.param
Then you write two other procedures. The if block with optionally executed
queries can cause poor plans, but having single procedures like this can
really improve performance.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"John" <John@.nospam.infovis.co.uk> wrote in message
news:exulEIGpFHA.2720@.TK2MSFTNGP10.phx.gbl...
> Ok, I have this sp (attached). Still can't make it work.
> Thanks
> Regards
>
> "vuht2000" <vuht2000@.yahoo.com> wrote in message
> news:1124415729.139947.214020@.g44g2000cwa.googlegroups.com...
>
>

Multiple selects in sp

Hi
In a stored procedure, is it possible to check if an input parameter value
has been supplied by user? Furthermore if a parameter's value is supplied
then execute one select statement, if no value is supplied then use a second
select?
Thanks
Regardstry this:
create proc myproc
param int null
as
declare @.sql varchar(100)
if param is null
set @.sql = '...' -- your first select
else
set @.sql = '...' -- your second select
exec (@.sql)
go|||There is no & string operator in T-SQL.
Change to + and your proc "might" work :)
You don't have to use exec to do this, you can do:
if @.param is null
select ...
else
select ...
Although I would suggest (in order to keep the benefits of stored
procedures:
if @.param is null
exec procedureName
else
exec procedureName @.param = @.param
Then you write two other procedures. The if block with optionally executed
queries can cause poor plans, but having single procedures like this can
really improve performance.
--
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"John" <John@.nospam.infovis.co.uk> wrote in message
news:exulEIGpFHA.2720@.TK2MSFTNGP10.phx.gbl...
> Ok, I have this sp (attached). Still can't make it work.
> Thanks
> Regards
>
> "vuht2000" <vuht2000@.yahoo.com> wrote in message
> news:1124415729.139947.214020@.g44g2000cwa.googlegroups.com...
>> try this:
>> create proc myproc
>> param int null
>> as
>> declare @.sql varchar(100)
>> if param is null
>> set @.sql = '...' -- your first select
>> else
>> set @.sql = '...' -- your second select
>> exec (@.sql)
>> go
>
>

Multiple Selects in a Stored Proc

I need to perform 2 selects in a stored procedure, but only return data from the second..

eg.

first select gets the proper table to search from a transaction summary table

select category from trans summary where transsummary.transid = @.transid

based on the category returned something like this

if category = 1
then tabletosearch = table1
end if
if category = 2
then tabletoseach = table 2
endif

select * from @.tabletosearch


Is this possible, or am I going about this the wrong way?...
As you probably can tell I am new at this.
Thanks folksTry something like...

select @.category = (select category from trans summary where transsummary.transid = @.transid)

if @.category = 1
then @.tabletosearch = table1
end if
if @.category = 2
then @.tabletoseach = table 2
endif

select * from @.tabletosearch|||DECLARE @.category int,
@.tabletosearch sysname

SELECT @.category = t.category
FROM transsummary t
WHERE t.transid = @.transid

if (@.category = 1)
SET @.tabletosearch = 'table1'

if (@.category = 2)
SET @.tabletosearch = 'table2'

EXEC ('SELECT * FROM ' + @.tabletosearch)|||That worked great if I used SELECT * but as soon as I try to use only specific fields.....

Originally posted by achorozy

DECLARE @.category int,
@.tabletosearch sysname

SELECT @.category = t.category
FROM transsummary t
WHERE t.transid = @.transid

if (@.category = 1)
SET @.tabletosearch = 'table1'

if (@.category = 2)
SET @.tabletosearch = 'table2'

EXEC ('SELECT * FROM ' + @.tabletosearch)|||I'm sorry if the example I showed was inline with your question, after all your question did have 'SELECT *'.

Since you don't tell us why it doesn't work when you list out the columns my only guess is that each table has different columns.

So why not just do this:

DECLARE @.category int

SELECT @.category = t.category
FROM transsummary t
WHERE t.transid = @.transid

if (@.category = 1)
SELECT col1, col2, col3 FROM table1

if (@.category = 2)
SELECT colA, colB, colC FROM table2
Sorry if I sound a bit off, but I've never asked a question and all of my 200+ posting has come from answering question, trying too anyways. So I get tired after awhile when someone says "It does work".
What doesn't work? What error did you get? Why doesn't it work?|||My apologies, but if it isnt obvious already, I am new to SQL and these newsgroups..again my apologies..

So far I am getting to about 7/8ths of the way thru want I need to do.
SO I will do, what I should have done the first time. give all the details

Here is what I want, I am able to get it to the first portion to work (select if...) so I won't go into more detail on that.

I need to be able to get data from 3 tables and only certain fields from each table

so

select IDCode.t1, IdDetail.t1, SalesRep.t1, SalesRep1Addr.t2, SalesRepEmail.t2, OfficeId.t2, OfficeAddr.t3, OfficeEmail.t3

S.. t1 contains a unique id from t2, and t2 contains a unique id from t3...

and obviously the the corresonding Sales Rep Detail & Office Detail...

Thanks so much for the help

Multiple Selections and MDX

I am creating a report that the user wants to have mulitple parameters
but I am querying an OLAP Cube. Is there a way of building a query on
the fly depending on users selection. Fopr example in SQL you could say
"IN (Parameters!MyParam.Value)" is it possible to do something like
this in MDX or do I just have to query everything and filder using the
Report Parameter
Thanks in advance
DenverYes, you can do this pretty much the way you described. You're
building a string which is the mdx query and adding the parameters as
you do this. Can't remember the exact syntax I used, but it amounts
to:
"mdx_part1" + Parameters!MyParam.Value + "mdx_part2"|||How will this work for multiple selections?

multiple selection listbox as data control?

Hi, i have a listbox with multiple selection enabled, the end user uses this listbox to select what data they want to view eg. they select "green" to view all the green cars, "red" to select all the red cars etc.

i have the listbox as the control that is connected to the datasource (the sql used for it isselect * from cars_table where color =@.color

this works fine when one item in the listbox is selected, but when multiples are selected it does not work

what format does the=@.colorhave to be when multiples are selected? i've tried "green, red" "green + red" etc. but cannot seem to get it working

does anybody have any working examples that i can take a look at? it seems to be a common action, yet i cannot seem to find any documentation on how to get it to work

thanks in advance!

You will have to iterate through the selections and dynamically build your SQL

foreach (ListItem itemin ListBox1.Items)
{
if(item.Selected)
 {
sql += " Or color = '" + item.Text +"'";
 }
}

Multiple selection in a paramter?

I need a report (VS2005) that will select multiple records, such as:
Select * from CrimCase where Docket in (?). There is already code to
return a string of Dockets (pretty complex) so it would be easier to
just pass that along than to try to duplicate the code in SQL.
I tried setting the Report Parameter to Multi-Value but that doesn't
work. I have no idea how to pass in the parameter but this does work:
Select * from CrimCase where Docket in ('2006NY031095','2006NY024091')
When I replace the string with ? and get prompted for a parameter, it
does not work.
Any suggestions appreciated.On Feb 20, 9:31 am, dgk <d...@.somewhere.com> wrote:
> I need a report (VS2005) that will select multiple records, such as:
> Select * from CrimCase where Docket in (?). There is already code to
> return a string of Dockets (pretty complex) so it would be easier to
> just pass that along than to try to duplicate the code in SQL.
> I tried setting the Report Parameter to Multi-Value but that doesn't
> work. I have no idea how to pass in the parameter but this does work:
> Select * from CrimCase where Docket in ('2006NY031095','2006NY024091')
> When I replace the string with ? and get prompted for a parameter, it
> does not work.
> Any suggestions appreciated.
If you are referring to linking a multi-value parameter to a stored
procedure, you will want to select the Data tab >> select Edit
Selected Dataset [...] >> select the Parameters tab >> set Parameter
Name = @.Docket and set Parameter Value to an expression similar to
this: =Join(Parameters!Docket.Value, ","). Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||On Wed, 20 Feb 2008 18:51:47 -0800 (PST), EMartinez
<emartinez.pr1@.gmail.com> wrote:
>On Feb 20, 9:31 am, dgk <d...@.somewhere.com> wrote:
>> I need a report (VS2005) that will select multiple records, such as:
>> Select * from CrimCase where Docket in (?). There is already code to
>> return a string of Dockets (pretty complex) so it would be easier to
>> just pass that along than to try to duplicate the code in SQL.
>> I tried setting the Report Parameter to Multi-Value but that doesn't
>> work. I have no idea how to pass in the parameter but this does work:
>> Select * from CrimCase where Docket in ('2006NY031095','2006NY024091')
>> When I replace the string with ? and get prompted for a parameter, it
>> does not work.
>> Any suggestions appreciated.
>
>If you are referring to linking a multi-value parameter to a stored
>procedure, you will want to select the Data tab >> select Edit
>Selected Dataset [...] >> select the Parameters tab >> set Parameter
>Name = @.Docket and set Parameter Value to an expression similar to
>this: =Join(Parameters!Docket.Value, ","). Hope this helps.
>
Not exactly, since it isn't going to a stored procedure; the SQL is
text. But, I didn't know about that parameter tab, nor that I could
put in a more complex expression. I'm not sure how to interact with
the SSRS engine. I really want the query to end up constructing OR
statements - ie, Select * from LawCases where Docket = 1112222 or
docket = 444232 or docket = 777333, extending the query depending on
the actual number of docket numbers passed in the one parameter.
I think a more acceptable way is to just create a temporary table,
putting in the cases that I want reported on, and then call SSRS,
taking all the cases in that table.
I'm intrigued by what I can do with the code though. Is it possible to
write code in the Custom Code section that will actually construct the
query on the fly, or is that code only for calling once the query has
returned and the records are being processed?|||First, it looks like you are using ODBC (hence the ? in your query). No
problem, just that when you map query parameters to report parameters it is
order dependent (i.e. the order your ? come in your query).
The following query will work for you:
Select * from LawCases where Docket in (?)
Then in layout, Report Menu-> Report Parameters set this parameter as
multi-value.
For testing, put in the appropriate values in available values. Do not put
quotes (single or double). Make sure the data type of the parameter is
string.
Put this in for available values:
Label Value
Case1 2006NY031095
Case2 2006NY024091
After you get this working then add whatever parameters you need to have
another dataset that creates this list of dockets. For instance, add your
date range and the other dataset uses the data range to return the dockets.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"dgk" <dgk@.somewhere.com> wrote in message
news:912rr3hcfe43pt8ri4fm580tanniajmhd3@.4ax.com...
> On Wed, 20 Feb 2008 18:51:47 -0800 (PST), EMartinez
> <emartinez.pr1@.gmail.com> wrote:
>>On Feb 20, 9:31 am, dgk <d...@.somewhere.com> wrote:
>> I need a report (VS2005) that will select multiple records, such as:
>> Select * from CrimCase where Docket in (?). There is already code to
>> return a string of Dockets (pretty complex) so it would be easier to
>> just pass that along than to try to duplicate the code in SQL.
>> I tried setting the Report Parameter to Multi-Value but that doesn't
>> work. I have no idea how to pass in the parameter but this does work:
>> Select * from CrimCase where Docket in ('2006NY031095','2006NY024091')
>> When I replace the string with ? and get prompted for a parameter, it
>> does not work.
>> Any suggestions appreciated.
>>
>>If you are referring to linking a multi-value parameter to a stored
>>procedure, you will want to select the Data tab >> select Edit
>>Selected Dataset [...] >> select the Parameters tab >> set Parameter
>>Name = @.Docket and set Parameter Value to an expression similar to
>>this: =Join(Parameters!Docket.Value, ","). Hope this helps.
> Not exactly, since it isn't going to a stored procedure; the SQL is
> text. But, I didn't know about that parameter tab, nor that I could
> put in a more complex expression. I'm not sure how to interact with
> the SSRS engine. I really want the query to end up constructing OR
> statements - ie, Select * from LawCases where Docket = 1112222 or
> docket = 444232 or docket = 777333, extending the query depending on
> the actual number of docket numbers passed in the one parameter.
> I think a more acceptable way is to just create a temporary table,
> putting in the cases that I want reported on, and then call SSRS,
> taking all the cases in that table.
> I'm intrigued by what I can do with the code though. Is it possible to
> write code in the Custom Code section that will actually construct the
> query on the fly, or is that code only for calling once the query has
> returned and the records are being processed?

Multiple select statment inside a stored procedure

Hi there !
Recently i had written a stored procedure which contain multiple select
statment. One follow by anyother, like the following,
declare @.nTemp int
select count(*) AS TempCount from tableA where conditionA
select @.nTemp = TempCount
select count(*) from tableB where condtionB
select count(*) from tableC where conditionC
select count(*) from tableD where conditionD
...
When i run the store procedure, strange result had been return,
the count from tableA give correct result.
the count from tableB give correct result.
but start from there,
the count(*) from tableC return the same result as B
and the count(*) from tableD return same result as B as well.
If i just run the select count(*) statment from B, C, D individually,
3 different result had been return ( as expected the result would not
be the same. ).
It happens only when i move this store procedure from 1 server to
another.
it sounds like some setting of the server causing this problem. Can
someone help please ?Noodle wrote:
> Hi there !
> Recently i had written a stored procedure which contain multiple select
> statment. One follow by anyother, like the following,
> declare @.nTemp int
> select count(*) AS TempCount from tableA where conditionA
> select @.nTemp = TempCount
I am surprized the above does not give you an error. I would
write it like this:
select @.nTemp = count(*) from tableA where conditionA|||when you write a stored procedure with multiple result sets the statements
should be ended with a ;
in a supporting data provider in a program language you can then even access
all result sets ( like the .Net sql client provider ) with .nextresult or a
simular method
regards
Michel Posseth [MCP]
"Sericinus hunter" <serhunt@.flash.net> wrote in message
news:i8lQe.602$yQ1.361@.newssvr17.news.prodigy.com...
> Noodle wrote:
> I am surprized the above does not give you an error. I would
> write it like this:
> select @.nTemp = count(*) from tableA where conditionA|||On 28 Aug 2005 06:37:51 -0700, Noodle wrote:

>Hi there !
>Recently i had written a stored procedure which contain multiple select
>statment. One follow by anyother, like the following,
>declare @.nTemp int
>select count(*) AS TempCount from tableA where conditionA
>select @.nTemp = TempCount
(snip)
Hi Noodle,
Please post the exact code of your procedure, or at least a working
repro that you have actually tested on your system and that you have
witnessed displaying the same behaviour as your real proc.
The code you posted will throw an error on any server.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Ok, it is my mistake, i didn't write out the code. See if you guys
encounter this problem before
DECLARE @.nTemp smallint
SELECT @.nTemp = MAX(M_INDEX) FROM TABLE_A WHERE RECORD_ID =
@.REC_ID
SELECT @.nTemp AS MAXIMUM_INDEX
--First , get total record
SELECT Count(*) AS TOTAL_RECORD FROM TABLE_B WHERE
RECORD_ID = @.REC_ID AND
M_INDEX = @.nTemp
--Second , get total record with error
SELECT Count(*) AS TOTAL_RECORD_WITH_ERROR FROM TABLE_C WHERE
RECORD_ID = @.REC_ID AND
M_INDEX = @.nTemp AND
(ERR_CODE <> NULL OR ERR_CODE <> '')
--Thrid , get total record with conflict
SELECT Count(*) AS TOTAL_RECORD_WITH_INV_CONFLICT FROM TABLE_D
WHERE
RECORD_ID = @.REC_ID AND
M_INDEX = @.nTemp AND
(INV_CONFLICT <> '0' AND INV_CONFLICT <> NULL )
..
The first ,second and third select all return the same value.
But if i just copy out and execute the select statment individually
the value is different.|||Hi Hugo,
I had posted the code in my previous post.
Have u see this kind of problem before ?
It is kinda strange, it just seems like the first 'select count(*)'
never release its buffer, and make the following select count(*) return
back the same value.
I am sure that the data in the server is correct, if i copy out the
select statment, each of them and run it individually on the query
analyzer it returns back 3 different value.
Is there any setting or option in SQL Server need to be set ?
Can any one help ? This problem already drag me a day...
Thanks...
Hugo Kornelis wrote:
> On 28 Aug 2005 06:37:51 -0700, Noodle wrote:
>
> (snip)
> Hi Noodle,
> Please post the exact code of your procedure, or at least a working
> repro that you have actually tested on your system and that you have
> witnessed displaying the same behaviour as your real proc.
> The code you posted will throw an error on any server.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi Guys,
Thanks for your help.
Problem solved.
Some developer update the data wrongly.
sorry for all the trouble.|||By the way,
What is the difference between comparing NULL and Char(0)
in SQL server ?|||Noodle,
Is it possible that the Query Analyzer session has a different
setting of ANSI_NULLS than when you run these together?
I suggest you change each occurrence of
<> NULL
to
IS NOT NULL
and see if you get the desired results.
Steve Kass
Drew University
Noodle wrote:

>Hi Hugo,
>I had posted the code in my previous post.
>Have u see this kind of problem before ?
>It is kinda strange, it just seems like the first 'select count(*)'
>never release its buffer, and make the following select count(*) return
>back the same value.
>I am sure that the data in the server is correct, if i copy out the
>select statment, each of them and run it individually on the query
>analyzer it returns back 3 different value.
>Is there any setting or option in SQL Server need to be set ?
>Can any one help ? This problem already drag me a day...
>Thanks...
>
>Hugo Kornelis wrote:
>
>
>|||NULL means "there is no value here". NULL is the absence of a
value, and it is not a value. CHAR(0) is a value, the one-character
string containing the single byte with value 0.
Think of your column as an envelope. If the envelope is empty, you
that column IS NULL. If the envelope contains one byte of information,
and the byte value is zero, you have CHAR(0) in the column. Empty
is different from "contains CHAR(0)"
Steve Kass
Drew University
Noodle wrote:

>By the way,
>What is the difference between comparing NULL and Char(0)
>in SQL server ?
>
>

Multiple select statements in one query?

How can I run a select statement on data from a previous select statement
within the same query? In VFP I could select the data into a cursor and
then run the second select statement on the cursor. What is the best way to
get this to run properly?
Thanks.
Is this what you want?
select * from (select * from table1) as table2
Raymond Yap
"Preacher Man" <nospam> wrote in message
news:%23CXITaxMGHA.2704@.TK2MSFTNGP15.phx.gbl...
> How can I run a select statement on data from a previous select statement
> within the same query? In VFP I could select the data into a cursor and
> then run the second select statement on the cursor. What is the best way
to
> get this to run properly?
> Thanks.
>
|||You can use derived queries:
select col1
, col2
from (select value1 as col1
, value2 as col2
from tbl
) tblDerived
"Preacher Man" wrote:

> How can I run a select statement on data from a previous select statement
> within the same query? In VFP I could select the data into a cursor and
> then run the second select statement on the cursor. What is the best way to
> get this to run properly?
> Thanks.
>
>
|||We can probably give a better solution if we have better specifications.
While a derived table / subquery might be the right guess, we can be more
direct if you follow http://www.aspfaq.com/5006
"Preacher Man" <nospam> wrote in message
news:%23CXITaxMGHA.2704@.TK2MSFTNGP15.phx.gbl...
> How can I run a select statement on data from a previous select statement
> within the same query? In VFP I could select the data into a cursor and
> then run the second select statement on the cursor. What is the best way
> to get this to run properly?
> Thanks.
>
|||hi,
This might serve ur problem.
SELECT WWMLNM,Z3.SHCMC1,Z3.SHDOCO,Z3.SHDCTO,Z3.SHKCOO
FROM F0111
INNER JOIN
(SELECT RESLS,RESLSP,RERPCT,Z2.SHCMC1 AS SHCMC1,Z2.SHDOCO AS SHDOCO,
Z2.SHDCTO AS SHDCTO,Z2.SHKCOO AS SHKCOO,Z2.SHCMR1 AS SHCMR1
FROM F42003
INNER JOIN
(SELECT SHCMC1,SHDCTO,SHDOCO,SHKCOO,SHCMR1
FROM F4201
INNER F42003
ON SHCMC1=RESLS)Z2
ON RESLS=Z2.SHCMC1)Z3
ON wwan8=Z3.RESLSP
)SP2
bye
|||An if you have SQL 2005 available then you could use a CTE.
With SubQuery as (
Select Col1 from Tab1
)
Select
a.Col2
From Tab2 a
Join SubQuery b on b.Col1 = a.Col1
Regards
Colin Dawson
www.cjdawson.com
"Preacher Man" <nospam> wrote in message
news:%23CXITaxMGHA.2704@.TK2MSFTNGP15.phx.gbl...
> How can I run a select statement on data from a previous select statement
> within the same query? In VFP I could select the data into a cursor and
> then run the second select statement on the cursor. What is the best way
> to get this to run properly?
> Thanks.
>

Multiple select statements in one query?

How can I run a select statement on data from a previous select statement
within the same query? In VFP I could select the data into a cursor and
then run the second select statement on the cursor. What is the best way to
get this to run properly?
Thanks.Is this what you want?
select * from (select * from table1) as table2
Raymond Yap
"Preacher Man" <nospam> wrote in message
news:%23CXITaxMGHA.2704@.TK2MSFTNGP15.phx.gbl...
> How can I run a select statement on data from a previous select statement
> within the same query? In VFP I could select the data into a cursor and
> then run the second select statement on the cursor. What is the best way
to
> get this to run properly?
> Thanks.
>|||You can use derived queries:
select col1
, col2
from ( select value1 as col1
, value2 as col2
from tbl
) tblDerived
"Preacher Man" wrote:

> How can I run a select statement on data from a previous select statement
> within the same query? In VFP I could select the data into a cursor and
> then run the second select statement on the cursor. What is the best way
to
> get this to run properly?
> Thanks.
>
>|||We can probably give a better solution if we have better specifications.
While a derived table / subquery might be the right guess, we can be more
direct if you follow http://www.aspfaq.com/5006
"Preacher Man" <nospam> wrote in message
news:%23CXITaxMGHA.2704@.TK2MSFTNGP15.phx.gbl...
> How can I run a select statement on data from a previous select statement
> within the same query? In VFP I could select the data into a cursor and
> then run the second select statement on the cursor. What is the best way
> to get this to run properly?
> Thanks.
>|||hi,
This might serve ur problem.
SELECT WWMLNM,Z3.SHCMC1,Z3.SHDOCO,Z3.SHDCTO,Z3.SHKCOO
FROM F0111
INNER JOIN
(SELECT RESLS,RESLSP,RERPCT,Z2.SHCMC1 AS SHCMC1,Z2.SHDOCO AS SHDOCO,
Z2.SHDCTO AS SHDCTO,Z2.SHKCOO AS SHKCOO,Z2.SHCMR1 AS SHCMR1
FROM F42003
INNER JOIN
(SELECT SHCMC1,SHDCTO,SHDOCO,SHKCOO,SHCMR1
FROM F4201
INNER F42003
ON SHCMC1=RESLS)Z2
ON RESLS=Z2.SHCMC1)Z3
ON wwan8=Z3.RESLSP
)SP2
bye|||An if you have SQL 2005 available then you could use a CTE.
With SubQuery as (
Select Col1 from Tab1
)
Select
a.Col2
From Tab2 a
Join SubQuery b on b.Col1 = a.Col1
Regards
Colin Dawson
www.cjdawson.com
"Preacher Man" <nospam> wrote in message
news:%23CXITaxMGHA.2704@.TK2MSFTNGP15.phx.gbl...
> How can I run a select statement on data from a previous select statement
> within the same query? In VFP I could select the data into a cursor and
> then run the second select statement on the cursor. What is the best way
> to get this to run properly?
> Thanks.
>

Multiple select statements in one query?

How can I run a select statement on data from a previous select statement
within the same query? In VFP I could select the data into a cursor and
then run the second select statement on the cursor. What is the best way to
get this to run properly?
Thanks.Is this what you want?
select * from (select * from table1) as table2
Raymond Yap
"Preacher Man" <nospam> wrote in message
news:%23CXITaxMGHA.2704@.TK2MSFTNGP15.phx.gbl...
> How can I run a select statement on data from a previous select statement
> within the same query? In VFP I could select the data into a cursor and
> then run the second select statement on the cursor. What is the best way
to
> get this to run properly?
> Thanks.
>|||You can use derived queries:
select col1
, col2
from ( select value1 as col1
, value2 as col2
from tbl
) tblDerived
"Preacher Man" wrote:

> How can I run a select statement on data from a previous select statement
> within the same query? In VFP I could select the data into a cursor and
> then run the second select statement on the cursor. What is the best way
to
> get this to run properly?
> Thanks.
>
>|||We can probably give a better solution if we have better specifications.
While a derived table / subquery might be the right guess, we can be more
direct if you follow http://www.aspfaq.com/5006
"Preacher Man" <nospam> wrote in message
news:%23CXITaxMGHA.2704@.TK2MSFTNGP15.phx.gbl...
> How can I run a select statement on data from a previous select statement
> within the same query? In VFP I could select the data into a cursor and
> then run the second select statement on the cursor. What is the best way
> to get this to run properly?
> Thanks.
>|||hi,
This might serve ur problem.
SELECT WWMLNM,Z3.SHCMC1,Z3.SHDOCO,Z3.SHDCTO,Z3.SHKCOO
FROM F0111
INNER JOIN
(SELECT RESLS,RESLSP,RERPCT,Z2.SHCMC1 AS SHCMC1,Z2.SHDOCO AS SHDOCO,
Z2.SHDCTO AS SHDCTO,Z2.SHKCOO AS SHKCOO,Z2.SHCMR1 AS SHCMR1
FROM F42003
INNER JOIN
(SELECT SHCMC1,SHDCTO,SHDOCO,SHKCOO,SHCMR1
FROM F4201
INNER F42003
ON SHCMC1=RESLS)Z2
ON RESLS=Z2.SHCMC1)Z3
ON wwan8=Z3.RESLSP
)SP2
bye|||An if you have SQL 2005 available then you could use a CTE.
With SubQuery as (
Select Col1 from Tab1
)
Select
a.Col2
From Tab2 a
Join SubQuery b on b.Col1 = a.Col1
Regards
Colin Dawson
www.cjdawson.com
"Preacher Man" <nospam> wrote in message
news:%23CXITaxMGHA.2704@.TK2MSFTNGP15.phx.gbl...
> How can I run a select statement on data from a previous select statement
> within the same query? In VFP I could select the data into a cursor and
> then run the second select statement on the cursor. What is the best way
> to get this to run properly?
> Thanks.
>

Multiple select statements in one query?

How can I run a select statement on data from a previous select statement
within the same query? In VFP I could select the data into a cursor and
then run the second select statement on the cursor. What is the best way to
get this to run properly?
Thanks.
Is this what you want?
select * from (select * from table1) as table2
Raymond Yap
"Preacher Man" <nospam> wrote in message
news:%23CXITaxMGHA.2704@.TK2MSFTNGP15.phx.gbl...
> How can I run a select statement on data from a previous select statement
> within the same query? In VFP I could select the data into a cursor and
> then run the second select statement on the cursor. What is the best way
to
> get this to run properly?
> Thanks.
>
|||You can use derived queries:
select col1
, col2
from (select value1 as col1
, value2 as col2
from tbl
) tblDerived
"Preacher Man" wrote:

> How can I run a select statement on data from a previous select statement
> within the same query? In VFP I could select the data into a cursor and
> then run the second select statement on the cursor. What is the best way to
> get this to run properly?
> Thanks.
>
>
|||We can probably give a better solution if we have better specifications.
While a derived table / subquery might be the right guess, we can be more
direct if you follow http://www.aspfaq.com/5006
"Preacher Man" <nospam> wrote in message
news:%23CXITaxMGHA.2704@.TK2MSFTNGP15.phx.gbl...
> How can I run a select statement on data from a previous select statement
> within the same query? In VFP I could select the data into a cursor and
> then run the second select statement on the cursor. What is the best way
> to get this to run properly?
> Thanks.
>
|||hi,
This might serve ur problem.
SELECT WWMLNM,Z3.SHCMC1,Z3.SHDOCO,Z3.SHDCTO,Z3.SHKCOO
FROM F0111
INNER JOIN
(SELECT RESLS,RESLSP,RERPCT,Z2.SHCMC1 AS SHCMC1,Z2.SHDOCO AS SHDOCO,
Z2.SHDCTO AS SHDCTO,Z2.SHKCOO AS SHKCOO,Z2.SHCMR1 AS SHCMR1
FROM F42003
INNER JOIN
(SELECT SHCMC1,SHDCTO,SHDOCO,SHKCOO,SHCMR1
FROM F4201
INNER F42003
ON SHCMC1=RESLS)Z2
ON RESLS=Z2.SHCMC1)Z3
ON wwan8=Z3.RESLSP
)SP2
bye
|||An if you have SQL 2005 available then you could use a CTE.
With SubQuery as (
Select Col1 from Tab1
)
Select
a.Col2
From Tab2 a
Join SubQuery b on b.Col1 = a.Col1
Regards
Colin Dawson
www.cjdawson.com
"Preacher Man" <nospam> wrote in message
news:%23CXITaxMGHA.2704@.TK2MSFTNGP15.phx.gbl...
> How can I run a select statement on data from a previous select statement
> within the same query? In VFP I could select the data into a cursor and
> then run the second select statement on the cursor. What is the best way
> to get this to run properly?
> Thanks.
>

Multiple select statements in one query?

How can I run a select statement on data from a previous select statement
within the same query? In VFP I could select the data into a cursor and
then run the second select statement on the cursor. What is the best way to
get this to run properly?
Thanks.Is this what you want?
select * from (select * from table1) as table2
--
Raymond Yap
"Preacher Man" <nospam> wrote in message
news:%23CXITaxMGHA.2704@.TK2MSFTNGP15.phx.gbl...
> How can I run a select statement on data from a previous select statement
> within the same query? In VFP I could select the data into a cursor and
> then run the second select statement on the cursor. What is the best way
to
> get this to run properly?
> Thanks.
>|||You can use derived queries:
select col1
, col2
from ( select value1 as col1
, value2 as col2
from tbl
) tblDerived
"Preacher Man" wrote:
> How can I run a select statement on data from a previous select statement
> within the same query? In VFP I could select the data into a cursor and
> then run the second select statement on the cursor. What is the best way to
> get this to run properly?
> Thanks.
>
>|||We can probably give a better solution if we have better specifications.
While a derived table / subquery might be the right guess, we can be more
direct if you follow http://www.aspfaq.com/5006
"Preacher Man" <nospam> wrote in message
news:%23CXITaxMGHA.2704@.TK2MSFTNGP15.phx.gbl...
> How can I run a select statement on data from a previous select statement
> within the same query? In VFP I could select the data into a cursor and
> then run the second select statement on the cursor. What is the best way
> to get this to run properly?
> Thanks.
>|||hi,
This might serve ur problem.
SELECT WWMLNM,Z3.SHCMC1,Z3.SHDOCO,Z3.SHDCTO,Z3.SHKCOO
FROM F0111
INNER JOIN
(SELECT RESLS,RESLSP,RERPCT,Z2.SHCMC1 AS SHCMC1,Z2.SHDOCO AS SHDOCO,
Z2.SHDCTO AS SHDCTO,Z2.SHKCOO AS SHKCOO,Z2.SHCMR1 AS SHCMR1
FROM F42003
INNER JOIN
(SELECT SHCMC1,SHDCTO,SHDOCO,SHKCOO,SHCMR1
FROM F4201
INNER F42003
ON SHCMC1=RESLS)Z2
ON RESLS=Z2.SHCMC1)Z3
ON wwan8=Z3.RESLSP
)SP2
bye|||An if you have SQL 2005 available then you could use a CTE.
With SubQuery as (
Select Col1 from Tab1
)
Select
a.Col2
From Tab2 a
Join SubQuery b on b.Col1 = a.Col1
Regards
Colin Dawson
www.cjdawson.com
"Preacher Man" <nospam> wrote in message
news:%23CXITaxMGHA.2704@.TK2MSFTNGP15.phx.gbl...
> How can I run a select statement on data from a previous select statement
> within the same query? In VFP I could select the data into a cursor and
> then run the second select statement on the cursor. What is the best way
> to get this to run properly?
> Thanks.
>

Multiple Select Statements in 1 Query

i'm going to guess that there's no way to do this in T-SQL (or any
other SQL implementation), but I figured I'd ask around:
basically, what I'm looking to do is replace this union statement::
SELECT intX
FROM Table1
UNION
SELECT intY
FROM Table1
with a syntactical shortcut:
SELECT intX
SELECT intY
FROM Table1
The need for this shortcut is that my FROM clause is very complex, and
copying it multiple times is messy (and frankly, I'm in no mood to
create a View given the number of non-unique column names in the joined
tables). Anyone have any suggestions?scottstein@.gmail.com wrote:
> i'm going to guess that there's no way to do this in T-SQL (or any
> other SQL implementation), but I figured I'd ask around:
> basically, what I'm looking to do is replace this union statement::
> SELECT intX
> FROM Table1
> UNION
> SELECT intY
> FROM Table1
> with a syntactical shortcut:
> SELECT intX
> SELECT intY
> FROM Table1
> The need for this shortcut is that my FROM clause is very complex, and
> copying it multiple times is messy (and frankly, I'm in no mood to
> create a View given the number of non-unique column names in the joined
> tables). Anyone have any suggestions?
In SQL Server 2005 you can use a CTE. You'll have to assign some unique
column names though:
WITH T1 (intx, inty)
AS
(SELECT ... /* your complex logic here */ )
SELECT intx
FROM T1
UNION
SELECT inty
FROM T1 ;
There may be a better solution by changing the complex part of your
query that you didn't post.
David Portas
SQL Server MVP
--|||if are aren't on 2K5 yet, you can cross join with a sequence table,
like this:
select case when t=1 then expr1 else expr2 end
from(
-- your really complex expression goes here
select 'expr1' expr1, 'expr2' expr2)c
cross join
(select 1 t union all select 2) t
expr1
expr2
(2 row(s) affected)|||Thanks David + Alexander, I appreciate your responses. Unfortunately,
CTE has the same problem as the view -- unique column names. I haven't
even seen 2005 yet, so that isn't an option for now. Again, this
problem isn't unbearable, but it just makes my code a little messy.

Multiple Select statements + Stored Procedure

Hi all,

I have 2 select statements in my Stored Proc.

I want to display the results of each query in my DataGridView.

However, only the data of the last select query is returned.

Why is this?

Thanks.

At a time you can only bind one resultset. if you need both the resultset on your page, you need to have 2 datagrid view.

If you use dataset,

grid1.DataSource = dataset.Tables[0];

grid2.DataSource = dataset.Tables[1];

If you use datareader,

grid1.DataSource = datareader;

datareader.NextResultSet();

grid2.DataSource = datareader;

|||

Ideally I want all data displayed in one DataGridView.

Each SELECT Query returns the exact same Columns but differing data.

|||

As both queries return the same columns, could you use a union clause in your stored proc?

eg

Code Snippet

CREATE PROC proc

AS

SELECT col1, col2

FROM table1

UNION ALL
SELECT col1, col2

FROM table2

This would mean you'd have just one results set.

If its more complicated than that, you could put the results of each query into a temp table/variable and just select from that?

HTH!

|||

May be something like this...

Select 'Table 1' as Source, * From FirstTable

Union ALL

Select 'Table 2' as Source, * From SecondTable

Order By

Source, ....

|||If there any relation between the tables,you can group them in the gridview|||

Thanks!!

UNION ALL works.