SQL string problem

[TLowe]

Hello all!
I am using an ADO recordset to import data into Excel 2000 from a Win
2003/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? The

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. This statement 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.

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"

 

[Den]

I am just guessing here, but I would start by looking at your "like"
statements. To test the truncation problem replace all your like
statments with hard comparisons, as follows:

and vendor like "Mike" and [description] like "testdescription"

It might be that the parser does not understand your

like '"& x &'" combination.

Dennis

 

[Jake Marx]

Hi TLowe,

You must "escape" any single quotes when sending SQL statements directly to
SQL Server via ADO. By that, I mean that you must add another single quote
immediately after any existing single quotes in your SQL statement.
Typically, I use a simple function to accomplish this:

Public Function gsMakeStringSQLSafe(rsSQL As String) As String
gsMakeStringSQLSafe=Replace$(rsSQL, "'", "''")
End Function

Just pass your SQL statement through this function when opening your
recordset:

rs.Open gsMakeStringSQLSafe(strSQL), cnnStoredProc, _
adUseClient, adLockReadOnly

Hopefully, this will fix your problem.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

 

[TLowe]

Thanks Jake
That is an excellent function to use to clean up data. I was looking for
something like that to clean up some of the data passed to SQL. I dont
think that the source of this problem is the quotes. This is just cutting
off data like their is some kind of limit to the size. I will keep looking
and see if I can find anything that might shed some light on the issue.




Hi TLowe,

You must "escape" any single quotes when sending SQL statements directly to
SQL Server via ADO. By that, I mean that you must add another single quote
immediately after any existing single quotes in your SQL statement.
Typically, I use a simple function to accomplish this:

Public Function gsMakeStringSQLSafe(rsSQL As String) As String
gsMakeStringSQLSafe=Replace$(rsSQL, "'", "''")
End Function

Just pass your SQL statement through this function when opening your
recordset:

rs.Open gsMakeStringSQLSafe(strSQL), cnnStoredProc, _
adUseClient, adLockReadOnly

Hopefully, this will fix your problem.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

Hello all!
I am using an ADO recordset to import data into Excel 2000 from a Win
2003/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? The

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. This
statement 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.

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"

 

[onedaywhen]

"Jake Marx" ...

Public Function gsMakeStringSQLSafe(rsSQL As String) As String
gsMakeStringSQLSafe=Replace$(rsSQL, "'", "''")
End Function

Just pass your SQL statement through this function when opening your
recordset

Your function rendered my SQL statement as:

SELECT RefID
FROM PersonalDetails
WHERE Surname = ''Cruise''

which gives the error, "Incorrect syntax near 'Cruise'".

--

 

[Jake Marx]

Your function rendered my SQL statement as:

SELECT RefID
FROM PersonalDetails
WHERE Surname = ''Cruise''

which gives the error, "Incorrect syntax near 'Cruise'".

Yeah, I was thinking about this in the shower this morning (I know, what a
geek I am) and realized that I had given a really bad answer. <g>

I really use the function to clean up *user interface inputs* that may
contain an apostrophe, not the whole SQL statement itself, which will cause
problems. Sorry for the confusion.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

 

[Jake Marx]

Hi TLowe,

If this section of your code isn't too long, would you mind posting it here?
What you have posted so far looks OK, but you don't show all the steps, so
maybe there is an issue elsewhere in your code. AFAIK, there is no
practical limit on the length of the Source argument to the Open method, so
I don't think you're running into a limitation that would be cutting off
your statement.

As onedaywhen pointed out, my reply to your original post was flat out
wrong. So please disregard it. But it is a good function to use to clean
up particular data items when it's possible they may contain apostrophes. I
don't use it often, however, because I typically use stored procedures
instead of ad-hoc SQL statements. With stored procedures, you don't have to
clean up your parameters before sending them to SQL.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

Thanks Jake
That is an excellent function to use to clean up data. I was looking
for something like that to clean up some of the data passed to SQL.
I dont think that the source of this problem is the quotes. This is
just cutting off data like their is some kind of limit to the size.
I will keep looking and see if I can find anything that might shed
some light on the issue.




Hi TLowe,

You must "escape" any single quotes when sending SQL statements
directly to SQL Server via ADO. By that, I mean that you must add
another single quote immediately after any existing single quotes in
your SQL statement. Typically, I use a simple function to accomplish
this:

Public Function gsMakeStringSQLSafe(rsSQL As String) As String
gsMakeStringSQLSafe=Replace$(rsSQL, "'", "''")
End Function

Just pass your SQL statement through this function when opening your
recordset:

rs.Open gsMakeStringSQLSafe(strSQL), cnnStoredProc, _
adUseClient, adLockReadOnly

Hopefully, this will fix your problem.

Hello all!
I am using an ADO recordset to import data into Excel 2000 from a Win
2003/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? The

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. This
statement 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.

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"

 

[TLowe]

Hello Jake/onedaywhen
Thanks for your help. Here are the things you were asking about. I have
tried the strSQL as a variant and as a string. I get the same result. When
I print the strSQL statement before the actual use the statement is correct
and the statement is complete. I have test data in the database and that
data is returned when the statement is pasted into T-SQL and then run. I
have just run a trace with the ODBC connections so I will look thru that
file and see if there is anything that looks funky. I am using MDAC 2.8.
The DSN works fine. This DSN is used by a couple of different
programs.....Hmmm....could that be an issue. Its used by a .ASP process
along with this Excel. I appreciate the help as I havent done lot of work
with VBA/ADO in Excel

I did know what you meant with the function that you posted. Its great for
data.

Thanks

(Watch for line wrap)
Here is the code snippet for the issue in the original form.

Dim strSQL as String
'Dim strSQL As Variant
Set cnnStoredProc = New ADODB.Connection
Set rs = New ADODB.Recordset
cnnStoredProc.Open "DSN=test1;"

Here is the original code that assigns the statement to the sql variable. I
have also tried it as one long statement and the result is the same.

' strSQL = "SELECT [description],count(t2.Result) as total,t2.Result,
t2.Vendor, t1.[date]"
' strSQL = strSQL & "from Tblquestions t1 inner join
TblResultsQuestion t2 on T1.[id] = T2.QuestionID"
' strSQL = strSQL & "where T1.type = 4 and vendor like '" & x &
"' "
' strSQL = strSQL & "and [description] like '" & y & "' "
' strSQL = strSQL & "group by [description],Vendor, [date],
result"
' strSQL = strSQL & "order by [description],Result desc "

rs.Open (strSQL), cnnStoredProc, adUseClient, adLockReadOnly




Hello all!
I am using an ADO recordset to import data into Excel 2000 from a Win
2003/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? The

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. This statement 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.

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"

 

[Jake Marx]

Hi TLowe,

It's hard to tell what your code actually looks like with the line-wrap and
being commented out. I'm assuming when you print out the strSQL variable to
the debug window, you can actually copy/paste that into Query Analyzer and
execute it with no problems?

The only thing I see that isn't quite right are the Open method arguments
you used:

rs.Open (strSQL), cnnStoredProc, adUseClient, adLockReadOnly

That should be:

rs.Open strSQL, cnnStoredProc, adOpenStatic, adLockReadOnly

The third argument is the type of recordset to open is the cursor *type*,
not the cursor location. But you're lucky because the value for adUseClient
is 3, which is the same as that for adOpenStatic. <g>

If you want to use a client-side cursor, then you need to specify that at
the connection level:

With cnnStoredProc
.ConnectionString = "DSN=test1;"
.CursorLocation = adUseClient
.Open
End With

I think there may be something else going on here that we can't see. If
you'd like me to take a look, you can privately email me the file and I'll
see what I can do. My email is mvp <[at]> longhead <[dot]> com.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

Hello Jake/onedaywhen
Thanks for your help. Here are the things you were asking about. I
have tried the strSQL as a variant and as a string. I get the same
result. When I print the strSQL statement before the actual use the
statement is correct and the statement is complete. I have test data
in the database and that data is returned when the statement is
pasted into T-SQL and then run. I have just run a trace with the
ODBC connections so I will look thru that file and see if there is
anything that looks funky. I am using MDAC 2.8. The DSN works fine.
This DSN is used by a couple of different programs.....Hmmm....could
that be an issue. Its used by a .ASP process along with this Excel.
I appreciate the help as I havent done lot of work with VBA/ADO in
Excel

I did know what you meant with the function that you posted. Its
great for data.

Thanks

(Watch for line wrap)
Here is the code snippet for the issue in the original form.

Dim strSQL as String
'Dim strSQL As Variant
Set cnnStoredProc = New ADODB.Connection
Set rs = New ADODB.Recordset
cnnStoredProc.Open "DSN=test1;"

Here is the original code that assigns the statement to the sql
variable. I have also tried it as one long statement and the result
is the same.

' strSQL = "SELECT [description],count(t2.Result) as total,t2.Result,
t2.Vendor, t1.[date]"
' strSQL = strSQL & "from Tblquestions t1 inner join
TblResultsQuestion t2 on T1.[id] = T2.QuestionID"
' strSQL = strSQL & "where T1.type = 4 and vendor like '"
& x & "' "
' strSQL = strSQL & "and [description] like '" & y & "' "
' strSQL = strSQL & "group by [description],Vendor, [date],
result"
' strSQL = strSQL & "order by [description],Result desc "

rs.Open (strSQL), cnnStoredProc, adUseClient, adLockReadOnly

 

[onedaywhen]

...

I really use the function to clean up *user interface inputs* that may
contain an apostrophe, not the whole SQL statement itself, which will cause
problems.

I guess I knew <g>, just a 'heads up' to anyone listening in.

--

 

[onedaywhen]

Jake Marx wrote ...

It's hard to tell what your code actually looks like with the line-wrap and
being commented out. I'm assuming when you print out the strSQL variable to
the debug window, you can actually copy/paste that into Query Analyzer and
execute it with no problems?

This looks like it could be a problem. When I uncomment and resolve
the wordwrap I get some merged words i.e.

t1.[date]from
T2.QuestionIDwhere

which would cause an error.

--

 

[marioRoberto]

HI i just wanted to say that i have the exact same problem. its s
frustrating i've been on it for 4 days and still no solution.

a way to prove that somehow excel is limiting the sqlstring length i
by putting another shorter sqlstring query. i did that and it worked
so the problem has to be with the length of the variable.

i am now trying to do it through a stored procedure, but i would reall
like to make this work sqlstring query work (the stored procedure seem
like too much work, you have to specify input and all the outpu
parameters, i have a lot, and i cannnot just print out the recordset)

This is my code: (this works with a shorter sql query)

Set adoConn = New ADODB.Connection
adoConn.CursorLocation = adUseClient
adoConn.Mode = adModeRead
adoConn.ConnectionString = "File name=c:\Documents an
Settings\mfernandes\My Documents\projects\value pack\tryout.udl;"
adoConn.Open

'sQL = "SELECT count(*),
sQL = "SELECT B.X_ID_SERVICIO, B.X_ID_RED, B.X_ID_EQUIPO, B.X_SFID,"
sQL = sQL & " C.X_NOMBRE_SF, B.INSTANCE_NAME, D.DESCRIPTION
B.X_CONTRACT_START,"
sQL = sQL & " B.X_CONTRACT_DURATION, A.X_INST_CODE, A.X_START_DATE,"
sQL = sQL & " A.X_INST_DESCRIPTION, A.X_STATUS, B.X_INS2X_CUENTA
E2.X_TIPO_ACCION,"
sQL = sQL & " E2.X_DESCRIPCION, E2.X_TIMESTAMP, F.X_LITERAL
F.X_DESCRIPCION"
sQL = sQL & " FROM SA.TABLE_X_INST_PACKAGE A,"
sQL = sQL & " SA.TABLE_SITE_PART B,"
sQL = sQL & " SA.TABLE_X_F_VENTAS C,"
sQL = sQL & " SA.TABLE_PART_NUM D,"
sQL = sQL & " SA.TABLE_X_HIST_PRODUCTO E,"
sQL = sQL & " SA.TABLE_X_HIST_PRODUCTO E2,"
sQL = sQL & " SA.TABLE_X_DESC_HISTORICO F"
sQL = sQL & " WHERE"
sQL = sQL & " A.X_INST_CODE = 'VPP01' AND"
sQL = sQL & " A.X_START_DATE >= TO_DATE('01/02/2004','DD/MM/YYY
:HH24:MI:SS') "
sQL = sQL & " AND A.X_START_DATE <= TO_DATE('29/02/200
23:59:59','DD/MM/YYYY :HH24:MI:SS') AND"
sQL = sQL & " B.OBJID = A.X_INST_PACK2SITE_PART AND"
sQL = sQL & " C.X_SFID = B.X_SFID AND"
sQL = sQL & " D.PART_NUMBER = B.INSTANCE_NAME AND"
sQL = sQL & " E.X_ID_SERVICIO = B.X_ID_SERVICIO AND"
sQL = sQL & " E.X_TIPO_ACCION I
('0021','0050','0051','0052','1020','6005') AND"
'sQL = sQL & " TRUNC(E.X_TIMESTAMP) >= TRUNC(A.X_START_DATE) - 1 AND"
sQL = sQL & " F.X_CODIGO = E2.X_TIPO_ACCION AND"
sQL = sQL & " E.X_TIPO_ACCION IN ('6005') AND"
sQL = sQL & " E2.X_TIPO_ACCION IN ('0021','0050','0051','0052','1020'
AND"
sQL = sQL & " E.X_ID_SERVICIO=E2.X_ID_SERVICIO AND"
sQL = sQL & " TRUNC(E2.X_TIMESTAMP,'MI') = TRUNC(E.X_TIMESTAMP,'MI')"
'sQL = sQL & " group by"
'sQL = sQL & " B.X_ID_SERVICIO, B.X_ID_RED, B.X_ID_EQUIPO, B.X_SFID
C.X_NOMBRE_SF, B.INSTANCE_NAME, D.DESCRIPTION, B.X_CONTRACT_START,"
'sQL = sQL & " B.X_CONTRACT_DURATION, A.X_INST_CODE, A.X_START_DATE
A.X_INST_DESCRIPTION, A.X_STATUS, B.X_INS2X_CUENTA,"
'sQL = sQL & " E2.X_TIPO_ACCION, E2.X_DESCRIPCION, E2.X_TIMESTAMP
F.X_LITERAL, F.X_DESCRIPCION"
MsgBox (sQL)
sQL2 = " select X_ID_SERVICIO from TABLE_X_HIST_PRODUCTO where rownu
<100 "
Set rs = adoConn.Execute(sQL

 

[Roger Whitehead]

For what it's worth, I use this method of concatenation for huge SQL strings
without problem -except when I type an invalid string......:0(

Just looking through your string below, my ADO provider would fail at:
sQL = sQL & " E.X_TIPO_ACCION IN ('6005') AND"
because using the IN clause expects moe than one value. Of course, this may
not be the same for your provider.


--
Regards
Roger
Shaftesbury (UK)

 

[marioRoberto]

Hi Roger, thanks for the reply.

The IN thing isn't the problem it works fine in a smaller query.

In the code i posted, i do a smaller sql query, slq2, which works fine


Also the MsgBox(sQL) prints out only 3/4 of the whole string whic
means there is some limit to the maximum allowable characters. I wan
to know if there is a way around this, like defining a block string an
making it a bigger size.

I tried the MSDN library but no luck

 

[El Belgio]

Hi,

I'm not an Excel specialist, but I work with SAP BW (which uses Exce
as a front end).
I have a similar issue where I need to fill a string with cel
addresses to launch an SAP defined macro. I also noticed that th
String variable is limited (Variant as well by the way).
Isn't there any data type that can take more than 185 characters i
VB?

Cheers,

El Belgi

 

[Jamie Collins]

El Belgio wrote ...

I'm not an Excel specialist, but I work with SAP BW (which uses Excel
as a front end).
I have a similar issue where I need to fill a string with cell
addresses to launch an SAP defined macro. I also noticed that the
String variable is limited (Variant as well by the way).
Isn't there any data type that can take more than 185 characters in
VB?

What are we talking about here?

I had a look in my archive of recently executed queries and the
largest is 2573 characters. I've never had a problem with SQL string
length with ADO.

AFAIK, the SQL property is limited in 'QueryTables' (i.e. MS Query)
VBA code to 255 characters and this limit is circumvented by using an
array where each element is within the 255 limit.

If you are talking about the VBA6 native String type, the limit is
2^31 characters (approx 2 billion).

--