Re: Hidden error kills my transaction. Help!

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

From: Jacco Schalkwijk (NOSPAMjaccos_at_eurostop.co.uk)
Date: 03/10/04 

Date: Wed, 10 Mar 2004 09:18:31 -0000

Try SET NOCOUNT ON at the beginning of your stored procedure.

If you don't do that, SQL Server will return "n rows affected" messages, and
they are treated as separate, empty resultsets by ADO. So you don't see an
error because the first thing you get back is an empty resultset for the "n
rows affected" message. If you do NextRecordset in ADO, you will see the
error.

Btw, you have to enclose the string you have in curly brackets {} to get a
string that can be converted to a GUID. 

-- 
Jacco Schalkwijk
SQL Server MVP
"Miroo_news" <miroo@USUNTO.poczta.fm> wrote in message
news:c2mk5u$k1o$1@nemesis.news.tpi.pl...
> Hi,
> The situation is as follows:
> - I have a stored procedure with parameter
>   @ID_DOC  Char(36) OUTPUT,  --for uniqueidentifier
>   (and other paramters)
> - the first line in procedure is:
>   select @cnt = count(*) from docs where id_doc = @ID_DOC
> - the transaction begins
> - the parameter @ID_DOC is set in Delphi
>   to value (for example): 'ED5B7DD0-BBDD-45C5-912E-64F73720080B'
> - the procedure is called from Delphi using ADO
> - the procedure finishes properly
> But:
> Profiler shows there is an error:
>   Error: 8169, Severity: 16, State: 2 = 'Syntax error converting from a
> character string to uniqueidentifier.'
> The transaction is rollbacked
> The procedure goes on as the error wasn't raised
>
> Why? Why the error which rollbacks the transaction
> is not returned to Delphi as exception?
> Why it occures?
>
> Regards,
> Miroo
>
> ps.
> Don't ask me why uniqueidentifier is stored in
> char(36) parameter - this is not my own procedure.
>

Relevant Pages

  • Re: SQL 2000 Stored Procedure Problem
    ... I've got over 10 years with VB and SQL Server - ... spTestSimple: ... VB6/ADO to a SQL Stored Procedure when that Stored Procedure uses a Temp ... I added SET NOCOUNT ON as the first line in spTestTempTable - same ...
    (microsoft.public.sqlserver.security)
  • Re: multiple-step ole db operation generated errors? (Member of pr
    ... assign permissions to execute the stored procedure. ... I agree that the SET NOCOUNT ON wasn't the problem in this particular case. ... I've seen the DONE_IN_PROC messages prevent ADO apps from getting the SQL Server errors and is why I recommend it as a general practice for ADO applications. ...
    (microsoft.public.sqlserver.security)
  • Re: @@IDENTITY in SQL server
    ... Aaron Bertrand [SQL Server MVP] wrote: ... First, use a stored procedure, second, use SCOPE_IDENTITY (I assume SQL ... And I don't know what the point of "SET NOCOUNT ... Better yet, use an OUTPUT paremeter to send the IDENTITY value back to ASP, ...
    (microsoft.public.inetserver.asp.db)
  • Re: transaction error??
    ... > Theere are two sql server in different city,I connect then with VPN, then ... > use a stored procedure that does an insert to both local SQL Server 2000 ... > mesage (The current transaction could not be exported to the remote ... > SET NOCOUNT OFF ...
    (microsoft.public.sqlserver.programming)
  • Re: How to get list of EventClasses in MSSQLServer2000
    ... statement inside a stored procedure has completed.') ... SQL Server statement or stored procedure.') ... Plan','Displays the plan tree of the Transact-SQL statement executed.') ... Login','Occurs when a SQL Server login is added or removed; ...
    (microsoft.public.sqlserver.security)