I am using a stored proc to retreive a XML data field in my SQL DB (SQL Server 2012/BizTalk 2013 R2).
I am using SQL adapter. While trying to generate the schema it keeps popping the error
"Failed to execute SQL Statement. Please ensure that the supplied syntax is correct."

this is only happening if I am trying to retreive the  XML data type columns in my SP.

Going through some of the old articles it appears to be a known issue with XML files in SQL and reccomendation is to use SQL WCF adapter. However I am contrained with using SQL adapter only. Any pointers please.

Hi,

Could you please ensure that MSDTC is allowed for both Machine. There should not be any connection issue.

If Yes then please share your SQL script.

Please refer related thread to resolve your issue;

SQL Adapter returning "Failed to execute SQL statement. Please ensure that the supplied syntax is correct"

SQL Receive Adapter

Hi Please,

refer below useful article for reference;

Biztalk Adapter for SQL - Issues/Resolutions

Hi,

I need to retrieve data which is of XML type , also the SQL DB is local to my BizTalk machine.  Another point it works fine if I try to retrieve data of non XML type

GO
/****** Object:  StoredProcedure [dbo].[sp_GetTheXML]    Script Date: 9/3/2015 11:25:53 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [dbo].[sp_GetTheXML]
@ID int
AS
SET nocount on

SELECT Payload from XMLCache where Id=@ID
FOR XML AUTO, XMLDATA

Hi,

I need to retrieve data which is of XML type , also the SQL DB is local to my BizTalk machine.  Another point it works fine if I try to retrieve data of non XML type

GO
/****** Object:  StoredProcedure [dbo].[sp_GetTheXML]    Script Date: 9/3/2015 11:25:53 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [dbo].[sp_GetTheXML]
@ID int
AS
SET nocount on

SELECT Payload from XMLCache where Id=@ID
FOR XML AUTO, XMLDATA

Have you tried to change 'XMLDATA' to 'ELEMENTS'

SELECT Payload from XMLCache where Id=@ID
FOR XML AUTO, ELEMENTS

NOTE:  If you add "ELEMENTS" to the Stored Proc as I did here, it will create the schema representing each column as a "Record" node... if you remove "ELEMENTS" it will create the schema representing each column as an "Attribute" node (which may be best if you plan on promoting any of those columns for routing purposes or to inspect their values within an Orchestration). 

Reference: http://blogs.msdn.com/b/jeff/archive/2008/03/31/biztalk-adapter-for-sql-issues-resolutions.aspx

I would suggest, better to do the following steps and to use the new WCF-SQL Adapter rather than the old deprecated SQL adapter.

1. Add Items
2. Add Generated Items
3. Consume Adapter Service
4. Select sqlBinding and Configure the URI
5. Click Connect
6. Select Client (Outbound operations)
7. Select Strongly-Type Procedures
8. Select the Stored Procedure from Available categories and operations
9. Click Add
10. Give it a Filename Prefix
11. Click OK

This will generated the schemas plus binding files to create the port. You also don't need to have the FOR XML AUTO, ELEMENTS in your stored procedure any

FOR XML AUTO, ELEMENTS is tried as well :-(

Hi Johns,

Trying to see if I can still accomplish this using SQL since my client has no WCF- SQL adapter installed in production. They are little reluctant with the idea of installing a new component of BizTalk.

yes 

FOR XML AUTO, ELEMENTS is tried as well :-(

Hey, Do you try to execute the command from SQL Server?

Please try and see what result are you getting,

EXEC [sp_GetTheXM		

If its BizTalk Server 2013 R2, the WCF Adapters should have been installed with BizTalk Server itself, that's the problem.

Remind them that the SQL Adapter is officially deprecated so it's really a more risky decision to use that over installing a out of the box feature which has been shipping for ~8 years.

• Edited by Johns-305 [boatseller]MVP, Moderator 20 hours 19 minutes ago
• Marked as answer by Middleware Man 17 hours 19 minutes ago

If its BizTalk Server 2013 R2, the WCF Adapters should have been installed with BizTalk Server itself, that's the problem.

Remind them that the SQL Adapter is officially deprecated so it's really a more risky decision to use that over installing a out of the box feature which has been shipping for ~8 years.

• Edited by Johns-305 [boatseller]MVP, Moderator Friday, September 04, 2015 10:48 AM
• Marked as answer by Middleware Man Friday, September 04, 2015 1:48 PM

If its BizTalk Server 2013 R2, the WCF Adapters should have been installed with BizTalk Server itself, that's the problem.

Remind them that the SQL Adapter is officially deprecated so it's really a more risky decision to use that over installing a out of the box feature which has been shipping for ~8 years.

• Edited by Johns-305 [boatseller]MVP, Moderator Friday, September 04, 2015 10:48 AM
• Marked as answer by Middleware Man Friday, September 04, 2015 1:48 PM