Re: MSSQL vs. SYBASE

Tech-Archive recommends: Fix windows errors by optimizing your registry

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

Date: Wed, 11 Aug 2004 19:44:14 +0000 (UTC)

Lewis Howell (lou_junkmail@yahoo.com) writes:
> 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 are two suggestions that I got when inquired at Microsoft:

An MVP colleague suggested that you could peek at the output buffer with
DBCC OUTPUTBUFFER. That would require you to poll the buffer, and then
interpret what you see. I don't think I would try that myself.

A Microsoft engineer pointed out that if you lower the packet size to
the minimum 512, buffering is not going away, but the buffers will be
flushed more often. I tested this, and it had some effect, but buffering
is still apparent, not surprisingly.

You can set the default packet size by reconfiguring SQL Server, but I
recommend that you only use this lower value for packet size when you
connect to run this monitoring stored procedures, as too low packet
size can be detrimental to performance for other type of applications.

Unfortunately, I have not been able to find out how to set the packet size
from ADO. It does not seem be possible from the connection string. But
it is possible that if you examine the Properties collection on the
Connection object that you find the value there. I believe that the
name is "Packet Size".

And please keep in mind what I said about client-side cursor vs.
server-side. Since you never got any output before you killed the
process, I suspect that you were using client-side cursors. 

-- 
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

  • handling errors from stored procs
    ... SQL Server 2000 ... comprise the call to a stored proc ... the stored proc gets run...if so, it would be nice if the Access app ... - when stored proc encounters error, it returns that error message ...
    (comp.databases.ms-access)
  • Re: Changing Query behavior based on local vs. remote context?
    ... We had a business network and a process control network that had to be splittable for political reasons. ... They negotiated large frame sizes since they were both on FDDI, not considering that the equipment in the middle couldn't pass that big a packet. ... Microsoft SQL Server MVP ... do not do packet splitting can cause this. ...
    (microsoft.public.sqlserver.security)
  • [NT] Microsoft SQL Server 2000 Unauthenticated System Compromise
    ... The following security advisory is sent to the securiteam mailing list, and can be found at the SecuriTeam web site: http://www.securiteam.com ... Microsoft's database server SQL Server 2000 exhibits two buffer-overrun ... clients connecting to TCP port 1433 or both. ... This message is a single byte packet, ...
    (Securiteam)
  • Re: Retrieve error text from extended stored proc
    ... was only on first execution, the following executions, error number was 0. ... However, use OSQL with the -m-1 switch, and you will see that the RAISERROR ... Tibor Karaszi, SQL Server MVP ... > When I call the stored proc from Query Analyzer and force an error, ...
    (microsoft.public.sqlserver.programming)
  • Re: best way to retrieve thousands of records.
    ... I understand you should have a chunky not chatty interface, ... i.e code up another stored proc that accepts ... > retrieve records from sql server. ...
    (microsoft.public.dotnet.languages.vb)