Consolidating Data from 7 similar databases

I'm not quite sure if this is the correct forum to post this, if not please advise where should I post.

I have 7 databases with same structure, but different data in it, I need to have a query to consolidade some info from all of them in one report, is it possible just in onw script? how should I do it?

thanks,

Marcus

PS: I'm a beguinner in this so I apologize if the question seems stupid, or wrong.

If all the databases are on the same server, you can access tables in other databases using the long name [Database].[Owner (usually dbo)].[Table]. It would look something like SELECT * FROM AdventureWorks.Person.Address .

If the databases are on separate servers, you can use the "linked server" feature.

-Ryan / Kardax

Consolidate two UPDATE statements into on

I'm working on a stored procedure to age balances. In short, here's the algo
rithm:
Payments get applied to the oldest 'bucket' first; in my example, BalanceOve
r180.
Is the value in the Payments column greater than or equal to the value in Ba
lanceOver180?
If so, subtract BalanceOver180 from Payments and set BalanceOver180 to 0.
If not, subtract Payments from BalanceOver180 and set Payments to 0.
Then go on to the next oldest 'bucket' (which would be Balance151To180) and
run the same two update statements.
There are a total of 7 columns against which payments need to be applied.
Here are the two statements that execute for each column:
UPDATE AgedBalances
SET Payments = Payments - BalanceOver180, BalanceOver180 = 0
WHERE Payments >= BalanceOver180 AND Payments > 0
UPDATE AgedBalances
SET BalanceOver180 = BalanceOver180 - Payments, Payments = 0
WHERE Payments < BalanceOver180 AND Payments > 0
My question: Can these two statements be combined into one using the ABS fun
ction or the CASE construct
or something similar that I don't seem to be able to figure out?
Sample data:
BalanceOver180 Payments
Before: 200 500
After: 0 300
OR
Before: 600 500
After: 100 0
OR
Before: 400 400
After: 0 0
You get the picture.
As always, thanks in advance for all assistance.
Carl Imthurnsomething like that:
update <yor table>
set BalanceOver180 =
case
when
(Payments < BalanceOver180) then (BalanceOver180 - Payments)
when
(Payments >= BalanceOver180) then 0
end,
Payments =
case
when
(Payments < BalanceOver180) then 0
when
(Payments >= BalanceOver180) then (Payments - BalanceOver180)
end
where Payments > 0
--
Programmer
Let me know if it works for you :-))
"Carl Imthurn" wrote:

> I'm working on a stored procedure to age balances. In short, here's the al
gorithm:
> Payments get applied to the oldest 'bucket' first; in my example, BalanceO
ver180.
> Is the value in the Payments column greater than or equal to the value in
BalanceOver180?
> If so, subtract BalanceOver180 from Payments and set BalanceOver180 to 0.
> If not, subtract Payments from BalanceOver180 and set Payments to 0.
> Then go on to the next oldest 'bucket' (which would be Balance151To180) an
d run the same two update statements.
> There are a total of 7 columns against which payments need to be applied.
> Here are the two statements that execute for each column:
> UPDATE AgedBalances
> SET Payments = Payments - BalanceOver180, BalanceOver180 = 0
> WHERE Payments >= BalanceOver180 AND Payments > 0
> UPDATE AgedBalances
> SET BalanceOver180 = BalanceOver180 - Payments, Payments = 0
> WHERE Payments < BalanceOver180 AND Payments > 0
> My question: Can these two statements be combined into one using the ABS f
unction or the CASE construct
> or something similar that I don't seem to be able to figure out?
> Sample data:
> BalanceOver180 Payments
> Before: 200 500
> After: 0 300
> OR
> Before: 600 500
> After: 100 0
> OR
> Before: 400 400
> After: 0 0
> You get the picture.
> As always, thanks in advance for all assistance.
> Carl Imthurn
>|||That worked - thank you very much Sergey.
Sergey Zuyev wrote:

> something like that:
> update <yor table>
> set BalanceOver180 =
> case
> when
> (Payments < BalanceOver180) then (BalanceOver180 - Payments)
> when
> (Payments >= BalanceOver180) then 0
> end,
> Payments =
> case
> when
> (Payments < BalanceOver180) then 0
> when
> (Payments >= BalanceOver180) then (Payments - BalanceOver180)
> end
> where Payments > 0

consolidate two data files

How can I combine two data files (i.e .mdf and .ndf). We had two data file in our old server due to disk limitation. In the new server we don't have such limitation so we like to consolidate the two file in one. What is the best way to accomplish that?
ThanksFollow this:
http://groups.google.com.au/group/microsoft.public.sqlserver.server/browse_thread/thread/d7fa8af9c5ca5ca/71636c29fdf2666c%2371636c29fdf2666csqlsql

Consolidate SQL servers to single server

So processor is ruled out. That's good.
I suggest you look at the chapter we put in the SQL 2K HA
book which explains how memory works in depth. To access
8 GB of memory under 32-bit, you need to put /PAE in
boot.ini. There is no other way. With /3GB, you can get
up to 3 GB of usable memory (different space tho), and 1
GB is always reserved for the OS. Up to 16 GB, both can
technically play together, and some applications need it.
beyond 16 GB on 32-bit machines, you cannot combine /3GB
and /PAE.
So if you have 8 GB and use /PAE and /3GB, you have 7 GB
of usable memory, of which only 3 GB would be dynamic (so
one instance could potentially be set to dynamic). When
using PAE, it is best to set the AWE settings in SQL as
well as set max mem for the instance. You seem to know
what that is.
So you can run all three instances with separate memory
using /PAE only, and if you can, I'd recommend that
approach.
Thank you for your reply, though I am still a bit confused.
I understand that using /3GB and /PAE will give me 7GB of usable RAM.
However, you say "When using PAE, it is best to set the AWE settings
in SQL as well as set max mem for the instance. You seem to know what
that is.", and while this is what I have read elsewhere, I was under
the impression that AWE settings were not available on SQL 2000
Standard?
In the next paragraph you say "So you can run all three instances with
separate memory using /PAE only, and if you can, I'd recommend that
approach." I don't think I understand this sentence. Are you saying
I should run /PAE and not /3GB /PAE along with AWE settings in SQL?
Basically, I think I'm just looking to see if I can make SQL Standard
address memory above 4GB. I know you can with Enterprise using AWE, I
just thought AWE was not usable on Standard.
Thanks,
Kevin
"Allan Hirt" <anonymous@.discussions.microsoft.com> wrote in message news:<532201c47407$87fa08c0$a501280a@.phx.gbl>...
> So processor is ruled out. That's good.
> I suggest you look at the chapter we put in the SQL 2K HA
> book which explains how memory works in depth. To access
> 8 GB of memory under 32-bit, you need to put /PAE in
> boot.ini. There is no other way. With /3GB, you can get
> up to 3 GB of usable memory (different space tho), and 1
> GB is always reserved for the OS. Up to 16 GB, both can
> technically play together, and some applications need it.
> beyond 16 GB on 32-bit machines, you cannot combine /3GB
> and /PAE.
> So if you have 8 GB and use /PAE and /3GB, you have 7 GB
> of usable memory, of which only 3 GB would be dynamic (so
> one instance could potentially be set to dynamic). When
> using PAE, it is best to set the AWE settings in SQL as
> well as set max mem for the instance. You seem to know
> what that is.
> So you can run all three instances with separate memory
> using /PAE only, and if you can, I'd recommend that
> approach.
|||PMJI, but I'm 99.9% sure that SQL Standard will only ever use 2GB RAM
maximum. Doesn't matter what OS you use or what switches you have set.
Mike Kruchten
"Kevin" <kjarrard@.gmail.com> wrote in message
news:4f5b3722.0407281043.471e0365@.posting.google.c om...
> Thank you for your reply, though I am still a bit confused.
> I understand that using /3GB and /PAE will give me 7GB of usable RAM.
> However, you say "When using PAE, it is best to set the AWE settings
> in SQL as well as set max mem for the instance. You seem to know what
> that is.", and while this is what I have read elsewhere, I was under
> the impression that AWE settings were not available on SQL 2000
> Standard?
> In the next paragraph you say "So you can run all three instances with
> separate memory using /PAE only, and if you can, I'd recommend that
> approach." I don't think I understand this sentence. Are you saying
> I should run /PAE and not /3GB /PAE along with AWE settings in SQL?
> Basically, I think I'm just looking to see if I can make SQL Standard
> address memory above 4GB. I know you can with Enterprise using AWE, I
> just thought AWE was not usable on Standard.
> Thanks,
> Kevin
> "Allan Hirt" <anonymous@.discussions.microsoft.com> wrote in message
news:<532201c47407$87fa08c0$a501280a@.phx.gbl>...[vbcol=seagreen]

Consolidate SQL servers to single server

We have 3 SQL servers we would like to consolidate onto a single
server. While proccessor usage is not of concern (per perfmon
monitoring and stats on the new hardware), memory usage is.
The Plan:
Migrate three SQL Server 2000 Standard servers to a Dell 6650 (4 way),
each in their own instance. The Server is running Windows 2003
Advanced server.
The Question/Problem:
While I've read about the PAE, AWE and 3/4/gb tunings, I'm still
confused on just what options we have. Each of these DB's use up
around 1 - 1.75GB of memory on a normal basis. They never exceed
1.75GB of usage so we haven't seen the need to go to SQL 2000
Enterprise. Obviously, though, 1.75GB + 1.75GB + 1.75GB !< 4GB of RAM
typically installed on a server. I've used the /3GB tuning on Windows
2000 Advanced, but that doesn't allow for enough memory in this
instance. What are my options to get each of these SQL instances 2GB
of RAM working within the confines of OS the SQL memory management?
Can I simply run Windows in PAE mode and install each copy of SQL in
their own instance to have full run of the 8GB to be installed in this
server? Does the /4gb tuning work with PAE?
KevinSo processor is ruled out. That's good.
I suggest you look at the chapter we put in the SQL 2K HA
book which explains how memory works in depth. To access
8 GB of memory under 32-bit, you need to put /PAE in
boot.ini. There is no other way. With /3GB, you can get
up to 3 GB of usable memory (different space tho), and 1
GB is always reserved for the OS. Up to 16 GB, both can
technically play together, and some applications need it.
beyond 16 GB on 32-bit machines, you cannot combine /3GB
and /PAE.
So if you have 8 GB and use /PAE and /3GB, you have 7 GB
of usable memory, of which only 3 GB would be dynamic (so
one instance could potentially be set to dynamic). When
using PAE, it is best to set the AWE settings in SQL as
well as set max mem for the instance. You seem to know
what that is.
So you can run all three instances with separate memory
using /PAE only, and if you can, I'd recommend that
approach.|||Thank you for your reply, though I am still a bit confused.
I understand that using /3GB and /PAE will give me 7GB of usable RAM.
However, you say "When using PAE, it is best to set the AWE settings
in SQL as well as set max mem for the instance. You seem to know what
that is.", and while this is what I have read elsewhere, I was under
the impression that AWE settings were not available on SQL 2000
Standard?
In the next paragraph you say "So you can run all three instances with
separate memory using /PAE only, and if you can, I'd recommend that
approach." I don't think I understand this sentence. Are you saying
I should run /PAE and not /3GB /PAE along with AWE settings in SQL?
Basically, I think I'm just looking to see if I can make SQL Standard
address memory above 4GB. I know you can with Enterprise using AWE, I
just thought AWE was not usable on Standard.
Thanks,
Kevin
"Allan Hirt" <anonymous@.discussions.microsoft.com> wrote in message news:<532201c47407$87fa08c0$a501280a@.phx.gbl>...
> So processor is ruled out. That's good.
> I suggest you look at the chapter we put in the SQL 2K HA
> book which explains how memory works in depth. To access
> 8 GB of memory under 32-bit, you need to put /PAE in
> boot.ini. There is no other way. With /3GB, you can get
> up to 3 GB of usable memory (different space tho), and 1
> GB is always reserved for the OS. Up to 16 GB, both can
> technically play together, and some applications need it.
> beyond 16 GB on 32-bit machines, you cannot combine /3GB
> and /PAE.
> So if you have 8 GB and use /PAE and /3GB, you have 7 GB
> of usable memory, of which only 3 GB would be dynamic (so
> one instance could potentially be set to dynamic). When
> using PAE, it is best to set the AWE settings in SQL as
> well as set max mem for the instance. You seem to know
> what that is.
> So you can run all three instances with separate memory
> using /PAE only, and if you can, I'd recommend that
> approach.|||PMJI, but I'm 99.9% sure that SQL Standard will only ever use 2GB RAM
maximum. Doesn't matter what OS you use or what switches you have set.
Mike Kruchten
"Kevin" <kjarrard@.gmail.com> wrote in message
news:4f5b3722.0407281043.471e0365@.posting.google.com...
> Thank you for your reply, though I am still a bit confused.
> I understand that using /3GB and /PAE will give me 7GB of usable RAM.
> However, you say "When using PAE, it is best to set the AWE settings
> in SQL as well as set max mem for the instance. You seem to know what
> that is.", and while this is what I have read elsewhere, I was under
> the impression that AWE settings were not available on SQL 2000
> Standard?
> In the next paragraph you say "So you can run all three instances with
> separate memory using /PAE only, and if you can, I'd recommend that
> approach." I don't think I understand this sentence. Are you saying
> I should run /PAE and not /3GB /PAE along with AWE settings in SQL?
> Basically, I think I'm just looking to see if I can make SQL Standard
> address memory above 4GB. I know you can with Enterprise using AWE, I
> just thought AWE was not usable on Standard.
> Thanks,
> Kevin
> "Allan Hirt" <anonymous@.discussions.microsoft.com> wrote in message
news:<532201c47407$87fa08c0$a501280a@.phx.gbl>...
> > So processor is ruled out. That's good.
> >
> > I suggest you look at the chapter we put in the SQL 2K HA
> > book which explains how memory works in depth. To access
> > 8 GB of memory under 32-bit, you need to put /PAE in
> > boot.ini. There is no other way. With /3GB, you can get
> > up to 3 GB of usable memory (different space tho), and 1
> > GB is always reserved for the OS. Up to 16 GB, both can
> > technically play together, and some applications need it.
> > beyond 16 GB on 32-bit machines, you cannot combine /3GB
> > and /PAE.
> >
> > So if you have 8 GB and use /PAE and /3GB, you have 7 GB
> > of usable memory, of which only 3 GB would be dynamic (so
> > one instance could potentially be set to dynamic). When
> > using PAE, it is best to set the AWE settings in SQL as
> > well as set max mem for the instance. You seem to know
> > what that is.
> >
> > So you can run all three instances with separate memory
> > using /PAE only, and if you can, I'd recommend that
> > approach.

