April 1, 2009 at 8:38 am
Hi,
I do hope that someone can assist as I am going mad.
I have an ODBC connection configured on my SQL server that points to an Excel file on a different file server called Estimates and a DTS package that populates a table from the spreadsheet called dts_Estimates. The DTS Package runs fine when executed on the SQL box via the DTSrun statement in a cmd shell.
I have a stored proc that calls the dts package via xp_cmdshell with the following syntax;
exec master.dbo.xp_cmdshell "DTSRun /S servername /E /N dts_Estimates", no_output.
When this stored proc is run through Query Analyser by a domain user with rights to execute the procedure (and access the network file) it runs but no data is brought into the table - if I reconfigure the ODBC connection to point to the a copy of the file on the SQL box and do the same all works fine.
I can only conclude that permissions are the issue and the context in which the DTS package is executed is the problem.
If the stored proc is run without the no_output flag I get the following as part of the error;
Error string: [Microsoft][ODBC Excel Driver] '(unknown)' 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.
This indicates to me that the ODBC connection isn't valid in the user context i.e. the network drive where the files are contained is not mapped when the procedure runs?
I have a SQL Agent Proxy account (domain) configured (to allow xp_cmdshell to be executed without sysadmin rights) and have given this account rights to access the fileshare on the basis that I thought this account might be the context under which the DTS package is executed - doesn't seem to work however.
Does anyone know why I cannot get this to work?
Regards,
Simon
April 1, 2009 at 11:36 am
I'm not sure why, but I think you're on the right path. There's definitely a rights/permissions issue. Have you tried running the xp_cmdshell and writing something like a DIR or other command to a file to see what context there is? maybe print out some environment vars to a file?
dir z:\ >> test.txt
April 2, 2009 at 1:18 am
It's so obvious it's frightening!
The call to DTSRun from xp_cmdshell is under the context of the Agent Proxy. When the stored procedure executes DTSrun hooks into the ODBC connection however this connection is configured using a mapped network drive (on intial inspection this appears to be the only way to define a System DSN ODBC connection) and hence the path is 'unknown' i.e. the network drive doesn't exist for the Proxy login and hence the ODBC connection is invalid and the DTS import fails.
Recreating the ODBC connection using a UNC path to the file allows the connection to work for the proxy and resolves the issue.
As I stated earlier it appears that System DSN ODBC connections can only use mapped network drives (T:\file.xls) however doing the following creates a UNC (\\servername\file.xls) connection;
In the ODBC connection hit the 'select workbook' button
Click the 'Network' button
Pick 'None' for the drive letter assignment
Click 'browse' and locate the file in question
The ODBC connection will now use the UNC path.
I do hope this helps someone!
Simon
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply