?
Solved

Output to Excel

Posted on 2004-09-29
16
Medium Priority
?
421 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
  • 4
  • +1
16 Comments
 
LVL 7

Accepted Solution

by:
FDzjuba earned 400 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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
 

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

762 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