Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SQL table export to the Excel through sql query

Author  Topic 

sindhu sarah
Starting Member

34 Posts

Posted - 2012-09-25 : 02:54:02
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)

17689 Posts

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


KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

sindhu sarah
Starting Member

34 Posts

Posted - 2012-09-25 : 07:33:09
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)

17689 Posts

Posted - 2012-09-25 : 08:46:28
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
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

sindhu sarah
Starting Member

34 Posts

Posted - 2012-09-25 : 08:57:12
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)

17689 Posts

Posted - 2012-09-25 : 10:50:46
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
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

sindhu sarah
Starting Member

34 Posts

Posted - 2012-09-26 : 00:42:24
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

34 Posts

Posted - 2012-09-26 : 08:56:36
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
   

- Advertisement -