SQL Server :Creates a database audit specification object using the SQL Server audit feature

You administer a Microsoft SQL Server 2012 server that has a database named Contoso. The Contoso database has a table named ProductPrices in a schema named Sales.

You need to create a script that writes audit events into the application log whenever data in the ProductPrices table is updated.

Which four Transact-SQL statements should you use?

Answer:

With the help from following T-SQL, we'll be able to creates a server audit called C_Audit and then a database audit specification called C_AuditSpec that audits Update statements by the dbo user, for the [Sales].[ProductPrices] table in the ContosoDb database.

1.

use master

2.

create server audit C_Audit to application_log
alter server audit C_Audit with (State = on)

3.

use ContosoDb

4.

create database audit specification C_AuditSpec
for server audit C_Audit
Add (update on [Sales].[ProductPrices] by DBO)
alter database audit specification C_AuditSpec
with (State = on)

Brgds,

Chhavinath Mishra

Database Administrator

Microsoft Certified IT Professional (MCITP)

SQL Server : Export data from the table to a flat file by using the SQL Server Import and Export Wizard

You want to export data from the table to a flat file by using the SQL Server Import and Export Wizard.

You need to ensure that the following requirements are met:

1. The first row of the file contains the first row of data.

2. Each record is of the same length.

3. The date follows the U.S. date format.

4. The file supports international characters.

What should you do?

Answer:

Explanation

1. The first row of the file contains the first row of data.

Column names in first data row

Indicate whether you want to display column names in the first data row so check-box need not to be selected.

2. Each record is of the same length.

Format

Indicate whether to use delimited, fixed width, or ragged right formatting.

Value	Description
Delimited	Columns are separated by a delimiter, specified on the Columns page.
Fixed width	Columns have a fixed width.
Ragged right	Ragged right files are files in which every column has a fixed width, except for the last column, which is delimited by the row delimiter.

So, Fixed width option need to be selected.

3. The date follows the U.S. date format.

Locale

Specify the locale ID (LCID) that defines character sort orders and date and time formatting so English (United States)

4. The file supports international characters.

Unicode

Indicate whether to use Unicode. If you use Unicode, you do not have to specify a code page.

A Unicode character takes more bytes to store the data in the database. As we all know, many global industries wants to increase their business worldwide and grow at the same time, they would want to widen their business by providing services to the customers worldwide by supporting different languages like Chinese, Japanese, Korean and Arabic. Many websites these days are supporting international languages to do their business and to attract more and more customers and that makes life easier for both the parties.

To store the customer data into the database the database must support a mechanism to store the international characters so for international characters support Unicode checkbox should be selected.

Hope this will help.

Brgds,

Chhavinath Mishra

Database Administrator

Microsoft Certified IT Professional (MCITP)