Solved

Output to Excel

Posted on 2004-09-29
16
418 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 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

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

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
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 set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

726 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