Executing SQLCommand

Executing SQLCommand

• Marfig

  Paul,Thanks for your replies. I already understood that there was a permissioning problem going on since the error message was "Access to file denied". However, since I am an admin on the sql server box, and I have full permissions on the network forlder where the file resides, I could not understand why the authentication was not being passed.I the end I found that the windows sql service MSSQLSERVER was logged in as a different user, which also needed to have permissions on the network drive share where the file resides. This fixed the problem.Thanks for your help.Moshe.
  

• markp5511

  
  Below is an article I ran across. I think it should explain under what security context the package executes depending upon your scenario. Once you determine the identity of account under which the DTS package is executing you can provide full permissions (for that account) to the folder where the text (import) file is located.

  INF: How to Run a DTS Package as a Scheduled Job

  
  
  

• ashleyf

  Thanks for your quick answer.

  The problem is with the following:

  exec master.dbo.xp_cmdshell @RunPkg

  where @RunPkg is a string based on the dtsrun command which imports a fixed-width text file.

  The procedure works fine on our IT workstations, but cannot find either the package or the input file (I can't tell which) when run from the user's workstation. The dtsrun string by itself runs fine from the user's workstation.

  We don't want to put dtsrun as a VB function because we want the server to do it, and because that would require adding Enterprise Manager services to the user's configuration.

  It's not that we get an error. It's that the dtsrun doesn't operate as it should.

  
  

• VTDanoKim

  
  Could you indicate what the run time error is when you execute the command

  
  
  

• KttocS

  The only limitations would be related to whatever you are executing in the SqlCommand, not the fact that it's coming from SqlCommand. What does your CommandText look like, and what is the exact error message and call stack that you get Have you tried running Profiler on the SQL Server or checking the error log to see if any more information is available

  Thanks,
  Sarah

  
  
  

• rsb_mda

  
  Is the text file located on the workstation or a network resource From what I can determine there appears to be a security issue with respect to file access. It seems to be executing under a different identity when running from .NET, although I'm not sure at this point what it might be.

  One other question, this is a VB app and not an ASP.NET app correct

  
  
  

• tbh

  Paul,I am working with Tom on this issue. When executing the DTS package directly from enterprise manager the package runs correctly. However, when calling the package via the stored prodedure (either from the .net application, or from Query Analyzer) I get the following message. The code in the stored procedure that produced this message is {exec master.dbo.xp_cmdshell 'dtsrun /E /Slocalhost /NImportManForexCLSHosttrncs'} where ImportManForexCLSHosttrncs is the name of the DTS package. <<DTSRun: Loading... DTSRun: Executing... DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart: Copy Data from hosttrnc to [ManForexCLS].[dbo].[tblHoldHosttrnc] Step DTSRun OnError: Copy Data from hosttrnc to [ManForexCLS].[dbo].[tblHoldHosttrnc] Step, Error = -2147467259 (80004005) Error string: Error opening datafile: Access is denied. Error source: Microsoft Data Transformation Services Flat File Rowset Provider Help file: DTSFFile.hlp Help context: 0 Error Detail Records: Error: 5 (5); Provider Error: 5 (5) Error string: Error opening datafile: Access is denied. Error source: Microsoft Data Transformation Services Flat File Rowset Provider Help file: DTSFFile.hlp Help context: 0 DTSRun OnFinish: Copy Data from hosttrnc to [ManForexCLS].[dbo].[tblHoldHosttrnc] Step DTSRun: Package execution complete. NULL>>
  
• Executing SQLCommand