Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
SQL string 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" |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
SQL string problem
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 "TLowe" wrote in message ... 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" |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
SQL string problem
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 wrote: 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" |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
SQL string problem
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. "Jake Marx" wrote in message ... 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 wrote: 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" |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
SQL string problem
"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'". -- |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
SQL string problem
onedaywhen wrote:
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] |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
SQL string problem
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] TLowe wrote: 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. "Jake Marx" wrote in message ... 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. TLowe wrote: 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" |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
SQL string problem
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 "TLowe" wrote in message ... 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" |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
SQL string problem
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] TLowe wrote: 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
SQL string problem
"Jake Marx" wrote ...
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. -- |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
SQL string problem
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. -- |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
SQL string problem
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 -- Message posted from http://www.ExcelForum.com |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
SQL string problem
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 " wrote in message ... HI i just wanted to say that i have the exact same problem. its so 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 is 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 really like to make this work sqlstring query work (the stored procedure seems like too much work, you have to specify input and all the output 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 and Settings\mfernandes\My Documents\projects\value pack\tryout.udl;" adoConn.Open 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/YYYY :HH24:MI:SS') " sQL = sQL & " AND A.X_START_DATE <= TO_DATE('29/02/2004 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 IN ('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 rownum <100 " Set rs = adoConn.Execute(sQL) --- Message posted from http://www.ExcelForum.com/ |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
SQL string problem
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 -- Message posted from http://www.ExcelForum.com |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
SQL string problem
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 -- Message posted from http://www.ExcelForum.com |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
SQL string problem
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). -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem with VB string character limit. | Excel Worksheet Functions | |||
to search for a string and affect data if it finds the string? | Excel Worksheet Functions | |||
string sorting problem | Excel Programming | |||
Create a formula into a String then assign string to a cell | Excel Programming | |||
Formatting Problem on Double-Zero String | Excel Programming |