Archive
A random records
The problem is quite simple. I want to generate top 10 random rows from my table. At the first galance I thought about RAND() function. The Query:
SELECT RAND()
returns different values (from 0 to 1) whenever it was executed. But where is the problem? RAND() method returns the same value for all rows within a given call so the query:
SELECT TOP 10 rand(),RoleSecurityObjectPermissionID, SecurityObjectName
FROM dbo.RoleSecurityObjectPermission
or
SELECT TOP 10 rand(RoleSecurityObjectPermissionID), SecurityObjectName
FROM dbo.RoleSecurityObjectPermission
returns always the same rows!
The solution is to use the NEWID() function. NEWID() creates unique value of a type uniqueidentifier. For each rows NEWID() generates a unique, different value so the query:
SELECT TOP 10 RoleSecurityObjectPermissionID, SecurityObjectName
FROM dbo.RoleSecurityObjectPermission
ORDER BY NEWID()
returns exacily what we want.
Import and Export in SQL Server
There are three ways to make import and export data in SQL Server. In this post I want to focus on those mechanisms.
The BCP (Bulk Copy Program)
The BCP is a command line program recognized as the oldest internal mechanism in SQL Server. It provided the most performed solution to import a well defined data files to SQL Server environment and also export the tables to the other one file. The BCP defined a few important options:
- in – import data from file to the particular table
- out – export a whole table to the file
- -n – write data in the native SQL Server code
- -c – write data in the text format
Generally, we used -n option to export data to the other instance of SQL Server, and -c when it is required to export data to the other database (ex. Oracle).
Example:
bcp AdventureWorks.HumanResources.Department out C:\department.txt -n -SHOTEK -T bcp AdventureWorks.HumanResources.Department in c:\department2.txt -c -SHOTEK -U admin -P admin
Adventages:
- Simple in use
- Possibility to restrict the import/export data using SQL query instead of table name
Disadvantages:
- It’s a command line program
- It’s required to determinate the database instance, eventually login and password
- Possibility to work on the only single table
BULK INSERT
BULK INSERT is a similar to BCB program but works as a T-SQL query so it is not required to care about SQL Server instance or administrator’s login/password.
Example of use:
1. Data in persons.txt file:
Jan,Kowalski,j.kowalski@domain.com Piotr,Nowak,p.nowak@domain.com Admin,Admin,a@admin.com
and the BULK INSERT query:
BULK INSERT PersonList FROM C:\persons.txt WITH (FIELDTERMINATOR = ',');
Adventages:
- T-SQL Query
- It’s not required to identify instance name and login/password
Disadventages:
- There are no option for export data
- It can work only on the single table or view (similar to the BCP)
SQL Server Import and Export Wizard
The last but not least import/export mechanism is a SQL Server Import and Export Wizard integrated with SQL Server Integration Services. While BCP and BULK INSERT requires the SQL Server as a destination point of data, I/E Wizard doesn’t care about it.
To use it:
- In Object Explorer choose the database and from the context menu type: Tasks -> Export Data;
- In the next step localize the source and the destination database;
- Choose Copy Data From One Or More Tables Or Views;
- Choose table to export;
Adventages:
- It can be used the any database source (XML, OLE DB, Access)
- Possibility to make import/export
- Can work on the different table/views
Disadventages:
- It’s required to get the knowledge about SSIS
Summary
There are no golden mean to use one of the mechanisms above. Each of them has an adventages and disadventages. After import/export action it’s required to consider which one can help us to realize our gols as soon as possible with the least amount of work.
Configuring Database Mail
Database Mail is a solution for sending e-mail directly from SQL Server. In daily administration work is using mainly to notify administrator about alerts, performance errors or changes in database. In SQL Server 2008 Database Mail was added instead of SQL Mail in a few reason:
- Removing dependencies with Microsoft Mail Application Programming Interface;
- Simplitification configuration and management;
- Improving performance of sending e-mail.
Database Mail uses SMTP protocol. Each messages are registered in Msdb database and periodically administered by SQL Server Agent job. Database Mail contains a profiles in which the accounts are assumed. There are possible to create public and private account. First one is available for each user and second one only for them that have explicity granted access. If there are no profile created on SQL Server the default profile is used for sending e-mail. Default profile could be used also for approved users who won’t to identify profile during sending messages process.
To configure Database Mail is required to execute following query:
EXEC sp_configure 'Database Mail XPs', 1 GO RECONFIGURE WITH OVERRIDE GO
If you have a problem with executing this query and got the message like that:
Msg 15123, Level 16, State 1, Procedure sp_configure, Line 51
The configuration option ‘Database Mail XPs’ does not exist, or it may be an advanced option.
it is cause the Database Mail is under advanced option. The query below should solve the problem:
EXEC sp_configure 'show advanced options', 1 GO RECONFIGURE WITH OVERRIDE GO EXEC sp_configure 'Database Mail XPs', 1 GO RECONFIGURE WITH OVERRIDE GO
Inside Object Explorer open the Management tab and click right mouse botton on the Database Mail. From context menu choose ‘Configure Database Mail’. Create new profile typed the data like on the picture below:
To test if Database Mail works fine go back to the Object Explorer, click once more on the Databse Mail and choose ‘Sent Test E-mail’ (compare the picture below):
When you press the ‘Sent test email’ button you received the information that your message is in queue. SQL Server Agend send it soon :)
You must to also know that Notification Services is not available in SQL Server 2008.
