Operations and Configuration


This section focuses on the tools that you can use to configure and manage services, administer client and server network configurations, configure email support in SQL Server 2005, and run SQL Server scripts from the DOS command prompt.

SQL Server Configuration Manager

SQL Server Configuration Manager is yet another example of Microsoft's initiative to combine multiple tools into a single integrated tool. SQL Server Configuration Manager provides the functionality of three tools provided with SQL Server 2000: Service Manager, Server Network Utility, and Client Network Utility. SQL Server Configuration Manager is an MMC snap-in (%SystemRoot%\system32\SQLServerManager.msc) that provides the means to manage SQL Server services and connectivity settings. SQL Server Configuration Manager internally uses WMI to access and update SQL Server services and connectivity details.

Figure 5.10 shows the SQL Server Configuration Manager. The three top-level items on the tree on the left are SQL Server 2005 Services, SQL Server 2005 Network Configuration, and SQL Native Client Configuration. When you select SQL Server 2005 Services from the tree, the grid on the right shows various SQL Server 2005 services including Relational Engine, Analysis Services, Integration Services, Notification Services, Reporting Services, Agent, Full-Text, and the SQL Server Browser service. You can right-click a service and select Properties to view and change service settings. Figure 5.10 shows SQL Server Configuration Manager, listing various SQL Server 2005 services.

Figure 5.10. SQL Server Configuration Manager is an MMC snap-in application that helps you manage services and connectivity settings.


You can use SQL Server Configuration Manager to start, pause, resume, or stop services and to view and change service properties, such as service start mode (automatic, manual, or disabled) and the account name under which the service runs. If you would like to change the account under which the services should run, it is recommended that you use SQL Server Configuration Manager to do that. This ensures that the new account is assigned the required permissions on the disk folders and registry hives.

The SQL Server 2005 Network Configuration node in the SQL Server Configuration Manager tree can be used to configure network libraries that SQL Server listens on. If you expand this node in the tree, you should see items such as Shared Memory, Named Pipes, TCP/IP, and VIA for Virtual Interface Architecture, which is a network library designed to support highly reliable, fast, efficient data transfer between servers by using hardware from Giganet. You can right-click any network library and select Properties to view and configure network library settings or to enable or disable the protocol.

The SQL Native Client Configuration node can be used to manage the client net-libraries and define server alias names. Click Client Protocols and you should see all the client network libraries on the right hand side. You can right-click or double-click any entry to set the properties for the selected client network library. To create a new client alias, you expand the SQL Native Client Configuration node, right-click Aliases, and select New Alias.

The SQL Server Configuration Manager can also connect to a different server and manage services and connectivity settings for that server. To connect to a different server, you select the Start menu, right-click My Computer, and click Manage. In Computer Management, you right-click Computer Management (Local) and then select Connect to Another Computer. You connect to the remote server and under Services and Applications, you should see SQL Server Configuration Manager, which you can use to control SQL Server services and network settings on that server.

SQL Server Configuration Manager ships with all SQL Server 2005 editions, including SQL Server 2005 Express Edition.

The SQLCMD Utility

SQLCMD is a new DOS command-line utility introduced in SQL Server 2005 to interactively execute T-SQL statements and also to execute scripts from the command line.

SQL Server 2005 no longer ships the DB-librarybased isql.exe, and it discourages the use of the ODBC-based osql.exe. SQLCMD.exe uses the SQL native client, which is OLE DB based, to communicate with SQL Server. SQLCMD doesn't just replace osql.exe, but it also contains various enhancements that make it more robust and usable.

SQLCMD Basics

SQLCMD accepts several command-line switches, similarly to osql.exe. Also as with osql.exe, the SQLCMD command-line parameters are case sensitive. You can pass the -? or /? command-line switch to get a list of all the parameters that SQLCMD accepts. If you pass the -S parameter along with the server name, such as MyServer or MyServer\InstanceName, SQLCMD connects to the specified SQL Server instance by using Windows authentication.

Let's look at some basic SQLCMD examples.

The following statement connects to an instance named YUKON on the server DDGXP (-S) by using a trusted connection (-E); update the statement to connect to your SQL Server 2005 instance. It defaults to a database named AdventureWorks, and the -p switch tells SQLCMD to print performance statistics after every query is executed:

