Exporting to Excel Using a CLR Stored Procedure

  • slacker220

    Newbie

    Points: 2

    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

    SSCrazy

    Points: 2554

    Thanks!!!

    This saved a great deal of my time!

  • anandsinghh

    SSC Veteran

    Points: 298

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

  • BoilerJ

    Default port

    Points: 1428

    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

    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

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

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