September, 2014 | Kaizen - Continuous Improvement

Month: September 2014

How do I Change SQL Database from Single_User to Multi_User Mode

Posted on September 30, 2014 by

Raju Venkataraman Posted in SQL Server — No Comments ↓

How do I Change SQL Database from Single_User to Multi_User Mode: Sometimes, we have the need to set the database in Single User Mode to do some upgrades or blocking all the access to the Database. Set the Database to…

Nissan Murano 2010 Rack and Pinion Cost

Posted on September 28, 2014 by

Raju Venkataraman Posted in Collage — No Comments ↓

Dealership VS Non-Delarship Price I have NISSAN Murano 2010 AWD and I started noticing the Stiffness in power steering. I went to the NISSAN Dealer to Diagnose the problem and they found the Rack and Pinion was leaking. Obviously, the…

SQL Server – Generic Error Handling I use

Posted on September 25, 2014 by

Raju Venkataraman Posted in SQL Server — No Comments ↓

This is generic Error Handler I use for my Adhoc Scripts as Well as functions, stored procedures,etc.

-- Adding the error message.
USE master;
GO
EXEC sp_addmessage 50001, 16, 
   N'The Record doesn''t exist. Parameter : %s ,  Value : %s',@replace='Replace';
GO

-- Standard Variables Declarations

  DECLARE
	@ErrorMessage    NVARCHAR(4000),
        @ErrorNumber     INT,
        @ErrorSeverity   INT,
        @ErrorState      INT,
        @ErrorLine       INT,
        @ErrorProcedure  NVARCHAR(200),
	@ErrParameter    NVARCHAR(255),
	@ErrParameterValue NVARCHAR(255)

-- Stored Procedure Specific Variables Declarations
 DECLARE 
		@pUserName NVARCHAR(50)

 SELECT  
        @ErrorNumber = ERROR_NUMBER(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE(),
        @ErrorLine = ERROR_LINE(),
        @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');


SELECT @pUserName = username
		 FROM ps.User_ WITH (NOLOCK)
		WHERE UserID = @pUserID
		AND IsActive = 1 

IF @pUserName IS NULL
BEGIN
SELECT  @ErrorNumber = 50001 ,
        @ErrorSeverity = 16 ,
        @ErrorState    = 1 ,
	    @ErrorLine = ERROR_LINE(),
		@ErrParameter   = '@pUserID',
		@ErrParameterValue = CONVERT(VARCHAR(50) , @pUserID) ,
        @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');

GOTO ErrorHandler
END



GOTO ExitScript


ErrorHandler:


    -- Use RAISERROR inside the CATCH block to return error
    -- information about the original error that caused
    -- execution to jump to the CATCH block.
 RAISERROR 
        (
        @ErrorNumber, 
		@ErrorSeverity,   
        @ErrorState,     -- parameter: original error state.
		@ErrParameter,   -- '@pUserID',
		@ErrParameterValue
        );


ExitScript:

END

-- Adding the error message.

USE master;

EXEC sp_addmessage 50001, 16,

N'The Record doesn''t exist. Parameter : %s , Value : %s',@replace='Replace';

-- Standard Variables Declarations

DECLARE

@ErrorMessage NVARCHAR(4000),

@ErrorNumber INT,

@ErrorSeverity INT,

@ErrorState INT,

@ErrorLine INT,

@ErrorProcedure NVARCHAR(200),

@ErrParameter NVARCHAR(255),

@ErrParameterValue NVARCHAR(255)

-- Stored Procedure Specific Variables Declarations

DECLARE

@pUserName NVARCHAR(50)

SELECT

@ErrorNumber = ERROR_NUMBER(),

@ErrorSeverity = ERROR_SEVERITY(),

@ErrorState = ERROR_STATE(),

@ErrorLine = ERROR_LINE(),

@ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');

SELECT @pUserName = username

FROM ps.User_ WITH (NOLOCK)

WHERE UserID = @pUserID

AND IsActive = 1

IF @pUserName IS NULL

BEGIN

SELECT @ErrorNumber = 50001 ,

@ErrorSeverity = 16 ,

@ErrorState = 1 ,

@ErrorLine = ERROR_LINE(),

@ErrParameter = '@pUserID',

@ErrParameterValue = CONVERT(VARCHAR(50) , @pUserID) ,

@ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');

GOTO ErrorHandler

END

GOTO ExitScript

ErrorHandler:

-- Use RAISERROR inside the CATCH block to return error

-- information about the original error that caused

-- execution to jump to the CATCH block.

RAISERROR

(

@ErrorNumber,

@ErrorSeverity,

@ErrorState, -- parameter: original error state.

@ErrParameter, -- '@pUserID',

@ErrParameterValue

);

ExitScript:

END

Common Error Handling Stored Procedure

-- Check to see whether this stored procedure exists.  
IF OBJECT_ID (N'common.GetErrorInfo', N'P') IS NOT NULL  
    DROP PROCEDURE common.GetErrorInfo;  
GO  
  
-- Create procedure to retrieve error information.  
CREATE PROCEDURE common.GetErrorInfo  
AS  
    SELECT   
         ERROR_NUMBER() AS ErrorNumber  
        ,ERROR_SEVERITY() AS ErrorSeverity  
        ,ERROR_STATE() AS ErrorState  
        ,ERROR_LINE () AS ErrorLine  
        ,ERROR_PROCEDURE() AS ErrorProcedure  
        ,ERROR_MESSAGE() AS ErrorMessage;  
GO  


EXEC common.GetErrorInfo
  
-- SET XACT_ABORT ON will cause the transaction to be uncommittable  
-- when the constraint violation occurs.   
SET XACT_ABORT ON;  
  
BEGIN TRY  
    BEGIN TRANSACTION;  
        -- statement will generate a error.  
        SELECT 1/0 AS [1/0 Error]
    COMMIT TRANSACTION;  
END TRY  
BEGIN CATCH  
    -- Execute error retrieval routine.  
    EXECUTE common.GetErrorInfo;  
  
    -- Test XACT_STATE:  
        -- If 1, the transaction is committable.  
        -- If -1, the transaction is uncommittable and should   
        --     be rolled back.  
        -- XACT_STATE = 0 means that there is no transaction and  
        --     a commit or rollback operation would generate an error.  
  
    -- Test whether the transaction is uncommittable.  
    IF (XACT_STATE()) = -1  
    BEGIN  
        PRINT  
            N'The transaction is in an uncommittable state.' +  
            'Rolling back transaction.'  
        ROLLBACK TRANSACTION;  
    END;  
  
    -- Test whether the transaction is committable.  
    IF (XACT_STATE()) = 1  
    BEGIN  
        PRINT  
            N'The transaction is committable.' +  
            'Committing transaction.'  
        COMMIT TRANSACTION;     
    END;  
END CATCH;  
GO

-- Check to see whether this stored procedure exists.

IF OBJECT_ID (N'common.GetErrorInfo', N'P') IS NOT NULL

DROP PROCEDURE common.GetErrorInfo;

-- Create procedure to retrieve error information.

CREATE PROCEDURE common.GetErrorInfo

SELECT

ERROR_NUMBER() AS ErrorNumber

,ERROR_SEVERITY() AS ErrorSeverity

,ERROR_STATE() AS ErrorState

,ERROR_LINE () AS ErrorLine

,ERROR_PROCEDURE() AS ErrorProcedure

,ERROR_MESSAGE() AS ErrorMessage;

EXEC common.GetErrorInfo

-- SET XACT_ABORT ON will cause the transaction to be uncommittable

-- when the constraint violation occurs.

SET XACT_ABORT ON;

BEGIN TRY

BEGIN TRANSACTION;

-- statement will generate a error.

SELECT 1/0 AS [1/0 Error]

COMMIT TRANSACTION;

END TRY

BEGIN CATCH

-- Execute error retrieval routine.

EXECUTE common.GetErrorInfo;

-- Test XACT_STATE:

-- If 1, the transaction is committable.

-- If -1, the transaction is uncommittable and should

-- be rolled back.

-- XACT_STATE = 0 means that there is no transaction and

-- a commit or rollback operation would generate an error.

-- Test whether the transaction is uncommittable.

IF (XACT_STATE()) = -1

BEGIN

N'The transaction is in an uncommittable state.' +

'Rolling back transaction.'

ROLLBACK TRANSACTION;

END;

-- Test whether the transaction is committable.

IF (XACT_STATE()) = 1

BEGIN

N'The transaction is committable.' +

'Committing transaction.'

COMMIT TRANSACTION;

END;

END CATCH;

Tagged with: SQL, SQLServer

PHP Drivers for MSSQL Issues

Posted on September 21, 2014 by

Raju Venkataraman Posted in LAMP, PHP, SQL Server, Technology — No Comments ↓

PHP Drivers for MSSQL Issues I wanted to setup my Mac Book Pro to access the SQL Server RDS Instance on AWS.My intial thinking was just google on how to setup PHP Drivers on Mac OSX and follow the steps.…

SQL Script Header Footer I use

Posted on September 19, 2014 by

Raju Venkataraman Posted in SQL Server — No Comments ↓

SQL Script Header Footer I use :

/******************************************************************************************************
** Desc: This File will create the XXXXXXXXXXXXXXXXXXXXXXX .
** Auth: Raju Venkataraman ( i90runner@gmail.com )
** Date: 09/21/2014 Created
**************************
** Change History
**************************
** CR Date Author Description
** ----- ----------– ----------- ------------------------------------------------------------
** 1 09/25/2014 Raju Venkataraman added Specific Checks to the Script
** 2 09/25/2014 Raju Venkataraman added ErrorHandler Validations
********************************************************************************************************/
SET NOCOUNT ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET NUMERIC_ROUNDABORT OFF

-----------BEGIN SQL SCRIPT HEADER-------------------
DECLARE @DB_NAME VARCHAR(128)
SET @DB_NAME = (SELECT DB_NAME(dbid) FROM MASTER..SYSPROCESSES WHERE spid=@@SPID)
PRINT '-----------------------------------------------------------------------'
PRINT '-----START RAPID DEVELOPEMENT SQL SCRIPT--------'
PRINT '-----SQL COMPILED BY: Raju Venkataraman'
PRINT '-----SCRIPT RAN ON DB: ' + @DB_NAME
PRINT '-----SCRIPT START TIME: ' + CONVERT(VARCHAR,CONVERT(DATETIME,GETDATE()),121)
PRINT '-----MachineName : ' + cast( serverproperty ( 'MachineName' ) as varchar )
PRINT '-----SQL Instance : ' + cast( @@SERVERNAME as varchar )
PRINT '-----DB User : ' + current_user
PRINT '-----System User : ' + system_user
PRINT '-----Host : ' + host_name()
PRINT '-----Application : ' + app_name()
PRINT '-----TranCount : ' + cast (@@trancount as varchar)
PRINT '-----------------------------------------------------------------------'
GO
----------------END SQL SCRIPT HEADER---------------------

----- Standard Variables Declarations

DECLARE
@ErrorMessage NVARCHAR(4000),
@ErrorNumber INT,
@ErrorSeverity INT,
@ErrorState INT,
@ErrorLine INT,
@ErrorProcedure NVARCHAR(200),
@ErrParameter NVARCHAR(255),
@ErrParameterValue NVARCHAR(255)

---- Script Specific Variables Declarations

DECLARE @pParam1 INT



IF NOT EXISTS ( 
	SELECT * FROM sys.syslogins lgs WHERE loginname = 'DevLogin'
 )

BEGIN

SELECT
@ErrorNumber = 50001 ,
@ErrorSeverity = 16 ,
@ErrorState = 1 ,
@ErrorLine = ERROR_LINE(),
@ErrParameter ='DevLogin Login doesn''t exist on this Server',
@ErrParameterValue = CONVERT(VARCHAR(50) , @pParam1) ,
@ErrorProcedure = ISNULL(ERROR_PROCEDURE(),'-');

GOTO ErrorHandler
END
ELSE
	BEGIN
		PRINT 'DevLogin Login Exists on this Server'
	END

IF @@TRANCOUNT >0

BEGIN

SELECT
@ErrorNumber = 50001 ,
@ErrorSeverity = 16 ,
@ErrorState = 1 ,
@ErrorLine = ERROR_LINE(),
@ErrParameter ='@@TranCont',
@ErrParameterValue = CONVERT(VARCHAR(255) ,'You left the Transction Open!!!. Open Transaction Count ::'+CONVERT(VARCHAR(10) ,@@TRANCOUNT)) ,
@ErrorProcedure = ISNULL(ERROR_PROCEDURE(),'-');

GOTO ErrorHandler
END

GOTO ExitScript

ErrorHandler:

----- Use RAISERROR inside the CATCH block to return error
----- information about the original error that caused
----- execution to jump to the CATCH block.
RAISERROR
(
@ErrorNumber,
@ErrorSeverity,
@ErrorState, ----- parameter: original error state.
@ErrParameter,
@ErrParameterValue
);

ExitScript:

-------BEGIN SQL SCRIPT FOOTER------------------------------------
PRINT '-----------------------------------------------------------'
PRINT '-------------------FINISHED SQL SCRIPT---------------------'
PRINT '--COMPLETED TIME:' + CONVERT(VARCHAR,CONVERT(DATETIME,GETDATE()),121)
PRINT '--TranCount : ' + cast (@@trancount as varchar)
PRINT '------------------------------------------------------------'

------------------------END SQL SCRIPT FOOTER-----------------

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

/******************************************************************************************************

** Desc: This File will create the XXXXXXXXXXXXXXXXXXXXXXX .

** Auth: Raju Venkataraman ( i90runner@gmail.com )

** Date: 09/21/2014 Created

**************************

** Change History

**************************

** CR Date Author Description

** ----- ----------– ----------- ------------------------------------------------------------

** 1 09/25/2014 Raju Venkataraman added Specific Checks to the Script

** 2 09/25/2014 Raju Venkataraman added ErrorHandler Validations

********************************************************************************************************/

SET NOCOUNT ON

SET ARITHABORT ON

SET CONCAT_NULL_YIELDS_NULL ON

SET QUOTED_IDENTIFIER ON

SET ANSI_NULLS ON

SET ANSI_PADDING ON

SET ANSI_WARNINGS ON

SET NUMERIC_ROUNDABORT OFF

-----------BEGIN SQL SCRIPT HEADER-------------------

DECLARE @DB_NAME VARCHAR(128)

SET @DB_NAME = (SELECT DB_NAME(dbid) FROM MASTER..SYSPROCESSES WHERE spid=@@SPID)

PRINT '-----------------------------------------------------------------------'

PRINT '-----START RAPID DEVELOPEMENT SQL SCRIPT--------'

PRINT '-----SQL COMPILED BY: Raju Venkataraman'

PRINT '-----SCRIPT RAN ON DB: ' + @DB_NAME

PRINT '-----SCRIPT START TIME: ' + CONVERT(VARCHAR,CONVERT(DATETIME,GETDATE()),121)

PRINT '-----MachineName : ' + cast( serverproperty ( 'MachineName' ) as varchar )

PRINT '-----SQL Instance : ' + cast( @@SERVERNAME as varchar )

PRINT '-----DB User : ' + current_user

PRINT '-----System User : ' + system_user

PRINT '-----Host : ' + host_name()

PRINT '-----Application : ' + app_name()

PRINT '-----TranCount : ' + cast (@@trancount as varchar)

PRINT '-----------------------------------------------------------------------'

----------------END SQL SCRIPT HEADER---------------------

----- Standard Variables Declarations

DECLARE

@ErrorMessage NVARCHAR(4000),

@ErrorNumber INT,

@ErrorSeverity INT,

@ErrorState INT,

@ErrorLine INT,

@ErrorProcedure NVARCHAR(200),

@ErrParameter NVARCHAR(255),

@ErrParameterValue NVARCHAR(255)

---- Script Specific Variables Declarations

DECLARE @pParam1 INT

IF NOT EXISTS (

SELECT * FROM sys.syslogins lgs WHERE loginname = 'DevLogin'

)

BEGIN

SELECT

@ErrorNumber = 50001 ,

@ErrorSeverity = 16 ,

@ErrorState = 1 ,

@ErrorLine = ERROR_LINE(),

@ErrParameter ='DevLogin Login doesn''t exist on this Server',

@ErrParameterValue = CONVERT(VARCHAR(50) , @pParam1) ,

@ErrorProcedure = ISNULL(ERROR_PROCEDURE(),'-');

GOTO ErrorHandler

END

ELSE

BEGIN

PRINT 'DevLogin Login Exists on this Server'

END

IF @@TRANCOUNT >0

BEGIN

SELECT

@ErrorNumber = 50001 ,

@ErrorSeverity = 16 ,

@ErrorState = 1 ,

@ErrorLine = ERROR_LINE(),

@ErrParameter ='@@TranCont',

@ErrParameterValue = CONVERT(VARCHAR(255) ,'You left the Transction Open!!!. Open Transaction Count ::'+CONVERT(VARCHAR(10) ,@@TRANCOUNT)) ,

@ErrorProcedure = ISNULL(ERROR_PROCEDURE(),'-');

GOTO ErrorHandler

END

GOTO ExitScript

ErrorHandler:

----- Use RAISERROR inside the CATCH block to return error

----- information about the original error that caused

----- execution to jump to the CATCH block.

RAISERROR

(

@ErrorNumber,

@ErrorSeverity,

@ErrorState, ----- parameter: original error state.

@ErrParameter,

@ErrParameterValue

);

ExitScript:

-------BEGIN SQL SCRIPT FOOTER------------------------------------

PRINT '-----------------------------------------------------------'

PRINT '-------------------FINISHED SQL SCRIPT---------------------'

PRINT '--COMPLETED TIME:' + CONVERT(VARCHAR,CONVERT(DATETIME,GETDATE()),121)

PRINT '--TranCount : ' + cast (@@trancount as varchar)

PRINT '------------------------------------------------------------'

------------------------END SQL SCRIPT FOOTER-----------------

Checklist for SQL Server Installation

Posted on September 19, 2014 by

Raju Venkataraman Posted in SQL Server, Technology, TempDB — No Comments ↓

Feature Selection : We don’t want to install all the features. Identify the features needed and install only those features. Model Database Properties : Figure out all the Model database Properties and configure them Correctly. File Layout : Make sure…

Helpful Linux Commands

Posted on September 18, 2014 by

Raju Venkataraman Posted in LAMP, Technology — No Comments ↓

Helpful Linux Commands Find whats on port running on a particular port : ex port 8098 $ sudo netstat -apn | grep 8098 tcp 0 0 10.224.35.187:8098 0.0.0.0:* LISTEN 19666/beam.smp Find status of a particular process : ex status of…

Git Cheat Sheet

Posted on September 18, 2014 by

Raju Venkataraman Posted in Git, LAMP, Technology — No Comments ↓

Git Quick Reference Adding the ShortCut:

$ git config --global alias.add-commit '!git add -A &&  git commit'
$ git add-commit
$ git config --global --edit

[user]
        name = Raju
        email = i90Runner@gmail.com
[gui]
        recentrepo = C:/Git
[alias]
        add-comment = !git add -A  && git commit

$ git config --global alias.add-commit '!git add -A && git commit'

$ git add-commit

$ git config --global --edit

[user]

name = Raju

email = i90Runner@gmail.com

[gui]

recentrepo = C:/Git

[alias]

add-comment = !git add -A && git commit

Getting help:

git help command or
git command --help Show help for a command

1 2	git help command or git command --help Show help for a command

Repository creation:

git init Create a repository in the current directory
git clone url Clone a remote repository into a subdirectory
git clone --recursive url Clone a remote repository into a subdirectory recursively including the submodules

git init Create a repository in the current directory

git clone url Clone a remote repository into a subdirectory

git clone --recursive url Clone a remote repository into a subdirectory recursively including the submodules

File operations:

git add path Add file or files in directory recursively
git rm path Remove file or directory from the working tree
-f Force deletion of file(s) from disk
git mv path destination Move file or directory to new location
-f Overwrite existing destination files
git checkout [rev] file Restore file from current branch or revision
-f Overwrite uncommitted local changes

git add path Add file or files in directory recursively

git rm path Remove file or directory from the working tree

-f Force deletion of file(s) from disk

git mv path destination Move file or directory to new location

-f Overwrite existing destination files

git checkout [rev] file Restore file from current branch or revision

-f Overwrite uncommitted local changes

Working tree:

git status Show status of the working tree
git diff [path] Show diff of changes in the working tree
git diff HEAD path Show diff of stages and unstaged changes
git add path Stage file for commit
git reset HEAD path Unstage file for commit
git commit Commit files that has been staged (with git-add)
-a Automatically stage all modified files
git reset --soft HEAD^ Undo commit & keep changes in the working tree
git reset --hard HEAD^ Reset the working tree to the last commit
git clean Clean unknown files from the working tree

git status Show status of the working tree

git diff [path] Show diff of changes in the working tree

git diff HEAD path Show diff of stages and unstaged changes

git add path Stage file for commit

git reset HEAD path Unstage file for commit

git commit Commit files that has been staged (with git-add)

-a Automatically stage all modified files

git reset --soft HEAD^ Undo commit & keep changes in the working tree

git reset --hard HEAD^ Reset the working tree to the last commit

git clean Clean unknown files from the working tree

Examining History:

git log [path] View commit log, optionally for specific path
git log [from[..to]] View commit log for a given revision range
--stat List diffstat for each revision
-S'pattern' Search history for changes matching pattern
git blame [file] Show file annotated with line modifications

git log [path] View commit log, optionally for specific path

git log [from[..to]] View commit log for a given revision range

--stat List diffstat for each revision

-S'pattern' Search history for changes matching pattern

git blame [file] Show file annotated with line modifications

Remote repositories – remotes:

git fetch [remote] Fetch changes from a remote repository
git pull [remote] Fetch and merge changes from a remote repository
git push [remote] Push changes to a remote repository
git remote List remote repositories
git remote add remote url Add remote to list of tracked repositories

git fetch [remote] Fetch changes from a remote repository

git pull [remote] Fetch and merge changes from a remote repository

git push [remote] Push changes to a remote repository

git remote List remote repositories

git remote add remote url Add remote to list of tracked repositories

Branches:

git checkout branch Switch working tree to branch
-b branch Create branch before switching to it
git branch List local branches
git branch -f branch rev Overwrite existing branch, start from revision
git merge branch Merge changes from branch

git checkout branch Switch working tree to branch

-b branch Create branch before switching to it

git branch List local branches

git branch -f branch rev Overwrite existing branch, start from revision

git merge branch Merge changes from branch

Exporting and importing:

git apply - < file Apply patch from stdin git format-patch from[..to] Format a patch with log message and diffstat git archive rev > file Export snapshot of revision to file
--prefix=dir/ Nest all files in the snapshot in directory
--format=[tar|zip] Specify archive format to use: tar or zip

git apply - < file Apply patch from stdin git format-patch from[..to] Format a patch with log message and diffstat git archive rev > file Export snapshot of revision to file

--prefix=dir/ Nest all files in the snapshot in directory

--format=[tar|zip] Specify archive format to use: tar or zip

Tags:

git tag name [revision] Create tag for a given revision
-s Sign tag with your private key using GPG
-l [pattern] List tags, optionally matching pattern

git tag name [revision] Create tag for a given revision

-s Sign tag with your private key using GPG

-l [pattern] List tags, optionally matching pattern

File status flags: M modified…

Tagged with: Git

MySQL and phpMyAdmin Setup

Posted on September 18, 2014 by

Raju Venkataraman Posted in AWS, LAMP, MySQL, NGINX, PHPMyAdmin — No Comments ↓

MySQL and phpMyAdmin Setup

sudo yum install mysql
sudo yum install mysql-server
sudo yum install mysql-devel
sudo chgrp -R mysql /var/lib/mysql
sudo chmod -R 770 /var/lib/mysql
sudo service mysqld start
sudo yum install phpmyadmin

sudo yum install mysql

sudo yum install mysql-server

sudo yum install mysql-devel

sudo chgrp -R mysql /var/lib/mysql

sudo chmod -R 770 /var/lib/mysql

sudo service mysqld start

sudo yum install phpmyadmin

Errors: # tail -f error.log 2014/03/06 20:11:05 [error] 2833#0: *1 directory index of “/usr/share/phpMyAdmin/” is forbidden, client: 216.251.112.134, server: localhost, request: “GET / HTTP/1.1”, host: “ec2-54-244-152-140.us-west-2.compute.amazonaws.com” Make sure you have given correct chmod permissions are…

Install S3CMD tools on EC2 Instance.

Posted on September 18, 2014 by

Raju Venkataraman Posted in AWS, EC2, LAMP, Technology — No Comments ↓

Install S3CMD tools on EC2 Instance. SSH Into your EC2 Box and you run the following command to install S3CMD.

$ sudo yum install s3cmd
Loaded plugins: priorities, update-motd, upgrade-helper
Bad id for repo: datastax , byte = 8
amzn-main/latest | 2.1 kB 00:00
amzn-updates/latest | 2.3 kB 00:00
No package s3cmd available.
Error: Nothing to do

$ sudo yum install s3cmd

Loaded plugins: priorities, update-motd, upgrade-helper

Bad id for repo: datastax , byte = 8

amzn-main/latest | 2.1 kB 00:00

amzn-updates/latest | 2.3 kB 00:00

No package s3cmd available.

Error: Nothing to do

No REPO is available for S3CMD.

Navigate to  cd  /etc/yum.repos.d/

1	Navigate to cd /etc/yum.repos.d/

RUN WGET Command

[ec2-user@ip-99-999-82-777 yum.repos.d]$ sudo wget http://s3tools.org/repo/RHEL_6/s3tools.repo

1	[ec2-user@ip-99-999-82-777 yum.repos.d]$ sudo wget http://s3tools.org/repo/RHEL_6/s3tools.repo

Then run your yum install

$ sudo yum install s3cmd

I have pasted my error output and successful installation output.

ec2-user@ip-99-999-82-777 ~]$ cd /etc/
[ec2-user@ip-99-999-82-777 etc]$ cd yum.repos.d/
[ec2-user@ip-99-999-82-777 yum.repos.d]$ ls
10gen-mongodb.repo amzn-nosrc.repo amzn-updates.repo epel.repo
amzn-main.repo amzn-preview.repo datastax.repo epel-testing.repo
[ec2-user@ip-99-999-82-777 yum.repos.d]$ sudo wget http://s3tools.org/repo/RHEL_6/s3tools.repo
--2014-02-16 22:25:42-- http://s3tools.org/repo/RHEL_6/s3tools.repo
Resolving s3tools.org (s3tools.org)... 93.89.80.122, 2a01:348:0:6:5d59:50c3:0:b0b1
Connecting to s3tools.org (s3tools.org)|93.89.80.122|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 301 [application/octet-stream]
Saving to: ‘s3tools.repo’

