Multiple Join Clause

I have a table "Users" like this:

GroupId
CompanyId
UserId

I need to query the users getting the company's and group's names, but I only know how to join one table. Example:

Select UserId, GroupId, Groups.Name, CompanyId, Companies.Name
From Users JOIN Groups ON Users.GroupId = Groups.Id

Hon can I add the companies table in the Join ?

Thanks,
MosheYou can have multiple JOIN clauses. I find it helpful to put them on separate lines, plus I always indicate the JOIN type since I never can remember which type is the default, and it is liable to change on someone's whim in the future. Plus normally I'd want to return the User's information whether or not I was able to successfully find the corresponding Group or Company -- which means OUTER JOINs:

SELECT
 Users.UserId,
 Users.GroupId,
 ISNULL(Groups.Name,'**unknown**'),
 Users.CompanyId,
 ISNULL(Companies.Name,'**unknown**')
FROM
 Users
LEFT OUTER JOIN
 Groups ON Users.GroupId = Groups.Id
LEFT OUTER JOIN
 Companies ON Users.CompanyID = Companies.CompanyID

Terri|||Thanks,
It was helpfull

Multiple job steps

I am creating jobs using SQL Server Agent, the job has multiple steps.
Example
Step1 creates tables
Step2 creates group of reports to be executed only when step 1 is successful
l
Step3 creates group of reports to be executed only when step 1 is successful
l
I would like to execute step2 and step3 at the same time, step 3 is not depe
nding on step2.
How I do that? I am thinking of creating another job to execute step3 after
evaluating the status of step1,
somebody has more efficient ideas?
Thanks,One way would be to use DTS and it's workflow engine. You can run sql tasks
conditionally or at the same time.
Ray Higdon MCSE, MCDBA, CCNA
--
"Tony-ICW Group" <tmangahas@.icwgroup.com> wrote in message
news:CCB3CD33-DDFC-4E8B-9324-774A90F7A5EB@.microsoft.com...
> I am creating jobs using SQL Server Agent, the job has multiple steps.
> Example
> Step1 creates tables
> Step2 creates group of reports to be executed only when step 1 is
successfull
> Step3 creates group of reports to be executed only when step 1 is
successfull
> I would like to execute step2 and step3 at the same time, step 3 is not
depending on step2.
> How I do that? I am thinking of creating another job to execute step3
after evaluating the status of step1,
> somebody has more efficient ideas?
> Thanks,
>|||Thanks Ray...I will try that.

Multiple instances of MSSQL and IP addresses

Hi.
Not sure if this is the correct newsgroup (could not find another suitable g
roup), and if
not please redirect me to the proper one...
We're running multiple instances of MSSQL 2000 on a server. This is done to
easily
incorporate client DBs into our testing operations. The problem is that all
instances
share the same IP address (but use different ports). So we can access each i
nstance
separately as we want, but we would like it better that each instance is lis
tening on its
own IP address.
But we can't find a way to change the IP address a named instance is listeni
ng on.
Anybody know how to do this?
NOTE: we prefer not to be using Clustering and/or Virtual Server. This is a
single
development server used for testing client scenario's...
Tx.Hi
Does this mean you have a network card for each instance? In which case you
may be able to disable the ports you don't want to use in the network
configurations IP Filtering options. Alternatively you could do it at the
firewall/router.
John
"Arjan de Haan" wrote:

> Hi.
> Not sure if this is the correct newsgroup (could not find another suitable
group), and if
> not please redirect me to the proper one...
> We're running multiple instances of MSSQL 2000 on a server. This is done
to easily
> incorporate client DBs into our testing operations. The problem is that al
l instances
> share the same IP address (but use different ports). So we can access each
instance
> separately as we want, but we would like it better that each instance is l
istening on its
> own IP address.
> But we can't find a way to change the IP address a named instance is liste
ning on.
> Anybody know how to do this?
> NOTE: we prefer not to be using Clustering and/or Virtual Server. This is
a single
> development server used for testing client scenario's...
> Tx.
>
>
>|||"John Bell" <jbellnewsposts@.h0tmail.com> wrote in message
news:BFD4DDB0-FAC1-4E24-9C33-999F9AAF902E@.microsoft.com...
> Hi
> Does this mean you have a network card for each instance? In which case yo
u
> may be able to disable the ports you don't want to use in the network
> configurations IP Filtering options. Alternatively you could do it at the
> firewall/router.
> John
>
No, the server has (currently) just one NIC.
To better clarify things:
The server running the multiple instances of MSSQL can be reached through a
number of IP
addresses. Is it possible, either through changing settings in Windows 2000
(which is
running on the server) or by changing some settings of each MSSQL instance t
o have each
instance receive packets directed to a specific IP address. For example,
3 running instances of MSSQL
SQL_A, 'binds' to IP xxx.xxx.0.1, port 1140,
SQL_B, 'binds' to IP xxx.xxx.0.2, port 1140, and
SQL_C, 'binds' to IP xxx.xxx.0.3, port 1140.
Currently all instances of MSSQL can be reached through the same IP address
but by using
different port numbers, like this:
3 running instances of MSSQL
SQL_A, 'binds' to IP xxx.xxx.0.1, port 1140,
SQL_B, 'binds' to IP xxx.xxx.0.1, port 1143, and
SQL_C, 'binds' to IP xxx.xxx.0.1, port 1146.
We prefer the first setup described. Is this possible without resorting to v
irtual servers
and clustering?
Thanks for your help.
...Arjan...|||Hi
I don't think there is a way to bind to a specific IP address, you are
limited to blocking ports.
How are these IP addresses assigned?
John|||"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:1115966034.360116.66440@.o13g2000cwo.googlegroups.com...
> Hi
> I don't think there is a way to bind to a specific IP address, you are
> limited to blocking ports.
> How are these IP addresses assigned?
>
John,
the server running the MSSQL instances has been assigned multiple IP address
es (visible
thru Network properties / TCP IP properties / Advanced properties). This was
apparently
done on the server itself.
Just checked but it is indeed reachable under all its assigned IP addresses.
...Arjan...|||Hi
I think you can only do this at the firewall then.
John|||"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:1115985697.036330.184570@.g14g2000cwa.googlegroups.com...
> Hi
> I think you can only do this at the firewall then.
>
In another newsgroup they suggested I need multiple NICs in the server for t
his, and even
then they were not sure it could be done.
Thanks anyway for answering.
...Arjan...|||Hi
See http://support.microsoft.com/defaul...kb;en-us;816792
which states
Note When you select this check box, you enable filtering for all
adaptors, but you configure the filters individually for each adaptor.
The same filters do not apply to all adaptors.
Which is what I pointed you to in the first post. As it is at adaptor
level having multiple IP addresses for the one adapter will not work.
John

Multiple Group/Detail Records are not showing

I have a report with a page header, a report header and footer, and many group headers, one group / detail line, and some group footers.

The problem is that only one record per ID is allowed in the one group/detail line. Both records per ID will show in data view but not in the report output. I do not have "allow duplicates" selected anywhere that I am aware of.

Where else can I look?

Thanks,
cj

I assume you are using table in your report. Is your table detail grouped on ID? If so, all rows with the same ID will be grouped into one. Try removing the grouping in Details Grouping and see if you get all records displayed.

Multiple group default database

SQL Server 2000. Windows Auth. If a Windows login is a member of multiple
local groups on the SQL Server box, and each local group maps to a SQL Serve
r
login, and the logins have different default databases, how do you determine
what default database is used for a given Windows login?
Thanks
VernVern,
There is an order. When I tested it seemed like alphabetical order, but I
did not absolutely verify it. Try assigning the first alphabetical group
for a user to default to a database where he has no rights and see if this
causes a login failure.
RLF
"Vern Rabe" <VernRabe@.discussions.microsoft.com> wrote in message
news:7C825239-6888-4DEC-BE0B-59CFB3D2764A@.microsoft.com...
> SQL Server 2000. Windows Auth. If a Windows login is a member of multiple
> local groups on the SQL Server box, and each local group maps to a SQL
> Server
> login, and the logins have different default databases, how do you
> determine
> what default database is used for a given Windows login?
> Thanks
> Vernsql

multiple files for 1 group but same "disk"... performance?

