ODBC error S1008

VS 2003 C++, ODBC
I see this error sometimes, I need to know what's causing this and how to deal with it.
I have 4 threads, each has its own db connection, query statements etc.
I'm calling a stored procedure repeatedly, committing every 15 seconds.
At 15 writes/sec frequency this error occurs 5-10 times an hour, usually in bursts within just 1 minute.
At 100 writes/sec it occurs 20 times more often.
This behavior is the same for different database instances.

Here's the error:
Sql state:=S1008
[Microsoft][ODBC SQL Server Driver]Operation canceled

Any help is appreciated.
darrgyasAsked:
Who is Participating?
 
itsmeandnobodyelseConnect With a Mentor Commented:
>>>> I increased the timeout to 30 sec and I'm getting very few errors.

Why not simply repeat the call when it returned with S1008? If we assume the ODBC error occurs when the calls were too fast and the ODBC doesn't wait when the sp still was busy but returned with error instead, you simply could ignore the error and try again.

A second idea which you could try is to remove the transaction handling and the auto-commit within the sp. Instead open a transaction in your prog and do a commit when it successfully returned. That might give you back the synchronisation which currently isn't done.
0
 
evilrixSenior Software Engineer (Avast)Commented:
According to documentation (afer a little bit o' Googling) S1008 means a transaction or command time out on a synchronous connection and the driver returns a SQLState of S1008 "Operation cancelled," which is normally reserved for asynchronous activities. Open Database Connectivity (ODBC) error S1008 is mapped by the ODBC provider to DB_E_CANCELLED.

Not sure if that really helps :)
0
 
darrgyasAuthor Commented:
I did all the digging I could before asking this question here.
Unfortunately, no - it doesn't help.
That's - right, it is, effectively a timeout. But why? How to avoid it?
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
itsmeandnobodyelseCommented:
If you do native ODBC, the threads should share the environment where you have a handle HENV. Each thread should have its own connection using the (global) HENV and thus getting an own HDBC handle for that thread. HDBC and HSTMT (statement handles) never must be shared between threads.
0
 
darrgyasAuthor Commented:
Took me a while to double check everything.
The env is shared, the address in all 4 threads is the same - 0x003a1628
0
 
itsmeandnobodyelseCommented:
>>>> That's - right, it is, effectively a timeout. But why?
The most likely issue for a cancel is if you made a dead-lock on a record. Assume there is a 'select for update' on the same record issued from two different threads. If both start writing to the same fields where a constraint is on, one of them could detect a deadlock and cancel the operation. You would need to check the error-log of your DBMS to find out if that could be the case.
0
 
darrgyasAuthor Commented:
nothing in the logs

2010-03-25 08:08:51.85 server    Microsoft SQL Server  2000 - 8.00.760 (Intel X86)
      Dec 17 2002 14:22:05
      Copyright (c) 1988-2003 Microsoft Corporation
      Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

2010-03-25 08:08:51.85 server    Copyright (C) 1988-2002 Microsoft Corporation.
2010-03-25 08:08:51.85 server    All rights reserved.
2010-03-25 08:08:51.85 server    Server Process ID is 1384.
2010-03-25 08:08:51.85 server    Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL\log\ERRORLOG'.
2010-03-25 08:08:51.87 server    SQL Server is starting at priority class 'normal'(4 CPUs detected).
2010-03-25 08:08:51.98 server    SQL Server configured for thread mode processing.
2010-03-25 08:08:51.99 server    Using dynamic lock allocation. [2500] Lock Blocks, [5000] Lock Owner Blocks.
2010-03-25 08:08:52.06 server    Attempting to initialize Distributed Transaction Coordinator.
2010-03-25 08:08:58.92 spid3     Starting up database 'master'.
2010-03-25 08:08:59.24 server    Using 'SSNETLIB.DLL' version '8.0.766'.
2010-03-25 08:08:59.24 spid5     Starting up database 'model'.
2010-03-25 08:08:59.26 server    SQL server listening on 10.54.94.246: 1433.
2010-03-25 08:08:59.26 server    SQL server listening on 10.1.29.245: 1433.
2010-03-25 08:08:59.26 server    SQL server listening on 127.0.0.1: 1433.
2010-03-25 08:08:59.26 server    SQL server listening on TCP, Shared Memory, Named Pipes.
2010-03-25 08:08:59.26 server    SQL Server is ready for client connections
2010-03-25 08:08:59.29 spid3     Server name is 'RANDDSERVER1'.
2010-03-25 08:08:59.31 spid9     Starting up database 'pubs'.
2010-03-25 08:08:59.31 spid10    Starting up database 'Northwind'.
2010-03-25 08:08:59.31 spid8     Starting up database 'msdb'.
2010-03-25 08:08:59.31 spid11    Starting up database 'gps'.
2010-03-25 08:08:59.31 spid12    Starting up database 'gps2'.
2010-03-25 08:08:59.31 spid13    Starting up database 'webdata'.
2010-03-25 08:08:59.31 spid14    Starting up database '8Z1'.
2010-03-25 08:08:59.31 spid15    Starting up database 'AccuStore'.
2010-03-25 08:08:59.56 spid16    Starting up database 'Glassfiber1'.
2010-03-25 08:08:59.56 spid17    Starting up database 'mistar'.
2010-03-25 08:08:59.76 spid5     Clearing tempdb database.
2010-03-25 08:09:00.37 spid5     Starting up database 'tempdb'.
2010-03-25 08:09:05.59 spid14    Analysis of database '8Z1' (10) is 100% complete (approximately 0 more seconds)
2010-03-25 08:09:05.99 spid3     Recovery complete.
2010-03-25 08:09:05.99 spid3     SQL global counter collection task is created.
2010-03-25 08:09:08.34 spid51    Using 'xpsqlbot.dll' version '2000.80.194' to execute extended stored procedure 'xp_qv'.
2010-03-30 10:05:01.39 spid53    Using 'xpstar.dll' version '2000.80.760' to execute extended stored procedure 'xp_regread'.
2010-03-31 12:43:35.00 spid61    Using 'xplog70.dll' version '2000.80.760' to execute extended stored procedure 'xp_msver'.
0
 
darrgyasAuthor Commented:
The only action performed in all 4 threads is insert.
Right now, there's nobody else using this database.
0
 
itsmeandnobodyelseCommented:
Do you run auto commit or is it transaction driven?

Can you post the stored procedure and some of the ODBC requests that were failing with S1008? I also wonder whether the output window of Visual Studio doesn't print out more infos that only the S1008.

You also could try to calling SQLError  (or newer SQLDiagRec) in a loop to get all error information after the failed call.
0
 
darrgyasAuthor Commented:
here's the sp:

CREATE PROCEDURE [dbo].[SP_REC_POSITION_Insert]
      @RET int OUTPUT,
      @i_year int,
      @i_month int,
      @i_day int,
      @i_hour int,
      @i_minute int,
      @i_second int,
      @i_millisecond int,
      @i_radio_id int,
      @f_true_azimuth float(52),
      @d_latY float(52),
      @d_lonX float(52),
      @i_solution tinyint,
      @i_autoguide tinyint,
      @i_vehicle tinyint,
      @f_az_spreader float(52),
      @d_lat_spreader float(52),
      @d_lon_spreader float(52),
      @i_gps int,
      @I_LATCHSTATUS int,
      @C_CONTID varchar(20)='',
      @i_rsvd int,
      @c_rsvd varchar(20)=''
AS
BEGIN
    BEGIN TRAN
    --
    --  insert
    --
    INSERT INTO [dbo].[PositionRecord_type]
        (i_year
        ,i_month
        ,i_day
        ,i_hour
        ,i_minute
        ,i_second
        ,i_millisecond
        ,i_radio_id
        ,f_true_azimuth
        ,d_latY
        ,d_lonX
        ,i_solution
        ,i_autoguide
        ,i_vehicle
        ,f_az_spreader
        ,d_lat_spreader
        ,d_lon_spreader
            ,i_gps
            ,I_LATCHSTATUS
            ,C_CONTID
            ,i_rsvd
            ,c_rsvd)
     VALUES
            (@i_year,
        @i_month,
        @i_day,
        @i_hour,
        @i_minute,
        @i_second,
        @i_millisecond,
        @i_radio_id,
        @f_true_azimuth,
        @d_latY,
        @d_lonX,
        @i_solution,
        @i_autoguide,
        @i_vehicle,
        @f_az_spreader,
        @d_lat_spreader,
        @d_lon_spreader,
            @i_gps,
            @I_LATCHSTATUS,
            @C_CONTID,
            @i_rsvd,
            @c_rsvd )
    SET @RET = @@error
    IF (@@error!=0)
    BEGIN
        RAISERROR  20001 'SP_REC_POSITION_Insert: Cannot update ORDER_STATE'
        ROLLBACK TRAN
        RETURN(1)
    END
    COMMIT TRAN
      RETURN(0)
END
0
 
darrgyasAuthor Commented:
this is for SQLPrepare, fail on execute

"{CALL %s.SP_REC_CONTAINER_Insert (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}"
0
 
darrgyasAuthor Commented:
and another one:

{CALL %s.SP_REC_POSITION_INSERT (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}
0
 
darrgyasAuthor Commented:
SQLError only return one error
0
 
itsmeandnobodyelseCommented:
why don't you use a 'normal' insert statement?

0
 
darrgyasAuthor Commented:
You mean without the sp?
?
0
 
itsmeandnobodyelseCommented:
Yes. Is there any advantage of doing all inserts with the sp? The problem is that the execution of the sp was only triggered by the odbc call which doesn't wait for completion. Hence, there is no way to return any errors which may have occurred in the sp. Moreover, it could be that you get the error when doing a second call from same thread while the previous execution from same thread still was in progress. When doing multiple inserts from thread you could put them into one transaction (or even do a batch insert) what in any case means kind of a synchronisation which IMO was not granted when invoking an sp where any execution was in its own transaction.
0
 
darrgyasAuthor Commented:
I hear you, but the funny thing is that when I commit manually (every 200 records or so) or automatically - the error occurs in the same way.
Can't do bulk or batch inserts for now.
SP is faster than prepared statement, that's the reason I do this.
It's a good explanation, that the previous call may still be executing, but what do I do?
0
 
itsmeandnobodyelseCommented:
>>>> when I commit manually (every 200 records or so) or automatically - the error occurs in the same way.

Ok. But you should get a better error message if you do it within one transaction. The error only should occur when committing but not before.
0
 
itsmeandnobodyelseCommented:
>>>> It's a good explanation, that the previous call may still be executing, but what do I do?

You also could try to have two connections per thread and toggle between those.
0
 
darrgyasAuthor Commented:
2 connections per thread is basically the same as increasing the number of threads since the threads share the load, which I've tried - up to 200, in fact, no dice.
Here's the strange thing - if do not commit at all, the error still happens!
When I switch to automatic commit, tho - the error almost went away.
But it's so much slower!
Any other ideas?
0
 
itsmeandnobodyelseCommented:
>>>> Any other ideas?

Not in the moment, but maybe later. I am offline for a few hours now.
0
 
darrgyasAuthor Commented:
I increased the timeout to 30 sec and I'm getting very few errors.
0
 
darrgyasAuthor Commented:
That slows things down, I need to be able to handle at least 2-3 hundreds inserts a second.
Anyway, I think we did all we could for the moment.
In this case it really was you and nobody else,
thank you.
0
All Courses

From novice to tech pro — start learning today.