You could try to have all stored procedures in one database, and query the
different databases from this stored procedure.
Have your report give a parameter for which client database you want to use,
and then have a case structure in your stored procedure.
Pseudoish code:
CREATE PROCEDURE [dbo].[HNDGetUsers]
(
@ClientID varchar(20),
@Parameter1 varchar(3)
)
AS
CASE
when @ClientID = 'Client1' then
SELECT [Client1Database].[Client1TableOwner].USERINFO.ID AS UserInfoID,
[Client1Database].[Client1TableOwner].USERINFO.NAME
FROM [Client1Database].[Client1TableOwner].USERINFO
WHERE ([Client1Database].[Client1TableOwner].EMPLTABLE.DATAAREAID =
@Parameter1)
when @ClientID = 'Client2' then
SELECT [Client2Database].[Client2TableOwner].USERINFO.ID AS UserInfoID,
[Client2Database].[Client2TableOwner].USERINFO.NAME
FROM [Client2Database].[Client2TableOwner].USERINFO
WHERE ([Client2Database].[Client2TableOwner].USERINFO.DATAAREAID =
@Parameter1)
end
RETURN
The statement might be syntactically wrong, but hopefully you get the idea.
You need to run the stored procedure as a user that has read rights in all
the databases you want to get data from.
Kaisa M. Lindahl Lervik
[quoted text, click to view] "ddaniels via SQLMonster.com" <u12523@uwe> wrote in message
news:622a977dc766c@uwe...
> Hi -
>
> I have a single database structure that will be implemented several times
> in
> SQL Server 2000 to support multiple clients. I would like to have a
> single
> instance of a report to use against all of these databases. I envision
> using
> the connection string to control which database the report is using as its
> datasource but I'm finding that Reporting Services for SQL Server 2000
> doesnt
> handle this.
>
> I have seen other postinigs with this same problem. The popular response
> is
> to pass the database as a parameter however, I'm using stored procedures
> and
> Reporting Services doesnt support EXEC (or EXECUTE). Does anyone have any
> other suggestions how I may handle this.
>
> Thanks in advance.
>
> --
> Message posted via
http://www.sqlmonster.com 
