OpenRowSet / LinkedServer Question

• sho-467341

  Ten Centuries

  Points: 1062

  More actions

  January 29, 2008 at 7:38 am

  #182375

  Hi,

  I have the following query (see below), it works, however, the DBA team have told me that they will take away the use of OPENROWSET. and have advised that I use LinkedServers instead. Okay, so this is gospel and I have no comeback.

  How can I use a linked server to do this? I know how to set them up and how to get data from and Excel file via Linked Servers, but the bit I'm having trouble with is specifying the cell range, ie: 'SELECT * FROM A5:B20'. I can't alter the Excel file and give it a named range.

  Any ideas? also the A5:B20 part is calculated using some wierd function provided by a different team, so the query below is actually dynamic sql! but I've just given the actual SQL below for simplicity.

  SELECT

  *

  FROM

  OPENROWSET(

  'Microsoft.Jet.OLEDB.4.0',

  'Excel 8.0;

  DATABASE=\\MyServer\MyFolder\MyFile.xls;HDR=NO',

  'SELECT * FROM A5:B20'

  )

  Thanks for any help.

• Matija Lah

  SSCertifiable

  Points: 6429

  More actions

  January 30, 2008 at 8:02 am

  #773307

  Have you considered using SSIS for this?

  ML

  ---
  Matija Lah, SQL Server MVP
  http://milambda.blogspot.com

• Piotr.Rodak

  SSCrazy Eights

  Points: 9376

  More actions

  January 30, 2008 at 8:33 am

  #773339

  Won't

  SELECT * FROM openquery(My_Excel, 'SELECT * FROM A5:B20')

  work once you have the server configured? Openrowset is an ad hoc linked server after all.

  Piotr

  ...and your only reply is slàinte mhath