How simple we can send Mail using SQL Server

Hi All ,

We can send email through SQL Server also,for this follow some simple steps.

Step 1:
          Open SQL Server Management Studio and then connect to server and go to  management tab in that right click on Database Mail then click on configure mail,then click on setup database mail then click on next.



Step 2:
           Now u can give profile name for the email and create new SMTP account , for that click on add and click on new account fill accordingly as shown in below image.

Step 3:
           Now manage profile Security and select u r's profile then click next and next and finsh.

Step 4:

           Now test the mail ,for that again right click on Database Mail and select Send Test E-Mail .and u will get email.

Thanks For Visiting Blog.

HOW TO USE CACHE TRANSFORM

            

           HOW EFFECTIVELY WE CAN USE CACHE TRANSFORM IN SSIS

Scenario:

• While using look up transformation, ur pointing to look up table which is available in database for checking the source data table.
• If u r checking for single source data table there is no problem because it only one time hit the database for getting the data from lookup table.
• But in real time scenario u may use more than one source table to look up on one single lookup table, in this situation database hits will increase(that many times it will hit the database for lookup table) and performance will automatically decreases.

To overcome this situation I used cache transformation in SSIS Data flow task.

Explanation:

Step 1:

           Drag and drop data flow task in control flow, open data flow and add oledb source and make all the database connection then choose the look up table from that database.

Step 2:

           Now add cache transformation to data flow and map oledb to cache and configure the cache connection manager like below.

        Double click on cache transformation and check the use file cache then select the .caw (cache file) file (for getting this .caw file open notepad and save as .caw extension.).

      After that go to column tab in cache connection manager to select one index column.

Step 3:

           Now add another data flow task in control flow and then open data flow and add oledb source for getting the source table. Add look up transformation to data flow and then map source table to look up then open look up and in cache mode select full cache then in connection type use cache connection manager.

Step 4:

           Now go to connection tab in look up and select that cache connection manager (already created connection to .caw file).

     

     Then go to columns tab and map on which column u r going to look up.

Then click on OK.

Now u can redirect the rows (matched and not matched) to desired destinations.

Finally:

             U can use same cache file so many times u want to look up with source tables so that it will hit the database only single time. 

In this above screen shot u can find the three different sources but having the one look up file so this reducing the database hits ,and increases the performance. 

THANKS FOR VISITING BLOG

FINDING TOP Nth RECORD FROM TABLE WITH OUT CHANGING THE ORDER OF RECORDS INSERTED

HI ALL,

FINDING TOP Nth RECORD FROM TABLE WITH OUT CHANGING THE ORDER OF RECORDS INSERTED (how they inserted in table same way i needed)

I already posted row_number() in my blog to get row number for each record using that logic with the help of sub query i am getting the result.

DATA:

Declare @test table(id int,name varchar(30))
insert into @test values(589,'ravi')
insert into @test values(689,'ramu')
insert into @test values(789,'rahul')
insert into @test values(525,'raghu')
insert into @test values(575,'ramesh')
insert into @test values(532,'ragav')
insert into @test values(556,'raju')
insert into @test values(500,'rita')
insert into @test values(501,'rama')
insert into @test values(505,'RIJJU')--10 Record while inserting the data to table
insert into @test values(589,'rajaini')
insert into @test values(456,'ranjith')
insert into @test values(123,'ramana')
select * from @test

QUERY:
SELECT S.* FROM(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 0))AS RID,ID,NAME FROM @TEST)S WHERE S.RID=10

THANKS FOR VISITING BLOG :)

USING SUB QUERY OR CTE

HI ALL 

I AM HAVING THE REQUIREMENT LIKE CUSTOMER VISITING THE STORE SO MANY TIMES ,

THE MANAGER WANT TO KNOW THE EACH CUSTOMER HOW MANY TIMES VISITED THE STORE AND HOW MUCH AMOUNT HE SPENT ON THE GOODS 

WE CAN GET THE COUNT OF EACH CUSTOMER USING AGGREGATE FUNCTION BUT WE NEED TO SHOW THE COUNT OF EACH CUSTOMER BESIDE THE AMOUNT COLUMN.

FOR THIS I USED SUBQUERY OR CTE TO GET THIS REQUIREMENT

FIND THE BELOW SCRIPT FOR GETTING THIS RESULT

DECLARE @SUBQUERY TABLE(CUSTOMERID INT,AMOUNT INT)

INSERT INTO @SUBQUERY VALUES(1,500)

