A myth about view TIP #102

 

I don’t know why every interviewer’s favorite question “Can we insert record using View ?” If you say Yes/No the interviewer will roaming around like so Can you update record using View?  or Can you delete record using view ?

I  hope everyone who is reading this article will be aware of what is view and how to create it ?

If not then need not to worry

“View can be see as a virtual table based on SQL SERVER result or in other world it is a layer above actual data tables” Or we can say when you want to hide actual table then you create a view wrapper”

You can easily create a View with following syntax

“CREATE VIEW

AS

QUERY “

Let’s understand this by an example.

Suppose in I have a database with name “IndiandotnetDB” which has a  table “tblStudentSource”

Now I created a  view just for fetching records from tblStudentSource

 

CREATE VIEW StudentSourceView
AS
SELECT StudentId,
       FirstName,
       LastName,
       Course,
       Detail
FROM tblStudentSource
Go

Now you can fetch records directly from  View as shown in below

SELECT * FROM StudentSourceView

You will get all the records from tblStudentSourceView

Now the Question “ Can you Insert record from View ?“

So the answer is specific condition you can.

In our scenario we will write following command and execute so the record will be added

So you are clear that we can Insert records from View.

In similar fashion we can update the records as shown below figure

We will following SQL statement as showing in figure in which we are going to update record no 2004 as shown in above figure

As shown record 2004’s FirstValue is updated to value “Updated”

 

Now in similar way we can delete the record using View.

Although there are certain other factor due to which “Insert/update/delete” is possible.

like we have only simple schema.

I will describe this later with more detail like  scenario where  we can not Insert/Uppdate/delete using view.

Till than Enjoy !!!

 

Thanks

RJ

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!!

Find all the dates in a date range ? TIP #100

 

It’s almost one month that I didn’t write anything on the blog due to some personal reason. I am really sorry for that.

Now , Lets talk about the scenario sometimes we need to generate a report of total sales in particular date range but the condition is you need to show all the dates whether there was any sales or not.

So first and most important thing for us is to determine all the dates between that particular date range and then determine total sales date wise.

To determine the all the dates which reside between from date & to date  we have 2 approches

First the classic approach with while loop  as shown below

DECLARE @StartDate AS DATE = '2005-07-01'
DECLARE @EndDate   AS DATE = '2005-07-29'
DECLARE @tblDateRange AS TABLE (salesDate DATE)
DECLARE @SeedDate AS DATE
SET @SeedDate = '2005-07-01'
WHILE @SeedDate <= @EndDate
BEGIN
  INSERT INTO @tblDateRange(salesDate) Values (@SeedDate)
  SET @SeedDate  = DATEADD(d,1,@seedDate)
END
SELECT * FROM @tblDateRange

Now second and interesting approach

DECLARE @StartDate AS DATE = '2005-07-01'
DECLARE @EndDate   AS DATE = '2005-07-29'
DECLARE @tblDateRange AS TABLE (salesDate DATE)

;WITH DatesCTE
AS (
SELECT @StartDate AS SalesDate
UNION ALL
SELECT DATEADD(d,1, SalesDate) As salesDate
FROM DatesCTE
WHERE DATEADD(d,1,SalesDate) <= @EndDate)

INSERT INTO @tblDateRange(salesDate)
SELECT * FROM DatesCTE

SELECT * FROM @tblDateRange

These are the 2 simple approaches which I like. I appreciate if you share other approaches which are simple & interesting.

Thanks

RJ

Enjoy !!!

How to hide my SQL Server instance in network ? TIP #99

 

In TIP #70  we saw how to find all the running SQL SERVER instance in a network or a machine.

to revise see below image.

This tip is just opposite to tip #70 you don’t want that your co-worker see your SQL Server instance running on your machine machine. (There are several reason behind this   and security is one of the most valuable aspects)

To achieve this you just need to do a very simple setting. Just follow below steps

1) Open “SQL SERVER Configuration Manager”

2) Once the screen is open right click on the instance which you want to hide from network (under  SQL SERVER network  configuration ) as shown below

3) When you click on Properties menu you will get a new screen as shown below

You need to set the value of Hide Instance option to Yes.

4) Click on apply button and restart the services.

Great , We achieved it. Isn’t it simple ?

I appreciate your feedback.

Enjoy!!!

RJ!!!

I am not able to access my SQL server instance on another computer tip #98

 

On last Friday , one of my colleagues had some issue related to accessing of SQL Server instance of database server machine in the office through his machine. I think this is generalized problem and might be faced by many of us.

So to begin with  default port of SQL SERVER is 1433 , I am sure most of us aware of this.

If we are facing such issue then most of the time it means port 1433 is not opened on SQL SERVER instance machine.

To open the port 1433  we have to follow below steps 

1) Open firewall (using Firewall.cpl command ) as shown in below figure

2) You will get below screen now click on Advance setting option as shown in below screen

3) You will get a new screen. In this we need to add a new rule for for “Inbound” & “outbound” by clicking the respective navigation menu. When you will click on “Inbound rule or outbound rule you will below screen. Now click New Rule link as highlighted below

4) When you click the New rule link you will get a new screen. Now in this screen select port radio button and click on next button 

5)Now add specific local port 1433 as shown in below figure 

When you click next button you need to choose option “Allow the connection” and you are good to go by click Next button

 

These same steps you have to follow for Inbound & outbound rules and your SQL port 1433 will be open and you will be able to access your sql server instance from other computer machine.

I hope this tips might resolve your problem too.

Enjoy!!!!

RJ!!!

How to send E-mail/E-mail with Attachments in SQL Server TIP #97

 

In last post, TIP#96 We have configured Database E-mail. Now in this post we will see how to send mail in SQL SERVER.

Sometimes we need to send mail for different requirements like

1) Notification mails like  data inserted /updated/deleted successfully

2)Send data reports like no of amount earned, failed transaction  etc.

for this SQL SERVER provides us stored procedure “sp_send_dbMail”.

The “SP_send_dbmail” has various parameters below are some important parameters like

@profile_Name : The profile which will use to send mail

@Recipients : To whom the mails need to be send

@body : This is message body

@Subject: Subject of the mail

@Query : SQL statement which you want to share

To send a simple E-mail  we can write following statements

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'IndiandotnetMailSmtp',
    @recipients = 'rajatjai@gmail.com',
    @body = 'Mail sent successfully.',
    @subject = 'Mail via SQL SERVER ' ;

We can also send a query result either inline text format or html format or as a  attachment

Below query will send mail to me with count of students of a class. the below mail is simple text format mail

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'IndiandotnetMailSmtp',
    @recipients = 'rajatjai@gmail.com',
    @query = 'SELECT COUNT(1), Class FROM IndiandotnetDB.dbo.tblStudent GROUP BY class' ,
    @subject = 'Mail via SQL SERVER ' ;

The above mail can also be send mail as a attachment with following command

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'IndiandotnetMailSmtp',
    @recipients = 'rajatjai@gmail.com',
    @query = 'SELECT COUNT(1), Class FROM IndiandotnetDB.dbo.tblStudent GROUP BY class' ,
    @subject = 'Mail via SQL SERVER ' ,
    @attach_query_result_as_file = 1 ;

We can add @body_format = 'HTML' and use various HTML tag in query or body parameters

A part from this you can cross check E-mail status whether mail is sent or not if it is not sent the what is the reason.

Below are the statements which can help to cross check mail sent status

-- Show all the emails

SELECT * FROM msdb.dbo.sysmail_allitems

-- Show all the  sent mails
SELECT * FROM msdb.dbo.sysmail_sentitems

-- show all the un sent mails
SELECT * FROM msdb.dbo.sysmail_unsentitems

-- show all the failed mail with reason
SELECT * FROM msdb.dbo.sysmail_faileditems

I hope this might help you somewhere.

Enjoy!!

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