Hi,
To try to improve my performance on a system where I have no control (a long
long story...)
My problem:
the HD subsystem is "bad", I'm on a SAN and most of my wait types are caused
by the disks.
I have only 1 disk available (300gb in Raid 5, but I don't know how many
disks behind this)
so I have 1 partition for my database!!! (the throughput is bad)
My server has 4 processors (8 with HT enabled)
I know that SQL perform better if I have 1 file by processor because SQL
server create 1 thread by file (I'm right?)
so, if I create 8 files but all in the same group, does this help me?
if yes, how can I redistribute the usage of my current tables, which are in
only 1 file for the moment, on all files (without having to copy the tables
in a new table)
or I have to create 8 groups with 1 file only and choose which table I put
in which group?
or this idea will improove... nothing!
thanks.
jeromeHello Jeje,
Seems we are in the same boat, I too have a large SAN with RAID 5 and
500 gb over 10 disks.
The optimization I have gone with is creating file groups for large
tables and sperate file groups for indexes.
Eg
FactSalesData -fact table data
FactSalesIndex - fact table index
You can only specify a file group for a table to use; you can't
specify the file as far as I know.
One other thing to consider is the use of logical drives on you single
partition, this will allow you to stream the disk queues via the OS,
well that's the theory.
So with sperate file groups for indexes and data you should reduce the
file lock contention.
For more tips on file groups see
http://www.sql-server-performance.com/filegroups.asp
Also check out http://www.sql-server-performance.com/ew_san.asp
It has some great ideas on SAN configuration.
Hope this helps,
Myles Matheson
Data Warehouse Architect
http://bi-on-sql-server.blogspot.com/|||thanks
do you see any improvement?
my firsts tests wher I divide my primary group into 8 files don't change
anything (or a too small change)
I'll try to use separated filegroups.
<Myles.Matheson@.gmail.com> wrote in message
news:1124192466.494874.179750@.f14g2000cwb.googlegroups.com...
> Hello Jeje,
> Seems we are in the same boat, I too have a large SAN with RAID 5 and
> 500 gb over 10 disks.
> The optimization I have gone with is creating file groups for large
> tables and sperate file groups for indexes.
> Eg
> FactSalesData -fact table data
> FactSalesIndex - fact table index
> You can only specify a file group for a table to use; you can't
> specify the file as far as I know.
> One other thing to consider is the use of logical drives on you single
> partition, this will allow you to stream the disk queues via the OS,
> well that's the theory.
> So with sperate file groups for indexes and data you should reduce the
> file lock contention.
> For more tips on file groups see
> http://www.sql-server-performance.com/filegroups.asp
>
> Also check out http://www.sql-server-performance.com/ew_san.asp
> It has some great ideas on SAN configuration.
> Hope this helps,
>
> Myles Matheson
> Data Warehouse Architect
> http://bi-on-sql-server.blogspot.com/
>

multiple files for 1 group but same "disk"... performance?

Hi,
To try to improve my performance on a system where I have no control (a long
long story...)
My problem:
the HD subsystem is "bad", I'm on a SAN and most of my wait types are caused
by the disks.
I have only 1 disk available (300gb in Raid 5, but I don't know how many
disks behind this)
so I have 1 partition for my database!!! (the throughput is bad)
My server has 4 processors (8 with HT enabled)
I know that SQL perform better if I have 1 file by processor because SQL
server create 1 thread by file (I'm right?)
so, if I create 8 files but all in the same group, does this help me?
if yes, how can I redistribute the usage of my current tables, which are in
only 1 file for the moment, on all files (without having to copy the tables
in a new table)
or I have to create 8 groups with 1 file only and choose which table I put
in which group?
or this idea will improove... nothing!
thanks.
jerome
Hello Jeje,
Seems we are in the same boat, I too have a large SAN with RAID 5 and
500 gb over 10 disks.
The optimization I have gone with is creating file groups for large
tables and sperate file groups for indexes.
Eg
FactSalesData -fact table data
FactSalesIndex - fact table index
You can only specify a file group for a table to use; you can't
specify the file as far as I know.
One other thing to consider is the use of logical drives on you single
partition, this will allow you to stream the disk queues via the OS,
well that's the theory.
So with sperate file groups for indexes and data you should reduce the
file lock contention.
For more tips on file groups see
http://www.sql-server-performance.com/filegroups.asp
Also check out http://www.sql-server-performance.com/ew_san.asp
It has some great ideas on SAN configuration.
Hope this helps,
Myles Matheson
Data Warehouse Architect
http://bi-on-sql-server.blogspot.com/
|||thanks
do you see any improvement?
my firsts tests wher I divide my primary group into 8 files don't change
anything (or a too small change)
I'll try to use separated filegroups.
<Myles.Matheson@.gmail.com> wrote in message
news:1124192466.494874.179750@.f14g2000cwb.googlegr oups.com...
> Hello Jeje,
> Seems we are in the same boat, I too have a large SAN with RAID 5 and
> 500 gb over 10 disks.
> The optimization I have gone with is creating file groups for large
> tables and sperate file groups for indexes.
> Eg
> FactSalesData -fact table data
> FactSalesIndex - fact table index
> You can only specify a file group for a table to use; you can't
> specify the file as far as I know.
> One other thing to consider is the use of logical drives on you single
> partition, this will allow you to stream the disk queues via the OS,
> well that's the theory.
> So with sperate file groups for indexes and data you should reduce the
> file lock contention.
> For more tips on file groups see
> http://www.sql-server-performance.com/filegroups.asp
>
> Also check out http://www.sql-server-performance.com/ew_san.asp
> It has some great ideas on SAN configuration.
> Hope this helps,
>
> Myles Matheson
> Data Warehouse Architect
> http://bi-on-sql-server.blogspot.com/
>
sql

Multiple details sections, possible?

In a table the details section is called "table1_Details_Group". Is it possible to add a second details group so that I can have the two groups have different group properties?

I'm trying to avoid creating a second table and using an expression to only show one at a time.

Again Thanks.

possible
poss bile
poss-bile
possibler
possibly
Edit...
Revert to "possbile"

See this tutorial on adding groupings to a report. It uses a table for an example.

http://msdn2.microsoft.com/en-us/library/ms170623.aspx

|||Thanks but the details section of a table seems not to fit the general information you linked to. For instance I can change the name of any group except the details group and the details group is not grouped on any expression.

I don't think I can create a separate details group, something crystal allows, but was hoping there was a trick or process I was missing.

So to be brief, can one table have more than one details group? If so how is the second detail group created?

general
Gretal
generally
Gretel
rural
Edit...
Revert to "gereral"

separate
desperate
separator
serrate
sprat
Edit...
Revert to "seperate"

|||I don't think I am clear on what you mean by details group. A table can have multiple groups and each group will have it's own set of detail rows based on the grouping expression. Can you give me a better idea of what you are trying to accomplish?|||
Create a brand new table. This table will have 3 sections; a header, a details section/group/ and a footer. Right click on the details section and choose edit group. This will bring up the group information for the details section. For example the name of this group will probably be "table1_Details_Group".

I want to have a second details group in the same table which will have different properties from the first. Can I do this?

|||No it isn't possible. How do you want your data to be displayed? Maybe there is another path to the end result you are looking for.|||FYI, you can insert more rows for the detail group. These have their own properties but they aren't the same as a group. I don't know what particular group properties you are looking for.|||thanks.

Multiple Data Files and paralell IO - yes or no

I've looked at many of the threads in this group that discuss whether
or not multiple data files in a filegroup will improve IO performance.
Some say that the BOL is wrong and that you don't need additional
files to have SQL server use parallel IO (starting with SQL 2K).
Others say that it still helps even in SQL 2K. I'm planning on doing
some benchmarking, but thought I'd throw it out there one more time.
What is Microsoft's stance on this? Should we or should we not use
multiple files in a filegroup. If we should, do we use the rule of
thumb of roughly 1 file per spindle in the RAID?
Thanks!
-Peter"Peter Daniels" <nospampedro@.yahoo.com> wrote in message
news:2fd8f155.0403291246.190893b6@.posting.google.com...
> I've looked at many of the threads in this group that discuss whether
> or not multiple data files in a filegroup will improve IO performance.
> Some say that the BOL is wrong and that you don't need additional
> files to have SQL server use parallel IO (starting with SQL 2K).
> Others say that it still helps even in SQL 2K. I'm planning on doing
> some benchmarking, but thought I'd throw it out there one more time.
> What is Microsoft's stance on this? Should we or should we not use
> multiple files in a filegroup. If we should, do we use the rule of
> thumb of roughly 1 file per spindle in the RAID?
>
Multiple files per filegroup and RAID can each be used to achieve parallel
IO.
If you have say, 10 disks in 5 2-disk raid mirrors, you might let a
filegroup span 2 of the mirror sets to to improve IO. But just as easily,
you could join the 2 mirror sets into a single stripe set and a single
logical volume. Then you can just use a single file to get parallel IO.
Makes your head hurt.
Since you can usually achieve the same thing with RAID alone that you could
achieve with RAID+Multiple files per filegroup, I would use RAID to alter
the IO performance profile, and always use a single file per filegroup.
And for OLTP applications just get as many disks as you can and SAME (Stripe
and Mirror Everything.
If you have
1 no raid
2 mirror
4 2 mirror sets
6 1 striped mirror set, one mirror set
8 2 striped mirror sets
...
David
David|||Peter,
Microsoft's stance on this is that there are too many variables to give a st
ock answer that will work for all hardware configurations. In my experience
using differeing hardware, I have got the best performance and ease of admin
istration by having one fil
e per database. However, you will need to do your own benchmarking on your o
wn hardware to be confident that the decision you make is right for your env
ironment.
Unless your databases are hundreds of gigabytes, I would suspect that one fi
le per database will be all you need.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk|||The last answer I got, from someone working for Microsoft Consulting
Services, was that there was no benefit to multiple files on the same disk
volume. However if you have multiple volumes you MAY see improvements.
It depends on the IO characteristics of your disk volumes. Example, if you
had 20 spindles would it be better to create one large RAID volume or >1
smaller volumes if they're all on the same controller? Additional testing
and/or input from the hardware vendor would be needed. If you have multiple
controllers/storage systems, then spreading multiple files across the
controllers should give a benefit. Whether you need to do this would depend
on your app.
HTH,
Mike Kruchten
"Peter Daniels" <nospampedro@.yahoo.com> wrote in message
news:2fd8f155.0403291246.190893b6@.posting.google.com...
> I've looked at many of the threads in this group that discuss whether
> or not multiple data files in a filegroup will improve IO performance.
> Some say that the BOL is wrong and that you don't need additional
> files to have SQL server use parallel IO (starting with SQL 2K).
> Others say that it still helps even in SQL 2K. I'm planning on doing
> some benchmarking, but thought I'd throw it out there one more time.
> What is Microsoft's stance on this? Should we or should we not use
> multiple files in a filegroup. If we should, do we use the rule of
> thumb of roughly 1 file per spindle in the RAID?
> Thanks!
> -Peter|||Sounds like the answer is no - mutiple data files in a single file
group on one RAID array will not give an IO perf benefit. I'm still
going to do some benchmarking, but thanks very much for your input.
I'd still love to here MS's official statement on this.
"Mike Kruchten" <mkruchten@.fsisolutions.com> wrote in message news:<OePgmzmFEHA.2768@.tk2msf
tngp13.phx.gbl>...
> The last answer I got, from someone working for Microsoft Consulting
> Services, was that there was no benefit to multiple files on the same disk
> volume. However if you have multiple volumes you MAY see improvements.
> It depends on the IO characteristics of your disk volumes. Example, if you
> had 20 spindles would it be better to create one large RAID volume or >1
> smaller volumes if they're all on the same controller? Additional testing
> and/or input from the hardware vendor would be needed. If you have multipl
e
> controllers/storage systems, then spreading multiple files across the
> controllers should give a benefit. Whether you need to do this would depen
d
> on your app.
> HTH,
> Mike Kruchten
>
> "Peter Daniels" <nospampedro@.yahoo.com> wrote in message
> news:2fd8f155.0403291246.190893b6@.posting.google.com...|||This has been debated extensively in the private MVP group, with MS develope
rs as participants in the
discussion. I don't think you will find an official answer from MS, and if y
ou do, it will probably be very
close to what Mark posted.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Peter Daniels" <nospampedro@.yahoo.com> wrote in message
news:2fd8f155.0403301510.5e0602a5@.posting.google.com...
> Sounds like the answer is no - mutiple data files in a single file
> group on one RAID array will not give an IO perf benefit. I'm still
> going to do some benchmarking, but thanks very much for your input.
> I'd still love to here MS's official statement on this.
>

Multiple Coumn Report goes to column 2 on group page break

I have a multiple Column report with a group that I have specified "page
break at end". The problem I am having is: if it just so happens to be
printing in column 1, the next group starts printing in column 2 instead of
on a new page.
Any ideas?Hi Ron,
> I have a multiple Column report with a group that I have specified "page
> break at end". The problem I am having is: if it just so happens to be
> printing in column 1, the next group starts printing in column 2 instead of
> on a new page.
I'm having the same problem here...
Regards,
Jens|||Just so you know, I did go through the time consuming process of creating a
Microsoft ticket to confirm my suspicion this is a bug. They had no help for
a work around. If you come up with some sort of clugy solution or whatever
to work around this let me know. I will do the same if I come up with
something.
"Jens Weiermann" wrote:
> Hi Ron,
> > I have a multiple Column report with a group that I have specified "page
> > break at end". The problem I am having is: if it just so happens to be
> > printing in column 1, the next group starts printing in column 2 instead of
> > on a new page.
> I'm having the same problem here...
> Regards,
> Jens
>|||Hi Ron,
> Just so you know, I did go through the time consuming process of creating
> a Microsoft ticket to confirm my suspicion this is a bug. They had no
> help for a work around.
thanks for the info.
> If you come up with some sort of clugy solution or whatever to work
> around this let me know. I will do the same if I come up with
> something.
I tried re-arranging my report so that I was able to put the multi-column
part into a sub-report and handle the page breaks outside of that, but just
to find out that multi-column sub-reports are also NOT working.
I guess you can imagine how much this actually s*cks. Especially for me who
was the "Let's save some $$$ - we don't need CrystalReports!" guy in our
company - and now am having a BIG problem.
Sigh...
Jens

multiple connection to the same data base file: is it possible?

Hi,

Please tell me if my query is suitable for this group or should I post this to the appropriate one. I am trying to find out if it is possible to create a new connection to an already opened database. I am using vb to create the user interface for my application.

I have programmed in a connection to a database file, which will display the file in the main window of the app. Through another form, the user can key in certain criteria which will be used generate a new qeury result of the already opened database file and display the query result in a new window. For this purpose, I have tested this by creating a new connection for the diplay form but I receive this error message :

'C:\MITalong\Project\test'. It is already opened exclusively by another user, or you need permission to view its data."

What is the correct way of doing this, ie. doing queries on an opened database file to generate result on another window. I am utilising the oledb as I wish for my app to work with microsoft access files.

Thank you.

Hi,

are you sure you are in the right group ? That sounds like a Access database file ratehr than a SQL Server file ? if you are using a Access database file you will have to tweak in the settings page, the lock status of an access database file which shouldn′t be then exclusivly locked if someone opens it.

Or are you using SQl Server Express userInbstance functionality ?

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||

Oops,

Thanks for the feed back. I final realised I have posted this to the wrong group.

Once again thank you.

|||

Hi again,

I have rechecked all the groups desription in this forum, and this is where queries on oledb connections can be directed.

Yes, I am using access for my project using the oledb thing. I forgot to mention I am quite new to vb.net way of things so some terms used might be lost to me. Regarding my starter thread, does anybody have any ideas? btw, I am working on a window app, so there is no web usage.

Thanks.

MULTIPLE COLUMNS using and GROUP BY

I hope there's someone that is able to help me. I'm new to SQL Server 2005 and have come across a pretty tough problem.

I need to return City Names from the City Table ordered by province and postal code. However, SELECT statement returns multiple occurrences of a City_Name from the Cities table. I only want the City Name to appear once though.

Here's the SQL statement:

"SELECT City_Name, Province_ID, City_PostCode
FROM Cities
WHERE City_PostCode >= " + startcode + " AND City_PostCode <= " + endcode
AND City_Name != '" + exclude
ORDER BY Province_ID ASC, City_PostCode ASC ";

I've tried to use distinct on the City Name but that didn't work. It seems the distinct keyword has to use all the column names to return a distinct record. An alternative would be to GROUP the records by City_Name however you have to use all the columns that were selected leaving me with the same distinct keyword problem.

Can anyone help me?

hi,

Well, if you have different zip codes to the same City, you should have duplications and no matter what you use; distinct or group by.

eg:

City_name Province_ID City_PostCode

Los Angeles CA 90150

Los Angeles CA 90151

In this case, you should not have one record, based on your select statement. Maybe you should appear a zip code range, instead of individual zips, then you should have distinct result set.

Also, if just change the second zip to the first one, and using distinct you will have one record. you do not need to specify column level the distinct, it applied on all enumerated column in the select statement.

I hope it helps.

Kind Regards,

Janos

|||

If you want to show the multiple postal codes as single row then you can use the following query.. One row per city with out data loose..

Code Snippet

Create Table #cities (

[City_name] Varchar(100) ,

[Province_ID] Varchar(100) ,

[City_PostCode] Varchar(100)

);

Insert Into #cities Values('Los Angeles','CA','90150');

Insert Into #cities Values('Los Angeles','CA','90151');

Insert Into #cities Values('Austin','TX','73301');

Select

Distinct

Main.City_name

, Main.Province_ID

, Substring((Select ',' + City_PostCode as [text()]

From #cities Sub

Where Sub.City_name = Main.City_name

And Sub.Province_ID = Main.Province_ID For XML Path('')),2,8000) as [City_PostCodes]

From

#cities as Main

|||Thanks Jano, I updated the statement to reflect the following:

"SELECT DISTINCT City_Name, Province_ID FROM Cities WHERE City_Name IN ( SELECT City_Name FROM Cities WHERE City_PostCode >= " + startcode + " AND City_PostCode <= " + endcode AND City_Name != '" + exclude + "' )
ORDER BY Province_ID ASC ";

The only thing is I can't sort the selections according to there postal codes. however the intial problem of the distinct fields is resolved.

Thanks again.

Regards,
Simon
|||

You can sort by postal_code (using OVER clause)..

"SELECT DISTINCT City_Name, Province_ID,Max([City_PostCode]) Over (Partition By City_name,Province_ID) as [City_PostCode] FROM Cities WHERE City_Name IN ( SELECT City_Name FROM Cities WHERE City_PostCode >= " + startcode + " AND City_PostCode <= " + endcode AND City_Name != '" + exclude + "' ) ORDER BY [City_PostCode] ASC ";

