Linked Server Problems with all providers

Im having problems with linked servers on one particular computer and am out
of ideas. The box is an XP SP2, SQL server 2000 SP3. When i create a linke
d
server of any type, clicking on "tables" or "views" in the enterprise manage
r
list, gives the following error:
Error 7399: OLE DB provider 'SQLOLEDB' reported an error.
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IDBinitialize::Initialize
returned 0x80004005: ].
This particular LSN is pointing to another SQL server machine. Regardless
of what provider i choose, i get the same error (the error message changes
the provider based on the provider of the LSN).
I am an administrator on both boxes, have disabled virus protection, the
windows firewall on the XP box is disabled (by a group policy on the domain)
,
and i have enabled inbound and outbound traffic in the dcomcnfg.exe.
LSN's created on other computers work fine. Any ideas?You can get this error with timeout issues. Refer to the
following:
Error message when you execute a linked server query in SQL
Server: "Timeout Expired"
http://support.microsoft.com/?id=314530
You can also double check the security mappings for the
linked servers.
You will also want to check the connectivity - try pinging
the other servers from the box you are having problems with.
Ping by server name and IP.
-Sue
On Wed, 15 Mar 2006 06:56:28 -0800, John Kurtis <John
Kurtis@.discussions.microsoft.com> wrote:

>Im having problems with linked servers on one particular computer and am ou
t
>of ideas. The box is an XP SP2, SQL server 2000 SP3. When i create a link
ed
>server of any type, clicking on "tables" or "views" in the enterprise manag
er
>list, gives the following error:
>Error 7399: OLE DB provider 'SQLOLEDB' reported an error.
>OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IDBinitialize::Initializ
e
>returned 0x80004005: ].
>This particular LSN is pointing to another SQL server machine. Regardless
>of what provider i choose, i get the same error (the error message changes
>the provider based on the provider of the LSN).
>I am an administrator on both boxes, have disabled virus protection, the
>windows firewall on the XP box is disabled (by a group policy on the domain
),
>and i have enabled inbound and outbound traffic in the dcomcnfg.exe.
>LSN's created on other computers work fine. Any ideas?|||Thanks for the reply. However im 99% sure that its not a timeout issue. I
get the error immediately after i click on "tables" or "views" in enterprise
manager.
Pings from the box that is having problems come back fine, both with IP as
well as name.

linked server problem

I am running trigger with update on a linked server in SQL 2005 SP2. It is
running very smooth and when I turn on the firewall, the trigger action is
giving error and failed to take action. Is this a Big NO between firewall
and Linked server? Is there a way to solve this problem? Thanks.You simply need to ensure the appropriate ports are open on the firewall, at
least between the two servers of concern. A simple packet capture while the
trigger is firing should inform you as to what needs to be opened up. At a
minimum it should be TCP 1433 and probably 1434. I don't know if other's
are involved in remote proc executions or not.
TheSQLGuru
President
Indicium Resources, Inc.
" 00ScarlettJohnson" <EE@.yahoo.com> wrote in message
news:uVGoFOxgHHA.588@.TK2MSFTNGP06.phx.gbl...
>I am running trigger with update on a linked server in SQL 2005 SP2. It is
>running very smooth and when I turn on the firewall, the trigger action is
>giving error and failed to take action. Is this a Big NO between firewall
>and Linked server? Is there a way to solve this problem? Thanks.
>|||What is the mechanism to capture the packet? Thanks.
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:ec5p9z1gHHA.1220@.TK2MSFTNGP03.phx.gbl...
> You simply need to ensure the appropriate ports are open on the firewall,
> at least between the two servers of concern. A simple packet capture
> while the trigger is firing should inform you as to what needs to be
> opened up. At a minimum it should be TCP 1433 and probably 1434. I don't
> know if other's are involved in remote proc executions or not.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> " 00ScarlettJohnson" <EE@.yahoo.com> wrote in message
> news:uVGoFOxgHHA.588@.TK2MSFTNGP06.phx.gbl...
>

linked server problem

I am running trigger with update on a linked server in SQL 2005 SP2. It is
running very smooth and when I turn on the firewall, the trigger action is
giving error and failed to take action. Is this a Big NO between firewall
and Linked server? Is there a way to solve this problem? Thanks.
You simply need to ensure the appropriate ports are open on the firewall, at
least between the two servers of concern. A simple packet capture while the
trigger is firing should inform you as to what needs to be opened up. At a
minimum it should be TCP 1433 and probably 1434. I don't know if other's
are involved in remote proc executions or not.
TheSQLGuru
President
Indicium Resources, Inc.
" 00ScarlettJohnson" <EE@.yahoo.com> wrote in message
news:uVGoFOxgHHA.588@.TK2MSFTNGP06.phx.gbl...
>I am running trigger with update on a linked server in SQL 2005 SP2. It is
>running very smooth and when I turn on the firewall, the trigger action is
>giving error and failed to take action. Is this a Big NO between firewall
>and Linked server? Is there a way to solve this problem? Thanks.
>
|||What is the mechanism to capture the packet? Thanks.
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:ec5p9z1gHHA.1220@.TK2MSFTNGP03.phx.gbl...
> You simply need to ensure the appropriate ports are open on the firewall,
> at least between the two servers of concern. A simple packet capture
> while the trigger is firing should inform you as to what needs to be
> opened up. At a minimum it should be TCP 1433 and probably 1434. I don't
> know if other's are involved in remote proc executions or not.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> " 00ScarlettJohnson" <EE@.yahoo.com> wrote in message
> news:uVGoFOxgHHA.588@.TK2MSFTNGP06.phx.gbl...
>

linked server problem

I am running trigger with update on a linked server in SQL 2005 SP2. It is
running very smooth and when I turn on the firewall, the trigger action is
giving error and failed to take action. Is this a Big NO between firewall
and Linked server? Is there a way to solve this problem? Thanks.You simply need to ensure the appropriate ports are open on the firewall, at
least between the two servers of concern. A simple packet capture while the
trigger is firing should inform you as to what needs to be opened up. At a
minimum it should be TCP 1433 and probably 1434. I don't know if other's
are involved in remote proc executions or not.
--
TheSQLGuru
President
Indicium Resources, Inc.
" 00ScarlettJohnson" <EE@.yahoo.com> wrote in message
news:uVGoFOxgHHA.588@.TK2MSFTNGP06.phx.gbl...
>I am running trigger with update on a linked server in SQL 2005 SP2. It is
>running very smooth and when I turn on the firewall, the trigger action is
>giving error and failed to take action. Is this a Big NO between firewall
>and Linked server? Is there a way to solve this problem? Thanks.
>|||What is the mechanism to capture the packet? Thanks.
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:ec5p9z1gHHA.1220@.TK2MSFTNGP03.phx.gbl...
> You simply need to ensure the appropriate ports are open on the firewall,
> at least between the two servers of concern. A simple packet capture
> while the trigger is firing should inform you as to what needs to be
> opened up. At a minimum it should be TCP 1433 and probably 1434. I don't
> know if other's are involved in remote proc executions or not.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> " 00ScarlettJohnson" <EE@.yahoo.com> wrote in message
> news:uVGoFOxgHHA.588@.TK2MSFTNGP06.phx.gbl...
>>I am running trigger with update on a linked server in SQL 2005 SP2. It is
>>running very smooth and when I turn on the firewall, the trigger action is
>>giving error and failed to take action. Is this a Big NO between firewall
>>and Linked server? Is there a way to solve this problem? Thanks.
>

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!!!)

sql

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!!!)

Linked server issues for named instances.

Howdy all. Ive configured linked servers plenty of times, but now Im
wondering if Ive ever done it on a named instance. I've got an SQL 2000 SP2
box that I can link up to default instances with no issue. But when I try to
link it to a named instance (various editions and service packs) it blows up
with:
"SQL Server does not exist or access is denied. "
I know it's not a login/ permissions issue as I've went so far as to use SA
on both the "source" and "destination" boxes. BOL says I can happen, so Im
not understanding what the problem is?
TIA, ChrisRHello,
Can you aricreate a server alias for the named instance using Client network
utility and then use this alias to create a linked server.
Thanks
Hari
"ChrisR" <ChrisR@.NoEmail.com> wrote in message
news:%237IBRMvIHHA.1248@.TK2MSFTNGP02.phx.gbl...
> Howdy all. Ive configured linked servers plenty of times, but now Im
> wondering if Ive ever done it on a named instance. I've got an SQL 2000
> SP2 box that I can link up to default instances with no issue. But when I
> try to link it to a named instance (various editions and service packs) it
> blows up with:
> "SQL Server does not exist or access is denied. "
> I know it's not a login/ permissions issue as I've went so far as to use
> SA on both the "source" and "destination" boxes. BOL says I can happen,
> so Im not understanding what the problem is?
> TIA, ChrisR
>|||A couple items to check:
1. Check the MDAC version. Updating to the latest might help resolve the
issue.
2. Check your firewall. The named instances are assigned dynamic port for
communication and you firewall might be dropping the packets. Normally a
good idea to assign a static port for the named instance and to configure
your firewall.
3. It is worth updating SQL Server to the latest SP.
Here are some useful links to troubleshoot the issue:
http://support.microsoft.com/kb/328306/
http://support.microsoft.com/kb/287932
http://support.microsoft.com/kb/827422/
Regards,
Plamen Ratchev
http://www.SQLStudio.com
"ChrisR" <ChrisR@.NoEmail.com> wrote in message
news:%237IBRMvIHHA.1248@.TK2MSFTNGP02.phx.gbl...
> Howdy all. Ive configured linked servers plenty of times, but now Im
> wondering if Ive ever done it on a named instance. I've got an SQL 2000
> SP2 box that I can link up to default instances with no issue. But when I
> try to link it to a named instance (various editions and service packs) it
> blows up with:
> "SQL Server does not exist or access is denied. "
> I know it's not a login/ permissions issue as I've went so far as to use
> SA on both the "source" and "destination" boxes. BOL says I can happen,
> so Im not understanding what the problem is?
> TIA, ChrisR
>

Linked server error in SSMS

