Excel Report - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Excel Report

Author

Topic

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2010-10-21 : 10:09:15

I have the task to take a result set from a Stored Procedure.

Very small set. 5 rows by 9 columns. But i need to insert it into

a specific area within the excel sheet. What is the best way i can

acheive this?

MCTS / MCITP certified

gavakie
Posting Yak Master

221 Posts

Posted - 2010-10-21 : 10:17:53

Execute the proc in excel. You can set set it to a certain cell to begin with or two a separate tab and link the data from there.

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2010-10-21 : 10:19:59

how do you execute in excel

gavakie
Posting Yak Master

221 Posts

Posted - 2010-10-21 : 10:32:06

What version of excel?

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2010-10-21 : 10:41:53

Excel 2010

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2010-10-21 : 11:19:22

Can someone tell me where iam going wrong with this script for an OpenRowSet.
I have looked at all the recomemded topics on here, but still can not see where i am going wrong.

i am using

INSERT INTO OPENROWSET ('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Excel\FlashReport-Template.xls;’,
'SELECT B5:j5 FROM [New date$]') SELECT * FROM dbo.V_Flash_Ref_1to5

and receive the following.

Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'SELECT'.
Msg 105, Level 15, State 1, Line 4
Unclosed quotation mark after the character string ') SELECT * FROM dbo.V_Flash_Ref_1to5

'.

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2010-10-21 : 11:41:18

I have now managed to get a bit further. I want to populate cells B5 down and accross to J9 so like B5:J9

but i execute the following code:

insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Excel\FlashReport-Template.xls;',
'SELECT B5 FROM [newdate$B5:J9]') select * from dbo.V_Flash_Ref_1to5

and receive the following error

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "The Microsoft Jet database engine could not find the object 'newdate$B5:J9'. Make sure the object exists and that you spell its name and the path name correctly.".
Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

i have double checked where my file is and that it is on the server.

michael.appleton
Posting Yak Master

160 Posts

Posted - 2010-10-21 : 11:50:55

No need for any vba! Just go to the data tab and select "From other sources->SQL Server". Follow the wizard to create a connection and untick "use a specific table" then next time you can use connection from existing connections. When it comes to the selecting the table part using the connection you've just made, select anything and go next. On the next page where it asks woudl you like to make a table, pivot etc select properties. In there you will find a definition page and under command text just insert your query. In your case this will be


EXEC [stored procedure name]

One other important thing; it's best to use SET NOCOUNT ON; at the top of any query used in this way as excel won't know which results to use if the database is telling it anything else besides the final result set e.g. if there are variables getting populated in the query and the database reports x rows effected.

michael.appleton
Posting Yak Master

160 Posts

Posted - 2010-10-21 : 11:54:33

I think I can see what you were trying to do now! Were you trying to run the query in SSMS and insert into an excel sheet? That is definitely not a way to do it. The application or reporting front end should request data off the database, not the database initiate sending data like that.

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2010-10-22 : 03:04:15

Hello Michael. THank you for your reply(s). Yes that is what iam trying to do. So i should set up excel to request the data from SQL Server.

That does make more sense.

michael.appleton
Posting Yak Master

160 Posts

Posted - 2010-10-22 : 04:36:46

Yeah, requesting the data in excel using the method I have described is something we do all the time at my work. If you need to ever update the data to the latest in the database, clicking the refresh button in excel runs the query again.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources