Exporting to Excel via stored procedure

• Eugene Elutin

  SSC Guru

  Points: 59322

  More actions

  March 2, 2012 at 8:33 am

  #1454227

  I think you still can do it with ##table, but you need to make everything in to your dynimc sql string:

  DECLARE @sql VARCHAR(8000)

  DECLARE @IdNbr VARCHAR(50)

  SET @IdNbr = '312012145014'

  SET @sql = 'INSERT INTO OPENROWSET (''Microsoft.Jet.OLEDB.4.0'',

  ''Excel 8.0;Database=c:\partner_files\Templates\open_call_reporttemp.xls;'',

  ''SELECT * FROM [Sheet1$]'')

  select * from ##TempOpenCallData_' + @IdNbr

  EXEC (@sql)

  _____________________________________________
  "The only true wisdom is in knowing you know nothing"
  "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
  (So many miracle inventions provided by MS to us...)

  How to post your question to get the best and quick help[/url]

• Cory Huntington

  Old Hand

  Points: 395

  More actions

  March 2, 2012 at 8:58 am

  #1454250

  Eugene Elutin (3/2/2012)

  ---

  I think you still can do it with ##table, but you need to make everything in to your dynimc sql string:

  DECLARE @sql VARCHAR(8000)

  DECLARE @IdNbr VARCHAR(50)

  SET @IdNbr = '312012145014'

  SET @sql = 'INSERT INTO OPENROWSET (''Microsoft.Jet.OLEDB.4.0'',

  ''Excel 8.0;Database=c:\partner_files\Templates\open_call_reporttemp.xls;'',

  ''SELECT * FROM [Sheet1$]'')

  select * from ##TempOpenCallData_' + @IdNbr

  EXEC (@sql)

  Yes!! This works as well, and allows me to stick with the temp tables!

  thanks!!