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
 Script Library
 Export to Excel
 New Topic  Reply to Topic
 Printer Friendly
Previous Page | Next Page
Author Previous Topic Topic Next Topic
Page: of 30

kalenthirababu
Starting Member

1 Posts

Posted - 03/20/2007 :  21:33:18  Show Profile  Send kalenthirababu an AOL message  Click to see kalenthirababu's MSN Messenger address  Send kalenthirababu a Yahoo! Message  Reply with Quote
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
Go to Top of Page

DestinyJack
Starting Member

22 Posts

Posted - 03/27/2007 :  02:41:50  Show Profile  Reply with Quote
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?
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 03/27/2007 :  02:54:48  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

DestinyJack
Starting Member

22 Posts

Posted - 03/27/2007 :  03:46:48  Show Profile  Reply with Quote
Got it. thanks
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 04/02/2007 :  02:48:12  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

Vegivore
Starting Member

2 Posts

Posted - 04/06/2007 :  18:35:56  Show Profile  Reply with Quote
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.
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 04/07/2007 :  04:08:24  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
I think if you read all replies posted in this topic, you may find the answer

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17645 Posts

Posted - 04/07/2007 :  04:23:53  Show Profile  Reply with Quote
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
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 04/07/2007 :  04:32:05  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
<<
208 replies ?
>>

It is dynamic

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

Vegivore
Starting Member

2 Posts

Posted - 04/07/2007 :  13:14:15  Show Profile  Reply with Quote
Been through all 11 pages twice - didn't see anything related to setting permissions. Thanks anyway.
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 05/05/2007 :  03:55:58  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Try using Linked Server
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q306397

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

YogeshDesai
Posting Yak Master

India
136 Posts

Posted - 06/05/2007 :  05:14:03  Show Profile  Send YogeshDesai a Yahoo! Message  Reply with Quote
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|
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 06/05/2007 :  09:47:18  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Before exporting data to EXCEL format that cell to TEXT

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

YogeshDesai
Posting Yak Master

India
136 Posts

Posted - 06/21/2007 :  08:06:04  Show Profile  Send YogeshDesai a Yahoo! Message  Reply with Quote
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|
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 06/21/2007 :  09:57:33  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

naadkhula
Starting Member

2 Posts

Posted - 07/13/2007 :  13:57:35  Show Profile  Reply with Quote
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
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 07/14/2007 :  00:22:04  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

tommyonline
Starting Member

Germany
3 Posts

Posted - 07/15/2007 :  16:15:41  Show Profile  Visit tommyonline's Homepage  Reply with Quote
Check out www.sqlscripter.com to export data to PDF/Excel.
It's free.
Go to Top of Page

sreeraj216
Starting Member

USA
4 Posts

Posted - 07/15/2007 :  21:09:17  Show Profile  Reply with Quote
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

Go to Top of Page

sreeraj216
Starting Member

USA
4 Posts

Posted - 07/15/2007 :  21:12:43  Show Profile  Reply with Quote
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
Go to Top of Page
Page: of 30 Previous Topic Topic Next Topic  
Previous Page | Next Page
 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.14 seconds. Powered By: Snitz Forums 2000