Learning Center (Beta)

Home

November 17

WCF SQL Server Adapter (Implementing Polling and Event-Driven Consumers)

by HQ\jarriaga

on 11/17/2011 10:17 PM

It was fun to be a presenter at the Twin Cities Connected Systems User Group Meeting on November 17^th, 2011. Here are the links to the presentation (and demo):

Cheers,

--Juan I. Arriaga

0 Comment(s)

August 19

Enqueue and Dequeue with SQL Server Broker

by HQ\jarriaga

on 8/19/2011 10:08 AM

Category: Miscellaneous

The following is sample T-SQL code to create SQL Server Broker artifacts and the corresponding stored procedures to queue and dequeue messages:

USE [TestDB]

CREATE SCHEMA [EWT]

CREATE MESSAGE TYPE [http://eworldtree.com/SampleOrder/V1]

VALIDATION = WELL_FORMED_XML

CREATE CONTRACT [http://eworldtree.com/SampleOrderContract/V1]

( [http://eworldtree.com/SampleOrder/V1] SENT BY INITIATOR,

[http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog] SENT BY TARGET )

CREATE QUEUE [EWT].[MyInitiatorQueue]

WITH

STATUS = ON,

RETENTION = OFF ,

ACTIVATION (

STATUS = ON,

PROCEDURE_NAME = [EWT].[EndConversation] ,

MAX_QUEUE_READERS = 1,

EXECUTE AS SELF )

CREATE QUEUE [EWT].[MyTargetQueue]

WITH STATUS=ON

CREATE SERVICE [MyEnqueueService]

ON QUEUE [EWT].[MyInitiatorQueue]

([http://eworldtree.com/SampleOrderContract/V1])

CREATE SERVICE [MyDequeueService]

ON QUEUE [EWT].[MyTargetQueue]

([http://eworldtree.com/SampleOrderContract/V1])

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

-- =============================================

-- Author: eWorldTree

-- Description: Dequeues a transaction

-- =============================================

CREATE PROCEDURE [EWT].[DequeueTransaction]

@transaction XML OUTPUT

,@success BIT OUTPUT

,@queueCount INT OUTPUT

,@errorMsg NVARCHAR(MAX) OUTPUT

BEGIN TRY

SET NOCOUNT ON;

SET @errorMsg = '';

DECLARE @cg UNIQUEIDENTIFIER

DECLARE @ch UNIQUEIDENTIFIER

DECLARE @messageTypeName NVARCHAR(256)

DECLARE @messageBody XML;

BEGIN TRANSACTION;

RECEIVE TOP(1)

@cg = conversation_group_id,

@ch = conversation_handle,

@messageTypeName = message_type_name,

@messageBody = CAST(message_body AS XML)

FROM [EWT].[MyTargetQueue];

IF (@@ROWCOUNT > 0)

BEGIN

PRINT 'Conversation Handle: ' + CAST(@ch AS NVARCHAR(MAX));

PRINT 'Message Type: ' + CAST(@messagetypename AS NVARCHAR(MAX));

PRINT 'Message Body: ' + CAST(@messagebody AS NVARCHAR(MAX));

END CONVERSATION @ch;

COMMIT TRANSACTION;

SET @transaction = @messagebody;

SET @success = 1;

SET @queueCount = (SELECT COUNT(conversation_handle) from [EWT].[MyTargetQueue]);

END

ELSE

BEGIN

PRINT 'No requests pending.'

ROLLBACK TRANSACTION;

SET @transaction = '';

SET @success = 1;

SET @queueCount = (SELECT COUNT(conversation_handle) from [EWT].[MyTargetQueue]);

END

END TRY

BEGIN CATCH

PRINT 'Error: ' + CAST(ERROR_NUMBER() AS NVARCHAR(MAX)) + ' ' + CAST(ERROR_MESSAGE() AS NVARCHAR(MAX));

ROLLBACK TRANSACTION;

SET @transaction = '';

SET @success = 0;

SET @queueCount = (SELECT COUNT(conversation_handle) from [EWT].[MyTargetQueue]);

SET @errorMsg = CAST(ERROR_NUMBER() AS NVARCHAR(MAX)) + ' ' + CAST(ERROR_MESSAGE() AS NVARCHAR(MAX));

END CATCH

-- =============================================

-- Author: eWorldTree

-- Description: Enqueues a transaction

-- =============================================

CREATE PROCEDURE [EWT].[EnqueueTransaction]

@transaction NVARCHAR(MAX)

,@success BIT OUTPUT

,@queueCount INT OUTPUT

,@errorMsg NVARCHAR(MAX) OUTPUT

BEGIN TRY

SET NOCOUNT ON;

SET @errorMsg = '';

DECLARE @ch UNIQUEIDENTIFIER

BEGIN TRANSACTION;

BEGIN DIALOG CONVERSATION @ch

FROM SERVICE [MyEnqueueService]

TO SERVICE 'MyDequeueService'

ON CONTRACT [http://eworldtree.com/SampleOrder/V1] WITH ENCRYPTION=OFF;

PRINT 'Sending msg '+ CAST(@transaction AS NVARCHAR(MAX));

SEND ON CONVERSATION @ch MESSAGE TYPE [http://eworldtree.com/SampleOrder/V1] (@transaction);

COMMIT TRANSACTION;

SET @success = 1;

SET @queueCount = (SELECT COUNT(conversation_handle) from [EWT].[MyTargetQueue]);

PRINT 'Msg sent on conversation handle ' + CAST(@ch AS NVARCHAR(MAX));

END TRY

BEGIN CATCH

PRINT 'Error: ' + CAST(ERROR_NUMBER() AS NVARCHAR(MAX)) + ' ' + CAST(ERROR_MESSAGE() AS NVARCHAR(MAX));

ROLLBACK TRANSACTION;

SET @success = 0;

SET @queueCount = (SELECT COUNT(conversation_handle) from [EWT].[MyTargetQueue]);

SET @errorMsg = CAST(ERROR_NUMBER() AS NVARCHAR(MAX)) + ' ' + CAST(ERROR_MESSAGE() AS NVARCHAR(MAX));

END CATCH

-- ==========================================================

-- Author: eWorldTree

-- Description: Ends the conversation at the Initiator Queue.

-- ==========================================================

CREATE PROCEDURE [EWT].[EndConversation]

BEGIN TRY

DECLARE @cg UNIQUEIDENTIFIER

DECLARE @ch UNIQUEIDENTIFIER

DECLARE @messageTypeName NVARCHAR(256)

DECLARE @messageBody XML;

BEGIN TRANSACTION;

RECEIVE TOP(1)

@cg = conversation_group_id,

@ch = conversation_handle,

@messageTypeName = message_type_name,

@messageBody = CAST(message_body AS XML)

FROM MyInitiatorQueue;

IF (@@ROWCOUNT > 0)

BEGIN

PRINT 'Conversation Handle: ' + CAST(@ch AS NVARCHAR(MAX));

PRINT 'Message Type: ' + CAST(@messagetypename AS NVARCHAR(MAX));

PRINT 'Message Body: ' + CAST(@messagebody AS NVARCHAR(MAX));

END CONVERSATION @ch;

COMMIT TRANSACTION;

END

ELSE

BEGIN

PRINT 'No queue items pending.'

ROLLBACK TRANSACTION;

END

END TRY

BEGIN CATCH

PRINT 'Error: ' + CAST(ERROR_NUMBER() AS NVARCHAR(MAX)) + ' ' + CAST(ERROR_MESSAGE() AS NVARCHAR(MAX));

ROLLBACK TRANSACTION;

END CATCH

0 Comment(s)

April 16

PowerShell Script to uninstall GAC Assemblies in BizTalk Application

by HQ\jarriaga

on 4/16/2011 1:33 PM

Category: BizTalk; PowerShell

The following is a sample of a PowerShell (PS) script function that makes use of the ExplorerOM object model to iterate through the assemblies in a BizTalk application to uninstall them from the global assembly cache (GAC) upon confirmation:

$TargetServer = "localhost"

$BizTalkMgmtDb = "BizTalkMgmtDb"

$ConnectionString = "Server=" + $TargetServer + ";Initial Catalog=" + $BizTalkMgmtDb + ";Integrated Security=SSPI;" # ConnectionString for ExplorerOM functionality.

function RemoveAssemblies()

{

param

(

[string] $AppName

)

Set-Alias gacutil $CommonDeployFolder\gacutil.exe

[void][Reflection.Assembly]::LoadFile("C:\Program Files\Microsoft BizTalk Server 2009\Developer Tools\Microsoft.BizTalk.ExplorerOM.dll")

$BizTalkExpObj = New-Object "Microsoft.BizTalk.ExplorerOM.BtsCatalogExplorer"

$BizTalkExpObj.ConnectionString = $ConnectionString

$BizTalkExpObj.Refresh()

[Microsoft.BizTalk.ExplorerOM.Application] $BizTalkApp = $BizTalkExpObj.Applications[$AppName]

foreach($Assembly in $BizTalkApp.Assemblies)

{

$unGac = Read-Host "Uninstall" $Assembly.DisplayName "from GAC (1=Yes)?"

if ($unGac -eq 1) {

gacutil /u $Assembly.DisplayName

}

Write-Host $(Get-Date) "Loaded BizTalk Helper functions."

Assuming a common library of functions, "dot source" the helper library and invoke the RemoveAssemblies function as in the following example:

$ApplicationName = "MyBTApp"

$CommonDeployFolder = "c:\temp\BTSdeploy\"

$BizTalkHelper = "c:\temp\BTSdeploy\BTHelper.ps1"

Write-Host *** BEGIN ***

. $BizTalkHelper # "dot source" the helper library.

RemoveAssemblies -AppName:$ApplicationName

Write-Host `n *** END ***

WARNING: Be careful when unGAC'ing an assembly. Other applications may depend on such assembly. For more information about undeploying BizTalk applications, visit the following links:

0 Comment(s)

April 16

PowerShell Script to restart BizTalk Host Instance

by HQ\jarriaga

on 4/16/2011 12:02 PM

Category: BizTalk; PowerShell

The following is a sample of a PoweShell (PS) function that makes use of Windows Management Instrumentation (WMI) to restart a BizTalk host instance:

function ManageHostInstance()

{

param

(

[string] $HostName,

[bool] $StartHostInstance

)

$filter = "HostName = '" + $HostName + "'"

$HostInstance = get-wmiobject MSBTS_HostInstance -namespace 'root\MicrosoftBizTalkServer' -filter $filter

$HostInstanceState = $HostInstance.GetState().State

Write-Host $(Get-Date) "Current state of" $HostName "Host Instance:" $HostInstanceState # 1=Stopped, 2=Start pending, 3=Stop pending, 4=Running, 8=Unknown

if ($StartHostInstance -eq $TRUE)

{

if ($HostInstanceState -eq 1) {

$HostInstance.Start() }

}

else

{

if ($HostInstanceState -eq 4) {

$HostInstance.Stop() }

}

Write-Host $(Get-Date) "New state of" $HostName "Host Instance:" $HostInstance.GetState().State

}

Assuming you have a common PS library of functions, "dot source" this helper library and restart the BizTalk host instance as in the following example:

$BizTalkHelper = "c:\temp\BTSdeploy\BTHelper.ps1"

Write-Host *** BEGIN ***

. $BizTalkHelper # "dot source" the helper library.

Write-Host "===> Restarting BizTalk host instance..."

ManageHostInstance -HostName:"BizTalkServerApplication" -StartHostInstance:$FALSE

Write-Host "'n"

ManageHostInstance -HostName:"BizTalkServerApplication" -StartHostInstance:$TRUE

Write-Host "<=== Restarted BizTalk host instance."

Write-Host `n *** END ***

The output should be similar to the following:

*** BEGIN ***

4/16/2011 12:07:34 PM Loaded BizTalk Helper functions.

===> Restarting BizTalk host instance...

4/16/2011 12:07:34 PM Current state of BizTalkServerApplication Host Instance: 4

__GENUS : 2

__CLASS : __PARAMETERS

__SUPERCLASS :

__DYNASTY : __PARAMETERS

__RELPATH :

__PROPERTY_COUNT : 1

__DERIVATION : {}

__SERVER :

__NAMESPACE :

__PATH :

ReturnValue :

4/16/2011 12:07:36 PM New state of BizTalkServerApplication Host Instance: 1

4/16/2011 12:07:36 PM Current state of BizTalkServerApplication Host Instance: 1

__GENUS : 2

__CLASS : __PARAMETERS

__SUPERCLASS :

__DYNASTY : __PARAMETERS

__RELPATH :

__PROPERTY_COUNT : 1

__DERIVATION : {}

__SERVER :

__NAMESPACE :

__PATH :

ReturnValue :

4/16/2011 12:07:38 PM New state of BizTalkServerApplication Host Instance: 4

<=== Restarted BizTalk host instance.

*** END ***

0 Comment(s)

March 10

Procedural MaxLength Validation in Entity Framework 2.0

by HQ\jarriaga

on 3/10/2011 11:45 AM

Category: Entity Framework

A lot of us get frustrated by the unhelpful 'String or binary data would be truncated' message that may occur when using the Entity Framework to save a new object into a database. The following is a snippet of the typical boiler-plate code that invokes the corresponding data-access-layer operations:

using (var context = new MyObjectEntities())

{

context.MyObjects.AddObject(myObject);

context.SaveChanges();

}

It is not a major task to pinpoint which database field triggered the error message when an object has two or three properties. However, what do we do when the object is complex and has lots of properties? Well, we have to do what we didn't do from the beginning: perform data validations before attempting to save the object.

One of the documented techniques is to extend the EDM entities to perform procedural validations. However, implementing these validations manually involves repetitive, tedious, and error-prone work. Is there a better way? Yes, since it is possible to customize the entity object generator.

For instance, using the ADO.Net EntityObject Generator, one could break the standard T4 template away to generate string length validations based on EDM metadata. Here is a sample of a break-away code for the 'OnChanging' event:

///partial void <#=ChangingMethodName(primitiveProperty)#>(<#=code.Escape(primitiveProperty.TypeUsage)#> value);

private void <#=ChangingMethodName(primitiveProperty)#>(<#=code.Escape(primitiveProperty.TypeUsage)#> value)

{

<#+ if (code.Escape(primitiveProperty.TypeUsage.EdmType.ToString()) == "Edm.String")

{ #>

if (value != null)

{

int maxLength = 0;

string edmMaxLength = Convert.ToString("<#=primitiveProperty.TypeUsage.Facets["MaxLength"].Value#>");

if (Int32.TryParse(edmMaxLength, out maxLength))

{

if (value.Length > maxLength)

throw new ArgumentException(string.Format("{0} '{1}' is longer than characters","<#=primitiveProperty.Name#>",value,maxLength));

}

<#+} #>

}

This break-away T4 code would substitute the standard 'OnChanging' event with something like this in the generated C# class:

///partial void OnMyPropertyChanging(global::System.String value);

private void OnMyPropertyChanging(global::System.String value)

{

if (value != null)

{

int maxLength = 0;

string edmMaxLength = Convert.ToString("5");

if (Int32.TryParse(edmMaxLength, out maxLength))

{

if (value.Length > maxLength)

throw new ArgumentException(string.Format("{0} '{1}' is longer than {2} characters", "MyProperty", value, maxLength));

}

NOTE: Please refer to this blog posting for additional information (the author deserves our credit for leading us to this adapted solution).

0 Comment(s)

March 09

BizTalk 2009 Map Scripts for Data Collection

by HQ\jarriaga

on 3/9/2011 5:05 PM

Category: BizTalk

Let's say we have an inline C# script such as the following that defines and populates a global hash table that is consumed in other data mappings:

System.Collections.Hashtable myTable = new System.Collections.Hashtable();

public void DeclareMyTable()

{

myTable["Code1"] = "Code Description 1";

myTable["Code2"] = "Code Description 2";

}

And let's pretend that we have an inline C# script that returns a code description in the hash table:

public string ReturnCodeDesc(string codeID)

{

string codeDesc = "NULL";

if (myTable.ContainsKey(codeID))

codeDesc = myTable[codeID].ToString();

return codeDesc;

}

Sometimes unfortunately, despite making an effort to link the hash-table Scripting functoid to a (parent) node as near of the beginning of the map as possible, BizTalk keeps generating XSLT code that puts the global variable way below in the XML process to be useful to data mappings that need to access it. Snippets of XSLT code:

<xsl:variable name="var:v1" select="userCSharp:ReturnCodeDesc(string(../s0:CodeID/text())" />

…

<xsl:variable name="var:v11" select="userCSharp:DeclareMyTable()" />

Based on the sample code, the code description would end with a "NULL" value. How do we solve this issue? A useful technique is to slighly modify the hash-table Scripting functoid to return a dummy string and pipe this output to a Logical String functoid that is linked as near of the beginning of the map as possible.

So the hash-table scripting would now look like this:

System.Collections.Hashtable myTable = new System.Collections.Hashtable();

public string DeclareMyTable()

{

myTable["Code1"] = "Code Description 1";

myTable["Code2"] = "Code Description 2";

return "Table has been initialized.";

}

And thanks to the Logical String functoid, the resulting XSLT code would now look like this:

<xsl:variable name="var:v1" select="userCSharp:DeclareMyTable()" />

<xsl:variable name="var:v2" select="userCSharp:LogicalIsString(string($var:v1))" />

<xsl:if test="$var:v2">

…

<xsl:variable name="var:v1" select="userCSharp:ReturnCodeDesc(string(../s0:CodeID/text())" />

…

</xsl:if>

Sweet! The LogicalString will always evaluate to true and therefore we have forced the declaration of our global table where we need it without having to modify the target schema.

0 Comment(s)

February 02

TCP Error Code 10061 (BizTalk NetTcp)

by HQ\jarriaga

on 2/2/2011 2:53 PM

Category: BizTalk

If you run into an error such as the following while attempting to connect to a NetTcp BizTalk receiving port:

Could not connect to net.tcp://192.168.1.20/myservice. The connection attempt lasted for a time span of 00:00:00.9960570. TCP error code 10061: No connection could be made because the target machine actively refused it 192.168.1.20:808.

Troubleshooting:

Make sure to have an inbound firewall rule that permits TCP traffic on port 808 (or another port other than the default of 808).
Verify that the BizTalk Host Instance is running -- why are some error messages totally misleading? J --

0 Comment(s)

January 27

WCF SQL Server Adapter MaxItemsInObjectGraph Quota

by HQ\jarriaga

on 1/27/2011 8:53 AM

Category: BizTalk

One may run into the following error while querying a large result set with a WCF SQL Server Adapter hosted outside of BizTalk:

The formatter threw an exception while trying to deserialize the message: There was an error while trying to deserialize parameter http://schemas.microsoft.com/Sql/2008/05/TableOp/dbo/MyTable:SelectResult. The InnerException message was 'Maximum number of items that can be serialized or deserialized in an object graph is '65536'. Change the object graph or increase the MaxItemsInObjectGraph quota.

The MaxItemsInObjectGraph quota is the equivalent to the typical Maximum received message size parameter that one can configure in the Binding tab of a BizTalk receive port location. To increase the allowed message size (maxItemsInObjectGraph) in a WCF SQL Server Adapter hosted outside of BizTalk, simply add a behavior to the corresponding client endpoint. Excerpts from a Service Model configuration:

<endpoint address="mssql://myserver/myinstance/mydatabase?" binding="sqlBinding"

behaviorConfiguration="MaximumMessageSize"

bindingConfiguration="SqlAdapterBinding"

contract="TableOp_dbo_MyTable" name="SqlAdapterBinding_TableOp_dbo_MyTable" />

</client>

</behavior>

</behavior>

</endpointBehaviors>

</behaviors>

0 Comment(s)

December 21

Using the WCF SQL Server Adapter and SQL Server Query Notification from outside BizTalk Server – Part II

by HQ\jarriaga

on 12/21/2010 5:43 PM

Category: BizTalk

The following is sample code developed in a Console application that demonstrates how to use the WCF SQL Server Adapter for notification services outside of BizTalk. This example, based on the WCF Service Model, can be modified and extended to run as a Windows Service or as a long-running service in the Windows Server AppFabric.

Program class

using System;

using System.ServiceModel;

using System.ServiceModel.Description;

using System.Collections.ObjectModel;

using System.ServiceModel.Channels;

namespace Test.NotificationOutsideBTS

{

[ServiceBehavior(InstanceContextMode = InstanceContextMode.Single)]

class NotificationNetworkCredentials : ClientCredentials, IServiceBehavior

{

public void AddBindingParameters(ServiceDescription serviceDescription, ServiceHostBase serviceHostBase, Collection<ServiceEndpoint> endpoints, BindingParameterCollection bindingParameters)

{

bindingParameters.Add(this);

}

public void ApplyDispatchBehavior(ServiceDescription serviceDescription, ServiceHostBase serviceHostBase)

{ }

public void Validate(ServiceDescription serviceDescription, ServiceHostBase serviceHostBase)

{ }

protected override ClientCredentials CloneCore()

{

ClientCredentials clone = new NotificationNetworkCredentials();

clone.Windows.ClientCredential = this.Windows.ClientCredential;

return clone;

}

public class NotificationAction

{

public Action<Notification> ProcessNotification = delegate(Notification message)

{

Console.WriteLine("\nNotification arrived:");

Console.WriteLine("-->Info: {0}", message.Info);

Console.WriteLine("-->Source: {0}", message.Source);

Console.WriteLine("-->Type: {0}", message.Type);

Console.WriteLine("\nWaiting for notification (press any key to stop the service)...");

};

}

class Program

{

static void Main(string[] args)

{

ServiceHost svcHost = null;

try

{

Console.WriteLine("Registering Notification listener...");

Uri[] svcUri = new Uri[] { new Uri("mssql://EWT//MyDatabaseName") };

svcHost = new ServiceHost(typeof(SqlAdapterBindingNamespace.SqlAdapterBindingService), svcUri);

NotificationNetworkCredentials credentials = new NotificationNetworkCredentials();

credentials.Windows.ClientCredential = System.Net.CredentialCache.DefaultNetworkCredentials;

svcHost.Description.Behaviors.Add(credentials);

svcHost.Open();

Console.WriteLine("Service Host and Listener opened...");

Console.WriteLine("Waiting for notification (press any key to stop the service)...");

Console.ReadKey();

}

catch (Exception ex)

{

Console.WriteLine(ex);

Console.ReadKey();

}

finally

{

if (svcHost.State == CommunicationState.Opened)

svcHost.Close();

else

svcHost.Abort();

}

The following are the sqlBinding service notification classes generated with the Add Adapter Service Reference wizard using Service (Inbound operations) as the contract type:

SqlAdapterBindingInterface class

[System.CodeDom.Compiler.GeneratedCodeAttribute("System.ServiceModel", "3.0.0.0")]

[System.ServiceModel.ServiceContractAttribute(Namespace="http://schemas.microsoft.com/Sql/2008/05/", ConfigurationName="NotificationOperation")]

public interface NotificationOperation {

// CODEGEN: Generating message contract since the wrapper namespace (http://schemas.microsoft.com/Sql/2008/05/Notification/) of message Notification does not match the default value (http://schemas.microsoft.com/Sql/2008/05/)

[System.ServiceModel.OperationContractAttribute(IsOneWay=true, Action="Notification")]

void Notification(Notification request);

}

[System.Diagnostics.DebuggerStepThroughAttribute()]

[System.CodeDom.Compiler.GeneratedCodeAttribute("System.ServiceModel", "3.0.0.0")]

[System.ServiceModel.MessageContractAttribute(WrapperName="Notification", WrapperNamespace="http://schemas.microsoft.com/Sql/2008/05/Notification/", IsWrapped=true)]

public partial class Notification {

[System.ServiceModel.MessageBodyMemberAttribute(Namespace="http://schemas.microsoft.com/Sql/2008/05/Notification/", Order=0)]

public string Info;

[System.ServiceModel.MessageBodyMemberAttribute(Namespace="http://schemas.microsoft.com/Sql/2008/05/Notification/", Order=1)]

public string Source;

[System.ServiceModel.MessageBodyMemberAttribute(Namespace="http://schemas.microsoft.com/Sql/2008/05/Notification/", Order=2)]

public string Type;

public Notification() {

}

public Notification(string Info, string Source, string Type) {

this.Info = Info;

this.Source = Source;

this.Type = Type;

}

SqlAdapterBindingService class

using Test.NotificationOutsideBTS;

namespace SqlAdapterBindingNamespace {

public class SqlAdapterBindingService : NotificationOperation {

public virtual void Notification(Notification request) {

//throw new System.NotImplementedException("The method or operation is not implemented.");

NotificationAction action = new NotificationAction();

action.ProcessNotification(request);

}

In this sqlBinding service class, we simply commented out the NotImplementedException and added an Action delegate, which is coded in the main program class.

ServiceModel Configuration

The following is the configuration generated by the wizard based on the input values provided:

</behavior>

</endpointBehaviors>

</behaviors>

</behaviorExtensions>

</extensions>

<endpoint address="mssql://ewt//MyDatabase?InboundId=MyNotification"

behaviorConfiguration="InboundActionEndpointBehavior" binding="sqlBinding"

bindingConfiguration="SqlAdapterBinding" contract="NotificationOperation" />

</service>

</services>

<binding name="SqlAdapterBinding" closeTimeout="00:01:00" openTimeout="00:01:00"

receiveTimeout="00:10:00" sendTimeout="00:01:00" maxConnectionPoolSize="100"

encrypt="false" workstationId="" useAmbientTransaction="true"

batchSize="20" polledDataAvailableStatement="" pollingStatement=""

pollingIntervalInSeconds="30" pollWhileDataFound="false" notificationStatement="SELECT ID FROM dbo.MyTable"

notifyOnListenerStart="true" enableBizTalkCompatibilityMode="true"

chunkSize="4194304" inboundOperationType="Notification" useDatabaseNameInXsdNamespace="false"

allowIdentityInsert="false" enablePerformanceCounters="false"

xmlStoredProcedureRootNodeName="" xmlStoredProcedureRootNodeNamespace="" />

</sqlBinding>

</bindings>

Console Output

Sample output when the notification listener starts:

Registering Notification listener...

Service Host and Listener opened...

Waiting for notification (press any key to stop the service)...

Notification arrived:

-->Info: ListenerStarted

-->Source: SqlBinding

-->Type: Startup

Waiting for notification (press any key to stop the service)...

Output if an insert occurs in the database that matches the notification statement:

Notification arrived:

-->Info: Insert

-->Source: Data

-->Type: Change

Waiting for notification (press any key to stop the service)...

0 Comment(s)

December 21

Unrecognized element ‘sqlBinding’

by HQ\jarriaga

on 12/21/2010 5:37 PM

Category: BizTalk

Problem

You get an error such as the following while attempting to use the WCF SQL Server Adapter based on the WCF Service Model:

System.Configuration.ConfigurationErrorsException: Unrecognized element 'sqlBinding'.

Possible Solutions

Double check the installation of the WCF LOB Adapter SDK and the WCF SQL Server Adapter. Particularly, make sure that the following entries have been created as part of the service model extensions in the corresponding machine.config file. If these entries are missing, add them manually or reinstall the software.

<extensions>

<behaviorExtensions>

<add name="sqlAdapterInboundTransactionBehavior" type="Microsoft.Adapters.Sql.SqlAdapterInboundTransactionBehavior, Microsoft.Adapters.Sql, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" />

</behaviorExtensions>

<bindingElementExtensions>

<add name="sqlAdapter" type="Microsoft.Adapters.Sql.SqlAdapterBindingElementExtensionElement, Microsoft.Adapters.Sql, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" />

</bindingElementExtensions>

<bindingExtensions>

<add name="sqlBinding" type="Microsoft.Adapters.Sql.SqlAdapterBindingCollectionElement, Microsoft.Adapters.Sql, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" />

</bindingExtensions>

</extensions>
If your solution is targeting a 64-bit machine (x64), compile your assembly in 32-bit mode (x86). The following is an example of the post-build event "corflags" command you could use for such purpose:

"C:\Program Files (x86)\Microsoft SDKs\Windows\v7.0A\Bin\corflags.exe" /32BIT+ "c:\Users\myusername\Documents\Visual Studio 2008\Projects\Test.SQLNotification\Test.SQLNotification\bin\Debug\Test.SQLNotification.exe"