January 5, 2009 at 1:44 pm
I am using SQL Server 2005 Management Studio to run the following statement on SQL Server 2000 (dev box).
SELECT F1, F2, F5, GETDATE(), F10,F11,F12,F13,F14,F15,F16,F17,F18,F19,F20,F21,F22,F23,F24,F25,F26,F27,F28 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=\\NetworkPath\D22ml.xls;HDR=YES', 'SELECT * FROM [Committed$]') WHERE F1 IS NOT NULL AND F2 IS NOT NULL;
Excel file is located on network folder which is PUBLIC and NetworkPath is a place holder here for the actual path.
The problem is this:
When I run this statement while I am loggged on to the machine (using Remote Desktop and Management Studio 2005) where the SQL Server is installed, I get the result without any errors. However, when I run it on my machine using Management Studio 2005, I get the following 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.].
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.
Strange thing is that I did not have this problem on the production server. I have used this statement (in a stored proc) in a C# application as well as in Management studio....until today. All of a sudden, production server started to behave like dev server (as mentioned above).
Can someone help me resolve this issue?
TIA
Khalique
January 5, 2009 at 1:53 pm
Can you open the spreadsheet using the \\NetworkPath\D22ml.xls bit from your machine, possibly from the run command? that will verify you have the appropriate rights to the share.
No chance the user account your SQL Server is running as is locked out, or password is expired or some other such thing is there?
-Luke.
January 5, 2009 at 2:01 pm
Check that the file is not already open.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 5, 2009 at 2:06 pm
Well, I've never seen it for OPENROWSET, but it sounds like you are being bitten by the "Two-Hop Rule". This thread has a good explanation of it for Linked Servers: http://www.sqlservercentral.com/Forums/Topic471172-359-1.aspx.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 5, 2009 at 2:14 pm
I can browse to the network folder and click on the file name to open it in Excel using Windows explorer.
I have no problem in using SQL Management Studio and doing all kind of stuff on either dev or prod server.
January 5, 2009 at 2:16 pm
The Excel file is not open by any other program.
January 5, 2009 at 2:46 pm
RBarryYoung (1/5/2009)
Well, I've never seen it for OPENROWSET, but it sounds like you are being bitten by the "Two-Hop Rule". This thread has a good explanation of it for Linked Servers: http://www.sqlservercentral.com/Forums/Topic471172-359-1.aspx.
I looked into the explanation provided by the link above. However, I think the isseue I am having is a bit different. Here is a little more detail:
I first had this problem on Dev box. I tried various things but couldn't resolve. Then, I tried the same statement on prod box and it worked. I thought there were some differneces between the two servers.. may be some configuration differences. However, I didn't care to find those differneces and continued my developemt using prod server. I used this statement in SSMS on my box (with Excel file on a network share with PUBLIC access) and also put this command in a sp and executed in SSMS and also used this sp in a C# app. It was working fine until last year (new year eve actually). However, today when I resumed work, prod box is giving me the same error like dev box and I am stuck.
January 5, 2009 at 3:02 pm
And does it still work if you remote into the prod server and run SSMS there?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 5, 2009 at 3:27 pm
RBarryYoung (1/5/2009)
And does it still work if you remote into the prod server and run SSMS there?
Yes, it works that way on both prod and dev servers.
And I just tested that if I move Excel file from network share to server, it works; likely pointing to the cause you mentioned. However, there must be some expanation why it worked before.
This is the first time I am using OPENROWSET. Therfore, first I tried and tested it using SSMS. Then I moved it to a sp and tested sp in SSMS. Then I used it in my C# app for over 2 weeks. And all form my local box.
January 5, 2009 at 3:44 pm
rehman (1/5/2009)
RBarryYoung (1/5/2009)
And does it still work if you remote into the prod server and run SSMS there?Yes, it works that way on both prod and dev servers.
And I just tested that if I move Excel file from network share to server, it works; likely pointing to the cause you mentioned. However, there must be some expanation why it worked before...
This sounds very much like the same problem.
Here is an excellent article by Brian Kelley on getting Kerberos to work (which is the only solution that MS will offer for this problem). It may help to illuminate this for you. http://www.sqlservercentral.com/articles/Security/65169/
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply