xp_cmdshell, mapped drives, and UNIX shares

  • A SQL Server job that currently works gets the date of a file on a UNIX share using the following:

    CREATE TABLE #cmd_result(output varchar(100));

    INSERT #cmd_result

    EXEC master.dbo.xp_cmdshell 'dir \\srvname\path\table.dbf /T:A';

    That no longer works (errors like "path\file is not a valid path") because that UNIX share requires a username/password to access the share. While logged into the SQL Server with a service account, I am able to map a drive to the share as G: and browse to the particular dbf file, open it, modify, save, etc.

    As a test I created a linked server with the following CREATE TO script:

    /****** Object: LinkedServer [ADVDBF] Script Date: 12/16/2009 12:38:41 ******/

    EXEC master.dbo.sp_addlinkedserver @server = N'ADVDBF', @srvproduct=N'Microsoft Jet', @provider=N'Microsoft.Jet.OLEDB.4.0', @datasrc=N'G:\path', @provstr=N'dBase 5.0'

    I can sucecssfully query the linked server table as long as the G: drive is mapped:

    SELECT * FROM ADVDBF...table

    I modified the SQL Server job to be the following:

    INSERT #cmd_result

    EXEC master.dbo.xp_cmdshell 'dir G:\path\table.dbf /T:A';

    But the #cmd_result.output is now "The system cannot find the path specified."

    Several features exhibit similar errors: xp_cmdshell, email queries via dbmail, & queries in scheduled jobs.

    I'm pretty sure the problem is the logged in user's mapped drive is not available to SQL Server. Any ideas on how to deal with this: Either by getting SQL Server services to use the logged in service account's mapped drives, or connect it to a password protected UNIX share?

  • I found a workaround. I put the following into a batch file:

    net use g: \\server\share /user:srv\user pword

    dir g:\path\table.dbf /T:A

    net use g: /delete

    Then changed xp_cmdshell to run the batch file instead:

    EXEC master.dbo.xp_cmdshell 'c:\batchfile.bat';

    Thanks for looking.

Viewing 2 posts - 1 through 1 (of 1 total)

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