Connecting to another SQL SERVER

  • Does anyone know the syntaxt to connect to and disconnect from another SQL SERVER while executing a T-SQL script from another intance?

  • You cannot do this. You need to setup a linked server to access another sql server from T-SQL.

    Steve Jones

  • Ok,

    So I've created a linked server, and can view all of the tables and views from the linked server, but when I reference it in a T-SQL script I get a

    Server: Msg 7314, Level 16, State 1, Line 38

    The table either does not exist or the current user does not have permissions on that table.

    I also gave my login full access to this table...

    could there be any other reason?

  • How are you defining the connection authentication method for the linked server?

    "Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

  • Instead of using sp_addlinkedserver, I'm going into my Enterprise Manager,Security tab, then adding a New Linked Server through there.

    The General Tab in the Properties of the new linked server is of type SQL SERVER, security tab set to "Be made using the login's current security context" and options tab having the first 5 checked (SQL SERVER 2000)

    I assume it may have something to do with the security tab b/c when I fool around with this, like specifying the login username and password I get different results..

    Am I doing something wrong?

  • Is you login a SQL login, if so then it does exist on the other server with permission (I assume based on what you said)? If so the use profiler to trace the remote server and audit logins to see if you can see a mistake occurr with the connection.

    "Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

  • Make sure you're using the full four part syntax, including the spelled out object owner.


  • Steve, what about using OpenRowset? I admit to a preference for linked servers myself.


  • Thanks Guys!

    The problem was missing the object owner. I did not have a fully qualified path.


    I was just using



    I have one more question about updating from a remote connection. I assume you can not update a remote table.

    i.e. "update Server_Name.DB_name.dbo.table_name set column_name = 'whatever' where column_name = 'whatever'"??

    So is there a different way to update a table on one server by running a T-Sql on another server?

    Edited by - petey20 on 04/03/2002 06:05:40 AM

  • That should work.


  • Just make sure Data Access, RPC, RPC Out on the Server Options tab of the Linked Server properties are enabled. Works great for me.

    "Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply