webservice proc xml parameter 500 error

i have a webservice that has a function which runs a proc. for some reason tho

Code:

---

        substrEmail.Append("<ROOT>")
        For loopindex = 0 To groupsToEmail.Count - 1
            substrEmail.Append("<group groupname=""" & groupsToEmail(loopindex) & """/>")
        Next
        'close the root node
        substrEmail.Append("</ROOT>")
        'get the users belonging to the groups
        Dim reader As SqlDataReader
        Dim dbs As New OI.Data.OIDB(ConString)
        reader = dbs.GetExternalUsersToEmail(substrEmail.ToString())

---

which passes the xml string of parameters to this function

Code:

---

        Public Function GetExternalUsersToEmail(ByVal groupXML As String) As SqlDataReader
            'function gets groups to email
            Dim reader As SqlDataReader
            Dim parameters As SqlParameter() = { _
                New SqlParameter("@group", SqlDbType.VarChar, 1000)}
            parameters(0).Value = groupXML
            reader = RunProcedure("sp_GetExternalUsersfromGroups", parameters)
            Return reader
        End Function

---

which calls RunProcedure

Code:

---

        Protected Overloads Function RunProcedure( _
            ByVal storedProcName As String, _
            ByVal parameters As IDataParameter()) _
            As SqlDataReader

            Dim returnReader As SqlDataReader

            Connection.Open()
            Dim command As SqlCommand = _
                BuildQueryCommand(storedProcName, parameters)
            command.CommandType = CommandType.StoredProcedure
          'errors here on the executereader (i think)
            returnReader = command.ExecuteReader( _
                CommandBehavior.CloseConnection)
            'connection will be closed automatically

            Return returnReader

        End Function

---

The ws throws a 500 error on the commandexecute reader error
I don't know whats going on with it (obviously a 500 error isn't very descriptive) i'm wondering whether it is in any way related to the fact that i am passing a string of xml?

any help would be appreciated

the proc works, i can exec it from query analyser, so that shouldn't be a problem

here is the proc code

Code:

---

CREATE PROC sp_GetExternalUsersfromGroups
(
        @groups varchar(1000)
)
AS
BEGIN
        SET NOCOUNT ON

        DECLARE @DocHandle int

        EXEC sp_xml_preparedocument @DocHandle OUTPUT, @groups

        SELECT username, emailaddress
        FROM       
                tbl_externalusers as e
        inner join
                tbl_group as g on e.groupid = g.groupid
        inner join
                OPENXML (@DocHandle, '/ROOT/group',1) WITH (groupname  varchar(1000)) AS x
                ON g.groupname = x.groupname

        EXEC sp_xml_removedocument @DocHandle
END
GO

---

Firstly you need to try and get more details on your error. Try going to Tools, Internet Options, Advanced, 'Show Friendly HTTP Error Messages' and uncheck the box.

A wild guess before you do that... maybe the datareader isn't being serialized, perhaps you'd have to use a dataset. I know I ran into a similar problem a few weeks ago.

Hello, found the problem.

My procs paramater is @groups, but in the da code i had put the paramater as @group. :)

However I now have a new problem. for some reason the proc only returns one result (with what i am passing it at the moment it should return 3 rows)

not sure why that would be

not sure it'll help but this is the xml i am currently passing as a parameter

Code:

---

<ROOT><group groupname="test"/><group groupname="test2"/></ROOT>

---

running the proc in qa correctly returns 3 rows. but not when the app runs the proc

Oh and this is the loop code. the loop code is fine, the problem seems to be that the reader itself does not the 3 rows. and i have no idea why it doesn't

Code:

---

        reader = dbs.GetExternalUsersToEmail(substrEmail.ToString())
        While reader.Read
            'send a mail to each one
            mail.SendEmail(False, reader.GetValue(1), strEmail, "Alert from blah", "Hello " & reader.GetValue(0) & vbCrLf & message, "OI", "alert from " & userName)
            reader.NextResult()
        End While

---

Sorted this problem too.

Strangely i changed the da code to return a dataset and it does pull back all rows

I can only assume that the recordset was in an xml format and the sqldatareader didn't error, but just picked up the first row.

and i guess the dataset was able to pick up the whole structure of the recordset.

oh well it works.

Quote:

---

Originally Posted by sagey

Sorted this problem too.

Strangely i changed the da code to return a dataset and it does pull back all rows

I can only assume that the recordset was in an xml format and the sqldatareader didn't error, but just picked up the first row.

and i guess the dataset was able to pick up the whole structure of the recordset.

oh well it works.

---

w00t! I was right about something :afrog: