Exporting to Excel Using a CLR Stored Procedure

  • 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


    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


    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 🙂

  • Thanks!!!

    This saved a great deal of my time!

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

  • 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?

  • 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).


Viewing 5 posts - 121 through 124 (of 124 total)

You must be logged in to reply to this topic. Login to reply