parameters in openrowset

• collie

  Old Hand

  Points: 367

  More actions

  September 9, 2009 at 9:01 am

  #215019

  Hi,

  How can i change the path and sheetname to parameters with the correct syntax?

  SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',

  'Excel 8.0;DATABASE=c:\test\Catalog_big.xls;IMEX=1', 'Select * from [catalog SU08$]')

  Thanks

• Garadin

  One Orange Chip

  Points: 29613

  More actions

  September 9, 2009 at 11:43 pm

  #1051574

  Not real familiar with openrowset, but ordinarily in a situation like this I'd sugest dynamic sql to solve this issue.

  Something like:

  declare @sql varchar(500),

  @path varchar(50),

  @sheet varchar(50)

  set @path = 'C:\test\'

  set @sheet = 'Catalog_big.xls'

  set @sql =

  'SELECT * FROM OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',

  ''Excel 8.0;DATABASE=' + @path + @sheet+ ';IMEX=1'', ''Select * from [catalog SU08$]'')'

  EXEC(@SQL)

  I'm freehanding this so I may have screwed up a quote or 10, but that's the general idea.

  Seth Phelabaum

  ---

  Consistency is only a virtue if you're not a screwup. 😉

  Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

• Garadin

  One Orange Chip

  Points: 29613

  More actions

  September 9, 2009 at 11:43 pm

  #1051575

  Ugh, having so many issues posting lately. I wonder if it's just me.

  Seth Phelabaum

  ---

  Consistency is only a virtue if you're not a screwup. 😉

  Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]