Accessing PDMS Data through SQL

Once you have configured SQL Server using the SQL Direct Configuration Configure SQL Server option, you can access PDMS data using SQL queries. To do this, you need a software application that provides a SQL command window. Examples of such tools are:

• Microsoft SQL Server Management Studio 2005

• Microsoft Visual Studio .NET 2003 (Server Explorer window)

• Microsoft SQL Server Enterprise Manager 2000

(Unfortunately, there is no suitable tool actually provided with MSDE 2000, but all the above products can be used successfully with data in MSDE.)

These products initially provide a "tree view" of the data within SQL Server. You can expand each branch, down through different databases to individual tables and data views. The SQL Direct Configuration Configure SQL Server option will have created data views within the "master" database on your SQL server, the views being named according to the tables you created in Table Designer, and saved in your table schema file referenced in your connection settings.

You can retrieve data from data views by right-clicking on them and selecting Open View or Retrieve Data From View (depending on which product you are using).

You can also open a SQL command window and execute SQL statements. (In Visual Studio .NET 2003 you need to have retrieved data from the view first in order to open the SQL window, as the 'SQL' option on the query menu bar is greyed out until you have done this.)

Once you have opened a SQL command window you can access PDMS data using SQL queries. For example:

SELECT *

FROM EQUIPMENT

WHERE REFNO = '=12345/67'

Where EQUIPMENT is a table you created in Table Designer, REFNO is a column name and =12345/67 is a value of REFNO.

Note
The use of single quotes around character data values.

Full documentation of the SQL Query language and its use is beyond the scope of this user guide. There are many texts available, both online and in book form.