INSERT INTO @SUBQUERY VALUES(1,600)

INSERT INTO @SUBQUERY VALUES(1,700)

INSERT INTO @SUBQUERY VALUES(1,800)

INSERT INTO @SUBQUERY VALUES(2,500)

INSERT INTO @SUBQUERY VALUES(2,600)

INSERT INTO @SUBQUERY VALUES(3,700)

INSERT INTO @SUBQUERY VALUES(3,800)

INSERT INTO @SUBQUERY VALUES(4,700)

INSERT INTO @SUBQUERY VALUES(4,900)

INSERT INTO @SUBQUERY VALUES(4,200)

INSERT INTO @SUBQUERY VALUES(4,100)

INSERT INTO @SUBQUERY VALUES(4,300)

INSERT INTO @SUBQUERY VALUES(4,400)

--AGGREGATE QUERY:

SELECT CUSTOMERID,count(CUSTOMERID) AS NUMBEROFTIMESVISITED FROM @SUBQUERY GROUP BY CUSTOMERID

--THIS IS USING SUBQUERY:

SELECT MA.*,SUB.NUMBEROFTIMESVISITED FROM (SELECT CUSTOMERID,count(CUSTOMERID) AS NUMBEROFTIMESVISITED FROM @SUBQUERY GROUP BY CUSTOMERID)SUB

JOIN @SUBQUERY MA ON SUB.CUSTOMERID=MA.CUSTOMERID 

--THIS IS USING CTE:

;WITH CTE AS (SELECT CUSTOMERID,count(CUSTOMERID) AS NUMBEROFTIMESVISITED FROM @SUBQUERY GROUP BY CUSTOMERID)

SELECT MA.*,CTE.NUMBEROFTIMESVISITED 

FROM CTE

JOIN @SUBQUERY MA ON CTE.CUSTOMERID=MA.CUSTOMERID 

THANKS FOR VISITING BLOG

Hi ALL

To day i am going to use the Replicate.
I am having the requirement like i need to genrate a pattern(employeeNumber) ,for this pattern we need to use two columns id and department id

Output:

        If  id is 1 and departmentid is 1 then i need out put like YH0010001

Rules:

1) first two characters(YH) are common for everyone.
2) next three digits should be department number(it having max three digit only ,if it is 1 u need to represent          as 001,if it is 2 then 002 ,if it is 23 then 023, if it 333 then 333 )
3) next four digits are id of the employee(it having max four digit only ,if it is 1 u need to represent as 0001,if it is 2 then 0002 ,if it is 23 then 0023, if it 333 then 0333 )

If id lenth is less than than the four digits then u need to keep Zero's
If Department number lenth is less than the three digits then u need to keep Zero's

so follow the above conditions and write a SQL.

DATA:

DECLARE @EMPLOYEE TABLE (ID VARCHAR(30),EMPNAME VARCHAR(30),DEPTID VARCHAR(30))
INSERT INTO @EMPLOYEE VALUES('1','SANTOSH','1')
INSERT INTO @EMPLOYEE VALUES('2','SUDEEP','2')
INSERT INTO @EMPLOYEE VALUES('33','KISHOR','12')
INSERT INTO @EMPLOYEE VALUES('444','PHANI','333')
INSERT INTO @EMPLOYEE VALUES('12','GAURAV','1')
INSERT INTO @EMPLOYEE VALUES('222','PRADEEP','2')
INSERT INTO @EMPLOYEE VALUES('4444','KRISHNA','3')


SELECT * FROM @EMPLOYEE

I NEED SECOND RESULT SET......


FINAL QUERY:


SELECT *,'YH'+REPLICATE('0',3-LEN(DEPTID))+DEPTID+REPLICATE('0',4-LEN(ID))+ID AS EMPLOYEEID FROM @EMPLOYEE

Explanation: 
    
for getting this result i used replicate which is a string function using this u can repeat the value for how many time s u want ....



THANKS FOR VISITING BLOG :) 

Validating Credit or Debit Card Using Small SQL Query

Hi All

Validating Credit or Debit Card using SQL


I have only the Expiry Date Of Credit/Debit Card in String formate like '2012-09' .

If i pass this as input and i need to get only the valid cards data.

declare @exptbl table(id int,expdate varchar(50))
insert into @exptbl values(1,'2012-09')
insert into @exptbl values(2,'2012-02')
insert into @exptbl values(3,'2012-05')
insert into @exptbl values(4,'2013-06')
insert into @exptbl values(5,'2013-07')

If u query the table variable you will get below out put ,according to our requirement  i  need to get only Marked Records.

I want Below output :

For Getting above output please use below query:

SELECT * FROM @EXPTBL

WHERE CONVERT(DATETIME,EXPDATE+'-01')>=CONVERT(DATETIME,CONVERT(VARCHAR(50),DATEPART(YYYY,GETDATE()))+'-'+CONVERT(VARCHAR(50),DATEPART(MM,GETDATE()))+'-01')

Thanks for Visiting the Blog

Santosh 

  

Getting Weather Report Using SSIS

Getting Weather report using SSIS through WEB Services.

URLS:

 Weather Web Service URL: http://www.webservicex.net/globalweather.asmx?WSDL

 WSDL File Data URL: http://www.webservicex.com/globalweather.asmx?wsdl 

For this Follow Below Simple Steps:

1) Create New SSIS Package.

2) In Control flow Drag the Web Service Task and configure the Connection. For that Double Click on Web service task u will get below screen

3) Click on HttpConnection u will Get  below Screen and Paste the Weather Web Service URL(URL Located in top) and click on Test Connection then click ok.

4) Next Click on WSDL file , for that first copy XML Data from Second URL( http://www.webservicex.com/globalweather.asmx?wsdl ) and paste in notepad and save as .WSDL  and map that file here.

 5) After adding the WSDL file goto Input tab and Select the Service,Method and give the input values as Variable(Create variables).Follow the below screen.

6) And finally Store the Weather Report in Variable.

7) Now Add Execute SQL Task in Control Flow and connect to the data base for getting the City and Country information from table (Find the table and data Script below) and paste this (Select City,Country  from CityandCountry where id=?) in SQLStatement.

8) Now Goto Prarmeter Mapping Tab and create a int type variable(Follow Below screen).

9) Next Goto Result Set tab and Map the Result to City and Country Variables.

10) Drag one more Execute SQL Task this is for Storing the data to Data base tables(Find the table Script below) and paste this ( INSERT INTO [dbo].[WeatherReport]

           ([Id]

           ,[City]

           ,[Country]

           ,[WeatherReport])

values(?,?,?,?) ) in SQLStatement(Follow the below screen).

11) And Goto Parameter Mapping and Again map all the variables(Follow Below screen).

12) And Finally Add ForLooooP to Control Flow and Configure this like Below.

13) And Execute the Package and check the Database table, u will found the XML formated Weather report. from that u can get the Weather Report Info.

Scrip for Tables:

CREATE TABLE [dbo].[CityandCountry](

[ID] [int] NOT NULL,

[City] [varchar](50) NULL,

[Country] [varchar](50) NULL

) ON [PRIMARY]

GO

CREATE TABLE [dbo].[WeatherReport](

[Id] [int] NOT NULL,

[City] [varchar](50) NULL,

[Country] [varchar](50) NULL,

[WeatherReport] [varchar](max) NULL,

[DateOfReport] [datetime] NULL

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

INSERT [dbo].[CityandCountry] ([ID], [City], [Country]) VALUES (1, N'Hyderabad', N'INDIA')

GO

INSERT [dbo].[CityandCountry] ([ID], [City], [Country]) VALUES (2, N'Delhi', N'INDIA')

GO

ALTER TABLE [dbo].[WeatherReport] ADD  CONSTRAINT [DF_WeatherReport_DateOfReport]  DEFAULT (getdate()) FOR [DateOfReport]

GO

Thanks For Visiting the Blog

Santosh 

How To Get The Row Number In Insert Order Only (Dnt Change The Order Of Data In The Table ) As New Cloumn

CREATE TABLE [dbo].[Rownumber](
[Name] [varchar](50) NULL
) ON [PRIMARY]

GO

INSERT [dbo].[Rownumber] ([Name]) VALUES ('Gaurav')
GO
INSERT [dbo].[Rownumber] ([Name]) VALUES ('Sudeep')
GO
INSERT [dbo].[Rownumber] ([Name]) VALUES ('Kishor')
GO
INSERT [dbo].[Rownumber] ([Name]) VALUES ('Santosh')
GO

I DNT WANT ABOVE OUT PUT, I WANT BELOW OUT PUT.....LETS TRY THIS

This is the Query for this output:

SELECT  Name,

                 ROW_NUMBER() OVER(ORDER BY (SELECT 0)) AS InsertOrderRowNumber

FROM     Rownumber