Solved

Output to Excel

Posted on 2004-09-29
16
416 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Server running out of memory - Something is consuming all the available memory 17 62
SQL Error - Query 6 26
performance query 4 24
sql server service accounts 4 27
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
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.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

831 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