SQL Stored Procedure return code

Home Page › Forums › BizTalk 2004 – BizTalk 2010 › SQL Stored Procedure return code

Viewing 1 reply thread
  • Author
    Posts
    • January 19, 2007 at 4:43 PM #17332
      community-content

        Hello all,

        I'm calling a SPROC to perform a product lookup on a SQL Server 2005 table, everything works great.  Occasionally here's a product in the file being processed in BizTalk and there's no match in the SQL Table.  Don't ask, it's a legacy system.

        I declared an @ReturnCode variable in the SPROC but don't know how to grab it back in BizTalk so that I can interrogate it.  From the thin example in the BizTalk 2006 Recipes book it doesn't appear that you need to add the variable to any of the schemas, because (I'm guessing) it's a variable and not data being mapped to a field.  Unfortunately on this particular topic the authors don't go into detail.

        Anyone know how I can read what @ReturnCode is?

         
        Thanks.
         

      • January 19, 2007 at 5:47 PM #17333
        community-content

          I was having a lot of trouble with that using Oracle…what most of my current customers use, and decided to use OUT paramaters instead, to make life easier.  There are, I'm sure, several dozen other methods at getting at these values, including third party adapters like TopXML.  I would suggest trying to add the Out param IF you have some control over the SQL code and there aren't too many fields to return.

          I haven't tested too many other ways, so maybe someone else had the answer you're looking for, but I'll keep my eyes open as well.

          Of course, you can always access the table directly or use a view if that's an option.

          • January 22, 2007 at 11:55 AM #17356
            community-content

              BizTron,

              where does one specify the OUT parameter?  In BizTalk I'm calling the Sproc stored in the SQL Server, but all the examples I've seen seem to define the OUT parameter when they create/execute the proc as part of a select statement – see the code example under the "Output Parameters" section: http://www.awprofessional.com/articles/article.asp?p=25288&seqNum=9&rl=1

              Thanks for your help.
               

              • January 22, 2007 at 1:24 PM #17358
                community-content

                  CREATE PROCEDURE (Transact-SQL)

                  CREATE { PROC | PROCEDURE } [schema_name.] procedure_name [ ; number ] 
    [ { @parameter [ type_schema_name. ] data_type } 
        [ VARYING ] [ = default ] [ OUT | OUTPUT ]
    ] [ ,...n ] 
...
                  • January 22, 2007 at 3:57 PM #17364
                    community-content

                      BizTron,

                      here's my Sproc that's called by BizTalk:

                       

                      set ANSI_NULLS ON
                      set QUOTED_IDENTIFIER ON
                      go

                      ALTER PROCEDURE [dbo].[TranslateLegacyProductNumber]
                          (
                              @LegacyProductNumber nvarchar(255)
                          )
                      AS
                          SELECT        LegacyProductNumber,
                                      SAPProductNumber,
                                      ProductDescription
                              FROM    ProductTranslation
                              WHERE    LegacyProductNumber = @LegacyProductNumber
                              FOR XML AUTO
                             
                          RETURN

                       

                       

                      If I add the error code variable after the @LegacyProductNumber nvarchar(255) line, my question still stands, how do I read the variable in BizTalk?  Maybe I haven't had enough caffeine but I don't see it.

                       

            • Author
              Posts
            Viewing 1 reply thread
            • The forum ‘BizTalk 2004 – BizTalk 2010’ is closed to new topics and replies.