Hi,
Is it possible to change a datasource (or its connection string) in the
moment of report running?
Thanks.On SQL 2000, the only way is to create an SP which uses linked servers based
on the parameter you pass ie
create proc getdata @.source varchar(10)
as
if @.source = 'a'
select ... from mylinkedserver1.db.dbo.table
else
if @.source
With SQL 2005 you can use a dynamic data source based on a parameter ie. In
the connection string
="data source=" &Parameters!ServerName.Value & ";initial
catalog=AdventureWorks
Hope this helps
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Vladimir Evdokimov" <evesq@.uk2.net> wrote in message
news:OCvqf0kzFHA.2008@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Is it possible to change a datasource (or its connection string) in the
> moment of report running?
> Thanks.
>|||Hello
In SQL 2000 is it possible to supply the datasource (database name etc) as
part of the arguments supplied in the URL?
My application runs off multiple databases corresponding to different
customers and I need to switch the datasource based on the customer user
calling the report.
Any ideas on the best way to accomplish this task in SQL 2000?
Thank you|||Although you can do this with RS 2000 it will be easier in 2005.
In 2000 you have to use dynamic SQL. Use generic query designer (button to
switch to this is to the right of the ...)
declare @.SQL varchar(255)
select @.SQL = 'select table_name from ' + @.Database +
'.information_schema.tables order by table_name'
exec (@.SQL)
Note that @.Database should cause a report parameter called this to be
created. Anyway, this is the concept.
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:
>>>>>>>>
a.. 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.
b.. 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.
c.. Specify credentials separately from the connection string. You can use
stored credentials, prompted credentials, or integrated security.
d.. 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.
e.. 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.
f.. 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
"GavinMc" <GavinMc@.discussions.microsoft.com> wrote in message
news:F7640227-35C6-44CB-89BD-ED283ED242A4@.microsoft.com...
> Hello
> In SQL 2000 is it possible to supply the datasource (database name etc) as
> part of the arguments supplied in the URL?
> My application runs off multiple databases corresponding to different
> customers and I need to switch the datasource based on the customer user
> calling the report.
> Any ideas on the best way to accomplish this task in SQL 2000?
> Thank you
>|||Thanks Bruce, unfortunately I'm stuck with SQL 2000 for the moment...
Just a further enquiry based on the above. To allow for the switching of
databases on the fly for my dataset based on an entered parameter, I created
the following sql:
USE master
declare @.SQL varchar(255), @.mydb varchar(255)
set @.mydb= (SELECT dbID FROM co WHERE (coID = @.co))
select @.SQL='USE ' + @.mydb + ' SELECT accid, accno, accname FROM acc'
exec (@.SQL)
This reads a user defined 'co' table in master that contains a reference to
my target databases, When running a report I then supply a 'coID' to retrieve
the reference to the correct database and this is then used as a variable in
report's underlying dataset to ensure that the correct database is used. I
think this is along the lines of what you suggested above.
My question is whether the select statement needs to be built up as a string
for execution, this will obviously require rewriting all my existing plain
sql statements and inserting concatenation operators around the parameters
etc.
Is there some way that I can declare a parameter for the database I want to
run the report off, and include this at the start of existing sql statements
in the 'use' statement without explicitly declaring a sql str variable?
