I set up a WMI alert in SQL2K5, which invokes a response launching a job. 
The alert is AUDIT_LOGIN_FAILED.  The response job is to insert the contents
of the token $(WMI(TextData)) into a table.

The job step text looks like:
INSERT INTO dbo.ddl_event_test_table(event_source, event_data)
VALUES('AUDIT_LOGIN_FAILED', REPLACE('$(WMI(TextData))', '''', ''''''))

The error looks like:
Executed as user: MyDomain\MyServiceAccount. Incorrect syntax near 'bubba'.
[SQLSTATE 42000] (Error 102).

The profile trace looks like:
INSERT INTO dbo.ddl_event_test_table(event_source, event_data)
VALUES('AUDIT_LOGIN_FAILED', REPLACE('Login failed for user 'bubba'.
[CLIENT: <local machine>]', '''', ''''''))

I have tried this with and without a REPLACE.  The result is the same.  Is
this documented?  Is there a workaround?

Thanks for all input.

John T (Jo***@discussions.microsoft.com) writes:
Show quoteHide quoteDocumented? Sort of. On
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/105bbb66-0ade-4b46-b8e4-
f849e5fc4d43.htm
it says:

   SQL Server Agent replaces an exact value for the token. Your job steps
   must take this into account and correctly quote the tokens you use.

Bug? Certainly quite a serious design flaw in my eyes. There is no way you
can save this situation with replace(). As I understand it, SQL Server
Agent pastes in the value of the token, and it's your job to put that
value in a syntactic correct context. Which is quite an uphill battle in
this case. SQL Server Agent would need to provide to means to expand a
token: as-is and as-string-literal. Say that the syntax for the latter
would be $$(WMI(TextData)). That would expand the string to

  N'Executed as user: MyDomain\MyServiceAccount. Incorrect syntax near
  ''bubba''. [SQLSTATE 42000] (Error 102).'

An alternative would be that ' were not expanded as such but as some
innocent character, for instance `.

The workaround you can employ is to say SET QUOTED_IDENTIFIER OFF in
the script. Then you can say:

  INSERT INTO dbo.ddl_event_test_table(event_source, event_data)
  VALUES('AUDIT_LOGIN_FAILED', "$(WMI(TextData))")

But that would not be waterproof, since you would get the same syntax
error for a message that includs a ".

I would suggest that you submit a bug/suggestion for this on
http://lab.msdn.microsoft.com/productfeedback/. If you submit it as
a bug, it may be closed as "By Design". I would not expect this to
be fixed in SQL 2005, as it would be a feature change. Then again,
since there is a potential source for SQL injection here, maybe there
is cause for alarm. Feel free to include to suggestions I've given here
in your submission on the Feedback Centre.

Disclaimer: I have very little experience of working with tokens in
SQL Server Agent, so I may have missed something obvious. (Which is
why I don't want to submit any bug/suggestion myself.)

SSMS Query Editor... Vs Query Analyser
DTS Designer error
Why is SQL Server 2005 Management Studio so slow?
MS Reporting, Oracle
Query Analyzer Debuging