Hello,
Can someone please help explain if the way MS SQL Server 2005 returns result from stored procedures is different from MS SQL Server 2000
The problem i am having is that i have lots of code that used to connect to SQL-2000 and did this:
SqlDataAdapter da = new SqlDataAdapter(sqlCmd);
ds = new DataSet();
da.Fill(ds);
if (ds.Tables[0].Rows.Count > 0)
//process rows
SqlCmd Type is a StoredProcedure btw, in SQL-2000 if the stored procedure result is empty, i still get one table in the dataset tables collection "table[0]", and the table is empty; in SQL-2005 however i get NO tables at all if the result is empty, which translates into a fiesta of NullReferenceExceptions.
i doubt that this has anything to do with ADO.NET or adapter Type (SqlDataAdapter btw), since i triedexecuting the stored procedure with the parametrs by hand from both SQL Managment Studio and Enterprise Manager; in the case of SQL-2000 i get empty table i.e. no rows but columns names; while in SQL-2005 i get nothing! just "Command(s) Completed Successfully"
Can this behaviour be chnages (i have a large legacy app that depends on the old behaviour)
Or am i doing something wrong
any help is greatly appreciated.
Thanks
A Ghoneim
stored procedure return/result behaviour change from 2000 -> 2005 ?
James Dean
I tried with both SQL Server 2000, SQL Server 2005 and it seemed to work fine.
Table : create table tb1(a int)
Stored Procs:
1) create procedure pr1 as begin select * from tb1 end
2) create procedure pr2(@p1 int) as begin select * from tb1 where a=@p1 end
Sample code below:
Dim sqlcn As New SqlConnection
Dim sqlcmd As New SqlCommand
Dim sqladap As SqlDataAdapter
Dim dtset As New DataSet
Dim dr As DataRow
sqlcn.ConnectionString = "Data Source=.;Integrated Security=SSPI;"
sqlcn.Open()
sqlcmd.Connection = sqlcn
'First Stored Proc
'sqlcmd.CommandType = CommandType.StoredProcedure
'sqlcmd.CommandText = "pr1"
'Second Stored Proc
'sqlcmd.CommandType = CommandType.StoredProcedure
'sqlcmd.CommandText = "pr2"
'sqlcmd.Parameters.Add("@p1", SqlDbType.Int).Value = 1
sqladap = New SqlDataAdapter(sqlcmd)
sqladap.Fill(dtset)
If (dtset.Tables(0).Rows.Count > 0) Then
For Each dr In dtset.Tables(0).Rows
For lc = 0 To dtset.Tables(0).Columns.Count - 1
MsgBox(dr(lc).ToString())
Next
Next
Else
MsgBox("No Rows of data")
End If
sqlcn.Close()
Hope this helps and answers your question.
jsyong
thanks, I tried another stored procedure and it works fine under both.
So i'm positive now that there is something wrong wiht my stored procedure or at least something in Transact-SQL under 2005 that makes it behave differently than 2000. it's a pretty big stored procedure.
i checked the MSDN section on backward compatability, and i think it has something to do with UNION inside an INSERT to a #temp table, since under 2005 this can lead to data cast errors.
Anyways, Thank you very much for you reply