Data Retrieval on Microsoft SQL Server
The MSS connector fully supports the read and selectdb ProcScript statements, with some restrictions.
The stepped hitlist mechanism is fully supported.
By default, ten hits are selected per step. This can be overruled by the connector option step size.
read
The read statement is fully supported, including the where and order by clauses, and with MSS U5.0 connector, the offset option.
When using the where clause,
all text between the double quotation marks ("") is inserted into the generated
select statement literally as entered.
To guarantee that a specific order is returned, use the order by clause. You cannot include further Text and Image fields in the order by qualifier. MS SQL does not support the creation of views that include the order by clause, but when a view is created, you can use this clause to access the tables.
If you experience poor performance when using
order by on a large table, consider using the
keysetcursor connector option to specify the cursor type
SQL_CURSOR_KEYSET_DRIVEN.
selectdb
The use of selectdb functions is restricted for some fields.
| MS SQL field | max | min | sum | ave | count |
|---|---|---|---|---|---|
| Bit | No | No | No | No | Yes |
|
Text |
No | No | No | No | No |
|
Image |
No | No | No | No | No |
|
Binary |
Yes | Yes | No | No | Yes |
|
Varbinary |
Yes | Yes | No | No | Yes |
|
Char |
Yes | Yes | No | No | Yes |
|
Varchar |
Yes | Yes | No | No | Yes |
|
Datetime |
Yes | Yes | No | No | Yes |
|
Date1 |
Yes | Yes | No | No | Yes |
|
Smalldatetime |
Yes | Yes | No | No | Yes |
|
Numeric |
Yes | Yes | Yes | Yes | Yes |
1 Only for mapping=23 on
Microsoft SQL Server 2008 and higher. |
|||||
