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

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
Pedro KesonIT specialistAsked:
Who is Participating?
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.
Pedro KesonIT specialistAuthor Commented:
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!

Bob LambersonSoftware EngineerCommented:
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

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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)
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?
Pedro KesonIT specialistAuthor Commented:

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.

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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.