Re: MSSQL vs. SYBASE

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

From: Erland Sommarskog (esquel_at_sommarskog.se)
Date: 08/10/04 

Date: Tue, 10 Aug 2004 19:24:55 +0000 (UTC)

Lewis Howell (lou_junkmail@yahoo.com) writes:
> Confirmed.
>
> I actually have an infinitely running stored proc that is a monitoring
> utility and works fine in SYBASE. In SQL Server I have to stop or cancel
> the stored proc to get any results. On occasion I get PRINT returns to
> different workstations using the ADO Connection message, but this is very
> inconsistent.
 
Here is variation:

   SET NOCOUNT ON
   DECLARE @i int
   SELECT @i = 1000
   WHILE @i > 0
   BEGIN
      SELECT i = @i
      IF @i % 100 = 0 WAITFOR DELAY '00:00:02'
      SELECT @i = @i - 1
   END

If you run this in QA, you will see that you get the result sets in
batches, but the batches are not even hundreds due to the buffering.

I also played with this with other client libraries. A DataReader in
ADO .Net did just fine, that is I got the result as soon as SQL Server
produced them.

In ADO, you have to be careful. The default cursor location is
adUseServer, but normally there is a recommendation to use client-side
cursor, but that is not possible in your case. You need a server-side
cursor. With client-side ADO waits until all data has arrived.

I've directed a question to my contacts at Microsoft to find out if there
is a way to unbuffer the output completely without using RAISERROR WITH
NOWAIT. 

-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

Relevant Pages

  • Re: ADO and Cursors with MSSQL
    ... >I think this is because ADO creates a cursor for each insert. ... So how can I free the cursors so that the SQL-Server can free the memory? ... The default memory configuration for sql server is to use all available memory. ...
    (borland.public.delphi.database.ado)
  • Re: How to Mimic Access Externally Linked Tables using ADO?
    ... > using SQL Server for efficiency reasons. ... > connection up programmatically using ADO or ADOX or some SQL Server API? ... >> newsgroup, but if you have a good reason to send me e-mail, you'll find ... ADO does not have a query engine. ...
    (microsoft.public.data.ado)
  • Re: How to access the SQL server express from mfc application?
    ... There are different ways to do this via MFC. ... You could certainly try ADO, ... the entire contents of the database to a SQL Server Standard Server. ...
    (microsoft.public.vc.mfc)
  • Re: DAO vs ADO
    ... and ADO when working with SQL Server data. ... which makes DAO a good choice. ... > add additional overhead by loading Jet, ...
    (microsoft.public.access.conversion)
  • Re: DAO vs ADO
    ... and ADO when working with SQL Server data. ... which makes DAO a good choice. ... > add additional overhead by loading Jet, ...
    (microsoft.public.access.adp.sqlserver)