Activation Stored Proc with Parameters

  • SQL STEWIE

    SSCommitted

    Points: 1837

    I am trying to begin playing with the Service Broker, and I came across the following tutorial which basically does exactly what I need

    http://www.sql-server-performance.com/articles/dev/service_broker_p2.aspx

    however, I am not sure if there is a flaw in the tutorial or if I am missing somerthing.

    I have attached the code I am using. I am running on a SQL Server 2005 box and when I execute the code, I get the following error

    Msg 9653, Level 16, State 1, Line 2

    The activation stored procedure 'spLogMessage' is invalid. Activation stored procedures cannot require parameters.

    but in the tutorial, the activation stored proc has parameters. Can someone please help me out? 🙂

    Thanks!

    Michael

  • RBarryYoung

    SSC Guru

    Points: 143327

    Then, unless it had all optional parameters, the tutorial was wrong. Service Broker passes no[/b] parameters to the activation procedure, so if it requires any, the activation will fail.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • RBarryYoung

    SSC Guru

    Points: 143327

    As a side note, when I was learning Service Broker a couple of years ago, I read over thirty articles, tutorials and walkthroughs, including many from Microsoft. And yet I could not get a single one of them to work!

    What I discovered later was that every one of those articles either had significant mistakes or, more often, left out important details that made it impossible to get their examples to work (Microsoft's examples have since been corrected, mostly). In response to this I made up my own presentation, and example code to teach Service Broker, and especially to point out all of the typical mistakes and traps that first-time SB developers get caught by.

    You can download it here. I promise you that it works.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • SQL STEWIE

    SSCommitted

    Points: 1837

    Bob Hovious (2/9/2010)


    As a side note, when I was learning Service Broker a couple of years ago, I read over thirty articles, tutorials and walkthroughs, including many from Microsoft. And yet I could not get a single one of them to work!

    What I discovered later was that every one of those articles either had significant mistakes or, more often, left out important details that made it impossible to get their examples to work (Microsoft's examples have since been corrected, mostly). In response to this I made up my own presentation, and example code to teach Service Broker, and especially to point out all of the typical mistakes and traps that first-time SB developers get caught by.

    You can download it here. I promise you that it works.

    Thank you very much! I will check out your examples and slides!

  • kcordell

    SSC Journeyman

    Points: 85

    Hi Barry, Your "You can download it here." link doesn't work. I'd love to see that example. Thanks.

  • mike 57299

    SSCrazy

    Points: 2285

    Me too.

  • SQLRNNR

    SSC Guru

    Points: 281210

    mike 57299 (2/27/2014)


    Me too.

    Same here. It'd be nice.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • RBarryYoung

    SSC Guru

    Points: 143327

    Sorry folks, the site has been down for years.

    I am attaching a ZIP of what I think was at this link ...

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • kevin_nikolai

    SSCarpal Tunnel

    Points: 4343

    Hi Barry, will give it a try - thanks for the effort.

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply