SQL Server Q&A

Symptoms
You can create a Microsoft SQL Server 2005 Integration Services (SSIS) packageby using SQL Server Business Intelligence Development Studio. When you create this package, you can create an expression for a property of the SSIS packageto update or to populate the property at runtime. For example, if the SSIS package contains a Send Mail task, you can create an expression for the Subject property and for the MessageSource property. You can use the Subject property expression to dynamically update the subject of an e-mail message. You can use the MessageSource property expression to dynamically update variables in the e-mail message, such as variables that are populated by a Row Count transformation.
This article discusses how to create a dynamic subject or message for the Send Mail task.
Resolution
The following is a sample property expression for the Subject property in a Send Mail task.

“Package>>> ” + @[System::PackageName] +” was executed at>>> ” + (DT_WSTR, 40) @[System::StartTime] + ” by user>>> ” + @[System::UserName] + ” on Machine>>> ” + @[System::MachineName]If you use this sample property expression, the subject of an e-mail message is updated dynamically. The subject will include the following information: Text information
In this example, the e-mail messagesubject includesthe “Package>>> ” text information.System variables
The e-mail message includes the following system variables:PackageName
The package name.StartTime
The time that the package was executed. UserName
The user who executed the package.MachineName
The name of the computer on whichthe package was executed. You can also include more information in the expression, such as a user-defined variable. For example,a Data Flow task can include a Row Count transformation before the Send Mail task. (The Row Count transformation is used to count rows.) The Row Count transformation populates a user-defined variable that is named @myrowcount. This variable stores the count information in the data flow.
To specify that an e-mail message be sent only if the row count is smaller than a certain value, modify the control flow by using precedence constraints. To do this, follow these steps: In SQL Server Business Intelligence Development Studio, right-clickData Flow Task,and then click Add Precedence Constraint.Double-click the precedence constraint that you just created. In the Precedence Constraint Editor dialog box, click Expression and Constraint in Evaluation operation.In the Expression box, type the following expression:
@myrowcount < 2 In the Precedence Constraint Editor dialog box, click OK.If less than two rows are processed in the data flow, an e-mail message is sent.
Additionally, you can use the Send Mail task as part of an error handler. For example, you may want to send an e-mail message to administrators when an SSIS package does not execute. To do this, create an OnError event handler for the package, and then add a Send Mail task to the event handler. Create a subject property expression that captures the time that the package is executed, the start time of the container, or the start time of the event handler from the relevant system variables. For example, create an expression that is similar to the following.

“Error in the task: ” +@[System::SourceName] + “with the ID: ” +@[System::SourceID] + ” has failed at: ” + (DT_WSTR, 20) @[System::ContainerStartTime] + “.”This sample expression uses the following system variables: StartTime
The time when the package was executed.ContainerStartTime
The time that the container started.EventHandlerStartTime
The time that the event handler started.

Symptoms
This article provides general guidelines to help you decide which type of antivirus software to run on the computers that are running SQL Server in your environment.
Resolution
Microsoft strongly recommends that you individually assess the security risk for each computer that is running SQL Server in your environment and that you select the tools that are appropriate for the security risk level of each computer that is running SQL Server. Additionally, Microsoft recommends that before you roll out any virus protection project, test the whole system under a full load to measure any changes to stability and performance.
Virus protection software requires some system resources to execute. You must perform testing before and after you install your antivirus software to determine if there is performance impact to the computer that is running SQL Server.
Security risk factorsThe value to your business of the information that is stored on the computer.The required security level for that information.The cost of losing access to that information.The risk of either virus or bad information propagating from that computer.
High-risk serversAny server is at some risk of infection. The highest risk servers generally meet one or more of the following criteria:The servers are on the public Internet.The servers have open ports to servers that are not behind a firewall.The servers read or execute files from other servers.The servers run HTTP servers, such as Microsoft Internet Information Services (IIS) or Apache. (For example: SQL XML for SQL Server 2000.)The servers are also hosting file shares.The servers use SQL Mail to handle inbound or outbound e-mail messages.Servers that do not meet the criteria for a high-risk server are generally at a lower risk, although not always.
Virus tool typesActive virus scanning: This type of scanning checks incoming and outgoing files for viruses.Virus sweep software: Virus sweep software scans existing files for file infection. It detects files after they are infected with a virus.This type of scanning may cause the following SQL Server database recovery and SQL Server full-text catalog file issues:If the virus sweep has opened a database file and still has it open when SQL Server tries to open the database (such as when SQL Server starts or when SQL Server opens a database that AutoClose has closed), the database to which the file belongs might be marked suspect. The SQL Server database files typically have the .mdf, .ldf, and .ndf file suffixes.If the virus sweep software has a SQL Server full-text catalog file open when the Microsoft Search service (MSSearch) tries to access the file, you may experience problems with the full text catalog.Vulnerability scanning software: The Microsoft Security Tool Kit CD includes best practice guidelines, information about securing your system, and service packs and patches that can protect your system against virus attacks. It also provides Microsoft tools to help you secure your systems and keep them secure. To download it, visit the following Microsoft Web site:
http://www.microsoft.com/security/(http://www.microsoft.com/security/)Antispyware software: Spyware and unwanted software refers to software that performs certain tasks on your computer, typically without your consent. For more information about how to help protect the computer from spyware and unwanted software, visit the following Microsoft Web site:
http://www.microsoft.com/protect/computer/spyware/default.mspx(http://www.microsoft.com/protect/computer/spyware/default.mspx)Additionally, Microsoft has released the Microsoft Windows Malicious Software Removal Tool to help remove specific, prevalent malicious software from computers that are running Microsoft Windows Server 2003, Microsoft Windows XP, or Microsoft Windows 2000. For more information about the Microsoft Windows Malicious Software Removal Tool, click the following article number to view the article in the Microsoft Knowledge Base:
890830?(http://support.microsoft.com/kb/890830/) The Microsoft Windows Malicious Software Removal Tool helps remove specific, prevalent malicious software from computers that are running Windows Vista, Windows Server 2003, Windows XP, or Windows 2000
Directories to exclude from virus scanningWhen you configure your antivirus software settings, make sure that you exclude the following files and directories from virus scanning. Doing this improves the performance of the files and helps make sure that the files are not locked when the SQL Server service must use them. However, if these files become infected, your antivirus software will not unable to detect the infection.SQL Server data files
These files usually have one of the following file name extensions:.mdf.ldf.ndfSQL Server backup files
These files frequently have one of the following file name extensions:.bak.trnFull-Text catalog filesThe directory that holds Analysis Services data
Note The directory that holds all Analysis Services 2005 data and Analysis Services 2008 data is specified by the DataDir property of the Analysis Services instance. By default, the path of thisdirectory is C:\Program Files\Microsoft SQL Server\MSSQL.X\OLAP\Data.If you use Analysis Services 2000, you can view and change the data directory by using Analysis Manager. To do this, follow these steps:In Analysis Manager, right-click the server, and then click Properties.In the Properties dialog box, click the General tab.The directory appears under Data folder.The directory that holds Analysis Services temporary files that are used during Analysis Services processing
Note The directory that holds all Analysis Services 2005 and Analysis Services 2008 temporary files during processing is specified by the TempDir property of the Analysis Services instance. By default, this property is empty. When this property is empty, the default directory is used. This directory is C:\Program Files\Microsoft SQL Server\MSSQL.X\OLAP\Data. If you use Analysis Services 2000, you can view and change the directory that holds temporary files in Analysis Manager. To do this, follow these steps:In Analysis Manager, right-click the server, and then click Properties.In the Properties dialog box, click the General tab.On the General tab, notice Note the directory under Temporary file folder.
Optionally, you can add a second temporary directory for Analysis Services 2000 by using the TempDirectory2 registry entry. If you use this registry entry, consider excluding from virus scanning the directory to which this registry entry points.For more information about the TempDirecotry2 registry entry, see the “TempDirectory2″ section of the following Microsoft Developer Network (MSDN) Web site:
http://msdn.microsoft.com/en-us/library/aa902654(SQL.80).aspx#sql2k_anservregsettings_topic52(http://msdn.microsoft.com/en-us/library/aa902654(SQL.80).aspx#sql2k_anservregsettings_topic52)Analysis Services backup files
Note By default, in Analysis Services 2005 and in Analysis Services 2008, the backup file location is the location that is specified by the BackupDir property. By default, this directory is C:\Program Files\Microsoft SQL Server\MSSQL.X\OLAP\Backup. You can change this directory in the Analysis Services instance properties. Any backup command can point to a different location. Or, the backup files may be copied elsewhere.The directory that holds Analysis Serviceslog files
Note By default, in Analysis Services 2005 and in Analysis Services 2008, the backup file location is the location that is specified by the LogDir property. By default, this directory is C:\Program Files\Microsoft SQL Server\MSSQL.X\OLAP\Log.Directories for any Analysis Services 2005 or Analysis Services 2008 partitions that are not stored in the default data directory
When you create the partitions, these locations are defined in the Storage location section of the Processing and Storage Locations page of the Partition Wizard.
Considerations for clusteringYou can run antivirus software on a SQL Server cluster, but you must make sure that the antivirus software is a cluster-aware version. Contact your antivirus vendor about cluster-aware versions and interoperability.
If you are running antivirus software on a cluster, make sure that you also exclude these locations from virus scanning:Q:\ (Quorum drive)c:\Windows\ClusterIf you back up the database to a disk or if you back up thetransaction log to a disk, you can exclude the backup files from the virus scanning.

Symptoms
Consider the following scenario. You develop a SQL Server 2005-based application. The application uses the SQL Server Management Objects (SMO) DLLs that are included in SQL Server 2005 Service Pack 1. The application calls the Server.JobServer.Jobs.Contains method to determine whether a specified job exists in the job collection. When you run the application on a computer that has SQL Server 2005 Service Pack 2 (SP2) installed, you receive a System.InvalidCastException exception.
Resolution
The fix for this issue was first released in Cumulative Update. For more information about how to obtain this cumulative update package for SQL Server 2005 Service Pack 2, click the following article number to view the article in the Microsoft Knowledge Base:
939537?(http://support.microsoft.com/kb/939537/LN/) Cumulative update packagefor SQL Server 2005 Service Pack 2Note Because the builds are cumulative, each new fix release contains all the hotfixes and all the security fixes that were included with the previous SQL Server 2005 fix release. Microsoft recommends that you consider applying the most recent fix release that contains this hotfix. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
937137?(http://support.microsoft.com/kb/937137/LN/) The SQL Server 2005 builds that were released after SQL Server 2005 Service Pack 2 was releasedMicrosoft SQL Server 2005 hotfixes are created for specific SQL Server service packs. You must apply a SQL Server 2005 Service Pack 2 hotfix to an installation of SQL Server 2005 Service Pack 2. By default, any hotfix that is provided in a SQL Server service pack is included in the next SQL Server service pack.

Symptoms
When you use SQL Server Management Studio in Microsoft SQL Server 2005 to connect to an instance of Microsoft SQL Server 2008, you may experience the following problems.
Problem 1When you open or delete a maintenance plan, you receive the following error message:

Exception has been thrown by the target of an invocation. (mscorlib)
ADDITIONAL INFORMATION:
While trying to find a folder on SQL an OLE DB error was encountered with error code 0×80040E14 (Could not find stored procedure ‘msdb.dbo.sp_dts_getfolder’.).
Problem 2When you copy a database from an instance of SQL Server 2008 to an instance of SQL Server 2005, you receive the following error message:

Message:Executed as user: UserName. …0.0.1080.5 for 32-bitCopyright (C) Microsoft Corp 1984-2005. All rights reserved.Started:TimeProgress: Date TimeSource: SATEESP-MAIN_SATEESP-MAIN_Transfer Objects TaskTask just started the execution.: 0% completeEnd ProgressError: Date TimeCode: 0×00000000Source: SATEESP-MAIN_SATEESP-MAIN_Transfer Objects TaskDescription: An exception occurred while executing a Transact-SQL statement or batch.StackTrace:at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteReader(String sqlCommand)at Microsoft.SqlServer.Management.Smo.ExecuteSql.GetDataReader(String query)at Microsoft.SqlServer.Management.Smo.DataProvider.SetConnectionAndQuery(ExecuteSql execSql, String query)at Microsoft.SqlServer.Management.Smo.ExecuteSql.GetDataProvider(StringCollection query, Object con, StatementBuilder sb, RetriveMode rm)at Microsoft.Sq…The package execution fa…The step failed.
Problem 3When you create a table or a view, you receive the following error message:

Microsoft.SqlServer.Types (Error)
Messages
Either the object or one of its properties is not supported on the target server version. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3207.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.UnsupportedVersionException&LinkId=20476
Resolution
The fix for this issue was first released in Cumulative Update5. For more information about how to obtain this cumulative update package for SQL Server 2005 Service Pack 2, click the following article number to view the article in the Microsoft Knowledge Base:
943656?(http://support.microsoft.com/kb/943656/LN/) Cumulative update package 5 for SQL Server 2005 Service Pack 2Note Because the builds are cumulative, each new fix release contains all the hotfixes and all the security fixes that were included with the previous SQL Server 2005 fix release. Microsoft recommends that you consider applying the most recent fix release that contains this hotfix. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
937137?(http://support.microsoft.com/kb/937137/LN/) The SQL Server 2005 builds that were released after SQL Server 2005 Service Pack 2 was releasedMicrosoft SQL Server 2005 hotfixes are created for specific SQL Server service packs. You must apply a SQL Server 2005 Service Pack 2 hotfix to an installation of SQL Server 2005 Service Pack 2. By default, any hotfix that is provided in a SQL Server service pack is included in the next SQL Server service pack.