Multi-record wrapping textbox?

I know that the subject is badly expressed, but hopefully I can get the
point across...
I'm a total newbie at MS Reporting services and I have a question. I
have a dataset something like this:
Customer Box Item Qty
A 1 X1 50
A 1 X2 50
A 2 X3 75
A 2 X4 25
A 3 X5 15
B 1 X1 35
B 1 X2 65
B 2 X2 10
B 2 X3 10
etc... you get the idea. Each box holds 100 mixed items; each customer
has one or more boxes.
The output report I want should look something like this:
Customer Box Contents
A 001 X1 50 * X2 50
002 X3 75 * X4 25
003 X5 15
Total for customer A: 3 boxes, 215 pieces
B 001 X1 35 * X2 65
002 X2 10 * X3 10 * X4 20 * X5 10 * X6
5
X7 5 * X8 40
003 X9 50
Total for customer B: 3 boxes, 250 pieces
I set it up as a report with two grouping levels. The tricky part is
the "contents" column. I want it to be a textbox that can be up to a
certain width, wrapping if necessary, and containing a string of data
from records. Any way to do that with Reporting Svcs?
If it helps, when I did this with MS Access, I made the wrapping
textbox part as a subreport with columns. I can try that with RS but
it seems like it might be possible another way?
I should also mention that I'm using SQL Server 2000, VB.NET 2003 as my
designer, so perhaps I don't have access to the newest features of 2005.I wrote:
> The tricky part is
> the "contents" column. I want it to be a textbox that can be up to a
> certain width, wrapping if necessary, and containing a string of data
> from records. Any way to do that with Reporting Svcs?
Never mind; I found this blog entry
http://blogs.msdn.com/chrishays/archive/2004/07/23/HorizontalTables.aspx
that gives a quite serviceable method for getting what I want.

Multiprocessors

What are my options for the number of multiprocessors that SQL Server 2000
will run on?
1 - 2 - 4 - 8 - 16?
Is there at some point where throwing more processors at SQL Server won't
make a difference?
Hi,
That depends on the OS version and SQl server version you are using.
SQL server 2000 enterprise edition on a Windows 2000 data center edition
will support 32 Processors (CPU).
See the details in topic "maximum capacity specifications" in books online.
Thanks
Hari
MCDBA
"William Gower" <w_gower@.hotmail.com> wrote in message
news:Ofl9m9QeEHA.2384@.TK2MSFTNGP09.phx.gbl...
> What are my options for the number of multiprocessors that SQL Server 2000
> will run on?
> 1 - 2 - 4 - 8 - 16?
> Is there at some point where throwing more processors at SQL Server won't
> make a difference?
>
|||Hi William
Re:
>Is there at some point where throwing more processors at SQL Server won't
make a difference?<
Conceptually not - connections are affinitised (though not hard-wired)
against CPUs by SQL Server's internal scheduling mechanisms, so assuming you
have many more user connections than CPUs, there is generally a benefit to
be gained from the extra CPUs.
In practical terms however, there is a law of diminishing returns in
continually adding CPUs. Where apex of that curve lies depends on many
scenario specifics.
HTH
Regards,
Greg Linwood
SQL Server MVP
"William Gower" <w_gower@.hotmail.com> wrote in message
news:Ofl9m9QeEHA.2384@.TK2MSFTNGP09.phx.gbl...
> What are my options for the number of multiprocessors that SQL Server 2000
> will run on?
> 1 - 2 - 4 - 8 - 16?
> Is there at some point where throwing more processors at SQL Server won't
> make a difference?
>
|||They're only affinitized for single threaded queries.
"Greg Linwood" <g_linwoodQhotmail.com> wrote in message
news:uqV3$0VeEHA.3632@.TK2MSFTNGP09.phx.gbl...
> Hi William
> Re:
> make a difference?<
> Conceptually not - connections are affinitised (though not hard-wired)
> against CPUs by SQL Server's internal scheduling mechanisms, so assuming
you[vbcol=seagreen]
> have many more user connections than CPUs, there is generally a benefit to
> be gained from the extra CPUs.
> In practical terms however, there is a law of diminishing returns in
> continually adding CPUs. Where apex of that curve lies depends on many
> scenario specifics.
> HTH
> Regards,
> Greg Linwood
> SQL Server MVP
> "William Gower" <w_gower@.hotmail.com> wrote in message
> news:Ofl9m9QeEHA.2384@.TK2MSFTNGP09.phx.gbl...
2000[vbcol=seagreen]
won't
>
|||Connection objects (spids) are indeed affinitised to cpus by virtue of the
fact that they're owned by ums schedulers which are themselves affinitised.
Worker threads which satisfy parallelism (multi-threaded queries) or other
multi-threaded work are another thing all together - they certainly are not
affinitised.
Regards,
Greg Linwood
SQL Server MVP
"Kevin" <ReplyTo@.Newsgroups.only> wrote in message
news:ePxBvbdeEHA.556@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> They're only affinitized for single threaded queries.
> "Greg Linwood" <g_linwoodQhotmail.com> wrote in message
> news:uqV3$0VeEHA.3632@.TK2MSFTNGP09.phx.gbl...
won't[vbcol=seagreen]
> you
to
> 2000
> won't
>