SQLCMD -S DDGXP\YUKON -E -d AdventureWorks -p

After you run this statement from a DOS command prompt, you should see the 1> prompt, where you can type T-SQL commands, press Enter, type GO, and press Enter to execute the query. You can type EXIT to end the SQLCMD session.

You use the -Q switch to execute the specified query and exit:

SQLCMD -S DDGXP\YUKON -E -d AdventureWorks -Q "SELECT Name FROM Sales.Store"

If you pass the -q switch, SQLCMD executes the query and stays in interactive query mode, and you have to type EXIT to end the SQLCMD session.

You can use the -Z switch to change the SQL Server password:

SQLCMD -S DDGXP\YUKON -U sa -P OldPassword2005 -Z NewPWD1234

If you pass the -z switch, SQLCMD changes the password and stays in interactive query mode, and you have to type EXIT to end the SQLCMD session.

One of the nice enhancements introduced in SQLCMD is the ability to provide multiple input script files. The following statement asks SQLCMD to connect to a SQL Server 2005 instance named YUKON, run the script files c:\1.sql and c:\2.sql, save the output results in a file called results.txt, separate the columns using the tilde character (~), and save the results.txt file as a Unicode file (-u switch) instead of using the default ANSI file format:

SQLCMD -S DDGXP\YUKON -i c:\1.sql,c:\2.sql -o results.txt -s~ -u

The -L switch is for getting a list of local and other remote SQL Server machines that are broadcasting on the network:

SQLCMD -L

If you pass -Lc instead of -L, SQLCMD generates "clean" output, which does not have the Servers: heading line or any formatting spaces in front of the listed server names.

Table 5.3 lists all the SQLCMD switches.

Table 5.3. SQLCMD.exe Command-Line Switches

Switch

Description

-S

Specifies the SQL Server instance to connect.

-U

Specifies the SQL Server login ID, if using SQL authentication to connect.

-P

Specifies the SQL server password, if using SQL authentication to connect.

-E

Specifies to use trusted or Windows authentication to connect.

-d

Specifies the default database to connect to.

-i

Specifies one or more input script files to execute.

-o

Specifies the output file to which results should be redirected.

-c

Specifies the batch terminator string. The default is GO.

-A

Logs in to SQL Server with a dedicated administrator connection (DAC), described later in this chapter.

-L or -Lc

Lists the servers.

-Q

Executes the specified query and exits.

-q

Executes the query and stays in interactive query mode.

-Z

Changes the SQL Server password for the login specified with -U and exits.

-z

Changes the SQL Server password for the login specified with -U and stays in interactive query mode.

-v

Passes variable values, as discussed later in this chapter.

-s

Specifies a column separator.

-h-1

Specifies to avoid printing column headers in the output.

-W

Removes trailing spaces from column values.

-k1 or -k2

Replaces each control character, such as a tab or a newline character, with a single space (-k1) or replaces each and consecutive control characters with a single space. In other words, if you have two consecutive tabs in the data, -k1 causes the tabs to be replaced with two space characters; -k2 causes the tabs to be replaced with a single space.

-p or -p1

Prints statistics. If 1 is specified, prints colon-separated stats (for example 4096:1:1:1.00:1000.00, which signifies, network packet size in bytes : number of transactions : clock time total in ms. : clock time avg. in ms.: transactions per second).

-I

Turns on the QUOTED_IDENTIFIER option.

-H

Specifies the name that is shown in the hostname column of the sys.processes catalog view.

-l

Specifies the login timeout, in seconds.

-t

Specifies the query timeout, in seconds.

-w

Specifies the screen width, a number between 9 and 65535. The default is 80.

-a

Specifies the packet size, a number between 512 and 32767.

-e

Prints the input script to standard output.

-f

Inputs and/or outputs a code page.

-R

Specifies to use client regional settings.

-u

Specifies to use Unicode output.

-m

Customizes the display of error messages. If an error occurs in a T-SQL script, SQLCMD shows the message number, the state, and the error level only if the T-SQL error severity level is greater than or equal to value specified with -m switch.

-V

Specifies the lowest severity level that SQLCMD reports. If an error occurs in a T-SQL script, SQLCMD reports the severity level only if the T-SQL error severity level is greater than or equal to value specified with -V switch. You can use %ERRORLEVEL% at the DOS command prompt or in a batch file to find out the severity level when it is reported.

-b

Specifies that if an error occurs in a T-SQL script, SQLCMD aborts the batch and exits, returning 1 as %ERRORLEVEL% if T-SQL error has a severity level greater than 10; else, %ERRORLEVEL% is 0.

-X or -X1

Disables the SQLCMD commands ED and !!. If 1 is passed, and if the script contains either ED or !! command, SQLCMD exits while showing a warning.

-r or -r0 or -r1

Redirects all error messages (-r1) or error messages with severity 17 or higher (-r or -r0) to stderr.

-y

Limits the number of characters that are returned for varchar(max), nvarchar(max), varbinary(max), xml, text, ntext, image, and user-defined data types.

-Y

Limits the number of characters that are returned for char, nchar, varchar(n) (where 1n8000), nvarchar(n) (where 1n4000), and sqlvariant.


SQLCMD and Environment Variables

One way to instruct SQLCMD about the server and database to connect to, the authentication method to use, the network packet size, and other settings, is by passing the appropriate command-line switches, as described in the previous section. The other method is to set some environment variables. For instance, if you set an environment variable named SQLCMDDBNAME to a database name string, either by using a SET statement at the command prompt or in a batch file or by using the Environment Variables button on the Advanced tab of System Properties dialog (which you open by selecting Control Panel | System), SQLCMD connects to the database specified by the environment variable, provided that the -d switch is not passed to it. Similarly, if you set the SQLCMDSERVER environment variable and you don't pass the -S parameter to SQLCMD, SQLCMD connects to the server specified by the environment variable. In other words, when you run SQLCMD, in addition to command-line switches, SQLCMD also looks at certain environment variables. If an environment variable is set to some value, and if an overriding switch is not provided on the command line, SQLCMD honors the environment variable. Here is how it works:

C:\>SET SQLCMDSERVER=DDGXP\YUKON C:\>SET SQLCMDDBNAME=AdventureWorks C:\>SET SQLCMDPACKETSIZE=8192 C:\>SQLCMD -p1 1>SELECT TOP 1 Name FROM Sales.Store; 2>GO Name -------------------------------------------------- A Bike Store (1 rows affected) 8192:1:1:1.00:1000.00

These statements at the DOS command prompt set environment variables for server, database, and network packet size and then run SQLCMD with -p1 to have colon statistics printed after each line. Note that SQLCMD automatically connects to a SQL Server 2005 instance, makes AdventureWorks the current database, and sets the packet size to 8192.

You can use the :setvar SQLCMD command to change the SQLCMD environment variable value. SQLCMD commands such as :setvar are discussed in the next section.

The following DOS command-prompt statements connect to a SQL Server 2005 instance and set ~ as the column separator:

C:\>SQLCMD -S DDGXP\YUKON -E -dAdventureWorks -s~ 1> SELECT TOP(1) LEFT(Name, 15) Name, SalesPersonID FROM Sales.Store; 2> GO Name           ~SalesPersonID ---------------~------------- A Bike Store   ~          280 1> :setvar SQLCMDCOLSEP "," 2> SELECT TOP(1) LEFT(Name, 15) Name, SalesPersonID FROM Sales.Store; 3> GO Name           ,SalesPersonID ---------------,------------- A Bike Store   ,          280 1> :setvar SQLCMDCOLSEP ";" 2> SELECT TOP(1) LEFT(Name, 15) Name, SalesPersonID FROM Sales.Store; 3> GO Name           ;SalesPersonID ---------------;------------- A Bike Store   ;          280

After executing one SELECT statement, this script sets SQLCMDCOLSEP to a comma (,), and the results returned by a subsequent SELECT statement separate the columns by using a comma instead of a tilde. This is repeated again to change the column separator to a semicolon (;).

Table 5.4 lists the SQLCMD-specific environment variables and their command-line switch equivalents.

Table 5.4. SQLCMD-Specific Environment Variables

Variable

Switch

SQLCMDSERVER

-S

SQLCMDUSER

-U

SQLCMDPASSWORD

-P

SQLCMDDBNAME

-d

SQLCMDPACKETSIZE

-a

SQLCMDLOGINTIMEOUT

-l

SQLCMDSTATTIMEOUT

-t

SQLCMDHEADERS

-h

SQLCMDWORKSTATION

-H

SQLCMDCOLSEP

-s

SQLCMDCOLWIDTH

-w

SQLCMDERRORLEVEL

-m

SQLCMDMAXVARTYPEWIDTH

-y

SQLCMDMAXFIXEDTYPEWIDTH

-Y

SQLCMDEDITOR

Not applicable (Determines which application to launch when the !!ED SQLCMD command is run.)

SQLCMDINI

Not applicable (Used to set a startup script, which is a script that is executed when SQLCMD is started.)


SQLCMD Commands

In addition to T-SQL commands, SQLCMD supports few other commands that you can run in interactive mode or inside T-SQL scripts executed using SQLCMD. The previous section shows an example of thisusing the setvar command to set environment and scripting variables.

Every SQLCMD command must be preceded with a colon character. For backward compatibility with osql.exe, a colon is not required before EXIT, QUIT, ED, !!, and RESET, but it is recommended that you use the colon in front of all SQLCMD commands except GO. The !! is used to execute operating system commands, much like xp_cmdshell in SQL Server 2000 extended stored procedures.

Try out the following:

C:\>SQLCMD -S DDGXP\YUKON -E -dAdventureWorks 1> :Help <Output omitted for brevity; shows help on all SQLCMD commands> 1> :ServerList <Output omitted for brevity; List of servers, output similar to -L switch> 1> !!DIR <Output omitted for brevity; Runs Operating System command> 1> :!!CLS <Runs Operating System command; clears the screen> 1> INSERT INTO tblTest VALUES (10); 2> GO 100 < Output omitted for brevity; Runs the INSERT statement 100 times> 1> :r c:\1.sql < Output omitted for brevity; Runs another script file>

The final command in this example can be particularly very helpful. You can use the :r command to execute some other script from within your T-SQL script. Also note the command before the :r line. The number 100 is passed with the GO statement, which leads to executing the batchthe INSERT statement, in this case100 times.

Table 5.5 lists the available SQLCMD commands.

Table 5.5. SQLCMD Commands

SQLCMD Command

Description

:Help

Lists SQLCMD commands along with a short description of each command.

GO [number]

Executes the T-SQL batch in statement cache the specified number of times. The default is 1.

:RESET

Clears the statement cache.

:ED

Launches the script editor. It defaults to the DOS command prompt utility EDIT.exe. You can cause the editor to launch by setting the SQLCMDEDITOR environment variable. The statement SET SQLCMDEDITOR=sqlwb sets SQL Server Management Studio as the script editor when the ED SQLCMD command is run. Note that if the statement cache is empty, the :ED command does nothing.

:!!

Executes an operating system command, much like the xp_cmdshell extended stored procedure in SQL Server 2000.

:QUIT

Ends the SQLCMD session.

:EXIT [(statement)]

Ends the SQLCMD session. You can execute a SQL statement and return a number that can be captured in a batch file or at the command prompt by using the %ERRORLEVEL% variable:

 

1>:EXIT (SELECT 0) C:\>ECHO %ERRORLEVEL%

 

The EXIT statement in the SQLCMD session returns 0, and then on the DOS command prompt, the %ERRORLEVEL% variable prints the value returned by the EXIT statement.

:ServerList

Lists the locally configured servers and the names of the servers broadcasting on the network. The output is similar to that of the -L switch.

:List

Prints the content of the statement cache.

:Listvar

Displays a list of the variables that are currently set, using :setvar command.

:Error <filename>|stderr|stdout

Redirects all error output to the file specified by <filename> to stderr or to stdout.

:Out <filename>|stderr|stdout

Redirects all query results to the file specified by <filename> to stderr or to stdout.

:Perftrace <filename>|stderr|stdout

Redirects all performance trace information to the file specified by <filename> to stderr or to stdout.

:Connect

Connects to an instance of SQL Server, closing the current connection.

 

The following example first connects to a SQL Server 2005 named instance and then uses the :connect SQLCMD command to connect to a SQL Server 2000 default instance on the machine, using trusted connection:

 

C:\>SQLCMD -E -S DDGXP\YUKON 1> :connect DDGXP Sqlcmd: Successfully connected to server 'DDGXP'. 2> SELECT @@Version 3> GO ------------------------------ Microsoft SQL Server 2000 - 8.00.760 (Intel X86) <Output truncated for brevity>

 

You can pass the server and instance name, the timeout, and SQL Server authentication details with the :connect command.

:On Error[ exit|ignore]

Tells SQLCMD what to do in case of a T-SQL script error in the batch. The exit option causes the SQLCMD session to end, returning the appropriate error value; the ignore option causes SQLCMD to disregard the T-SQL script error and continue executing the batch or script.

:XML [ON|OFF]

Specifies whether the results are returned as a binary stream (in hex format) or as text. If your script makes use of the FOR XML clause and you would like the results to be returned as XML text, you should turn on the XML SQLCMD option by running :XML ON. By default this option is OFF, and with that the FOR XML results are returned as binary stream in hex format. When this option is ON, even the error messages are returned as XML streams.


Tip

The Query Editor in SQL Server Management Studio supports SQLCMD mode, which can be useful for authoring, editing, and executing T-SQL scripts that use SQLCMD features, such as SQLCMD commands and variables. You can use the SQLCMD Mode button on the SQL Editor toolbar or the Query | SQLCMD Mode menu item to turn on or off the SQLCMD mode.


SQLCMD and Variables

The support for using variables with SQLCMD script enables you to write generic scripts so that one script can be used for multiple scenarios. Your T-SQL scripts can use $(VariableName) syntax in the T-SQL statements, and then you can pass the variable values from the command line (by using the -v switch), by using the :setvar SQLCMD command, or by setting their values as environment variables, using the SET statement.

Let's say that the following text is available in a script file called SQLCMDVariables.sql:

EXEC master.dbo.sp_addumpdevice     @devtype = N'disk',     @logicalname = '$(DeviceLogicalName)',     @physicalname = '$(DevicePhysicalName)'; GO BACKUP DATABASE    $(DBName) TO    $(DeviceLogicalName) GO

This script creates a new backup device and then performs a full database backup. The logical device name, physical file location, and database to back up are variables that can be set by using the :setvar command in the script (which is not done in this case), or they can be environment variables that are set by using the SET statement or by using the Control Panel, or they can be passed to SQLCMD by using the -v switch. Here is how to pass variables by using the -v switch:

[View full width]

SQLCMD -E -S DDGXP\YUKON -v DeviceLogicalName="AW1" DevicePhysicalName="c:\AW1.bkp" DBName="AdventureWorks" -i "c:\SQLCMDVariables.sql"

Instead of passing the variable values on the command line by using the -v switch, you can set an environment variable with the same name as the variable name in the script, and SQLCMD uses the environment variable value if the variable value is not passed using -v:

C:\>SET DeviceLogicalName=AW2 C:\>SET DevicePhysicalName=c:\AW2.bkp C:\>SET DBName=AdventureWorks C:\>SQLCMD -E -S DDGXP\YUKON -i "c:\SQLCMDVariables.sql"

The third way to set variable values is by using the :setvar SQLCMD command, as illustrated earlier.

You can also use the $(VariableName) syntax to access environment variables:

1> SELECT '$(NUMBER_OF_PROCESSORS)' 2> GO

These statements, when executed in a SQLCMD session, return the value of the NUMBER_OF_PROCESSORS environment variable, indicating the number of processors on the local machine.

Startup Scripts

You can set the SQLCMDINI environment variable to a SQL script file that will be run every time SQLCMD is started. Assume that the following text is available in the c:\SQLCMDStartup.sql file:

SET NOCOUNT ON PRINT '' PRINT @@SERVERNAME PRINT @@VERSION PRINT GETDATE() PRINT SYSTEM_USER PRINT USER PRINT DB_NAME() PRINT ''

Now, if you set the SQLCMDINI environment variable to the c:\SQLCMDStartup.sql file, this script will be run every time you start SQLCMD:

C:\>SET SQLCMDINI=c:\SQLCMDStartup.sql C:\>SQLCMD -E -S DDGXP\YUKON <executes script c:\SQLCMDStartup.sql; output omitted for brevity>