Consolidate SQL servers to single server

So processor is ruled out. That's good.
I suggest you look at the chapter we put in the SQL 2K HA
book which explains how memory works in depth. To access
8 GB of memory under 32-bit, you need to put /PAE in
boot.ini. There is no other way. With /3GB, you can get
up to 3 GB of usable memory (different space tho), and 1
GB is always reserved for the OS. Up to 16 GB, both can
technically play together, and some applications need it.
beyond 16 GB on 32-bit machines, you cannot combine /3GB
and /PAE.
So if you have 8 GB and use /PAE and /3GB, you have 7 GB
of usable memory, of which only 3 GB would be dynamic (so
one instance could potentially be set to dynamic). When
using PAE, it is best to set the AWE settings in SQL as
well as set max mem for the instance. You seem to know
what that is.
So you can run all three instances with separate memory
using /PAE only, and if you can, I'd recommend that
approach.Thank you for your reply, though I am still a bit confused.
I understand that using /3GB and /PAE will give me 7GB of usable RAM.
However, you say "When using PAE, it is best to set the AWE settings
in SQL as well as set max mem for the instance. You seem to know what
that is.", and while this is what I have read elsewhere, I was under
the impression that AWE settings were not available on SQL 2000
Standard?
In the next paragraph you say "So you can run all three instances with
separate memory using /PAE only, and if you can, I'd recommend that
approach." I don't think I understand this sentence. Are you saying
I should run /PAE and not /3GB /PAE along with AWE settings in SQL?
Basically, I think I'm just looking to see if I can make SQL Standard
address memory above 4GB. I know you can with Enterprise using AWE, I
just thought AWE was not usable on Standard.
Thanks,
Kevin
"Allan Hirt" <anonymous@.discussions.microsoft.com> wrote in message news:<532201c47407$87fa0
8c0$a501280a@.phx.gbl>...
> So processor is ruled out. That's good.
> I suggest you look at the chapter we put in the SQL 2K HA
> book which explains how memory works in depth. To access
> 8 GB of memory under 32-bit, you need to put /PAE in
> boot.ini. There is no other way. With /3GB, you can get
> up to 3 GB of usable memory (different space tho), and 1
> GB is always reserved for the OS. Up to 16 GB, both can
> technically play together, and some applications need it.
> beyond 16 GB on 32-bit machines, you cannot combine /3GB
> and /PAE.
> So if you have 8 GB and use /PAE and /3GB, you have 7 GB
> of usable memory, of which only 3 GB would be dynamic (so
> one instance could potentially be set to dynamic). When
> using PAE, it is best to set the AWE settings in SQL as
> well as set max mem for the instance. You seem to know
> what that is.
> So you can run all three instances with separate memory
> using /PAE only, and if you can, I'd recommend that
> approach.|||PMJI, but I'm 99.9% sure that SQL Standard will only ever use 2GB RAM
maximum. Doesn't matter what OS you use or what switches you have set.
Mike Kruchten
"Kevin" <kjarrard@.gmail.com> wrote in message
news:4f5b3722.0407281043.471e0365@.posting.google.com...
> Thank you for your reply, though I am still a bit confused.
> I understand that using /3GB and /PAE will give me 7GB of usable RAM.
> However, you say "When using PAE, it is best to set the AWE settings
> in SQL as well as set max mem for the instance. You seem to know what
> that is.", and while this is what I have read elsewhere, I was under
> the impression that AWE settings were not available on SQL 2000
> Standard?
> In the next paragraph you say "So you can run all three instances with
> separate memory using /PAE only, and if you can, I'd recommend that
> approach." I don't think I understand this sentence. Are you saying
> I should run /PAE and not /3GB /PAE along with AWE settings in SQL?
> Basically, I think I'm just looking to see if I can make SQL Standard
> address memory above 4GB. I know you can with Enterprise using AWE, I
> just thought AWE was not usable on Standard.
> Thanks,
> Kevin
> "Allan Hirt" <anonymous@.discussions.microsoft.com> wrote in message
news:<532201c47407$87fa08c0$a501280a@.phx.gbl>...[vbcol=seagreen]