Multiprocessors

What are my options for the number of multiprocessors that SQL Server 2000
will run on?
1 - 2 - 4 - 8 - 16?
Is there at some point where throwing more processors at SQL Server won't
make a difference?Hi,
That depends on the OS version and SQl server version you are using.
SQL server 2000 enterprise edition on a Windows 2000 data center edition
will support 32 Processors (CPU).
See the details in topic "maximum capacity specifications" in books online.
Thanks
Hari
MCDBA
"William Gower" <w_gower@.hotmail.com> wrote in message
news:Ofl9m9QeEHA.2384@.TK2MSFTNGP09.phx.gbl...
> What are my options for the number of multiprocessors that SQL Server 2000
> will run on?
> 1 - 2 - 4 - 8 - 16?
> Is there at some point where throwing more processors at SQL Server won't
> make a difference?
>|||Hi William
Re:
>Is there at some point where throwing more processors at SQL Server won't
make a difference?<
Conceptually not - connections are affinitised (though not hard-wired)
against CPUs by SQL Server's internal scheduling mechanisms, so assuming you
have many more user connections than CPUs, there is generally a benefit to
be gained from the extra CPUs.
In practical terms however, there is a law of diminishing returns in
continually adding CPUs. Where apex of that curve lies depends on many
scenario specifics.
HTH
Regards,
Greg Linwood
SQL Server MVP
"William Gower" <w_gower@.hotmail.com> wrote in message
news:Ofl9m9QeEHA.2384@.TK2MSFTNGP09.phx.gbl...
> What are my options for the number of multiprocessors that SQL Server 2000
> will run on?
> 1 - 2 - 4 - 8 - 16?
> Is there at some point where throwing more processors at SQL Server won't
> make a difference?
>|||They're only affinitized for single threaded queries.
"Greg Linwood" <g_linwoodQhotmail.com> wrote in message
news:uqV3$0VeEHA.3632@.TK2MSFTNGP09.phx.gbl...
> Hi William
> Re:
> make a difference?<
> Conceptually not - connections are affinitised (though not hard-wired)
> against CPUs by SQL Server's internal scheduling mechanisms, so assuming
you
> have many more user connections than CPUs, there is generally a benefit to
> be gained from the extra CPUs.
> In practical terms however, there is a law of diminishing returns in
> continually adding CPUs. Where apex of that curve lies depends on many
> scenario specifics.
> HTH
> Regards,
> Greg Linwood
> SQL Server MVP
> "William Gower" <w_gower@.hotmail.com> wrote in message
> news:Ofl9m9QeEHA.2384@.TK2MSFTNGP09.phx.gbl...
2000[vbcol=seagreen]
won't[vbcol=seagreen]
>|||Connection objects (spids) are indeed affinitised to cpus by virtue of the
fact that they're owned by ums schedulers which are themselves affinitised.
Worker threads which satisfy parallelism (multi-threaded queries) or other
multi-threaded work are another thing all together - they certainly are not
affinitised.
Regards,
Greg Linwood
SQL Server MVP
"Kevin" <ReplyTo@.Newsgroups.only> wrote in message
news:ePxBvbdeEHA.556@.tk2msftngp13.phx.gbl...
> They're only affinitized for single threaded queries.
> "Greg Linwood" <g_linwoodQhotmail.com> wrote in message
> news:uqV3$0VeEHA.3632@.TK2MSFTNGP09.phx.gbl...
won't[vbcol=seagreen]
> you
to[vbcol=seagreen]
> 2000
> won't
>

Multiprocessors

What are my options for the number of multiprocessors that SQL Server 2000
will run on?
1 - 2 - 4 - 8 - 16?
Is there at some point where throwing more processors at SQL Server won't
make a difference?Hi,
That depends on the OS version and SQl server version you are using.
SQL server 2000 enterprise edition on a Windows 2000 data center edition
will support 32 Processors (CPU).
See the details in topic "maximum capacity specifications" in books online.
Thanks
Hari
MCDBA
"William Gower" <w_gower@.hotmail.com> wrote in message
news:Ofl9m9QeEHA.2384@.TK2MSFTNGP09.phx.gbl...
> What are my options for the number of multiprocessors that SQL Server 2000
> will run on?
> 1 - 2 - 4 - 8 - 16?
> Is there at some point where throwing more processors at SQL Server won't
> make a difference?
>|||Hi William
Re:
>Is there at some point where throwing more processors at SQL Server won't
make a difference?<
Conceptually not - connections are affinitised (though not hard-wired)
against CPUs by SQL Server's internal scheduling mechanisms, so assuming you
have many more user connections than CPUs, there is generally a benefit to
be gained from the extra CPUs.
In practical terms however, there is a law of diminishing returns in
continually adding CPUs. Where apex of that curve lies depends on many
scenario specifics.
HTH
Regards,
Greg Linwood
SQL Server MVP
"William Gower" <w_gower@.hotmail.com> wrote in message
news:Ofl9m9QeEHA.2384@.TK2MSFTNGP09.phx.gbl...
> What are my options for the number of multiprocessors that SQL Server 2000
> will run on?
> 1 - 2 - 4 - 8 - 16?
> Is there at some point where throwing more processors at SQL Server won't
> make a difference?
>|||They're only affinitized for single threaded queries.
"Greg Linwood" <g_linwoodQhotmail.com> wrote in message
news:uqV3$0VeEHA.3632@.TK2MSFTNGP09.phx.gbl...
> Hi William
> Re:
> >Is there at some point where throwing more processors at SQL Server won't
> make a difference?<
> Conceptually not - connections are affinitised (though not hard-wired)
> against CPUs by SQL Server's internal scheduling mechanisms, so assuming
you
> have many more user connections than CPUs, there is generally a benefit to
> be gained from the extra CPUs.
> In practical terms however, there is a law of diminishing returns in
> continually adding CPUs. Where apex of that curve lies depends on many
> scenario specifics.
> HTH
> Regards,
> Greg Linwood
> SQL Server MVP
> "William Gower" <w_gower@.hotmail.com> wrote in message
> news:Ofl9m9QeEHA.2384@.TK2MSFTNGP09.phx.gbl...
> > What are my options for the number of multiprocessors that SQL Server
2000
> > will run on?
> >
> > 1 - 2 - 4 - 8 - 16?
> >
> > Is there at some point where throwing more processors at SQL Server
won't
> > make a difference?
> >
> >
>|||Connection objects (spids) are indeed affinitised to cpus by virtue of the
fact that they're owned by ums schedulers which are themselves affinitised.
Worker threads which satisfy parallelism (multi-threaded queries) or other
multi-threaded work are another thing all together - they certainly are not
affinitised.
Regards,
Greg Linwood
SQL Server MVP
"Kevin" <ReplyTo@.Newsgroups.only> wrote in message
news:ePxBvbdeEHA.556@.tk2msftngp13.phx.gbl...
> They're only affinitized for single threaded queries.
> "Greg Linwood" <g_linwoodQhotmail.com> wrote in message
> news:uqV3$0VeEHA.3632@.TK2MSFTNGP09.phx.gbl...
> > Hi William
> >
> > Re:
> > >Is there at some point where throwing more processors at SQL Server
won't
> > make a difference?<
> >
> > Conceptually not - connections are affinitised (though not hard-wired)
> > against CPUs by SQL Server's internal scheduling mechanisms, so assuming
> you
> > have many more user connections than CPUs, there is generally a benefit
to
> > be gained from the extra CPUs.
> >
> > In practical terms however, there is a law of diminishing returns in
> > continually adding CPUs. Where apex of that curve lies depends on many
> > scenario specifics.
> >
> > HTH
> >
> > Regards,
> > Greg Linwood
> > SQL Server MVP
> >
> > "William Gower" <w_gower@.hotmail.com> wrote in message
> > news:Ofl9m9QeEHA.2384@.TK2MSFTNGP09.phx.gbl...
> > > What are my options for the number of multiprocessors that SQL Server
> 2000
> > > will run on?
> > >
> > > 1 - 2 - 4 - 8 - 16?
> > >
> > > Is there at some point where throwing more processors at SQL Server
> won't
> > > make a difference?
> > >
> > >
> >
> >
>

