There are three examples of the use of the stored procedure just after the source of the stored procedure. The DDL is the SQL Data Definition Language command that you want to send to the data source. The DDL commands are defined by ODBC. These ODMC sources must be able to respond to a certain number of these commands, depending whether the ODBC driver is Minimun, Core or Extended.

I hope that helps!

see http://www.jsoftware.com/help/user/sql_statements.htm

Hi,
Really cool stuff. One thing I seem to be missing though; how do you fill the spreadsheet with data from sql server as opposed to explicitly indicating the values you want to insert into the spreadsheet?

My apologies for the goofy question. It's probably in there somewhere and I missed it.

Many thanks!

Another Excellent Article! Thanks for the great detail Robyn and Phil!

Using similiar steps as in your article, I am creating an Excel worksheet with two sheets from SQL; the second sheet is a summary of the data in the first sheet.
In my testing I create the summary sheet with SQL as well and that works of course.
For formating (ease of viewing) however I would like to use Excel functions in the second sheet.
Examples:
{=SUMPRODUCT((Sheet1!J2:J40)*(Sheet1!$I$2:$I$40="PartA"))}
or
{=SUM(VALUE(Sheet1!J2:J40))}
or
{=COUNT(VALUE(Sheet1!J2:J40))}

The "Rub" is that to enter an array function into an Excel Cell you must type it without the curly brackets and hit CTRL + SHIFT + ENTER.

I cannot recreate this in SQL to insert the Array Function into the Cell...

Any ideas?

I thought about recording a Macro and having that run when the Worksheet was opened; how do I insert a Macro into a new sheet from SQL?
At the end of my SQL Script I also email the Excel as an attachment to the User - they need to be able to open the Worksheet with few if not zero steps... (nothing new there...)

Thanks!
Todd

Great article! Finally I've found other who are trying to solve the same problems that I am.

One issue I've encountered though. The Microsoft.Jet.OLEDB.4.0 driver does not exist for 64-bit Windows Servers AND Microsoft apparently has no plans to create one. Very little info from Microsoft on this topic (http://connect.microsoft.com/SQLServer/feedback/Vote.aspx?FeedbackID=125117).

You can insert formulas into Excel just as easily as you do values from TSQL. I'm sorry if we didn't quite make that clear. you just assign your formula to the .FORMULA attribute of the cell or cell range. You'll see in the example in the article where we assign to the .VALUE of a cell to insert the resultset values into excel. You just do the same, but assign to the .FORMULA

Good luck and let us know how you get on!

This is an 'interesting' problem. The way I get around this is to use a linked SQL Server Express 'Grunt' server to do this sort of job, on a 32-bit machine. Works fine: no hassle. But it aint the perfect solution.

I've heard that one can get ODBC to work by running C:\WINDOWS\SysWOW64\odbcad32.exe but I've never tried it.

Hi Robyn,
Hi Phil,
Thanks for the great work you've been doing. I think this would be a very useful script for me to have. However having copied it from the site (in steps of course)I find that I'm getting the error... "Msg 50000, Level 16, State 1, Procedure spDMOExportToExcel, Line 249
Error whilst Creating the Excel Application, on MyServer, sp_OAMethod usage: ObjPointer int IN, MethodName varchar IN [, @returnval <any> OUT [, additional IN, OUT, or BOTH params]]"
So I know it has to do with the excel application (which exists) but not what to do about it. Any light for the darkness of this newbie?
Thanks

Copy the script from the box at the top of the article. it is more reliable. Click on where CODE DOWNLOAD is written. The error means that there is a parameter missing in the call to the sp_oaMethod is being called to create the OLE object. As far as I'm aware, the code itself is OK, so I suspect something got lost when you pasted it in. Let us know if that doesn't solve the problem

We're glad you like the article. I use the code a lot myself.

Hi Phil (and Robyn), thanks for the quick response. I did as suggested but I still get that same error message. I thought it might be related to the surface area configuration so I ensured that OLE automation was enabled as well as xp_cmdshell. However that hasn't made a difference. Any other ideas? I've already got three assignments that could use this approach once I get it working. Thanks again...

Hi Robyn and Phil,
as everybody, i love your solution. It was going to help me a lot (36 servers on which some results are to be published).
I tested on my ntb - all went fine.
But ... On the first server I went to test, I can't get after EXEC @hr=sp_OAMethod @objconnection, 'Open' - I keep getting that weird error code, with no additional info ...Did you ever get this one ?
Thanks a lot !

Hi Phil (and Robyn), thanks for the quick response. I did as suggested but I still get that same error message. I thought it might be related to the surface area configuration so I ensured that OLE automation was enabled as well as xp_cmdshell. However that hasn't made a difference. Any other ideas? I've already got three assignments that could use this approach once I get it working. Thanks again...

Great work on this. Extremely helpful.

I am able to populate the spreadsheet with data selected from my sql db using an "insert into", and I can update specified rows.

But I cannot determine how to delete all rows. Maybe I missed something in your detail. I tried the following script

delete OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',

'Data Source="\\FS1\DriveF\Misc-Admin\JobInfo\New.xls"
;User ID=Admin;Password=;Extended properties=Excel 8.0')
...Newtab$

and get the following error

Server: Msg 7345, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' could not delete from table 'Newtab$'. There was a recoverable, provider-specific error, such as an RPC failure.
[OLE/DB provider returned message: Deleting data in a linked table is not supported by this ISAM.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IRowsetChange::DeleteRows returned 0x80040e21: DBROWSTATUS_E_FAIL].

If I remove the ...Newtab$, I get the following error

Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near ')'.

Could you plz assist. Thanks

Great work on this. Extremely helpful.

I am able to populate the spreadsheet with data selected from my sql db using an "insert into", and I can update specified rows.

But I cannot determine how to delete all rows. Maybe I missed something in your detail. I tried the following script

delete OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',

'Data Source="\\FS1\DriveF\Misc-Admin\JobInfo\New.xls"
;User ID=Admin;Password=;Extended properties=Excel 8.0')
...Newtab$

and get the following error

Server: Msg 7345, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' could not delete from table 'Newtab$'. There was a recoverable, provider-specific error, such as an RPC failure.
[OLE/DB provider returned message: Deleting data in a linked table is not supported by this ISAM.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IRowsetChange::DeleteRows returned 0x80040e21: DBROWSTATUS_E_FAIL].

If I remove the ...Newtab$, I get the following error

Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near ')'.

Could you plz assist. Thanks

Yes. Isn't it strange. It looks from the error message that the OLEDB provider doesn't even support deletion of rows. I often have to shrug and go back to doing a full automation of Excel when something simple doesn't work!
Try the link approach, but I suspect that will not work either

Good morning and thanks for the above. The issue that i am having with this and hope that you have found a workaround is for fields with more than 255 chars. Seems that utilizing Microsoft.Jet.OLEDB.4.0 , Excel 8.0 will throw an error "Msg 8152, Level 16, State 4, Line 1
String or binary data would be truncated." I have tried the tweak at http://support.microsoft.com/kb/189897/EN-US/ to no avail. any help would be greatlt appreciated!
steve@nyctechsupport.com