Setting Linked server for 2 different instance of sql 2005 on same box

• Rookee DBA

  SSCrazy

  Points: 2188

  More actions

  October 21, 2008 at 5:12 pm

  #127188

  Hi

  I have a sql 2005 server installed on my machine. I have 2 instance running on same sever. I need to put a pull data from one data base on sql 1 to database 2 on sql 2 by using select into. Please let me know if I can do the same if I can use a linked server for the same and also any good link for setting the saem.

  cheers

  siddarth

• sayfrend

  SSCommitted

  Points: 1733

  More actions

  October 21, 2008 at 7:17 pm

  #887904

  Very easy and best way to do this which involves no Scripts and no hassles.

  Create a DTS/SSIS package

  create a source connection to sql1 database

  create a destination connection to sql2 database.

  drop output from connection1 to connection2.

  Here is a sample video

  http://www.jumpstarttv.com/Media.aspx?vid=3

  register for free to watch this video.

  Thanks,

  Imran Mohammed.

• krayknot

  SSCertifiable

  Points: 7998

  More actions

  October 21, 2008 at 11:13 pm

  #887936

  sayfrend (10/21/2008)

  ---

  Very easy and best way to do this which involves no Scripts and no hassles.

  Create a DTS/SSIS package

  create a source connection to sql1 database

  create a destination connection to sql2 database.

  drop output from connection1 to connection2.

  Here is a sample video

  http://www.jumpstarttv.com/Media.aspx?vid=3

  register for free to watch this video.

  Thanks,

  Imran Mohammed.

  Imran is correct.

  but there is another easy approach. just create the link servers and prefix the link server name before the db name and use the same insert into query

  kshitij kumar
  kshitij@krayknot.com
  www.krayknot.com

• Atif

  Hall of Fame

  Points: 3578

  More actions

  October 22, 2008 at 2:38 am

  #887994

  You can use ad hoc query with open row set in following way

  SELECT *

  FROM OPENROWSET('SQLNCLI','dba\test';'sa';'test',

  'SELECT * from pubs..authors')

  Go

  where dba\test is instance sa is id and test is pwd

  DBDigger Microsoft Data Platform Consultancy.