Pentaho Kettle Error: Could not create the Java Virtual Machine.

 E:\pdi\data-integration>Spoon.bat

DEBUG: Using JAVA_HOME

DEBUG: _PENTAHO_JAVA_HOME=C:\Program Files (x86)\Java\jdk1.7.0_65

DEBUG: _PENTAHO_JAVA=C:\Program Files (x86)\Java\jdk1.7.0_65\bin\javaw.exe

E:\pdi\data-integration>start "Spoon" "C:\Program Files (x86)\Java\jdk1.7.0_65\bin\javaw.exe"  "-Xms1024m" "-Xmx2048m" "-XX:MaxPermSize=256m" "-Dhttps.protocols=TLSv1,TLSv1.1,TLSv1.2" "-Djava.library.path=libswt\win32" "-DKETTLE_HOME=." "-DKETTLE_REPOSITORY=" "-DKETTLE_USER=" "-DKETTLE_PASSWORD=" "-DKETTLE_PLUGIN_PACKAGES=" "-DKETTLE_LOG_SIZE_LIMIT=" "-DKETTLE_JNDI_ROOT=" -jar launcher\pentaho-application-launcher-6.0.1.0-386.jar -lib ..\libswt\win32

Error :

Error: Could not create the Java Virtual Machine.
Error: A fatal exception has occurred. Program will exit.

scenario :

Few hours before pentaho was running fine on machine, after changing the version of libraries, bat files and plugins i was facing above error.

So i was pretty sure about error is not related to OS version or java

Solution:

I decreased the Xmx1024m to Xmx512m in spoon.bat, it worked

Writing in Excel Using Excel Writer Step

 Failing During Writing in Excel while writing in excel , scenario can be as below

• 1 Transformation
• Multiple Excel Writer step
• All Excel Writer Step trying to write in 1 excel

What i noticed due to time or speed, if 1st step locked the excel to writing then 2nd excel writer step will failed to write. Which lead to fail whole transformation

How you can save yourself from your error, some how i found this after google it:

// for compatibility with Kettle 3.2 and 4.0: choose correct flag
var STATUS_FINISHED;
var STATUS_STOPPED;
var STATUS_HALTED;
var STATUS_HALTING;
if(STATUS_FINISHED == null) {
	try {
		// Kettle 3.2 flag
		STATUS_FINISHED = Packages.org.pentaho.di.trans.step.StepDataInterface.STATUS_FINISHED;
		STATUS_HALTING = Packages.org.pentaho.di.trans.step.StepDataInterface.STATUS_HALTING;
		STATUS_HALTED = Packages.org.pentaho.di.trans.step.StepDataInterface.STATUS_HALTED;
		STATUS_FINISHED = Packages.org.pentaho.di.trans.step.StepDataInterface.STATUS_FINISHED;
		STATUS_STOPPED = Packages.org.pentaho.di.trans.step.StepDataInterface.STATUS_STOPPED;
	} catch(e) {
		// Kettle 4.0 flag
		STATUS_FINISHED = Packages.org.pentaho.di.trans.step.BaseStepData.StepExecutionStatus.STATUS_FINISHED;
		STATUS_HALTING = Packages.org.pentaho.di.trans.step.BaseStepData.StepExecutionStatus.STATUS_HALTING;
		STATUS_HALTED = Packages.org.pentaho.di.trans.step.BaseStepData.StepExecutionStatus.STATUS_HALTED;
		STATUS_STOPPED = Packages.org.pentaho.di.trans.step.BaseStepData.StepExecutionStatus.STATUS_STOPPED;
	}
}
// this is the step that must complete before moving on
var waitForStep;
if(waitForStep == null) {
	waitForStep = _step_.getDispatcher().findBaseSteps("WritingObjectProperties").get(0);
}
// sleep until the step we wait for is done
while(waitForStep.getStatus() != STATUS_FINISHED && waitForStep.getStatus() != STATUS_STOPPED && waitForStep.getStatus() != STATUS_HALTING && waitForStep.getStatus() != STATUS_HALTED) {
	println("waiting for the step to finish");
	java.lang.Thread.currentThread().sleep(5);
}

Keep "Check" the checkboxinside step "Java Script"

See screenshot from reference 

Procedure | Compare Field Count and Distinct Count From a Database

 This procedure is for SQL Server, please take a idea and feel free to modify and use this procedure as per your need. This Procedure giving 3 fields in Output, 1st field giving column Name, 2nd field giving total count of records in table, and 3rd field having distinct records of a field.

CREATE TABLE #tempCount (
	TableName NVARCHAR(500)
	, AllTableCount NVARCHAR(500)
	, ColumnDistinctCount NVARCHAR(500)
	)

ALTER PROCEDURE countOfAField
AS
DECLARE @table_name NVARCHAR(50)
	, @column_name NVARCHAR(50)
	, @SQL_v NVARCHAR(500)

DECLARE cur_col CURSOR
FOR
SELECT TABLE_NAME
	, column_name
FROM INFORMATION_SCHEMA.COLUMNS
WHERE column_name = 'XXXXX'

OPEN cur_col

FETCH NEXT
FROM cur_col
INTO @table_name
	, @column_name

WHILE @@FETCH_STATUS = 0
BEGIN
	SET @SQL_v = 'Insert into #tempCount 
					Select 
						''' + @table_name + ''' as TableName
						, count(*) as AllTableCount 
						,count(distinct ' + @column_name + ') AS ColumnDistinctCount 
					from  ' + @table_name

	PRINT @SQL_v

	EXEC sp_executesql @SQL_v

	FETCH NEXT
	FROM cur_col
	INTO @table_name
		, @column_name
END

SELECT *
FROM #tempCount

CLOSE cur_col

DEALLOCATE cur_col

EXEC countOfAField

py0023 ValueError: not enough values to unpack

I am following "Learn Python 3 the Hard Way_ A Very Simple Introduction to the Terrifyingly Beautiful World of Computers and Code ( PDFDrive ).pdf" document to to read out basic concept of python.

At excercide 13 "ex13.py", tell how to pass variable in python script while executing from cmd.

However i am doing practie @pycharm, faced below error while excuting script.

Content in file 
from sys import argv
script, first, second, third = argv
print("The script is called:", script)
print("Your first variable is:", first)
print("Your second variable is:", second)
print("Your third variable is:", third)

Error while execution

C:\Users\******\AppData\Local\Programs\Python\Python37-32\python.exe C:/Users/******/PycharmProjects/learnPython/ex13.py
Traceback (most recent call last):
  File "C:/Users/******/PycharmProjects/learnPython/ex13.py", line 3, in <module>
    script, first, second, third = argv
ValueError: not enough values to unpack (expected 4, got 1)
Process finished with exit code 1

So conclusion is as i told i was executing script in pycharm, which was wrong approach.

Microsoft Windows [Version *********
(c) 2018 Microsoft Corporation. All rights reserved.
C:\Users\******>cd C:\Users\*****\AppData\Local\Programs\Python\Python37-32\
C:\Users\******\AppData\Local\Programs\Python\Python37-32>python.exe C:/Users/******/PycharmProjects/learnPython/ex13.py 1st 2nd 3rd
The script is called: C:/Users/******/PycharmProjects/learnPython/test.py
Your first variable is: 1st
Your second variable is: 2nd
Your third variable is: 3rd

1st, 2nd, 3rd is variable value we passed in python script during execution

SQL 17 Delete Duplicate from a table having single column

In requirement we have table having single column, no primary key and foreign key applied on table. It is easier to delete the duplicate data from table if it having primary key applied using CTE or row_number. But if in table no primary key, delete using row_number window function will work cause it will delete all the records from table.

Alternate is 

- we can create new temp table using select distinct Into 
- delete all records from old table
- reinsert distinct record from temp table

But in this case new table create and delete object not allowed to user.

SELECT *
INTO SQL17
FROM (
	SELECT 1 id
	
	UNION ALL
	
	SELECT 2
	
	UNION ALL
	
	SELECT 1
	
	UNION ALL
	
	SELECT 2
	
	UNION ALL
	
	SELECT 3
	
	UNION ALL
	
	SELECT 3
	
	UNION ALL
	
	SELECT 3
	
	UNION ALL
	
	SELECT 4
	) t

DELETE
FROM SQL17
WHERE %%physloc%% IN (
		SELECT loc
		FROM (
			SELECT %%physloc%% loc
				, *
				, row_number() OVER (
					PARTITION BY id ORDER BY id
					) rn
			FROM SQL17
			) t
		WHERE rn > 1
		)