Creating Queries with the SQL Server Web Assistant

The SQL Server Web Assistant offers several ways to build queries. You can:

• Build a query from a database/table hierarchy.
• Enter a query as free-form text.
• Use an existing stored procedure.

Building a Query from a Database/Table Hierarchy

Select this option for interactive assistance in building a query.

The SQL Server Web Assistant Query screen appears.

    To interactively build a query from a database/table hierarchy

1. From the Query screen, select the Build A Query From A Database Hierarchy option.
2. From the database list, select and expand a database. To expand a database object, click the plus sign (+) next to it.
3. From the expanded database list, select the table(s) to use in your query. This option is equivalent to a SELECT * FROM table_name statement.
4. To select individual columns, expand the table selection. All columns are selected by default. To cancel a selection, clear the column(s).
5. In the Do You Want Any Additional Criteria . . . box, type additional clauses for the query, such as WHERE or ORDER BY.
6. To continue, choose the Next button. To exit, choose the Cancel button.

For example, to create a web page that contains author information from the sample pubs database, select and expand the pubs database, and then select the authors table. To include only au_id and au_lname columns, expand the authors table and select only these columns. To further refine the query and include only authors from the state of California, add the following clause to the Additional Criteria list box:

WHERE state = "CA"
  

Note You must enclose string values in double quotation marks ("). Using a single quotation mark (') will generate a syntax error.

Entering a Query as Free-form Text

Select this option to directly enter Transact-SQL queries or to execute external stored procedures. When you select this option from the Query screen, the following screen appears.

    To enter a Transact-SQL query

1. From the Query screen, select the Enter A Query As Free-Form Text option and select a database.
2. In the Type Your Query text box, type your query.
3. To continue, choose the Next button. To exit, choose the Cancel button.

You can use the free-form text option with the EXECUTE command to execute existing stored procedures or extended stored procedures.

    To execute an extended stored procedure

1. From the Query screen, select the Enter A Query As Free-Form Text option and select a database.
2. Use the EXECUTE command with the extended stored procedure call and applicable arguments.
3. To continue, choose the Next button. To exit, choose the Cancel button.

For example, to execute a directory command in the command shell, use the xp_cmdshell stored procedure within the following syntax:

EXEC xp_cmdshell dir
  

Using a Query in a Stored Procedure

Select this option to execute Transact-SQL stored procedures. When you select this option from the Query screen, the following screen appears.

    To use a query in a stored procedure

1. From the Query screen, select the Use A Query In A Stored Procedure option and select a database.
2. Select a stored procedure.

   The procedure text is displayed and cannot be edited.

3. In the Does Your Stored Procedure Require Any Arguments text box, type applicable arguments for the stored procedure.
4. To continue, choose the Next button. To exit, choose the Cancel button.