100%[==================================================================>] 301 --.-K/s in 0s

2014-02-16 22:25:42 (25.1 MB/s) - ‘s3tools.repo’ saved [301/301]

[ec2-user@ip-99-999-82-777 yum.repos.d]$ ls
10gen-mongodb.repo amzn-nosrc.repo amzn-updates.repo epel.repo s3tools.repo
amzn-main.repo amzn-preview.repo datastax.repo epel-testing.repo
[ec2-user@ip-99-999-82-777 yum.repos.d]$ sudo yum install s3cmd
Loaded plugins: priorities, update-motd, upgrade-helper
Bad id for repo: datastax , byte = 8
10gen | 951 B 00:00
s3tools | 1.3 kB 00:00
s3tools/primary | 1.0 kB 00:00
s3tools 3/3
Resolving Dependencies
--> Running transaction check
---> Package s3cmd.x86_64 0:1.0.0-4.1 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

============================================================================================================
Package Arch Version Repository Size
============================================================================================================
Installing:
s3cmd x86_64 1.0.0-4.1 s3tools 91 k

Transaction Summary
============================================================================================================
Install 1 Package

Total download size: 91 k
Installed size: 296 k
Is this ok [y/d/N]: y
Downloading packages:
warning: /var/cache/yum/x86_64/latest/s3tools/packages/s3cmd-1.0.0-4.1.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID e612b227: NOKEY
Public key for s3cmd-1.0.0-4.1.x86_64.rpm is not installed
s3cmd-1.0.0-4.1.x86_64.rpm | 91 kB 00:00
Retrieving key from http://s3tools.org/repo/RHEL_6/repodata/repomd.xml.key
Importing GPG key 0xE612B227:
Userid : "home:mludvig OBS Project <home:mludvig@build.opensuse.org>"
Fingerprint: 96c4 2afc 03b2 73a8 43e2 a964 9f7d 494d e612 b227
From : http://s3tools.org/repo/RHEL_6/repodata/repomd.xml.key
Is this ok [y/N]: y
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : s3cmd-1.0.0-4.1.x86_64 1/1
Verifying : s3cmd-1.0.0-4.1.x86_64 1/1

Installed:
s3cmd.x86_64 0:1.0.0-4.1

Complete!

$ sudo yum install s3cmd

I have pasted my error output and successful installation output.

ec2-user@ip-99-999-82-777 ~]$ cd /etc/

[ec2-user@ip-99-999-82-777 etc]$ cd yum.repos.d/

[ec2-user@ip-99-999-82-777 yum.repos.d]$ ls

10gen-mongodb.repo amzn-nosrc.repo amzn-updates.repo epel.repo

amzn-main.repo amzn-preview.repo datastax.repo epel-testing.repo

[ec2-user@ip-99-999-82-777 yum.repos.d]$ sudo wget http://s3tools.org/repo/RHEL_6/s3tools.repo

--2014-02-16 22:25:42-- http://s3tools.org/repo/RHEL_6/s3tools.repo

Resolving s3tools.org (s3tools.org)... 93.89.80.122, 2a01:348:0:6:5d59:50c3:0:b0b1

Connecting to s3tools.org (s3tools.org)|93.89.80.122|:80... connected.

HTTP request sent, awaiting response... 200 OK

Length: 301 [application/octet-stream]

Saving to: ‘s3tools.repo’

100%[==================================================================>] 301 --.-K/s in 0s

2014-02-16 22:25:42 (25.1 MB/s) - ‘s3tools.repo’ saved [301/301]

[ec2-user@ip-99-999-82-777 yum.repos.d]$ ls

10gen-mongodb.repo amzn-nosrc.repo amzn-updates.repo epel.repo s3tools.repo

amzn-main.repo amzn-preview.repo datastax.repo epel-testing.repo

[ec2-user@ip-99-999-82-777 yum.repos.d]$ sudo yum install s3cmd

Loaded plugins: priorities, update-motd, upgrade-helper

Bad id for repo: datastax , byte = 8

10gen | 951 B 00:00

s3tools | 1.3 kB 00:00

s3tools/primary | 1.0 kB 00:00

s3tools 3/3

Resolving Dependencies

--> Running transaction check

---> Package s3cmd.x86_64 0:1.0.0-4.1 will be installed

--> Finished Dependency Resolution

Dependencies Resolved

============================================================================================================

Package Arch Version Repository Size

============================================================================================================

Installing:

s3cmd x86_64 1.0.0-4.1 s3tools 91 k

Transaction Summary

============================================================================================================

Install 1 Package

Total download size: 91 k

Installed size: 296 k

Is this ok [y/d/N]: y

Downloading packages:

warning: /var/cache/yum/x86_64/latest/s3tools/packages/s3cmd-1.0.0-4.1.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID e612b227: NOKEY

Public key for s3cmd-1.0.0-4.1.x86_64.rpm is not installed

s3cmd-1.0.0-4.1.x86_64.rpm | 91 kB 00:00

Retrieving key from http://s3tools.org/repo/RHEL_6/repodata/repomd.xml.key

Importing GPG key 0xE612B227:

Userid : "home:mludvig OBS Project <home:mludvig@build.opensuse.org>"

Fingerprint: 96c4 2afc 03b2 73a8 43e2 a964 9f7d 494d e612 b227

From : http://s3tools.org/repo/RHEL_6/repodata/repomd.xml.key

Is this ok [y/N]: y

Running transaction check

Running transaction test

Transaction test succeeded

Running transaction

Installing : s3cmd-1.0.0-4.1.x86_64 1/1

Verifying : s3cmd-1.0.0-4.1.x86_64 1/1

Installed:

s3cmd.x86_64 0:1.0.0-4.1

Complete!

Setup EC2 Instance with NGINX and PHP-FPM

Posted on September 18, 2014 by

Raju Venkataraman Posted in LAMP, NGINX, Technology — No Comments ↓

Setup EC2 Instance with NGINX and PHP-FPM Login into AWS Console and launch default Amazon Instance. Security Group: Create your security group and download your key. Restrict the permissions on your key.

sudo chmod 400 Kaizen.pem

1	sudo chmod 400 Kaizen.pem

SSH into your Machine:

ssh -i Kaizen.pem ec2-user@ec2-54-245-1-226.us-west-2.compute.amazonaws.com

1	ssh -i Kaizen.pem ec2-user@ec2-54-245-1-226.us-west-2.compute.amazonaws.com

Update your…

SQL Server – Interview Questions

Posted on September 17, 2014 by

Raju Venkataraman Posted in Interview Questions, SQL Server, Technology — No Comments ↓

Database Professional – Interview Questions What factors do you usually consider for your datastore selection ? Data Volume , Variety and Velocity. Whats CAP Theorem?. Consistency , Availability and Partition Tolerance and you can’t all three together. In theoretical computer science,…

Tagged with: Interview, Interview Questions

NGINX – SSL Certificate Setup

Posted on September 17, 2014 by

Raju Venkataraman Posted in LAMP, NGINX, Technology — No Comments ↓

Create the Server Key and Certificate Signing Request Start off by creating the 1024 rsa private key.

sudo openssl genrsa -des3 -out /etc/nginx/conf.d/i90runner.key 1024
sudo openssl req -new -key /etc/nginx/conf.d/i90runner.key -out /etc/nginx/conf.d/i90runner.csr

1 2	sudo openssl genrsa -des3 -out /etc/nginx/conf.d/i90runner.key 1024 sudo openssl req -new -key /etc/nginx/conf.d/i90runner.key -out /etc/nginx/conf.d/i90runner.csr

Remove the Passphrase

sudo cp /etc/nginx/conf.d/i90runner.key /etc/nginx/conf.d/i90runner.key.org
sudo openssl rsa -in /etc/nginx/conf.d/i90runner.key.org -out /etc/nginx/conf.d/i90runner.key

1 2	sudo cp /etc/nginx/conf.d/i90runner.key /etc/nginx/conf.d/i90runner.key.org sudo openssl rsa -in /etc/nginx/conf.d/i90runner.key.org -out /etc/nginx/conf.d/i90runner.key

Sign your SSL Certificate

sudo openssl x509 -req -days 1000 -in /etc/nginx/conf.d/i90runner.csr -signkey /etc/nginx/conf.d/i90runner.key -out /etc/nginx/conf.d/i90runner.crt

1	sudo openssl x509 -req -days 1000 -in /etc/nginx/conf.d/i90runner.csr -signkey /etc/nginx/conf.d/i90runner.key -out /etc/nginx/conf.d/i90runner.crt

Set Up the Certificate

 sudo vim virtual.conf
 sudo sudo service nginx restart
NGINX config Server Block :
  server {
        listen       443;
        server_name  i90runner.com;

        ssl                  on;
        ssl_certificate      i90runner.crt;
        ssl_certificate_key  i90runner.key;
        ssl_session_timeout  5m;

        ssl_protocols  SSLv2 SSLv3 TLSv1;
        ssl_ciphers  HIGH:!aNULL:!MD5;
        ssl_prefer_server_ciphers   on;

        location / {
            root   /xxx/share/nginx/xxx/kaizen;
            index  index.html index.htm index.php;
        }
        }

sudo vim virtual.conf

sudo sudo service nginx restart

NGINX config Server Block :

server {

listen 443;

server_name i90runner.com;

ssl on;

ssl_certificate i90runner.crt;

ssl_certificate_key i90runner.key;

ssl_session_timeout 5m;

ssl_protocols SSLv2 SSLv3 TLSv1;

ssl_ciphers HIGH:!aNULL:!MD5;

ssl_prefer_server_ciphers on;

location / {

root /xxx/share/nginx/xxx/kaizen;

index index.html index.htm index.php;

}

Command History :

1001 sudo openssl genrsa -des3 -out /etc/nginx/conf.d/i90runner.key 1024
1002 sudo openssl req -new -key /etc/nginx/conf.d/i90runner.key -out /etc/nginx/conf.d/i90runner.csr
1003 sudo cp /etc/nginx/conf.d/i90runner.key i90runner.key.org
1004 sudo cp /etc/nginx/conf.d/i90runner.key /etc/nginx/conf.d/i90runner.key.org
1005 sudo openssl rsa -in /etc/nginx/conf.d/i90runner.key.org -out /etc/nginx/conf.d/i90runner.key
1006 sudo openssl x509 -req -days 1000 -in /etc/nginx/conf.d/i90runner.csr -signkey /etc/nginx/conf.d/i90runner.key -out /etc/nginx/conf.d/i90runner.crt

1007 cp conf.d/i90runner.key i90runner.key
1008 sudo cp conf.d/i90runner.key i90runner.key
1009 sudo cp conf.d/i90runner.crt i90runner.crt
1010 ls
1011 sudo chown nginx i90runner.*
1012 ls -l
1013 ls
1014 sudo vim nginx.conf
1015 cd conf.d/
1018 sudo vim virtual.conf
1019 sudo sudo service nginx restart

1001 sudo openssl genrsa -des3 -out /etc/nginx/conf.d/i90runner.key 1024

1002 sudo openssl req -new -key /etc/nginx/conf.d/i90runner.key -out /etc/nginx/conf.d/i90runner.csr

1003 sudo cp /etc/nginx/conf.d/i90runner.key i90runner.key.org

1004 sudo cp /etc/nginx/conf.d/i90runner.key /etc/nginx/conf.d/i90runner.key.org

1005 sudo openssl rsa -in /etc/nginx/conf.d/i90runner.key.org -out /etc/nginx/conf.d/i90runner.key

1006 sudo openssl x509 -req -days 1000 -in /etc/nginx/conf.d/i90runner.csr -signkey /etc/nginx/conf.d/i90runner.key -out /etc/nginx/conf.d/i90runner.crt

1007 cp conf.d/i90runner.key i90runner.key

1008 sudo cp conf.d/i90runner.key i90runner.key

1009 sudo cp conf.d/i90runner.crt i90runner.crt

1010 ls

1011 sudo chown nginx i90runner.*

1012 ls -l

1013 ls

1014 sudo vim nginx.conf

1015 cd conf.d/

1018 sudo vim virtual.conf

1019 sudo sudo service nginx restart

xp_cmdshell command to Add User

Posted on September 16, 2014 by

Raju Venkataraman Posted in Technology — No Comments ↓

Add user to Admin Group through SQL Server Its absolutely bad practice to turn on xp_cmdshell without comprehensive security audit and security testing. By default this feature is turned off. If you end up having the permission to execute xp_cmdshell,…

Connect to SQL Server When SA or Service Account Credentials not Available

Posted on September 16, 2014 by

Raju Venkataraman Posted in SQL Server, Technology — No Comments ↓

Connect to SQL Server When SA Account or Service Account Credentials not Available : I recently inherited the SQL Serer which was running under a user account who left the company and no one knows the password for sa account.…

Move SQL Server Database to Different Location

Posted on September 15, 2014 by

Raju Venkataraman Posted in SQL Server, Technology — No Comments ↓

Move SQL Server Database to Different Location : There are several way to do this but I find this option being simple.Sometimes we face the issue with Drive being full. It happens when we have Database on C Drive and…

SQL Server Waits and Queues

Posted on September 15, 2014 by

Raju Venkataraman Posted in SQL Server, Technology — No Comments ↓

SQL Server Waits and Queues : I watched Paul Randall Videos on Wait Types and which inspired me to write a simple post on wait types on logical way. We all have written queries, TSQL statements to perform CRUD (…

Nginx Config for Perma Links

Posted on September 13, 2014 by

Raju Venkataraman Posted in WordPress — No Comments ↓

NGINX Config Change to enable Permalink changes : By default WordPress links are configured similar to ” https://ramblingsofraju.com/?p=123 “. If you need to change this URL to be something SEO friendly and other than default URL which points to the…

Tagged with: permalinks, Technology, WordPress

SQL Join Diagram and Query

Posted on September 13, 2014 by

Raju Venkataraman Posted in SQL Server, Technology — No Comments ↓

SQL Join Diagram and Query Create TableA and TableB and Populate Data

CREATE TABLE TableA
    (
      ID INT IDENTITY(1, 1) ,
      [Key] NVARCHAR(255) ,
      Value NVARCHAR(4000) ,
      CreatedDateTime DATETIME2 DEFAULT GETUTCDATE() ,
      CreatedBy NVARCHAR(50) DEFAULT CURRENT_USER ,
      Status TINYINT DEFAULT 1
    );

CREATE TABLE TableB
    (
      ID INT IDENTITY(1, 1) ,
      [Key] NVARCHAR(255) ,
      Value NVARCHAR(4000) ,
      CreatedDateTime DATETIME2 DEFAULT GETUTCDATE() ,
      CreatedBy NVARCHAR(50) DEFAULT CURRENT_USER ,
      Status TINYINT DEFAULT 1
    );


INSERT  INTO dbo.TableA
        ( [Key] ,
          Value 
        )
        SELECT  'AL' ,
                'Alabama'
        UNION
        SELECT  'AK' ,
                'Alaska'
        UNION
        SELECT  'AZ' ,
                'Arizona'
        UNION
        SELECT  'CA' ,
                'California'
        UNION
        SELECT  'CO' ,
                'Colorado'
        UNION
        SELECT  'WA' ,
                'Washington';


INSERT  INTO dbo.TableB
        ( [Key] ,
          Value 
        )
        SELECT  'FL' ,
                'Florida'
        UNION
        SELECT  'DE' ,
                'Delaware'
        UNION
        SELECT  'CT' ,
                'Connecticut'
        UNION
        SELECT  'AR' ,
                'Arkansas'
        UNION
        SELECT  'CO' ,
                'Colorado'
        UNION
        SELECT  'WA' ,
                'Washington';

CREATE TABLE TableA

(

ID INT IDENTITY(1, 1) ,

[Key] NVARCHAR(255) ,

Value NVARCHAR(4000) ,

CreatedDateTime DATETIME2 DEFAULT GETUTCDATE() ,

CreatedBy NVARCHAR(50) DEFAULT CURRENT_USER ,

Status TINYINT DEFAULT 1

);

CREATE TABLE TableB

(

ID INT IDENTITY(1, 1) ,

[Key] NVARCHAR(255) ,

Value NVARCHAR(4000) ,

CreatedDateTime DATETIME2 DEFAULT GETUTCDATE() ,

CreatedBy NVARCHAR(50) DEFAULT CURRENT_USER ,

Status TINYINT DEFAULT 1

);

INSERT INTO dbo.TableA

( [Key] ,

Value

)

SELECT 'AL' ,

'Alabama'

UNION

SELECT 'AK' ,

'Alaska'

UNION

SELECT 'AZ' ,

'Arizona'

UNION

SELECT 'CA' ,

'California'

UNION

SELECT 'CO' ,

'Colorado'

UNION

SELECT 'WA' ,

'Washington';

INSERT INTO dbo.TableB

( [Key] ,

Value

)

SELECT 'FL' ,

'Florida'

UNION

SELECT 'DE' ,

'Delaware'

UNION

SELECT 'CT' ,

'Connecticut'

UNION

SELECT 'AR' ,

'Arkansas'

UNION

SELECT 'CO' ,

'Colorado'

UNION

SELECT 'WA' ,

'Washington';

LEFT JOIN : Select records from the first (left-most) table with matching right table records.

SELECT  A.[Key] ,
        A.Value ,
        B.[Key] ,
        B.Value
FROM    dbo.TableA A
        LEFT JOIN dbo.TableB B ON A.[Key] = B.[Key];

SELECT  A.[Key] ,
        A.Value ,
        B.[Key] ,
        B.Value
FROM    dbo.TableA A
        LEFT JOIN dbo.TableB B ON A.[Key] = B.[Key]
		WHERE B.[Key] IS NULL

SELECT A.[Key] ,

A.Value ,

B.[Key] ,

B.Value

FROM dbo.TableA A

LEFT JOIN dbo.TableB B ON A.[Key] = B.[Key];

SELECT A.[Key] ,

A.Value ,

B.[Key] ,

B.Value

FROM dbo.TableA A

LEFT JOIN dbo.TableB B ON A.[Key] = B.[Key]

WHERE B.[Key] IS NULL

RIGHT JOIN : Select records from the second (right-most) table with…

Run SQL Scripts using PowerShell

Posted on September 11, 2014 by

Raju Venkataraman Posted in PowerShell, SQL Server — No Comments ↓

Run SQL Scripts using PowerShell We all have the need to run SQL Scripts against Multiple Servers on Daily Basis. Sometimes, Its cumbersome to manually run the scripts against the server using Management Studio. This powershell Script comes handy to…

Month: September 2014

How do I Change SQL Database from Single_User to Multi_User Mode

Nissan Murano 2010 Rack and Pinion Cost

SQL Server – Generic Error Handling I use

PHP Drivers for MSSQL Issues

SQL Script Header Footer I use

Checklist for SQL Server Installation

Helpful Linux Commands

Git Cheat Sheet

MySQL and phpMyAdmin Setup

Install S3CMD tools on EC2 Instance.

Setup EC2 Instance with NGINX and PHP-FPM

SQL Server – Interview Questions

NGINX – SSL Certificate Setup

xp_cmdshell command to Add User

Connect to SQL Server When SA or Service Account Credentials not Available

Move SQL Server Database to Different Location

SQL Server Waits and Queues

Nginx Config for Perma Links

SQL Join Diagram and Query

Run SQL Scripts using PowerShell

Recent Posts

Categories

Recent Posts

Archives