Author Archives: Biswajeet

About Biswajeet

Biswajeet is my Name, Success is my Aim and Challenge is my Game. Risk & Riding is my Passion and Hard Work is my Occupation. Love is my Friend, Perfection is my Habit and Smartness is my Style. Smiling is my Hobby, Politeness is my Policy and Confidence is my Power.

Creating Job in SQL Server 2008

SQL Server Job:
A job is a specified series of actions that SQL Server Agent performs. Its internal setup consists of a windows service, that is used to execute jobs and a set of SQL Server tables that has the metadata about these jobs. We use jobs to define an administrative task that can be run one or more times and monitored for success or failure. If the Job encounters any problem, SQL Server Agent can record the event and notify you. A job can run on one local server or on multiple remote servers. In this article, I will demonstrate how to create a SQL Server Job.

Note: Before create a SQL Server Job, make sure that your SQL Server Agent is running.

In below example I have a database named “MyTestDB”. I want to backup this database from server, from Monday to Friday on a specific time. So here using SQL Server Agent Job I can automate this task. Not only for database backup, But we can automate a numerous types of task using SQL Server Agent Job.

Steps To Create SQL Server Job:

Step 1:
Click on SQL Server agent and right-click on “Jobs” and click on “New Job”.

1

Step 2:
There are many steps to create a Job, so let’s go one by one. In the first Step “General” provide a job name and description and select the database owner and click “Ok”.

2

Step 3:
Now click on second Step “Steps”. Click on the “New” button for a new step and provide a step name, type and select database and write a command to save in a location and then click “Ok”.

Note: In the command area, you can write any SQL query or stored procedures according to your requirement for execute in this job.

3

4

Step 4:
Now click on the third step “Schedules”. Click on the “New” button for a new schedule and provide name, schedule type, frequency and click “Ok”.

Note: For this example my job schedule will be weekly at 1 AM.

5

6

Step 5:
Now click on the fourth step “Alerts”. Click on the “Add” button and provide alert name, database name and click “Ok”.

Note: An alert is an automatic response to a specific event. For example, an event can be a job that starts or system resources that reach a specific threshold. You define the conditions under which an alert occurs.

7

8

Step 6:
Now click on the fifth step “Notifications” and select how you want notifications. Here you have option for sending email when job complete or it runs successfully or it fails. Chose the appropriate option as per your requirement and click “Ok”.

9

Step 7:
The final step is the “Targets”, that shows you the options of the target server, you can also select multiple target servers.

10

And here the new job has created in SQL Server Agent.

11

Convert 15 digit Id to 18 digit Id in Salesforce apex class

In Salesforce each record Id represents a unique record within an Organisation.
These ids are in two formats for every record Id in Salesforce:

  • 15 digit case-sensitive version which is referenced in the UI (Detail pages / reports)
  • 18 digit case-insensitive version which is referenced through the API

Apex Class:

public class TestPage
{
    public String FifteenDigit {get;set;}
    public Id EighteenDigit {get;set;}
     
    public TestPage(){
        FifteenDigit = 'a0390000009ooJG';
        EighteenDigit = FifteenDigit;
    }
}

Visualforce Page:

<apex:page controller="TestPage" doctype="html-5.0">
   <apex:form>
       <apex:outputtext value="Fifteen Digit : "></apex:outputtext>
       <apex:outputtext value="{!FifteenDigit}"></apex:outputtext>
       <apex:outputtext value="Eighteen Digit : "></apex:outputtext>
       <apex:outputtext value="{!EighteenDigit}"></apex:outputtext>
   </apex:form>
</apex:page>

download

Note: CASESAFEID(Id) function in a formula field can be used to capture the 18 digit id of a record.

How to split a comma delimited string in SQL Server

In this article I’ll demonstrate how to split a comma delimited string in SQL Server.
There can be different types of delimiters like Comma ‘ , ‘, vertical bar ‘ | ‘, Single space or a Tab.
Here in below example i’m using Comma ‘ , ‘ delimiter.

Here is sample table:

download (1)

And here is the expected output:

download (2)

Create a sample table and insert data:

CREATE TABLE Test 
( 
   Id INT, 
   Country VARCHAR(200) 
) 
GO 
 
-- Insert Sample Data 
INSERT INTO Test SELECT
1, 'India, USA, UK' UNION ALL SELECT
2, 'Singapore, Dubai, Japan'  UNION ALL SELECT
3, 'Vietnam, South Africa, Ireland'
GO
 
-- Verify the Sample Data of Test Table
SELECT Id, Country FROM Test

Here is the query for How to split a comma delimited string:

;WITH cde AS
 
( 
    SELECT
        Id, 
        CAST('<m>' + REPLACE(Country,  ',' , '</m><m>') + '</m>' AS XML) AS Country
    FROM Test 
) 
SELECT
    Id, 
    Split.a.value('.', 'VARCHAR(100)') AS Country
FROM cde
CROSS APPLY Names.nodes('/M') Split(a)

Difference between multiple messaging components in Visualforce Page?

Difference between multiple messaging components in Visualforce Page

  • It is used for display a message for a specific component, such as a warning or error.
    <apex:message></apex:message>
    
  • It is used for display all messages for all components on the current page, such as a warning or error.
    <apex:messages></apex:messages>
    
  • It is used for displaying custom messages in the visualforce page using the Salesforce pattern for errors, warnings and other types of messages for a given severity.
    <apex:pagemessage></apex:pagemessage>
    
  • It is used for displays all the messages that were generated for all components on the current visualforce page, presented using the Salesforce styling.
    <apex:pagemessages></apex:pagemessages>
    

Apex Message in Visualforce Pages

Apex Messages in Salesforce are used for displaying messages such as a warning or an error in visualforce page. In this article I’ll demonstrate how to use different types of messaging options in VisualForce page.
Apex message severities are:

  • CONFIRM
  • ERROR
  • FATAL
  • INFO
  • WARNING

CONFIRM:

Visualforce Page:

<apex:page controller="SuccessMessage"> 
<apex:pagemessages> 
</apex:pagemessages> 
</apex:page> 

Apex Class:

public class SuccessMessage
{ 
   public SuccessMessage()
   {
      ApexPages.addMessage(new ApexPages.Message(ApexPages.severity.Confirm, 'This is a success message'));
   }
} 

Confirm

 

ERROR:

Visulaforce Page:

<apex:page controller="ErrorMessages"> 
<apex:pagemessages> 
</apex:pagemessages> 
</apex:page> 

Apex Class:

public class ErrorMessages
{ 
   public ErrorMessages()
   {
     ApexPages.addMessage(new ApexPages.Message(ApexPages.severity.Error, 'This is an error message'));
   }
}

Error

 

WARNING:

Visualforce Page:

<apex:page controller="WarningMessage"> 
<apex:pagemessages> 
</apex:pagemessages> 
</apex:page>

Apex Class:

public class WarningMessage
{ 
   public WarningMessage()
   {
      ApexPages.addMessage(new ApexPages.Message(ApexPages.severity.Warning, 'This is a warning message'));
   }
} 

Warning

 

INFO:

Visualforce Page:

<apex:page controller="InfoMessage"> 
<apex:pagemessages> 
</apex:pagemessages> 
</apex:page>

Apex Class:

public class InfoMessage
{ 
   public InfoMessage()
   {
      ApexPages.addMessage(new ApexPages.Message(ApexPages.severity.Info, 'This is an informational message'));
   }
}

Info