Compartilhar via

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

  • Artigo

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'>
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 = <'sd#@@$FDajdlksaj'>
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

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

  • Anonymous
    March 29, 2007
    Perfect work. Thanks for this sites [url=http://www.shelterrestaurant.com/images/online/diazepam]diazepam[/url] <a href="http://www.shelterrestaurant.com/images/online/diazepam">diazepam</a> http://www.shelterrestaurant.com/images/online/diazepam Good bye!

  • Anonymous
    July 31, 2007
    <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,

  • Anonymous
    July 31, 2007
    <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,

  • Anonymous
    July 31, 2007
    <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,

  • Anonymous
    August 01, 2007
    <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,

  • Anonymous
    February 01, 2008
    <a href= http://index1.greathal.com >pre teen pageant gown</a>

  • Anonymous
    September 03, 2011
    The comment has been removed

  • Anonymous
    October 16, 2011
    The comment has been removed