Results 1 to 5 of 5
  1. #1
    Join Date
    May 2004
    Posts
    2

    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
    from <database>..<table>

    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)

  2. #2
    Join Date
    Nov 2002
    Posts
    207
    Pls. provide exact error message(s) as it helps to get more insight into the problem.

    Thanks.

  3. #3
    Join Date
    May 2004
    Posts
    2
    This is the error.

    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).

  4. #4
    Join Date
    Nov 2002
    Posts
    207
    If I am not wrong, only way you can pass dbname and tablename as parameters is by using shell script. Hope someone else can provide more info.

  5. #5
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    If you're referring to a temp table (#) they live in tempDB by default (so you do not need to use the tempdb..#foo syntax).
    Thanks,

    Matt

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •