Linked Server - server name same as instance name

I am trying to configure a linked server in SQL 2005 to point to a server that has the same name as the SQL 2005 instance name. In other words, my computer name and SQL Server 2005 instance name are both EMUNDORF. In the "New Linked Server" dialog, I set the following:

Linked Server: EMUNDORF

Provider: SQL Native Client

Product Name: EMUNDORF

Data Source: EMUNDORF\EMUNDORF

This does not work. If I configure a Linked Server to a server with a named instance that is different than the server name, it works. The following is the error I get:

OLE DB provider "SQLNCLI" for linked server "EMUNDORF" returned message "Login timeout expired".

OLE DB provider "SQLNCLI" for linked server "EMUNDORF" 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.".

Msg 50000, Level 16, State 2, Line 10

Unable to connect to server. This operation will be tried later!

Can someone tell me what I am doing wrong?

The error is the same as if I try to connect to a server that is no longer running. I double checked all my settings with a server that has a named instance, and can link to other servers, but I can see no differences. The other strange thing is that my server can link to other servers, but no one is able to connect to mine. I know it has to be a setting somewhere...but where?|||

Did you enable remote connections for the server instance (It is disabled by default). To do that -

Go to Configuration Tools -> SQL Server Surface Area Configuration

Select Surface Area Configuration for Services and Connections

You should be able to see your server instance in the available components, Select Database Engine -> Remote Connections and then click on Local and Remote Connections with the Appropriate protocol. You need to restart the service once you save the settings.

Hope that helps,

Kuntal

|||

I would expect your product name to be NULL since you are using SQL Server.

I have success with these:

sp_addlinkedserver @.server = N'yourLnkdSrvName', @.srvproduct=N'',@.provider='SQLNCLI', @.datasrc=N'yourserver/instance' ;

Linked Server - server name same as instance name

I am trying to configure a linked server in SQL 2005 to point to a server that has the same name as the SQL 2005 instance name. In other words, my computer name and SQL Server 2005 instance name are both EMUNDORF. In the "New Linked Server" dialog, I set the following:

Linked Server: EMUNDORF

Provider: SQL Native Client

Product Name: EMUNDORF

Data Source: EMUNDORF\EMUNDORF

This does not work. If I configure a Linked Server to a server with a named instance that is different than the server name, it works. The following is the error I get:

OLE DB provider "SQLNCLI" for linked server "EMUNDORF" returned message "Login timeout expired".

OLE DB provider "SQLNCLI" for linked server "EMUNDORF" 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.".

Msg 50000, Level 16, State 2, Line 10

Unable to connect to server. This operation will be tried later!

Can someone tell me what I am doing wrong?

The error is the same as if I try to connect to a server that is no longer running. I double checked all my settings with a server that has a named instance, and can link to other servers, but I can see no differences. The other strange thing is that my server can link to other servers, but no one is able to connect to mine. I know it has to be a setting somewhere...but where?|||

Did you enable remote connections for the server instance (It is disabled by default). To do that -

Go to Configuration Tools -> SQL Server Surface Area Configuration

Select Surface Area Configuration for Services and Connections

You should be able to see your server instance in the available components, Select Database Engine -> Remote Connections and then click on Local and Remote Connections with the Appropriate protocol. You need to restart the service once you save the settings.

Hope that helps,

Kuntal

|||

I would expect your product name to be NULL since you are using SQL Server.

I have success with these:

sp_addlinkedserver @.server = N'yourLnkdSrvName', @.srvproduct=N'',@.provider='SQLNCLI', @.datasrc=N'yourserver/instance' ;

Linked Server - server name same as instance name

I am trying to configure a linked server in SQL 2005 to point to a server that has the same name as the SQL 2005 instance name. In other words, my computer name and SQL Server 2005 instance name are both EMUNDORF. In the "New Linked Server" dialog, I set the following:

Linked Server: EMUNDORF

Provider: SQL Native Client

Product Name: EMUNDORF

Data Source: EMUNDORF\EMUNDORF

This does not work. If I configure a Linked Server to a server with a named instance that is different than the server name, it works. The following is the error I get:

OLE DB provider "SQLNCLI" for linked server "EMUNDORF" returned message "Login timeout expired".

OLE DB provider "SQLNCLI" for linked server "EMUNDORF" 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.".

Msg 50000, Level 16, State 2, Line 10

Unable to connect to server. This operation will be tried later!

Can someone tell me what I am doing wrong?

The error is the same as if I try to connect to a server that is no longer running. I double checked all my settings with a server that has a named instance, and can link to other servers, but I can see no differences. The other strange thing is that my server can link to other servers, but no one is able to connect to mine. I know it has to be a setting somewhere...but where?|||

Did you enable remote connections for the server instance (It is disabled by default). To do that -

Go to Configuration Tools -> SQL Server Surface Area Configuration

Select Surface Area Configuration for Services and Connections

You should be able to see your server instance in the available components, Select Database Engine -> Remote Connections and then click on Local and Remote Connections with the Appropriate protocol. You need to restart the service once you save the settings.

Hope that helps,

Kuntal

|||

I would expect your product name to be NULL since you are using SQL Server.

I have success with these:

sp_addlinkedserver @.server = N'yourLnkdSrvName', @.srvproduct=N'',@.provider='SQLNCLI', @.datasrc=N'yourserver/instance' ;

Linked server

Can anyone point me where can I find a good architectural document on
how the Linked server works in SQL Server. I am not asking about how to
use Linked server, I am interested in know how it is defined and
architected.
Our project needs a data transfer from SQL server database to the
Oracle database, I am trying to find out whether can we use DTS or
create a linked server and execute a sql query to copy data.

Thanks in Advance.
AllenI'm not really sure what you're asking for - can you be more specific?
Does the information in Books Online ("Configuring Linked Servers" has
a diagram of the architecure) not give you enough information? Both DTS
and linked servers are possible ways to get data from Oracle, but
they're very different solutions, so if you can give more details about
what you need to know and what you're trying to do, someone may be able
to point you in the right direction.

Simon|||Thanks Simon,
We have around 20 tables to be transfered from sql server to Oracle
database. The current plan is use lined server and write a stored proc
in Oracle and grab the data using simple select into statement. This is
will happen every night and couple of tables have more than million
rows.

The question is whether linked server is reliable to do these kind of
data load use DTS. I have a very limitted knowledge in SQL Server. I am
trying to find a book/document which talks about the internal
architecture of linked server and dts. Preferably not by Microsoft.

Thanks
Allen

Simon Hayes wrote:
> I'm not really sure what you're asking for - can you be more specific?
> Does the information in Books Online ("Configuring Linked Servers" has
> a diagram of the architecure) not give you enough information? Both DTS
> and linked servers are possible ways to get data from Oracle, but
> they're very different solutions, so if you can give more details about
> what you need to know and what you're trying to do, someone may be able
> to point you in the right direction.
> Simon|||arichie (arichie@.gmail.com) writes:
> We have around 20 tables to be transfered from sql server to Oracle
> database. The current plan is use lined server and write a stored proc
> in Oracle and grab the data using simple select into statement. This is
> will happen every night and couple of tables have more than million
> rows.
> The question is whether linked server is reliable to do these kind of
> data load use DTS. I have a very limitted knowledge in SQL Server. I am
> trying to find a book/document which talks about the internal
> architecture of linked server and dts. Preferably not by Microsoft.

I would suspect that DTS is a better solution for this than linked
tables, if nothing else for performance. Certainly, this is precisly
the scenario for which DTS is intended. (But I have never used DTS myself.)

In the linked-server scenario, SQL Server is basically a client to the
other server that uses OLE DB to connect to the remote server. (Tip is
to use Oracle's own OLE DB Provider. Microsoft has not updated their
MSDAORA for recent Oracle releass.) Internally, SQL Server transforms
the access to client API calls. I don't think there is much literature
that discusses linked servers as such.

For DTS, please have a look at http://www.sqldts.com and
visit the group microsof.public.sqlserver.dts.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||allen

so you dont trust microsoft; but you trust them for your database?

go and play with your unix and be sure to check out www.tpc.org, sql
server has taken over the (affordable) world kid

and be sure to check out www.olapreport.com to see exactly how
irrelevant Oracle is to the world these days.. MS won the OLAP battle;
it's obvious to see