Using the SQL Server Query Processor with the Scantron OLE DB Provider

The following examples are based on Microsoft® SQL Server 2000 SP1 and assume that a linked server has been created to map to the data file, mts-240.dat. This is a fictitious file name and is not included in the samples installed by the ScanTools Plus Link SDK.

For more information on the SQL Server Query Processor, see the SQL Server documentation.

Examples of common tasks with the SQL Server Query Processor and the Scantron OLE DB Provider

Synthesize a 4-part name for a table in the data file

• The first part is the linked server name, mts-240.dat.
• The second (catalog) is NULL.
• The third part (schema) is NULL.
• The fourth part is the table (document definition name).
• The period (.) character delimits the parts.

SELECT * FROM [mts-240.dat]...[Answer Sheet 9 (229634)]

Use the SQL Server query processor to augment the limited SQL capabilities of the Scantron OLE DB Provider

SELECT * FROM [mts-240.dat]...[Answer Sheet 9 (229634)] WHERE [Ident #] > '100' ORDER BY [Ident #]

Use a pass-through query

SELECT * FROM openquery ([mts-240.dat],'SELECT * FROM [Answer Sheet 9 (229634)] ')

Use multiple table queries

SELECT b.[Test id],b.[Inst LName]+ b.[Inst FName] AS [Teacher Name], A.* FROM [mts-240.dat]...[Answer Sheet 9 (229634)] AS A ,
[mts-240.dat]...[Header, Full Page] AS B