dataSource is different instance of same database(Urgent)

Hi,

I need help......

I am trying to create a report whose datasource will be different instances of the same database.So both the databases contains the same tables with same schema but the thing is that teh data is different.I have to collect the daat from different instances of the same table a(as they contains different different data) and using the collection to generate the report.

It's very very urgent ...plz reply ...

Thanx in advance

HI,

I dont thnk this is possible to do(as far as i know you cant connect a datasource to different database at runtime and also you cant change datasource at runtime). Now to solve your problem i can think of these solutions -

1. Create two datasources(connecting to two different database), and in your report create two datasets connecting to each one of the datasources. Similarly create two instances of tables (or whatever control you want to display) and connect controls to dataets. Now on runtime show/hide the table based on data from which table you want to show.

This is not a verygood choice(actually very poor) and you can achieve this using two copies of same report connecting to different databases and by showing the appropriate report to user.

2. Better approch is to have two tables in the same database like a_1 and a_2 and pass a parametre to your sp specifying which table to be used. In SP you will have to create dynamic sql in which table name will be created based on the parametre passed.

Hope i am able to convey whats there in my mind....

|||

Just came across this blog, it looks like we can dynamically change the connection string(did'nt tried though) -

http://blogs.msdn.com/bimusings/archive/2006/07/20/673051.aspx

DataDir Property - Is it possible to store files on more than 1 drive?

The "DataDir" property for an AS 2005 instance seems to only allow for one path to be specified. I am working with a very large data set that is being updated frequently and I would like to be able to reference more than on LUN on my disk array.

Yes, you can.

Take a look at the StorageLocation property of your partition.

Edward Melomed (MSFT)

--

This posting is provided "AS IS" with no warranties, and confers no rights.

Databases: Active tranzactions (_Total)

MS SQL Server 2000:
When I collected performance data about the active tranzactions counter of my SQL server I found that (_Total) instance is very large (>9999999999999) and is not equal to the sum of the partial instances by DBs.
I have SP2 and all security patches installed.
What's wrong?

Thanks!Which way do you collect the data?|||Originally posted by Coolberg
Which way do you collect the data?

As usual:
Programs=>Administrative tools=>Performance=>Performance Logs and Alerts=>Counter Logs=>New Log Settings=>Name=>Add=>SQLServer: Databases=>Active Transactions+Total=>Add=>Start

Databases show in EM but not in sysdatabases?

I'm managing the back end SQL 2000 instance for a vendors application. The
application allows users to create databases on the fly for testing, etc.
I opened the instance in Enterprise Manager, and noted that the number of
databases had grown to about a dozen (other than the system databases). Hmm,
so being concerned about drive space, etc, I executed a sp_helpdb.
Yikes..only one of the dozen user databases showed in the listing for
sp_helpdb. Checked sysdatabases and sure enough only one user db shows.
But in EM I can open any of the other 11 DB's, open tables from them, view
options, etc etc. So I'm just curious, how can I view them in EM but they
don't show in sysdatabases?
TIA
<*blush*>..Sigh..one of those days. I did a 'Romo'. Time for a brewski.
"Tibor Karaszi" wrote:

> My guess is that you are connecting to different SQL Server instances.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "bill_the_cat" <billthecat@.discussions.microsoft.com> wrote in message
> news:AF04CEDA-BF16-415C-A652-CB7693F2F16D@.microsoft.com...
>

Databases show in EM but not in sysdatabases?

I'm managing the back end SQL 2000 instance for a vendors application. The
application allows users to create databases on the fly for testing, etc.
I opened the instance in Enterprise Manager, and noted that the number of
databases had grown to about a dozen (other than the system databases). Hmm
,
so being concerned about drive space, etc, I executed a sp_helpdb.
Yikes..only one of the dozen user databases showed in the listing for
sp_helpdb. Checked sysdatabases and sure enough only one user db shows.
But in EM I can open any of the other 11 DB's, open tables from them, view
options, etc etc. So I'm just curious, how can I view them in EM but they
don't show in sysdatabases?
TIAMy guess is that you are connecting to different SQL Server instances.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"bill_the_cat" <billthecat@.discussions.microsoft.com> wrote in message
news:AF04CEDA-BF16-415C-A652-CB7693F2F16D@.microsoft.com...
> I'm managing the back end SQL 2000 instance for a vendors application. Th
e
> application allows users to create databases on the fly for testing, etc.
> I opened the instance in Enterprise Manager, and noted that the number of
> databases had grown to about a dozen (other than the system databases). H
mm,
> so being concerned about drive space, etc, I executed a sp_helpdb.
> Yikes..only one of the dozen user databases showed in the listing for
> sp_helpdb. Checked sysdatabases and sure enough only one user db shows.
> But in EM I can open any of the other 11 DB's, open tables from them, view
> options, etc etc. So I'm just curious, how can I view them in EM but they
> don't show in sysdatabases?
> TIA|||<*blush*>..Sigh..one of those days. I did a 'Romo'. Time for a brewski.
"Tibor Karaszi" wrote:

> My guess is that you are connecting to different SQL Server instances.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "bill_the_cat" <billthecat@.discussions.microsoft.com> wrote in message
> news:AF04CEDA-BF16-415C-A652-CB7693F2F16D@.microsoft.com...
>|||Happens to all of us... :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"bill_the_cat" <billthecat@.discussions.microsoft.com> wrote in message
news:DBD746A2-59BE-49C8-90A9-757086DA9412@.microsoft.com...[vbcol=seagreen]
> <*blush*>..Sigh..one of those days. I did a 'Romo'. Time for a brewski.
> "Tibor Karaszi" wrote:
>