Multiple Columns Not Wrapping

I want to create a report with two columns with a single group heading with
one table. I will use this as a subreport in a larger report consisting of
single column tables. When I layout the report it shows two columns but when
I preview the report it is just one long half-size column and never wraps to
the second column (and the same thing happens when I put it in the larger
report).
Is there some setting I'm missing to get a multi-column table/report? Is
there another setting to make the multicolumns wrap in the smallest possible
vertical space?
Thanks for any tips.Multi-column reports only render as such on paper (when you actually print
the report), and also in PDF and TIFF export formats. All other render modes
(including the Report Viewer on screen) will appear as a single column.
--
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.com
"Don Miller" <nospam@.nospam.com> wrote in message
news:exD9ZFyFIHA.2372@.TK2MSFTNGP04.phx.gbl...
>I want to create a report with two columns with a single group heading with
>one table. I will use this as a subreport in a larger report consisting of
>single column tables. When I layout the report it shows two columns but
>when I preview the report it is just one long half-size column and never
>wraps to the second column (and the same thing happens when I put it in the
>larger report).
> Is there some setting I'm missing to get a multi-column table/report? Is
> there another setting to make the multicolumns wrap in the smallest
> possible vertical space?
> Thanks for any tips.
>|||Thanks, didn't know that (or find it in documentation anywhere).
"Alain Quesnel" <alainsansspam@.logiquel.com> wrote in message
news:OCxmSq2FIHA.3548@.TK2MSFTNGP06.phx.gbl...
> Multi-column reports only render as such on paper (when you actually print
> the report), and also in PDF and TIFF export formats. All other render
> modes (including the Report Viewer on screen) will appear as a single
> column.
> --
> Alain Quesnel
> alainsansspam@.logiquel.com
> www.logiquel.com
>
> "Don Miller" <nospam@.nospam.com> wrote in message
> news:exD9ZFyFIHA.2372@.TK2MSFTNGP04.phx.gbl...
>>I want to create a report with two columns with a single group heading
>>with one table. I will use this as a subreport in a larger report
>>consisting of single column tables. When I layout the report it shows two
>>columns but when I preview the report it is just one long half-size column
>>and never wraps to the second column (and the same thing happens when I
>>put it in the larger report).
>> Is there some setting I'm missing to get a multi-column table/report? Is
>> there another setting to make the multicolumns wrap in the smallest
>> possible vertical space?
>> Thanks for any tips.
>|||I am getting the second column on the next page.Am i missing something?
"Don Miller" wrote:
> Thanks, didn't know that (or find it in documentation anywhere).
> "Alain Quesnel" <alainsansspam@.logiquel.com> wrote in message
> news:OCxmSq2FIHA.3548@.TK2MSFTNGP06.phx.gbl...
> > Multi-column reports only render as such on paper (when you actually print
> > the report), and also in PDF and TIFF export formats. All other render
> > modes (including the Report Viewer on screen) will appear as a single
> > column.
> >
> > --
> >
> > Alain Quesnel
> > alainsansspam@.logiquel.com
> >
> > www.logiquel.com
> >
> >
> > "Don Miller" <nospam@.nospam.com> wrote in message
> > news:exD9ZFyFIHA.2372@.TK2MSFTNGP04.phx.gbl...
> >>I want to create a report with two columns with a single group heading
> >>with one table. I will use this as a subreport in a larger report
> >>consisting of single column tables. When I layout the report it shows two
> >>columns but when I preview the report it is just one long half-size column
> >>and never wraps to the second column (and the same thing happens when I
> >>put it in the larger report).
> >>
> >> Is there some setting I'm missing to get a multi-column table/report? Is
> >> there another setting to make the multicolumns wrap in the smallest
> >> possible vertical space?
> >>
> >> Thanks for any tips.
> >>
> >
>
>|||You need to put all fields on the left hand side. The second column appears
on the report, but doesn't contain anything at design time. Make sure your
whole report (columns 1 & 2) doesn't exceed your page width.
--
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.com
"Prabhakar" <Prabhakar@.discussions.microsoft.com> wrote in message
news:613B2B6A-ABA7-4183-B0C5-C3BAF4BEBCBB@.microsoft.com...
>I am getting the second column on the next page.Am i missing something?
> "Don Miller" wrote:
>> Thanks, didn't know that (or find it in documentation anywhere).
>> "Alain Quesnel" <alainsansspam@.logiquel.com> wrote in message
>> news:OCxmSq2FIHA.3548@.TK2MSFTNGP06.phx.gbl...
>> > Multi-column reports only render as such on paper (when you actually
>> > print
>> > the report), and also in PDF and TIFF export formats. All other render
>> > modes (including the Report Viewer on screen) will appear as a single
>> > column.
>> >
>> > --
>> >
>> > Alain Quesnel
>> > alainsansspam@.logiquel.com
>> >
>> > www.logiquel.com
>> >
>> >
>> > "Don Miller" <nospam@.nospam.com> wrote in message
>> > news:exD9ZFyFIHA.2372@.TK2MSFTNGP04.phx.gbl...
>> >>I want to create a report with two columns with a single group heading
>> >>with one table. I will use this as a subreport in a larger report
>> >>consisting of single column tables. When I layout the report it shows
>> >>two
>> >>columns but when I preview the report it is just one long half-size
>> >>column
>> >>and never wraps to the second column (and the same thing happens when I
>> >>put it in the larger report).
>> >>
>> >> Is there some setting I'm missing to get a multi-column table/report?
>> >> Is
>> >> there another setting to make the multicolumns wrap in the smallest
>> >> possible vertical space?
>> >>
>> >> Thanks for any tips.
>> >>
>> >
>>

multiple calls to SP

Hi group,

I've got a performance issue.
Here's in global what the sp (Let's call it SP_A) does.
Step 1 Call a different SP (Lets call it SP_B) and store the output in a variable
Step 2 SP_B runs a select statement that returns 1 value
Step 3 SP_A uses this value as a parameter in a select statement.
Step 4 The result of the SP_A is the result of the select statement (744 rows (always))

All tables used in SP_A and SP_B are temp tables.
Total performance of SP_A is between 0.090 and 0.140 seconds.

The problem is that this SP is called 180 times from outside SQL server. That means that the total processing time is somewhere between 21 and 25 seconds.

When I move the entire processing to within SQL server I gain only 2 seconds. So I lose 2 seconds in connecting to the database 180 times.

Can someone give me some pointers on where to look for performance wins?

If you like I can add the SP's

Regards,

Sander

Call it one time instead of 180 times =;o)

But seriously, it does indeed look like a 'looping' symptom.
Your problem is how do I tune 180 calls, not how do I tune this one single procedure, if I understand it right.

Have you considered to - if possible - do fewer calls? Ideal would probably be just one instead of 180. It's a bit hard to come up with something tangible without knowing more. Why is it 180 calls? Are they all parts of something that is complete once 180 is done?

/Kenneth

|||"Your problem is how do I tune 180 calls, not how do I tune this one single procedure, if I understand it right."

Completely correct! And I cannot perform less calls.
180 = 15 years * 12 months.

I'm now working on filling several tables. These tables would contain the output of SP_A (in normalized form). That way the users would only need a select for the dates required.....but I do not know if that will work.
So i'm working on this workaround on the side.

Do you know what possibilities I've got for tuning the 180 calls?

|||If you could provide some details about what exactly is your SP doing, what are you calculating in general, and maybe the SP code and the caller code too, that would be nice-we could be more specific.|||Here is the source code, btw: SP_A and SP_B cannot be combined (technicly they can of course....)

This is SP_A (uspRetrieveHourlyFactor)
ALTER PROCEDURE uspRetrieveHourlyFactor
@.StartDate2 varchar(10),
@.EndDate2 varchar(10),
@.InMarket nvarchar(50),
@.InProductType int,
@.InWeekDay int,
@.Normalise bit
AS
SET NOCOUNT ON
DECLARE @.StartDate as datetime
DECLARE @.EndDate as datetime
DECLARE @.InProductTypeID as int
DECLARE @.InMarketID as int
DECLARE @.RC as numeric(25,20)
DECLARE @.CurrDate as datetime
DECLARE @.WeightedAverage AS numeric(25,20)

SELECT @.InMarketID = ...WHERE MarketPlace = @.InMarket
SELECT @.InProductTypeID = ...WHERE ProductTypeID = @.InProductType


IF @.Normalise = 0
BEGIN
--No normalisation required!
SET @.WeightedAverage = 1
END
ELSE
BEGIN
EXEC @.RC = uspCalcWeightedAverage @.StartDate2, @.EndDate2, @.InMarket, @.InProductType, 1, @.WeightedAverage OUTPUT
END

SET @.StartDate = CAST(@.StartDate2 as datetime)
SET @.EndDate = CAST(@.EndDate2 as datetime)

SET DATEFIRST 1

CREATE TABLE #DatesBetweenInterval ([Date] [datetime] NULL)

SET @.CurrDate = @.StartDate
WHILE @.CurrDate < dateadd(hh,24,@.EndDate)

BEGIN
INSERT INTO #DatesBetweenInterval VALUES (@.currDate)
set @.CurrDate = dateadd(hh,1,@.currDate)
END
SELECT
DBI.DATE [DATE],
[PDF].[HOUR] [HOUR],
FLAG [FLAG],
ISNULL((HHF.Factor * flag) / @.WeightedAverage,0.0) [FACTOR]
FROM ##TBL_PRODUCTDEFS PDF
INNER JOIN #DATESBETWEENINTERVAL DBI ON DATEPART(HH, [DBI].[DATE]) = [PDF].[HOUR] - 1
INNER JOIN ##tbl_historichourlyfactors HHF ON DATEPART(dw, DATEPART(D,[DBI].[DATE])) = [HHF].[DayID]
AND [PDF].[HOUR] = [HHF].[HOUR]
AND DATEPART(M,[DBI].[DATE]) = [HHF].[Month]
WHERE PDF.MARKETID = @.InMarketID
AND PDF.PRODUCTTYPEID = @.InProductTypeID
AND
(([PDF].[WD-WE] = 1 AND DATEPART(dw, [DBI].[DATE] ) <= 5) OR
([PDF].[WD-WE] = 0 AND DATEPART(dw, [DBI].[DATE] ) > 5)
)
AND HHF.MARKETID = @.InMarketID
ORDER BY DBI.DATE
DROP TABLE #DatesBetweenInterval


This is SP_B (uspCalcWeightedAverage)
ALTER PROCEDURE dbo.uspCalcWeightedAverage
@.StartDate2 varchar(10),
@.EndDate2 varchar(10),
@.InMarket nvarchar(50),
@.InProductType int,
@.InWeekDay int,
@.WeightedAverage numeric(25,20) OUTPUT
AS

SET NOCOUNT ON
DECLARE @.StartDate as datetime
DECLARE @.EndDate as datetime
DECLARE @.InProductTypeID as int
DECLARE @.InMarketID as int
DECLARE @.CurrDate as datetime
DECLARE @.helpfloat as numeric(25,20)

--Get ID's for selected parameters
SELECT @.InMarketID = ...WHERE MarketPlace = @.InMarket
SELECT @.InProductTypeID = ...WHERE ProductTypeID = @.InProductType

SET @.StartDate = CAST(@.StartDate2 as datetime)
SET @.EndDate = CAST(@.EndDate2 as datetime)

SET DATEFIRST 1
--Create temp table
CREATE TABLE #DatesBetweenInterval ([Date] [datetime] NULL)
Set @.CurrDate = @.StartDate
WHILE @.CurrDate < dateadd(hh,24,@.EndDate)

BEGIN
INSERT INTO #DatesBetweenInterval VALUES (@.currDate)
set @.CurrDate = dateadd(hh,1,@.currDate)
END

SELECT @.WeightedAverage = (SUM(HHF.FACTOR) / COUNT(PDF.FLAG))
FROM
##TBL_PRODUCTDEFS PDF
INNER JOIN #DATESBETWEENINTERVAL DBI ON DATEPART(HH, [DBI].[DATE]) = [PDF].[HOUR]
INNER JOIN ##tbl_historichourlyfactors HHF ON DATEPART(D,DBI.DATE) = HHF.DayID
AND [PDF].[HOUR] = [HHF].[HOUR]
AND DATEPART(M,DBI.DATE) = [HHF].[Month]
WHERE
PDF.MARKETID = @.InMarketID
AND PDF.PRODUCTTYPEID = @.InProductTypeID
AND --[PDF].[WD-WE] = @.InWeekDay
(([PDF].[WD-WE] = 1 AND DATEPART(dw, DBI.DATE ) <= 5) or
([PDF].[WD-WE] = 0 AND DATEPART(dw, DBI.DATE ) > 5)
)
AND HHF.MARKETID = @.InMarketID
AND PDF.FLAG = 1
GROUP BY FLAG
DROP TABLE #DatesBetweenInterval

|||

SDerix wrote:

Completely correct! And I cannot perform less calls.
180 = 15 years * 12 months.

I'm now working on filling several tables. These tables would contain the output of SP_A (in normalized form). That way the users would only need a select for the dates required.....but I do not know if that will work.
So i'm working on this workaround on the side.

Do you know what possibilities I've got for tuning the 180 calls?

Hmmmm... I'm still not convinced that you have to do 180 calls, even though I don't doubt your word on it =;o)

On the other hand, it looks more or less like the overall is grouped by year and month, so it may be doable all at once anyway.. At least in theory. Depending on the datavolume, hardware may restrain the performance if resources aren't available for the 'full' set.

It seems like the proc itself isn't really a problem, since 0.14 sec exec time seems quite acceptable? Though, 180 * 0.14 = 25.2 seconds... And that's the problem.

Would it be possible to rethink the current 'single-month-at-a-time' strategy into something that involves the entire range all at once?

Perhaps you could consider replacing the temporary date-hour table that gets created and thrown away 360 times each run, for a permanent table to join against instead?

spA ends with an order by - is that necessary?
(it would only serve it's ordering purpose if the result is sent to the client, or inserted into a table with some other ordering attribute)

In any case, I believe that the best tuning would be to lower the number of calls from 180 to some lower number, but that would probably involve some rethinking/redesigning of what these procs does....

So... why just a single month each call for a 15 year period? Would it be possible to produce the same result for all 12 months within a year? Or for all months and years in just a single call?

/Kenneth

|||

I think you can do without this temp table #DatesBetweenInterval and use a between clause for the input start and end date.

Did you try creating indexes on the global temp tables?