Not Committing with Internet Connection

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

SQL Server 2005 Forums

Transact-SQL (2005)

Not Committing with Internet Connection

Author

Topic

battler
Starting Member

1 Post

Posted - 2012-01-04 : 23:14:04

I have a problem where a Stored Procedure works fine while in Query Analyser however when executed through a web server it appears fine but no records are being committed to the database. I have done this many times before successfully so I am not sure why now nothing is happening. I receive no error messages, the only possible difference this time is I am using a SELECT INTO statement within the procedure.

Any suggestions would be appreciated, thanks in advance.

The SP is included below:

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO

Alter PROCEDURE [dbo].[sp_courseAttendance]
@lessonID smallInt,
@csvAttendance varchar(4000)
AS
SET NOCOUNT Off

IF OBJECT_ID ('tOldAttendance', 'U') IS NOT NULL
DROP TABLE tOldAttendance;

IF OBJECT_ID ('tNewAttendance', 'U') IS NOT NULL
DROP TABLE tNewAttendance;

DECLARE @memberID int

--get old enrolment values into temp table
SELECT memberID INTO tOldAttendance FROM tLessonAttendance WHERE lessonID = @lessonID
SELECT value INTO tNewAttendance FROM [dbo].[fn_Split](@csvAttendance, ',') --value = memberID

DECLARE attend_curs CURSOR FOR
SELECT value FROM tNewAttendance -- value = memberID

OPEN attend_curs
FETCH NEXT FROM attend_curs INTO @memberID

WHILE @@FETCH_STATUS = 0
BEGIN
IF @memberID > 0
BEGIN
SELECT memberID FROM tOldAttendance WHERE memberID = @memberID

IF @@rowCount = 0 --NO PREVIOUS RECORD
INSERT INTO tLessonAttendance ([LessonID], [MemberID]) VALUES(@lessonID, @memberID)
END
FETCH NEXT FROM attend_curs INTO @memberID
END
CLOSE attend_curs
DEALLOCATE attend_curs

--finally remove any attendance records that should not be there
DELETE FROM tLessonAttendance WHERE LessonID = @lessonID AND MemberID =(
SELECT memberID from tOldAttendance AS O WHERE NOT EXISTS ( SELECT value from tNewAttendance AS N WHERE O.memberID = n.value )
)

GO
IF OBJECT_ID ('tOldAttendance', 'U') IS NOT NULL
DROP TABLE tOldAttendance;
GO
IF OBJECT_ID ('tNewAttendance', 'U') IS NOT NULL
DROP TABLE tNewAttendance;
GO

SET NOCOUNT ON

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-01-05 : 00:19:47

Have you run a trace to confirm that the stored procedure call is making it to the database server?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources