2

I have created a BizTalk application that contains two orchestrations, one for each method I exposed as a SOAP service. Each method runs a specific Stored Procedure in SQL Server and returns the result. It all worked fine in development and testing but when I got to production, I hit a road bump. In our testing environments, the Stored Procedures' ownership is DBO. But in production, it is not. It is [msg]. So the procedure is [database].[msg].[stored_procedure] instead of [database].[dbo].[stored_procedure] and so I get an error.

I tried changing the SOAP Action Header but I get an error stating (correctly) that the stored procedure SP_XXXX1, for example, does not exist.

Error Description: Microsoft.ServiceModel.Channels.Common.MetadataException: Object [dbo].[SP_XXXX1] of type StoredProcedure does not exist

<BtsActionMapping xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <Operation Name="SP_XXXX1" Action="Procedure/dbo/SP_XXXX1" />
  <Operation Name="SP_XXXX2" Action="Procedure/dbo/SP_XXXX2" />
</BtsActionMapping>

I tried changing "dbo" above to "msg" but I also get an error:

Error Description: Microsoft.ServiceModel.Channels.Common.XmlReaderParsingException: The start element with name "SP_XXXX1" and namespace "http://schemas.microsoft.com/Sql/2008/05/Procedures/dbo" was unexpected. Please ensure that your input XML conforms to the schema for the operation.

Do I need to re-generate everything, run the wizard again to create schemas, etc. and then test it all again re-creating the environment in the dev environment?

I had not done this because I thought this would be easy to configure in the Send Port.

Dijkgraaf
  • 11,049
  • 17
  • 42
  • 54
Aecio Lemos
  • 61
  • 2

1 Answers1

1

If you create schemas from a stored procedure, your schema will contain references to the SQL schema (e.g. dbo). My recommendation would be to align the SQL schemas over all environments and regenerate the schema and adapt your mappings.

To be fair, in SQL, a table 'Sales' in the dbo SQL schema, is a different table than a table 'Sales' in the 'msg' SQL schema.

zurebe-pieter
  • 3,246
  • 21
  • 38
  • Thank you for your answer Pieter. I will re-generate everything and update the application schemas. I have changed the schema on my development machine to MSG instead o DBO so I can test. I wish it were simpler, but I guess it does make sense. As you pointed out, objects in different schemas in SQL ARE different. :) – Aecio Lemos Aug 30 '16 at 16:29