Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Unable to access the Linked Server Expand / Collapse
Author
Message
Posted Tuesday, September 4, 2012 11:22 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, September 15, 2014 2:02 PM
Points: 121, Visits: 1,397
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
Posted Wednesday, September 5, 2012 1:01 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, January 31, 2014 2:56 AM
Points: 483, Visits: 256
Is the linked server using the default port 1433? Otherwise you need to specify the port: @server = N'PC068751\SQLEXPRESS,<port>'
Post #1354342
Posted Wednesday, September 5, 2012 2:09 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, September 15, 2014 2:02 PM
Points: 121, Visits: 1,397
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
Posted Wednesday, September 5, 2012 2:42 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Thursday, September 18, 2014 3:29 PM
Points: 4,324, Visits: 10,571
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.


--
Gianluca Sartori

Get your two-cent-answer quickly
spaghettidba.com
@spaghettidba
Post #1354381
Posted Wednesday, September 5, 2012 2:44 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Thursday, September 18, 2014 3:29 PM
Points: 4,324, Visits: 10,571
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.


--
Gianluca Sartori

Get your two-cent-answer quickly
spaghettidba.com
@spaghettidba
Post #1354382
Posted Wednesday, September 5, 2012 2:45 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Thursday, September 18, 2014 3:29 PM
Points: 4,324, Visits: 10,571
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.


--
Gianluca Sartori

Get your two-cent-answer quickly
spaghettidba.com
@spaghettidba
Post #1354383
Posted Wednesday, September 5, 2012 5:40 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, September 15, 2014 2:02 PM
Points: 121, Visits: 1,397
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
Posted Wednesday, September 5, 2012 6:16 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Thursday, September 18, 2014 3:29 PM
Points: 4,324, Visits: 10,571
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



--
Gianluca Sartori

Get your two-cent-answer quickly
spaghettidba.com
@spaghettidba
Post #1354465
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse