December 16, 2009 at 2:45 pm
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?
December 16, 2009 at 4:44 pm
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