Unable to access the Linked Server

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Unable to access the Linked Server

Unable to access the Linked Server

Rate Topic

Display Mode

Topic Options

Author

Message

MuraliKrishnan1980

Posted Tuesday, September 04, 2012 11:22 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 12:01 AM
Points: 111, Visits: 1,333

Hi Friends,
I have been trying to create a Linked Server from my machine for the last few days and I am not able to do so successfully. My Machine is having SQL Server 2008 R2 Dev Edition and the machine that i am trying to connect is SQL Server 2008 Express Edition.

I have enabled the Named Pipes and TCP/IP in the Express Edition Machine. Please find below the code that I am using to create the linked server.

EXEC master.dbo.sp_addlinkedserver @server = N'PC068751\SQLEXPRESS', @srvproduct=N'',@datasrc = N'PC068751\SQLEXPRESS',@provider='SQLNCLI'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'PC068751\SQLEXPRESS',@useself=N'False',@rmtuser='****',@rmtpassword='******'

I have masked the username and password for security purpose.

However I am getting an error while trying to test the connection or access the server using OPENROWSET. I have read numerous articles. However I am unable to resolve the issue. Your help would be appreciated..

Post #1354322

SQL Surfer '66

Posted Wednesday, September 05, 2012 1:01 AM

SSC Veteran

Group: General Forum Members
Last Login: Friday, February 22, 2013 2:56 AM
Points: 225, Visits: 178

Is the linked server using the default port 1433? Otherwise you need to specify the port: @server = N'PC068751\SQLEXPRESS,<port>'

Post #1354342

MuraliKrishnan1980

Posted Wednesday, September 05, 2012 2:09 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 12:01 AM
Points: 111, Visits: 1,333

SQL Surfer '66 (9/5/2012)

Is the linked server using the default port 1433? Otherwise you need to specify the port: @server = N'PC068751\SQLEXPRESS,<port>'

Do i need to specify the port number in the @Server or @datasrc

Post #1354373

spaghettidba

Posted Wednesday, September 05, 2012 2:42 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Thursday, May 16, 2013 8:29 AM
Points: 4,804, Visits: 8,067

First thing to check: from the SQL Server, can you connect to the SQLExpress instance (with SSMS or SQLCMD)?
If not, there's no need to involve the linked server in your troubleshooting.

Get your two-cent-answer quickly
The Spaghetti DBA

Post #1354381

spaghettidba

Posted Wednesday, September 05, 2012 2:44 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Thursday, May 16, 2013 8:29 AM
Points: 4,804, Visits: 8,067

SQL Surfer '66 (9/5/2012)

Is the linked server using the default port 1433? Otherwise you need to specify the port: @server = N'PC068751\SQLEXPRESS,<port>'

True only if the port is static, which is not by default.

Get your two-cent-answer quickly
The Spaghetti DBA

Post #1354382

spaghettidba

Posted Wednesday, September 05, 2012 2:45 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Thursday, May 16, 2013 8:29 AM
Points: 4,804, Visits: 8,067

MuraliKrishnan1980 (9/5/2012)

SQL Surfer '66 (9/5/2012)

Is the linked server using the default port 1433? Otherwise you need to specify the port: @server = N'PC068751\SQLEXPRESS,<port>'

Do i need to specify the port number in the @Server or @datasrc

You'd better use an alias in the configuration manager and keep the linked server simple.

Get your two-cent-answer quickly
The Spaghetti DBA

Post #1354383

MuraliKrishnan1980

Posted Wednesday, September 05, 2012 5:40 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 12:01 AM
Points: 111, Visits: 1,333

First thing to check: from the SQL Server, can you connect to the SQLExpress instance (with SSMS or SQLCMD)?
If not, there's no need to involve the linked server in your troubleshooting.

Hi,

I would go ahead and create an alias for the sql express instance. I enabled the remote connection, TCP/IP and Named Pipe in the Express machine and I am able to view the server name of the machine in the Netwerk Servers list while trying to connect to the Database Engine from SSMS. However, I am unable to connect to the same.

What could be the problem? Can someone provide me the steps to connect to an express instance from a Developer Instance..

Post #1354445

spaghettidba

Posted Wednesday, September 05, 2012 6:16 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Thursday, May 16, 2013 8:29 AM
Points: 4,804, Visits: 8,067

Some things to check:

1) Remote connections enabled and service restarted on the Express instance (according to your reply, this should be ok)
2) TCP/IP enabled with a static port on the Express instance
3) Native Client alias created on the Dev instance machine, pointing to the host/port of the Express instance
4) Network traffic enabled in your firewall

Get your two-cent-answer quickly
The Spaghetti DBA

Post #1354465

« Prev Topic | Next Topic »

Permissions