Permission on a file in a network drive

[seaport]

I am trying to use OLE automation to get the last modified date of a file located in a mapped drive on the SQL Server computer. The network is peer-to-peer. I logged on the SQL server as the administrator. The SQL Server and the workstation sharing its folder have the same password for the administrator account.

When I run the following code, I can not get the file object. The error output contains only nulls. The variable @FilePath1 contains full network path like "\\jack\sharedfolder\test.bak".

I know the code has no error since it worked for another network path, which points to a file on a network storage device's public folder.

I ran a vbscript on the SQL server and was able to get the file object.

I do not know where went wrong. As I understand, in a peer-to-peer network, if two computers have the same user name and password, then a user from one computer should be able to access another computer without problems.

Here is my code:

EXEC @hr=sp_OACreate 'Scripting.FileSystemObject', @objFSys out

-- get file

EXEC @hr=sp_OAMethod @objFSys, 'GetFile', @obj out, @FilePath1; -- step 200

IF @hr <> 0

BEGIN

EXEC sp_OAGetErrorInfo null, @ErrSrc OUT, @ErrDesc OUT

select @msg=@msg + char(10) + 'Step 200: ' + char(10) +

'Err Source: ' + coalesce(@ErrSrc,'') + char(10) +

'Err Description: ' + coalesce(@ErrDesc,'')

END

Any suggestions?

 

[TheBugSlayer]

Does the user in question the proper privileges on Computer B? Check the network path that work and see what kind of permissions the user has there that he does not have on the path where the program is failing.

MCITP SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[seaport]

The SQL Server and the Computer B are in a workgroup, not a domain.
Both computers have the administrator account with the same password.
When logging in as the administrator (as I've been doing) on the SQL Server, I had full control on the shared folder from the computer B through the Windows Explorer. I even wrote a vbscript to get the file lastmodified date and the script worked.

I am sure there is no typo (like the network path) in my code.

Right now I am thinking about a workaround. I can use an vbscript, which is located on the SQL server drive, to read the lastmodified info (of the file at the shared folder), and write the info to a txt file on the SQL server drive. Then I can use the OLE Automation to get the info from the text file.

I wonder this is a bug on the SQL 2005. I will make another test in a domain network.