Multiple Values on Point Labels

Hellow all,
Is there a way to show multiple point labels on a pie chart? I have the pie
showing the percentage using the following format :
=Sum(Fields!YTD.Value)/Sum(Fields!YTD.Value, "chart1")
format code: %.0
I want to add the YTD value on a separate line so I will need to add a
return then add some code like Sum(Fields!YTD.Value).
I can get the two values to join by using a + sign but the number run
together with the same format code. I need the first line to show as a
percent and the second on a new line as a whole number.
Has anyone done this or know how to do it? In Excel this was easy but I am
unsure how to do it in SRS.
Thanks,
AnthonyUse the Format function to apply certain format codes to parts of your label
string. E.g.:
=Format(Sum(Fields!YTD.Value)/Sum(Fields!YTD.Value, "chart1"), "P1") &
vbcrlf & Format(Sum(Fields!YTD.Value), "C0")
See also:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctFormat.asp
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"anthonysjo" <anthonysjo@.discussions.microsoft.com> wrote in message
news:08E789F2-6DA2-4A6F-ADF4-98BF617C52DE@.microsoft.com...
> Hellow all,
> Is there a way to show multiple point labels on a pie chart? I have the
> pie
> showing the percentage using the following format :
> =Sum(Fields!YTD.Value)/Sum(Fields!YTD.Value, "chart1")
> format code: %.0
> I want to add the YTD value on a separate line so I will need to add a
> return then add some code like Sum(Fields!YTD.Value).
> I can get the two values to join by using a + sign but the number run
> together with the same format code. I need the first line to show as a
> percent and the second on a new line as a whole number.
> Has anyone done this or know how to do it? In Excel this was easy but I
> am
> unsure how to do it in SRS.
> Thanks,
> Anthony|||Robert,
Thanks for all the help!!! I won't get to try this until tomorrow but it
makes sense. I think my biggest problem is I am not a "programmer" by trade.
I am getting pretty good with SQL but I know exactly where I need to look by
using the Transact SQL help that comes with SQL server 2000. Is there a
specific place that I can look that gives me examples of how to code for SRS?
I need to be able to look up functions and such then be able to translate
them. Also what language are you using for most of the examples that you
provide? I think I have the option to use any of the .net languages but I am
not sure. If I had to pick I think Visual Basic would be the best...I know
a little from Access.
Thanks again for all the help!!!
"Robert Bruckner [MSFT]" wrote:
> Use the Format function to apply certain format codes to parts of your label
> string. E.g.:
> =Format(Sum(Fields!YTD.Value)/Sum(Fields!YTD.Value, "chart1"), "P1") &
> vbcrlf & Format(Sum(Fields!YTD.Value), "C0")
> See also:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctFormat.asp
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "anthonysjo" <anthonysjo@.discussions.microsoft.com> wrote in message
> news:08E789F2-6DA2-4A6F-ADF4-98BF617C52DE@.microsoft.com...
> > Hellow all,
> >
> > Is there a way to show multiple point labels on a pie chart? I have the
> > pie
> > showing the percentage using the following format :
> > =Sum(Fields!YTD.Value)/Sum(Fields!YTD.Value, "chart1")
> > format code: %.0
> >
> > I want to add the YTD value on a separate line so I will need to add a
> > return then add some code like Sum(Fields!YTD.Value).
> >
> > I can get the two values to join by using a + sign but the number run
> > together with the same format code. I need the first line to show as a
> > percent and the second on a new line as a whole number.
> >
> > Has anyone done this or know how to do it? In Excel this was easy but I
> > am
> > unsure how to do it in SRS.
> >
> > Thanks,
> > Anthony
>
>|||The RDL expression language is based on VB.NET and has additional built-in
RS specific functions.
Use this as a starting point - it will lead to built-in RS aggregate
functions, various object collections in the ReportObjectModel, etc.:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rscreate/htm/rcr_creating_expressions_v1_6fhv.asp
Besides that, you can use ALL functions available in the VB.NET run-time
library:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vaoriVBRuntimeLibraryKeywords.asp
And by default, you can use everything that is contained within the
following .NET framework namespaces:
* System
* System.Math
* System.Convert
* Microsoft.VisualBasic
Furthermore, you can add custom code or reference custom assemblies (written
in any .NET language).
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"anthonysjo" <anthonysjo@.discussions.microsoft.com> wrote in message
news:A328F9B7-3070-4D9A-8D86-51EA26E906E9@.microsoft.com...
> Robert,
> Thanks for all the help!!! I won't get to try this until tomorrow but it
> makes sense. I think my biggest problem is I am not a "programmer" by
> trade.
> I am getting pretty good with SQL but I know exactly where I need to look
> by
> using the Transact SQL help that comes with SQL server 2000. Is there a
> specific place that I can look that gives me examples of how to code for
> SRS?
> I need to be able to look up functions and such then be able to translate
> them. Also what language are you using for most of the examples that you
> provide? I think I have the option to use any of the .net languages but I
> am
> not sure. If I had to pick I think Visual Basic would be the best...I
> know
> a little from Access.
> Thanks again for all the help!!!
> "Robert Bruckner [MSFT]" wrote:
>> Use the Format function to apply certain format codes to parts of your
>> label
>> string. E.g.:
>> =Format(Sum(Fields!YTD.Value)/Sum(Fields!YTD.Value, "chart1"), "P1") &
>> vbcrlf & Format(Sum(Fields!YTD.Value), "C0")
>> See also:
>> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctFormat.asp
>> -- Robert
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "anthonysjo" <anthonysjo@.discussions.microsoft.com> wrote in message
>> news:08E789F2-6DA2-4A6F-ADF4-98BF617C52DE@.microsoft.com...
>> > Hellow all,
>> >
>> > Is there a way to show multiple point labels on a pie chart? I have
>> > the
>> > pie
>> > showing the percentage using the following format :
>> > =Sum(Fields!YTD.Value)/Sum(Fields!YTD.Value, "chart1")
>> > format code: %.0
>> >
>> > I want to add the YTD value on a separate line so I will need to add a
>> > return then add some code like Sum(Fields!YTD.Value).
>> >
>> > I can get the two values to join by using a + sign but the number run
>> > together with the same format code. I need the first line to show as a
>> > percent and the second on a new line as a whole number.
>> >
>> > Has anyone done this or know how to do it? In Excel this was easy but
>> > I
>> > am
>> > unsure how to do it in SRS.
>> >
>> > Thanks,
>> > Anthony
>>|||Thanks again this information is very helpful!!!
"Robert Bruckner [MSFT]" wrote:
> The RDL expression language is based on VB.NET and has additional built-in
> RS specific functions.
> Use this as a starting point - it will lead to built-in RS aggregate
> functions, various object collections in the ReportObjectModel, etc.:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rscreate/htm/rcr_creating_expressions_v1_6fhv.asp
> Besides that, you can use ALL functions available in the VB.NET run-time
> library:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vaoriVBRuntimeLibraryKeywords.asp
> And by default, you can use everything that is contained within the
> following .NET framework namespaces:
> * System
> * System.Math
> * System.Convert
> * Microsoft.VisualBasic
> Furthermore, you can add custom code or reference custom assemblies (written
> in any .NET language).
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "anthonysjo" <anthonysjo@.discussions.microsoft.com> wrote in message
> news:A328F9B7-3070-4D9A-8D86-51EA26E906E9@.microsoft.com...
> > Robert,
> >
> > Thanks for all the help!!! I won't get to try this until tomorrow but it
> > makes sense. I think my biggest problem is I am not a "programmer" by
> > trade.
> > I am getting pretty good with SQL but I know exactly where I need to look
> > by
> > using the Transact SQL help that comes with SQL server 2000. Is there a
> > specific place that I can look that gives me examples of how to code for
> > SRS?
> > I need to be able to look up functions and such then be able to translate
> > them. Also what language are you using for most of the examples that you
> > provide? I think I have the option to use any of the .net languages but I
> > am
> > not sure. If I had to pick I think Visual Basic would be the best...I
> > know
> > a little from Access.
> >
> > Thanks again for all the help!!!
> >
> > "Robert Bruckner [MSFT]" wrote:
> >
> >> Use the Format function to apply certain format codes to parts of your
> >> label
> >> string. E.g.:
> >> =Format(Sum(Fields!YTD.Value)/Sum(Fields!YTD.Value, "chart1"), "P1") &
> >> vbcrlf & Format(Sum(Fields!YTD.Value), "C0")
> >>
> >> See also:
> >> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctFormat.asp
> >>
> >> -- Robert
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >>
> >>
> >> "anthonysjo" <anthonysjo@.discussions.microsoft.com> wrote in message
> >> news:08E789F2-6DA2-4A6F-ADF4-98BF617C52DE@.microsoft.com...
> >> > Hellow all,
> >> >
> >> > Is there a way to show multiple point labels on a pie chart? I have
> >> > the
> >> > pie
> >> > showing the percentage using the following format :
> >> > =Sum(Fields!YTD.Value)/Sum(Fields!YTD.Value, "chart1")
> >> > format code: %.0
> >> >
> >> > I want to add the YTD value on a separate line so I will need to add a
> >> > return then add some code like Sum(Fields!YTD.Value).
> >> >
> >> > I can get the two values to join by using a + sign but the number run
> >> > together with the same format code. I need the first line to show as a
> >> > percent and the second on a new line as a whole number.
> >> >
> >> > Has anyone done this or know how to do it? In Excel this was easy but
> >> > I
> >> > am
> >> > unsure how to do it in SRS.
> >> >
> >> > Thanks,
> >> > Anthony
> >>
> >>
> >>
>
>

Multiple Union

Anyone point know a more elegant solution to the following?
I have 2 tables
Events and Locations
Events structure is
EventID int
EventName varchar
EventLeader int
LocationID int
Locations consists of
LocationID int
Location1 varchar
Location2 varchar
Location3 varchar
.
.
Location20 varchar
I want to be able to get 1 column containing all locations
for a particular EventID like
Location
Boston
.
.
Seattle
NewYork
The way I found is having a SELECT location1 ..UNION
SELECT location2 all the way to SELECT location20
Is there a better way?"michael.obrien@.DelThiSul.ie" <anonymous@.discussions.microsoft.com> wrote in
message news:57ef01c42d34$2e05fc30$a501280a@.phx.gbl...
> Anyone point know a more elegant solution to the following?
> I have 2 tables
> Events and Locations
> Events structure is
> EventID int
> EventName varchar
> EventLeader int
> LocationID int
> Locations consists of
> LocationID int
> Location1 varchar
> Location2 varchar
> Location3 varchar
> .
> .
> Location20 varchar
> I want to be able to get 1 column containing all locations
> for a particular EventID like
> Location
> Boston
> .
> .
> Seattle
> NewYork
> The way I found is having a SELECT location1 ..UNION
> SELECT location2 all the way to SELECT location20
> Is there a better way?
Well you could store the locations in a seperate table, or use a
table-values UDF.
Like this:
create table location(
LocationID int,
Location1 varchar(20),
Location2 varchar(20),
Location3 varchar(20))
go
alter function get_locations(@.LocationID int)
returns @.locations table (Location varchar(20))
as
begin
declare @.Location1 varchar(20)
declare @.Location2 varchar(20)
declare @.Location3 varchar(20)
select
@.Location1 = Location1,
@.Location2 = Location2,
@.Location3 = Location3
from location
where locationID = @.LocationID
if @.Location1 is not null
insert @.locations values(@.Location1);
if @.Location2 is not null
insert @.locations values(@.Location2);
if @.Location3 is not null
insert @.locations values(@.Location3);
return;
end
go
insert location(locationid, location1,location2) values
(1,'Detroit','Houston')
select * from get_locations(1)
David

