Saturday, February 25, 2012

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)

No comments:

Post a Comment