Using a Dedicated Administrator Connection

In situations where SQL Server is locked or running in an abnormal state and not responding when you try to connect to it, the members of sysadmin server role can connect to a local or remote SQL Server 2005 instance over TCP/IP by using SQLCMD and the -A switch to troubleshoot problems on the SQL Server machine. This feature is called dedicated administrator connection (DAC). SQL Server allows only one DAC per instance. When you connect to SQL Server by using the -A switch, it is strongly recommended that you restrict your usage to certain diagnostic and troubleshooting commands only and set the isolation level to READ UNCOMMITTED to avoid any blocking. The 'remote admin connections' sp_configure option must be turned on in order to allow remote connections using DAC. If you are connecting to a named instance, the SQL Browser service must be started.

Introducing Database Mail

SQL Server 2005 introduces a highly scalable, reliable, and secure method of sending emails from within T-SQL code. This new feature, called Database Mail, uses Simple Mail Transfer Protocol (SMTP) to send email messages. Unlike the SQL Mail feature in SQL Server 2000, Database Mail does not require Microsoft Outlook or MAPI to be installed on the server.

Database Mail scales well because it uses Service Broker to asynchronously send email messages. Service Broker in turn activates an external process (DatabaseMail90.exe) to send email messages. This external Database Mail process uses a standard ADO.NET connection to communicate with SQL Server. This process isolation increases server reliability because even if the external process stops or fails, it does not affect the SQL Server process. The other reliability feature introduced in Database Mail is the ability to specify a failover SMTP server. If Database Mail cannot send by using one SMTP server, it tries to use another specified SMTP server to send emails. Unlike SQL Mail in SQL Server 2000, Database Mail is cluster aware and is fully supported on a cluster. Database Mail is fully supported on 64-bit SQL Server 2005 installations.

Some of the ways in which Database Mail offers a secure emailing solution are the MaxFileSize (to limit the size of email attachments) and ProhibitedExtensions (to restrict attachment file types) configuration settings, the fact that it does not require Outlook or MAPI on the SQL Server machine, and auditing and logging.

Note

Database Mail only supports sending emails. Unlike SQL Mail in SQL Server 2000, Database Mail in this release cannot be used to read emails. MAPI-based SQL Mail is still available in SQL Server 2005 and can still be used to read and send emails.


Configuring Database Mail

Following the "secure by default" principal, SQL Server 2005 does not enable Database Mail support by default. You have to configure Database Mail before you can use msdb.dbo.sp_send_dbmail stored procedure to send emails. You can enable and configure Database Mail by using the Database Mail Configuration Wizard. You can launch the Database Mail Configuration Wizard from within Management Studio by right-clicking the Database Mail node under Management folder in Object Explorer and selecting the Configure Database Mail menu item.

Note

Because Database Mail depends on Service Broker to send email messages, Service Broker must be enabled in the msdb database.


The Database Mail Configuration Wizard allows you to perform multiple tasks. You can use this wizard to enable and install Database Mail, to manage Database Mail profiles and accounts, to manage Database Mail security settings, and to configure Database Mail system parameters, such as MaxFileSize and ProhibitedExtensions.

The msdb database is the Database Mail host database. It contains the stored procedures and messaging objects for Database Mail. Database Mail stores the SMTP server information, profiles, accounts, security, and system parameters in the msdb system database.

Figure 5.11 shows various things that you can do by using the Database Mail Configuration Wizard.

Figure 5.11. The Database Mail Configuration Wizard can be used to enable and configure Database Mail.


The msdb.dbo. sp_send_dbmail stored procedure posts the email message in a Service Broker queue. The Service Broker activation feature launches an external application (DatabaseMail90.exe) when there is a message in the queue. This external process reads the messages from the Service Broker queue and the SMTP account and profile setting, including the SMTP server settings, from the msdb mail host database; and sends messages to the email server. The SMTP server is then responsible for actually delivering the messages.

Here are the steps for enabling Database Mail and sending an email by using Database Mail. These steps assume that you have an SMTP server installed and working. These steps use the local SMTP service installed as part of an IIS Web server. The SMTP virtual server is configured to grant connection and relay access to the IP address on which SQL Server 2005 is installed (local, in this case):

1.

Launch SQL Server Management Studio. Connect Object Explorer to a SQL Server 2005 instance.

2.

Expand the Management folder, right-click Database Mail, and select Configure Database Mail.

3.

The Database Mail Configuration Wizard appears. Click Next on the Welcome screen.

4.

Select the first option, Set up Database Mail by performing the following tasks, and then click Next.

5.

Set the Profile Name to TestProfile. Click the Add button next to the SMTP Accounts heading and provide the SMTP account details. Type the account name as TestAccount; specify outgoing and reply-to email addresses, and specify the SMTP server. For a local IIS SMTP service, specify localhost. Provide SMTP authentication information if needed. Click OK and then click Next on the New Profile screen.

6.

Make TestProfile a public profile by selecting the check box next to it. Also make it the default profile. Click Next.

7.

The next wizard screen is Configure System Parameters. This is where you can specify things like the maximum file size for attachments, prohibited file extensions, and so on. For this demo, leave the defaults and click Next.

8.

Verify the Database Mail configuration settings and click Finish when you're done reviewing. When the wizard finishes, click Close to end the wizard. Database Mail is now configured.

9.

Send an email by using Database Mail:

EXEC msdb.dbo.sp_send_dbmail     @profile_name = 'TestProfile',     @recipients = 'someonw@somewhere.com',     @body = 'Simple Database Mail Message',     @subject = 'Database Mail';

When you run these statements, you should get the "Mail queued." message, and the recipient should get an email if the SMTP server is configured properly.

The msdb.dbo.sp_send_dbmail stored procedure takes various parameters, which you can use, for instance, to send results of a query in text or HTML format, attach files, and specify CC recipients, importance, sensitivity, and so on. Refer to SQL Server 2005 Books Online for details on other parameters.

Monitoring and Troubleshooting Database Mail

You can monitor and troubleshoot Database Mail by using the auditing and logging tables in the msdb mail host database.

For each outgoing email, Database Mail adds a status/logging record to the sysmail_log table, and the actual mail details are saved in the sysmail_mailitems table. You need to run a SELECT query on these two tables to find out whether Service Broker was able to activate the DatabaseMail90.exe application, whether DatabaseMail90.exe encountered problems while sending messages, and the status of each outgoing email. The event_type and description fields in the sysmail_log table and the sent_status field in the sysmail_mailitems table can be used to determine the success or failure for each outgoing email. The value of 0 for the sent_status field means that the DatabaseMail90.exe application is yet to process the message; 1 means success, indicating DatabaseMail90.exe was able to deliver message to the SMTP server; and 2 means that DatabaseMail90.exe received an error while delivering the message to the SMTP server, and in this case you should look at the sysmail_log table for more details about the failure.

In addition to the above-mentioned two tables, you can also access other Database Mail tables also in the msdb mail host database to verify the profile, SMTP account, security, and other system configuration settings. Table 5.6 lists these Database Mail-related tables in the msdb database.

Table 5.6. Database Mail-Specific Tables in the msdb System Database

Table Name

Description

sysmail_attachments

Contains one record for each attachment in every email message sent.

sysmail_profile

Contains Database Mail SMTP profile details.

sysmail_principalprofile

Contains profile security details.

sysmail_account

Contains SMTP account details, including email address, display name, and reply email address.

sysmail_profileaccount

Associates account(s) with a profile.

sysmail_servertype

Contains a row that indicates whether a server is incoming, outgoing, or both. In this release, Database Mail does not support reading emails.

sysmail_server

Contains SMTP server information, including name, port, and credentials.

sysmail_configuration

Contains the server configuration settings, such as MaxFileSize and ProhibitedExtensions.


In addition to tables mentioned in Table 5.6, you should also check the system and application event log (eventvwr.exe) entries for any errors related to Database Mail or SMTP. You should also check the folders under the C:\Inetpub\mailroot directory to see whether emails are being queued.




Microsoft SQL Server 2005(c) Changing the Paradigm
Microsoft SQL Server 2005: Changing the Paradigm (SQL Server 2005 Public Beta Edition)
ISBN: 0672327783
EAN: 2147483647
Year: 2005
Pages: 150

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net