Biztalk Adapter for SQL - Issues/Resolutions

  • Article

Two things that you might encounter while you are attempting to create your schema for your SQL Adapter using the "SQL Transport Schema Generation Wizard":

  1. One thing that seems to be a common problem, is the SQL Transport Schema Generation Wizard diappears or closes unexpectedly right after you have entered your Stored Procedure script or manually added your SQL script and click 'Next'.  This KB article speaks to this known issue and how to resolve it, but it might also be because you have been developing/running other local applications that are holding a connection and/or reading/writing to this table otherwise locking it out.  In this case you could try closing any other open apps (including VStudio) that may be attached to this database.  You might also restart the SQL Service for good measure.
  2. The other issue you may encounter when using the SQL Transport Schema Generation Wizard (perhaps after you've resolved the first issue) is an error message "Failed to execute SQL Statement. Please ensure that the supplied syntax is correct.".   Again this error is referenced in the KB article mentioned above, but there are a couple additional ways to resolve this...
    • 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'.
    • Check the SQL itself and reference this article "Working with the Biztalk Adapter for SQL" for some tips.  I resolved this issue be appends ELEMENTS at the end of my 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
GO

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). 

Hope this helps!