Generating Excel from SQL Server

Question

IF @hr=0 
EXEC @hr=sp_OACreate 'Excel.Application', @objExcel OUT

The above line for creating excel is giving errorcode "-2147221005"

Answer 1

You're trying to create the COM object for Excel on your SQL Server and it fails.

Do you really have the Microsoft Office package installed on your SQL Server?? Normally, this is not considered a good idea.... (at least on a production server).

If I were you, I'd try to find another way to generate a file that Excel can use - e.g. a CSV file or something like that.

For a few ideas, see:

• Creating CSV files using bcp and stored procedures
• Saving SQL Query Results to Comma Delimited Files (CSV)

Answer 2

Maybe you do not have Excel installed on the Sql server box.

You should rather try using a different approach

See

• Sql Server: Export to Excel
• Export data from SQL Server to Excel

EDIT for Comment:

Maybe this would be best handled by the Application layer, and let Sql Server do what it does best.

Answer 3

This kind of processing does not belong in a SQL clr procedure. Do it in the client, using the Primary Interop Assemblies of Office, see How to automate Microsoft Excel from Microsoft Visual C# .NET. If you insist on doing it from the server, your SQL Server instance may unexpectedly vanish.