· First open
the Enterprises Manager of SQL server 7. Click on start button, choose programs
and then Microsoft SQL server.
The database is located under the server and the server group and hence it is
necessary for you to expand the server first and the server group next in the
process for writing a SQL query statement.
When you see the data Transformation services folder, right click on it and choose
or click on New packages so as to get the DTS package window.
Now from the menu choose Data from, Microsoft OLE DB provider for SQL server.
For your query to run you should get connected to the server first and hence create
a connection to the SQL server.
Now you will get one connection window that will take inputs from you. Specify
or enter the details of your SQL server name and after giving the correct authentication
mode and database, click the Ok button.
For creating the exact SQL query, after selecting the Task, one DTS package window
will appear and note to select the Execute SQL Task from this window. After correctly
selecting the execute SQL Task option, you will get a pop-up Execute SQL Properties
Now from the Execute SQL properties window click on the build query button and
you will get an additional window which is nothing but the data Transformation
services Query Designer window. This is the place where from you can start building
your SQL query statements using a graphical tool
For selecting a table, you can either drag and drop the table from the left side
frame onto the upper right side frame or drag and drop the table and from the
left frame onto the upper- right frame or right click either in the upper-right
frame or right click in the upper left frame , and after choosing the add table
property and after selecting the table name, say Customers and Orders tables,
you can choose Ok and close the Add Table window
For adding the Grid pane to the Data Transformation services Query designer Window,
right click on the upper left window and choose the show panes option.
Now you will have 4 frames. The upper right frame is the Diagram pane, the left
frame is the Table/view list, the lower right frame is the SQL pane and the middle
or right frame is the Grid panel.
From the Data Transformation services Query Designer window you will be able to
hide the view or table list jut by clicking the far left toolbar button.
Now you can drag one of the fields name, say the company name field from the customer
table from the diagram pane and drop it to the Grid panes first row.
Now for getting the Order ID field drag the field from the orders table that is
shown in Diagram pane and drop it in the Grid panes second row.
For the select Group By filter in the menu you can add alias num orders.
Now select the Group By from the menu after right clicking in the Grid pane.
When you see in Grid pane the order Id row, click the group by Column and change
the value to count.
Now you can select the sort option by clicking the sort Type column from the Order
ID row and choose the Ascending option.
Now you can define a criteria say that COUNT must be higher that 7. For achieving
this, enter the value >7 in the criteria field after clicking it in the order
you have successfully built one SQL query, which is very similar to any other
listing type SQL statement. You can also the see the text format of the SQL query
you have just built in the SQL pane of the Data Transformation services Query
At this stage it is also possible for you to use your SQL query statement in any
of the ASP script by just copy-paste action on the SQL statement.
Once you click on the Ok button in the Data Transformation services query designer
window the job of creating a SQL query statement will be over.
appeared SQL properties window will help in executing the SQL query and if you
click Ok it will allow you to finish the query building process.
Now you can close the DTS Designer window
it comes to creating and executing SQL statements and queries, there are many
other graphical tools that can come to your aid and if you can have the Visual
Inter Dev 6.0, then you can make use of it for creating SQL scripts much similar
to the DTS query Designer in SQL server 7.0
to our mailing list and receive new articles
through email. Keep yourself updated with latest
developments in the industry.
: We never rent, trade, or sell my email lists to
We assure that your privacy is respected
.NET Programming Tutorial Homepage