Exporting to Excel Using a CLR Stored Procedure

Question

Exporting to Excel Using a CLR Stored Procedure

Viewing 15 posts - 106 through 120 (of 124 total)

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

sidni Valued Member Points: 60 More actions · Answer 1

For some of my Tables I get the following errors, which I don't know if anyone come across these. I have no idea how to try and fix these:

1. The file you trying to open is in a different format than specified by the file extemsion....

1. comes from all executions and 2 below for some. Something to do with excel

2.

Problems During Load

Problems came up in the following areas during load

Workbook Setting

Thanks

sidni

sidni Valued Member Points: 60 More actions · Answer 2

Igore my previous post, just figured it out. Great work.

Thanks

sidni

Rob Fisk SSCommitted Points: 1861 More actions · Answer 3

sidni (6/30/2010)
Igore my previous post, just figured it out. Great work.
Thanks
sidni

Sorry, missed the original question so have not investigated. Since you have worked it out then an explanation of the cause and solution may be helpful in case anyone else comes across the same issues.

Cheers.

[font="Arial"]_______________________________________________________
Change is inevitable... Except from a vending machine.[/font]

sidni Valued Member Points: 60 More actions · Answer 4

Basically if your first field/column contains only nulls or no data, you will get the excel error on opening the output file and the file will not open at all. That is because the first column values are taken as the excel tab name, and you cannot have an unnamed tab name.

Having gone to basics and reviewed the examples from the article, I worked out that you need to at least create a dummy column populated with data, for example as per article

select 'sysobjects',*from sys.objects, which creates a first field populated with 'sysobjects' and this is taken as the tab name.

I hope it helps

sidni

sidni Valued Member Points: 60 More actions · Answer 5

I must add a thank you, this is saving me a great deal of time.

sidni Valued Member Points: 60 More actions · Answer 6

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

Rob Fisk SSCommitted Points: 1861 More actions · Answer 7

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'

[font="Arial"]_______________________________________________________
Change is inevitable... Except from a vending machine.[/font]

sidni Valued Member Points: 60 More actions · Answer 8

sidni

Valued Member

Points: 60

July 3, 2010 at 4:02 am

#1188496

Thank you that did the trick.

L Cerniglia Mr or Mrs. 500 Points: 579 More actions · Answer 9

How do you open this project with SQL Server 2005? I get the message "The application for project <project> is not installed".

Versatile Newbie Points: 1 More actions · Answer 10

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,

ben_shaw1 SSC Enthusiast Points: 143 More actions · Answer 11

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?

Anders Pedersen SSChampion Points: 11410 More actions · Answer 12

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

ben_shaw1 SSC Enthusiast Points: 143 More actions · Answer 13

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 🙂

slacker220 Newbie Points: 2 More actions · Answer 14

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

Anders Pedersen SSChampion Points: 11410 More actions · Answer 15

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