Cannot Get File Written with OSQL and xp_Cmdshell via APP
-
What am I missing? File will NOT get produced by application.
I have an SP that sucessfully creates a row for each record that I need to place into a text file on our domain file system. I have successfully worked around problems using master.dbo.xp_Cmdshell by following MS Article 890775.
The application that is calling the SP successfully runs the rest of the SP but the file is never produced. When I as an admin run this SP either via Query Analyzer or via the application, the file is allways successfully created. Additionally, both the user running the application and the account used by the SQL Server Agent (Proxy) can see the folder and file via windows explorer.
--OSQL Options Used Explanation:
/*
OSQL Options Used Explanation:
-E Passthrough authentication
-Q Process specified query
-S Process on specified server
-d Process on specified database
-h-1 Process output without column headings
-w670 Process output to specified width
-s"" Process output with specified (none) character as column spacer
-o Process output to file (location)
*/
EXEC @status = master.dbo.xp_cmdshell 'osql -E -Q"SELECT ProviderFileOutput FROM MyDatabase.dbo.ProviderFile ORDER BY ProviderFileOutput" -SMyServer\DB1 -dMyDatabase -h-1 -n -w670 -s"" -o"\\domain.com\dfs\Common\ABC\Company\SQLOutputFiles\SGI0000.txt"'
-
When you say the other users can "see" the file & folder, do you mean that they have also got Write and Delete permissions in that folder (and on the file), i.e. change control?
-
Paul - Thanks!
The proxy account I was using for the SQL Agent Service did not have the correct permissions on the folder in which the file was being written.
-
Excellent - happy to help.
I've been caught out by this one many times before!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply