Need advice - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Need advice

Author

Topic

vfactor
Starting Member

11 Posts

Posted - 2006-07-18 : 18:32:46

Hi all,

My first request for help here even if I read this site quite often and got tons of usueful information. Thanx all

I have an application (VB 6) calling store procedure on a SQLServer 2000 DB. One of the table gives me headache. I wrote a simple store procedure to insert a record into that table. If I call the store procedure from query manager it works perfect but if I call it from VB it looks like to work (return from the execute fine) but then I query the table for that record and it'll just take time and then return time out. I have to stop the VB application and then query it again then it'll return no record.

I suspect the table being locked somehow but I check inside the VB app code and that's the only place the table is called. Further more I have hundred of store procedures being used that way and they're all ok. I indexed the table , no use either...

I am just short of ideas how to debug this...I'll need your advice :)

thank you

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-07-18 : 18:35:13

Could you post the stored procedure code plus the VB code associated with setting up the command and executing it?

Tara Kizer

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-07-18 : 18:38:07

Have you started a transaction but never completed it?

Peter Larsson
Helsingborg, Sweden

vfactor
Starting Member

11 Posts

Posted - 2006-07-18 : 18:41:21

wa that was fast :)

allright here it is. I commented part of the sp for debug purpose...

VB code

Private Function InsererPF(ByVal IdFenSoum As Long, ByVal refItem As Long, ByVal pNoCommande As Long, ByVal pNoFenetre As Integer, ByVal pTypePiece As Integer) As Boolean
Dim sql As String
Dim objcmd As New ADODB.command
Dim resultat As Boolean: resultat = False
On Error GoTo GestionErreur

'**********************************************************************************
'Configurer la commande
With objcmd
.Name = "sp_INSERT_PIECE_FENETRE"
.CommandText = "sp_INSERT_PIECE_FENETRE"
.CommandType = adCmdStoredProc
.ActiveConnection = BDPOLYCO

'Ajouter les paramètre
.Parameters.append _
objcmd.CreateParameter("RC", adInteger, adParamReturnValue)
.Parameters.append _
objcmd.CreateParameter("noIDFenSoumission", adInteger, adParamInput, 4, IdFenSoum)
.Parameters.append _
objcmd.CreateParameter("refItem", adInteger, adParamInput, 4, refItem)
.Parameters.append _
objcmd.CreateParameter("noCommande", adInteger, adParamInput, 4, pNoCommande)
.Parameters.append _
objcmd.CreateParameter("noFenetre", adInteger, adParamInput, 4, pNoFenetre)
.Parameters.append _
objcmd.CreateParameter("typePiece", adInteger, adParamInput, 4, pTypePiece)
End With
'*******************************************************************************

objcmd.Execute

If (objcmd.Parameters("RC")) Then
resultat = True
Else
resultat = False
End If

InsererPF = resultat

Set objcmd = Nothing

Exit Function
GestionErreur:
InsererPF = False
End Function

Store procedure

CREATE PROCEDURE dbo.sp_INSERT_PIECE_FENETRE
@idFenSoumission int,
@refItem int,
@noCommande int,
@noFenetre int,
@typePiece int --0 = barrotin, 1 = autre pièces 2 = carrelage amovible
AS

SET NOCOUNT ON
BEGIN
DECLARE @tabletemp TABLE(noCommande INT, noFenetre INT, refNoIDPiece INT, refItem INT)

----------------------------------------------------------------
-- Les autres pièces
----------------------------------------------------------------
IF(@typePiece = 1)
BEGIN
INSERT INTO @tabletemp
SELECT @noCommande AS noCommande , @noFenetre AS noFenetre, tblOptionPiece.refNoIDPiece, @refItem as refItem

FROM tblOptionPiece RIGHT OUTER JOIN
tblOptionsFenetreSoumission ON tblOptionPiece.refNoIDOptionSerieModele = tblOptionsFenetreSoumission.refNoIdOptionSerieModele

WHERE ( tblOptionsFenetreSoumission.refNoIDFenetreSoumission = @idFenSoumission) AND (tblOptionPiece.refNoIDTypePiece = 1)

IF @@ROWCOUNT > 0 GOTO INSERT_HERE
END

----------------------------------------------------------------
-- Carrelage amovible
----------------------------------------------------------------
/*IF(@typePiece = 2)
BEGIN

INSERT INTO @tabletemp
SELECT @noCommande AS noCommande, @noFenetre AS noFenetre, CarrelageAmovible.No_Piece, @refItem as refItem

FROM tblCarrelageFenetreSoumission INNER JOIN
CarrelageAmovible ON tblCarrelageFenetreSoumission.refNo_carrelage = CarrelageAmovible.No_Carrelage

WHERE (tblCarrelageFenetreSoumission.refNoIDFenetreSoumission = @idFenSoumission)

IF @@ROWCOUNT > 0 GOTO INSERT_HERE

END

----------------------------------------------------------------
-- Les poteaux pour Bay windows
----------------------------------------------------------------
IF(@typePiece = 3)
BEGIN

INSERT INTO @tabletemp
SELECT @noCommande AS noCommande, @noFenetre AS noFenetre, CAST(Valeur AS INT), @refItem as refItem

FROM tblPropriete RIGHT OUTER JOIN
tblFenetreSoumissionPropriete ON tblPropriete.NoIDPropriete = tblFenetreSoumissionPropriete.refNoIDPropriete

WHERE (tblFenetreSoumissionPropriete.refNoIDFenetreSoumission = @idFenSoumission) AND (tblPropriete.ClePropriete = 'key_Poteau')

IF @@ROWCOUNT > 0 GOTO INSERT_HERE
END*/

END

RETURN 1

INSERT_HERE:
BEGIN TRAN
----------------------------------------------------------------
-- Insérer dans PIECE_FENETRE
----------------------------------------------------------------
INSERT INTO PIECE_FENETRE(No_Commande, No_fenetre, no_piece, refItem)
SELECT * FROM @tabletemp

IF @@ERROR <> 0 GOTO PROBLEM

COMMIT TRAN

RETURN 1

PROBLEM:
BEGIN
ROLLBACK TRAN
PRINT 'Erreur sp_INSERT_PIECE_FENETRE refItem : ' + CAST(@refItem AS varchar)
END

RETURN 0
GO

vfactor
Starting Member

11 Posts

Posted - 2006-07-18 : 18:42:42

I even separated the insert part inside the sp into a INSERT_HERE label to make sure that I only BEGIN TRAN AND COMMIT TRAN there so no table lock except when there're something to insert...

vfactor
Starting Member

11 Posts

Posted - 2006-07-18 : 21:42:51

I found the problem finally...

There's a begin tran without commit ( a return when a test is false ) in one of a sp way before the one I am having a problem. Even if it's not directly related to the table in use.

Thank you all

by the way, anyone knows an easy way to debug a sp ? any commercial tool available ?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-07-19 : 12:43:15

quote:
Originally posted by vfactor

by the way, anyone knows an easy way to debug a sp ? any commercial tool available ?

Query Analyzer and Visual Studio do this for free.

Tara Kizer

vfactor
Starting Member

11 Posts

Posted - 2006-07-19 : 18:20:24

yes I am using query analyser to debug myself

It gives the line where the errors occurs. It just sometimes the sp is too long and counting the line is a boring job..:)

It'll be nice if there's some tool where you debug it line by line and see the results as in code debuggin'

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-07-19 : 18:23:55

quote:
Originally posted by vfactor

It'll be nice if there's some tool where you debug it line by line and see the results as in code debuggin'

Query Analyzer has this!

Tara Kizer

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-07-19 : 20:27:30

quote:
Originally posted by vfactor

I found the problem finally...

There's a begin tran without commit ( a return when a test is false ) in one of a sp way before the one I am having a problem. Even if it's not directly related to the table in use.

Thank you all

by the way, anyone knows an easy way to debug a sp ? any commercial tool available ?

Any time your stored procedure starts a transaction, it should check the count of @@trancount first. If it is anything other than what you are expecting (usually 0), than is an error condition, and you should have the error handler in your stored procedure handle it (most likely rollback, and return an error message).

I make it a standard with our stored procedures to check for open transactions at the beginning of the procedure, and rollback with an error message if there is an open transaction, unless the procedure is specifically designed to be called inside a transaction.

If you don't do this, you will have hard to debug blocks, and very unhappy users.

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources