|
Home > Archive > BizTalk Server Orchestration > March 2005 > troubling retrieving value in sql adapter
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
troubling retrieving value in sql adapter
|
|
|
| I have an orchestration that calls a sql adapter on a port. When it
was set up to just do a send, I could insert my values to the database
by mapping the passed in message to the sql message in a map then
calling the send port. Now I want to add some functionality, because I
need the inserted identity value to be sent back. I have a
request-response port set up that still inserts properly, but it hangs
on the response. I'm not sure the steps I need to take to obtain that
identity value in a response to be used in my orchestration. The
Response node of the generated sql schema only identifies a value
called "Success". I have a "Select @@Identity as 'RequestID'" at the
ned of my insert sp.
Where do I go from here? Any good examples of this out there?
Thanks in advance.
| |
| Neal Walters 2005-02-23, 5:58 pm |
| 1) Did you put "for xml, elements" on your select statement?
2) Did you re-run the SQL Adapter, or manually build the schema response
element? (make sure you have XMLDATA clause present when you run the adapter).
3) When you say "hangs" - what do you mean?
Neal Walters
http://Biztalk-Training.com - New FTP, HTTP, SQL and WebService Demos
"Chad" wrote:
> I have an orchestration that calls a sql adapter on a port. When it
> was set up to just do a send, I could insert my values to the database
> by mapping the passed in message to the sql message in a map then
> calling the send port. Now I want to add some functionality, because I
> need the inserted identity value to be sent back. I have a
> request-response port set up that still inserts properly, but it hangs
> on the response. I'm not sure the steps I need to take to obtain that
> identity value in a response to be used in my orchestration. The
> Response node of the generated sql schema only identifies a value
> called "Success". I have a "Select @@Identity as 'RequestID'" at the
> ned of my insert sp.
>
> Where do I go from here? Any good examples of this out there?
>
> Thanks in advance.
>
>
| |
|
| When i add elements to the clause it requires a mode, should I be using
auto? Based on your comments I changed my select statement at the end
of my insert sp to look like this...SELECT @@IDENTITY as 'RequestID'
FOR XML AUTO, ELEMENTS, XMLDATA
Thanks for your help, I'm going to try implementing this.
| |
|
| I only see 2 options for doing this, neither option seems to work. If
I leave this as the above Select statement, when I try and generate the
adapter, I get an error message stating that the Select statement is
missing a From clause. If I declare RequestID as an output parameter
and try and generate the adapter, I get an error message stating that
the FOR XML clause is not allowed in an assignment statement (SELECT
@RequestID = @@IDENTITY FOR XML AUTO, ELEMENTS, XMLDATA). Keep in
mind I am running both statements inside the insert sp. There has to
be a way to return values on an insert within biztalk's sql adapter.
I'm just not sure what I am doing wrong. To clarify, I am including an
abbreviated version of my sp. There are more parameters than this, but
those are irrelevant. This is the structure.
CREATE Procedure request_ins
@ApprovalLevel int = null,
@CapExpType int = null,
@Cash varchar(50) = null,
@RequestID int = null OUTPUT
AS
INSERT INTO REQUEST
( rqst_appr_level, rqst_cap_exp_type, rqst_acct_cash )
VALUES
( @ApprovalLevel,@CapExpType, @Cash )
-- return identity value
SELECT @RequestID = @@IDENTITY FOR XML AUTO, ELEMENTS, XMLDATA
GO
| |
|
| UPDATE...I have modified my sp after finding an example, but I am still
having problems. SP now looks like this at the bottom...
SELECT SCOPE_IDENTITY() AS RequestID
FROM REQUEST
FOR XML RAW, XMLDATA
This allows me to generate the sql adapter with an Response Node in my
sql schema that has a child record named row. Row has a child element
named RequestID.
I am happy to see it is now generated as part of the schema, however
when I run it I get errors still. In my event viewer I get a message
saying....
Received unexpected message type " does not match expected type
'http://bizSQL#Response'. Then I receive a time out in my web form
that is submitting to biztalk.
Any ideas???
| |
|
| I've got the same "Received unexpected message type" issue when returning a
result from the SQL adaptor. Have you found a solution?
"Chad" wrote:
> UPDATE...I have modified my sp after finding an example, but I am still
> having problems. SP now looks like this at the bottom...
>
> SELECT SCOPE_IDENTITY() AS RequestID
> FROM REQUEST
> FOR XML RAW, XMLDATA
>
> This allows me to generate the sql adapter with an Response Node in my
> sql schema that has a child record named row. Row has a child element
> named RequestID.
>
> I am happy to see it is now generated as part of the schema, however
> when I run it I get errors still. In my event viewer I get a message
> saying....
> Received unexpected message type " does not match expected type
> 'http://bizSQL#Response'. Then I receive a time out in my web form
> that is submitting to biztalk.
>
> Any ideas???
>
>
| |
| Rohith 2005-03-16, 12:12 pm |
| chad-
remove this line frpm u r PROC
@RequestID int = null OUTPUT
update..
Select.............................for xml raw, XMLDATA
Hope this helps you!!
CR-
quote:
Originally posted by Dr. B
I've got the same "Received unexpected message type" issue when returning a
result from the SQL adaptor. Have you found a solution?
"Chad" wrote:
> UPDATE...I have modified my sp after finding an example, but I am still
> having problems. SP now looks like this at the bottom...
>
> SELECT SCOPE_IDENTITY() AS RequestID
> FROM REQUEST
> FOR XML RAW, XMLDATA
>
> This allows me to generate the sql adapter with an Response Node in my
> sql schema that has a child record named row. Row has a child element
> named RequestID.
>
> I am happy to see it is now generated as part of the schema, however
> when I run it I get errors still. In my event viewer I get a message
> saying....
> Received unexpected message type " does not match expected type
> 'http://bizSQL#Response'. Then I receive a time out in my web form
> that is submitting to biztalk.
>
> Any ideas???
>
>
|
|
|
|
|