A strange problem with ‘ADD SIGNATURE’ and the new SQLCMD command line utility

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 =
2> go
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 =
2> go

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.

Comments (9)

  1. praveen says:

    i am having similar problem when i try to drop and add the primary key column. please suggest a solution at prneee@gmail.com

  2. <a href="httpwwwigenqmvhcnpage19html">grillsremixbynelly</a> grillsremixbynelly,<a href="httpwwwigenqmvhcnpage16html">kittenheelkneehighboots</a> kittenheelkneehighboots,<a href="httpwwwigenqmvhcnpage14html">gaybowelsyndrome</a> gaybowelsyndrome,<a href="httpwwwigenqmvhcnpage16html">menseuropeanboots</a> menseuropeanboots,<a href="httpwwwtyxotwjecnpage92html">creativezencpm</a> creativezencpm,<a href="httpwwwigenqmvhcnpage18html">tightcollegeblonde</a> tightcollegeblonde,<a href="httpwwwigenqmvhcnpage18html">blondenudepublic</a> blondenudepublic,<a href="httpwwwtyxotwjecnpage95html">collegefundraisingstudent</a> collegefundraisingstudent,<a href="httpwwwtyxotwjecnpage94html">sonylcdfreeshipping</a> sonylcdfreeshipping,<a href="httpwwwigenqmvhcnpage19html">wherecanifindnellyssongdilemma</a> wherecanifindnellyssongdilemma,

  3. <a href="httpwwwihamuicscnpage97html">glitterityourway</a> glitterityourway,<a href="httpwwwevzvhqkucnpage10html">babyanimalslikekittens</a> babyanimalslikekittens,<a href="httpwwwevzvhqkucnpage15html">fordf1504x4truck</a> fordf1504x4truck,<a href="httpwwwevzvhqkucnpage6html">thehealthychewcom</a> thehealthychewcom,<a href="httpwwwevzvhqkucnpage14html">freeinvisiblemyspacetracker</a> freeinvisiblemyspacetracker,<a href="httpwwwihamuicscnpage99html">mattelferrari54593</a> mattelferrari54593,<a href="httpwwwevzvhqkucnindexhtml">punkinsidercomyourinsidetopunkrock</a> punkinsidercomyourinsidetopunkrock,<a href="httpwwwevzvhqkucnpage19html">rockamwald</a> rockamwald,<a href="httpwwwevzvhqkucnpage16html">gingrichpowerfulpelosiwouldbeadisaster</a> gingrichpowerfulpelosiwouldbeadisaster,<a href="httpwwwevzvhqkucnpage3html">josephydejesusddsps</a> josephydejesusddsps,

  4. <a href="httpwwwnxedpuuecnpage55html">leosymboltattoos</a> leosymboltattoos,<a href="httpwwwnxedpuuecnpage54html">entertainmentchannel</a> entertainmentchannel,<a href="httpwwwnxedpuuecnpage70html">tullowrumours</a> tullowrumours,<a href="httpwwwnxedpuuecnpage55html">tattooeyewear</a> tattooeyewear,<a href="httpwwwnxedpuuecnpage57html">tigerrvvan</a> tigerrvvan,<a href="httpwwwnxedpuuecnpage78html">annschmiesinganddeborahhollistheroleofspecial</a> annschmiesinganddeborahhollistheroleofspecial,<a href="httpwwwnxedpuuecnpage70html">rumoursgirlclub</a> rumoursgirlclub,<a href="httpwwwnxedpuuecnpage52html">free3dgayporncomics</a> free3dgayporncomics,<a href="httpwwwnxedpuuecnpage65html">miamidolphinsnewswire</a> miamidolphinsnewswire,<a href="httpwwwnxedpuuecnpage79html">paulbinghamdj</a> paulbinghamdj,

  5. <a href="httpwwwmenhzyjdcnpage48html">glovesizehandjob</a> glovesizehandjob,<a href="httpwwwmenhzyjdcnpage22html">jewelyouweremeantformevideo</a> jewelyouweremeantformevideo,<a href="httpwwwmenhzyjdcnpage21html">rosemcgowensex</a> rosemcgowensex,<a href="httpwwwmenhzyjdcnpage25html">navyelearningonnko</a> navyelearningonnko,<a href="httpwwwmenhzyjdcnpage27html">nikefloridagatorscrosstrainingjacket</a> nikefloridagatorscrosstrainingjacket,<a href="httpwwwmenhzyjdcnpage49html">cannonimplosionvideo</a> cannonimplosionvideo,<a href="httpwwwmenhzyjdcnpage46html">teasebusty</a> teasebusty,<a href="httpwwwmenhzyjdcnpage33html">costbaselines</a> costbaselines,<a href="httpwwwmenhzyjdcnpage21html">vienessesarahrose</a> vienessesarahrose,<a href="httpwwwmenhzyjdcnpage43html">zeldaalinktothepast</a> zeldaalinktothepast,

  6. kistov says:

    <a href= http://index1.greathal.com >pre teen pageant gown</a>

  7. svdba says:

    There is a free tool "SQLS*Plus" (on http://www.memfix.com ) which is like SQL*Plus for SQL Server.

    Very flexible with data formatting (set lines size, pagesize, etc), variables (&, &&), spool, batch execution of multiple files, etc

  8. cherry says:

    British reporters are known for doing almost anything to get a Mulberry Bags. But reports that a newspaper secretly listened to telephone messages of murdered schoolgirls and other private citizens have produced Mulberry Handbags and anger.

    On Friday, British police arrested Andy Coulson, former editor of Mulberry Bag Britain's best-selling newspaper, News of Mulberry Outlet the World. The investigation led him to Mulberry UK Sale resign in January as communications director to Prime Minister David Cameron.

    The arrest came in a widening investigation of Mulberry UK telephone hacking. Other accusations include paying police for mulberry shoulder bags information on stories. The Reuters news agency reported that Mr. Coulson was released on Mulberry Bag UK until a date in October.

    Prime Minister Cameron promised Men's Mulberry Bags Friday that a judge will lead a full public inquiry into Women's Mulberry Bags the case after police complete their investigation.

    DAVID CAMERON: "Murder victims, terrorist victims, families who have lost loved ones, sometimes defending our country, that these people could have had their phones hacked into, in order to generate stories for Mulberry Bags  Mulberry Handbags  Mulberry Bag   Mulberry Outlet  Mulberry UK  mulberry bayswater bag  Mulberry Alexa Bag, is simply disgusting."