Solved

Output to Excel

Posted on 2004-09-29
16
409 Views
Last Modified: 2012-06-22
How may I output a select statement with 1 parameter to excel or text from a stored procedure?
0
Comment
Question by:wordtool
  • 7
  • 4
  • 4
  • +1
16 Comments
 
LVL 7

Accepted Solution

by:
FDzjuba earned 100 total points
ID: 12185829
you can do so by setting up linked server, thats one way around it

To create a linked server against an Excel spreadsheet:

The Microsoft OLE DB Provider for Jet 4.0 can be used to access Microsoft Excel spreadsheets.

To create a linked server that accesses an Excel spreadsheet, use the format of this example.
sp_addlinkedserver N'Excel', N'Jet 4.0',
                   N'Microsoft.Jet.OLEDB.4.0',
                   N'c:\data\MySheet.xls', NULL, N'Excel 5.0'
GO
sp_addlinkedsrvlogin N'Excel', false, sa, N'ADMIN', NULL
GO

To access data from an Excel spreadsheet, associate a range of cells with a name. A named range can be accessed by using the name of the range as the table name. The following query can be used to access a named range called SalesData using the linked server set up in the previous example.
SELECT *
FROM EXCEL...SalesData
GO

When you insert a row into a named range of cells, the row will be added after the last row that is part of the named range of cells. Thus, if you want to insert row rA after the column heading, associate the column heading cells with a name and use that name as the table name. The range of cells will grow automatically as rows are inserted.

To set up a linked server against a formatted text file:

Microsoft OLE DB Provider for Jet can be used to access and query text files.

To create a linked server for accessing text files directly without linking the files as tables in an Access .mdb file, execute sp_addlinkedserver, as in this example.
The provider is Microsoft.Jet.OLEDB.4.0 and the provider string is 'Text'. The data source is the full path name of the directory that contains the text files. A schema.ini file, which describes the structure of the text files, must exist in the same directory as the text files. For more information about creating a schema.ini file, see the Jet Database Engine documentation.

--Create a linked server.
EXEC sp_addlinkedserver txtsrv, 'Jet 4.0',
    'Microsoft.Jet.OLEDB.4.0',
    'c:\data\distqry',
    NULL,
    'Text'
GO

--Set up login mappings.
EXEC sp_addlinkedsrvlogin txtsrv, FALSE, NULL, Admin, NULL
GO

--List the tables in the linked server.
EXEC sp_tables_ex txtsrv
GO

--Query one of the tables: file1#txt
--using a 4-part name.
SELECT *
FROM txtsrv...[file1#txt]

0
 
LVL 7

Expert Comment

by:FDzjuba
ID: 12185946
here is a little example:


sp_addlinkedserver N'Excel', N'Jet 4.0',
                   N'Microsoft.Jet.OLEDB.4.0',
                   N'c:\book1.xls', NULL, N'Excel 5.0'
GO
sp_addlinkedsrvlogin N'Excel', false, sa, N'ADMIN', NULL
GO

INSERT INTO EXCEL...sheet1$ ([Column1])
SELECT * FROM table1



-----
NOTE: ADO assumes that very first row in excel sheet is a column name, so you would have to name those columns. Of course you would need to have XLS file ready. How to create XLS is a different story and requires different topic :)
The above example is not the best, but if you working with single spreadsheet all the time that will fit your needs
0
 
LVL 34

Expert Comment

by:arbert
ID: 12186043
You could also use Openrowset and DTS--just depends on your requirements and what you're familar with...
0
 

Author Comment

by:wordtool
ID: 12186093
trying it...
0
 

Author Comment

by:wordtool
ID: 12186120
I get the following error:

Server: Msg 15028, Level 16, State 1, Procedure sp_addlinkedserver, Line 79
The server 'Excel' already exists.

(0 row(s) affected)


(1 row(s) affected)


(0 row(s) affected)


(1 row(s) affected)

Server: Msg 7399, Level 16, State 1, Line 2
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.  
[OLE/DB provider returned message: Unspecified error]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005:   ].


Also is this looking for the book1.xls on my local machine or on the server?
0
 

Author Comment

by:wordtool
ID: 12186129
I'm not sure DTS would work because I have a parameter for the select statement.  Can you give an example of using openrowset?
0
 
LVL 34

Expert Comment

by:arbert
ID: 12186142
"I'm not sure DTS would work because I have a parameter for the select statement."

Sure it would--setup a executeSQL task and pass a global parm.  Use DTSRUN from a stored proc to run it....
0
 
LVL 34

Expert Comment

by:arbert
ID: 12186148
From books online:

Includes all connection information necessary to access remote data from an OLE DB data source. This method is an alternative to accessing tables in a linked server and is a one-time, ad hoc method of connecting and accessing remote data using OLE DB. The OPENROWSET function can be referenced in the FROM clause of a query as though it is a table name. The OPENROWSET function can also be referenced as the target table of an INSERT, UPDATE, or DELETE statement, subject to the capabilities of the OLE DB provider. Although the query may return multiple result sets, OPENROWSET returns only the first one.

Syntax
OPENROWSET ( 'provider_name'
    , { 'datasource' ; 'user_id' ; 'password'
        | 'provider_string' }
    , { [ catalog. ] [ schema. ] object
        | 'query' }
    )

Arguments
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

Author Comment

by:wordtool
ID: 12186190
The last line "OPENROWSET returns only the first one."  I can only output 1 record?  If not can you give me an example utilizing the syntax?
0
 
LVL 34

Expert Comment

by:arbert
ID: 12186319
No--read closer multiple RESULT SETS not records--different things.....
0
 
LVL 9

Expert Comment

by:miron
ID: 12186328
-- ... is this looking for the book1.xls on my local machine or on the server?

it is looking for the file located on the server connected to. Can be solved by using UNC path: \\<machine_name>\<share_name>\<excel file name>

Make sure windows account running sql server has sufficient privileges to access the files on the share.

-- cheers
0
 
LVL 7

Expert Comment

by:FDzjuba
ID: 12188899
arbert, right. DTS would do the job as well, he will guide you in that direction:)

Linked server is good thing to do when you constantly working with the same excel file damping data into it. In all the other cases Linked server is no-no-no.
Anyway about errors:

>>Server: Msg 15028, Level 16, State 1, Procedure sp_addlinkedserver, Line 79
The server 'Excel' already exists.<<
Means you have already created linked server, running this

-------
sp_addlinkedserver N'Excel', N'Jet 4.0',
                   N'Microsoft.Jet.OLEDB.4.0',
                   N'c:\book1.xls', NULL, N'Excel 5.0'
GO
sp_addlinkedsrvlogin N'Excel', false, sa, N'ADMIN', NULL
GO
----------


use this to drop server, if you no longer want it.
sp_dropserver 'Excel', 'droplogins'

you can also sp_helpserver to determine if linked server connected.


>>Server: Msg 7399, Level 16, State 1, Line 2
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.  
[OLE/DB provider returned message: Unspecified error]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005:   ].<<
is because file doesn't exists.

>>Also is this looking for the book1.xls on my local machine or on the server?<<
Server side! if  you specified c:\book1.xls then file has to be in pr drive C: on the server
0
 

Author Comment

by:wordtool
ID: 12193048
Okay I'll try this again...
0
 

Author Comment

by:wordtool
ID: 12193864
This seems to work and it runs without errors but I have the path set to a different location, when I go to the file it didn't write to the excel file.
0
 
LVL 7

Expert Comment

by:FDzjuba
ID: 12198765
have you specified correct sheet name, like sheet1$ is a sheet name, plus don't forget to name your very first row in excel sheet, ADO assumes these are column names

INSERT INTO EXCEL...sheet1$ ([Column1])
SELECT * FROM table1
0
 

Author Comment

by:wordtool
ID: 12385026
Please see me next question...

If I don't have a range in my Excel spreadsheet I can't select that table (range) once I'm linked to the sql.  Is there a way to name the range during the process of linking to the file from sql?
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now