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!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.|||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.|||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.
No comments:
Post a Comment