Hi all
I have installed Sql Server 2005 + Sp2 developer edition. Once trying to add
a linked server in SSMS Server Objects => Linked Servers => New Linked
Server I'm getting the error immediately saying " Cannot show requested
dialog. Cannot find table 0. (System.Data)". Additionally Providers tree
under Linked servers is also empty. However I could add a linked server
successfully using t-sql sp_addlinkedserver. I got the feeling that some
files are corrupt on my PC, but I don't know which. I tried to uninstall and
put the server back again and it did not help either. I installed Sql Server
from the same disk onto the other machine and it works fine. Has anybody got
an idea how to resolve my problem?
Many thanks
DariusDarius
Hmm, I tried succefully create a linked server throu SSMS to SQL Server
2000. Pehaps something got corrupted during the installation of SSMS...
"Darius" <nospam@.mail.com> wrote in message
news:uU8j%23TlgHHA.4064@.TK2MSFTNGP02.phx.gbl...
> Hi all
> I have installed Sql Server 2005 + Sp2 developer edition. Once trying to
> add a linked server in SSMS Server Objects => Linked Servers => New Linked
> Server I'm getting the error immediately saying " Cannot show requested
> dialog. Cannot find table 0. (System.Data)". Additionally Providers tree
> under Linked servers is also empty. However I could add a linked server
> successfully using t-sql sp_addlinkedserver. I got the feeling that some
> files are corrupt on my PC, but I don't know which. I tried to uninstall
> and put the server back again and it did not help either. I installed Sql
> Server from the same disk onto the other machine and it works fine. Has
> anybody got an idea how to resolve my problem?
> Many thanks
> Darius
>
>|||Hi Uri
I believe something is corrupted on my PC (drivers, etc.), I successfully
can create linked server on the other machines. The error appears straight
after I click on "New Linked Server", it does not even show any dialog box..
I reinstalled the Sql Server from the scratch but there were no difference.
Just don't know where to start looking at...
Darius
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uHjRvelgHHA.4260@.TK2MSFTNGP03.phx.gbl...
> Darius
> Hmm, I tried succefully create a linked server throu SSMS to SQL Server
> 2000. Pehaps something got corrupted during the installation of SSMS...
>
> "Darius" <nospam@.mail.com> wrote in message
> news:uU8j%23TlgHHA.4064@.TK2MSFTNGP02.phx.gbl...
>> Hi all
>> I have installed Sql Server 2005 + Sp2 developer edition. Once trying to
>> add a linked server in SSMS Server Objects => Linked Servers => New
>> Linked Server I'm getting the error immediately saying " Cannot show
>> requested dialog. Cannot find table 0. (System.Data)". Additionally
>> Providers tree under Linked servers is also empty. However I could add a
>> linked server successfully using t-sql sp_addlinkedserver. I got the
>> feeling that some files are corrupt on my PC, but I don't know which. I
>> tried to uninstall and put the server back again and it did not help
>> either. I installed Sql Server from the same disk onto the other machine
>> and it works fine. Has anybody got an idea how to resolve my problem?
>> Many thanks
>> Darius
>>
>>
>|||Hi
Have you tried to search on "Google" for this error message?
"Darius" <nospam@.mail.com> wrote in message
news:eK2yKolgHHA.3852@.TK2MSFTNGP04.phx.gbl...
> Hi Uri
> I believe something is corrupted on my PC (drivers, etc.), I successfully
> can create linked server on the other machines. The error appears straight
> after I click on "New Linked Server", it does not even show any dialog
> box.. I reinstalled the Sql Server from the scratch but there were no
> difference. Just don't know where to start looking at...
> Darius
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uHjRvelgHHA.4260@.TK2MSFTNGP03.phx.gbl...
>> Darius
>> Hmm, I tried succefully create a linked server throu SSMS to SQL Server
>> 2000. Pehaps something got corrupted during the installation of
>> SSMS...
>>
>> "Darius" <nospam@.mail.com> wrote in message
>> news:uU8j%23TlgHHA.4064@.TK2MSFTNGP02.phx.gbl...
>> Hi all
>> I have installed Sql Server 2005 + Sp2 developer edition. Once trying to
>> add a linked server in SSMS Server Objects => Linked Servers => New
>> Linked Server I'm getting the error immediately saying " Cannot show
>> requested dialog. Cannot find table 0. (System.Data)". Additionally
>> Providers tree under Linked servers is also empty. However I could add a
>> linked server successfully using t-sql sp_addlinkedserver. I got the
>> feeling that some files are corrupt on my PC, but I don't know which. I
>> tried to uninstall and put the server back again and it did not help
>> either. I installed Sql Server from the same disk onto the other machine
>> and it works fine. Has anybody got an idea how to resolve my problem?
>> Many thanks
>> Darius
>>
>>
>>
>|||yep, this is the first thing I do before posting to newsgroups. Anyway, will
post the solution when/if I manage to solve that problem.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uxr7xplgHHA.2640@.TK2MSFTNGP06.phx.gbl...
> Hi
> Have you tried to search on "Google" for this error message?
>
> "Darius" <nospam@.mail.com> wrote in message
> news:eK2yKolgHHA.3852@.TK2MSFTNGP04.phx.gbl...
>> Hi Uri
>> I believe something is corrupted on my PC (drivers, etc.), I successfully
>> can create linked server on the other machines. The error appears
>> straight after I click on "New Linked Server", it does not even show any
>> dialog box.. I reinstalled the Sql Server from the scratch but there were
>> no difference. Just don't know where to start looking at...
>> Darius
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:uHjRvelgHHA.4260@.TK2MSFTNGP03.phx.gbl...
>> Darius
>> Hmm, I tried succefully create a linked server throu SSMS to SQL Server
>> 2000. Pehaps something got corrupted during the installation of
>> SSMS...
>>
>> "Darius" <nospam@.mail.com> wrote in message
>> news:uU8j%23TlgHHA.4064@.TK2MSFTNGP02.phx.gbl...
>> Hi all
>> I have installed Sql Server 2005 + Sp2 developer edition. Once trying
>> to add a linked server in SSMS Server Objects => Linked Servers => New
>> Linked Server I'm getting the error immediately saying " Cannot show
>> requested dialog. Cannot find table 0. (System.Data)". Additionally
>> Providers tree under Linked servers is also empty. However I could add
>> a linked server successfully using t-sql sp_addlinkedserver. I got the
>> feeling that some files are corrupt on my PC, but I don't know which. I
>> tried to uninstall and put the server back again and it did not help
>> either. I installed Sql Server from the same disk onto the other
>> machine and it works fine. Has anybody got an idea how to resolve my
>> problem?
>> Many thanks
>> Darius
>>
>>
>>
>>
>

Linked server error in SSMS

Hi all
I have installed Sql Server 2005 + Sp2 developer edition. Once trying to add
a linked server in SSMS Server Objects => Linked Servers => New Linked
Server I'm getting the error immediately saying " Cannot show requested
dialog. Cannot find table 0. (System.Data)". Additionally Providers tree
under Linked servers is also empty. However I could add a linked server
successfully using t-sql sp_addlinkedserver. I got the feeling that some
files are corrupt on my PC, but I don't know which. I tried to uninstall and
put the server back again and it did not help either. I installed Sql Server
from the same disk onto the other machine and it works fine. Has anybody got
an idea how to resolve my problem?
Many thanks
DariusDarius
Hmm, I tried succefully create a linked server throu SSMS to SQL Server
2000. Pehaps something got corrupted during the installation of SSMS...
"Darius" <nospam@.mail.com> wrote in message
news:uU8j%23TlgHHA.4064@.TK2MSFTNGP02.phx.gbl...
> Hi all
> I have installed Sql Server 2005 + Sp2 developer edition. Once trying to
> add a linked server in SSMS Server Objects => Linked Servers => New Linked
> Server I'm getting the error immediately saying " Cannot show requested
> dialog. Cannot find table 0. (System.Data)". Additionally Providers tree
> under Linked servers is also empty. However I could add a linked server
> successfully using t-sql sp_addlinkedserver. I got the feeling that some
> files are corrupt on my PC, but I don't know which. I tried to uninstall
> and put the server back again and it did not help either. I installed Sql
> Server from the same disk onto the other machine and it works fine. Has
> anybody got an idea how to resolve my problem?
> Many thanks
> Darius
>
>|||Hi Uri
I believe something is corrupted on my PC (drivers, etc.), I successfully
can create linked server on the other machines. The error appears straight
after I click on "New Linked Server", it does not even show any dialog box..
I reinstalled the Sql Server from the scratch but there were no difference.
Just don't know where to start looking at...
Darius
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uHjRvelgHHA.4260@.TK2MSFTNGP03.phx.gbl...
> Darius
> Hmm, I tried succefully create a linked server throu SSMS to SQL Server
> 2000. Pehaps something got corrupted during the installation of SSMS...
>
> "Darius" <nospam@.mail.com> wrote in message
> news:uU8j%23TlgHHA.4064@.TK2MSFTNGP02.phx.gbl...
>|||Hi
Have you tried to search on "Google" for this error message?
"Darius" <nospam@.mail.com> wrote in message
news:eK2yKolgHHA.3852@.TK2MSFTNGP04.phx.gbl...
> Hi Uri
> I believe something is corrupted on my PC (drivers, etc.), I successfully
> can create linked server on the other machines. The error appears straight
> after I click on "New Linked Server", it does not even show any dialog
> box.. I reinstalled the Sql Server from the scratch but there were no
> difference. Just don't know where to start looking at...
> Darius
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uHjRvelgHHA.4260@.TK2MSFTNGP03.phx.gbl...
>|||yep, this is the first thing I do before posting to newsgroups. Anyway, will
post the solution when/if I manage to solve that problem.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uxr7xplgHHA.2640@.TK2MSFTNGP06.phx.gbl...
> Hi
> Have you tried to search on "Google" for this error message?
>
> "Darius" <nospam@.mail.com> wrote in message
> news:eK2yKolgHHA.3852@.TK2MSFTNGP04.phx.gbl...
>