0

We've got a stored procedure that's running in a SQL Server agent job on multiple servers. It has the little block of code to log debugging information. It's the same code on both machines, but on one machine the message is coming out garbled and the other clean. I don't see why it's behaving differently on the different servers.

DECLARE @NumToProcess VARCHAR(5)
SELECT @NumToProcess = COUNT(*) FROM @ProcessingIDs
IF(@NumToProcess = 0)
BEGIN
    RAISERROR('Nothing to process', 10, 1) WITH NOWAIT
    RETURN
END
ELSE
BEGIN
    DECLARE @Message VARCHAR(100)
    SET @Message = 'Processing ' + @NumToProcess + ' events'
    RAISERROR(@Message, 10, 1) WITH NOWAIT
END

On one server I get the expected result in the Agent job history when there are records to process (e.g "Processing 105 events [SQLSTATE 01000] (Message 50000)")

But on the other server I see slight variations on this

...vents [SQLSTATE 01000] (Message 50000)
...ts [SQLSTATE 01000] (Message 50000)
...ents [SQLSTATE 01000] (Message 50000)

Not only is the "Processing " replaced with ..., parts of the word after the number gets consumed too.

The code looks dirt simple. Can someone explain why the one server could be producing this result?

  • Is the behaviour the same if you run that code from SSMS instead of a job or a procedure? – Ronaldo May 1 at 23:23
  • What is the collation set to on the working servers and the problematic one? Also try nvarchar as the type for your variables. – CB_Ron May 2 at 0:00
  • If you add an output file for the jobstep, what do these messages look like in that file? – Tibor Karaszi May 3 at 6:31
  • Sorry for the delay in responding. Yesterday got away from me. A) No it works as expected just running that code in an SSMS query window. B) If I output the job step to a file, it works correctly there, too. C) The collation on both servers for the tempdb and the databases being operated on is SQL_Latin1_General_CP1_CI_AI. I can try changing the variables to nvarchar next. – user1664043 May 5 at 13:24
  • Instead of trying nvarchar I just tried RAISERROR('Processing %d events', 10, 1, @NumToProcess) instead, and that output correctly. So I have a way to get it right. I'm just really puzzled why it mangles it on this system in the first place – user1664043 May 5 at 13:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.