Unanswered: Using Variables in from portion of select statement
I am trying to create a trigger that checks for identical ids across databases. I am able to populate a variable with the remote database name. However when I try to do the select statement with the variable in the from potion of the select if does not compile.
This is how I populate the variable with the remote database name.
select @findb = FINDB.DBNAME
This is how I find the matching id in the remote database.
(I copy any results into a temp table.)
exec("select MSCBR_ID into "
+ @tempdb + ".." + @mscbr_table + " from "
+ @findb + "..BLSET f, "
+ @enrdb + "..deleted e "
+ "where e.MSCBR_ID = f.MSCBR_ID")
This is the check to see if there is a matching ID
This is where it fails. It does not like the @tempdb and @mscbr_id in the from statement.
IF EXISTS (select * from @tempdb..@mscbr_table)
DBD:ybase::st execute failed: Server message number=208 severity=16 state=1 line=38 server=ITG_DEV1 procedure=MSCBR_DEL text=tempdb..#mscbr_table not found. Specify owner.objectname or use sp_help to check whether the object exists (sp_help may produce lots of output).