Problem trying to execute a very long query string:

Question

Problem trying to execute a very long query string:

Felix Quevedo

Old Hand

Points: 344
More actions
April 27, 2005 at 10:41 pm

#166889

Hi there, i have a problem trying to execute a very long query string:
I have an app that builds a query string dinamically, but when I try to execute it using code like: myConn.Execute strSQL - i get an error like: query string will be truncated.
How can i solve this problem, thanks a lot!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Chris Hedgate One Orange Chip Points: 25041 More actions · Answer 1

Change the network packet size. The default is 4096 bytes, and the max batch string length is 65536 * network packet size. Note that changing this might have performance implications, so if you change it in the server (using sp_configure) you should change it back ASAP. I think it is also possible to change it for the connection in ADO, but I have no idea how.

--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/

Frank Kalis SSC Guru Points: 111183 More actions · Answer 2

You might also find this article interesting: http://www.sommarskog.se/dyn-search.html

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Felix Quevedo Old Hand Points: 344 More actions · Answer 3

Thanks guys!

I tried to fix the error, but:

Detailed code below:

Public Function GetUMConnectionString() ' sample values

GetUMConnectionString = "Provider=SQLOLEDB.1;Data Source=(local);Initial Catalog=myDB;uid=myuser;pwd=mypass;Packet Size=32767"

End Function

.... I use this function to execute a collection of queries - dynamic ones

Public Function SaveChangesPackage(argStruct As Collection, Optional strFormName = "") As Boolean

Dim i As Long

SaveChangesPackage = True

On Error GoTo Error_Handler

objUMConn.BeginTrans

For i = 1 To argStruct.Count

objUMConn.Execute argStruct(i) ' HERE I GOT ERROR

On Error GoTo 0

Exit Function

Error_Handler:

...........

end function

Now, packet size as you can see is big enough, BUT I STILL HAVE THE PROBLEM.

Any clue?, thanks!

Felix.