Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2004
    Posts
    7

    Unanswered: Invalid Object Name - Grr... SOLVED

    This is what I have. It works fine until I get to the select statement, then it tells me that I have an invalid object name. What am I missing? Thanks!

    DECLARE @SvrName varchar(100)

    if @@SERVERNAME='pubs' begin
    set @SvrName=’pubs.books.isbn’
    print @SvrName
    end
    if @@SERVERNAME='MGMFILENET' begin
    set @SvrName=’store.books.isbn’
    print@SvrName
    end

    print @@SERVERNAME
    PRINT @SvrName
    SELECT * FROM "@SvrName"
    Last edited by acral; 03-03-04 at 18:49.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Doesn't work that way


    DECALRE @SQL
    SET @SQL = 'SELECT * FROM ' + @SvrName
    EXEC(@SQL)

    But why do this...uhh dynamic sql....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Feb 2004
    Posts
    7
    Even in a stored proc?

    What I am trying to do is find out what the server is, then point the rest of the gazillion SQL statements to follow to that server.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by acral
    Even in a stored proc?

    What I am trying to do is find out what the server is, then point the rest of the gazillion SQL statements to follow to that server.
    Huh?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Feb 2004
    Posts
    7
    This will be running as a stored proc... the proc takes many steps in moving data around, but first I need to determine what environment the user is in (i.e. what server)...

    The there will be a series of statements such as Update this table, email a percentage to that group, make a temp table over there, and so on. In one environment, all of the databases are on one server, in another environment, the database are on different servers. In both cases, they have to interact.

    So if YOU are logged into the system, when the stored proc executes, it will see which server you are logged to then point you from there by way of the rest of the statements.

    Make sense?

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Not to me, but that's not saying much..

    What's the application layer?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Wouldn't it just be simpler to write a stored procedure that does what you need on each server, then call the stored procedure on the appropriate server? This gets about a gazillion RPC calls and cross-server queries (with potential cross-server joins) out of the way.

    That way each box can call one stored procedure (you could even make it an sp_ if you wanted to make thing simple), and there are so many fewer moving parts.

    -PatP

  8. #8
    Join Date
    Feb 2004
    Posts
    7
    Okay, here's what I ended up doing...

    using a string like

    Exec(@SQL) was not going to cut it, so I have an if/then scenario that checks @@SERVERNAME then sets a variable. The contents of that variable in turn point to the right server for the right instance.

    The reasoning is this.. in one evironment the databases referenced are on the same server, in another environment they are on different servers.

    Thanks for the help... I would have kept pounding on that stupid "string" half the night had you guys not set me straight.

Posting Permissions

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