470,815 Members | 1,471 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,815 developers. It's quick & easy.

Stored Procedure "failing"

I'm using a SQL Server 2000 stored procedure similar to the one below
to upload data to a database. This data is collected from the user on
a number of asp pages and stored in session variables (that's the way
I inherited the system...). When the user has captured all the info
and clicks the submit button the stored procedure is called and it
uploads the data to the database.

This normally works perfectly except for a few occassions when the
stored procedure has "failed" while loading the data onto the last
table (TestTable3) - the data in the session variables is not loaded
onto the database and the respective fields on the respective tables
are blank.

The most recent time it happened the user was using IE6.0 on Windows
XP.

Why is the stored procedure failing to complete?

The system uses code similar to the following to pass the information
that the user has captured to the stored procedure:
<!--#include file='dbConnect.inc'-->
<%
sqlstring = "EXEC dbo.InsertData @Test1 = '" & session("Test1") & "',
@Test2 = '" & session("Test2") & "', @Test3 = '" & session("Test3") &
"', " &_
"@Data1 = '" & session("TestData1") & "', @Data2 = '" &
session("TestData2") & "', @Data3 = '" & session("TestData3") & "', "
&_
"@SomeData1 = '" & session("Test1") & "', @SomeData2 = '" &
session("Test2") & "', @SomeData3 = '" & session("Test3") & "', "'"

conn.execute(sqlstring)
conn.close
Set conn = nothing
%>

The stored procedure is similar to:

CREATE PROCEDURE dbo.InsertData

@Test1 NVARCHAR(10),
@Test2 NVARCHAR(10),
@Test3 NVARCHAR(10),

@Data1 NVARCHAR(50),
@Data2 NVARCHAR(50),
@Data3 NVARCHAR(50),

@SomeData1 NVARCHAR(10),
@SomeData2 NVARCHAR(10),
@SomeData3 NVARCHAR(10),

AS
BEGIN

SET NOCOUNT ON
DECLARE @ID INT

INSERT TestTable1
(Field1,Field2,Field3)
VALUES
(@Test1,@Test12,@Test3)

SELECT @ID = SCOPE_IDENTITY()
SELECT ID = @ID

INSERT TestTable2
(ID,FieldAA,FieldBB,FieldCC)
VALUES
(@ID,@Data1,@Data2,@Data3)

INSERT TestTable3
(ID,FieldX1,FieldX2,FieldX3)
VALUES
(@ID,@SomeData1,@SomeData2,@SomeData3)

END
GO
Jul 19 '05 #1
0 1272

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

13 posts views Thread by Larry Menard | last post: by
2 posts views Thread by Raoul Watson | last post: by
reply views Thread by OutdoorGuy | last post: by
reply views Thread by mihailmihai484 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.