Multithreading SQL connections

Hi there
I am working on the design of a system that its’ main function will be to
talk to devices over TCP/IP. There can be up to 50,000 devices that I need t
o
talk to within an hour period.
There will be a Queue on SQL 2005 which will contain requests for these
devices. I will need to check this queue and depending on the information on
the queue I need to talk to the devices. There will be one request for each
device every hour, therefore I need to be able to process multiple
communications at one time.
Here is my question: Should I create independent threads which would read
SQL and do pretty much everything, having a collection of about 50 or so
threads doing this? I have heard that I should avoid this high number of
connections to SQL at one time.
The other solution that I came up with is to have one SQL reader and have
the communication sockets as the threads, so they do their job and return. I
n
this thread communication scenario should I destroy the thread once I am
done, or should I have thread communication and keep the thread alive and
just send new requests to it?
I know this is pretty broad, but any help would be appreciated.
Thank you,
Spike S50 connections at a time in SQL Server is not an issue at all. Especially
if you are using connection pooling. When you say Queue what exactly do you
mean? Is this a regular table or is this a Queue from Service Broker?
Andrew J. Kelly SQL MVP
"Spike Spiegel" <cbb_spike@.hotmail.com> wrote in message
news:8AFEA5E5-1E9B-4D23-BFD9-1AEB25230AF6@.microsoft.com...
> Hi there
> I am working on the design of a system that its' main function will be to
> talk to devices over TCP/IP. There can be up to 50,000 devices that I need
> to
> talk to within an hour period.
> There will be a Queue on SQL 2005 which will contain requests for these
> devices. I will need to check this queue and depending on the information
> on
> the queue I need to talk to the devices. There will be one request for
> each
> device every hour, therefore I need to be able to process multiple
> communications at one time.
> Here is my question: Should I create independent threads which would read
> SQL and do pretty much everything, having a collection of about 50 or so
> threads doing this? I have heard that I should avoid this high number of
> connections to SQL at one time.
> The other solution that I came up with is to have one SQL reader and have
> the communication sockets as the threads, so they do their job and return.
> In
> this thread communication scenario should I destroy the thread once I am
> done, or should I have thread communication and keep the thread alive and
> just send new requests to it?
> I know this is pretty broad, but any help would be appreciated.
> Thank you,
> Spike S|||Hi Andrew
Sorry, I should have not used the word queue. It is basically just a table
with a very log record, about 50 fields, my application would be pulling fro
m
it.
What do you mean by "connection pooling?"
Right I am running a test and basically I am opening a connection using the
System.Data.SqlClient.SqlConnection class, and reading my record with
SqlConnection + SqlDataReader and writing records with the SqlConnection.+
TSQL statement. Are these the most efficient/fastest way to do this? I am
very interested in speed here.
If I were to create a thread and send the SqlConnection to the thread for
them all to share the same connection, would that make the code more
efficient?
What would be a good threshold for number of threads? 100? 500? 1000? 10000?
Please let me know.
Thank you,
Spike S.|||> What do you mean by "connection pooling?"
I would google for more details but essentially it is used by .net to make
the process of connecting and disconnecting much more efficient. You retain
a pool of connections that stay connected all the time. These are then
handed out as the app requests new connections and instead of closing it
completely it just cleans it up and makes it ready for the next user. It's
the default behavior with .net.

> Right I am running a test and basically I am opening a connection using
> the
> System.Data.SqlClient.SqlConnection class, and reading my record with
> SqlConnection + SqlDataReader and writing records with the SqlConnection.+
> TSQL statement. Are these the most efficient/fastest way to do this? I am
> very interested in speed here.
Actually using stored procedures to read and write the data is the most
efficient way.

> What would be a good threshold for number of threads? 100? 500? 1000?
> 10000?
Testing is the only way to know for sure.
Andrew J. Kelly SQL MVP
"Spike Spiegel" <cbb_spike@.hotmail.com> wrote in message
news:96A7733E-31CD-4744-9DD9-FD8CF64278A0@.microsoft.com...
> Hi Andrew
> Sorry, I should have not used the word queue. It is basically just a table
> with a very log record, about 50 fields, my application would be pulling
> from
> it.
> What do you mean by "connection pooling?"
> Right I am running a test and basically I am opening a connection using
> the
> System.Data.SqlClient.SqlConnection class, and reading my record with
> SqlConnection + SqlDataReader and writing records with the SqlConnection.+
> TSQL statement. Are these the most efficient/fastest way to do this? I am
> very interested in speed here.
> If I were to create a thread and send the SqlConnection to the thread for
> them all to share the same connection, would that make the code more
> efficient?
> What would be a good threshold for number of threads? 100? 500? 1000?
> 10000?
> Please let me know.
> Thank you,
> Spike S.|||Hi Andrew
Thank you very much for all the information.
I did some testing, using the connection pooling, and it is exactly what I
was looking for, I can have around 50 threads before I see a performance hit
.
This is the first time I posted into a managed Microsoft newsgroup. From
this experience I will not think twice before checking the managed group
again.
Thank you again for all your help.
Take care,
Spike S.|||Please note that Andy doesn't have anything to do with the MSDN managed
newsgroup program/policy/whatever you want to call it. Andy doesn't work for
Microsoft and is out here helping everybody on his own dime. That, in my
opinion, means he deserves even more thanks. :-)
Sincerely,
Steve Dybing
This posting is provided "AS IS" with no warranties, and confers no rights.
"Spike Spiegel" <cbb_spike@.hotmail.com> wrote in message
news:74D54EA9-786B-45AE-8007-88804D2A4B56@.microsoft.com...
> Hi Andrew
> Thank you very much for all the information.
> I did some testing, using the connection pooling, and it is exactly what I
> was looking for, I can have around 50 threads before I see a performance
> hit.
> This is the first time I posted into a managed Microsoft newsgroup. From
> this experience I will not think twice before checking the managed group
> again.
> Thank you again for all your help.
> Take care,
> Spike S.|||Double thanks to Andrew then...|||Double you are welcome.
Andrew J. Kelly SQL MVP
"Spike Spiegel" <cbb_spike@.hotmail.com> wrote in message
news:A5A1E014-F5C1-43E4-8956-F9D19EA5C153@.microsoft.com...
> Double thanks to Andrew then...|||Ahhh gee Thanks Steve :)
Andrew J. Kelly SQL MVP
"Steve Dybing [MSFT]" <steve.dybing@.online.microsoft.com> wrote in message
news:eFQhpyFdGHA.3908@.TK2MSFTNGP04.phx.gbl...
> Please note that Andy doesn't have anything to do with the MSDN managed
> newsgroup program/policy/whatever you want to call it. Andy doesn't work
> for Microsoft and is out here helping everybody on his own dime. That, in
> my opinion, means he deserves even more thanks. :-)
> --
> Sincerely,
> Steve Dybing
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Spike Spiegel" <cbb_spike@.hotmail.com> wrote in message
> news:74D54EA9-786B-45AE-8007-88804D2A4B56@.microsoft.com...
>sql

Multiple Values for single row

hi iam totally new to databases , as a project i have to design a database of users...they have to register first like any site..so i used stored procs and made entries to database using insert command...its working for now..

now every user will search and add other users in the database..so every user will have a contact list...i have no idea how to implement this...

so far i created a table 'UserAccount' with column names as
UserName as varchar(50)
Password as varchar(50)
EmailID as varchar(100)
DateOfJoining as datetime
UserID as int --> this is unique for user..i enabled automatic increment..and this is primary key..

so now every user must have a list of other userid's.. as contact list..

Any help any ideas will be great since i have no clue how to put multiple values for each row..i didnt even know how to search for this problems solution..iam sorry if this posted somewhere else..
THANK YOU !

if it helps..iam using sql server express edition..and iam accessing using asp.net/C#Hi,

Create a contact list table which includes columns,

UserId,
ContactId

Eralper

Multiple tables in the dataset

Hi,
I have a stored proc which returns multiple result sets. I think SSRS picks
up the first one by design. What's the best way forward, a data extension, or
is there a simpler way?
Thanks in advance,
Regards,
DattaYou need to split each result set into a uniqe dataset in RS. It can't
handle multiple result sets. Can you create new stored procedures from the
queries in the original one?
Kaisa M. Lindahl Lervik
"Datta" <Datta@.discussions.microsoft.com> wrote in message
news:F228B40A-B4B2-40A5-A243-F2573CFEBFC8@.microsoft.com...
> Hi,
> I have a stored proc which returns multiple result sets. I think SSRS
> picks
> up the first one by design. What's the best way forward, a data extension,
> or
> is there a simpler way?
> Thanks in advance,
> Regards,
> Datta|||Thanks, I suspected so.
I can split the procedures, but I think a custom data extension is the right
way to go, if there is no in-built support.
"Kaisa M. Lindahl Lervik" wrote:
> You need to split each result set into a uniqe dataset in RS. It can't
> handle multiple result sets. Can you create new stored procedures from the
> queries in the original one?
> Kaisa M. Lindahl Lervik
> "Datta" <Datta@.discussions.microsoft.com> wrote in message
> news:F228B40A-B4B2-40A5-A243-F2573CFEBFC8@.microsoft.com...
> > Hi,
> >
> > I have a stored proc which returns multiple result sets. I think SSRS
> > picks
> > up the first one by design. What's the best way forward, a data extension,
> > or
> > is there a simpler way?
> >
> > Thanks in advance,
> > Regards,
> > Datta
>
>

Multiple Server Reporting

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

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

Multiple Server Reporting

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

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

Multiple Server Reporting

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