Walkthrough: Create and Execute a Simple Transact-SQL Script

Artykuł
02/20/2011

In this walkthrough, you create and execute a simple Transact-SQL script using the Transact-SQL editor in Visual Studio. This walkthrough illustrates the following tasks:

Opening a Transact-SQL editor session.
Connecting to an instance of SQL Server, beginning with SQL Server 2005.
Executing a single query to verify that the connection works correctly.
Disconnecting from the database and work offline.
Updating the script to add several Transact-SQL statements.
Validating the Transact-SQL syntax of the script to identify and correct errors.
Executing the script and viewing the results returned.
Examining client statistics.
Saving your script.

Prerequisites

You must have Visual Studio and an instance of SQL Server, beginning with SQL Server 2005. A login to the instance of SQL Server is also required. For more information about SQL Server permissions, see Lesson 2: Configuring Permissions on Database Objects.

To open a Transact-SQL editor session

On the Data menu, point to Transact-SQL Editor, and click New Query Connection. The Transact-SQL editor appears, and the Connect to Server dialog box appears on top of the editor.

Next, you provide information to connect to your instance of SQL Server.

To connect to your instance of SQL Server

On the Connect to Server dialog box, confirm that the Server type is set to Database Engine.
On the Server name box, specify the name of the database server. To specify a default instance of SQL Server that is running on the same computer that you are running Visual Studio, enter the name of the computer.
In the Authentication box, select Windows Authentication or SQL Server Authentication.
1. To use your Microsoft Windows credentials to log on to the server, click Windows Authentication.
2. To use SQL Server Authentication to log on to the server, select SQL Server Authentication, and then type your user name and password in User name and Password boxes.
Click Connect. A connection is established to the server and the default database associated with your login.
On the Transact-SQL Editor toolbar, select the database of your choice in the Database list.

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

To verify your connection

In the Transact-SQL editor, type: SELECT SERVERPROPERTY('ServerName')
On the Data menu, point to Transact-SQL Editor, and click Execute SQL. The results pane appears and displays the query results in a grid. There are several other ways to execute a query. For more information, see How to: Execute a Query.

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

To disconnect from the server and work offline

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

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

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

To add statements to your Transact-SQL script

In the Transact-SQL editor, delete the SELECT statement that you added in a previous step.
Type the following in the editor:
```
select SERVERPROPERTY('BuildClrVersion')
select SERVERPROPERTY('Collation')
sel SERVERPROPERTY('Edition')
```
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

On the Data menu, point to Transact-SQL Editor, and click Validate SQL Syntax. You will be automatically prompted to re-connect to your instance of SQL Server.
To reconnect to the server, complete the Connect to Database Engine dialog box as you did earlier in this walkthrough.
Then click Connect. After the connection is made, Visual Studio will immediately perform the validation of your Transact-SQL statements.

The Results pane appears and shows the Messages tab. Because the script that you typed contained an error (sel instead of select), the following error appears on the Messages tab:

Msg 102, Level 15, State 1, Line 3

Incorrect syntax near 'SERVERPROPERTY'.

Correct the third statement to match the following:

select SERVERPROPERTY('Edition')

On the Data menu, point to Transact-SQL Editor, and click Validate SQL Syntax to revalidate the script. The syntax validates and the following message appears on the Messages tab: Command(s) completed successfully.

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

To execute the script and view the results

On the Data menu, point to Transact-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, in the Results tab. 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.

Each call to the SERVERPROPERTY function returns one row.
Click the Messages tab, which displays the messages that the server returns for each statement executed. In this case, the following messages appear:

(1 row(s) affected)

To examine client statistics

On the Data menu, point to Transact-SQL Editor, and click Include Client Statistics to enable client statistics.
Execute the Transact-SQL script a few more times: On the Data menu, point to Transact-SQL Editor, and click Execute SQL. You can also use CTRL+SHIFT+E to execute the script.
Click the Client Statistics tab to display information about each execution of the script. One column appears each time you execute the script.

To save your script to disk

On the File menu, click Save SQLQueryN.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 ServerPropertyExamples.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.

Udostępnij za pośrednictwem