Creating Linked Server Object to the Same Instance in SQL Server

linked-serversql server 2014sql-server-express

I'm trying to create a Linked Server Object back to the same SQL Express 2014 server instance (see below for the context), but I'm hitting the following error…

Cannot initialize the data source object of OLE DB provider "SQLNCLI11" for linked server "NewServer".

OLE DB provider "SQLNCLI11" for linked server "NewServer" returned message "Invalid connection string attribute". (Microsoft SQL Server, Error: 7303)

I've used the following configuration…

  • Linked Server: NewServer
  • Server Type: Other data source
  • Provider: Microsoft OLE DB Provider for SQL Server
  • Product name: {empty}
  • Data source: LOCALHOST\SQLEXPRESS (have also tried actual server name)
  • Provider string: SQLNCLI
  • Security, Login: Using the login's current security context (have also tried using this security context and providing sa/password)

Why am I getting this error, and how do I fix it?

Is it because I'm trying to create a linked server object back to itself?


For context, the reason I'm doing this is due to a client who's UAT and Production environments have a linked server with which we need to interact.

As a result I need to create stored procedures on my local development machine that use the [NewServer].[Database].[dbo].[Table] name… so that the sproc can be stored in the repo and easily deployed via comparison tools. But it won't let me create/update the sprocs without the valid data source.

I thought the simple solution would be to create the [Database].[dbo].[Table] on my local SQL Express 2014 instance and add a linked server back to itself using the [NewServer] name.

Is the only option to create a new SQL instance?

Best Answer

I've already worked it out...

The problem was incorrectly setting the provider string to SQLNCLI.

When I removed it (leaving it empty) it connected immediately...

  • Linked Server: NewServer
  • Server Type: Other data source
  • Provider: Microsoft OLE DB Provider for SQL Server
  • Product name: {empty}
  • Data source: LOCALHOST\SQLEXPRESS
  • Provider string: {empty} (UPDATED)
  • Security, Login: Using the login's current security context