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

You may encounter when using the SQL Transport Schema Generation Wizard is an error message "Failed to execute SQL Statement. Please ensure that the supplied syntax is correct." in BizTalk When you are trying to generate from SQL server 2008

Ways to Resolve this :

First try to execute the following stored procedure, if any issue occurs then the issue is not with the stored procedure.

CREATE TABLE Employee(id INT ,EmpName VARCHAR(50));
ALTER PROCEDURE GetEmpDetails
@ID int
AS
select * from Employee
where id = @ID for xml auto,xmldata

If it get executed successfully, then check on your stored procedure.

Else,

This issue can be resolved by appending ELEMENTS at the end of a stored procedure as in the following example:

CREATE PROCEDURE SP_GetNewStudentInfo
AS
DECLARE @Process_Date DateTime
SET @Process_Date=GetDate()
Update StudentInfo Set ProcessedDate=@Process_Date Where ProcessedDate is NULL
SELECT StudentID, Lastname, Firstname, Term, GPA, DateofAdmission FROM StudentInfo WHERE ProcessedDate=@Process_Date FOR XML AUTO, ELEMENTS

For reference/source see Biztalk Adapter for SQL - Issues/Resolutions .
---------------------------------------------------------------------------------------------------------
In SQL Server 2005 also, you may get this error.

Cause

This problem occurs when the database table contains a column that has one of the following data types:

The varbinary(MAX) data type
The varchar(MAX) data type
The nvarchar(MAX) data type
The xml data type
These data types are not supported by the SQL adapter in BizTalk Server.

Resolution

These data types are new data types in SQL Server 2005. When the database table contains a column that has one of these data types, do not use the SQL adapter in BizTalk Server to insert data into the database table. Additionally, do not use the SQL adapter in BizTalk Server to retrieve data from the database table.