- This topic has 3 replies, 1 voice, and was last updated 9 years, 1 month ago by
.
-
Posts
-
-
Hi there
all of the stored procedures developed for my company's database with which I have to work return a numeric value via a select statement at the end of the proc – for instance they might issue a SELECT 1 to indicate that the SP completed ok (usually an int variable is used rather than a specific number, but the effect appears to be the same).
When I try to register such an SP via the BTS SQL Transport Schema Generation Wizard I get the following message at the last step:
Failed to execute SQL Statement. Please ensure that the supplied syntax is correct. SQLXML: error loading XML result (An invalid character was found in text content.)
If I remove the SELECT {number} from the end of the stored proc or replace it with, for instance, SELECT 'A', it works fine.
Am I missing something?
Please don't tell me that BTS can't accept such a numeric response .. we'll be in a right mess if it can't!
Thx in advance
Bob
-
Hey, is there a FOR XML AUTO, <ELEMENTS> at the end of the SELECT statement? Otherwise, BizTalk can't read it. For what you're doing, assuming you aren't passing too much into the proc, I'd just write a .NET component that calls the proc and returns the "int" back to the orchestration. For me personally, unless I'm returning a block of data from a stored proc, I prefer helper components that I can call from the orchestration.
-
Hi
no there is no xml auto … it does work though if the reponse is char rather than int – I would have expected that not to work either.
I am working with several hundred legacy stored procs on an MSSQL 2000 db, so I can't go changing them. It'd be a shame to have to bypass all of the standard BTS functionality … in fact it would be a major problem since we are supposed to be adopting BTS as our corporate standard workflow / interfacing tool !
Thx for the reply
Bob
-
Hey Bob,
I see your dilemma, as it's a common one when dealing with already-existing stored procs. First, this is a great BizTalk paper on the SQL Adapter (http://msdn2.microsoft.com/en-us/library/ms935658.aspx), so check that out.
To your point, I wouldn't say that using a component to call a proc is "bypassing standard BTS functionality." When using the SQL Adapter, one of the primary "value adds" is the generation of schemas. That's not really useful in your case. Given that you're just getting a value back from your call, you could still store that value in an orchestration variable, and use the BizTalk workflow to drive a next step based on it. The SQL Adapter is just one piece that you can choose to use, or ignore. There's still lots of other interfaces you can talk to besides SQL, and the workflow piece doesn't really care where the data comes from!
Does that help at all?
-
-
-
- The forum ‘BizTalk 2004 – BizTalk 2010’ is closed to new topics and replies.