Re: SQL statement is truncated.

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: Val Mazur (group51a_at_hotmail.com)
Date: 05/13/04 

Date: Wed, 12 May 2004 23:10:03 -0400

Hi,

185 characters is definitely not a limit for the SQL string. I think there
is something wrong when application assigns SQL statement to the variable.
Could you please post actual strSQL variable declaration and the code, which
assign SQL statement to the variable? Also try to put breakpoint right
before you open recordset and print content of the strSQL variable in a
debug window to see if you have proper statement. 

-- 
Val Mazur
Microsoft MVP
"TLowe" <noelbisspam@nomindspringspam.com> wrote in message 
news:%23K5nadEOEHA.1312@TK2MSFTNGP12.phx.gbl...
> Hello all!
> I am sorry but I had posted a similiar question a couple of weeks ago but 
> I
> wasnt able to view in my viewer.  I will try again. I have posted this in
> the Excel programming newsgroup in case someone has experienced this 
> problem
> with Excel.
>
> I am using an ADO recordset to import data into Excel 2000 from a
> Win2003/SQL Server2000 setup.  My problem is the SQL string is getting
> truncated and is not passed to SQL server 2000 in a complete form.  Its 
> cut
> off.  It appears to cutoff around 185 characters.  Is this a setting that
> can be reset?  Is this an ADO Property that should be set to allow a 
> bigger
> SQL statement to be passed to SQL server.  Its really weird that it only
> takes so many characters, truncates the balance of the statement and then
> still tacks the double quotes at the end of the statement?
>
> Here is the ADO recordset :
> rs.Open strSQL, cnnStoredProc, adUseClient, adLockReadOnly
>
> Here is the full SQL statement that is assigned to the strSQL variable 
> that
> is initially stored in the variable strSQL.  As a statement this runs in 
> the
> SQL analyzer with no problems (variables x, and y with actual values).
>
> SELECT [description],count(t2.Result) as total,t2.Result,t2.Vendor,
> t1.[date]from Tblquestions t1 inner join TblResultsQuestion t2 on T1.[id] 
> =
> T2.QuestionID where T1.type = 4 and vendor like '" & x & "' and
> [description] like '" & y & "' group by [description],Vendor, [date], 
> result
> order by [description],Result desc
>
> Here is the actual statement that gets put into the strSQL variable when
> passed via the above rs.open statement.  The error message is "Runtime 
> error
> 3021".   This is because the recordset is empty.
>
> strSQL : "SELECT [description],count(t2.Result) as total,t2.Result,
> t2.Vendor, t1.[date] from Tblquestions t1 inner join TblResultsQuestion t2
> on T1.[id] = T2.QuestionID where t1.type = 4 and ve"
>
>
> Thanks
> I really appreciate any help or insight you can provide.
>
>
>
>
>

Relevant Pages

  • Re: Using part of a field
    ... See the article "Finding and replacing characters using wildcards" at: ... AS400 SQL may have a different way of doing ... called i.dsn, containing the followiing text: ... sort of thing in a query. ...
    (microsoft.public.word.mailmerge.fields)
  • RE: Collation settings for ASCII code page
    ... The string sort and comparison behavior for non-Unicode data ... characters you can store without using Unicode. ... There is no such thing as an "ASCII code page" in SQL Server. ...
    (microsoft.public.sqlserver.dts)
  • Re: sendStringParameterAsUnicode: How to insert unicode data corre
    ... We are using SAP XI and connecting it to SQL Server 2005 using JDBC. ... The destination columns are nvarchar and store unicode data correctly. ... is unable to display the characters you have. ... Configuration: MS SQL server 2005 SP2, and MS jdbc driver version: 1.1 ...
    (microsoft.public.sqlserver.jdbcdriver)
  • Re: simple (?) question about having ";" in the password
    ... whatever characters they damn well please. ... Attempting to escape SQL strings is a non-starter. ... themselves "do I need to use a prepared statement here ?". ... but it could happen) since its going to be added to the SQL string. ...
    (comp.lang.java.programmer)
  • Re: Problem with sql statement in VB
    ... Of course you could use, but you never know what are special characters, ... >I went and looked around at the various postings regarding SQL Injection ... because it could lead to SQL injection attack from ... to avoid any issues with the dates format or number ...
    (microsoft.public.vb.database.ado)