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)
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment