Issue with Linked server without database name in query

• marks_262

  SSCommitted

  Points: 1716

  More actions

  October 20, 2014 at 7:58 am

  #291724

  I have two SQL Server 2008 servers that I create a linked server between (code below). As I have specified the table name in the linked server but when try to reference the linked server in a Select statement it returns an invalid schema error message (code and error below).

  Is it possible to create a linked server that allows a select statement to execute without the database name included in the four dot notation that does not use OpenQuery function?

  EXEC sp_addlinkedserver

  @server='MyLinkedServerAlias',

  @srvproduct='',

  @provider='SQLNCLI',

  @datasrc='RemoteSQLServer',

  @catalog='ADatabase'

  sp_addlinkedsrvlogin @rmtsrvname='MyLinkedServer',

  @locallogin = NULL , @useself = N'False',

  @rmtuser = N'AUser', @rmtpassword = N'APassword'

  -- WORKS

  select top 100 *

  from MyLinkedServer.ADatabase.dbo.MyTable

  -- DOES NOT WORK

  select top 100 *

  from MyLinkedServer...MyTable

  -- DOES NOT WORK

  select top 100 *

  from MyLinkedServer..dbo.MyTable

  Msg 7313, Level 16, State 1, Line 1

  An invalid schema or catalog was specified for the provider "SQLNCLI10" for linked server "MyLinkedServer".