Query all Stored Procedure in SQL

This will be useful if you want to list down all Stored Procedure names in a database.


SELECT
*
FROM DatabaseName.information_schema.routines
WHERE routine_type = ‘PROCEDURE’
ORDER BY SPECIFIC_NAME ASC;

If you want to filter the list of SP (not including those with sp_, xp_, dt_, ms_ etc…)


SELECT
SPECIFIC_NAME,
‘sp_helptext ‘ + SPECIFIC_NAME AS ‘QueryStmt’
FROM Forex.information_schema.routines
WHERE routine_type = ‘PROCEDURE’
AND LEFT(Routine_Name, 3) NOT IN (‘sp_’, ‘xp_’, ‘ms_’, ‘dt_’)
ORDER BY SPECIFIC_NAME ASC;

Unable to find valid certification path to requested target

Does your client application getting this error?

javax.net.ssl.SSLHandshakeException: sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target

Root Cause: It means that the web server or the URL you are trying to connect to does not have a valid certificate from an authorized CA.

Solution: Import the server certificate and install it in your JDK’s keystore.

Below are the steps:

  1. Download the server certificate
  •  Copy the URL that you are connecting and open it in Google Chrome. Click the secured icon (green padlock) and then click Certificate Information

1_unable

  • From the certificate window, select Details tab then click Copy to File..

2_unable

  • Click Next

3_unable

  • Select DER encoded binary X.509 (.CER) then click Next

4_unable

  • Set the folder path where you want to save the certificate and certificate name then click Next afterwards

Eg. D:\cert\certificate.cer

5_unable

 

  • Click Finish

6_unable

Check the saved certificate file

7_unable

  1. Import the server certificate to JDK
  • Run command prompt as Administrator, then navigate to bin folder of the JRE

For this case, the path is c:\Program Files (86)\Java\jre6\bin. This may differ depends  on the what Java version are you using

8_unable

  • Use keytool to manage certificate. This is a command-line utility with numerous arguments that allow you to create and manage keystores for housing digital certificates.

To list down the current certificates contained within keystore, type the following in the command prompt then enter keystore password :changeit

You will see the number of keystores contains

keytool -list -keystore ..\lib\security\cacerts

9_unable

  • Now, import your server certificate by typing the following command

keytool -import -alias myalias -keystore ..\lib\security\cacerts -file D:\folder\certificate.cer

Eg. keytool -import -alias iphoneftmobile -keystore ..\lib\security\cacerts -file D:\sas\cert\iphoneftcert.cer

10_unable

  • Type “yes” in “Trust this certificate?” Then press enter key

11_unable

 

  • Check again the list of certificates contained in the keystore using this command

keytool -list -keystore ..\lib\security\cacerts

12_unable

  • Run again the client app and see if it able to connect to the URL

How to limit EditText to two decimal places

< EditText android:id=”@+id/eWithdrawAmount” android:layout_width=”match_parent” android:layout_height=”wrap_content” android:layout_marginTop=”4dp” android:ems=”10″ android:inputType=”numberDecimal” >

etInput= (EditText) v.findViewById(R.id.etInput);

etInput.addTextChangedListener(new TextWatcher () {

@Override
public void beforeTextChanged(CharSequence s, int start, int count, int after) {

}

@Override
public void onTextChanged(CharSequence s, int start, int before, int count) {

}

@Override
public void afterTextChanged(Editable edt) {

String temp = edt . toString ();
int posDot = temp . indexOf ( “.” );

if (posDot <= 0) { return ; } if (temp . length () – posDot – 1 > 2) {
edt . delete(posDot + 3, posDot + 4);
}
}
});

Create table backup in SQL

There are times that we need to play around the table in our database. But before mess it up, better to backup it first.

Usually, this is how i do it before:

1. Use the auto generated script to create the main table.
2. Update the script and rename it with backup table name. Execute the script.
3. Create insert script to insert all records from main to backup table.

I wasn’t aware that there was a short way to do this, just executing this script

SELECT
     * INTO
dbo.BackupTableName
FROM dbo.TableName;



One day, one script at a time 🙂

An unhandled exception occurred in the Silverlight Application when creating site in SharePoint 2010

Have you experienced this in SharePoint 2010 when creating a site, document library, list etc. and then when you hit the “Create” button this message shows?

1-UnhandledExceptionInSilverLight

The initial solution that you might think is to install or re-install the Silverlight to our client PC. But eve you do that, the same issue happens again.

After digging more possible solution in internet, I finally found the answer and hopefully save your time and ass to look for solution.

Solution:

1. Open you SharePoint Central admin site and log-in as administrator.

2. Navigate to Central Administration  > Application Management 

3. Click Manage web applications link.

4. Select the target web application and then click the General settings from the SharePoint Ribbon.

5. From the Web Application General Settings window, scroll down to Web Page Security Validation part. You will see that the Security validation is turned off. This causing the Silverlight not to work properly. Silverlight Application is unable to connect to the WCF endpoint configured by the product for enabling Client Object Model.

2-UnhandledExceptionInSilverLight

6. Switch the selection to On and click OK to apply the changes and to close the window.

3-UnhandledExceptionInSilverLight

7. Now, go back to your SharePoint site and try to create a site, document library etc. You will successfully create the list or library.

4-UnhandledExceptionInSilverLight

Hope this will help. 🙂

 

 

Create Bulk Users in Active Directory using Powershell

Hello World!

I’m working on a project that requires us to setup a portal and make it public which customer (other companies) should able to access from their own local PC or from their own office. To make our life easy, we use SharePoint as based portal and setup some certificate to make it secured site (https) and then make it public using company domain etc….

The challenge here is how customers can access the site and what account they’re going to use?. Well, with the help of an architect, we come up to setup a separate Domain Controller for our customers only and these customers will use their own account created by us.

To make the story short, another challenge came up. This is how to create those accounts knowing there were more than 200+ customer company and each of them might have 100 users or more.

At the start, we manually created an AD account for them for each of the user. Then we feel that its very tedious to do so. I look for any possible solution to make our task easier 🙂 Thank you Mr. Google for the help.
Here is the solution:

  1. Put all your user information to CSV.
  2. Create PowerShell script that will import them to Active Directory.
  3. User account should be created and be added to their specified Active Directory Security Group.
  4. Done 🙂

Simple right? For you to visualize here are the steps.

1. We have list of users with their information like username, first name, surname, title, department, company name etc. In your CSV file, use the first column as header following the Active Directory attribute (to avoid confusion during scripting).  Save the file as “CustomerUser.csv”.
Here is a snapshot.ImageNote:
OrgUnit column was not an attribute in Active Directory. I inserted it there for me to know which Active Directory group (Security Group) where the user should be added.

2. Here is the exciting part.. PowerShell scripting… (I’m not good at it but at least it works). If you are using windows 7 or 8 you can use the editor in All Programs > Accessories > Windows PowerShell > Windows PowerShell ISE.
Create a new file then copy the script below and save it as “CreateUserFromCSV.ps1”.

#Import the PowerShell module containing AD cmdlets
Import-Module ActiveDirectory
write-host “Start Process” “Start Process”
write-host “Start Process” “————————————-“
try
{

#Read the CSV file
$csvPath = “C:\ImportUser\CustomerUser.csv”
$csvData = import-csv $csvPath
write-host “Reading the CSV file……”
#Loop through all items in the CSV items
ForEach ($user In $csvData)
{

$saMAccountName = $user.sAMAccountName
#Check if the User exists
$ADuser = Get-ADUser -LDAPFilter “(sAMAccountName=$saMAccountName)”
If ($ADuser -eq $Null)
{

#Create user using New-ADUser cmdlet
$path = ‘OU=’ + $user.OrgUnit + ‘,OU=Customer,DC=chikchok,DC=net’
$userPrincipalName = $user.sAMAccountName + “@chikchok.net”
New-ADUser -Name $user.cn
-SamAccountName $sAMAccountName
-UserPrincipalName $userPrincipalName
-GivenName $user.givenname
-Surname $user.sn
-DisplayName $user.displayName
-EmailAddress $user.mail
-Company $user.company
-Title $user.title
-Department $user.department
-AccountPassword (ConvertTo-SecureString “p@ssw0rd” -AsPlainText -Force)
-PasswordNeverExpires $true
-Path $path
-Enabled $true

}
else
{

write-host “- ” $user.sAMAccountName “|Account Exists” -ForegroundColor yellow

}

}

}
catch
{

write-host “Start Process” “Error: ” $($_.CategoryInfo) -ForegroundColor red
write-host “Start Process” “Message: ” $($_.Exception.Message) -ForegroundColor red

}
write-host “Start Process” “————————————-“
write-host “Start Process” “End Process”


3. Copy both the csv file and PowerShell script into your AD server. I created a folder name “ImportUser” in drive C.

CopyCSVandPowerShellToADServer

4. Open the Active Directory Users and Computers. And see the structure that we have.

I have an Organizational Unit (OU) “Customer” and under that I have separate Organizational Unit for each of the customer company. Then on the same level of “Customer”, I have created the “CustomerGroup” and inside it I added Active Directory group (Security Group) for each of the customer. The goal is, create the user and put it in Customer > [Customer Name] organizational unit. Then that user should be added in the security group in CustomerGroup > [Customer Name]

AD

Note:
Make sure that the Organizational Units and Security Groups are already crated. The PowerShell script above doesn’t have any instructions for creating the OU and the Security Group. (I will update this as soon as I have created and tested the script)

5. Open the Windows PowerShell as Administrator.

OpenPowerShell

6. Before we can run the script, we need to make sure that the “Execution Policy” allows you to execute the script.

To check the execution policy  type this “get-executionpolicy”

If the result is “Restricted” which will not allow you to run your script, run this command “set-executionpolicy remotesigned” then press “Y” or “y”.

That’s it.

Get-Policy

7. Here is how to execute your PowerShell script. One option is to go to your folder directory where the PowerShell script located and run the command
ExecutePowerShell

8. Verify the result.

Check if user is created on its designated OU. The user Tim Jones AD account was created under Customer > CustomerA OU.. which is correct.

ADUserCreated

Tim Jones is also added as member of CustomerA security group.

ADUserAddedinADGroup

I know there are more areas of improvements in this article like how to add logs, create OU if not exists, create Security Group if not exists.

Well, follow this blog and once I have it, I will definitely update this.

And there you go 🙂

If this helped you, please like this page and share it.. Thanks 🙂

Vista/XP : Change Icon and Default Name of your Local Drives

Well, this is my first Blog and the thing that push or inspire me to post article in this blog is the other author of Blog’s which they basically share their ideas base on their specialties. Some professional may not know everything and they still need others ideas or some beginners find the best way and they mostly found the answers thru Blog’s.

I want to share with you some tricks or let say some behind the scene on how to change the drive icons and default name of your local drives in XP or Vista.

Icon-1

 

If you find too boring with these icons, we can change it the way we want it looks like just follow the steps below.

1. Create an icon file using image editor tool as long it saves file with .ico extension. Am using now the Axialis Icon Workshop. Save the file in location where you can easily remember it. (I saved my file in C:\Movie.ico). Other easy way is search any existing icon file that you want to use, if  you don’t like to create a new one.

Icon-0

 

 

 

 

 

 

 

 

2. Open Registry Editor by clicking Start > Run > type “regedit” and click OK. Shortcut to this is Window + R.

3. Locate the path HKEY_LOCAL_MACHINE >SOFTWARE >Microsoft >Windows >currentversion >Explorer

Icon-24. If the “DriveIcons” key name exists in the path skip step #5 and proceed to step #6, if not proceed to step #5.

5. Right click on Explorer key and then select New > Key. This will create a new subfolder and named it “DriveIcons”.

Icon-3

6. Right click on “DriveIcons” key and then select New > Key. This will create a new subfolder and named it “E” the drive letter that you want to modify the icon.

Icon-47. Right click on “E” key and then select New > Key. This will create a new subfolder and named it “DefaultIcon” and this is where we need to declare the path of the icon. From the right pane of the window you will see a String Value that has a value name of “(Default)” with Type of “REG_SZ”. Double click this and in the Edit String window provide the path where the icon is saved (in Value Data field). I set my path to “C:\Movie.ico” and then click OK button to close Edit String Window. Notice that the Data column has the value of the path you declared.

Icon-5

8. This is optional in case you want to set default name for your local drive. Right click on “E” key again and then select New > Key. This will create a new subfolder and named it “DefaultLabel” and this is where we declare the default name of the drive. From the right pane of the window you will see a String Value that has a value name of “(Default)” with Type of “REG_SZ”. Double click this and in the Edit String window provide the name of the drive (in Value Data field). I set the name to “Custom Name” and then click OK button to close Edit String Window. Notice that the Data column has the name that you declared.

Icon-6

9. Open the My Computer window and see if the icon and label are reflected.

Icon-7

10. That’s it 🙂