If you are creating queries using Microsoft® SQL Server™, you can take advantage of features specific to that server. The following guidelines outline differences you will notice in the Query Designer when you work with SQL Server, and provide information about SQL Server-specific features that you can use.
Below you will find information about:
The Query Designer is designed to support versions 6.5 and 7.0 of the Microsoft SQL Server, but is fully compatible with earlier versions as well. If you attempt to use a SQL Server 7.0 feature when connected to version 6.5 of the SQL Server, the server will report an error. If you are using a version of SQL Server earlier than 6.0, the Query Designer will not recognize SQL Server-specific features (such as syntax elements or functions). Instead, the Query Designer will recognize only standard ODBC syntax.
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, you can join tables from different databases on the same server. In that case, database objects can have four part names. For more details, see Working with Tables from Different Data Sources.
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.
For compatibility with other databases, you can use [ and ] around identifier names in the SQL pane. When the SQL statement is reformatted, [ and ] are replaced with double quotation marks ("), which are used to mark database identifiers.
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 (see Identifying Database Objects, above).
The SQL Server ODBC driver supports a Quoted Identifiers setting for the session or connection. If this setting is on, double quotation marks are interpreted as delimiters for identifiers. However, if you turn this setting off, double quotation marks are interpreted instead as delimiters for literal strings.
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 To determine the case sensitivity of a server, execute the stored procedure sp_server_info, and then examine the contents of row 18. If the server has been installed with the case-insensitive setting, the option for sort_order will be set to nocase.
In the SQL pane, you can use the = operator to specify an alias, as in this example:
SELECT tax = price * .1
FROM table
When you change panes or execute the query, the SQL statement is reformatted and the = is replaced with AS.
The SQL Server supports all standard operators for expressions, including % for modulus. In addition, you can use the following bitwise operators in expressions:
| Operator | Meaning |
| & | BITAND |
| | | BITOR |
| ^ | BITXOR |
| ~ | BITNOT |
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:
@@CONNECTIONS and @@CURSOR_ROW into the Grid and SQL panes.CURRENT_TIMESTAMP and CURRENT_USER in either pane.NULL in the Grid or SQL panes, it is treated as a literal value, not a constant.
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 the Results pane, 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.
If you are working with SQL Server version 6.5 or earlier, using the DISTINCT keyword creates a query that cannot be updated in the Results pane.
In the SQL pane, you can use the SQL Server-specific *= and =* operators to specify an outer join. When you change panes or execute the query, the Query Designer reformats your SQL statement, and replaces *= with LEFT OUTER JOIN and =* with RIGHT OUTER JOIN.
In Microsoft SQL Server 7.0, you can include references to the new GUID data type, which is used to store globally unique identifiers. In Update and Insert 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, you can specify a zero-length string in an Update or Insert Values query by entering two single quotation marks, as in the following 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 'abd 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 Client Network Utility from Programs on the Start menu. (For more details about Automatic ANSI to OEM conversion, refer to the Microsoft 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:
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 incorrect, the OEM conversion setting is probably wrong. Close the query and the project, change the setting in the SQL Server Client Configuration dialog box, and then open the 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.
The following types of SQL Server queries are not supported graphically:
The following types of queries cannot be represented graphically, so you must enter them in the SQL pane. They do not result in errors when you execute them or change panes.
If you are using SQL Server 7.0, you can create distributed, heterogeneous queries —queries from input sources (tables and views) outside the server to which you have created a data connection. SQL Server can access any data source that supports OLE DB. You can use input sources from these outside data sources as you would any tables available on the base server (if you have proper permissions to access to the outside data source).
Microsoft SQL Server 7.0 can access outside data sources in two ways. The first is using a linked server, which is defined in the SQL Server database to point to the outside data source. A linked server makes the data source accessible using a naming convention similar to that of native SQL Server data objects. The second is to use a dynamic reference to the outside source using the OpenRowSet( ) function, which allows you to connect to any accessible data source in your query, even if no linked server is defined for it.
Input sources from outside servers do not appear as part of the list of input sources. Instead, to use them, you use syntax in the SQL pane of the Query Designer to refer to the linked server or to include the OpenRowSet( ) function. However, when you refer an outside data source in the SQL pane, the Query Designer adds an input source window to the Diagram pane to represent the outside data source.
To refer to an outside data source using a linked server
linkserver.catalog.schema.object
Where:
Note You must define the SQL Server data source (the linkserver part of the name) before you use this name in the query. Data sources you specify will not appear in Data View. They are only used in the context of the query.
The following is an example of an SQL statement that joins data from tables from the local database with a table on a server called "hrserver":
SELECT e.id, e.lname, h.hiredate
FROM employee AS e INNER JOIN hrserver.hr.dbo.hiredata AS H
ON e.emp_id = h.emp_id
To refer to an outside data source dynamically
OpenRowSet(provider,connectString,object)
Where:
datasource;user id;password, which lists specific connection attributes.
-or-
provider string, which is a single string of named attributes with values for creating the connection, similar to the string used in a .dsn file.
Tip The data source referenced by OpenRowSet( ) is easier to work with if you assign it a table alias.
The following examples illustrate variations on using OpenRowSet( ) to dynamically access data from an outside data source. The first two show access using a set of attributes for the connect string, the first to an Oracle database and the second to a Microsoft® Jet (Access) database. The third example shows how you can pass a connect string.
SELECT t.*
FROM OpenRowSet('MSDASQL', 'OracleDB';'user1';'pwd', pubs.dbo.titles)
AS t
SELECT n.*
FROM OpenRowSet('Microsoft.jet.OLEDB.3.51',
'c:\nwind.mdb';'admin';'pwd', authors)
AS n
SELECT a.*
FROM OpenRowSet('MSDASQL', 'Driver=SQL Server;Server=Test;
UID=user1;PWD=pwd', pubs.dbo.authors)
AS a