SQL Exception when executing stored procedure from within Biztalk

Question

We have a stored procedure in place that creates a new SSIS execution and starts it:

Declare @execution_id bigint

EXEC [SSISDB].[catalog].[create_execution] 
    @package_name=N'00 XXXX.dtsx', 
    @execution_id=@execution_id OUTPUT, 
    @folder_name=N'XX', 
    @project_name=N'XXX';

EXEC [SSISDB].[catalog].[start_execution] @execution_id;

When we call this stored procedure logged on using SQL Server Management Studio, this works perfectly. However, when we execute this stored procedure from within BizTalk (as a BTS service account user) we receive this error:

System.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near '-'.
Incorrect syntax near '-'.
Incorrect syntax near '-'.

Server stack trace:
at System.Runtime.AsyncResult.End[TAsyncResult](IAsyncResult result)
at System.ServiceModel.Channels.ServiceChannel.SendAsyncResult.End(SendAsyncResult result)
at System.ServiceModel.Channels.ServiceChannel.EndCall(String action, Object[] outs, IAsyncResult result)
at System.ServiceModel.Channels.ServiceChannel.EndRequest(IAsyncResult result)

Exception rethrown at [0]:
at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)
at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)
at System.ServiceModel.Channels.IRequestChannel.EndRequest(IAsyncResult result)
at Microsoft.BizTalk.Adapter.Wcf.Runtime.WcfClient`2.RequestCallback(IAsyncResult result)

The schema we use from BTS to MS SQL:

<?xml version="1.0" encoding="utf-16"?>
<schema xmlns:b="http://schemas.microsoft.com/BizTalk/2003" xmlns:ns0="http://schemas.microsoft.com/Sql/2008/05/TypedProcedures/dbo" elementFormDefault="qualified" targetNamespace="http://schemas.microsoft.com/Sql/2008/05/TypedProcedures/dbo" version="1.0" xmlns="http://www.w3.org/2001/XMLSchema">
  <annotation>
    <appinfo>
      <fileNameHint xmlns="http://schemas.microsoft.com/servicemodel/adapters/metadata/xsd">TypedProcedure.dbo</fileNameHint>
    </appinfo>
  </annotation>
  <element name="StartBifImport">
    <annotation>
      <documentation>
        <doc:action xmlns:doc="http://schemas.microsoft.com/servicemodel/adapters/metadata/documentation">TypedProcedure/dbo/StartBifImport</doc:action>
      </documentation>
    </annotation>
    <complexType>
      <sequence />
    </complexType>
  </element>
</schema>

The BTS service account has the db_owner role on the database where the stored procedure resides, and we have given it explicitly all 'grant' permissions on the SSIS folder and SSIS package.

We can see in the executions table that no execution is created, so something seems to go wrong on that call. When connecting with SQL Profiler we see the same error returning, but without much further context as to what is wrong; leaving is without much trace.

Any pointers/ideas on how the debug or resolve this issue?

Through a Send Port (schema based). The call to the stored procedure itself works, since when we call it with the contents place in comments it executes without any problems. — jeroenverh
I added the schema to my initial question. The schema message validates against the schema. When we remove everything from the stored proc, it works just fine. — jeroenverh
Try using SQL Server PRofiler to capture the actual SQL call that gets generated, see if that helps. — Dan Field

Dieter Gobeyn Dieter Gobeyn · Accepted Answer · 2015-08-04T11:15:15

I suggest not to use a "Strongly-Typed Procedure" schema, but a "Procedure" schema. The "Strongly-Typed Procedure" from the WCF-SQL adapter has a documented issue with generating the schema for stored procedures with temp tables in it.

The SQL adapter does not support generating metadata for strongly-typed stored procedures that contain temporary tables in their definition. Instead, you should generate metadata for the same procedure from under the Procedures node while using the Add Adapter Service Reference Plug-in or Consume Adapter Service Add-in.

More information can be found here: https://msdn.microsoft.com/en-us/library/dd788435(BTS.10).aspx#BKMK_SQLMetadataStronglyTyped

SQL Exception when executing stored procedure from within Biztalk

2 Answers