October 3, 2007 at 3:41 pm
I am trying to get a list of roles from a specific set of DB's. My DB's are stored in a table along with the server name. So, I concatenate the values together to get something like 'EX_SERVER_NAME.EX_DB_NAME.dbo.sysusers'.
Here is the select I want to run:
select uid, name
from sysusers
where issqlrole = 1
and gid > 0
However, I want to know what the values are on another server and DB.
So, instead of '...from sysusers..., I want ...from 'EX_SERVER_NAME.EX_DB_NAME.dbo.sysusers'. But I can't hardcode the value because I am getting it from a table and putting it into a variable. And '...from @db_full_name...' won't compile. Can I do this?
October 3, 2007 at 3:48 pm
declare @variable varchar(2000)
set @variable = 'select * ...from' + @server + '.' +......
exec (@varible)
* Noel
October 3, 2007 at 7:05 pm
I have a proc with this:
DECLARE @TempRoles TABLE
(db_name char(25),
app_name char(25),
db_role char(25),
role_dscr varchar(255))
select @select_var = 'INSERT INTO @TempRoles (db_name, app_name, db_role, role_dscr).........
The proc compiles OK but when I run it I get:'Must declare the variable '@TempRoles'.'
If I do ...'INSERT INTO ' +@TempRoles+' (db_name, app_name,...
the proc won;t compile and I get::'Must declare the variable '@TempRoles'.'
October 4, 2007 at 6:41 am
If you declare a variable, it is only visible in the context of your connection. If you then use EXEC @select_var to run some code that contains the name of that variable, this is run in a different context and so the variable is not visible. You will either need to declare the table within the code that you run with EXEC, or use a permanent table or global temporary table.
Hope that makes sense
John
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply