February 8, 2011 at 1:47 am
Hello,
I have some problems when creating Linked server.
we are using SQL 2005 developer Edtion sce pack 3.
a server is installed in WINDOWS xp sce pack3
another server is in WINDOWS 2003.
I tried to create with sql server managment studio a linked servers.
the linked server that I want to connect is : "SRV-001"
Remote authorization are permitted, ping is correct.
I always got this error :
TITLE: Microsoft SQL Server Management Studio
------------------------------
"The linked server has been updated but failed a connection test. Do you want to edit the linked server properties?"
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Named Pipes Provider: Could not open a connection to SQL Server [53].
OLE DB provider "SQLNCLI" for linked server "[SRV-001]" returned message "Login timeout expired".
OLE DB provider "SQLNCLI" for linked server "[SRV-001]" returned message "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.". (Microsoft SQL Server, Error: 53)
questions :
Could we have in the server-name some characters like "-\"
Could we have server name and instance like this : "SRV-001\INSTANCE_1"
Do we need to put servername between brackets ? [SRV-001\INSTANCE_1]I say yes, but....
even with brackets, I always got errors.
-------------------------------------------------------------------------------------------
exemple of script that we used into stored proc :
IF EXISTS (SELECT * FROM master.sys.servers WHERE name = @a_destination_servername )
BEGIN
SET @query_string = "EXECUTE [dbo].[Spf_DropLinkedServer] '" + @a_destination_servername + "';";
EXECUTE dbo.sp_executesql @query_string, N'@a_destination_servername sysname', @a_destination_servername;
END
SET @return_value = 1;
SET @query_string = "EXECUTE [dbo].[sp_addlinkedserver] '" + @a_destination_servername + "';";
EXECUTE dbo.sp_executesql @query_string, N'@a_destination_servername sysname', @a_destination_servername;
--SET @a_destination_servername = QUOTENAME(@a_destination_servername);
SET @query_string = "EXECUTE [dbo].[sp_addlinkedsrvlogin] '" + @a_destination_servername + "', false, '" + @a_source_usrid + "', '"
+ @a_destination_usrid + "', '" + @a_destination_password + "';";
EXECUTE dbo.sp_executesql @query_string, N'@a_destination_servername sysname, @a_source_usrid sysname, @a_destination_usrid sysname,
@a_destination_password NVARCHAR(32)', @a_destination_servername, @a_source_usrid, @a_destination_usrid,
@a_destination_password;
-------------------------------------------------------------------------------------------
answer will be more than welcome.
Thank you in advance for your help.
regards.
February 8, 2011 at 2:01 am
Assuming that this linked server is connecting to sql server and thats what you have selected in 'server type' option.
The other thing would like to check the security tab is configured with 'be made using the login's.......'.
If the security ocnfiguration is different then you have to make sure that configured account have enough permission on remote server.
yes we can create linked server with special character but need to be within [ ].
----------
Ashish
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply