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 |
|
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 . |
|
|
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] |
|
|
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 -Tplease let me know whether the above syntax is correct. |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
|
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 codeinsert into OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\Users\v\Documents\testing.xls;', 'SELECT * FROM Sheet1') select * from esDb.AxEntitybut its throwing an error stating Msg 7399, Level 16, State 1, Line 1The 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 1Cannot 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 |
|
|
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 2007insert 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 1Cannot 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 |
|
|
|