April 2, 2002 at 9:04 am
Does anyone know the syntaxt to connect to and disconnect from another SQL SERVER while executing a T-SQL script from another intance?
April 2, 2002 at 10:44 am
You cannot do this. You need to setup a linked server to access another sql server from T-SQL.
Steve Jones
April 2, 2002 at 1:15 pm
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?
April 2, 2002 at 2:00 pm
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)
April 2, 2002 at 2:16 pm
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?
April 2, 2002 at 2:53 pm
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)
April 2, 2002 at 4:08 pm
Make sure you're using the full four part syntax, including the spelled out object owner.
Andy
April 2, 2002 at 4:16 pm
Steve, what about using OpenRowset? I admit to a preference for linked servers myself.
Andy
April 3, 2002 at 6:04 am
Thanks Guys!
The problem was missing the object owner. I did not have a fully qualified path.
Server_Name.DB_name.dbo.table_name
I was just using
Server_Name.DB_name..table_name
....
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
April 3, 2002 at 6:32 am
April 3, 2002 at 7:20 am
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