Exporting to Excel Using a CLR Stored Procedure

• slacker220

  Newbie

  Points: 2

  More actions

  March 28, 2012 at 6:21 am

  #1465613

  I finally got it last night. What you have to do is a few converts on the datetime field. Here is a quick example...

  --Exporting to Excel

  DECLARE @cdate NVARCHAR(MAX)

  SET @cdate = '2012-03-24T00:00:00' -- XML DATTIME

  SET @params = '<params><param name="cdate" value="' + @cdate + '" /></params>'

  exec prc_ExportToExcel 'Your Stor Proc', 'Your File Loc', 'Name', @params

  --Stor Proc Examp

  DECLARE @cdate DATETIME

  SELECT Date

  FROM Table

  WHERE (Table.Date <= CONVERT(DATETIME, @cdate, 126)) --126 lets SQL know that it is a XML DATETIME format, and converts it to SQL DATETIME format

  Hope this helps someone who may need to do the same thing I was working on

• awaksm

  Hall of Fame

  Points: 3433

  More actions

  December 11, 2012 at 9:13 pm

  #1567681

  Thanks!!!

  This saved a great deal of my time!

• anandsinghh

  Old Hand

  Points: 348

  More actions

  July 12, 2013 at 4:12 am

  #1631868

  we can do the excel formatting, like bold the column or colours the column

• BoilerJ

  Default port

  Points: 1428

  More actions

  March 10, 2015 at 11:30 am

  #1783026

  I know this post is old but I was wondering if anyone experienced the same thing I have. First off I think this CLR is very cool. I use it for several automated reports via SQL Agent. Most of the reports are fairly small result sets. I have recently had a few reports that are 10-30K rows. The Proc/CLR runs fine but the file is abnormally large for the result set. When I run the Stored Procedure in Query Analyzer and paste the results into excel and save the file it is 15MB. When I run it through the CLR/Proc the file is 75MB or 5 times larger. This is keeping me from emailing the file. I am obviously cool with not emailing a report if file sizes are beyond what normal email/servers) handle but this should be 15MB which I am able to email successfully. Has anyone else seen this or know why this code might be doing this?

• Anders Pedersen

  SSChampion

  Points: 11410

  More actions

  March 10, 2015 at 12:59 pm

  #1783064

  JCSQL,

  I have not seen that happen myself in the years of using it, but I have to admit I do not think I ever used it for anything quite that large (number of records).

  Anders