Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««910111213

Exporting to Excel Using a CLR Stored Procedure Expand / Collapse
Author
Message
Posted Wednesday, March 28, 2012 6:21 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 15, 2013 6:45 AM
Points: 2, Visits: 33
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 :)
Post #1274232
Posted Tuesday, December 11, 2012 9:13 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Wednesday, March 11, 2015 7:09 AM
Points: 666, Visits: 74
Thanks!!!
This saved a great deal of my time!
Post #1395413
Posted Friday, July 12, 2013 4:12 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, November 28, 2013 3:23 AM
Points: 48, Visits: 95
we can do the excel formatting, like bold the column or colours the column
Post #1472929
Posted Tuesday, March 10, 2015 11:30 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 1:24 PM
Points: 64, Visits: 470
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?
Post #1667283
Posted Tuesday, March 10, 2015 12:59 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, March 23, 2015 1:52 PM
Points: 1,329, Visits: 795
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
Post #1667323
« Prev Topic | Next Topic »

Add to briefcase «««910111213

Permissions Expand / Collapse