Stored Procedure Error

[EZEason]

I'm new to stored Procedures. I'm trying to run a porcedure from VB/ADO in SQL 2000. Below in my VB code.

'Open XML connection and load XMLDOM object
 On Error GoTo Err_XML
 Set prmXML = New ADODB.Parameter
 With sqlCommand
    .ActiveConnection = sqlCon
    .CommandText = "sp_ImportXML"
    .CommandType = adCmdStoredProc
    Set prmXML = .CreateParameter("@strXML", adChar, adParamInput, 5000, objXMLDOM.xml)
    .Parameters.Append prmXML
    .Execute , , adExecuteNoRecords
End With

Here is my SP:

CREATE PROCEDURE [dbo].[sp_ImportXML]
    @strXML Char
AS

    DECLARE @iDoc int

    EXECUTE sp_xml_preparedocument @iDoc OUTPUT, @strXML

    INSERT INTO PDP (IID, RID, ResvTech, PYear, PMonth, AWord, UWord, AValue, RawValue, StdValue)

      (SELECT * FROM OpenXML(@iDoc, '/batch/PDP', 1)

                WITH  (Field NVARCHAR(50), Reservoir NVARCHAR(50), ResvTech Char(10), 
		PYear SMALLINT, PMonth SMALLINT,  AWord CHAR(5), UWord CHAR(5),
		AVal DECIMAL (9), RVal DECIMAL(9), SVal DECIMAL(9)))

    EXECUTE sp_xml_removedocument @iDoc
GO

When I get to .CreateParameter part of my code I get and error. I can't even tell what type of error it is. Can anyone give me a suggetion on what I'm doing wrong???




What doesn't kill you makes you stronger.

 

[yksvaan]

I think
@strXML Char
defaults to one character, but maybe it has nothing to do with this.

Cheers


[blue]Backup system is as good as the latest recovery[/blue]

 

[nigelrivett]

See

http://www.nigelrivett.net/VB6DataAccessLayer.html

For creating an adodb parameter (at the end).

Do you want a fixed length? I would have thought advarchar would be better (see the link for setting the length).

If the error is really on the create parameter then it's nothing to do with the SP as you haven't tried to call it yet - although the parameter should be varchar(5000)

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[EZEason]

I do not want a fixed length.

What doesn't kill you makes you stronger.

 

[SQLSister]

You have to have a fixed length in the SP. If you do not specify a length, it will use the default of 1. Make it nvarchar (4000) or varchar (8000) if you must, but but need to define the length in the sp.

Questions about posting. See faq183-874