September 4, 2012 at 11:22 pm
[font="Tahoma"]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..
[/font]
September 5, 2012 at 1:01 am
Is the linked server using the default port 1433? Otherwise you need to specify the port: @server = N'PC068751\SQLEXPRESS,<port>'
September 5, 2012 at 2:09 am
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
September 5, 2012 at 2:42 am
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
September 5, 2012 at 2:44 am
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
September 5, 2012 at 2:45 am
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
September 5, 2012 at 5:40 am
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..
September 5, 2012 at 6:16 am
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply