Generating Excel File Using SQL Server 2000
page 3 of 8
by Aravind Kumar
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 1234393/ 2419

DOS File Copy

Microsoft SQL Server is a not only a powerful database, but also a tool for utilizing other Microsoft utilities like DOS, Office suite and other various software.  We can utilize various DOS internal commands using SQL Server for our needs.  And one good command, which will be used here, is the DOS File Copy command.

We can utilize DOS commands from SQL Server 2000 using the built-in stored procedure "Xp_CmdShell" provided by SQL Server 2000.  This built-in stored procedure is placed by default in the master database.  So, henceforth it is assumed that we will be working in the master database while running this particular command.

By default, only members (i.e. - sa) of the sysadmin fixed server role can execute this extended stored procedure.  You may, however, grant other users permission to execute this stored procedure.

For Example, if we need to delete a file we can run the following command in SQL.

Listing 1

EXEC MASTER..XP_CMDSHELL 'Del d:\sdf.xls'

In the above example we can see that the built-in stored procedure XP_CMDSHELL will be executed by passing the desired dos command.  Here the dos command "Del" has been used to instruct the stored procedure to delete the file in the path specified viz., 'd:\sdf.xls’;.


View Entire Article

User Comments

Title: Link Server Error   
Name: Shankar
Date: 2010-08-30 3:24:57 AM
Comment:
hi, I am getting link server error
Title: when i close the excel file, then automatically to excel data is save the sql server database   
Name: Buvaneshwaran.J
Date: 2009-04-28 3:53:11 AM
Comment:
when i close the excel file, then automatically to excel data is save the sql server database....

whether it is possible or not... it's possible please send me code....

Please send me my mail:puvaneswaranbe@yahoo.co.in


it's very urgent.....please help me......



Thanks

Buvaneswaran.J
Title: Nice Article   
Name: Ravinder
Date: 2008-04-13 10:46:37 PM
Comment:
Hey Thanks for the great information. I am new to SQL Server, had to to lot of job on servers without excel and this has saved me a lot of time.

Great Job.

Cheers,
Ravinder
Title: File Download - Security Warning   
Name: Srinivas Alwala
Date: 2008-04-03 7:08:50 AM
Comment:
Hi,

While downloading an Excel file in internet explorer, it prompts an file download window with open,save and cancel buttons.
On clicking on open button, it gives an error as "Could not open 'filepathtoopen'".
Pls let me know how to rectify this error.

Regards,
Srinivas Alwala
Email : srinivas.alwala@yahoo.com
Title: Generating Excel File Using SQL Server 2000   
Name: Francis
Date: 2008-04-01 9:39:58 PM
Comment:
I am using SQL 2005 on a 64 bit platform. The 'Microsoft.Jet.OLEDB.4.0' provider is not working producing an error of The OLE DB provider "Microsoft.Jet.OLEDB.4.0" has not been registered. I believe this is a 64bit OS issue. However, I have an urgent need to be able to delete and refill Excel files from my SQL code. Any ideas as to how I can manage this with our system?

Thanks
Title: OLE DB error trace   
Name: Karthik
Date: 2008-02-27 2:33:00 AM
Comment:
[OLE/DB provider returned message: Could not find installable ISAM.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: ].
Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
Title: numbers are being stored on the sheet as text   
Name: Nat
Date: 2007-12-20 12:32:41 PM
Comment:
PLEASE HELP: The data is arriving in the Excel file but... the numbers are being stored on the sheet as text. Unfortunately the formatting of the TEMPLATE file does not work! Accounting can not use those files for analysis.

Any other ideas???
Title: Re : enable the use of 'Ad Hoc Distributed Queries'   
Name: Author
Date: 2007-12-14 5:19:35 AM
Comment:
SQL Server 2005 is more secured than SQL Server 2000 and by default it blocks Distributed Queries, Shell Command etc. So to enable 'Ad Hoc Distributed Queries', Please run the following commands as Administrator.

EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO
Title: enable the use of 'Ad Hoc Distributed Queries'   
Name: Nat
Date: 2007-12-13 10:59:19 AM
Comment:
Please note, on SQL 2005 you may need modify the configuration a little. See below the warning I've got:


Msg 15281, Level 16, State 1, Line 1
SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.
Title: How to open Excel file which is already generated??   
Name: Shailesh
Date: 2007-11-21 11:28:55 PM
Comment:
I can't open excel file from asp which is already generated...plzzz help me..

thanx

regards,
Shailesh
Title: MS-Excel file executing but not showing the data   
Name: Abdul Majeed
Date: 2007-11-10 3:29:50 AM
Comment:
My problem is excel file not opening directly, Excel application working fine but not showing file data. if i open excel files Manually it then showing file data,
I have reinstalled excel then also the same problem.

thank and best regards
majeed
Title: how+to+generate+excel+file+using+vbscript+in+asp   
Name: saurabh agrawal
Date: 2007-10-25 3:06:11 AM
Comment:
hello all,

my problem is that, i want to genrate one file in .xls format. the record which is exist in excel sheet ,comes via sql query.
plz give the excet format with correct syntex.
tanks and best regards
saurabh
Title: How to add instance of excelsheet in Sql database   
Name: safi1981
Date: 2007-09-04 2:25:49 PM
Comment:
Hi
This artical is really gud but do u have any idea how us save the instance of excelsheet in sql server database.
Title: How to create Excel file   
Name: Rohith
Date: 2007-08-06 5:11:02 AM
Comment:
i have created excel file using Excel9.0 object but at the client place its 10.0 its giving error as "Cannot create Activex component" pls Help me

bluefoster@rediffmail.com
Title: How to create Excel file   
Name: Rohith
Date: 2007-08-06 5:07:59 AM
Comment:
i have created excel file using Excel9.0 object but at the client place its 10.0 its giving error as "Cannot create Activex component" pls Help me
Title: Query   
Name: Prashant
Date: 2007-06-07 6:30:46 AM
Comment:
Hi,
I have one problem i want to merge 10 excel files in to singel excel,excel file contain images also .
but i have to do all this in my .net code.
is there any way to do that

choudhary_prashant@rediffmail.com
Title: Response to Creation of Excel file without Data   
Name: Rosemary
Date: 2007-05-29 5:44:22 AM
Comment:
It's Excellent Article but I could not get the data in the excell file after running the procedure
Title: Response to the error by Tirthesh Jain   
Name: Chaitu
Date: 2007-05-10 9:25:59 AM
Comment:
Hello friends,

While executing stored procedure
if your Specified excel sheet is open then only you will get the following error

"OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider did not give any information about the error.
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: The provider did not give any information about the error.].
"
Just Checkout

Regards
Chaitu
Title: Got An error   
Name: Tirthesh Jain
Date: 2007-04-26 3:08:21 AM
Comment:
While running the same Proc I am getting the under written Error Given by SQL Server 2000 and I am using the 2003 Excel version.

"OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider did not give any information about the error.
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: The provider did not give any information about the error.].
"
Title: Great Script   
Name: Ariest
Date: 2007-04-14 7:42:18 AM
Comment:
Good article Aravind
Title: Nice one   
Name: Rama Krishna
Date: 2007-02-05 12:43:29 AM
Comment:
A good article Mr Aravind. Keep it up.
Title: Error   
Name: Narendra
Date: 2007-01-27 3:28:42 PM
Comment:
I have created the Template of the Table structure, changed the column names and Table name. Run the scripts in Different Database. When I execute the procedure I got the following error:
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' supplied invalid metadata for column 'item'. The data type is not supported.
OLE DB error trace [Non-interface error: Column 'item' (ordinal 1) of object 'SELECT item,Warehouse,Quantity FROM [Sheet1$]' reported an unsupported value for DBTYPE of 0].


Please can you help me to solve this error.

My email id is mail2narendra@gmail.com

Thanks in advance.

Regards:
Narendra
Title: Nice One   
Name: Narendra Babu A
Date: 2007-01-27 2:39:21 PM
Comment:
Thanks a lot. It helps me a lot.
Title: Error   
Name: Sreeni
Date: 2007-01-23 7:20:51 AM
Comment:
i got the following error while executing your code for my xl template,

Server: Msg 7354, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' supplied invalid metadata for column 'SNo'. The data type is not supported.

Shall you help me to solve this problem?

Thanks,
Sreeni
Title: Fantastic   
Name: Prajeesh V
Date: 2007-01-18 1:30:19 AM
Comment:
Its really fantastic... Very much helpful to SQL Programmers.


Regards,
Praji
Title: Length of character in excel   
Name: ?
Date: 2007-01-15 11:05:01 AM
Comment:
This is very good article, when i tried this, i am getting an error if the number of the charters in the column inserting inot excel is more then 450. Any idea why it is causing this problem, any help is appreciated.
Title: Nice and Easy for any user   
Name: Neel
Date: 2007-01-08 4:24:14 AM
Comment:
Hi Aravind,

Thanks for ur nice article. Its realy useful and easy to use for everybody. we expect more from u..

One question, is it possible to generate such file from recordset with some data menipulation?

Thanks again
Neel
Title: Mr.   
Name: Vikram Singh
Date: 2006-12-21 12:17:06 AM
Comment:
Its really cool but if possible please also try to put some internal overview on commands like : "MASTER..XP_CMDSHELL"
thnx
Title: a Question   
Name: Adnan
Date: 2006-12-14 8:48:32 AM
Comment:
Everything is good except i DO NOT want the column headers to show in the excel sheet. How do I go about doing that ?
Title: Excellent man!!!   
Name: Beny
Date: 2006-11-29 1:25:18 AM
Comment:
Ive been searching the net for this kind of excel generation for quite a long time....Keep up the goog work..!!!
Excellent method...Is it possible for cross-mapping also??
Title: Mr   
Name: Prabhu
Date: 2006-11-14 1:35:34 PM
Comment:
It is a very useful article.
Title: good one   
Name: cecil
Date: 2006-10-29 10:33:21 PM
Comment:
Really a good one.
Title: if file path contain space, giving error   
Name: bhupal
Date: 2006-10-24 7:45:46 AM
Comment:
if excel file path contains spaces its giving error.
Title: Getting error while executing SP   
Name: Anju Pandey
Date: 2006-09-13 1:21:34 AM
Comment:
while executing SP_Excelman stored prcedure I am getting this error
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider did not give any information about the error.
Can you please help me out?
Thanks.
Title: Thanks   
Name: Rani
Date: 2006-09-12 4:03:23 PM
Comment:
I got an emergency request to do exactly what is in this article and this article was the life saver for me. thank you so much Arvind for sharing this good article with us.
Title: thanks   
Name: Ilango
Date: 2006-08-28 3:05:04 AM
Comment:
This is a very good example for who directly extract the large amount of data from SQL server then store stright to the Excel file.
Title: Prabhu rk   
Name: Prabhu Rk
Date: 2006-08-23 6:53:41 AM
Comment:
Excellent article ... !!!!
Title: Excellent, cute, flamboyant code   
Name: Jinesh Aiyer Madhavan
Date: 2006-08-04 2:37:15 AM
Comment:
Excellent, cute, flamboyant code
Title: Very Good   
Name: Deependra Joshi
Date: 2006-08-03 8:19:40 AM
Comment:
This is really very nice article. Since a week I was looking for the same.

Thank you.
Title: Mr.   
Name: Farrukh
Date: 2006-07-25 2:02:57 AM
Comment:
Realy very very nice article.
Title: Very Good   
Name: Prabhakar.T
Date: 2006-07-05 10:32:06 AM
Comment:
Really Nice article Keep it up.

thks and Regards,
Prabhakar.T
Title: Reply for Fabio   
Name: Aravind Kumar - Author
Date: 2006-07-05 1:11:09 AM
Comment:
I suspect that the "sa" user id has been restricted for Linked Server access in your SQL Server. Anyway try this link for solution - http://support.microsoft.com/default.aspx?scid=285833
Title: Error running   
Name: Fabio Nunes de Souza
Date: 2006-07-04 9:41:27 AM
Comment:
error.

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. Authentication failed.
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80040e4d: Authentication failed.].
Title: Good!   
Name: Umesh Kumar
Date: 2006-06-27 6:37:25 AM
Comment:
Iam generating reports in excel and am having some problem in closing the excel instance after generating reports. I think this article may help a lot to me. Thanks Aravind!. Shall you help me to resolve my problem with this.
Title: Thank U   
Name: Bala
Date: 2006-06-27 5:57:25 AM
Comment:
Very Usefull,,,,Thank U.......
Title: Mr.   
Name: Ashvani
Date: 2006-06-26 11:06:03 AM
Comment:
Really nice article and helped me lot.

Thanks & Regards
Ashvani
Title: good   
Name: krishnamoorty
Date: 2006-06-26 12:26:16 AM
Comment:
very useful article for all.

thks.
krish
Title: Good One   
Name: Hamzah
Date: 2006-06-23 1:37:22 PM
Comment:
Nice article. Just a quick question. Is there any way you can create Test.xls on fly using the CMDSHELL.(Dynamically)

Thanks
Hamzah
Title: Ms.   
Name: Malar
Date: 2006-06-22 5:59:00 AM
Comment:
Really nice one!!!!! Very useful...






Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2017 ASPAlliance.com  |  Page Processed at 2017-09-22 6:26:25 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search