Bulk Copy Program (BCP)–A simple way to export data in a file TIP#101

Let’s consider a scenario where the end user require a CSV file of all the records in a table then BCP is one of the simplest way.

BCP stands for Bulk copy program. By the name you got the feeling of lots of data .

Although, there are various options & parameters available with BCP but here we will talk about simple one .

To understand it more clearly lets consider an example.

I have a database with name “IndiandotnetDB” and in this database I have a table with name tblStudentSource which having few records as shown in below figure

Now, the objective here is to export all the data which is in tblStudentSource table to a CSV file.

to achieve this we will use BCP command.  We are running BCP from command prompt as shown in below figure

Although there are various other options available with BCP command but for current post I choose simple one .

Now let me explain the command

BCP IndiandotnetDb.dbo.tblStudentSource OUT d:\File\output\sampleExport.csv –S . –T –c

so in above command “IndiandotnetDB” is my database

“dbo” is schema

“tblStudentSource” is  a table whose data need to be export

OUT – here out stands for export (export the table or query data)

“D:\File\output\” is folder where I want exported file

“sampleExport.csv” is file name which will be created by BCP and contains entire records after execution of BCP command

-S is stands for server I used “.” for localhost we can provide instance name as well

-T is for trusted connection (we can use – U for username – P for password)

-c is stands for character data type if you are not using this then you have to provide data type for each fields of output query result

Below is the output of the command which we run “ a sampleExport.csv file with all the records”

I hope this simple post will help you some where. I will share more details about BCP command in coming post.

Thanks

RJ!!

How to Configure E-mail in SQL Server Step by Step–TIP #96

 

For different reason we need to send database report , data and other SQL Server database related stuff using E-mail . SQL  Server provides E-mail functionality using “Database Email” feature. We can setup any E-mail  in few simple steps.

Let’s follow below steps

Step 1:- Open “Management” option of object Explorer in SQL SERVER  management studio.

You will find “Database Mail” option. now click on configure Database mail option as shown below

 

Step 2:- When you click on Configure database mail you will get below wizard screen

Step 3:- Click on next button then you will below screen which is basically provide you option to setup or manage existing profile . In our case we are going to create a fresh database mail so let the first default option as is as shown in below figure and click on next button

Step 4:- You will get below screen. Just give a unique profile name and click on ADD button which is highlighted

Step 5: Now this is the main setting of mail “SMTP settings”

In this screen we have to give proper E-mail address, password , SMTP server name & Port number. In below screen I used gmail settings for a my gmail account. You can use any email address

Step 6:- Once you assure that everything is perfect in above screen then click on OK button. When click Ok button this SMTP profile will be saved and you can find in below screen now click on next button

Step 7:-  If you want you can make this profile as public default profile means default this profile will be use if someone wants to send mail from sql database. Now click Next button till the end of wizard you will following screens.

 

Step7:- Now click on Finish button then your setting will be actually saved in database and below screen will appear.

Now Once you close the above screen you are good to go.

You can test this by sending a test mail to your account using following screen

I hope this steps may help you to setup database email.

Enjoy Database E-mail and try to send a test mail to me also.

Enjoy !!

RJ

“sp_helptext“ – Useful command to view detail TIP #85

 

It might be already known to you but I thought for sharing because I frequently use this command and it is very useful command.

When someone wants to determine detail of a function or stored procedure he/she can use this useful command.

The syntax is very simple. Just write

sp_helptext Storedprocedure/ functionname

For example If I want to determine detail of a stored procedure “proc_FindStudentUsingCorrect” then I have to write following command

sp_helptext proc_FindStudentUsingCorrect

see below snap for detail

 

You can now copy the result text and check what exactly written in the stored procedure or function.

I hope you will use it in your day to day practice.

Enjoy!!!

RJ

Sequence feature TIP #76

Although it is a old feature for those who knows ORACLE but for SQL server developers it is a new feature.

Let understand it by an example. Suppose we want an auto incremented column a part from primary key which is a identity column,

then to achieve this we can use sequence feature.

We can create  sequence feature by following command

“CREATE SEQUENCE StudentEnrollmentId AS INT
START WITH 2014000
INCREMENT BY 1”

so if you see above statement we have created a sequence with name StudentEnrollmentId which is an integer type sequence and first value means starting point is 2014000 and each time when we call sequence it will be incremented by 1.

We can create same sequence by screen also as shown in below figure

We have other option also  as shown in below

CREATE SEQUENCE SEQUENCE_NAME
AS DATA_TYPE
START WITH <constant>
INCREMENT BY <constant>
MINVALUE value
MAXVALUE value
CYCLE | NO CYCLE
CACHE int | NO CACHE

as shown in above option we can provide minimum & maximum for sequence. We have cycle option mean restart again after reaching maximum or minimum.

Now we can use it with following way

“SELECT NEXT VALUE FOR StudentEnrollmentId”

I hope this might help you somewhere.

Enjoy !!!

Rj !!!

How to disable constraints of a table ? TIP #74

 

You are reading this post just because of two reason

1) You are curious whether it is possible or not and why we require ?

2) You need to disable constraints 

So , let me share here that you can disable constraints at anytime of a table.

Sometime it is possible when you are doing bulk insert or you need to insert values in column and for this you need to disable constraints.

You can disable all the constraints of a table using following command

ALTER TABLE TABLENAME NOCHECK CONSTRAINT ALL ;

If you want to disable a specific constraint of a table then you can use following syntax

ALTER TABLE TABLENAME NOCHECK CONSTRAINT_NAME

For example suppose you want to disable all the constraints of  student table then you can write following syntax

ALTER TABLE dbo.Students NOCHECK ALL;

Below is very live example in Indian scenario

Suppose you added a check marks in last class more than 45% then only add student now due to some out side pressure you want to give admission to a student who has 40% percent then you need to disable percentage check.

ALTER TABLE dbo.Student NOCHECK chk_Student_Percentage_40

I hope this might help you somewhere.

Thanks

Rj!!!

Build 2014–2nd–4th April

Dear all,

Its back enjoy build on 2nd & 4th April. Although I am very interested to join and see this great event live at San Francisco but unfortunately not able to go there.

I will enjoy video

You can find more detail with below link

http://www.buildwindows.com/

So enjoy learning

Regards,

Rajat Jaiswal

Check Best Practices with SQL SERVER with Best Practices Analyzer (BPA)

Hello Friends,
We always heard that we should adopt Best Practices in SQL Server and most of the time we tried our best to adopt best practices in our work but the problem is who will analyze the best practices which we did for our database ,SQL Server.
Hmm, that’s the point my dear friends but don’t worry on this part also because Microsoft introduce BPA (Best Practice Analyzer).
I tried this tool recently it is easy to use. It scans your database, SQL SERVER and provides you the best way to adopt Best Practices with your current structure.
You can download the installer of this tool from http://www.microsoft.com/downloads/en/details.aspx?FamilyId=da0531e4-e94c-4991-82fa-f0e3fbd05e63
After install this BPA utility you can easily scan your SQL SERVER, Databases with this.

The BPA will provide you report of scan. The scan report will give you idea about what is missing, what need to do?
Below are some listed options which BPA raised after scan report
1) Backup is latest or is it out dated?
2) Database having any anomalies.
3) Database integrity check required or not?
4) Check Disk IO delay problem
5) Backup should be on separate volume
6) Bulletin\Administrators in sysAdmin Role
7) Set Page verify option
8) SQL Login Password policy violation
9) Database is not in full recovery mode
Although some of the warning in the above list which we can ignore according to our business need.
And many more issues to make SQL Server database robust just check below report snap.

As per my opinion the tool is good but need some more functionality or utility to manage and apply
Best practices. So why you people waiting just download, install and use.
Make your database robust with best practices.


Enjoy Best Practices.

Thanks & Regards
Rajat Jaiswal