Results 1 to 3 of 3
  1. #1
    Join Date
    May 2003
    Location
    New York, NY
    Posts
    225

    Thumbs up Unanswered: RESOLVED - 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:


    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:


    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?

    Thanks
    Last edited by Mark Gambo; 05-14-06 at 23:29.

  2. #2
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    What is the edition of SQL used and its memory settings?
    Is SQL Server shared by other applications?
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  3. #3
    Join Date
    May 2003
    Location
    New York, NY
    Posts
    225

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •