How to use openrowset query

• sql_learner29

  SSCrazy Eights

  Points: 9107

  More actions

  January 16, 2009 at 10:18 am

  #128048

  Hi all,,

  I want to know how can i use openrowset to select data from another server...

  I gave like this:-

  select * from

  OPENROWSET( 'server_name',

  'Trusted_Connection=yes;Data Source=clairehsu;Initial Catalog=MSSQLTIPS',

  'select * from MSSQLTIPS.dbo.dept' )

  It gave me an error::

  Msg 7403, Level 16, State 1, Line 1

  The OLE DB provider "'server_name'" has not been registered.

  Any help..

  i dont have the rite syntax...

  Thanks

  [font="Comic Sans MS"]
  ---------------------------------------------------

  Thanks [/font]

• John Rowan

  SSC Guru

  Points: 56440

  More actions

  January 16, 2009 at 12:08 pm

  #928421

  You posted this in the SSIS forum, are you trying to do this in SSIS? If so, is this in a ExecuteSQL task or where have you done this?

  Selecting data from another server is very easy in SSIS, I would recommend setting up a connection in the connection manager and using that to get your data into SSIS, not openrowset.

  John Rowan

  ======================================================
  ======================================================
  Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

• dharani-919169

  Old Hand

  Points: 302

  More actions

  January 17, 2009 at 4:00 am

  #928658

  ya john your are Absolutely right,

  if you dont use ssis

  it is better to use linked server concept to retrieve data from another server

  step1: add linked server

  step2: add linker server login

  to query:select * from [servername] .[databasename].[tablename]