how to change database at runtime?

Hello,
Our customers often have 2 or more databases with the same structure.
How can we pass the database as a parameter at runtime in Reporting Services
2005?
Thank you,
LoreacaIn 2005 you will be able to have dynamic datasources. From 2005 help:
Data Source Expressions
You can put an expression into a connection string to allow users to select
the data source at run time. For example, suppose a multinational firm has
data servers in several countries. With an expression-based connection
string, a user who is running a sales report can select a data source for a
particular country before running the report.
The following example illustrates the use of a data source expression in a
SQL Server connection string. The example assumes you have created a report
parameter named ServerName:
Copy Code
="data source=" &Parameters!ServerName.Value & ";initial
catalog=AdventureWorks
Data source expressions are processed at run time or when a report is
previewed. The expression must be written in Visual Basic. Use the following
guidelines when defining a data source expression:
>>>>>>>>
Design the report using a static connection string. A static connection
string refers to a connection string that is not set through an expression
(for example, when you follow the steps for creating a report-specific or
shared data source, you are defining a static connection string). Using a
static connection string allows you to connect to the data source in Report
Designer so that you can get the query results you need to create the
report.
When defining the data source connection, do not use a shared data source.
You cannot use a data source expression in a shared data source. You must
define a report-specific data source for the report.
Specify credentials separately from the connection string. You can use
stored credentials, prompted credentials, or integrated security.
Add a report parameter to specify a data source. For parameter values, you
can either provide a static list of available values (in this case, the
available values should be data sources you can use with the report) or
define a query that retrieves a list of data sources at run time.
Be sure that the list of data sources share the same database schema. All
report design begins with schema information. If there is a mismatch between
the schema used to define the report and the actual schema used by the
report at run time, the report might not run.
Before publishing the report, replace the static connection string with an
expression. Wait until you are finished designing the report before you
replace the static connection string with an expression. Once you use an
expression, you cannot execute the query in Report Designer. Furthermore,
the field list in the Datasets window and the Parameters list will not
update automatically.
>>>>>>>
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Lori" <lhaiducescu@.seniorsoftware.ro> wrote in message
news:eXsv4cceGHA.1324@.TK2MSFTNGP04.phx.gbl...
> Hello,
> Our customers often have 2 or more databases with the same structure.
> How can we pass the database as a parameter at runtime in Reporting
> Services 2005?
> Thank you,
> Loreaca
>|||Perfect.
Thank you!
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:eUgGHreeGHA.1320@.TK2MSFTNGP04.phx.gbl...
> In 2005 you will be able to have dynamic datasources. From 2005 help:
> Data Source Expressions
> You can put an expression into a connection string to allow users to
> select the data source at run time. For example, suppose a multinational
> firm has data servers in several countries. With an expression-based
> connection string, a user who is running a sales report can select a data
> source for a particular country before running the report.
> The following example illustrates the use of a data source expression in a
> SQL Server connection string. The example assumes you have created a
> report parameter named ServerName:
> Copy Code
> ="data source=" &Parameters!ServerName.Value & ";initial
> catalog=AdventureWorks
>
>
> Data source expressions are processed at run time or when a report is
> previewed. The expression must be written in Visual Basic. Use the
> following guidelines when defining a data source expression:
>>>>>>>>
> Design the report using a static connection string. A static connection
> string refers to a connection string that is not set through an expression
> (for example, when you follow the steps for creating a report-specific or
> shared data source, you are defining a static connection string). Using a
> static connection string allows you to connect to the data source in
> Report Designer so that you can get the query results you need to create
> the report.
> When defining the data source connection, do not use a shared data source.
> You cannot use a data source expression in a shared data source. You must
> define a report-specific data source for the report.
> Specify credentials separately from the connection string. You can use
> stored credentials, prompted credentials, or integrated security.
> Add a report parameter to specify a data source. For parameter values, you
> can either provide a static list of available values (in this case, the
> available values should be data sources you can use with the report) or
> define a query that retrieves a list of data sources at run time.
> Be sure that the list of data sources share the same database schema. All
> report design begins with schema information. If there is a mismatch
> between the schema used to define the report and the actual schema used by
> the report at run time, the report might not run.
> Before publishing the report, replace the static connection string with an
> expression. Wait until you are finished designing the report before you
> replace the static connection string with an expression. Once you use an
> expression, you cannot execute the query in Report Designer. Furthermore,
> the field list in the Datasets window and the Parameters list will not
> update automatically.
>>>>>>>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Lori" <lhaiducescu@.seniorsoftware.ro> wrote in message
> news:eXsv4cceGHA.1324@.TK2MSFTNGP04.phx.gbl...
>> Hello,
>> Our customers often have 2 or more databases with the same structure.
>> How can we pass the database as a parameter at runtime in Reporting
>> Services 2005?
>> Thank you,
>> Loreaca
>

How to Change AutoNumber Not Start From 1

I have a table with structure below :
Table_A
{
PGradeID int IDENTITY(1,1),
PName varchar(20)
}

If I delete all records on Table_A, and then fill some records,
PGradeID will no longer start from 1 anymore, everybody knows that. How
to change it so PGradeID will start from 1 after all records was
deleted?

Thanks b4

ResantLookup the DBCC CHECKIDENT command. Also, if you TRUNCATE rather than DELETE
then the IDENTITY will be reset to seed.

Why do you care what the IDENTITY value is? It's usually fatal to attach any
significance to the value assigned by IDENTITY. If you care about the value
then IDENTITY isn't the right solution.

--
David Portas
SQL Server MVP
--|||Reset identity value will not cause problem in my case, but I
appreciate your warning.
Thanks a lot, it's works!

How to build good Data Warehouse Structure

I'm new to OLAP, and just tried build OLAP & Data Warehousing using
DTS. Now I'm arrive at the step where I must concern about performance.
There some questions that I want to ask, there're:
1. Where should I store OLTP database and OLAP database, should they in
separate database or even in separate server?
2. Which should i choose, create fact tables by create new tables or
views?
3. What best technique to transfer data from OLTP to OLAP except DTS
(or better than DTS) ?
Could you give me suggestion how to build good structure of Data
Warehouse?
Thx in advance
There is a lot of point to analyse:
* Data volume size
* Usage of the data (who, how, when, how many time)
* Frequency of updates
If your current OLTP environnement is not used at 100% and if the volume is
small, then you can use the same server for both OLTP & OLAP databases. If
we talk about more then 10Gb of data, moving to another server could help
you (because the hard drive setup will be different). in my case I have some
installations which shared a lot of databases, operationnal + olap etc... on
1 server only, due to a small amount of data.
If you plan to make some cleansing processes, or plan to query directly your
OLAP database (thourgh SQL syntaxes like reports), then use tables to make
sure you can create specific indexes. If you plan to just fill an OLAP cube,
and your OLTP database is clean and there is no cleansing or transformation
to do, then use views, but this impact the OLTP database during cube
process.
And finally, there is a lot of ETL tools on the market. And again, choose
the right one in term of performance, transformations capabilities, etc...
Do you plan to have a "big" project? what is the budget? 10 000$, 100
000$...?
Do you talk about 1Gb of data, 10Gb, 100Gb, 1Tb?
"Resant" <resant_v@.yahoo.com> wrote in message
news:1110869095.453586.311680@.f14g2000cwb.googlegr oups.com...
> I'm new to OLAP, and just tried build OLAP & Data Warehousing using
> DTS. Now I'm arrive at the step where I must concern about performance.
> There some questions that I want to ask, there're:
> 1. Where should I store OLTP database and OLAP database, should they in
> separate database or even in separate server?
> 2. Which should i choose, create fact tables by create new tables or
> views?
> 3. What best technique to transfer data from OLTP to OLAP except DTS
> (or better than DTS) ?
> Could you give me suggestion how to build good structure of Data
> Warehouse?
> Thx in advance
>

How to build good Data Warehouse Structure

I'm new to OLAP, and just tried build OLAP & Data Warehousing using
DTS. Now I'm arrive at the step where I must concern about performance.
There some questions that I want to ask, there're:
1. Where should I store OLTP database and OLAP database, should they in
separate database or even in separate server?
2. Which should i choose, create fact tables by create new tables or
views?
3. What best technique to transfer data from OLTP to OLAP except DTS
(or better than DTS) ?
Could you give me suggestion how to build good structure of Data
Warehouse?
Thx in advanceThere is a lot of point to analyse:
* Data volume size
* Usage of the data (who, how, when, how many time)
* Frequency of updates
If your current OLTP environnement is not used at 100% and if the volume is
small, then you can use the same server for both OLTP & OLAP databases. If
we talk about more then 10Gb of data, moving to another server could help
you (because the hard drive setup will be different). in my case I have some
installations which shared a lot of databases, operationnal + olap etc... on
1 server only, due to a small amount of data.
If you plan to make some cleansing processes, or plan to query directly your
OLAP database (thourgh SQL syntaxes like reports), then use tables to make
sure you can create specific indexes. If you plan to just fill an OLAP cube,
and your OLTP database is clean and there is no cleansing or transformation
to do, then use views, but this impact the OLTP database during cube
process.
And finally, there is a lot of ETL tools on the market. And again, choose
the right one in term of performance, transformations capabilities, etc...
Do you plan to have a "big" project? what is the budget? 10 000$, 100
000$...?
Do you talk about 1Gb of data, 10Gb, 100Gb, 1Tb?
"Resant" <resant_v@.yahoo.com> wrote in message
news:1110869095.453586.311680@.f14g2000cwb.googlegroups.com...
> I'm new to OLAP, and just tried build OLAP & Data Warehousing using
> DTS. Now I'm arrive at the step where I must concern about performance.
> There some questions that I want to ask, there're:
> 1. Where should I store OLTP database and OLAP database, should they in
> separate database or even in separate server?
> 2. Which should i choose, create fact tables by create new tables or
> views?
> 3. What best technique to transfer data from OLTP to OLAP except DTS
> (or better than DTS) ?
> Could you give me suggestion how to build good structure of Data
> Warehouse?
> Thx in advance
>