Blogging BizTalk

Lets Discuss BizTalk

SQL Adapter Metadata Wizard Error : Failed to execute SQL Statement. Please ensure that the supplied syntax is correct.


Note: If you are trying to pool  data from a SQL table column of XML data type may be you are better off not try using the SQL adapter, use WCF SQL adapter instead.

I got this exception while I was trying to generate schema for a stored proc using VS 2005 with BizTalk 2006 R2.

Error

This seems to be a known issue with the new data types introduced in SQL 2005 version.

The varbinary(MAX) data type
The varchar(MAX) data type
The nvarchar(MAX) data type
The xml data type

Microsoft provides these steps to correct the issue:

Re register the Microsoft.BizTalk.Adapter.SQL COM+ application by using the Regsvcs.exe utility. On a computer that has Microsoft Visual Studio installed, follow these steps:
Open the Visual Studio command window, and then locate the BizTalk installation folder.

Note By default, BizTalk Server 2006 is installed in the C:\Program Files\Microsoft BizTalk Server 2006 folder. BizTalk Server 2004 is installed in the C:\Program Files\Microsoft BizTalk Server 2004 folder.
Register the following DLLs. To do this, follow these steps:
Type regsvcs Microsoft.BizTalk.Adapter.SQL.dll , and then press ENTER.
Type regsvcs Microsoft.BizTalk.Adapter.SQLAdmin.dll, and then press ENTER.
Type regasm /codebase Microsoft.BizTalk.Adapter.SQLAdmin.dll , and then press ENTER.
The Regsvcs.exe utility is located in the \WINNT\Microsoft.NET\Framework\Framework Version folder.

Registration

Few more points to consider would be:

Make sure the user who runs the wizard has DBO access.

Be sure that if your database only accepts Windows authentication only, that you didn’t select the ‘Use a specific user name and password’ on the Data Link Properties pane, instead select ‘Use Windows NT Integrated security.

Try using “FOR XML AUTO, XMLDATA” for SP which returns a record set

Use WCF SQL adapter instead.

https://msdn.microsoft.com/en-us/library/ee267582%28v=bts.10%29.aspx?f=255&MSPPError=-2147217396

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

D Goins Insperience

Technological outformation for this day and age

[INACTIVE BLOG] Connected Thoughts - Thiago Almeida

Connected systems and the occasional picture

Uri Katsir's Blog

BizTalk , BizTalk RFID and .NET

prashantbiztalkblogs

My BizTalk Experiences

BizTalk Server Tutorial

BizTalk Server Concepts and Common Errors

Extremely Talented Monkeys

A Technical Blog by Ed Jones: Azure, .NET, BizTalk, WCF, and SQL Server

On All Things Web

Discussing web development without limits

Vikas Bhardwaj's Blog

Articles about BizTalk Server and .NET C#

Cloud develop

a blog about (cloud) development... because I'm a nerd

Hooking Stuffs Together

My learning logs from day to day work experience about Integration platform using Microsoft technologies.

Connected Pawns

Mainly BizTalk & Little Chess

jhelatissimo

a blogger in the process.

MS Innovations Blog

Tips, Tricks, and Workarounds for BizTalk and other Microsoft technologies

srirambiztalks

Katradhu Kaialavu,Kalladhadhu Ulagalavu!

Vijay Microsoft Technical

BizTalk, WCF, ESB ToolKit, Windows Azure

Mind Over Messaging

Musings on BizTalk, Azure, and Enterprise Integration

%d bloggers like this: