Last post Sep 27, 2011 02:26 AM by padwalpriyanka10
Oct 27, 2008 06:00 AM|sunny74|LINK
I am trying to export data from sql server 2000 to MS Excel 2007 and also to import data from EXcel to Sql Server 2000 using DTS. I searched the Internet but all the articles is about using the wizard. I want to Invoke the DTS API
and do the job. How to invoke the DTS API in order to do this?
Is using DTS the best method especially when it comes to exporting/Importing bulk records ie about 2,00,000 records in a single shot.
Similarly I need to do export/import between SQL server 2000 and MS Access 2007.
Pls suggest the best method to do this so that the solution can handle bulk data in a very short span of time.
Thanks in advance,
Oct 29, 2008 05:27 AM|Jian Kang - MSFT|LINK
I think you can import/export data between SQL Server and Excel files without DTS by using SQL Server distributed queries and BCP Utility.
Please refer to the links belowL
How to import data from Excel to SQL Server
Oct 29, 2008 07:14 AM|sunny74|LINK
Thanks for your reply.
Regarding export of data from sql server to Excel you have given a link where u r saying to create a stored procedure and inside that Execute the the proc XP_Cmdshell which in turns executes the BCP utility. But the proc xp_Cmdshell is non-existent in SQl
So what proc shud I use in order to get the same effect.
Secondly for export of data from sql Server to Excel, Access can I use the distributed queries like OpenRowset,OpenDataSource.If so send me some links where it is shown with examples.I tried it myself but it is giving error 7366, so I thought that it may
not be working in case of export but works only in case of import.
Oct 29, 2008 08:01 AM|sunny74|LINK
I executed the following query in the query analyser.My machine is having only the sql server client installed and the server is on another machine.
EXEC master..xp_cmdshell 'bcp "select * from AAI_trial.dbo.Buildings" queryout "\\172.16.121.203\Shared\abc.xlsx" -c -S"172.16.121.203" -U"sa" -P""'
The command executed succesfully and said 37101 rows copied.
The Excel File shud be situated on a shared folder either on the machine where the database is located or any other machine, right?
After the copy when I try to open the EXcel file abc.xslx it gives me an error message-
"Excel cannot open the file because the file foramt or extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file".
I get this message whenever I try to open the file after a data export by whichever method I do it.
So how to solve this problem.
Oct 29, 2008 08:58 PM|Jian Kang - MSFT|LINK
The warning message is a user-notification function that was added to Excel 2007. It is used to prevent unexpected problems for possible incompatible issue.
For more infromation, please visit the KB article below:
Oct 30, 2008 07:18 AM|sunny74|LINK
The code involving the xp_cmdshell requires a shared folder which is accessible to both the machines i.e the machine that has database installed and the one on which I am writing the program.
The problem is that there are shared folders on the machine having the database so the file is being created there.But the file is created without the headers.So I am running the normal code to create the Excel Application, workbook and then trying to save
it to that shared folder.But when I am trying to save this file it is showing error which says the file cannot be accessed.It is not able to create the file there.
So where is the problem?
Oct 30, 2008 09:31 PM|Jian Kang - MSFT|LINK
To export records to the Excel file with header by using BCP Utility, I think we can specify the column name manually.
Please refer to the statement below:
EXEC master..xp_cmdshell 'bcp "select ''categoryname'',''description'' union all select categoryname,description from northwind.dbo.categories" queryout "d:\test1.xls" -c -S"ServerName" -U"UserID" -P"Password"'
Oct 31, 2008 01:13 AM|sunny74|LINK
I have 2 queries:
1) Is there any stored procedure which can retrieve the column headers for a particular table and write it to the Excel file.My Idea is to run such a query first and then run the xp_cmdshell proc with the bcp command.
2) Is the bcp command able to create the Excel file only on the machine in which SQL server is installed ? Because when I try to create it on a shared folder on another machine in the network it returns an error.
In case it is able to create it on a shared folder on another machine pls tell me the syntax.
Oct 31, 2008 01:26 AM|Jian Kang - MSFT|LINK
You can get the column name as below:
DECLARE @col_name varchar(500)
SELECT @col_name=isnull(@col_name + ',','') + column_name
WHERE table_name = 'categories'
Oct 31, 2008 03:05 AM|sunny74|LINK
In my case the user will select a table and the contents of the table will have to written to the Excel file dynamically.
So in this case the stored procedure will have to be created at run time i.e dynamically. How to create a stored procedure dynamically?
Another problem is how to store the Column names in a single variable within the proc since it does not support array data type.
If the file name is test1.xls then while creating the workbook bcp is assigning the same name to the Sheet. How can I specify a different name
for the sheet? Does bcp allow me to specify a sheet name while exporting data?
Nov 01, 2008 06:31 AM|sunny74|LINK
I am trying to put the code for generating an eXcel file using xp_cmdshell into a stored procedure for the purpose of calling it from code.
The code was given by you sometime back.
EXEC master..xp_cmdshell 'bcp "select ''categoryname'',''description'' union all select categoryname,description from northwind.dbo.categories" queryout "d:\Shared\test1.xls" -c -S"172.16.121.203" -U"sa" -P""'
I tried to put it into a stored procedure but getting various errors.The sp creation code is as below:
CREATE PROCEDURE [dbo].[SpWriteExcel]
--create a variable @sql
--DECLARE @SQLStatement varchar(500)
DECLARE @SQLStatement1 varchar(500)
-- @SQLStatement = USE northwind
SELECT @SQLStatement1 = "EXEC master..xp_cmdshell 'bcp select ' 'categoryname' ' ,' 'description' ' union all select categoryname,description from northwind.dbo.categories queryout 'd:\Shared\test2.xls' -c -S\'172.16.121.203\' -U 'sa' -P' ''"
--Execute the SQL statement
The error is with SELECT @SQLStatement1. So I tried to change it in various ways but did not get any success.
One of the ways I changed it to is as below:
SELECT @SQLStatement1 = " EXEC master..xp_cmdshell 'bcp "+"select categoryname,description union all select categoryname,description from northwind.dbo.categories"+" queryout "+ "d:\Shared\test3.xls"+" -c "+"-S"+"172.16.121.203"+"-U"+"sa"+" -P"+""+"'"
Could you pls write a proc for me which contains the above command and which takes the database name, table name and the excel file path as parameters.
Thanks for your help.
Sep 27, 2011 02:26 AM|padwalpriyanka10|LINK
Plz Help Me!!!!!!!!!!!!!!
I want " export Sql Server Database into MS Access "
Is there any code you have or any link plz let me know............Its very Urgent!!!