Can't find the information you are looking for here? Then leave a message over on our WinBatch Tech Support Forum.
retcode = qExecDirect(hstmt, qry2) If (retcode != @qSuccess) && (retcode != @qSuccessInfo) err=qerror(hstmt,2) Message("qExecDirect failed", StrCat(retcode,@CR,err)) Exit Endif
And qry2 is built earlier, and is:
select top 1 L.RecNum, L.TStamp, Round(ABS(L.Reading)+S.Reading+B.Reading, 6) as "GC Total" from REA_Lake L join REA_Skito S on L.RecNum = S.RecNum join REA_Butte B on L.RecNum = B.RecNum where L.TStamp like "200103%" order by "GC Total" desc
The query fails, and err is: S0022 [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name '200103%'.
In troubleshooting this, I've been pulling up a AskTextBox so I can proof my syntax for this query. I can copy this text and paste it into Query Analyzer and it works fine. It's just in this context that it fails. I can also run a different query in place of this one and it works, so I think the environment is OK too.
I suspect it has something to do with the % in it. When I build this string, I use %% so I don't get the substitution error, and it looks fine in the AskTextBox. I'm pretty confused now about how to figure out how to fix this. Any suggestions?.
Here is my actual code:
rndabs='Round(ABS(L.Reading)+S.Reading+B.Reading, 6)' joins='REA_Lake L join REA_Skito S on L.RecNum = S.RecNum join REA_Butte B on L.RecNum = B.RecNum ' qry2=StrCat('select top 1 L.RecNum, L.TStamp, ',rndabs,' as "GC Total" from ',joins,'where L.TStamp like "200103%%','" order by "GC Total" desc') ;I also added this line to proof it, and to be able to cut and paste from Query ;Analyzer, making sure it was working code (as far as SQL server is ;concerned). qry2=AskTextBox('qry2','is this right?',qry2,0,0)
I noticed you are using the LIKE operator in your WHERE statement. Please note:
LIKE is a very powerful operator that allows you to select only rows that are "like" what you specify. The percent sign "%" can be used as a wild card to match any possible character that might appear before or after the characters specified. For example:select first, last, city from empinfo where first LIKE 'Er%';This SQL statement will match any first names that start with 'Er'.
Strings must be in single quotes.
Or you can specify,select first, last from empinfo where last LIKE '%s';
This statement will match any last names that end in a 's'.
As you can see from the LIKE documentation above, strings must be in single quotes. You will need to modify your script to use single quotes instead of double quotes. In otherwords, you will need to use double quotes to define the WIL strings, and single quotes in the SQL string.
rndabs="Round(ABS(L.Reading)+S.Reading+B.Reading, 6)" joins="REA_Lake L join REA_Skito S on L.RecNum = S.RecNum join REA_Butte B on L.RecNum = B.RecNum" qry2=StrCat("select top 1 L.RecNum, L.TStamp, ",rndabs," as 'GC Total' from ",joins," where L.TStamp like '200103%%'"," order by 'GC Total' desc") qry2=AskTextBox('qry2','is this right?',qry2,0,0)
Article ID: W14541
Filename: s0022 error from SQL Server Driver.txt
File Created: 2017:07:28:12:58:33
Last Updated: 2001:03:06:13:05:10