Linked Server not wanting to connect

Morning ALL.

I have a utility server that I am running SS2K5 SP2 w/ the latest patches.

It has numerous Linked Server to both SS2K and SS2K5 servera already in place and working great.

I scripted out (numerous times) a Link Server create statement for a SS2K5 server that is working great and then changed the server name in the script to reflect the new server name and executed it.

It DID created the linked server BUT when it finished up it generated the following message:

================ ERROR TEXT BEGIN ======================

TITLE: Microsoft SQL Server Management Studio

"The test connection to the linked server failed."

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "DC:AUS02DB21".
OLE DB provider "MSDASQL" for linked server "DC:AUS02DB21" returned message "[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (PreLoginHandshake()).".
OLE DB provider "MSDASQL" for linked server "DC:AUS02DB21" returned message "[Microsoft][ODBC SQL Server Driver][DBNETLIB]General network error. Check your network documentation.". (Microsoft SQL Server, Error: 7303)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=7303&LinkId=20476

BUTTONS:

OK

============= ERROR TEXT END ==============

Now when I try to open the Catalogs object under the newly created Linked Server, I get the following message each time I try to open it:

================ ERROR TEXT BEGIN ======================

TITLE: Microsoft SQL Server Management Studio

Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "DC:AUS02DB21". (Microsoft SQL Server, Error: 7303)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=7303&LinkId=20476

BUTTONS:

OK

============= ERROR TEXT END ==============

Here is the code that I used as a template (and which is from a SS2K5 server that is working fine)

=========== CODE BEGIN ========

/****** Object: LinkedServer [DC:AUS02DB19] Script Date: 09/11/2007 10:30:42 ******/

EXEC master.dbo.sp_addlinkedserver @.server = N'DC:AUS02DB19', @.srvproduct=N'OLE DB for ODBC', @.provider=N'MSDASQL', @.provstr=N'DRIVER={SQL Server};SERVER=AUS02DB19.DomainName.com;UID=user;PWD=password;'

/* For security reasons the linked server remote logins password is changed with ######## */

EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname=N'DC:AUS02DB19',@.useself=N'False',@.locallogin=NULL,@.rmtuser=N'SQL_',@.rmtpassword='########'

GO

EXEC master.dbo.sp_serveroption @.server=N'DC:AUS02DB19', @.optname=N'collation compatible', @.optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @.server=N'DC:AUS02DB19', @.optname=N'data access', @.optvalue=N'true'

GO

EXEC master.dbo.sp_serveroption @.server=N'DC:AUS02DB19', @.optname=N'dist', @.optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @.server=N'DC:AUS02DB19', @.optname=N'pub', @.optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @.server=N'DC:AUS02DB19', @.optname=N'rpc', @.optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @.server=N'DC:AUS02DB19', @.optname=N'rpc out', @.optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @.server=N'DC:AUS02DB19', @.optname=N'sub', @.optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @.server=N'DC:AUS02DB19', @.optname=N'connect timeout', @.optvalue=N'0'

GO

EXEC master.dbo.sp_serveroption @.server=N'DC:AUS02DB19', @.optname=N'collation name', @.optvalue=null

GO

EXEC master.dbo.sp_serveroption @.server=N'DC:AUS02DB19', @.optname=N'lazy schema validation', @.optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @.server=N'DC:AUS02DB19', @.optname=N'query timeout', @.optvalue=N'0'

GO

EXEC master.dbo.sp_serveroption @.server=N'DC:AUS02DB19', @.optname=N'use remote collation', @.optvalue=N'true'

=========== CODE END ==========

Here is what the code looks like when I replaced the name of DB19 to DB21 globally throughout the script:

=========== CODE BEGIN ========

/****** Object: LinkedServer [DC:AUS02DB21] Script Date: 09/11/2007 10:30:42 ******/

EXEC master.dbo.sp_addlinkedserver @.server = N'DC:AUS02DB21', @.srvproduct=N'OLE DB for ODBC', @.provider=N'MSDASQL', @.provstr=N'DRIVER={SQL Server};SERVER=AUS02DB21.DomainName.com;UID=user;PWD=password;'

/* For security reasons the linked server remote logins password is changed with ######## */

EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname=N'DC:AUS02DB21',@.useself=N'False',@.locallogin=NULL,@.rmtuser=N'SQL_',@.rmtpassword='########'

GO

EXEC master.dbo.sp_serveroption @.server=N'DC:AUS02DB21', @.optname=N'collation compatible', @.optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @.server=N'DC:AUS02DB21', @.optname=N'data access', @.optvalue=N'true'

GO

EXEC master.dbo.sp_serveroption @.server=N'DC:AUS02DB21', @.optname=N'dist', @.optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @.server=N'DC:AUS02DB21', @.optname=N'pub', @.optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @.server=N'DC:AUS02DB21', @.optname=N'rpc', @.optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @.server=N'DC:AUS02DB21', @.optname=N'rpc out', @.optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @.server=N'DC:AUS02DB21', @.optname=N'sub', @.optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @.server=N'DC:AUS02DB21', @.optname=N'connect timeout', @.optvalue=N'0'

GO

EXEC master.dbo.sp_serveroption @.server=N'DC:AUS02DB21', @.optname=N'collation name', @.optvalue=null

GO

EXEC master.dbo.sp_serveroption @.server=N'DC:AUS02DB21', @.optname=N'lazy schema validation', @.optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @.server=N'DC:AUS02DB21', @.optname=N'query timeout', @.optvalue=N'0'

GO

EXEC master.dbo.sp_serveroption @.server=N'DC:AUS02DB21', @.optname=N'use remote collation', @.optvalue=N'true'

=========== CODE END ==========

Now I have masked the real values in this post of the @.provstr string for obvious reasons and the real Linked Server object has all the correct parameters set.

@.provstr=N'DRIVER={SQL Server};SERVER=AUS02DB21.DomainName.com;UID=user;PWD=pass;'

SO ... what am I missing?

Thanks ALL

Is the remote server AUS02DB21 a 2005 server? Does it enable remote access?

Can you access the server from your utility server by SSMS?|||

Yes ... the instance I am creating the linked server to is a SQL Server 2005 SP2 instance.

It has remote access enabled.

I am also able, and have for quite some time, a registered instance in SSMS on the server I am creating the linked server on.

What is interesting is that DB19 and DB21 are identical servers in our farm. Same hardware and software.

I am now getting this error message when I try to view the Catalogs of the newly created linked server:

TITLE: Microsoft SQL Server Management Studio

Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

TCP Provider: The specified network name is no longer available.
(Microsoft SQL Server, Error: 64)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=64&LinkId=20476

BUTTONS:

OK

Any clue with this added error message?

Thanks for all the help!!

|||

I have found something rather interesting.

In the Windows Application event log I am seeing error about a connection coming from the server I have the linked server created on trying to access the DAC.

Here is the event:

Could not connect because the maximum number of '1' dedicated administrator connections already exists. Before a new connection can be made, the existing dedicated administrator connection must be dropped, either by logging off or ending the process. [CLIENT: 10.100.10.7]

So why in the heck is the DAC being accessed?|||

I got the same problem on SQL server 2005 SP2.

It creates the link but when I try to test it: "The test connection to the linked server failed."An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Cannot initialize the data source object of OLE DB provider "Sybase.ASEOLEDBProvider" for linked server "server_name" returned message "Invalid server name was specified.". (.Net SqlClient Data Provider)

I'm trying to establish a linked server between SQL server 2005 SP2 and a Sybase server (ASE)

Thanks

|||

Hey, I got it working:

I had to put a provider string:

Server Name=server_name,port_no

(server_name would be what ever your servername would be

port_no: 5000 for sybase ASE)

now I need to know of a way to replicate the server w/out having the replication agent on the server (since the admin of the ASe server doesn'T want to unable this option agrrr!!!)