| Author |
 Topic  |
|
kalenthirababu
Starting Member
1 Posts |
 Posted - 03/20/2007 : 21:33:18
|
| i am getting "The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object".error in To export data from Excel to existing SQL Server table how over come this error.anybody help me. my mail id:pkbabu1212@gmail.com |
 |
|
|
DestinyJack
Starting Member
22 Posts |
Posted - 03/27/2007 : 02:41:50
|
I run the script:
INSERT INTO
OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:\TmpWork\Testing.xls;HDR=YES',
'SELECT * FROM [sheet1$]')
Select * from Pcat
but end up it doesn't work and I got error like this:
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.].
Anyone knows what's wrong here? |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 03/27/2007 : 02:54:48
|
quote:
Originally posted by Peso
The OPENROWSET syntax with Jet provider does not give you full error description when problem occurs, such as permission errors.
-- Using this code for a file with no appropriate permissions throws a general error
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 5.0;HDR=No;IMEX=0;Database=\\seludt2135\c$\documents and settings\selupln\desktop\book2.xls', 'select * from [Sheet1$a1:q50]')
If you have some error and you don't understand why, try using MSDASQL provider temporarily.
SELECT * FROM OPENROWSET('MSDASQL', 'Driver={Microsoft Excel Driver (*.xls)};DBQ=\\seludt2135\c$\documents and settings\selupln\desktop\book2.xls',
'SELECT * FROM [Sheet1$]')
Now most error desriptions are output. When you have resolved the error, switch back to Jet again.
Peter Larsson
Helsingborg, Sweden |
Edited by - SwePeso on 03/27/2007 07:16:30 |
|
|
|
DestinyJack
Starting Member
22 Posts |
Posted - 03/27/2007 : 03:46:48
|
| Got it. thanks |
|
|
|
madhivanan
Premature Yak Congratulator
India
22460 Posts |
Posted - 04/02/2007 : 02:48:12
|
quote:
Originally posted by dollsangell
Can any one tell me how to export reports/results after certain conditions are met ,into an excel sheet?????
All you need to do is applying WHERE condition
As said, read all replies posted here
Madhivanan
Failing to plan is Planning to fail |
|
|
|
Vegivore
Starting Member
2 Posts |
Posted - 04/06/2007 : 18:35:56
|
I've been using INSERT INTO OPENROWSET..... for a while and it's always worked fine.
We now want to launch it from an ASP.Net app, but the exact same SQL returns the following error on an cmd.ExecuteNonQuery statement. Any help?
The OLE DB provider \"Microsoft.Jet.OLEDB.4.0\" for linked server \"(null)\" reported an error.
The provider indicates that the user did not have the permission to perform the operation.
The OLE DB provider \"Microsoft.Jet.OLEDB.4.0\" for linked server \"(null)\" could not INSERT INTO table \"[Microsoft.Jet.OLEDB.4.0]\".
OLE DB provider \"Microsoft.Jet.OLEDB.4.0\" for linked server \"(null)\" returned message \"Cannot update.
Database or object is read-only. |
|
|
|
madhivanan
Premature Yak Congratulator
India
22460 Posts |
Posted - 04/07/2007 : 04:08:24
|
I think if you read all replies posted in this topic, you may find the answer
Madhivanan
Failing to plan is Planning to fail |
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16745 Posts |
Posted - 04/07/2007 : 04:23:53
|
quote:
Originally posted by madhivanan
I think if you read all replies posted in this topic, you may find the answer
Madhivanan
Failing to plan is Planning to fail
208 209 replies ? 
KH
|
Edited by - khtan on 04/07/2007 04:34:21 |
|
|
|
madhivanan
Premature Yak Congratulator
India
22460 Posts |
Posted - 04/07/2007 : 04:32:05
|
<<
208 replies ?
>>
It is dynamic 
Madhivanan
Failing to plan is Planning to fail |
|
|
|
Vegivore
Starting Member
2 Posts |
Posted - 04/07/2007 : 13:14:15
|
| Been through all 11 pages twice - didn't see anything related to setting permissions. Thanks anyway. |
|
|
|
madhivanan
Premature Yak Congratulator
India
22460 Posts |
|
|
YogeshDesai
Posting Yak Master
India
136 Posts |
Posted - 06/05/2007 : 05:14:03
|
Hi,
I am using oledb.net for inserting text values in excel sheet.
I have excel sheet with columns already present on the system.
Problem comes when text value exceeds 255 characters. I will be grateful for any help on this.
Yogesh V. Desai. | SQLDBA| |
|
|
|
madhivanan
Premature Yak Congratulator
India
22460 Posts |
Posted - 06/05/2007 : 09:47:18
|
Before exporting data to EXCEL format that cell to TEXT
Madhivanan
Failing to plan is Planning to fail |
|
|
|
YogeshDesai
Posting Yak Master
India
136 Posts |
Posted - 06/21/2007 : 08:06:04
|
quote:
Originally posted by madhivanan
Before exporting data to EXCEL format that cell to TEXT
Madhivanan
Failing to plan is Planning to fail
still not able to insert same error
Yogesh V. Desai. | SQLDBA| |
|
|
|
madhivanan
Premature Yak Congratulator
India
22460 Posts |
Posted - 06/21/2007 : 09:57:33
|
I am not sure why that error happens
Try merging the columns and see if all characters are exported to EXCEL cell
Madhivanan
Failing to plan is Planning to fail |
|
|
|
naadkhula
Starting Member
2 Posts |
Posted - 07/13/2007 : 13:57:35
|
Hi Madhivann, this is chinmmay from pune.
There is a nice blog for the export to excel
I am using visual studio 2005 and sql 2000 and also excel 2003 (11)
I am trying sql query
insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\report.xls;', 'SELECT * FROM [Sheet1$]') select * from " + rept_name + "
but its failing its giving error : OLE DB Provider reported an error
i even tried to create the excel sheet first then make the column heading as that of the sql table btu still getting the same error.
Can you please help me out. It is required for the completion of the project. so its urgent |
|
|
|
madhivanan
Premature Yak Congratulator
India
22460 Posts |
Posted - 07/14/2007 : 00:22:04
|
Is rept_name a table name?
Try
insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\report.xls;', 'SELECT * FROM [Sheet1$]') select * from rept_name
Madhivanan
Failing to plan is Planning to fail |
|
|
|
tommyonline
Starting Member
Germany
3 Posts |
Posted - 07/15/2007 : 16:15:41
|
Check out www.sqlscripter.com to export data to PDF/Excel.
It's free. |
|
|
|
sreeraj216
Starting Member
USA
4 Posts |
Posted - 07/15/2007 : 21:09:17
|
Hi everybody,
I am new to SQL server and client is using SQL server 2005.
I am trying to use OpenRowSet function for exporting data to excel file with the query:
INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\SQL Server\Emp_Personal_Data.xls;', 'SELECT * FROM [Sheet1$]') SELECT emp_ID, emp_Fname, emp_Lname, emp_DOJ
from Emp_Budget_Info.
It is throwing the error:
OpenRowset cannot be used as a destination with this query type.
And for Ad-Hoc Distributed queries, it is telling to refer Surface Area Configuration.
Can anybody please let me know how to fix this problem.
Thanks in Adveance
Sree Raj
|
|
|
|
sreeraj216
Starting Member
USA
4 Posts |
Posted - 07/15/2007 : 21:12:43
|
quote:
Originally posted by madhivanan
Apart from using DTS and Export wizard, we can also use this query to export data from SQL Server2000 to Excel
Create an Excel file named testing having the headers same as that of table columns and use this query
insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xls;',
'SELECT * FROM [SheetName$]') select * from SQLServerTable
To export data from Excel to new SQL Server table,
select *
into SQLServerTable FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xls;HDR=YES',
'SELECT * FROM [Sheet1$]')
To export data from Excel to existing SQL Server table,
Insert into SQLServerTable Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xls;HDR=YES',
'SELECT * FROM [SheetName$]')
Madhivanan
Failing to plan is Planning to fail
Hi everybody,
I am new to SQL server and client is using SQL server 2005.
I am trying to use OpenRowSet function for exporting data to excel file with the query:
INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\SQL Server\Emp_Personal_Data.xls;', 'SELECT * FROM [Sheet1$]') SELECT emp_ID, emp_Fname, emp_Lname, emp_DOJ
from Emp_Budget_Info.
It is throwing the error:
OpenRowset cannot be used as a destination with this query type.
And for Ad-Hoc Distributed queries, it is telling to refer Surface Area Configuration.
Can anybody please let me know how to fix this problem.
Thanks in Adveance
Sree Raj |
|
|
|
Topic |
|
Export to Excel
