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 Friday, July 02, 2010 9:12 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, October 30, 2013 9:37 AM
Points: 49, Visits: 160
Is there a way I can run the procedure from other databases on the same server without having to copy into each database I want to run it on.

Thanks
s
Post #946915
Posted Friday, July 02, 2010 12:22 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, June 29, 2012 4:56 AM
Points: 163, Visits: 427
sidni (7/2/2010)
Is there a way I can run the procedure from other databases on the same server without having to copy into each database I want to run it on.

Thanks
s


Yes, I have it in a Tools DB where I keep the tally table and all globally useful functions and procedures etc so there is only one point of maintenance.

Therefore the syntax would be..

EXEC Tools.dbo.ExportToExcel 'thisDB.dbo.MyResultsProcedure'


_______________________________________________________
Change is inevitable... Except from a vending machine.
Post #947040
Posted Saturday, July 03, 2010 4:02 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, October 30, 2013 9:37 AM
Points: 49, Visits: 160
Thank you that did the trick.

Post #947176
Posted Monday, July 19, 2010 5:45 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, March 31, 2014 9:20 AM
Points: 24, Visits: 295
How do you open this project with SQL Server 2005? I get the message "The application for project <project> is not installed".
Post #954751
Posted Tuesday, September 21, 2010 7:42 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 25, 2013 9:55 PM
Points: 1, Visits: 20
Hi,
This is an excellent post for CLR procedure to generate an Excel file.
I have another question.
Is there a way to add MIME type to the Excel file using XSLT code you are using.
We can generate Excel file but when we open the file we are getting the warning as "The file you are trying to open .xlsx is in a different format than specified by the file extension. verify the file is not corrupted and is from trusted source before opening the file. Do you want to open the file now?"

How do we resolve this?

Thanks,
Post #990279
Posted Friday, January 21, 2011 4:46 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, April 11, 2014 9:45 AM
Points: 13, Visits: 401
After executing

CREATE PROCEDURE[dbo].[prc_ExportToExcel]
@proc [nvarchar](100),
@path [nvarchar](200),
@filename [nvarchar](100),
@params xml
AS
EXTERNAL NAME [ExportToExcel].[StoredProcedures].[ExportToExcel

I am getting an error reading

More than one method, property or field was found with name 'ExportToExcel' in class 'StoredProcedures' ..... Overloaded methods, properties or fields are not supported.

I am not sure how to tackle this problem...

Does anybody have any tips?
Post #1051438
Posted Friday, January 21, 2011 5:55 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 12:43 PM
Points: 1,279, Visits: 739
Versatile,

I'd guess it would be possible. However we have not had a need for it so I have not tried it. Take a look at the source, or have someone that knows C# look at the source and see.

BenShaw,

I would guess that someone has already imported it to SQL. Not seen that error message while working on it.

Anders
Post #1051470
Posted Friday, January 21, 2011 6:04 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, April 11, 2014 9:45 AM
Points: 13, Visits: 401
I took a look at the c#.

There were two methods called

ExportToExcel has been created as a partial class.

There are two methods called ExportToExcel, one of which has been overloaded with xmlParams.

I deleted the method ExportToExcel(SqlString procName, SqlString filePath, SqlString fileName) and all works fine.

On another note.. how about creating a Asymetric key insteaded of changing the TRUSTWORTHY property?

Thanks for your quick reply.. I really enjoyed your article...

Ben :)
Post #1051475
Posted Monday, March 19, 2012 5:45 PM
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
Can anyone get this to work with date parameters? I get a lovely error...

Msg 6522, Level 16, State 1, Procedure prc_ExportToExcel, Line 0
A .NET Framework error occurred during execution of user-defined routine or aggregate "prc_ExportToExcel":
System.Data.SqlClient.SqlException: Error converting data type nvarchar to datetime.
System.Data.SqlClient.SqlException:
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnectionSmi.EventSink.DispatchMessages(Boolean ignoreNonFatalMessages)
at Microsoft.SqlServer.Server.SmiEventSink_Default.DispatchMessages(Boolean ignoreNonFatalMessages)
at Microsoft.SqlServer.Server.SmiEventSink_Default.DispatchMessages(Boolean ignoreNonFatalMessages)
at System.Data.SqlClient.SqlDataReaderSmi.InternalNextResult(Boolean ignoreNonFatalMessages)
at System.Data.SqlClient.SqlDataReaderSmi.NextResult()
at System.Data.SqlClient.SqlCommand.RunExecuteReaderSmi(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapt...
Post #1269284
Posted Tuesday, March 27, 2012 2:40 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 12:43 PM
Points: 1,279, Visits: 739
I'll see if I can test it with a date as a parameter. Honestly I couldn't find a single place that I use this with a parameter, so it is the least tested part of it. Hopefully I can get some time "soon" to test it out...
Post #1273922
« Prev Topic | Next Topic »

Add to briefcase «««910111213»»

Permissions Expand / Collapse