creating linked server
-
In my system i have one local server, there is one more remote server which is connected through lan i want to connect these two server and retrieve some details by using sql query.when i try to link the server i face this error
"There is no security context that can be used to verify if the specified SQL server exists. Would you like to continue to create the linked server?"
Can any one help me on this problem.
Thanks
-
How did you create the linked server? Did you specify the login mappings on the security tab?
Regards,Yelena Varsha
-
Before creating the Linked server, I linked the other server name
'Prod' which is connected in lan by using Server Registeration option available in SQL Enterprise manager . So when i open the Sql Server Enterprise manager it shows both the server
ie., local server name '
MIS' and newly registered server 'Prod'. By pressing security option, under 'Prod' server i select linked server option and under server type i mentioned Sql server type and mention the name of newly registered server 'Prod'. In security option i mentioned nothing and select the option 'Be made without using a security context'. After this it shows the error 'OLE DB provider 'SQLOLEDB' reported an error.
I tried another way ie.,
enter the Remote login : after select the option in security tab 'Be made using the security context'. after this method it shows some of the tables which is no way connected with the server 'Prod'.
what should i do?
My ultimate aim is, I want to retrieve the data from different tables available in both local server and new registered server. Is it really possible. If it is possible, pls let me know the method.
Thanks.
-
Before creating the Linked server, I linked the other server name
'Prod' which is connected in lan by using Server Registeration option available in SQL Enterprise manager . So when i open the Sql Server Enterprise manager it shows both the server
ie., local server name '
MIS' and newly registered server 'Prod'. By pressing security option, under 'Prod' server i select linked server option and under server type i mentioned Sql server type and mention the name of newly registered server 'Prod'. In security option i mentioned nothing and select the option 'Be made without using a security context'. After this it shows the error 'OLE DB provider 'SQLOLEDB' reported an error.
I tried another way ie.,
enter the Remote login : after select the option in security tab 'Be made using the security context'. after this method it shows some of the tables which is no way connected with the server 'Prod'.
what should i do?
My ultimate aim is, I want to retrieve the data from different tables available in both local server and new registered server. Is it really possible. If it is possible, pls let me know the method.
Thanks.
-
Did you see the today's article by Greg Larsen? There are several steps for the linked servers that can help
http://www.databasejournal.com/features/mssql/article.php/3696506
Setting Up Delegation for Linked Servers
Regards,Yelena Varsha
-
create a new login/password on the target server that you want to link to. Make this login a user on the databases that you want to query, and ensure that the user has (at least) datareader access to the required tables. Check by connecting to the target database via query analyzer using the login/password that you just created. If you can retrieve data, proceeed to the next step:
on the host server, add a new linked server (the target server) using the new login/password as authentication. No need for user mapping or delegation etc - not in my experience anyway. Once the new linked server has been added, try querying the target database from the host server. You will need to use four part naming in the query - e.g. Select * from server.northwind.dbo.orders.
-
Try the following on your local server, remember you will need to set the security context.
--SCRIPT START
Create Procedure usp_AddlinkedServer (
@a_Server_name varchar (50),
@a_errorText varchar(200)OUTPUT,
@a_return int OUTPUT
)
as
Declare
@v_error int
set @v_error = 0 -- Assume no Error
set @a_errorText = '' -- Assume no Error Text.
EXEC master.dbo.sp_addlinkedserver @server = @a_Server_name, @srvproduct=N'SQL Server'
EXEC master.dbo.sp_serveroption @server=@a_Server_name, @optname=N'collation compatible', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=@a_Server_name, @optname=N'data access', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=@a_Server_name, @optname=N'dist', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=@a_Server_name, @optname=N'pub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=@a_Server_name, @optname=N'rpc', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=@a_Server_name, @optname=N'rpc out', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=@a_Server_name, @optname=N'sub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=@a_Server_name, @optname=N'connect timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=@a_Server_name, @optname=N'collation name', @optvalue=null
EXEC master.dbo.sp_serveroption @server=@a_Server_name, @optname=N'lazy schema validation', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=@a_Server_name, @optname=N'query timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=@a_Server_name, @optname=N'use remote collation', @optvalue=N'true'
set @v_error = @@error
if @v_error = 0
begin
set @a_return = 0
end
else
set @a_return = @v_error
--SCRIPT END
CodeOn
-
keep in mind that to be able to create a linked server (from SQL2005) to SQL2000, you need to run an extra script at SQL2000 side.
KB ref at http://support.microsoft.com/default.aspx?scid=kb;en-us;906954
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
re: "need to run an extra script"
I haven't had to do this - perhaps its only required for 64 bit installations of SQL 2005 where linked to SQL 2000?
-
can you script and post your "create linked server" DDL (and the details) ?
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply