ASP ODBC error with passing xml data

[lleemon]

I have a vbscript/asp page that on submit I create a xml file and pass to a stored procedure and want to insert into a current table.

Here is the error I am receiving:
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]INSERT failed because the following SET options have incorrect settings: 'ANSI_NULLS'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods.

Any ideas how I can get this to work?

vbscript code

' Selected constants from adovbs.inc
Const adCmdStoredProc    = 4
Const adInteger          = 3
Const adCurrency         = 6
Const adExecuteNoRecords = 128
Const adVarChar 		= 200
Const adParamUnknown 	= 0  	'Direction is unknown
Const adParamInput 		= 1 	'Input parameter
Const adParamOutput 	= 2 	'Output parameter
Const adParamInputOutput 	= 3 'Both input and output parameter
Const adParamReturnValue 	= 4 'Return value

Dim lngAdded, sResult
lngAdded = 0
sResult = ""

Set adoCmd = Server.CreateObject("ADODB.Command")
Set adoCmd.ActiveConnection = conn

'Set properties of the command
With adoCmd
    .CommandText = "spInsertSP"
    .CommandType = adCmdStoredProc

    'Create the parameters
    'return value must be first parameter
    '(paramname, paramtype, direction, size, value)
    .Parameters.Append .CreateParameter ("return", adInteger, adParamReturnValue)
    
    .Parameters.Append .CreateParameter ("@A_LINK_TEXT", adVarChar, adParamInput, 35, UCase(Request.Form("txtLinkText")))
    .Parameters.Append .CreateParameter ("@A_XML",201, adParamInput, len(sXML), sXML)

    'Run the stored procedure
    .Execute

    'Extract the return value
    lngAdded = .Parameters("return").Value
End With

stored procedure calling

set ANSI_NULLS OFF
set QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[spInsertSP]
(
	@A_LINK_TEXT varchar(35),
	@A_XML xml
)
AS
DECLARE @A_ID int
   BEGIN
    SET NOCOUNT ON
    INSERT INTO TABLE (A_LINK_TEXT, A_ENTRY_DT)
    VALUES (@A_LINK_TEXT, getdate())
		
	SET @A_ID = @@IDENTITY
	
	INSERT INTO ARTICLES_CASE_STUDIES_RELATED
	SELECT @A_ID, e.value('@csid', 'INT'), '', 1, 0, getdate()
	FROM @A_XML.nodes('//articlelink') articles_case_studies_related(e)

	RETURN @A_ID
   END

sample xml passing

<articlelinks>
<articlelink csid="10" />
<articlelink csid="20" />
</articlelinks>

[brucevde]

The ANSI_NULLS option must be ON when using the XML Data type.

Drop the stored procedure and recreate it with ANSI_Nulls On.

From BOL
When querying xml type columns or variables using xml data type methods, the following options must be set as shown.

SET Options Required Values
ANSI_NULLS ON
ANSI_PADDING ON
ANSI_WARNINGS ON
ARITHABORT ON
CONCAT_NULL_YIELDS_NULL ON
NUMERIC_ROUNDABORT OFF
QUOTED_IDENTIFIER ON

If the options are not set as shown, queries and modifications on xml data type methods will fail.