Changing Databases

I have two databases on the same SQL server which have identical data
structures.
I want to be able to use one set of reports on my report server to run
against either database. How can I achieve this? Whenever I run the report
it always runs against whatever the datasource points at.
NickWhat you do is write a stored procedure for your sql need in third database
other than those two and write in a way that it can accept database name and
then fully qualify your object name and build a dynamic sql in the proc. Use
this proc in RS to buid one dataset and create another dataset with simple
query like "select dbname from sysdatabases where <criteira of your
interest>". Use this dataset as a input to the parameters of the first
database so the final report will have at least one parameter to pick a
database name from the dropdown list and you can run your report off of
selected database. I have implemented this way and I am running my report
off of more than two database from one server and it works great. Let me
know if you have any difficulties. email me.
Vipul Shah
"Nick Jacobs" wrote:
> I have two databases on the same SQL server which have identical data
> structures.
> I want to be able to use one set of reports on my report server to run
> against either database. How can I achieve this? Whenever I run the report
> it always runs against whatever the datasource points at.
> Nick
>
>

Changing Database Structures Through Code

I am having a hard time finding materials on this subject. I am guessing I am using the wrong keywords to search. Basically, I want to be able to modify database tables through a web form. They can add columns and delete columns through the form. I would just want to default the type of column and the length. I am sure it has been done, I was just wondering if anyone had some resources they could throw my way. I would appreciate it. Thanks.

Hi, I wrote a stored procedure to alter table, share with you:)

create proc sp_alterTbl @.TblName sysname,@.ColName sysname,@.ColType varchar(50)=null,
@.ColLen int=null,@.Op varchar(6)='Add'
as
begin
declare @.cmd varchar(500),@.TypeLenStr varchar(50)
if object_id(@.TblName) is null
Raiserror('Table does not exist!',16,1)
else
if not exists (select * from systypes where name= @.ColType)
Raiserror('Type does not exists!',16,1)
else
begin

select @.TypeLenStr=CASE @.ColLen WHEN null Then ' '
Else '('+convert(varchar(6),@.ColLen)+')'
END
select @.cmd= CASE @.Op when 'Add' Then 'alter table'+@.TblName+''+@.Op+''+@.ColName+' '
+@.ColType+ @.TypeLenStr
WHEN 'Drop' Then 'alter table'+@.TblName+''+@.Op+''+@.ColName+' '
END
EXEC(@.cmd)
END
END