Creating a Transaction-SQL Query

Use the Transaction-SQL (T-SQL) editor in the SQL Management Studio to create and test queries to be used for reports.

1. Log on to Microsoft SQL Server Management Studio:
   1. From the Windows Start menu, click All Programs > Microsoft SQL Server 2008 > SQL Server Management Studio.
   2. In the Connect to Server dialog box, click Connect.
2. In the left pane, expand Databases and expand the database for which you want to create an SQL query.
   Expand Tables to see the database tables.

3. Right-click the database and click New Query.
4. In the right pane, create a T-SQL query using these elements:

   Syntax	Example
   SELECT column	SELECT U.Username, G.Groupname, G.Description
   FROM table_name	FROM Users U, Groups G, GroupMembers M
   WHERE selection_criteria	WHERE G.GroupID = 2 AND G.GroupID = M.GroupID AND U.UserID = M.UserID You cannot use variable names defined in a report query argument when you create a T-SQL query. After you add the T-SQL query to a report query, you can add argument substitution.

   The following is an example of a completed T-SQL query:

   SELECT U.Username, G.Groupname, G.Description
   FROM Users U, Groups G, GroupMembers M
   WHERE G.GroupID = 2 AND
         G.GroupID = M.GroupID AND 
         U.UserID = M.UserID

5. Click Execute (toolbar) or Query > Execute.

   The Results tab shows the data returned by the query.

   If there are problems, the Messages tab describes the query errors.

6. Repeat steps 4 and 5 until your SQL query returns the information you want.
7. To test the query on another vault, select the vault database from the Available Databases field above the left pane.
8. Copy the SQL query.
9. Open a formatted report query in Notepad.

   For information on formatting a report query, see Formatting a Report Query.

10. Paste the SQL query into the §Sql section of the report.
11. Save the completed report query as a .crp file.