INSERT INTO OPENROWSET

Question

INSERT INTO OPENROWSET

Rame...

SSC Veteran

Points: 286
More actions
December 26, 2007 at 2:07 am

#70682

I have used the code like this.. but Still I recieve the Error message like this ..
Could not process object 'SELECT * from [testing$]'. The OLE DB provider 'Microsoft.Jet.OLEDB.4.0' indicates that the object has no columns.
OLE DB error trace [Non-interface error: OLE DB provider unable to process object, since the object has no columnsProviderName='Microsoft.Jet.OLEDB.4.0', Query=SELECT * from [testing$]'].
i would be very hopeful to u if you help me out
Rame...

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

jacob sebastian SSChampion Points: 11822 More actions · Answer 1

What is the TSQL statement that you are trying to execute? It looks like you are trying to read data from an excel sheet. But you have not posted the query that you are running.

.

Rame... SSC Veteran Points: 286 More actions · Answer 2

Hi

Thanks for your reply.

I am using this code inside the procedure,if I run the procedure the excel file should be created in specified location. I am new for SQL server 2000. If it is wrong please suggest me to do that..

Thanks.

Rame..

jacob sebastian SSChampion Points: 11822 More actions · Answer 3

Rame..,

From your first post, I understand that you are trying to read data from an excel sheet. But i did not find code which actually connects to the excel document (OPENDATASOURCE, OPENROWSET or something similar). That is the reason why I asked for the code.

Read this article which will help you out on this: http://www.databasejournal.com/features/mssql/article.php/3331881

.

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 4

There is a registry setting that prevents SQL Server from being able to use OpenRowSet... look in the registry for "DisallowAdhocAccess" and change it's value to zero.

Also, OpenRowSet creates temporary files in C:\Temp (or whatever the Temp/Tmp directory is defined as). The SQL Server login being used must have the privs to write there.

Last but not least, I know of no way to get it to work with Windows Authentication. The only way I've been able to get it to work is to use SQL Server Authentication.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Madhivanan-208264 SSCertifiable Points: 7516 More actions · Answer 5

Rame... (12/26/2007)
I have used the code like this.. but Still I recieve the Error message like this ..
Could not process object 'SELECT * from [testing$]'. The OLE DB provider 'Microsoft.Jet.OLEDB.4.0' indicates that the object has no columns.
OLE DB error trace [Non-interface error: OLE DB provider unable to process object, since the object has no columnsProviderName='Microsoft.Jet.OLEDB.4.0', Query=SELECT * from [testing$]'].
i would be very hopeful to u if you help me out
Rame...

Also make sure to read this fully

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

Madhivanan

Failing to plan is Planning to fail