dtsrun works from command prompt, but hangs from Query Analyzer RSS

• • Reply
  

  VanessaEHL

  Member

  15 Points

  3 Posts

  dtsrun works from command prompt, but hangs from Query Analyzer

  Jul 17, 2006 07:53 PM|LINK

  I have a dts that works from the command prompt, but hangs in Query Analyzer.  Here's the code inside Query Analyzer:
  exec master..xp_cmdshell 'dtsrun /S BFHSQL4 /N vhl_dts_14144b /E'
  
  From the command prompt, it's just:
  dtsrun /S BFHSQL4 /N vhl_dts_14144b /E
  
  Any ideas what's wrong?  Thanks for your help!
  
• • Reply
  

  Iori_Jay

  Star

  12940 Points

  2450 Posts

  Re: dtsrun works from command prompt, but hangs from Query Analyzer

  Jul 18, 2006 08:16 AM|LINK

  Did the dtsrun cause SQL Server hang or just Query Analyzer hang? Did you got any error message when executing the package from Query Analyzer? You need more information for troubleshooting: use SQL Profiler to respectively capture a trace when execute dtsrun to the same package from commandline and Query Analyzer.
  Welcome to my SQL/ASPNET forum for Chinese
  http://51up.org/bbs/forumdisplay.php?fid=38
• • Reply
  

  VanessaEHL

  Member

  15 Points

  3 Posts

  Re: dtsrun works from command prompt, but hangs from Query Analyzer

  Jul 18, 2006 11:52 AM|LINK

  Thanks for your response.  I guess hangs might not be the right word.  There are no error messages.  Query Analyzer just tries to execute the package forever.  It takes less than a minute to run from the command prompt.  I've let it run for 1/2 hour in Query Analyzer before cancelling the query, then it takes several minutes to cancel.  Other packages run fine with the same syntax.  I think the problem is with this particular package.  The dts just runs an activeX script, as follows:
  Function Main()
      Dim fileName
  
      fileName = "\\mypath\myExcelFile.xls"
  
      dim excelObject
      set excelObject = CreateObject("Excel.Application")
  
      excelObject.workbooks.open fileName
  
      excelObject.Quit
      set excelObject = Nothing
  
      Main = DTSTaskExecResult_Success
  End Function
  
  I'm not really familiar with SQL Profiler.  Do you still think using it will be helpful for this scenario?  Thanks again for your help.
  
• • Reply
  

  VanessaEHL

  Member

  15 Points

  3 Posts

  Re: dtsrun works from command prompt, but hangs from Query Analyzer

  Jul 18, 2006 03:03 PM|LINK

  Turns out that there was code inside the Excel file that was trying to save a file to a path that didn't exist.  I fixed the path and now everything works fine.