Help with a Query

Am I able to do something like this? The syntax below is obviously
wrong, I'm looking to see if there is a way to accomplish this.
DECLARE @.DBName varchar(5)
SET @.DBName = 'pubs'
SELECT * FROM @.DBName.dbo.authors
I have a master application and database containing data for several
companies. Each company uses a certain web-based sales software. I have
a local copy of those databases. Each of those databases is identical
in structure, they just contain each company's data.
I want to use stored procedures in my main database to run reports on
the data in each of the databases. I don't want to copy my stored
procedures into each database. Instead I want to call them from the
main database, and within the stored procedure, decide on which
database to run the t-sql.
Thanks!"George" <george.durzi@.gmail.com> wrote in message
news:1134592425.040038.242050@.z14g2000cwz.googlegroups.com...
> Am I able to do something like this? The syntax below is obviously
> wrong, I'm looking to see if there is a way to accomplish this.
> DECLARE @.DBName varchar(5)
> SET @.DBName = 'pubs'
> SELECT * FROM @.DBName.dbo.authors
> I have a master application and database containing data for several
> companies. Each company uses a certain web-based sales software. I have
> a local copy of those databases. Each of those databases is identical
> in structure, they just contain each company's data.
> I want to use stored procedures in my main database to run reports on
> the data in each of the databases. I don't want to copy my stored
> procedures into each database. Instead I want to call them from the
> main database, and within the stored procedure, decide on which
> database to run the t-sql.
> Thanks!
>
You will need to use Dynamic SQL.
Try the following:
EXECUTE ('SELECT * FROM ' + @.DBName + '.dbo.authors')
Rick Sawtell
MCT, MCSD, MCDBA|||Rick, the only issue with that is I actually am gonna be writing a
complex stored procedure.
It looks like dynamic sql is going to have to be the way to go though.|||Before diving into the dynamicity pool and drowning, take look up
"distributed partitioned views" in Books Online.
I'd create an extra database and use it as a central reporting data source.
ML
http://milambda.blogspot.com/