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.

To create a T-SQL query:

  1. Log on to Microsoft SQL Server Management Studio and 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.
  10. Paste the SQL query into the §Sql section of the report.
  11. Save the completed report query as a .crp file.