Wrapping A Dynamic Sql Call In A Select

I'm expecting to revamp some stored procs so that their selects are executed on a dynamic string that always returns the same columns but varies the sources.

I'm concerned that the bread and butter of products like RS and SSIS is the ability to predict what columns, and what column types to expect from a query, but that introducing dynamic sql will complicate using them.

I'm motivated not to use temp tables or table vars if possible. I'm also somewhat motivated to learn of a solution that works equally well in 2000 and 2005.

I've tried wrapping dynamic calls in a select as shown below but to no avail...


After€¦

declare @sqlString nvarchar(4000)
set @sqlString = 'select * from [' + @dbName + '].[dbo].[activity]'

I€™ve already tried things like€¦.
select a.* from exec sp_executesql @sqlString a

and

select * from exec (@sqlString)