This one is a little odd: But, when using the new 'ADD SIGNATURE' DDL in SQL Server 2005, if you are using SQLOLEDB through the SQLNCLI (SQL Native Client) protocol stack, i.e. the stack the new command line utility SQLCMD uses, you will get the following 'Incorrect syntax' error when trying to issue an 'ADD SIGNATURE' command:
C:\>sqlcmd -S .
1> ADD SIGNATURE TO object_signature BY CERTIFICATE my_certificate WITH PASSWORD = 'sd#@@$FDajdlksaj'
Msg 156, Level 15, State 1, Server <server>, Line 1
Incorrect syntax near the keyword 'ADD'.
No, you are not going mad, the syntax is fine; but, under the covers SQLNCLI is thinking the keyword 'ADD' is a user created stored procedure and is automatically prepending 'exec ' to the command. You can see this with your own eyes by turning on profiler, you will see the following batch getting executed (notice the prepended 'exec '):
exec ADD SIGNATURE TO object_signature BY CERTIFICATE my_certificate WITH PASSWORD = 'sd#@@$FDajdlksaj'
This is a mistake in our SQLOLEDB protocol stack which we hope to fix in SQL Server 2005 Service Pack 1. In the mean time, the workaround is simple, if not a little awkward. You can prepend a ';' to your ADD SIGNATURE DDL i.e.:
C:\>sqlcmd -S .
1> ;ADD SIGNATURE TO object_signature BY CERTIFICATE my_certificate WITH PASSWORD = 'sd#@@$FDajdlksaj'
The ';' will confuse the parsing going on in SQLOLEDB so that it does not add the 'exec ', so everything will work fine. We are not aware of any other DDL that if affected by this, but if you find any please let me know.
Again, this is only a problem with SQLOLEDB in SQLNCLI (SQL Native Client). OSQL is okay, because this uses ODBC, so is the old Query Analyzer. The new SQL Management Studio is also fine, because this uses the new SQLClient managed stack.