Previous Topic

Next Topic

Book Contents

Book Index

MS SQL Server

Use of Rowcount

Performance of queries, particularly those accessing large table, can be improved by having AQT set the rowcount option. This is discussed in more detail in the section Rowcount for SQL Server and Sybase.

Using Temporary Tables

In SQL Server, you can use temporary tables to store the results of queries for the duration of your session. Example:

Select * into #temp1 from dbo.sysobjects where name like 'sys%';

Select * from #temp1;

When you run these you may get the message: Invalid object name '#temp1'. This problem is due to a limitation of the SQL Server ODBC Driver (for technical types, this is described in http://msdn.microsoft.com/library/en-us/odbcsql/od_6_015_8nce.asp).

You can circumvent this limitation by going to Options > Technical Parameters and checking Use SQLExecDirect. When this option is selected, AQT will process your SQL in a slightly different way that avoids this limitation with the ODBC Driver.

Server-Side Cursors

This is a technical topic for advanced users.

By default, AQT uses client-side cursors. This works well in almost all circumstances, however there is one limitation: only one client-side cursor can be active at once

The only time you are likely to meet this limitation is if you are using the Get More Rows feature. When this feature is used, AQT will not close your query, but will keep it open so you can continue scrolling through the query results. If you try to run another query, while you have an open query, you will get message:

Connection is busy with results for another hstmt

You will only be able to run the second query once the open query has been closed.

If this limitation is a problem for you, you can go to Options > Technical Parameters and check Use SQLExecDirect. When this option is effect, AQT will use Server-Side cursors. This will allow you to have multiple queries open at the same time

Table / View / Column Descriptions

AQT supports the use of table / view / column descriptions within SQL Server. These are held as an Extended Property of the tables / views and columns. AQT uses these with an Extended Property name of MS_Description. This is compatible with the way descriptions are managed within Enterprise Manager.

If you are using a Extended Property name other than MS_Description (for instance Caption), you will need to make a change the AQT config files:

In each of these files, make a global change of MS_Description to (for instance) Caption.

IDENTITY_INSERT

If you wish to use the statement:

set IDENTITY_INSERT tablename on

then this will only work if you have set Options > Technical Parameters > Use SQLExecDirect. If you have not set this option, the set statement will say that it has worked, though it will have been ignored.

Advanced Query Tool
https://www.querytool.com
© 2023 Cardett Associates Ltd. All rights reserved.