CODEWITHSUNDEEP
sql-serversql-server-agent
Clyde
Clyde

Reputation: 8145

SQLSTATE 01000 in SQL Server job output

I've got SQL Server jobs running that include periodic 'print' statements so that I can look in the job history and get a sense of what happened. But the output is cluttered with [SQLSTATE 01000]. Given that there is a limit to how much will fit in this output, I'd like as much space as possible for information I care about.

Is there any way to suppress the [SQLSTATE 01000] output for 'print' commands?

Upvotes: 3

Views: 18072

Answers (3)

Ken Klaft
Ken Klaft

Reputation: 31

The problem (sorry. "feature") is with the PRINT command in SQL Agent. I've overcome this problem by creating a table variable with one column for 'output' and populating it as I progress through my script, then at the very end of the job SELECT from the table variable. Piping the output to a file through SQL Agent gives me a log of the job. At any point in the job if it encounters an error, I dump the contents of the table before quitting. (I typically include the command to be executed into the table variable immediately before executing it). Simply replacing all your PRINT statements with one SELECT statement at the end of the job will get rid of those pesky [SQLSTATE 01000] suffixes to

every... damn... line...

Cheers, Ken

Upvotes: 3

Brettski
Brettski

Reputation: 20081

Not positive if you wanted to remove just the [SQLSTATE 01000] from the output or anything which contained [SQLSTATE 01000]. So here is both ways.

Print replace(@PrintThis, '[SQLSTATE 01000]', '');

Basically this will replace [SQLSTATE 01000] with nothing.

The next useses CHARINDEX(expression1, expression1 [, start_location]), Searches expression2 for expression1 and returns its starting position if found. The search starts at start_location.

@result = CHARINDEX('[SQLSTATE 01000]', @PrintThis)
if (@result > 0)
    Print @PrintThis

So if CHARINDEX < 0 the string [SQLSTATE 01000] doesn't exist and it doesn't print

If missed what you are trying to do please respond as such.

I hope this helps,

Brett

Upvotes: -1

Mitch Schroeter
Mitch Schroeter

Reputation: 1001

You're probably out of luck. That is normal output for PRINT statements. I don't think there is any way to suppress them.

Upvotes: 0

Related Questions