Adding a custom event error to SQL

Transact-SQL
1

Hello - I have been tasked with copying some custom event errors from one sql database server to another (this is for Sharepoint Team Foundation Server). I found online a query that would run against the old server (SQL 2005) and create a script I could use for the new server (SQL 2012): select 'exec sp_addmessage ' + convert(varchar(6), message_id) + ', ' + convert(varchar(6), severity) + ', ''' + text + ''''
from sys.messages where message_id > 50000

It appeared successful with several lines that show variations of the below statement:
exec sp_addmessage 400000, 16, '%s: Unexpected Database Failure - Error %d executing %s statement for %s'

The issue I am having is on about a dozen of these statements that have an opening and closing single quote around a percentage sign with or without other characters. The SQL server does not like this, I find that if I put double quotes around the percentage sign or if I put no quotes around the percentage sign, it has no problem putting it into the database. My concern is if either of these changes will actually make the error statement work properly when it is raised. Below are a couple of examples to which I am referring and the error the sql server gives me:

exec sp_addmessage 500075, 16, '%s: Version Failure - Invalid version spec '%''

exec sp_addmessage 500091, 16, '%s: ShelveSet Failure - Generic error '%s'.'

The error message from sql is: Incorrect syntax near '%'. Which points to the single quotes.

Example the SQL server likes:

exec sp_addmessage 500075, 16, '%s: Version Failure - Invalid version spec %'

exec sp_addmessage 500091, 16, '%s: ShelveSet Failure - Generic error "%s".'

Would either of these changes work for the statement as it is intended? If they do not work, what do I need to put around these single quotes for the SQL Server to like it? Forgive me if this is a very basic question, but I am new to Transact-SQL and still trying to learn.

Everything I have googled does not quite give me this answer...

I appreciate any help!
Thanks,
Lana

2

SQL Server uses a single quote to begin and terminate a character literal. If you need have a single quote within the literal itself, the way to do it is to use another single quote as the escape character. You can see how that works by copying and running the following example:

PRINT 'Here is a single quote -->''<--';
PRINT 'Here are two single quotes -->''''<--';

So your statements should be these:

exec sp_addmessage 500075, 16, '%s: Version Failure - Invalid version spec ''%'''

exec sp_addmessage 500091, 16, '%s: ShelveSet Failure - Generic error ''%s''.'
1 Like
3

Hi James –

Thank you so much! That did the trick!!

I appreciate your assistance! :smiley:

Lana