TestStand Open SQL Statement does not support SQL's ORDER BY clause???

[Bob Rafuse]

TestStand 1.0.3
Windows 2000 SP1
SQL Server 2000 Personal

You've got to be kidding me...

It appears that the built-in TestStand Open SQL Step does NOT support the
"ORDER BY" clause in the SELECT statement, even though the documentation
says it does. Is this true?

I have an Open SQL Statement query:

"SELECT * FROM [MyTable] WHERE ([Batch ID]=1234)"

it works fine, returning a correct record count 120 records. If I change
the Open SQL Statement query simply by adding an ORDER BY clause, such as:

"SELECT * FROM [MyTable] WHERE ([Batch ID]=1234) ORDER BY [MyField] ASC"

it returns a record count of zero. I know that "MyField" exists in the
MyTable table and contains valid data. The
second query works fine in SQL
Server Enterprise Manager.

Am I missing something? Is it true that the TestStand Open SQL Step does
NOT support the "ORDER BY" clause? If not, what &#$!ing good is it and why
does the manual state it is supported? Is there any other way using just
the TestStand steps to order a database recordset on one or more fields?

Any help would be appreciated.

Grrrrr....

Bob Rafuse
Etec Inc.

[Scott_Richardson]

Bob -
The database step types do not do anything special to the SQL command that you give it. The step just passes the command to the ADO provider. I tried a simple query using the step types with the following command,

"SELECT UUT_RESULT.* FROM UUT_RESULT WHERE ([UUT_SERIAL_NUMBER] = 12345) ORDER BY [EXECUTION_TIME] ASC"

and this return the expected results and the record count parameter was as expected. I tried this on TS 1.0.2 and TS 2.0 with MS Access 2000 and MS SQL Server 7.0. I do not have MS SQL Server 2000 at this time.

It would be surprised if the step types are messing something up.

Questions:
1. Have you verified whether the data is actually returned even though the record count is zero?

2. Are you using any advanced options on the Opend SQL
Statement step type, specifically the cursor type set to forward only? Forward only cursors do not allow for record counts.

Scott Richardson
National Instruments
scott.richardson@ni.com
http://www.ni.com/teststand/

[Bob Rafuse]

> Bob -
> The database step types do not do anything special to the SQL command
> that you give it. The step just passes the command to the ADO
> provider. I tried a simple query using the step types with the
> following command,
>
> "SELECT UUT_RESULT.* FROM UUT_RESULT WHERE ([UUT_SERIAL_NUMBER] =
> 12345) ORDER BY [EXECUTION_TIME] ASC"
>
> and this return the expected results and the record count parameter
> was as expected. I tried this on TS 1.0.2 and TS 2.0 with MS Access
> 2000 and MS SQL Server 7.0. I do not have MS SQL Server 2000 at this
> time.
>
> It would be surprised if the step types are messing something up.

I've been doing some experimenting over the past couple of days. Simple,
one-table queries seem to handle the ORDER BY clause fine. Th
ings seem to
get messed up when I try multi-table queries with ORDER BY clause with the
TestStand database steps. I get no errors but the returned record counts
are always 0 with the ORDER BY and positive without the ORDER BY. The exact
same queries work fine in Visual Basic/ADO and the SQL Server Query
Analyzer.

> Questions:
> 1. Have you verified whether the data is actually returned even though
> the record count is zero?

Hmmm... yes data IS getting returned (at least on the two instances I just
checked), but the record count is always zero. I was not proceeding with
processing if the record count was 0.

Still... I don't know how to loop through the recordset without knowing how
many records there are an not eventually generate an error by passing EOF.
Is there another way using the TestStand database steps to determine a) the
number of records in the recordset or b) when I'm at EOF?

> 2. Are you using any advanced options on the Opend SQL Statement step
> type, specifically
the cursor type set to forward only? Forward only
> cursors do not allow for record counts.

Everything on the Advanced tab of the Open SQL Statement step is set to "Use
Default".

Bob.

[Bob Rafuse]

Scott and Roberto,

This may be Microsoft's bug. Sorry for blaming TestStand.

I stumbled across an MSDN KB article that may explain ~some~ of what I'm
seeing.

Q264002: BUG: Compound Filter Condition in ADO2.5 Incorrectly Returns 0
Records

See: http://support.microsoft.com/support/kb/articles/Q264/0/02.ASP

The bug is in MDAC/ADO 2.5. I'm using MDAC/ADO 2.6 (I have to in order to
talk to SQL Server 2000). I see no mention of the bug being fixed in ADO
2.6 so I assume its still there.

This might explain why some of my more complex queries (with JOINS, etc)
return 0 records. The simple one-table query I mentioned in my original
post still fails, why I'm not sure. This MAY be the result of the ADO
library just being in a "confused state" after the m
ore complex queries
which preceed it have already tanked.

Unfortunately, I'm still stuck unless there is an alternate way to either a)
determine the number of records in the recordset or b) determine when I'm at
EOF without generating a runtime error. Are either of these features
available in TestStand 1.0.3?

Bob Rafuse
Etec Inc.

[rpfi]

Hello Bob,

The Manual of TestStand is correct and it does support the "ORDER BY" clause. I didn't have the SQL server installed right now, but I tested using Access 2000 and it worked fine. Also, you didn't mention in your email what type of DB provider you are using. There are two possibilities:

* Microsoft OLE DB Provider for ODBC
* Microsoft OLE DB Provider for SQL Server

In order to know which one you are using, check the Open Database Step and verify if you are using a DSN (Data Source Name), if yes, you are using the Provider for ODBC.

According to the readme file shipped with TestStand, National Instruments recommends you to use the second option: Microsoft OLE DB Provider for SQL Server. Make sure you are using this provider and also that you hav
e the latest drivers available for your database system installed.

Roberto Piacentini
Applications Engineer
www.ni.com/support

[Bob Rafuse]

> The Manual of TestStand is correct and it does support the "ORDER BY"
> clause. I didn't have the SQL server installed right now, but I tested
> using Access 2000 and it worked fine. Also, you didn't mention in your
> email what type of DB provider you are using. There are two
> possibilities:

I've done some experimenting over the past couple of days. I am seeing
curious behavior with multi-table queries and/or multi-table queries with a
JOIN clause simply when an ORDER BY clause is added to the query. The exact
same queries with the "ORDER BY" work fine in Visual Basic/ADO and SQL
Server Query Analyzer.

See my reply to Scott, above. It turns out that (at least some of the
times) data IS getting returned, though the record count is still always 0.

>
> * M
icrosoft OLE DB Provider for ODBC
> * Microsoft OLE DB Provider for SQL Server
>
> In order to know which one you are using, check the Open Database Step
> and verify if you are using a DSN (Data Source Name), if yes, you are
> using the Provider for ODBC.
>
> According to the readme file shipped with TestStand, National
> Instruments recommends you to use the second option: Microsoft OLE DB
> Provider for SQL Server. Make sure you are using this provider and
> also that you have the latest drivers available for your database
> system installed.

Thats the provider I'm using. Here's my connection string (with example
username and password) used in the Open Database step:

"Provider=SQLOLEDB.1;Password=mypassword;Persist Security Info=True;User
ID=myusername;Initial Catalog=ETEC;Data Source=MYSQLSERVER"

Bob Rafuse
Etec Inc.

[rpfi]

Bob,

I tried the ORDER BY using complex data tables, and it worked fine again (with Access 2000).

I have another suggestion for you. Try to change the cursor type in the advanced tab. Try using instead of default, try the dynamic, static and keyset. Also try varying the location from default to Client or Server. Does it change the results? In my case I got record count 0 only when I used the Forward only cursor type (and this is the expected behavior for this cursor type).

As Scott said, TestStand just passes commands to the database using ADO.

If it still doesn't work, let me know.

Roberto Piacentini
Applications Engineer
www.ni.com/support

[Bob Rafuse]

> I tried the ORDER BY using complex data tables, and it worked fine
> again (with Access 2000).
>
> I have another suggestion for you. Try to change the cursor type in
> the advanced tab. Try using instead of default, try the dynamic,
> static and keyset. Also try varying the location from default to
> Client or Server. Does it change the results? In my case I got record
> count 0 only when I used the Forward only cursor type (and this is the
> expected behavior for this cursor type).
>
> As Scott said, TestStand just passes commands to the database using
> ADO.
>
> If it still doesn't work, let me know.

Roberto,

See my reply above to Scott. Most of what I'm seeing can most likely be
attributed to an MS MDAC/ADO bug with complex filter expr
essions and
recordcounts.

The other problems I'm seeing (simple one-table queries) I'm willing to
believe are consequences of the above mentioned bug. (Though I'm still not
exactly sure why I didn't see similar problems performing the same queries
using Visual Basic/ADO)

Looks like this is probably a MS bug. Sorry for blaming TestStand!

Bob Rafuse
Etec Inc.