ADO SQL query in VB6 (sp6) too long! Truncated while executed.

Posted on 2005-04-20
Medium Priority
Last Modified: 2008-06-18
Hi experts!
I am in a middle of a project where I access through ADO to a MS Access database (later after testing phase to MS SQL server).
Untill now everything was just fine, but now I experienced problems in on of my procedures, where I exectute quite a large, complex SELECT command which returns then a simple recordset with just two fields. The query has heaps of joins and tables connected.

The query is not over 64 k long which I thought is the limit. It is some 700 - 1000 chars long.

Now when I execute it, I get nothing in the recordset as the query somehow is truncated within this procedure. (when I debug the mySQL which is the argument for the execute it is not complete!!!!)

I tried to delete some WHERE conditions, shortened the query a bit and it worked again. But I need all those conditions.

I do not want to go to the database designer and rename all tables and fields so that they have just 2-3 characters as it will loose its logic for later programmer. Is there some other way how to get rid of this misbehaviour or am I missing some important declaration issue?

Or is it just MS access issue? Shall I switch directly to MS SQL? I am missing the wysiwyg query analyser there... :o)

Please help!

Thanks Petr
Question by:Pedro Keson
LVL 10

Accepted Solution

PSSUser earned 280 total points
ID: 13826761
One thing you could do is to put the SQL into a query (Stored Procedure/View) in the database, or combine several of your joined tables in views and then make use of the views.

Author Comment

by:Pedro Keson
ID: 13826880
Hi PSSUser!
So do I read you well that IT IS A STANDART BEHAVIOUR?
I am now thinking about renaming all the tables and fields to something short to test it.
The thing with stored procedures (views) is that I need to set a number of paramaters and the way of passing those parameters to the stored procedure is somehow odd... Anyway if there is some REAL sollution I will appreciate it!

LVL 12

Assisted Solution

by:Bob Lamberson
Bob Lamberson earned 120 total points
ID: 13831040
not sure if this directly addresses your problem but if you are moving to sql anyway, why not start with MSDE? It's free and this would allow you to code exactly as you will need to for sql and allow you to create views and stored procedures where you need to.
You can still create queries in Access and paste them into sql with little changes. Also long select commands like you describe can be done in several parts of a stored procedure and combined as a batch file.

check it out at    http://www.microsoft.com/sql/msde/downloads/download.asp

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

LVL 10

Expert Comment

ID: 13831815
I don't know about a standard behaviour, I've never tried to use very long query strings. I prefer to use stored procedures, since the are optimised and also can make debugging problems easier - it helps if you can run the exact query outside of the software. If you need help with using ADO Command objects to run stored procedures, let me know.

By your comment about using sored procedures being "somehow odd" do you mean the problem a lot of people have with parameters having to be passed in order? If so the trick is to set the NamedParameters of your command object to true. This allows you to pass them by name instead.(provided you are using ADO 2.6 or later)

Assisted Solution

cjard earned 120 total points
ID: 13832200
how do you know your sql is being truncated? i've run query strings with vb/access that are over 2kb and no problems.. can you post the code that you use to:

build the sql
print it when you are debugging.. i.e. how you know it is truncated?

Author Comment

by:Pedro Keson
ID: 13832919

thanks for all your posts. I have found out, that there is a certain 255 chars limit for ADODB.Recordset source. Don't ask me why. I talked to our VB developers and they told me that it somehow corresponds with command line limit of 255 characters.

So I did use stored procedures (or views) and now the thing works. But I HAD TO MAKE MY TABLES and FIELDS names shorter anyway as I wanted to pass some 8-10 parameters to the stored procedure and they are heading to 200 characters with all necesary formating etc.

Anyway, I know now, that I have to transmit as little code as possible and I will work further.

Thank you all. P.S. CJARD: I see the truncated sql query in debuger where it shows only 255 chars. And also the query results do come with errors as the query was truncated just in the middle of the name or so.
LVL 10

Expert Comment

ID: 13833332

you could change the way you are calling your stored procedure.

If you make use of a command object you can add parameters that you set the values to, instead of putting the full call to the stored procedure in an SQL string.

For Example set up the command object:
With AdoCmd
  .ActiveConnection = AdoCn
  .CommandType = adCmdStoredProc
  .CommandText = "[Stored Proc Name]"
  .NamedParameters = True 'Make sure you don't have pass the parameters in order

  .Parameters.Append .CreateParameter("[@Param1]", adVarChar, adParamInput, 30)'VarChar length=30
  .Parameters.Append .CreateParameter("[@Param2]", adInteger, adParamInput)
End With

Then open the recordset using:
AdoCmd.Parameters("[@Param1]").Value="Some String"

Set AdoRs=New ADODB.RecordSet
AdoRs.Open AdoCmd, ,adOpenForwardOnly,adLockOptimistic

This way you won't have an issue with the SQL string length, so won't need to rename your tables/columns

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question