Groups > comp.databases.ms-sqlserver > #1193

Re: SSE2008: #Tables, Stored Procedures, Avoiding ActiveX

From	Erland Sommarskog <esquel@sommarskog.se>
Newsgroups	comp.databases.ms-sqlserver
Subject	Re: SSE2008: #Tables, Stored Procedures, Avoiding ActiveX
Date	2012-07-31 21:45 +0200
Organization	Erland Sommarskog
Message-ID	<XnsA0A1DD643649CYazorman@127.0.0.1> (permalink)
References	<b3ge181eguiicnuugkkak08jcpq3a0hmgd@4ax.com> <XnsA0A17141AB34CYazorman@127.0.0.1> <cj0g181jboo2s984amhsd95199t3cs5jes@4ax.com>

Show all headers | View raw

Gene Wirchenko (genew@ocis.net) writes:
>      I gave a simple example of error return.  There may be more rows;
> OrderBy is for presenting them in the correct order.  The additional
> rows may be added one at a time so I went with a table.  Is there a
> better way?

I can only work from the example that I saw, and that looked funny.

The normal procedure is to raise an error when there is one, and then
exit. Only exceptionally do you care to produce multiple errors. Partly
due to that the increase complexity induces a risk in the error-handling
code. And when the error-handling code fails and produces an error,
the user is left in the dark entirely. And often ourselves as the
support and maintenance person tot.

>      (I do not know what to call the level that is between the browser
> and SQL Server.  It a) receives the HttpXMLRequest and queries SQL and
> b) receives the resultset from SQL Server and packages it for the
> browser.  What is it called?  I will now refer to it as Glue.)

I usually call that a "web server". A more general term is "middle tier", 
which also can cover forms application that does not communicate directly
with the database server.

>      IIUC, you are saying that if I try to return more than one table?
> Not the case.  Either I return the table data requested, or I return
> error data.  I do not even know how I would return more than one
> resultset.  Is it even possible?  

Yet the procedure you posted can potentially produce two result sets!

Yes, you can easily produce multiple result sets from SQL Server, and
you can easily test this in SSMS. But not all applications handle
multiple recordsets well, partly due to flaws in the client API.
ADO .Net does a good job in this area. (In difference to old ADO.)

>      No.  The purpose of the procedure is to return the row or that
> there is not one.  If the row is there, the procedure should return
> it.  I need the data to populate the form.

OK. Let it stop there, and don't deal whether this is an error or not.
It's up to the outer layer to decide whether this is an error or not.

>      IIUC, I read the rowversion value along with the rest of the row
> and write it back with the rest of the update.  

You don't write it back - you can't modify a rowversion column. But your
update looks like this:

  UPDATE tbl
  SET    ...
  WHERE  keycol = @key
    AND  tstamp = @tstamp
  IF @@rowcount = 0
  BEGIN 
     RAISERROR ('The row has been modified by another user', 16, 1)
     RETURN 1
  END

>      That means that the rowversion value makes it to the front-end,

Not necessarily. It could stay in the middle tier, but that depends on
how you design that part of the application. If you keep it in the
web server, you need to maintain state there, which I guess comes
with its own set of problems. (I have never been involved with 
designing a web app, so I don't know what I'm talking about.)

-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

SSE2008: #Tables, Stored Procedures, Avoiding ActiveX Gene Wirchenko <genew@ocis.net> - 2012-07-30 19:37 -0700
  Re: SSE2008: #Tables, Stored Procedures, Avoiding ActiveX Gene Wirchenko <genew@ocis.net> - 2012-07-30 19:47 -0700
  Re: SSE2008: #Tables, Stored Procedures, Avoiding ActiveX Erland Sommarskog <esquel@sommarskog.se> - 2012-07-31 09:08 +0000
    Re: SSE2008: #Tables, Stored Procedures, Avoiding ActiveX Gene Wirchenko <genew@ocis.net> - 2012-07-31 09:26 -0700
      Re: SSE2008: #Tables, Stored Procedures, Avoiding ActiveX "Bob Barrows" <reb01501@NOyahooSPAM.com> - 2012-07-31 14:12 -0400
        Re: SSE2008: #Tables, Stored Procedures, Avoiding ActiveX Gene Wirchenko <genew@ocis.net> - 2012-07-31 15:02 -0700
          Re: SSE2008: #Tables, Stored Procedures, Avoiding ActiveX Erland Sommarskog <esquel@sommarskog.se> - 2012-08-01 21:04 +0200
      Re: SSE2008: #Tables, Stored Procedures, Avoiding ActiveX Erland Sommarskog <esquel@sommarskog.se> - 2012-07-31 21:45 +0200
        Re: SSE2008: #Tables, Stored Procedures, Avoiding ActiveX Gene Wirchenko <genew@ocis.net> - 2012-07-31 15:02 -0700
          Re: SSE2008: #Tables, Stored Procedures, Avoiding ActiveX Erland Sommarskog <esquel@sommarskog.se> - 2012-08-01 20:59 +0200
            Re: SSE2008: #Tables, Stored Procedures, Avoiding ActiveX Gene Wirchenko <genew@ocis.net> - 2012-08-01 13:30 -0700
              Re: SSE2008: #Tables, Stored Procedures, Avoiding ActiveX Erland Sommarskog <esquel@sommarskog.se> - 2012-08-01 23:27 +0200
  Re: SSE2008: #Tables, Stored Procedures, Avoiding ActiveX "Bob Barrows" <reb01501@NOyahooSPAM.com> - 2012-07-31 14:25 -0400
    Re: SSE2008: #Tables, Stored Procedures, Avoiding ActiveX Gene Wirchenko <genew@ocis.net> - 2012-07-31 15:02 -0700
      Re: SSE2008: #Tables, Stored Procedures, Avoiding ActiveX Jeroen Mostert <jmostert@xs4all.nl> - 2012-08-01 00:26 +0200
        Re: SSE2008: #Tables, Stored Procedures, Avoiding ActiveX Gene Wirchenko <genew@ocis.net> - 2012-07-31 17:43 -0700
          Re: SSE2008: #Tables, Stored Procedures, Avoiding ActiveX "Bob Barrows" <reb01501@NOSPAMyahoo.com> - 2012-08-01 06:29 -0400
            Re: SSE2008: #Tables, Stored Procedures, Avoiding ActiveX Jeroen Mostert <jmostert@xs4all.nl> - 2012-08-01 20:40 +0200
              Re: SSE2008: #Tables, Stored Procedures, Avoiding ActiveX Erland Sommarskog <esquel@sommarskog.se> - 2012-08-01 21:02 +0200
              Re: SSE2008: #Tables, Stored Procedures, Avoiding ActiveX "Bob Barrows" <reb01501@NOyahooSPAM.com> - 2012-08-01 16:02 -0400
          Re: SSE2008: #Tables, Stored Procedures, Avoiding ActiveX Jeroen Mostert <jmostert@xs4all.nl> - 2012-08-01 20:29 +0200
      Re: SSE2008: #Tables, Stored Procedures, Avoiding ActiveX Erland Sommarskog <esquel@sommarskog.se> - 2012-08-01 09:14 +0200
        Re: SSE2008: #Tables, Stored Procedures, Avoiding ActiveX Jason Keats <jkeats@melbpcDeleteThis.org.au> - 2012-08-04 18:14 +1000
          Re: SSE2008: #Tables, Stored Procedures, Avoiding ActiveX Erland Sommarskog <esquel@sommarskog.se> - 2012-08-04 21:15 +0200
            Re: SSE2008: #Tables, Stored Procedures, Avoiding ActiveX Jason Keats <jkeats@melbpcDeleteThis.org.au> - 2012-08-05 18:10 +1000
              Re: SSE2008: #Tables, Stored Procedures, Avoiding ActiveX Erland Sommarskog <esquel@sommarskog.se> - 2012-08-05 10:50 +0200
                Re: SSE2008: #Tables, Stored Procedures, Avoiding ActiveX Jason Keats <jkeats@melbpcDeleteThis.org.au> - 2012-08-06 00:03 +1000
                Re: SSE2008: #Tables, Stored Procedures, Avoiding ActiveX Erland Sommarskog <esquel@sommarskog.se> - 2012-08-05 20:21 +0200
                Re: SSE2008: #Tables, Stored Procedures, Avoiding ActiveX Jason Keats <jkeats@melbpcDeleteThis.org.au> - 2012-08-06 19:44 +1000
                Re: SSE2008: #Tables, Stored Procedures, Avoiding ActiveX Erland Sommarskog <esquel@sommarskog.se> - 2012-08-06 20:54 +0200

csiph-web