Bonjour à tous.

Je m'essaye à écrire une proc stockée sur Sybase ASE 11.9 (je sais, ce n'est
plus maintenu !!)
La voici. C'est une proc qui renvoie des valeurs qui servent pour les clés
primaires numériques

Lorsque je l'execute via transact SQL je suis déconnecté et j'ai une
méchante erreur dans le log de Sybase: 941 - Illegal database context
operation

Comment faire pour la résoudre?
J'ai essayé la solution proposé ici mais rien à faire.
Merci de votre aide.

Abel

Erreur obtenue (extrait log du serveur)
--------------------------------------------
00:00000:00010:2006/07/12 15:27:10.87 server Error: 941, Severity: 20, State: 1
00:00000:00010:2006/07/12 15:27:10.87 server Illegal database context operation.
00:00000:00010:2006/07/12 15:27:10.87 kernel ************************************
00:00000:00010:2006/07/12 15:27:10.87 kernel SQL causing error : DECLARE @i decimal
exec stpr_get_counter 'NAME',@i
SELECT @i
00:00000:00010:2006/07/12 15:27:10.87 kernel ************************************
00:00000:00010:2006/07/12 15:27:10.89 server SQL Text: DECLARE @i decimal
exec stpr_get_counter 'NAME',@i
SELECT @i
00:00000:00010:2006/07/12 15:27:10.89 kernel curdb = 5 pstat = 0x10000 lasterror = 941
00:00000:00010:2006/07/12 15:27:10.89 kernel preverror = 0 transtate = 0
00:00000:00010:2006/07/12 15:27:10.89 kernel curcmd = 224 program = SQL_Advantage
00:00000:00010:2006/07/12 15:27:10.89 kernel pc: 0x8a2977 os_get_cur_stk_desc+ 0x57 (0x13ff04c, 0x1d001d, 0x13ff04c, 0x0)
00:00000:00010:2006/07/12 15:27:10.89 kernel pc: 0x8a2977 os_get_cur_stk_desc+ 0x57 (0x13ff04c, 0x13ff5a0, 0x270f, 0x2)
00:00000:00010:2006/07/12 15:27:10.89 kernel pc: 0x883514 pcstkwalk+ 0x224 (0x1d001d, 0x2, 0x270f, 0x0)
00:00000:00010:2006/07/12 15:27:10.89 kernel pc: 0x8830ae ucstkgentrace+ 0x1ce (0x0, 0x1, 0x14, 0x0)
00:00000:00010:2006/07/12 15:27:10.89 kernel pc: 0x862864 ucbacktrace+ 0x84 (0x0, 0xffffffff, 0x13ff964, 0x4527a8)
00:00000:00010:2006/07/12 15:27:10.89 kernel pc: 0x405fe6 terminate_process+ 0x566 (0x13ff9c4, 0x639a02, 0x9, 0x29)
00:00000:00010:2006/07/12 15:27:10.89 kernel pc: 0x4527ea close_network+ 0x1a (0x9, 0x29, 0x14, 0x1)
00:00000:00010:2006/07/12 15:27:10.89 kernel pc: 0x4527a8 hdl_default+ 0x48 (0x9, 0x29, 0x14, 0x1)
00:00000:00010:2006/07/12 15:27:10.89 kernel pc: 0x639a02 s_handle+ 0x162 (0x9, 0x29, 0x14, 0x1)
00:00000:00010:2006/07/12 15:27:10.89 kernel pc: 0x452413 ex_raise+ 0x193 (0xffffffff, 0x202d2594, 0x20010060, 0x0)
00:00000:00010:2006/07/12 15:27:10.89 kernel pc: 0x47597c closedb+ 0x2ac (0x208192ac, 0x20819324, 0x0, 0x0)
00:00000:00010:2006/07/12 15:27:10.89 kernel pc: 0x65a328 execproc+ 0x828 (0x202d2594, 0x81a, 0x2081edec, 0x0)
00:00000:00010:2006/07/12 15:27:10.89 kernel pc: 0x65467a s_execute+ 0x213a (0x2080c800, 0x202d2594, 0x20010060, 0x0)
00:00000:00010:2006/07/12 15:27:10.89 kernel [Handler pc: 0x6398a0 s_handle installed by the following function:-]
00:00000:00010:2006/07/12 15:27:10.89 kernel pc: 0x63678f sequencer+ 0x15f (0x2081ed74, 0x2081edec, 0x0, 0x0)
00:00000:00010:2006/07/12 15:27:10.89 kernel pc: 0x659d8f execproc+ 0x28f (0x21, 0x202d2594, 0x202d2594, 0x20288e28)
00:00000:00010:2006/07/12 15:27:10.89 kernel pc: 0x65467a s_execute+ 0x213a (0x2082d800, 0x21, 0x202d97d1, 0x202d2594)
00:00000:00010:2006/07/12 15:27:10.89 kernel [Handler pc: 0x6398a0 s_handle installed by the following function:-]
00:00000:00010:2006/07/12 15:27:10.89 kernel pc: 0x63678f sequencer+ 0x15f (0x0, 0x20014b38, 0x1c001c, 0x20013830)
00:00000:00010:2006/07/12 15:27:10.89 kernel pc: 0x436d31 tdsrecv_language+ 0xc1 (0x3, 0x4000, 0x20014b38, 0x1c001c)
00:00000:00010:2006/07/12 15:27:10.89 kernel [Handler pc: 0x452660 hdl_backout installed by the following function:-]
00:00000:00010:2006/07/12 15:27:10.89 kernel [Handler pc: 0x5c0c40 ut_handle installed by the following function:-]
00:00000:00010:2006/07/12 15:27:10.89 kernel [Handler pc: 0x5c0c40 ut_handle installed by the following function:-]
00:00000:00010:2006/07/12 15:27:10.89 kernel pc: 0x40dc1c conn_hdlr+ 0xb5c (0x20014b38, 0x20013830, 0x1c001c, 0x20014b38)
00:00000:00010:2006/07/12 15:27:10.89 kernel pc: 0x89c1c6 kpntwrapper+ 0x96 (0x89c130, 0x20014b38, 0x0, 0x0)
00:00000:00010:2006/07/12 15:27:10.96 kernel pc: 0x7c80b50b kernel32.dll (0x661b4e, 0x20821800, 0xff, 0x2)
00:00000:00010:2006/07/12 15:27:10.96 kernel end of stack trace, spid 10, kpid 1900573, suid 11
------------------------
Transact sql pour executer la proc
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
----------
DECLARE @i decimal
exec stpr_get_counter 'NAME',@i
SELECT @i
----------
Code de la proc
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
------------------------------------------------
CREATE Procedure stpr_get_counter
@countername VARCHAR(128),
@countervalue DECIMAL = NULL OUTPUT
AS
 
 DECLARE @errmsg VARCHAR(255)
 DECLARE @strQuery VARCHAR(255)
 DECLARE @ok INT
  DECLARE @nextvalue DECIMAL
 
  SELECT @ok = 0
 SELECT @nextvalue = -1
 
 SET NOCOUNT ON
 
-- Highest transaction level. Only one connection can enter this at the same 
time
 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
 BEGIN TRAN GET_COUNTER
 
 SELECT @ok = COUNT(*) FROM ref_counters WHERE cnt_name = @countername
 IF @@ERROR > 0
 BEGIN
  SELECT @errmsg = 'Failed on query SELECT COUNT(*) FROM ref_counters'
  GOTO ERROR_HANDLER
 END
-- Insert countername entry if necessary
 IF @ok = 0
 BEGIN
  SELECT @strQuery = 'INSERT INTO ref_counters (cnt_name, cnt_lastvalue) 
VALUES (''' +  @countername + ''', 0)'
  EXEC @strQuery
  IF @@ERROR > 0
  BEGIN
   SELECT @errmsg = 'Failed on statement INSERT INTO ref_counters'
   GOTO ERROR_HANDLER
  END
 END
 
-- Getting next counter value
 SELECT @nextvalue = cnt_lastvalue + 1 FROM ref_counters WHERE cnt_name = 
@countername
 IF @@ERROR > 0
 BEGIN
  SELECT @errmsg = 'Failed on query SELECT cnt_lastvalue+1 FROM 
ref_counters'
  GOTO ERROR_HANDLER
 END
-- Update last value in counters table
 UPDATE ref_counters SET cnt_lastvalue = @nextvalue WHERE cnt_name = 
@countername
 IF @@ERROR > 0
 BEGIN
  SELECT @errmsg = 'Failed on statement UPDATE ref_counters'
  GOTO ERROR_HANDLER
 END
-- Return counter
 SELECT @countervalue = @nextvalue
 
 SELECT @countervalue
 
-- Commit transaction
 COMMIT TRAN GET_COUNTER
 
 SET NOCOUNT OFF
 RETURN 0
 
ERROR_HANDLER:
 ROLLBACK TRAN GET_COUNTER
 SET NOCOUNT OFF
 RAISERROR @@ERROR
 PRINT @errmsg
 RETURN @@ERROR
 
------------------------------------------