Multiple Union

Anyone point know a more elegant solution to the following?
I have 2 tables
Events and Locations
Events structure is
EventID int
EventName varchar
EventLeader int
LocationID int
Locations consists of
LocationID int
Location1 varchar
Location2 varchar
Location3 varchar
.
.
Location20 varchar
I want to be able to get 1 column containing all locations
for a particular EventID like
Location
Boston
.
.
Seattle
NewYork
The way I found is having a SELECT location1 ..UNION
SELECT location2 all the way to SELECT location20
Is there a better way?"michael.obrien@.DelThiSul.ie" <anonymous@.discussions.microsoft.com> wrote in
message news:57ef01c42d34$2e05fc30$a501280a@.phx.gbl...
> Anyone point know a more elegant solution to the following?
> I have 2 tables
> Events and Locations
> Events structure is
> EventID int
> EventName varchar
> EventLeader int
> LocationID int
> Locations consists of
> LocationID int
> Location1 varchar
> Location2 varchar
> Location3 varchar
> .
> .
> Location20 varchar
> I want to be able to get 1 column containing all locations
> for a particular EventID like
> Location
> Boston
> .
> .
> Seattle
> NewYork
> The way I found is having a SELECT location1 ..UNION
> SELECT location2 all the way to SELECT location20
> Is there a better way?
Well you could store the locations in a seperate table, or use a
table-values UDF.
Like this:
create table location(
LocationID int,
Location1 varchar(20),
Location2 varchar(20),
Location3 varchar(20))
go
alter function get_locations(@.LocationID int)
returns @.locations table (Location varchar(20))
as
begin
declare @.Location1 varchar(20)
declare @.Location2 varchar(20)
declare @.Location3 varchar(20)
select
@.Location1 = Location1,
@.Location2 = Location2,
@.Location3 = Location3
from location
where locationID = @.LocationID
if @.Location1 is not null
insert @.locations values(@.Location1);
if @.Location2 is not null
insert @.locations values(@.Location2);
if @.Location3 is not null
insert @.locations values(@.Location3);
return;
end
go
insert location(locationid, location1,location2) values
(1,'Detroit','Houston')
select * from get_locations(1)
David

