webservice proc xml parameter 500 error

[sagey]

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

        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

        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

        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

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

[The_Duck]

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.

[mendhak]

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.

[sagey]

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

<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

[sagey]

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

        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

[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.

[mendhak]

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