SQL Server – Search complete database for value

Search complete Database in SQL Server for some value

Recently, I was in need to search complete Database for some value and found below very useful script (T-SQL)

DECLARE @SearchStr nvarchar(100) = 'SEARCHSTRING'
DECLARE @Results TABLE (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET  @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')


WHILE @TableName IS NOT NULL

BEGIN
    SET @ColumnName = ''
	BEGIN TRY
    SET @TableName = 
    (
        SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
        FROM     INFORMATION_SCHEMA.TABLES
        WHERE         TABLE_TYPE = 'BASE TABLE'
            AND    QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
            AND    OBJECTPROPERTY(
                    OBJECT_ID(
                        QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
                         ), 'IsMSShipped'
                           ) = 0
    )
END TRY
BEGIN CATCH
END CATCH
    WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)

    BEGIN
        SET @ColumnName =
        (
            SELECT MIN(QUOTENAME(COLUMN_NAME))
            FROM     INFORMATION_SCHEMA.COLUMNS
            WHERE         TABLE_SCHEMA    = PARSENAME(@TableName, 2)
                AND    TABLE_NAME    = PARSENAME(@TableName, 1) 
                AND    QUOTENAME(COLUMN_NAME) > @ColumnName 
        )
         

        IF @ColumnName IS NOT NULL

        BEGIN
            INSERT INTO @Results
            EXEC
            (
                'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 4000) 
                FROM ' + @TableName + ' (NOLOCK) ' +
                ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
            )
        END
    END    
END

SELECT ColumnName, ColumnValue FROM @Results

If we run above script for word ‘king’ then output in below format will be displayed Continue reading “SQL Server – Search complete database for value”

SQL Server – Create Folders using T-SQL & OLE automation

Tutorial to create folders in SQL Server using Transact-SQL and OLE Automation Stored Procedures

SQL Server has some standard stored procedures that allow OLE automation.

First step, is to check whether Ole Automation Procedures are enabled in SQL Server or not ? It can be enabled by simply executing below T-SQL commands.

T-SQL to enable Ole Automation Procedures in SQL Server

        sp_configure 'show advanced options', 1;
	GO
	RECONFIGURE;
	GO
	sp_configure 'Ole Automation Procedures', 1;
	GO
	RECONFIGURE;
	GO

Next step is to create a stored procedure, which will use an OLE Automation procedures and create a folder on system drive Continue reading “SQL Server – Create Folders using T-SQL & OLE automation”

Calling Apex method at regular interval from Lightning Component

Importance of $A.getCallback and Modifying Components Outside the Framework Lifecycle

Recently, I was in need of Lightning Component, which can poll Apex class at regular interval and display status of some process running in background. Initially, I thought its very easy and straight forward. Just like we do in Visualforce, use window.setInterval method of Javascript and I came up with below code. Continue reading “Calling Apex method at regular interval from Lightning Component”

Automated Code review for Apex in Salesforce – Static code analysis – Video

Using open source PMD tool to generate code quality report for Apex classes

PMD is very well known source code analyzer for Java, android and many more languages. Good news for us (Salesforce developers) is , that it supports now Apex. You might be thinking how can we make PMD as part of our daily life ?

There are multiple ways

  1. We can run static code analysis standalone
  2. It can be part of ANT build to generate error reports
  3. Jenkins can use it to generate nice report around code quality
  4. Eclipse can use it as a plugin to generate report

In this blog post, we will discuss option 1, that is running it as a standalone application to generate code quality report.

First Step is to download jar file of latest PMD distribution from here. Continue reading “Automated Code review for Apex in Salesforce – Static code analysis – Video”

Get Selected HTML or Lightning component in Aura Iterator

How to detect event from Lightning Component and HTML5 components in Aura Iterator

Coming from Visualforce background, most of us are well aware about repeater component. Same way, Lightning also offers iterator component.

Detecting Selected Lightning Component in Iterator

Usage is very simple, It is used to iterate over collection and render some HTML / Lightning component dynamically as shown below

<aura:component>
  <aura:iteration items="1,2,3,4,5" var="item">
    <meter value="{!item}"/>
  </aura:iteration>
</aura:component>

In above example, each element in collection can be referred using variable “item”. Most of time we find ourselves in situation where we need to get value or component selected in iterator. As we don’t know upfront, how many items would be there, so identifying selected item at run time seems little bit tricky but believe me it easy. Continue reading “Get Selected HTML or Lightning component in Aura Iterator”

Where to find me in Dreamforce 2016

Author posted by Jitendra on Posted on under category Categories Salesforce and tagged as Tags with Leave a comment on Where to find me in Dreamforce 2016

Find me at Brain Dating , Cognizant Booth , Community Powered Circle Sessions and Admin Meadow in Dreamforce 2016

Hey All,

Its time of year again where we will get a chance to meet personally in virtually connected world. I know most of you either on Facebook, Twitter or Youtube, let’s plan to connect this Dreamforce.

  • My Session in Dreamforce
    My Session in Dreamforce
  • MVP's in Dreamforce
    MVP's in Dreamforce
  • Sassy in Dreamforce
    Sassy in Dreamforce
  • Meeting with Friends
    Meeting with Friends
  • Parker Harris in Lightning Suit
    Parker Harris in Lightning Suit
  • Do you see my name there ;)
    Do you see my name there 😉
  • Dreamforce Keynote
    Dreamforce Keynote

So, like all previous Dreamforce, this year as well I would be involved in some activities . Question is, where can we meet ? Well, here are some details about me. Please don’t hesitate to comment on this blog about availability, place etc.

Brain Dating

This is a new event in Dreamforce 2016 and I am totally excited about this. We would be exchanging best practices and challenges on pre-decided topic. I would be discussing on two topics Continue reading “Where to find me in Dreamforce 2016”

Use Lightning Component in Visualforce Page

How to use Lightning Out to surface Lightning Component on Visualforce Pages

In all of my projects currently, I have stopped creating Visualforce code. well, thats partially correct ;). I still need Visualforce page because most my clients are still on classic / Aloha Salesforce. However, If you have noticed, all Salesforce release notes, talks, webinars, Dreamforce focuses on Lightning experience. And I can say with confidence that Lightning is really really very cool. Sky is limit to your imagination, You can unleash hidden talent within you. Well, all talk talk… and you may be thinking, how can I start with Lightning Component? My client don’t even want to move away from classic. Its lots of work and money for them.

Well, here is an Idea what I am doing. I still uses Salesforce classic, still uses Visualforce but behind the scene, magic… tadaaa , Lightning Component. I will show walk you through this, how its being done.

We will use existing Flipcard Lightning Component to display on Visualforce page. Once Lightning Component is created, we need to create a Lightning Application to act an as a container. Continue reading “Use Lightning Component in Visualforce Page”

Flipcard Lightning Component

Author posted by Jitendra on Posted on under category Categories Salesforce and tagged as Tags with Leave a comment on Flipcard Lightning Component

A simple CSS based animated Flip card Lightning Component for beginners

There could be situation where we want to display a calculation or summary of some records. This information sometimes needed to be stand out from other piece of data on same page. We may also need component where number is displayed in big text and when cursor is moved, related information explaining calculation should be displayed. It can be achieved in many ways and one of them is to have animated flip card where information can be swapped on cursor hover event.

In this post, we will create a very simple animated Flip card Lightning component. This component will support following properties

  • Animation direction horizontal or Vertical
  • Background color
  • font color
  • border color
  • Text on front size
  • Text on back side

Continue reading “Flipcard Lightning Component”

Considerations and Best Practices before enabling Salesforce Shield Platform Encryption

Best practices and things to consider before rolling out Shield Platform Encryption for your customer

Its been more than a year since launch of platform encryption solution by Salesforce named Shield. Even though its one of most costly offerings by Salesforce however momentum of adaption amongst financial and healthcare industry are very high. I have already implemented platform encryption for few of my clients within a year. In this post, I will share some of my learning and best practices around rolling out platform shield to customers.

First question to ask customer before going with Platform encryption is, “Which security threat customer trying to solve” ?

If answer is security from internal Salesforce users then unfortunately shield is not an answer. We can leverage OWD, sharing rules, profiles, FLS to set security for Salesforce internal users.

If answer from customer is – compliance, security at database and data center level then Shield is a way to go to solve security issue. Continue reading “Considerations and Best Practices before enabling Salesforce Shield Platform Encryption”

Send Visualforce as an email attachment from Apex Trigger – Alternate design

Process Builder & InvocableMethod- Alternative to send Visualforce as an email attachment from Apex Trigger

Previously I had written same blog post and it was working fine before Winter 16. However, after Winter 16 critical update was released by Salesforce to consider getContent() method as a callout. If this update is enabled in your Salesforce instance then my previous blog post will not work.

In this blog post, we will go through alternate design, where we would still be able to send Visualforce page content as an Email attachment. Difference is, instead of Apex Trigger , Invocable Method and Process builder will help us getting there.

Again, consider below simple Visualforce page, which we want to send as an attachment.

Continue reading “Send Visualforce as an email attachment from Apex Trigger – Alternate design”