Re: SqlCommand.ExecuteReader does not populate Output Parmeters?

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

I told him as much. He still thinks it's stupid.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

"Pablo Castro [MS]" <pablocas@xxxxxxxxxxxxxxxxxxxx> wrote in message
news:OUaG1oOtFHA.2212@xxxxxxxxxxxxxxxxxxxxxxx
> The fact that output parameters are exposed at the end is actually
> completely unrelated to the DataReader interface. It has to do with how
> SQL Server executes statements and how that information flows between the
> client and the server.
>
> Without going into details on why the server works like this, you can
> verify that output parameters really come at the end of the results at the
> network protocol level. Simply execute a batch like this:
>
> SELECT * FROM <some table>
> SET @output_param = 'output parameter marker'
>
> if you use network monitor to look at the network traffic while executing
> the statement you'll see the text that you put in the output parameter
> effectively comming last, after all the rows returned by the previous
> SELECT statement. So we simply don't have the information before we
> finished consuming the reader :)
>
> --
> Pablo Castro
> Program Manager - ADO.NET Team
> Microsoft Corp.
>
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
>
> "Buddy Ackerman" <buddy_nospam@xxxxxxxxxxxxxxxxx> wrote in message
> news:elntYujsFHA.3732@xxxxxxxxxxxxxxxxxxxxxxx
>> It is stupid and you don't understand the issue. This has nothing do
>> with the return of the @@ROWCOUNT before teh rcord set has completed.
>>
>> sample proc:
>>
>>
>> create proc test
>> @numrows int output
>> AS
>> SET NOCOUNT ON
>>
>> select identity(1,1) rownum,* into #tmp from mytable
>>
>> SET @numrows = @@rowcount
>>
>>
>> select * from #tmp
>> where rownum between 11 and 20
>>
>>
>>
>>
>> Typical of a paged return. The issue here has nothing to do with SQL
>> server the issue is that the DataReader interface is not reading the
>> output parameter until after the recordset has been paged through. No
>> reason for it. Stupid.
>>
>>
>>
>>
>>
>>
>> William (Bill) Vaughn wrote:
>>> Ah, SQL Server is one of the fastest (if not the fastest), most
>>> respected and most sophisticated DBMS systems on the planet. This
>>> behavior (as stupid as you might think) has been in place since the
>>> first implementation of stored procedure OUTPUT parameters. Just because
>>> it does not work the way you want it does not make it stupid. The
>>> problem is that setting an OUTPUT parameter to a value like @@ROWCOUNT
>>> is meaningless. Consider that SQL Server does not know how many rows
>>> qualify for the most recent query until all the rows are fetched. Since
>>> SQL Server is designed to work with hundreds to thousands of users a
>>> second, the data is constantly in flux. As rows are added, changed,
>>> removed and moved around in the DBMS, the engine won't (can't) know how
>>> many rows will eventually be sent to the client until it has completed
>>> the process of retrieving and transmitting the rows to the client. SQL
>>> Server does not send any rows to the client until your application
>>> actually asks for the rows. This means your application must retrieve
>>> each and every row fetched from the server before SQL Server can compute
>>> the @@ROWCOUNT. That's one reason why OUTPUT parameters are sent last.
>>>
>


.

Relevant Pages

  • Re: SqlCommand.ExecuteReader does not populate Output Parmeters?
    ... Ah, SQL Server is one of the fastest, most respected ... and most sophisticated DBMS systems on the planet. ... you want it does not make it stupid. ... That's one reason why OUTPUT parameters are sent ...
    (microsoft.public.dotnet.framework.adonet)
  • Preventing Injection - Client Side
    ... client and SQL Server as the server. ... I am new to SQL Server and I ... Am I doing anything too stupid if the admin would prefer a more secure ... maybe on a company database? ...
    (microsoft.public.sqlserver.security)
  • Re: Upsizing issue
    ... >>> half the application because of stupid Recordset problem. ... You could link the SQL Server ... magnitude as modifying the original ASP pages. ...
    (microsoft.public.inetserver.asp.db)
  • Re: SqlCommand.ExecuteReader does not populate Output Parmeters?
    ... It is stupid and you don't understand the issue. ... The issue here has nothing to do with SQL server the issue is that the DataReader interface is not reading the output parameter until after the recordset has been paged through. ... The problem is that setting an OUTPUT parameter to a value like @@ROWCOUNT is meaningless. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Getting the Stored Procedure ReturnValue and a recordset at the same time.
    ... SQL Server Return Codes and Output Parameters, ... > See the "Handling the Gazoutas: Understanding the SQL Server Return ... >> Dim ConnectionString As String ...
    (microsoft.public.vb.database.ado)