Multiple Union

Anyone point know a more elegant solution to the following?
I have 2 tables
Events and Locations
Events structure is
EventID int
EventName varchar
EventLeader int
LocationID int
Locations consists of
LocationID int
Location1 varchar
Location2 varchar
Location3 varchar
..
..
Location20 varchar
I want to be able to get 1 column containing all locations
for a particular EventID like
Location
Boston
..
..
Seattle
NewYork
The way I found is having a SELECT location1 ..UNION
SELECT location2 all the way to SELECT location20
Is there a better way?
"michael.obrien@.DelThiSul.ie" <anonymous@.discussions.microsoft.com> wrote in
message news:57ef01c42d34$2e05fc30$a501280a@.phx.gbl...
> Anyone point know a more elegant solution to the following?
> I have 2 tables
> Events and Locations
> Events structure is
> EventID int
> EventName varchar
> EventLeader int
> LocationID int
> Locations consists of
> LocationID int
> Location1 varchar
> Location2 varchar
> Location3 varchar
> .
> .
> Location20 varchar
> I want to be able to get 1 column containing all locations
> for a particular EventID like
> Location
> Boston
> .
> .
> Seattle
> NewYork
> The way I found is having a SELECT location1 ..UNION
> SELECT location2 all the way to SELECT location20
> Is there a better way?
Well you could store the locations in a seperate table, or use a
table-values UDF.
Like this:
create table location(
LocationID int,
Location1 varchar(20),
Location2 varchar(20),
Location3 varchar(20))
go
alter function get_locations(@.LocationID int)
returns @.locations table (Location varchar(20))
as
begin
declare @.Location1 varchar(20)
declare @.Location2 varchar(20)
declare @.Location3 varchar(20)
select
@.Location1 = Location1,
@.Location2 = Location2,
@.Location3 = Location3
from location
where locationID = @.LocationID
if @.Location1 is not null
insert @.locations values(@.Location1);
if @.Location2 is not null
insert @.locations values(@.Location2);
if @.Location3 is not null
insert @.locations values(@.Location3);
return;
end
go
insert location(locationid, location1,location2) values
(1,'Detroit','Houston')
select * from get_locations(1)
David

multiple sql inst. -- correct distributor (merge)

Hello,
Server has 2 ins of sql server:
ESS_SQL
ESS_SQL\SQL_2000
Maybe this is known but no matter what I do I can't get it to point to
ESS_SQL\SQL_2000 as the distributor (always says ESS_SQL as the dist its
trying to connect to during a sync)
I think its truncating the \SQL_2000 (but just the @.distributor parameter)
@.distributor = N'ESS_SQL\SQL_2000',
I've tried ent manager to mod it after but cant seem to find a work-around--
This is how the the sub create scripts looks like:
exec sp_addmergepullsubscription @.publication = N'gwl', @.publisher =
N'ESS_SQL\SQL_2000', @.publisher_db = N'Intranet', @.subscriber_type =
N'anonymous', @.subscription_priority = 0.000000, @.sync_type = N'automatic',
@.description = N'Merge publication of gwb database from Publisher
ESS_SQL\SQL_2000.'
exec sp_addmergepullsubscription_agent @.publisher = N'ESS_SQL\SQL_2000',
@.publisher_db = N'Intranet', @.publication = N'gwl', @.distributor =
N'ESS_SQL\SQL_2000', @.subscriber_security_mode = 1, @.publisher_security_mode
= 1, @.distributor_security_mode = 1, @.enabled_for_syncmgr = N'true',
@.use_ftp = N'true', @.use_interactive_resolver = N'false', @.offloadagent =
N'false'
GO
Thanks!!!
Scott Kramer
CDS Technologies, Inc.
www.clevelanddata.com
www.groundzerohq.com
www.team501.com
hate to sound patronizing, but is ESS_SQL\SQL_2000 configured as the distributor for ESS_SQL?
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"S c o t t K r a m e r" wrote:

> Hello,
> Server has 2 ins of sql server:
> ESS_SQL
> ESS_SQL\SQL_2000
> Maybe this is known but no matter what I do I can't get it to point to
> ESS_SQL\SQL_2000 as the distributor (always says ESS_SQL as the dist its
> trying to connect to during a sync)
> I think its truncating the \SQL_2000 (but just the @.distributor parameter)
> @.distributor = N'ESS_SQL\SQL_2000',
> I've tried ent manager to mod it after but cant seem to find a work-around--
> This is how the the sub create scripts looks like:
>
> exec sp_addmergepullsubscription @.publication = N'gwl', @.publisher =
> N'ESS_SQL\SQL_2000', @.publisher_db = N'Intranet', @.subscriber_type =
> N'anonymous', @.subscription_priority = 0.000000, @.sync_type = N'automatic',
> @.description = N'Merge publication of gwb database from Publisher
> ESS_SQL\SQL_2000.'
> exec sp_addmergepullsubscription_agent @.publisher = N'ESS_SQL\SQL_2000',
> @.publisher_db = N'Intranet', @.publication = N'gwl', @.distributor =
> N'ESS_SQL\SQL_2000', @.subscriber_security_mode = 1, @.publisher_security_mode
> = 1, @.distributor_security_mode = 1, @.enabled_for_syncmgr = N'true',
> @.use_ftp = N'true', @.use_interactive_resolver = N'false', @.offloadagent =
> N'false'
> GO
>
> Thanks!!!
> --
> Scott Kramer
> CDS Technologies, Inc.
> www.clevelanddata.com
> www.groundzerohq.com
> www.team501.com
>
>

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 row updates

Hello,
I am working on a web app and am at a point where I have multiple rows in my GUI that need to be sent to and saved in SQL Server when the user presses Save. We want to pass the rows to a working table and the do a begin tran, move rows from working table to permanent tables with set processing, commit tran. The debate we are having is how to get the data to the work table. We can do individual inserts to the work table 1 round trip for each row (could be 100's of rows) or concatenate all rows into 1 long (up to 8K at a time) string and make one call sending the long string and then parse it into the work table in SQL Server. Trying to consider network usage and overhead by sending many short items vs 1 long item and cpu overhead for many inserts vs string manipulation and parsing. Suggestions?

Thanks you
JeffHow about dump to Text File then Use Stored procedure to import then move files out of the way when complete.

May even remove the need for a work table cos U will already have a log & using DTS or BCP to import is pretty fast

just a thought

GW|||Yeah I was thinking that's what I would do...

But what happens when, let's say you have 10 users trying to do this at the same time...

Your work table seems problematic

Just dump the file...and use a sproc to bcp it in...