Store Procedure: SQL Server 2000 to Excel

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Store Procedure: SQL Server 2000 to Excel

Author

Topic

Bob3242
Starting Member

4 Posts

Posted - 2003-06-03 : 09:39:23

Hi,

HELP! I am working on an generic store prodecure that will export SQL tables to an excel spread sheet. The file will generate about thirty lines of output into a spread sheet and seems to jump into a infinate loop. Any ideas?

Bob

CREATE PROCEDURE dbo.ExportToExcel (
@server sysname = null,
@uname sysname = null,
@pwd sysname = null,
@QueryText varchar(200) = null,
@filename varchar(200) = 'd:\ImportToExcel.xls'
)

AS
DECLARE @SQLServer int,
@QueryResults int,
@CurrentResultSet int,
@object int,
@WorkBooks int,
@WorkBook int,
@Range int,
@hr int,
@hrr int,
@Columns int,
@Rows int,
@indColumn int,
@indRow int,
@off_Column int,
@off_Row int,
@code_str varchar(500),
@result_str nvarchar(500)

IF @server IS NULL SELECT @server = @@servername
IF @uname IS NULL SELECT @uname = SYSTEM_USER

SET NOCOUNT ON

EXEC @hr = sp_OACreate 'SQLDMO.SQLServer', @SQLServer OUT

IF @pwd IS NULL
BEGIN
EXEC @hr = sp_OAMethod @SQLServer, 'Connect', null, @server, @uname
END
ELSE
BEGIN
EXEC @hr = sp_OAMethod @SQLServer, 'Connect', null, @server, @uname, @pwd
END

SELECT @result_str = 'ExecuteWithResults("' + @QueryText + '")'
EXEC @hr = sp_OAMethod @SQLServer, @result_str, @QueryResults OUT
if @hr <> 0
BEGIN
Goto Clean_Up3
END

EXEC @hr = sp_OAMethod @QueryResults, 'CurrentResultSet', @CurrentResultSet OUT
IF @hr <> 0
BEGIN
Goto Clean_Up3
END

EXEC @hr = sp_OAMethod @QueryResults, 'Columns', @Columns OUT
IF @hr <> 0
BEGIN
Goto Clean_Up3
END

EXEC @hr = sp_OAMethod @QueryResults, 'Rows', @Rows OUT
IF @hr <> 0
BEGIN
Goto Clean_Up3
END

EXEC @hr = sp_OACreate 'Excel.Application', @object OUT
IF @hr <> 0
BEGIN
Goto Clean_Up2
END

EXEC @hr = sp_OAGetProperty @object, 'WorkBooks', @WorkBooks OUT
IF @hr <> 0
BEGIN
Goto Clean_Up2
END

EXEC @hr = sp_OAGetProperty @WorkBooks, 'Add', @WorkBook OUT
IF @hr <> 0
BEGIN
Goto Clean_Up1
END

EXEC @hr = sp_OAGetProperty @object, 'Range("A1")', @Range OUT
IF @hr <> 0
BEGIN
Goto Clean_Up1
END

SELECT @indRow = 1
SELECT @off_Row = 0
SELECT @off_Column = 1

WHILE (@indRow <= @Rows)
BEGIN

SELECT @indColumn = 1

WHILE (@indColumn <= @Columns)
BEGIN

EXEC @hr = sp_OAMethod @QueryResults, 'GetColumnString', @result_str OUT, @indRow, @indColumn
IF @hr <> 0
BEGIN
Goto Clean_Up1
END

EXEC @hr = sp_OASetProperty @Range, 'Value', @result_str
IF @hr <> 0
BEGIN
Goto Clean_Up1
END

EXEC @hr = sp_OAGetProperty @Range, 'Offset', @Range OUT, @off_Row, @off_Column
IF @hr <> 0
BEGIN
EXEC @hrr = sp_displayoaerrorinfo @Range, @hr
Goto Clean_Up1
END

SELECT @indColumn = @indColumn + 1

END --ends while loop

SELECT @indRow = @indRow + 1
SELECT @code_str = 'Range("A' + LTRIM(str(@indRow)) + '")'
EXEC @hr = sp_OAGetProperty @object, @code_str, @Range OUT
IF @hr <> 0
BEGIN
Goto Clean_Up1
END

END

--SELECT @result_str = 'exec master..xp_cmdshell ''del ' + @filename + ''', no_output'
--EXEC(@result_str)
SELECT @result_str = 'SaveAs("' + @filename + '")'
EXEC @hr = sp_OAMethod @WorkBook, @result_str
IF @hr <> 0
BEGIN
Goto Clean_Up1
END

Clean_Up1:
EXEC @hr = sp_OAMethod @WorkBook, 'Close'
IF @hr <> 0
BEGIN
GOTO Clean_Up2
END
Clean_Up2:
EXEC @hr = sp_OADestroy @object
IF @hr <> 0
BEGIN
Goto Clean_Up3
END
Clean_Up3:
EXEC @hr = sp_OADestroy @SQLServer
IF @hr <> 0
BEGIN
RETURN
END

X002548
Not Just a Number

15586 Posts

Posted - 2003-06-03 : 11:14:23

Why not use DTS?

Brett

8-)

Bob3242
Starting Member

4 Posts

Posted - 2003-06-03 : 11:20:34

I would love to use DTS but I was asked to create a stored procedure. It's my first week interning and I am trying to do it their way.

quote:

Why not use DTS?

Brett

8-)

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2003-06-03 : 11:22:22

Write a stored proc that calls a DTS package

Damian

X002548
Not Just a Number

15586 Posts

Posted - 2003-06-03 : 11:37:48

Do you have to create EXCEL? Will comma delimited do?

Damian, I've never seen anything like this done in SQL Server before...didn't even know it existed...you can create MS Objects from with in SQL Server (well I guess of course you can, since that's what DTS soed, right?)

I guess they're asking you to build your own DTS?

bcp won't do?

You could bcp out a view the generates comma delimited data?

Do these guys also walk across town to speak with someone instead of using a phone?

Brett

8-)

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2003-06-03 : 11:41:15

There is an executable called dtsrun.exe that you can call using xp_cmdshell.

Or put inside a job with no schedule and call msdb..sp_start_job

Damian

X002548
Not Just a Number

15586 Posts

Posted - 2003-06-03 : 12:03:16

Ok, just read the chapter in Ken Hendersons book (The Guru's guide to SQL Server, Stored Procedures, XML and HTML).

Has a whole example on hoe to export a table (in your case it could be a view)...All very thorough and commented well.

I would reccomend that you get this book.

It also has some excellent tips like telling you that you should run as "out-of-proc" servers, instead of in sql server, which Ken seems to identify as a bad thing.

To much in the book to list here.

But where the hell is the documentation on what all of the available COM objects are, their methods and/or properties?

Brett

8-)

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2003-06-03 : 12:06:19

Note to self : Check Ken's book before joining threads like this one

Damian

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-06-03 : 20:12:58

quote:
But where the hell is the documentation on what all of the available COM objects are, their methods and/or properties?

For Excel, Word, Access, and most of the MS Office products, the help files actually do a good job of documenting the COM object model for each application. They usually have pretty good code examples too. This has gone down the tubes a bit since Office 97, which used the older help file format and structure and was IMHO MUCH better than the current help format. MS Access had ungodly excellent examples of just about everything in its object model and DAO too.

Unfortunately I cannot say I've had the same experience with other products, like Crystal Reports. I'm sure there's documentation out there but it varies greatly in quality. The other thing is to try the vendor's website for a developer's forum, most of the decent ones have such a thing, and many also provide an SDK and documentation with the product.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-06-03 : 21:35:37

I still feel the best way to put data into Excel is to PULL it into Excel using a linked pivot table. You just create a view in SQL server, create new linked pivot table in Excel, add some criteria and choose your sorts, and then the data is there. Right click anytime you want to refresh.

Not always the best solution for all cases, but certainly easy to implement.

Rob -- I TOTALLY agree about the "new and improved" help files. I MUCH prefer the old ones. and I also agree about the Office help -- they document the object model very well. Make sure you have installed the VBA help, though -- I don't think the "standard" office installation includes VBA help. Brett, that could be why you haven't seen it.

- Jeff

Edited by - jsmith8858 on 06/03/2003 21:36:44

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-06-03 : 22:00:13

I agree with Rob and Jeff,
However if they insisted on a stored proc. create some excel vba code that uses ado command objects and recordsets to call the stored proc and populate a spreadsheet.

I recommend oreilly's excel macros book it is an excellent excel vba book.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources