OK, something weird is happening.
I have a standard Windows XP client and I am trying to set up a simple ODBC
entry. So I go to Control Panel/Administrative Tools/ODBC/System Tab. From
there I create a new ODBC entry connecting to a SQL Server. I enter in the
name of my ODBC and a descripton. When I hit the drop-down button to select
a server I see my 2 SQL Server machines (local machine also named "Jim" and
sbserver) but they are listed twice or even 3 times (see below) and the local
machine has even more entries. What gives and how do I clean this up? Both
seem to work when I "test" at the last step but I have a local application
using this ODBC and it is failing to run so I think it is having "issues"
with these multiple entries.
Thanks!
-Richard
e.g.
(local)
(local)
Jim
Jim
jim
sbserver
sbserver
Make sure your MDAC installation isn't flaky - you can use
component checker to verity the MDAC version and
installation. You can download the tool from:
http://msdn2.microsoft.com/en-us/data/aa937730.aspx
How many instances do you have on each of those servers?
Do you get the same list if you execute the following from
the command prompt:
osql -L
-Sue
On Fri, 16 Mar 2007 04:47:05 -0700, Richard K
<RichardK@.discussions.microsoft.com> wrote:
>OK, something weird is happening.
>I have a standard Windows XP client and I am trying to set up a simple ODBC
>entry. So I go to Control Panel/Administrative Tools/ODBC/System Tab. From
>there I create a new ODBC entry connecting to a SQL Server. I enter in the
>name of my ODBC and a descripton. When I hit the drop-down button to select
>a server I see my 2 SQL Server machines (local machine also named "Jim" and
>sbserver) but they are listed twice or even 3 times (see below) and the local
>machine has even more entries. What gives and how do I clean this up? Both
>seem to work when I "test" at the last step but I have a local application
>using this ODBC and it is failing to run so I think it is having "issues"
>with these multiple entries.
>Thanks!
>-Richard
>e.g.
>(local)
>(local)
>Jim
>Jim
>jim
>sbserver
>sbserver
Saturday, February 25, 2012
Multiple servers
Hi All,
I have recently published a website to our webserver and i get a sql error. We have a webserver that does not have sqlserver on it and and our database server which does. i have used the configuration utility to to setup my users and roles which created the ASPNETDB in my local App_Data folder. Is there a way to copy this database to our database server and change the references so the site refers to the new instance on the database server as apposed to the local instance when a user logs in?
Thanks
Bryan
Are you storing the database connection information in the web application's web.config file? Or did you hard-code it into each sql command?
|||all my other connections are stored in the webconfig. But as for the ASPNETDB.mdf connection string i have no idea where it is stored by default. Does it matter if there is no sql server on the pc that the site resides on. To my knowledge it does, so i need to find away to have the database on the database server and the site on our webserver. I dont know if i am making myself clear... if you create a simple site with a login.aspx with a login control and a default.aspx with a simple "HELLO" on it and choose ASP.net configuration web utility and setup some users and roles, it created a folder called App_Data where the ASPNETDB database is stored.
Now if this project was a piece of electornic equipment seperated into distinct pieces, i would like to remove the database piece from where it is in my application and physically move it to another geographical place namelly my other server (database server), but if i remove it totally, clip all the wires and remove it then my equipment (website) does not work correctly. So what i want to do is to extend the cables so that they are able to reach the other server (databse server) so my equipment (website) still works fine, just with the extended cable ( some sort of connection string stored somewhere! ) :-) kind of a dumb ass analogy, but im sure you get what I mean now.
I dont know if i am missing something or the only person that has done something like this but all i do is create a simple website, which works fine on my pc, and deploy it and the login doesnt work...
ANY suggestions will be greatly appreciated, ive been pulling my hair out for almost 2 days now trying to figure this out.
thanks for all the help
Bryan
|||
Hello,
The solution was: mounted the default sql serverdatabase (ASPNETDB) on our database server and added a connectionstring in the webconfig to point to it. what i had to do was remove theconnection string and then recreate it as follows
<remove name="LocalSqlServer"/>
<add name="LocalSqlServer" connectionString="DataSource=CORE;Initial Catalog=ASPNETDB;Persist Security Info=True;UserID=user;Password=password" providerName="System.Data.SqlClient"/>
this sorted out all my issues.
Thanks
BryanMultiple 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
>
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
>
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
I need to design a report that displays data for 3 branches of same
companies,
Branch A ,Branch B and Branch C. The problem is that each Branch has their
own
distinct server and database. The schema is identical between them, just
different data in each.
The report is identical for each Branch, the only different is that the
report needs to pull data from all the three Databases A,B,C to display the
report data for that Company as an aggregated view and Also data for each
branch
individually.
How can I design the report or setup the data source.
I really don't want to create and maintain 3 identical reports, the only
difference being the data source.
One possible option I guess would be to create a Linked Server and
Distributed view that combines data from identical tables in 3 databases, and
use that view as the datasource in the report.
Other option is moving data to the centralized server and report from that,
which would cause data redundancy both at local branch and at centralized
server and huge vloume of data movement and keeping track of data.
Just looking for what others have done in similar circumstances so I don't
spend multiple days architecting the wrong approach.
Thanks!
SHi There,
I was under impression that there are expertise here who has implemented a
valid and reliable solution to my similar issue. Now it makes me to beleive
that none has gone through such a senario.
Any help or suggestion are welcome
/
S
"ERS Developer" wrote:
> Let me preface this
> I need to design a report that displays data for 3 branches of same
> companies,
> Branch A ,Branch B and Branch C. The problem is that each Branch has their
> own
> distinct server and database. The schema is identical between them, just
> different data in each.
> The report is identical for each Branch, the only different is that the
> report needs to pull data from all the three Databases A,B,C to display the
> report data for that Company as an aggregated view and Also data for each
> branch
> individually.
> How can I design the report or setup the data source.
> I really don't want to create and maintain 3 identical reports, the only
> difference being the data source.
> One possible option I guess would be to create a Linked Server and
> Distributed view that combines data from identical tables in 3 databases, and
> use that view as the datasource in the report.
> Other option is moving data to the centralized server and report from that,
> which would cause data redundancy both at local branch and at centralized
> server and huge vloume of data movement and keeping track of data.
> Just looking for what others have done in similar circumstances so I don't
> spend multiple days architecting the wrong approach.
> Thanks!
> S|||ERS,
The simplest solution is linked servers (if you have high
availability), have one report and use IF statements based on a
parameter to decide the query to use.
The parameter would be the branch, so you could have;
If @.Branch = 'A'
Select * From BranchAServer.MyDB.dbo.MyTable
If @.Branch = 'B'
Select * From BranchBServer.MyDB.dbo.MyTable
If @.Branch = 'C'
Select * From BranchCServer.MyDB.dbo.MyTable
So the user could select their branch. This is very basic and could be
made more efficient placing the query in a stored procedure. Also there
is aperformance issue with distributed queries like this pulling all
data to the calling server before processing. Ideally you'd use an
OPENQUERY, check BOL for info on that.
Letting the user select a branch is obviously a security issue, but you
could deny access to the main report but create 'linked' reports for
each branch and set the branch parameter in properties of the linked
report and set it not to prompt the user. 'Linked' reports are merely
shortcuts, so when you change the main report, it's reflected in the
linked reports as well, but the parameter settings remain independant.
There is another option which we use here, which is to create a simple
form of data warehouse. This is more useful if you have disparate
datasources. Basically, create jobs that copies data from the branches
into a central database periodically, say every 20 minutes. To reduce
redundancy, just take the fields you need. You could use replication to
do this also. Not only will this make your report quicker, but it
should reduce the overhead on your OLTP servers.
Chris
ERS Developer wrote:
> Hi There,
> I was under impression that there are expertise here who has
> implemented a valid and reliable solution to my similar issue. Now it
> makes me to beleive that none has gone through such a senario.
> Any help or suggestion are welcome
> /
> S
> "ERS Developer" wrote:
> > Let me preface this
> > I need to design a report that displays data for 3 branches of same
> > companies,
> > Branch A ,Branch B and Branch C. The problem is that each Branch
> > has their own
> > distinct server and database. The schema is identical between them,
> > just different data in each.
> >
> > The report is identical for each Branch, the only different is that
> > the report needs to pull data from all the three Databases A,B,C to
> > display the report data for that Company as an aggregated view and
> > Also data for each branch
> > individually.
> >
> > How can I design the report or setup the data source.
> >
> > I really don't want to create and maintain 3 identical reports, the
> > only difference being the data source.
> >
> > One possible option I guess would be to create a Linked Server and
> > Distributed view that combines data from identical tables in 3
> > databases, and use that view as the datasource in the report.
> >
> > Other option is moving data to the centralized server and report
> > from that, which would cause data redundancy both at local branch
> > and at centralized server and huge vloume of data movement and
> > keeping track of data.
> >
> > Just looking for what others have done in similar circumstances so
> > I don't spend multiple days architecting the wrong approach.
> >
> > Thanks!
> > S|||Hi S/All,
just to let viewers here know the generic answer to the generic
question...
Lots of people, tens of thousands of them, have faced the issue of
reporting from multiple different oprational systems and the Corporate
Information Factory is one rather comprehensive solution to the
pervasive issue of consolitated analysis/reporting...
Best Regards
Peter Nolan
www.peternolan.com
http://groups-beta.google.com/group/microsoft.public.sqlserver.datawarehouse/browse_frm/thread/473404688a753292/64024bd1636f02e9?hl=en#64024bd1636f02e9
Multiple Server query
I'm trying to write a query that will be used in a stored
proc to grab data from a table on a different server.
So I have SVR1 trying to get data from SVR2, so I'm trying
to use this query:
SELECT * FROM SVR2..TESTDB.TESTTBL
but I get the following error:
OLE DB provider 'SVR2' does not contain
table '"dbo"."master"'. The table either does not exist
or the current user does not have permissions on that
table.
OLE DB error trace [Non-interface error: OLE DB provider
does not contain the table: ProviderName='SVR2',
TableName='"dbo"."master"'].
Can anyone help?
Thanks,
Chris
Chris,
It seems more likely that you would want
select * from SVR2.TESTDB..TESTTBL
though I'm not quite sure why you get this particular error. What did
you use to add the server SVR2?
Steve Kass
Drew University
Chris W wrote:
>I'm trying to write a query that will be used in a stored
>proc to grab data from a table on a different server.
>So I have SVR1 trying to get data from SVR2, so I'm trying
>to use this query:
>SELECT * FROM SVR2..TESTDB.TESTTBL
>but I get the following error:
>OLE DB provider 'SVR2' does not contain
>table '"dbo"."master"'. The table either does not exist
>or the current user does not have permissions on that
>table.
>OLE DB error trace [Non-interface error: OLE DB provider
>does not contain the table: ProviderName='SVR2',
>TableName='"dbo"."master"'].
>
>Can anyone help?
>Thanks,
>Chris
>
>
proc to grab data from a table on a different server.
So I have SVR1 trying to get data from SVR2, so I'm trying
to use this query:
SELECT * FROM SVR2..TESTDB.TESTTBL
but I get the following error:
OLE DB provider 'SVR2' does not contain
table '"dbo"."master"'. The table either does not exist
or the current user does not have permissions on that
table.
OLE DB error trace [Non-interface error: OLE DB provider
does not contain the table: ProviderName='SVR2',
TableName='"dbo"."master"'].
Can anyone help?
Thanks,
Chris
Chris,
It seems more likely that you would want
select * from SVR2.TESTDB..TESTTBL
though I'm not quite sure why you get this particular error. What did
you use to add the server SVR2?
Steve Kass
Drew University
Chris W wrote:
>I'm trying to write a query that will be used in a stored
>proc to grab data from a table on a different server.
>So I have SVR1 trying to get data from SVR2, so I'm trying
>to use this query:
>SELECT * FROM SVR2..TESTDB.TESTTBL
>but I get the following error:
>OLE DB provider 'SVR2' does not contain
>table '"dbo"."master"'. The table either does not exist
>or the current user does not have permissions on that
>table.
>OLE DB error trace [Non-interface error: OLE DB provider
>does not contain the table: ProviderName='SVR2',
>TableName='"dbo"."master"'].
>
>Can anyone help?
>Thanks,
>Chris
>
>
Multiple server deployment
I have a multiple report server deployment, and we removed one of the servers from the farm, however the name still appears in the list on the initialize screen in report server configurator.
We removed reporting services from this box, but the name still appears in the list.
Where is this server name being stored?
Thanks!!
BobP
Just for posterity... It is being stored in the Keys table in the ReportServer database.
BobP
Subscribe to:
Posts (Atom)