Hi,

I'm using Database Lookup Functoidwith ProductID and Brand sources node as below query:

SELECT [ProductID], [ProductName], [Price], [Brand]
 FROM [BiztalkSample].[dbo].[Products]
 Where ProductID=1  And    Brand='Nokia'

When I run this query within SQL Server then get a record result,

In my map have configure the Database lookup functiod as below

1. Use String Concatenate functoid as the first parameter. Input of this string Concatenate functoid contain ProductID and Brand from the source schema.

2. In the Fourth parameter: Set the value as (CAST(ProductID as nvarchar(3)+ '|' + Brand).

This is my input xml:

   <?xml version="1.0"?>
   <ns0:PurchaseOrder xmlns:ns0="http://MultilpleINParamInLookupDBFunctoid.Messages.PurchaseOrder">
            <ProductID>1</ProductID>
            <Brand>Nokia</Brand>
   </ns0:PurchaseOrder>

But my output xml are not return value like the result in SQL Server,

this is output xml:

<?xml version="1.0" encoding="UTF-16"?>
<ns0:Invoice xmlns:ns0="http://MultilpleINParamInLookupDBFunctoid.Messages.Invoice">
    <ProductName/>
    <Price/>
    <Brand/>
</ns0:Invoice>

What I work wrong?

Any help is greatly appreciated.

Thanks,

When I debug maps have get this error

"Cannot find a script or an extension object associated with namespace 'http://schemas.microsoft.com/BizTalk/2003/ScriptNS0'."

in this line of MapName.xsl file

<xsl:variable name="var:v2" select="ScriptNS0:DBLookup(0 , string($var:v1) , &quot;Provider=SQLOLEDB;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=BiztalkSample;Data Source=NVCHINH&quot; , &quot;BiztalkSample.dbo.Products&quot; , &quot;(CAST(ProductID as nvarchar(3)+ '|' + Brand)&quot;)" />

Help me ASAP please...

Hi Daniel,

Creating multiple where clauses in a database lookup wont work.
What you can do is calling a stored procedure runner and make a stored procedure of the query:

http://blogs.msdn.com/b/ebattalio/archive/2006/02/16/dbfunktoids.aspx

What i used to do was to create a extra column in the table where i concatenated the 2 where clauses where i was looking for. So you would get something like this:

Column named Key which is a concatenation of ProductId_Brand (1_Nokia). Then in your map you concatenate the fields with a String concatenate functoid. You connect that functoid to a database lookup functoid and everything should work fine.

Gr,

Ronald 

Hi Daniel,

Lets analysis this errors one-by-one.

When you debug the map, you get this error ""Cannot find a script or an extension object associated with namespace 'http://schemas.microsoft.com/BizTalk/2003/ScriptNS0'."". I hope you debug it, by generating the XSLT and while debugging the XSLT. The problem here is that while debugging the XSLT, it not able to find the Custom Extension Objects. Databaselookup functiods are implemented in external functions, in a separate assembly. When you validate the map, this would have generated the XSLT (which you used to debug) and also custom Extension Object XML which contains the reference for the base Microsoft.BizTalk.BaseFunctoids under namespace "'http://schemas.microsoft.com/BizTalk/2003/ScriptNS0". When you debug the XSLT, you're not passing the reference to this custom Extension Object, hence you get this error.

Best way to debug in this case is using the SQL-Profiler and see the SQL query being constructed.

I think the problem is here is the way you are passing the parameters. This workaround for passing multiple SQL where condition in how to handle the SQL injection. Try the following:

• Use string concatenate functiod as the first parameter to  Databaselookup functiods . And input of this string Concatenate functoid contain ProductID and Brand from the source schema (as you're doing now). i.e. The string concatenate functiod would have just two parameters - ProductID & Brand.
• In the fourth parameter set the condition as (CAST(Id as nvarchar(3))+Brand). No pipe symbol. You used string concatenate functiod in the first parameter, so no need for pipe symbol. Also I noticed you have not closed the brackets properly. The one I have given has the additional closing bracket, which shall work for you. Try it.