Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2004
    Posts
    15

    Cool Unanswered: Invalid procedure call when appending large amounts of data

    I have 2 queries pulling information from 2 tables. I then have a third query combinging the information and appending matched data to a third table. This procedure works fine for small numbers of records but bombs with the Invalid Procedure Call error when one of the first two table has the normal amount of records in it (18,000). Any ideas would be greatly appreciated.

    Thanks,

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Are you using any user written functions in your query
    or are you using any VB supplied functions in your query

    'invalid procedure call' could refer to either a mising reference - unlikely as you are getting some results from a small amount of data
    OR
    you are passing or retuning an incorrect value to a function. I'd guess that you have some rogue data which your query or functions arer not handling correctly

    HTH

  3. #3
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    Error

    I agree with Healdem that it's probably something to do with the data. You may want to check things like your date fields (does any of them have 12/05 or some other bad date value); or possibly one of your other data fields which doesn't have good data in it.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  4. #4
    Join Date
    Jun 2004
    Posts
    15

    This is my query built upon two others

    INSERT INTO TempHoldforEOM ( Field8, Field11, Field4, Field5, Field6, Vendor )
    SELECT [StringExtract-Concatenation].Field8, [StringExtract-Concatenation].Field11, [StringExtract-Concatenation].Field4, [StringExtract-Concatenation].Field5, [StringExtract-Concatenation].Field6, [StringExtract-Concatenation].Vendor
    FROM [StringExtract-Concatenation], [StringExtract-ConcatenationB]
    WHERE ((([StringExtract-Concatenation].Expr2)=[Expr4]));

  5. #5
    Join Date
    Jun 2004
    Posts
    15

    Sorry should've named the fields more appropriately

    Field8 = Full Name
    Field11 = Full Address
    Field4 = City
    Field5 = State Abbreviation
    Field6 = Zip Code
    Vendor = 1 or 2

    Expression 2 = Street Number & Zip Code joined from first table
    Expression 4 = Street Number & Zip Code joined from second table
    Last edited by vman92; 12-31-05 at 12:12.

  6. #6
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    441
    If I'm not mistaken, the line
    FROM [StringExtract-Concatenation], [StringExtract-ConcatenationB]
    will create a cartesian product. What happens if you try to join in stead?

    FROM [StringExtract-Concatenation] inner join [StringExtract-ConcatenationB] on [StringExtract-Concatenation].Expr2 = [StringExtract-ConcatenationB].Expr4

    If you have some primary key structures in the tables, perhaps use them, and join on them in stead?
    Roy-Vidar

  7. #7
    Join Date
    Jun 2004
    Posts
    15

    Same - Invalid Procedure Call

    I replaced the FROM portion of my SQL with the one you suggested.
    Also checked the Primary keys.

  8. #8
    Join Date
    Jun 2004
    Posts
    15

    Thumbs up Fixed - Yeah!!!

    Quote Originally Posted by healdem
    Are you using any user written functions in your query
    or are you using any VB supplied functions in your query

    'invalid procedure call' could refer to either a mising reference - unlikely as you are getting some results from a small amount of data
    OR
    you are passing or retuning an incorrect value to a function. I'd guess that you have some rogue data which your query or functions arer not handling correctly

    HTH


    You were right! It was bad data. Cleaned it and the query worked perfectly!

Posting Permissions

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