i'm talking to a sql server 2000 db via ADO.NET

in debug builds my db layer checks the execution plan for each statement you try and run so it can check for full table scans, stupid index choices etc.

on sql server 7 everything was fine. on sql server 2000 it fails if you are using command parameters. no exception, no indication of the problem anywhere i can see, just no data coming back.

so...this works:
Code:
m_oDB.GetDataset(CommandType.Text, "select * from customer where lastname = 'king'", "MyTable")
but this doesn't:
Code:
m_oDB.GetDataset(CommandType.Text, "select * from customer where lastname = @surname", "MyTable", New SqlParameter("@surname", "king"))
other relevant code looks like this:
Code:
'--------------------------------------------------------------
'-- relevant part of CheckQueryPlan() start
'--------------------------------------------------------------
oCommand = New SqlCommand("set showplan_all on", CType(oConnection, SqlConnection))
oCommand.ExecuteNonQuery()

oCommand = New SqlCommand
InitCommand(oCommand, oConnection, eCommandType, sCommandText, aoCommandParameters)
dsPlan = New DataSet
oDataAdaptor = New SqlDataAdapter(oCommand)
oDataAdaptor.Fill(dsPlan, "QueryPlan")
dtPlan = dsPlan.Tables("QueryPlan")
oCommand.Parameters.Clear()

oCommand = New SqlCommand("set showplan_all off", CType(oConnection, SqlConnection))
oCommand.ExecuteNonQuery()
'--------------------------------------------------------------
'-- relevant part of CheckQueryPlan() end
'--------------------------------------------------------------

Protected Sub InitCommand(ByVal oCommand As IDbCommand, ByVal oConnection As IDbConnection, _
ByVal eCommandType As CommandType, ByVal sCommandText As String, ByVal aoCommandParameters() As IDbDataParameter)
	If (oConnection.State <> ConnectionState.Open) Then
		oConnection.Open()
	End If

	oCommand.Connection = oConnection
	oCommand.CommandText = sCommandText
	oCommand.CommandType = eCommandType

	If ((aoCommandParameters Is Nothing) = False) Then
		AttachParameters(oCommand, aoCommandParameters)
	End If
End Sub

Protected Sub AttachParameters(ByVal oCommand As IDbCommand, ByVal aoCommandParameters() As IDbDataParameter)
	Dim oParam As IDbDataParameter

	For Each oParam In aoCommandParameters
		oCommand.Parameters.Add(oParam)
	Next oParam
End Sub
if i use command params when it gets to this bit

oDataAdaptor.Fill(dsPlan, "QueryPlan")
dtPlan = dsPlan.Tables("QueryPlan") <-- dsPlan.Tables.Count = 0 so dtPlan gets set to Nothing

anyone got any ideas?