MSSQL SPROC and VB6

I wrote the following SPROC and it works the first time i run it. But if I attempt to run it again I get the following T-SQL Error: "There is not enough memory to complete the task. Close down some operations and try again". Then the app closes. Any ideas?

Here is my complete code:

USE IADATA
IF EXISTS (select * from syscomments where id = object_id ('TestSP'))
DROP PROCEDURE TestSP

GO
CREATE PROCEDURE TestSP
/*Declare Variables*/
@.ListStr varchar(100) /*Hold Delimited String*/
AS
Set NoCount On
DECLARE @.ListTbl Table (InvUnit varchar(50)) /*Creates Temp Table*/
DECLARE @.CP int /*Len of String */
DECLARE @.SV varchar(50) /*Holds Result */

While @.ListStr<>''
Begin
Set @.CP=CharIndex(',',@.ListStr) /*Sets length of words - Instr */
If @.CP<>0
Begin
Set @.SV=Cast(Left(@.ListStr,@.CP-1) as varchar) /*Copies Portion of String*/
Set @.ListStr=Right(@.ListStr,Len(@.ListStr)-@.CP) /*Sets up next portion of string*/
End
Else
Begin
Set @.SV=Cast(@.ListStr as varchar)
Set @.ListStr=''
End
Insert into @.ListTbl Values (@.SV) /*Inserts variable into Temp Table*/
End

Select InvUnit From @.ListTbl LT
INNER Join dbo.Incidents ST on ST.Inv_Unit=LT.InvUnit

and my VB6 Code:

Dim adoConn As ADODB.Connection
Dim adoCmd As ADODB.Command
Dim adoRS As ADODB.Recordset
Dim strLegend As String
Dim strData As String

Set adoConn = New ADODB.Connection
adoConn.Open connString

Set adoRS = New ADODB.Recordset
Set adoCmd = New ADODB.Command

With adoCmd
Set .ActiveConnection = adoConn
.CommandText = "TestSP"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("ListStr", adVarChar, adParamInput, 100)
.Parameters("ListStr").Value = "Unit 41,Unit 32,Unit 34,Unit 54"

Set adoRS = .Execute

Do While Not adoRS.EOF
Debug.Print adoRS.Fields(0).Value
adoRS.MoveNext
Loop

End With

Set adoCmd = Nothing
adoRS.Close
Set adoRS = Nothing
Set adoCmd = Nothing
adoConn.Close
Set adoConn = Nothing

End Sub

Any ideas?

ThanksWhat is the edition of SQL used and its memory settings?
Is SQL Server shared by other applications?|||Thanks for the reply my problem was resolved (http://vbforums.com/showthread.php?t=405134)

MS-SQL Server help me

hai, we r developing a application in VB6.0 with MS SQL Server as
backend. we are facing problem of SQL Server getting hanged after
running continously for more than 4 hrs. our application will be posting
and updating data continously to database. also we have client machines
were we can view these data(these are seperate machines).
we have SQL Server and our application running in seperate machines. SQL
Server is running in a server class machine PIII processor, 256 MB ram
and our application running on a PIV processor, 256 MB ram.
please help me to resolve the problem.
regards
Suriya.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Look lik you are stucking into a deadlock situation. If so, try to check the
activity in SQL Server EM for Deadlocking. If Deadlocks occur , try to use
another locking method in your programs and scripts, that´ll help.
Jens Süßmeyer.
"Suriya Narayanan Vadivel Murugan" <suriyasj@.rediffmail.com> schrieb im
Newsbeitrag news:ex5Z2VgZDHA.4020@.tk2msftngp13.phx.gbl...
> hai, we r developing a application in VB6.0 with MS SQL Server as
> backend. we are facing problem of SQL Server getting hanged after
> running continously for more than 4 hrs. our application will be posting
> and updating data continously to database. also we have client machines
> were we can view these data(these are seperate machines).
> we have SQL Server and our application running in seperate machines. SQL
> Server is running in a server class machine PIII processor, 256 MB ram
> and our application running on a PIV processor, 256 MB ram.
> please help me to resolve the problem.
> regards
> Suriya.
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!