Geeks With Blogs

Connected Systems Chilled Out Blog Hanging stuff together in a meaningful way with some fun added

The answer came form Dave Robinson - one of the great guys at Solidsoft (WWW.Solidsoft.com)

  1. In your schema which defines your stored procedure (for insert etc) you need to set the xml body field to a distinguished field.
  2. When mapping from your source schema to your sql schema you will map the values that you want and also you have to map a ‘dummy’ entry to the body parameter (because it’s a distinguished field it must be initiliazed).
  3. After your map use a message assignment shape to set the distinguished field to the inputdoc.outerxml (or if you don’t want the full document then you will need to do some XmlDocument manipulation).

 

Posted on Friday, March 4, 2005 10:44 AM BizTalk 2009 | Back to top


Comments on this post: How to pass an ntext parameter to a stored procedure using the sql adapter.

# re: How to pass an ntext parameter to a stored procedure using the sql adapter.
Requesting Gravatar...
hi,

I want to have a Sql adapter which will execute a stored procedure.

I want to decide upon the execution of the stored proc and the parameters that i am going to pass to it in run time.

How can I do it?

Regards,
Mukesh
Left by Mukesh on Apr 20, 2005 3:49 PM

# re: How to pass an ntext parameter to a stored procedure using the sql adapter.
Requesting Gravatar...
Many developers have asked how to insert data into an nText field in SQL Server with ADO. Most try to use standard string SQL statements and run into problems with special characters.

In order to do it properly, you'll really need to use the ADO command object. Here's a little sample of the syntax with a stored procedure name spPost and the nText column being @bodyText:

Dim lRecs
Dim moADOCon
Dim moADOCom

Set moADOCon = Server.CreateObject("ADODB.Connection")
Set moADOCom = Server.CreateObject("ADODB.Command")

moADOCon.Open "your connection string"

With moADOCom
.ActiveConnection = moADOCon
.CommandText = "spPost"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("@RETURN_VALUE", adInteger, adParamReturnValue,0)
.Parameters.Append .CreateParameter("@PostID", adInteger, adParamInput, , msPostID)
.Parameters.Append .CreateParameter("@bodytext", adVarWChar, adParamInput, 1073741823, msBodyText)
.Execute lRecs, , adExecuteNoRecords
End With

moADOCon.Close
Set moADOCom = nothing
Set moADOCon = nothing


SQL Server Stored procedure code

CREATE PROCEDURE spPost
(
@PostID int,
@BodyText ntext
) AS

update MyTable
Set BodyText = @BodyText
Where PostID = @PostID


GO
Left by visu on Oct 03, 2005 10:46 AM

Your comment:
 (will show your gravatar)


Copyright © BizTalk Visionary | Powered by: GeeksWithBlogs.net