Consolidate Rows on Select

I want to condense several rows of a table using a select
statement, function, or stored procedure
For Example
mytable :
1 a
2 b
2 c
2 d
3 a
so that my select should result in
1 a
2 b,c,d
3 a
Any ideas or suggestions?
While this code references the sample database in my book (CHA2), the
pattern will work for your problem as well. In this example, the @.EventDates
variable is used to gather, or denomalize, the EventDate column:
USE CHA2
DECLARE
@.EventDates VARCHAR(1024)
SET @.EventDates = ''
SELECT @.EventDates = @.EventDates + CONVERT(VARCHAR(15), a.d,107 ) + '; '
FROM (Select DateBegin as [d] FROM Event
JOIN Tour
ON Event.TourID = Tour.TourID
WHERE Tour.[Name] = 'Outer Banks Lighthouses') as a
SELECT Left(@.EventDates, Len(@.EventDates)-1) AS 'Outer Banks Lighthouses
Events'
-Paul Nielsen, SQL Server MVP
SQL Server 2000 Bible, Wiley Press
"Dan" <anonymous@.discussions.microsoft.com> wrote in message
news:392801c4aa4b$477a6810$a401280a@.phx.gbl...
>I want to condense several rows of a table using a select
> statement, function, or stored procedure
> For Example
> mytable :
> 1 a
> 2 b
> 2 c
> 2 d
> 3 a
> so that my select should result in
> 1 a
> 2 b,c,d
> 3 a
> Any ideas or suggestions?