Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2004
    Location
    Tel Aviv - Israel
    Posts
    40

    Unanswered: How to use tables from two servers under the same group?

    Hi

    I have two servers under the same group in my enterprise manager, I need to run insert query on a table in one server and select into the insert from the other server.
    So what is the syntax to do it ?? hope it's possible...

    Thanks,

    Inon.

  2. #2
    Join Date
    Dec 2004
    Location
    Sweden
    Posts
    74
    First of all, the group in Enterprise Manager doesn't dictate whether this is possible or not, so that isn't a factor, really.
    The factor in this case is whether the two servers can "see" eachother, meaning if you can get a result when connected to one of the server executing a query like this

    select * from [OtherServername].[DBName].dbo.[tablename] where 1=2
    alt.
    select * from [InstanceName\OtherServername].[DBName].dbo.[tablename] where 1=2


    (Yes, even if you replace the placeholders, I wrote this query on purpose to return 0 rows, all I'm interessted in for the moment is wheter you get a result or not.)

    If one of the two above queries work, you've can write as I did, and if it doesn't, you need to setup a link between the two servers as described in the SQL Server books online help on the command sp_addlinkedserver.

  3. #3
    Join Date
    Jun 2004
    Location
    Tel Aviv - Israel
    Posts
    40

    Progress...

    I added a linked server, I can almost connect to it... the only thing left is to specify the login information since the defualt 'sa' can't login on the linked server.

    So, I guess it's in the provider string (@provstr), can you remind me what is the syntax to specify the login information ? And correct me if it's not supposed to be in the provider string.

    Thanks,

    Inon.

  4. #4
    Join Date
    Dec 2004
    Location
    Sweden
    Posts
    74

    Hmmmm .... what are you trying to do here...?

    Ummmm .... don't know exactly what you are trying to do here, but it sounds like you are trying to login to a server you aren't supposed to be able to login to. If you don't know the sa password you can use another user/password that has access to the tables you need, but I would strongly suggest you get hold of the sa password and use it in the link between the two servers, that way you won't have any access limitations at all, if that is possible ...

  5. #5
    Join Date
    Jun 2004
    Location
    Tel Aviv - Israel
    Posts
    40
    Quote Originally Posted by Nephilim
    Ummmm .... don't know exactly what you are trying to do here, but it sounds like you are trying to login to a server you aren't supposed to be able to login to. If you don't know the sa password you can use another user/password that has access to the tables you need, but I would strongly suggest you get hold of the sa password and use it in the link between the two servers, that way you won't have any access limitations at all, if that is possible ...
    No no no... I know the sa password, I asked how/where do I specify it ???
    You see, I added a linked server without specifing the login information, now, when I try to connect I get message "Login failed for sa..." this is because I did not specify the password anywhere, my question is, where do I specify it ??

    Inon.

  6. #6
    Join Date
    Dec 2004
    Location
    Sweden
    Posts
    74
    If you're using the Enterprise Manager you can specify the password in the tab under Security/Linked servers (Properties/Security).

    There you should see a text saying something like :

    "For a login not defined in the list above, connections will be made with the following context :" followed by 4 options. Tick the last one, fill in remote login (sa) and the password for sa in the next field.

    I'm sure there is a way to specify it in a command, but I just aint got the time to check it in the help for the moment.

Posting Permissions

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