problem on stored procedure

Question

0.00/5 (No votes)

See more:

Hi friends
This is my stored procedure

SQL

alter procedure sp_excelfortest
 as
-- declare @datavar varchar(200)
 declare @sql varchar(500)
-- set @datavar  = 'Excel 8.0;DATASOURCE=' + @filname

 set nocount on
  begin
  --create table #temptable (Date date ,Day varchar(30),Reason varchar(100))
  --set @sql = (SELECT *  FROM OPENDATASOURCE('Microsoft.jet.OLEDB.4.0','+@datavar +;HDR=YES','SELECT Data,Day,Reason FROM [Sheet1$]')
  Set @sql ='select * FROM OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',
 ''Excel 8.0;Database=D:\baskarbook1.xls;HDR=YES;'',
  ''SELECT * FROM [Sheet1$]'')'
  exec (@sql)
 end

This works onetime successfully but when i restart the sql server it shows some error.
The error is

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

I dont know how to solve it.and i red some article regarding with this error,but i couldnt get correct solution.please help me

Posted 23-Aug-12 22:26pm

baskaran chellasamy

Add a Solution

Comments

Kamalkant(kk) 24-Aug-12 4:32am

Which version of sql server have u used?

baskaran chellasamy 24-Aug-12 4:48am

sql server2008

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Kamalkant(kk) · Answer 1 · 2012-08-23T22:39:00

Solution 1

Try this query in sql server 2005 and set the Configuration--> sql server surface area configuration-->..

Posted 23-Aug-12 22:39pm

Kamalkant(kk)

pradiprenushe · Answer 2 · 2012-08-23T22:45:00

Solution 2

Possible reason is file is open, permission issue, file is locked for read.
Try close file if it is open & give some more permission (try giving full permission may be when first time file read it may be going in lock).
Go through these link
http://social.msdn.microsoft.com/Forums/en/sqldatabaseengine/thread/83300532-e780-4ac6-9a64-7246e58c253a
http://blogs.msdn.com/b/spike/archive/2008/07/23/ole-db-provider-microsoft-jet-oledb-4-0-for-linked-server-null-returned-message-unspecified-error.aspx

Posted 23-Aug-12 22:45pm

pradiprenushe

Comments

baskaran chellasamy 24-Aug-12 4:56am

I studied the above articles and check the permission on temp folder.all the user in my system has permission to write and read and everything. but still the error occurs.if the excel file is locked mean how can i unlocked

pradiprenushe 24-Aug-12 4:59am

1. Close xls file if it open
2. Right click xls file go to security add user Asp.net & give full permission to Asp.Net user.

baskaran chellasamy 24-Aug-12 5:01am

Now i created another one excel file to execute. but this time the new excel file is also not executed. the same error occurs.

pradiprenushe 24-Aug-12 5:12am

Are you sure sp working for first time?

baskaran chellasamy 24-Aug-12 5:14am

yes i am sure first time its working. then after i shut down and restart the system and then only its not working.

pradiprenushe 24-Aug-12 5:26am

Then when does it starts working again?

baskaran chellasamy 24-Aug-12 5:31am

no.when i was creating the procedure at first time, query is working. but now even i create new query with new excel table it doesnt working. the procedure is successfully compiled but when the excecution it doesnt working.

pradiprenushe 24-Aug-12 5:36am

Have you upgraded your system from 32 bit to 64 bit? What is version of xls? Have you upgrded it?

baskaran chellasamy 24-Aug-12 5:39am

now i restart sql server and run the above query it shows the same error with adding some extra information.
the error is
<pre>OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "The Microsoft Jet database engine could not find the object 'Sheet1$'. Make sure the object exists and that you spell its name and the path name correctly.".
Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".</pre>

pradiprenushe 24-Aug-12 5:45am

I think you are using some different version of xls. Can you check it once?

baskaran chellasamy 24-Aug-12 5:48am

i am using ms2007.how can i check whether it is 32 bit or 64 bit.and only 2007 verson is installed in my system and my sistem is 32 bit

pradiprenushe 24-Aug-12 5:59am

Try this
'Microsoft.ACE.OLEDB.12.0','EXCEL 12.0;
instead of 'Microsoft.Jet.OLEDB.4.0'', ''Excel 8.0