March 28, 2012 at 6:21 am
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 🙂
December 11, 2012 at 9:13 pm
Thanks!!!
This saved a great deal of my time!
July 12, 2013 at 4:12 am
we can do the excel formatting, like bold the column or colours the column
March 10, 2015 at 11:30 am
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?
March 10, 2015 at 12:59 pm
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 124 (of 124 total)
You must be logged in to reply to this topic. Login to reply