Counter of Festivals

Ashok Blog for SQL Learners and Beginners and Experts

Tuesday, 6 January 2015

Add Catalog to an Existing Linked Server/ Missing Database in Linked Server

I have an existing Linked Server with a few catalogs... how can I add another, existing database/catalog to this linked server?
For Example, my object explorer looks like this:
Linked Servers
  • Providers
  • DB4_LINK
    • Catalogs
      • System Catalogs
      • MyDatabase
      • MyOtherDatabase
How can I add yet another database that already exists to DB4_LINK?
EDIT: I'm trying to add an existing database to this linked server entry.
When u tried to access that db from linked server or missing db in linked server
see error below
Msg 7314, Level 16, State 1, Line 1
The OLE DB provider "SQLNCLI" for linked server "linkedservername" does not contain the table ""databasename"."dbo"."tablename"". The table either does not exist or the current user does not have permissions on that table.

You can do this by sending dynamic SQL via the linked server:
EXEC Linkedservername.master..sp_executesql N'CREATE DATABASE databasename;';
Of course this requires that you have the permissions to do so, and it's a simplistic command assuming that the default settings are fine - you may want to customize the CREATE DATABASE command.