| Author |
 Topic  |
|
madhivanan
Premature Yak Congratulator
India
22461 Posts |
 Posted - 11/02/2006 : 19:55:16
|
quote:
Originally posted by sanjayanthan
quote:
Originally posted by madhivanan
I dont know where the problem is. Post this as new topic so that other members may help you
Madhivanan
Failing to plan is Planning to fail
i got work around solutions refer
http://www.dnzone.com/ShowDetail.asp?NewsId=234
sanjayanthan
Thanks for sharing that link
Madhivanan
Failing to plan is Planning to fail |
 |
|
|
madhivanan
Premature Yak Congratulator
India
22461 Posts |
Posted - 11/02/2006 : 19:59:19
|
Philkos, check whether the sheet name is sheet1 or sheet 1 (note that there is space)
Also the file should exist in the server where SQL Server is installed
Madhivanan
Failing to plan is Planning to fail |
|
|
|
heze
Posting Yak Master
USA
192 Posts |
Posted - 11/03/2006 : 19:10:07
|
hi I am trying to run
insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=\\myServer\myPart\myDira\myDirb\hterms.xls;',
'SELECT * FROM [Sheet1$]') select * from reports..terms
with the folloing results
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.
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.].
what can the problem be?
thanks
|
|
|
|
madhivanan
Premature Yak Congratulator
India
22461 Posts |
Posted - 11/03/2006 : 19:39:38
|
When you run that query make sure that EXCEL file is closed or it is not used by other applications
Madhivanan
Failing to plan is Planning to fail |
|
|
|
philkos
Starting Member
Kuwait
5 Posts |
Posted - 11/04/2006 : 00:28:27
|
Madhivanan
Sheet name is Sheet1 not Sheet 1 (that i used the correct name,but still it is not working)
Philkos
quote:
Originally posted by madhivanan
Philkos, check whether the sheet name is sheet1 or sheet 1 (note that there is space)
Also the file should exist in the server where SQL Server is installed
Madhivanan
Failing to plan is Planning to fail
|
|
|
|
madhivanan
Premature Yak Congratulator
India
22461 Posts |
Posted - 11/04/2006 : 04:13:41
|
If you have spelled it correctly, it would work. Check that again
Madhivanan
Failing to plan is Planning to fail |
|
|
|
funketekun
Constraint Violating Yak Guru
Australia
491 Posts |
Posted - 12/13/2006 : 14:54:31
|
| how do i do it with multiple excel files with different names and in different directories?? |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 12/13/2006 : 15:03:31
|
You use dynamic SQL.
Peter Larsson
Helsingborg, Sweden |
|
|
|
funketekun
Constraint Violating Yak Guru
Australia
491 Posts |
Posted - 12/13/2006 : 16:00:53
|
dynamic sql...?
declare @sql varchar (8000)
set @sql = 'select * into SQLServerTable FROM OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',
''Excel 8.0;Database=C:\*.xls;HDR=YES'',
''SELECT * FROM [Sheet1$]'')'
print (@sql)
exec (@sql)
|
Edited by - funketekun on 12/13/2006 16:01:05 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 12/13/2006 : 16:04:13
|
Exactly.
If you have a specific question, please feel free to post it in the forums.
Peter Larsson
Helsingborg, Sweden |
|
|
|
madhivanan
Premature Yak Congratulator
India
22461 Posts |
Posted - 12/14/2006 : 05:49:38
|
quote:
Originally posted by funketekun
dynamic sql...?
declare @sql varchar (8000)
set @sql = 'select * into SQLServerTable FROM OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',
''Excel 8.0;Database=C:\*.xls;HDR=YES'',
''SELECT * FROM [Sheet1$]'')'
print (@sql)
exec (@sql)
Although you executed it using Dynamic sql, it is actually static as path is hard-coded
Madhivanan
Failing to plan is Planning to fail |
|
|
|
madhivanan
Premature Yak Congratulator
India
22461 Posts |
Posted - 12/14/2006 : 05:52:11
|
<<
C:\*.xls
>>
Note that you need to supply the path not *. It seems as if you are trying Search in Windows system
Madhivanan
Failing to plan is Planning to fail |
|
|
|
madhivanan
Premature Yak Congratulator
India
22461 Posts |
Posted - 12/23/2006 : 09:06:38
|
quote:
Originally posted by heze
hi I am trying to run
insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=\\myServer\myPart\myDira\myDirb\hterms.xls;',
'SELECT * FROM [Sheet1$]') select * from reports..terms
with the folloing results
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.
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.].
what can the problem be?
thanks
Also refer this
http://support.microsoft.com/default.aspx?scid=kb;EN-US;296711
Madhivanan
Failing to plan is Planning to fail |
|
|
|
dollsangell
Starting Member
1 Posts |
Posted - 01/19/2007 : 09:42:05
|
Can any one tell me how to export reports/results after certain conditions are met ,into an excel sheet?????
|
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 01/19/2007 : 09:44:57
|
This entire topic is about that! You have 10 pages to read...
Peter Larsson
Helsingborg, Sweden |
|
|
|
spirit1
Cybernetic Yak Master
Slovenia
11741 Posts |
Posted - 01/19/2007 : 10:10:14
|
but peter... i don't want to read. it hurts too much! 
Go with the flow & have fun! Else fight the flow 
blog thingie: http://weblogs.sqlteam.com/mladenp |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 01/19/2007 : 10:15:09
|
Sadly true...
I wonder what they think about this forum? All get their question answered for free, without hiring a consultant.
I think that calls for some kind of effort from orignal poster, right?
Peter Larsson
Helsingborg, Sweden |
|
|
|
edvardas
Starting Member
Lithuania
1 Posts |
Posted - 03/01/2007 : 05:29:38
|
Hello I have tried to export data to excel, I do not get an errors, but the export not working.. what can the problem be?
Alter Procedure ExportToExcelSheet(@fileName varchar(255),@sheetName varchar(255), @ID int)
as
Declare @sql varchar(1000)
DECLARE @Name varchar(15)
Set @sql ='insert into OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',''Excel 8.0;Database='+@fileName+';HDR=YES'',''SELECT * From ['+@sheetName+'$F1:I1]'')'
select TimeDateStart3, TimeDateEnd3, Time, Status from tblResults where Time is not null and VehicleID = @ID
Exec (@sql)
exec ExportToExcelSheet @fileName='C:\Reports\Template.xls', @sheetName=@Name, @ID=@VeID |
|
|
|
praveenkjohn
Starting Member
India
9 Posts |
Posted - 03/15/2007 : 09:41:16
|
but what to do if i need to export query dynamically to Excel in which i have to generate the column names automatically in the excel sheet , the thing is i dont have to use T-SQL , any other way in SSIS or anything else
Regards
Praveen John
+91-9895074288
"Frankly, my dear, I don’t give a damn" |
|
|
|
spirit1
Cybernetic Yak Master
Slovenia
11741 Posts |
Posted - 03/15/2007 : 10:21:26
|
you can always use .net and create your excel sheet from there with excel interop.
_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp |
|
|
|
Topic |
|
Export to Excel
