Partager via

DBFunktoids - SQL Server Stored Procedure Functoid / Extractor Functoids

  • Article

DBFunktoids is a collection of three functoids that enable the invocation and manipulation of SQL Server 2000 / 2005 stored procedures and their results. You can find a zip with the project and a compiled assembly at the end of this article.

 

DBFunktoids were written for BizTalk 2006 and the .NET Framework 2.0. If there is enough interest, I can port the project back to BizTalk 2004 (it should pretty much be code copy with a replacement of a Dictionary Generic with a Hashtable).

 

DBFunktoids are described below.

Stored Procedure Runner

The Stored Procedure Runner executes SQL Server stored procedures and provides the following capabilities:

  • Can call parameter-less stored procedures.

  • Supports procedures with up to 9 parameters (can be easily tweaked in source).

  • Stored procedure parameters can be constants or supplied from a source field.

  • Values are retrieved using the Value Extraktor functoid.

  • SQL Exceptions are trapped and stored for retrieval using the Error Extraktor functoid.

To use the Stored Procedure Runner, supply the appropriate number of arguments. If your stored procedure has no parameters, you only need to supply two arguments; if it has one or more parameters, you must supply at least four arguments:

Arg#

Argument

Description

Example

1

Database connection String

Connection string for database server

Persist Security Info=False;Integrated Security=SSPI;Data Source=myserver;Initial Catalog=AdventureWorks

2

Stored procedure name

Name of the stored procedure to invoke

uspGetEmployeeManagers

3

Parameter names

Pipe-delimited (“|”) list of stored procedure parameters

@EmployeeID|@LastName

4

Parameter 1 value

First parameter value. You must have at least one parameter value.

102

n

Parameter n

Subsequent parameters. These are optional and if not set will be NULL.

Benny

If your stored procedure has parameters, the functoid expects you to supply at least one value even though it will automatically supply NULL values for all other parameters. If you want to call a procedure with null parameter values, write a wrapper proc with no parameters and call that instead.

Value Extraktor

The Value Extraktor retrieves a value corresponding to a column name from the first row of records returned by the stored procedure. It takes two arguments:

Arg#

Argument

Description

Example

1

Output from Stored Procedure Runner

Guid associated with the stored proc call and results

2

Column name

Name of the column from which to retrieve a value; if the column doesn’t exist, an empty string is returned

LastName

 

Note that the current implementation does not capture return values from the stored procedure nor does it handle out parameters. One potential solution is to include these values in the result set; another is to modify the source to handle queries differently. If there are enough complaints, I can change it.

Error Extraktor

The Error Extraktor returns either an error message if there was an error in executing the stored procedure or an empty string if there was not (or the results cannot be found). It takes one argument:

Arg#

Argument

Description

Example

1

Output from Stored Procedure Runner

Guid associated with the stored proc call and results

To Install

To install:

  1. Install the DBFunktoids.dll assembly into the Global Assembly Cache: gacutil –if DBFunctoids.dll.

  2. Copy the DBFunktoids.dll into the <biztalk root>\Developer Tools\Mapper Extensions directory.

  3. From Visual Studio 2005, click Tools | Choose Toolbox Items, click the Functoids tab, then click Browse to browse to and select the DBFunctoids.dll. On the functoid screen, scroll until you see the three new funktoids and then click the check box to put them on the Database Functoids toolbox palette.

Enjoy.

[updated 5/26/2006: fixed bug in source]

DBFunktoids.zip

Comments

  • Anonymous
    February 16, 2006
    Check out the new functoids that Eric created!! DBFunktoids is a collection of three functoids that enable...
  • Anonymous
    January 10, 2007
    The comment has been removed
  • Anonymous
    January 10, 2007
    For those of you who have to run against something other than SQL Server it’s worth noting that the port to OleDB is very easy, just a simple Search and Replace
  • using System.Data.SqlClient  to using System.Data.OleDb;
  • and then pretty much where ever it says sql you say OleDb
  • you then need to pass the provider in the connection string Provider=MSDAORA.1;

  • Anonymous
    March 08, 2007
    Hi There We are passing two values to the stored Proc , but iam not getting the result when i use  the store procedure runner and the value extraktor functoids.When i use provider                 Initial Catalog=Homer_Common_Sc;Data Source=Dell11;Provider=SQLOLEDB.1; Function 'ScriptNS0:RunStoredProcedure()' has failed. Exception has been thrown by the target of an invocation. Keyword not supported: 'provider'.Your help is really appreciated Thanks Saravana ramkumar

  • Anonymous
    March 08, 2007
    Hello Saravana, Omit the provider information.  The funktoid will is using SqlClient.  Let me know if that doesn't work. Thanks, Eric

  • Anonymous
    March 09, 2007
    The comment has been removed

  • Anonymous
    March 13, 2007
    Hello Eric It worked very well.Thanks for your Custom functoids.I was missing the parameter values that didnt give results though. Regards Saravana ramkumar

  • Anonymous
    March 17, 2007
    I'm having trouble using the Stored Procedure functoid. When I attempt to validate the map using them I receive the following error: Exception Caught: Mapper Compiler: Functoid not found: guid ({FC1E7D80-7FA7-4AED-AD24-923DC8363E95}) with functoid id (6070) I confirmed that DBfunktoids.dll is in the GAC as well as C:Program FilesMicrosoft BizTalk Server 2006Developer ToolsMapper Extensions so I'm not sure what the problem is. Any help is greatly appreciated! Thanks, Fred

  • Anonymous
    April 12, 2007
    I get exactly the same error.  The functoid and maps compiled and worked fine until yesterday! I have uninstalled the functoid from the Gac, deleted it from the mapper extentions and reset the toolbox.  I reinstalled everything from scratch: Assembly GUID: FC1E7D80-7FA7-4AED-AD24-923DC8363E95 GAC GUID: e90ba9d74b97ae9c. But I still get this error: Exception Caught: Mapper Compiler: Functoid not found: guid ({FC1E7D80-7FA7-4AED-AD24-923DC8363E95}) with functoid id (6070) All help is welcome! Thanks, Margarete

  • Anonymous
    April 13, 2007
    Frauss / Margarete, Can you provide any other information?  I cannot replicate this problem -- once registered, the funktoids appear to work just fine. Has anything else changed in your environment? Thanks, Eric

  • Anonymous
    November 09, 2007
    Question: How would you supply a UDL as the connection string?  Our company stresses the use of these as it makes migration through various testing servers considerably easier - we don't have to recompile for each server. Thanks. Herve Mann

  • Anonymous
    November 11, 2009
    I have observed that Functiod “Stored Procedure Runner” calls SQL Stored procedure multiple times (as many as we put “Value Extraktor” functoids). For example, If an SP returns a row with 8 columns and we use 8 Value Extraktor functoids (one for each column) to fetch those column values,  I can see in SQL Profiler that this SP is being called 8 times. We I reduce this to 7 Extraktor functoids, SP is being called 7 times. Is there a way we can avoid this so that SP will be called only once?