WinBatch Tech Support Home

Database Search

If you can't find the information using the categories below, post a question over in our WinBatch Tech Support Forum.

TechHome

ODBC
plus

Can't find the information you are looking for here? Then leave a message over on our WinBatch Tech Support Forum.

s0022 error from SQL Server Driver

 Keywords:  

Question:

I have a SQL query that works fine in the SQL Query Analyzer, but will not in my script. I can't figure out what the problem is.

I use:

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)

Answer:

Your correct, in that, you should use two percent signs '%%' to represent one percent sign '%' in the SQL string.

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.

Example:

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