Walkthrough: Create and Execute a Simple Transact-SQL Script

Article
08/23/2010

In this walkthrough, you create and execute a simple Transact-SQL (T-SQL) script against the Northwind sample database using the T-SQL Editor in Visual Studio Team System Database Edition. This walkthrough illustrates the following tasks:

Opening a Transact-SQL Editor session.
Connecting to the Northwind sample database.
Executing a single query to verify that the connection works correctly.
Disconnecting from the database and work offline.
Updating the script to add several T-SQL statements.
Validating the T-SQL syntax of the script to identify and correct errors.
Executing the script, and examine the results returned.
Saving your script.

Prerequisites

You must have installed the Northwind sample database on the instance of Microsoft SQL Server 2000 or Microsoft SQL Server 2005 on your local computer.

To open a Transact-SQL editor session

On the Data menu, point to T-SQL Editor, and click New Query Connection.

The Transact-SQL Editor appears, and the Connect to Database dialog box appears on top of the editor.

Next, you provide information to connect to your Northwind database.

To connect to your Northwind database

If you have previously defined a connection to your Northwind database, click the connection in the Connection String list, then go to step 6.
If you do not have a previously defined connection string for your Northwind database, click New Connection.

The Connection Properties dialog box appears.
Type or click (local) as your database server.
Click Use Windows Authentication as the authentication method to use to connect to your server.
In Select or enter a database name, type or click Northwind, and click OK.

The Connection Properties dialog box closes, and you are returned to the Connect to Database dialog box.
Click OK to confirm your choice for database connection.

The Connect to Database dialog box closes, and your Transact-SQL Editor is available with a connection to your Northwind database.

Next, you verify your connection by running a very simple SELECT statement.

To verify your connection

Type SELECT * FROM [dbo].[Suppliers] in the Transact-SQL Editor.
On the Data menu, point to T-SQL Editor, and click Execute SQL.

The results pane appears and displays the query results in a grid.

Next, you disconnect from the database server and work offline.

To disconnect from the server and work offline

On the Data menu, point to T-SQL Editor, point to Connection, and click Disconnect.

Your editor session disconnects from the server. The window title changes to SQLQuery_N.sql - Disconnected, where N is a sequentially assigned number. The property browser reflects that you are working in a disconnected state.

Next, you modify your T-SQL script to include multiple statements.

To add statements to your T-SQL script

In the T-SQL Editor, delete the SELECT statement that you added in a previous step.
Type the following in the editor:
```
EXECUTE [dbo].[SalesByCategory] 'Beverages', '1997'
GO
EXE [dbo].[Ten Most Expensive Products]
GO 
```
Important Note:

These statements contain a syntax error on purpose to demonstrate validation in the next procedure.

Next, you will validate the syntax of your script.

Important Note:
These statements contain a syntax error on purpose to demonstrate validation in the next procedure.

To validate the syntax of your script

To reconnect to the database, on the Data menu, point to T-SQL Editor, point to Connection, and click Connect.

The Connection Properties dialog box appears, with the server, authentication, and database information. The dialog box shows the values that you used to connect in an earlier step.
Click OK to reconnect to the same database and server.
On the Data menu, point to T-SQL Editor, and click Validate SQL Syntax.

The Results pane appears and shows the Messages tab. Because the script that you typed in contains an error (EXE instead of EXEC or EXECUTE), the following error appears on the Messages tab: Msg 170, Level 15, State 1, Line 1

Line 1: Incorrect syntax near '.'."
Correct the second statement to match the following:
```
EXECUTE [dbo].[Ten Most Expensive Products]
```
On the Data menu, point to T-SQL Editor, and click Validate SQL Syntax to revalidate the script.

The SQL syntax is valid appears on the Messages tab.

Next, you will reconnect to the database, execute the script, and examine the results.

To execute the script and examine the results

On the Data menu, point to T-SQL Editor, and click Execute SQL to execute your script.

The results of the two statements appear in the Results pane. By default, the results appear in a grid. If you cannot see both result sets, use the right-most vertical scrollbar in the Results pane or resize the Results pane by using the splitter bar.

The SalesByCategory stored procedure returns 12 rows, and the Ten Most Expensive Products stored procedure returns 10 rows.
Click the Messages tab, which displays the messages that the server returns for each statement executed. In this case, the following messages appear:

(12 row(s) affected)

(10 row(s) affected)
Click the Client Statistics tab to display information about each execution of the script. One column appears each time you execute the script.
On the Data menu, point to T-SQL Editor, and click Execute SQL to execute your script again.
Click the Client Statistics tab again to redisplay it.

A second column appears for the second execution, and the averages column is updated accordingly.

In the final step, you save your script.

To save your script to disk

On the File menu, click Save SQLQuery_N.sql (again, where N is a sequentially assigned number).

The Save File As dialog box appear, in which you can specify a path and a file name for the script.
In File name, type Northwind_Sales.sql.
Click Save to save your script to disk.

Your script is saved to disk. The window title is updated to display the new name, which appears in the tooltip for the window tab.

Share via