SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SQL table export to the Excel through sql query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sindhu sarah
Starting Member

United Arab Emirates
34 Posts

Posted - 09/25/2012 :  02:54:02  Show Profile  Reply with Quote
Dear all,

Please can anyone provide me the sample for exporting the table into excel throguh sql query.I tried with bcp and open row set but both did not work..

khtan
In (Som, Ni, Yak)

Singapore
17658 Posts

Posted - 09/25/2012 :  03:00:28  Show Profile  Reply with Quote
refer to http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926


KH
Time is always against us

Go to Top of Page

sindhu sarah
Starting Member

United Arab Emirates
34 Posts

Posted - 09/25/2012 :  07:33:09  Show Profile  Reply with Quote
Thanks for your response.I tried with sp_makewebtask.It threw an error saying The EXECUTE permission was denied on the object 'sp_makewebtask', database 'mssqlsystemresource', schema 'sys'.
Then i was excuted the other procedure with the xp_cmdshell.But it throw an error saying 'The EXECUTE permission was denied on the object 'xp_cmdshell', database 'mssqlsystemresource', schema 'sys'.'
Also i tried with BCP but it shows as incorrect syntax near '.'.
Kindly assist .
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17658 Posts

Posted - 09/25/2012 :  08:46:28  Show Profile  Reply with Quote
your Login does not have the required permission to execute it. You need to approach your DBA.

As for the BCP, you need to show us your full BCP command.


KH
Time is always against us

Go to Top of Page

sindhu sarah
Starting Member

United Arab Emirates
34 Posts

Posted - 09/25/2012 :  08:57:12  Show Profile  Reply with Quote
i have access to admin since i am logging as sql admin.But still its not creating.BCP command which i used is as below
bcp "select * from ezContracting..tscurrent" queryout
C:\Users\v\Documents\test.xls -f c:\bcp.fmt -T

please let me know whether the above syntax is correct.
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17658 Posts

Posted - 09/25/2012 :  10:50:46  Show Profile  Reply with Quote
Where did you execute the BCP command ?

you may refer to the BCP help for further information
http://msdn.microsoft.com/en-us/library/ms162802.aspx


KH
Time is always against us

Go to Top of Page

sindhu sarah
Starting Member

United Arab Emirates
34 Posts

Posted - 09/26/2012 :  00:42:24  Show Profile  Reply with Quote
i wanted to use open row set command since my requirement is to save the data in the existing excel file.

i used the following code
insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\Users\v\Documents\testing.xls;',
'SELECT * FROM Sheet1') select * from esDb.AxEntity

but its throwing an error stating Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".


please let me know the solution for the above issue
Go to Top of Page

sindhu sarah
Starting Member

United Arab Emirates
34 Posts

Posted - 09/26/2012 :  08:56:36  Show Profile  Reply with Quote
Please let me know whats wrong in the below statement.I am using SQL 2008 and Excel 2007

insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 12.0;Database=c:\Sample.xls;',
'SELECT Entity,Name,Cury FROM Emp') select Entity,Name,Cury from ezBusDb.AxEntity

Am getting an error message stating OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message
"Could not find installable ISAM.".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

and if i run below command then the statement keeps on running for 1 hour then i cancelled the query.

insert into OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;Database=c:\Sample.xls;',
'SELECT Entity,Name,Cury FROM Emp') select top 1 Entity,Name,Cury from ezBusDb.AxEntity
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000