linked server ignoring catalog setting

• magennis.weate-846530

  SSC Rookie

  Points: 47

  More actions

  October 13, 2008 at 5:40 pm

  #199361

  Hi,

  I have trying to create a link between two SQL 2005 servers. I want the SQL to read:

  select * from LINKEDSERVER...TableName

  in other words, I do not want to specify the database name in queries - I want this to come from the "catalog" setting when setting up the linked server (this makes deployment easier as the source database names may change).

  But when I don't specify the database name in the query, I get the error:

  Msg 7313, Level 16, State 1, Line 1

  An invalid schema or catalog was specified for the provider "SQLNCLI" for linked server "LINKEDSERVER".

  Supplying the database name in the query works OK.

  I have also tried using a logon for the linked connection which defaults to the desired database - resulting in the same error.

  I know I can create an SQL string at “run time” and include the correct database name but I would prefer to keep it simple.

  Am I missing something?

  Thanks

  Magennis

• Megistal

  SSCrazy Eights

  Points: 8787

  More actions

  October 14, 2008 at 8:38 am

  #884671

  I believe you can use the stored procedure sp_serveroption to configure your linked server collation setting.

  Check the options: "Use Remote Collation" and or "Collation Name"