August 8, 2007 at 6:11 am
OLE DB provider "MICROSOFT.JET.OLEDB.4.0" for linked server "(null)" returned message "'m:\AccountsMails\20070702\ACDB21.MDB' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.".
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)".
AND
OLE DB provider "MICROSOFT.JET.OLEDB.4.0" for linked server "(null)" returned message "Disk or network 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)".
August 8, 2007 at 6:40 am
You say you confirmed the file exists. Does it exist from SQL's perspective or yours? i.e. Can you see the file when logged in as SQL account.
Does xp_CMDShell 'dir "\\HMserver\EXTRANET\AccountsMails\20070702"' return the contents of the directory?
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
August 8, 2007 at 11:22 pm
Thank you for your immediate reply.
I am working on a XP MACHINE(BIJUTHOMAS) in which SQLSERVER2005 is installed. I also have another server HMSERVER with SQL2005. When I am login LOCALLY and execute
xp_CMDShell 'dir "\\HMserver\EXTRANET\AccountsMails\20070702"'
OUTPUT IS Access is denied. NULL
Here Openrowset is not working.
When I logged into HMSERVER and tried , I am getting Directory Listing and My openrowset query is working.
Now I understand that it is not a SQL 2005 bug but a authentication issue.
How is it possible to loging to a server BIJUTHOMAS and get details from a MDB file which is stored in a different server HMSERVER ( without loging into HMSERVER) using openrowset
August 9, 2007 at 12:29 am
What is the NT loging that SQL is using? i.e. the user account in the services. Let's assume it's SQLLogin.
If you logon to the server with that username, can you go start > run \\someUNCpath\?
From the CMD result, it is looks like SQL does not have access to the path.
You say when you logged on, it works. How did you log on? Your account or SQL's?
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
August 9, 2007 at 2:17 am
In my case local xp machine is BIJUTHOMAS(SQL2005 ) and Remote 2003 machine is HMSERVER where the MDB FILE is stored.
If I login locally to BIJUTHOMAS and execute the XP_CMDSHELL / openrowset command with UNCPATH for the file in HMSERVER, it gives error.
If I login to HMSERVER(where MDB is) and execute the XP_CMDSHELL / openrowset command with UNCPATH IT IS WORKING.
SO MY latest question is it possible to LOGIN locally on XP machine in one domain and execute the openrowset command for a MDB file in another domain.?
August 9, 2007 at 3:05 am
Yes it is. But you need the right access.
If SQL is running on your machine, how is it logging on? LocalSystem? Your username? If localsystem, no, it will not work.
If you username, are you allowed to see the UNC path? Can you go, on your machine, \\servername\someshare?
if the two machines are in two different domains, you need to speak to whoever manages the domains and get the server domain to trust your domain\username.
And of course, ensure your SQL service is logging on with the correct username.
Remeber, you loggin on to your XP machine means nothing to SQL server. It has it's own login.
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
August 9, 2007 at 5:22 am
- get an account that can access the given share. say domain\usera.
- start SQL with that account
or
- write a procedure in SQL that calls
xp_cmdshell 'net use M: \\server\share /user:domain\usera password_of_that_user '
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply