About Query Designer considerations for SQL Server databases (ADP)

The Query Designer is designed to support versions of Microsoft SQL Server 2000, but is compatible with earlier versions as well. If you attempt to use a SQL Server 2000 feature when connected to version 7.0 or 6.5 of SQL Server, the server will report an error.

When the Query Designer builds a statement in the SQL pane, it will use syntax specific to SQL Server whenever possible. For example, database objects such as tables and views are qualified using SQL Server owner names.

You can also type SQL Server-specific syntax in the SQL pane. In some cases when you verify a query, the Query Designer converts server-specific syntax to ANSI standard syntax. However, the changed query will always return the same results.

When you enter the names of database objects (tables, views, and columns) in the SQL pane, you must provide sufficient information for SQL Server to identify the object you want. Database objects are identified with unique names that consist of up to three parts (for tables and views) or four parts (for columns):

database.owner.table

database.owner.table.column

Note   In Microsoft SQL Server 7.0 or higher, you can join tables from different databases on the same server. In that case, database objects can have four part names.

In general, you need to provide only enough qualifiers to uniquely identify the object you want to work with. For example, if you are working with a column called price in the titles table in the current database, you can simply reference the column by name, as in this SQL statement:

SELECT price
FROM titles
				

However, if you are working with two tables, such as orders and products, and each has a column called price, you must qualify references to the column with the appropriate table name, as in this example:

SELECT products.prod_id, orders.price
FROM orders INNER JOIN products ON 
 orders.prod_id = products.prod_id
				

When you use the Diagram pane and Grid pane to work with tables in the current database, the Query Designer automatically adds owner and table qualifiers for you. If you are not the owner of a table that you are working with, the owner’s name will appear in the table names. For example, if you work in the pubs database, the owner name dbo will appear in front of table names. If you are working with multiple tables, the Query Designer adds table name qualifiers to column names.

The standard delimiters for literal strings in SQL are single quotation marks ('). By default, SQL Server reserves double quotation marks (") as delimiters for database objects.

To avoid ambiguity, the Query Designer always sets Quoted Identifiers on, so that double quotation marks are always interpreted as database object delimiters. If you have previously turned Quoted Identifiers off, the Query Designer overrides your setting.

Therefore, in the Query Designer, always use single quotation marks to enclose string literals. Use double quotation marks only as needed for database objects delimiters.

Text information in a SQL Server database can be stored in uppercase letters, lowercase letters, or a combination of both. For example, a last name can appear as "SMITH," "Smith," or "smith."

Depending on how SQL Server was installed, databases can be case-sensitive or case-insensitive. If a database is case-sensitive, when you search for text data, you must construct your search conditions using the exact combination of uppercase and lowercase letters. For example, if you are looking for a name such as "Smith," you cannot use the search conditions "=smith" or "=SMITH."

In addition, if the server was installed as case-sensitive, you must provide database, owner, table, and column names using the correct combination of uppercase and lowercase characters. If the case of the name you provide does not match exactly, SQL Server returns an error reporting an "invalid object name."

When you create queries using the Diagram and Grid panes, the Query Designer will always accurately reflect the case-sensitivity of your server. However, if you enter queries in the SQL pane, you must be careful to match names to the way they will be interpreted by the server.

If the server was installed with a case-insensitive option, you can enter database object identifiers and search conditions using any combination of uppercase and lowercase characters.

Tip

The Query Designer supports the use of certain SQL Server constants, variables, and reserved column names in the Grid or SQL panes. Generally, you can enter these values by typing them in, but the Grid pane will not display them in drop-down lists. Examples of supported names include:

• IDENTITYCOL     If you enter this name in the Grid or SQL pane, the SQL Server will recognize it as a reference to an auto-incrementing column.

• Predefined global values    You can enter values such as @@CONNECTIONS and @@CURSOR_ROW into the Grid and SQL panes. For a complete list of global values, see the SQL Server documentation.
• Constants (niladic functions)    You can enter constant values such as CURRENT_TIMESTAMP and CURRENT_USER in either pane. For a complete list of constants (niladic functions), see the SQL Server documentation.
• NULL    If you enter NULL in the Grid or SQL panes, it is treated as a literal value, not a constant.
• ROWGUIDCOL If you enter this name in the Grid or SQL pane, the SQL Server will recognize the column as a row global unique identifer column. For more information on row global unique identifier columns, see the SQL Server documentation.

In the Grid pane, to specify that you want data interpreted as money, precede the value with $ or $- (for negative values). Do not include a comma or other delimiter to indicate thousands. Formatting values this way alerts the Query Designer that you are entering values to be treated as or compared to data in money or smallmoney type columns. Values are rounded to the nearest hundredth of a unit.

You can use $ no matter what currency you are working with. When a query displays values from money columns in datasheet view, it does not include the $ prefix. Depending on the setting in the Windows Regional Settings dialog box, currency data might or might not include a comma or other delimiter for thousands.

In Microsoft SQL Server 7.0 or higher, you can include references to the GUID data type, which is used to store globally unique identifiers. In Update and Append queries you can call the NEWID( ) function to generate a new GUID to be stored in the database.

When you are creating a Select query, the only operations allowed with a GUID type column are comparisons based on equality (= and <>).

If you are working with Microsoft SQL Server 7.0 or higher, you can specify a zero-length string in an Update, Append Values, or Make-Table query by entering two single quotation marks, as in the following Update query example:

UPDATE employee
SET minit = ''
WHERE emp_id = 'CFS88322F'
				

In versions of SQL Server 6.5 or earlier, two single quotation marks are treated as a single space. For example, you can use quotation marks in the following expression: 'abc' + '' + 'def'. The resulting value would be 'abc def'.

If you are entering a query directly in the SQL pane, you can add optimizer hints to specify the use of specific indexes, locking methods, and so on. However, when reformatting the contents of the SQL pane, the Query Designer might not maintain these comments. Optimizer comments are not represented graphically.

For more details about optimizer hints, refer to the Microsoft SQL Server documentation.

Data containing extended characters— that is, characters outside the ASCII range 32 (space) to 126 (~), including international characters such as "ä," "ç," "é," "ñ," and "ß"— can require special handling when you are working with SQL Server.

The representation of extended characters in a result set depends on the code page in use. A code page is a character set that a computer uses to interpret and display data properly. Code pages usually correspond to different platforms and languages and are used in international applications. For example, the ASCII value 174 might appear as the symbol "^®" in one code page but as a chevron character in another code page.

In general, code pages are divided into ANSI code pages and OEM code pages. ANSI code pages, in which high-numbered ASCII values represent international characters, are used in Windows. OEM code pages, in which high-numbered ASCII values represent line-drawing and punctuation characters, were designed for MS-DOS.

When data is entered into a SQL Server database, SQL Server settings on the local (client) computer specify whether the data is stored in ANSI or OEM format. The option is specified using the Automatic ANSI to OEM conversion option on the DB Library Options tab in the SQL Server Client Configuration dialog box. This dialog box is available by clicking the Microsoft SQL Server 7.0 or later Client Network Utility from Programs on the Start menu. (For more details about Automatic ANSI to OEM conversion, refer to the SQL Server documentation.)

By default, this option is selected for the SQL Server Client, a choice which causes the data to be converted from high-numbered ASCII characters to OEM characters. For example, if the OEM conversion option is set and you enter the name "Günther" in a column and then save the row, the character "ü" will be converted to another character before the row is stored in the database.

The results of queries that you create in the Query Designer are affected by the format in which extended-character data is stored in combination with the setting of the OEM conversion option in the SQL Server Client Configuration dialog box. Depending on these variables:

• You might not be able to search for data that includes high-order ASCII characters.
• Your query results might appear with incorrect characters substituted for high-order ASCII characters.

In general, if data is stored in OEM format, you should set the OEM conversion option so the data will display properly and so you can search it. If data is stored in ANSI format (that is, it was not converted to OEM format) but you have set the OEM conversion option, the data will not display properly and you will not be able to search for it.

To determine whether data was stored in OEM format, you can use a query to display the contents of the table or tables you are working with. If extended characters appear incorrectly, the OEM conversion setting is probably wrong. Close the query and the Access project, change the setting in the SQL Server Client Configuration dialog box, and then open the Access project and query again.

Some types of legal SQL Server queries cannot be represented graphically in the Query Designer. You can still enter them in the SQL pane, and they will execute correctly. However, the Query Designer will display the Query Definitions Differ dialog box and report an error when you execute your query or change panes.

Several types of SQL Server queries are not supported graphically, including:

• Queries using INTERSECT.
• Queries using UNION [ALL].
• Queries using CASE.
• Any data definition (DDL) query, including CREATE TABLE, ALTER TABLE, CREATE PROCEDURE, ALTER PROCEDURE, and so on. CREATE VIEW and ALTER VIEW queries are not supported graphically, but you can use the Query Designer to create and edit views.
• Update and Delete queries that include an extra FROM clause (FROM table FROM table) that specifies the list of rows to update or delete.
• Queries using the FOR BROWSE clause.
• Queries that include UPDATE as a search condition.
• Queries including CURRENT OF.