Need Help : SQL Server Jobs Hangs every day with No Error msg

• NirmalKatta

  Old Hand

  Points: 326

  More actions

  November 2, 2012 at 7:59 am

  #284347

  We have a job that creates a XML file ( for EDI Team ) , this job ran fine for first few runs and then started this issue , Job will not stop until we kill the SPID or restart SQL server / Agent.

  It was running fine for few runs after we restart SQL , but now even that is not working .

  Job internally executes a stored proc that prepares data and then do a BCP to file.

  Same code runs ( proc) in query window less than 45 seconds for 5K+ members.

  But in SQL Job it just runs , No end.

  SELECT @MemXml = (

  SELECT

  @header "Test_Header"

  ,

  (

  SELECT ISNULL(MemberData, '') ,

  --1. member

  ISNULL(PlanSpans, ''),

  ISNULL(flags, '') ,

  ISNULL(pcpspans, '') ,

  ISNULL(Langspans, '')

  --1. end member

  FROM XMLHolder

  FOR XML PATH('Test'), TYPE

  ) FOR XML PATH(''), TYPE , ROOT('Test'), ELEMENTS XSINIL

  )

  SET @MemXml.modify('delete //*[not(node())]');

  DECLARE @xmlroot AS VARCHAR(MAX)

  SET @xmlroot = CAST ( @MemXml AS VARCHAR(MAX))

  TRUNCATE TABLE Hnny_Feed_Mem

  INSERT Hnny_Feed_Mem

  SELECT @xmlroot

  SELECT @bcpCommand = 'bcp ' + DB_NAME() + '.dbo.Hnny_Feed_Mem out ' + @fileName + ' -c -t -T'

  PRINT @bcpCommand

  DECLARE @recordcount INT

  SELECT @recordcount = COUNT(*) FROM Hnny_Feed_Mem ( NOLOCK)

  IF (@recordcount > 0 )

  EXEC master..xp_cmdshell @bcpCommand

  Please help.

• Steve Jones - SSC Editor

  SSC Guru

  Points: 738743

  More actions

  November 2, 2012 at 9:36 am

  #1555552

  If you print @Command, can you run it from a command window?

  Have you checked the proxy that runs xp_cmdshell for issues?