Linked Server in SQL Server?

Question

Is it possible to create database in linked server? If yes then how.

I appreciate your help. Thank You.

Answer 1

If your linked server allows it, then you can run sp_executesql remotely and by that means you can do absolutely anything on the linked server. Eg. create a database:

exec <linkedserver>.master.sys.sp_execute_sql N'create database foo';

Answer 2

See:

• MSDN: Linking Servers

You basically need to call the sp_addlinkedserver stored proc:

sp_addlinkedserver
     [ @server= ] 'server' [ , [ @srvproduct= ] 'product_name' ] 
     [ , [ @provider= ] 'provider_name' ]
     [ , [ @datasrc= ] 'data_source' ] 
     [ , [ @location= ] 'location' ] 
     [ , [ @provstr= ] 'provider_string' ] 
     [ , [ @catalog= ] 'catalog' ] 

Something like:

EXEC sp_addlinkedserver  @server='S1_instance1', @srvproduct='',
                         @provider='SQLNCLI', @datasrc='S1\instance1'

For details, see the MSDN docs - it's really pretty good!

Answer 3

if you want create link server in sql server you can go 2 way:

1- write query.

2- use with SQL Server Management Studio with open Object Explorer and expand Server Objects, right-click Linked Servers and then click New Linked Server.

I want to say, write query: if you write this query you will create link server:

EXEC sp_addlinkedserver @server = [The_server_address_you_want_have_it]
EXEC sp_addlinkedsrvlogin [ @rmtsrvname = ] 'rmtsrvname'   
     [ , [ @useself = ] { 'TRUE' | 'FALSE' | NULL } ]   
     [ , [ @locallogin = ] 'locallogin' ]   
     [ , [ @rmtuser = ] 'rmtuser' ]   
     [ , [ @rmtpassword = ] 'rmtpassword' ]

for example:

EXEC sp_addlinkedserver @server = "1.1.1.1"
EXEC sp_addlinkedsrvlogin '1.1.1.1'
                         ,'false'
                         ,NULL
                         ,'yes'
                         ,'123'