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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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)
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)
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Guys,
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.
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.
keson,
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
.Parameters.Append .CreateParameter("[@Param2
End With
Then open the recordset using:
AdoCmd.Parameters("[@Param
AdoCmd.Parameters("[@Param
Set AdoRs=New ADODB.RecordSet
AdoRs.Open AdoCmd, ,adOpenForwardOnly,adLockO
This way you won't have an issue with the SQL string length, so won't need to rename your tables/columns
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
.Parameters.Append .CreateParameter("[@Param2
End With
Then open the recordset using:
AdoCmd.Parameters("[@Param
AdoCmd.Parameters("[@Param
Set AdoRs=New ADODB.RecordSet
AdoRs.Open AdoCmd, ,adOpenForwardOnly,adLockO
This way you won't have an issue with the SQL string length, so won't need to rename your tables/columns
ASKER
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!
Thanks.
:o)