kettle - Filter step not working initialize both input steps

2015/12/30 17:57:54 - Get Variables.0 - Finished processing (I=0, O=0, R=1, W=1, U=0, E=0)
2015/12/30 17:57:54 - Table input.0 - Finished reading query, closing connection.
2015/12/30 17:57:54 - Table input 2.0 - ERROR (version 5.4.0.1-130, build 1 from 2015-06-14_12-34-55 by buildguy) : Unexpected error
2015/12/30 17:57:54 - Table input 2.0 - ERROR (version 5.4.0.1-130, build 1 from 2015-06-14_12-34-55 by buildguy) : org.pentaho.di.core.exception.KettleDatabaseException: 
2015/12/30 17:57:54 - Table input 2.0 - An error occurred executing SQL: 
2015/12/30 17:57:54 - Table input 2.0 - SELECT **********************
2015/12/30 17:57:54 - Table input 2.0 - Invalid object name ''.
2015/12/30 17:57:54 - Table input 2.0 - 
2015/12/30 17:57:54 - Table input 2.0 - at org.pentaho.di.core.database.Database.openQuery(Database.java:1722)
2015/12/30 17:57:54 - Table input 2.0 - at org.pentaho.di.trans.steps.tableinput.TableInput.doQuery(TableInput.java:224)
2015/12/30 17:57:54 - Table input 2.0 - at org.pentaho.di.trans.steps.tableinput.TableInput.processRow(TableInput.java:138)
2015/12/30 17:57:54 - Table input 2.0 - at org.pentaho.di.trans.step.RunThread.run(RunThread.java:62)
2015/12/30 17:57:54 - Table input 2.0 - at java.lang.Thread.run(Thread.java:745)
2015/12/30 17:57:54 - Table input 2.0 - Caused by: java.sql.SQLException: Invalid object name '******'.
2015/12/30 17:57:54 - Table input 2.0 - at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:368)
2015/12/30 17:57:54 - Table input 2.0 - at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2820)
2015/12/30 17:57:54 - Table input 2.0 - at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2258)
2015/12/30 17:57:54 - Table input 2.0 - at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:632)
2015/12/30 17:57:54 - Table input 2.0 - at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java:477)
2015/12/30 17:57:54 - Table input 2.0 - at net.sourceforge.jtds.jdbc.JtdsStatement.executeQuery(JtdsStatement.java:1304)
2015/12/30 17:57:54 - Table input 2.0 - at org.pentaho.di.core.database.Database.openQuery(Database.java:1711)
2015/12/30 17:57:54 - Table input 2.0 - ... 4 more
2015/12/30 17:57:54 - mock_transformation - ERROR (version 5.4.0.1-130, build 1 from 2015-06-14_12-34-55 by buildguy) : Errors detected!
2015/12/30 17:57:54 - Table input.0 - Finished processing (I=7, O=0, R=0, W=7, U=0, E=0)
2015/12/30 17:57:54 - Table input 2.0 - Finished reading query, closing connection.
2015/12/30 17:57:54 - Filter rows 2.0 - Finished processing (I=0, O=0, R=1, W=0, U=0, E=0)
2015/12/30 17:57:54 - Table input 2.0 - Finished processing (I=0, O=0, R=0, W=0, U=0, E=1)
2015/12/30 17:57:54 - mock_transformation - Transformation detected one or more steps with errors.
2015/12/30 17:57:54 - mock_transformation - Transformation is killing the other steps!

Problem
(can say property of kettle)
All the process in pentaho kettle initialized in parallel. If you are having steps (Table input, table output, etc) in your transformation. When you execute the ktr, all of these steps are initialized all together.

"Execute SQL Script" step in Pentaho Kettle is self triggering. It gets executed at the initialization phase of the transformation.

Solution
Use job level steps for data filtration and sql execution

for me its works around
filter rows - "Simple Evaluation"

table_input - "Evaluate rows number in a table"

Caused by: java.sql.SQLRecoverableException: No more data to read from socket at oracle.jdbc.driver.T4CMAREngine.unmarshalUB1(T4CMAREngine.java:1200)

Caused by: java.sql.SQLRecoverableException: No more data to read from socket at oracle.jdbc.driver.T4CMAREngine.unmarshalUB1(T4CMAREngine.java:1200)

C:\ProgramData\Oracle\Java\javapath;
E:\app\gursingh\product\11.2.0\dbhome_1\bin;
C:\Program Files (x86)\Intel\iCLS Client\;
C:\Program Files\Intel\iCLS Client\;%SystemRoot%\system32;%SystemRoot%;%SystemRoot%\System32\Wbem;%SYSTEMROOT%\System32\WindowsPowerShell\v1.0\;
C:\Program Files (x86)\Intel\OpenCL SDK\2.0\bin\x86;C:\Program Files (x86)\Intel\OpenCL SDK\2.0\bin\x64;
C:\Program Files\Intel\Intel(R) Management Engine Components\DAL;C:\Program Files\Intel\Intel(R) Management Engine Components\IPT;
C:\Program Files (x86)\Intel\Intel(R) Management Engine Components\DAL;
C:\Program Files (x86)\Intel\Intel(R) Management Engine Components\IPT;
C:\Program Files\Java\jdk1.7.0_65\bin\;C:\Program Files (x86)\MySQL\MySQL Server 5.6\bin;
C:\Program Files\TortoiseGit\bin;C:\Program Files (x86)\Skype\Phone\;
C:\Program Files\Microsoft SQL Server\110\Tools\Binn\;E:\a_installation\SQLServer\110\Tools\Binn\;C:\Program Files\Microsoft SQL Server\110\DTS\Binn\;
E:\a_installation\SQLServer\110\Tools\Binn\ManagementStudio\;
C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\PrivateAssemblies\;E:\a_installation\SQLServer\110\DTS\Binn\


I faced this error while using kettle while transformation execution
Steps in transformation

1. Table Input (Get date (data_type - DATE) from some table, this date will pass in next "table input")
2. Table Input (Simple select query with where clause "where coalesce(Created_date,modified_date) > coalesce(to_date(?,'any format'),to_date('any default date','any date format')" )

Above error populate at second step while conversion of date type string,
I have googled it, as a solution i found it is a driver issue, but for me its not a driver issue, it is data-type conversion error.

Solution:
As a solution i have added one more step in transformation:

1. Table Input (Get date (data_type - DATE) from some table, this date will pass in next "table input")
2. "Select values" [in "metadata" tab - change datatype of date to "STRING"]
3. Table Input (Simple select query with where clause "where coalesce(Created_date,modifieddate) > coalesce(to_date(?,'any format'),to_date('any default date','any date format')" )

ORA-01704: string literal too long

create table test (sno number , textstring clob );

insert into test values(1,'PASTE YOUR STRING HERE');

insert into test values(1,'
                          *
ERROR at line 1:
ORA-01704: string literal too long


Problem
The string you are trying to insert is more than of 4000 char. As SQL datatype Char,Varchar, nvarchar and clob have limit of 4000 chars where as on other hand PLSQL have char limit of 32000

Solution
Use PLSQL to insert long length string


DECLARE
    v_string CLOB;
BEGIN
    v_string := 'PASTE YOUR STRING HERE';

    INSERT INTO test VALUES (1, v_string);
END; 
/
PL/SQL procedure successfully completed.

Fill null values with previous values, until next come in the row

want to fill the null values with previous name ('Orange' and 'mangoes') after spending few time i have build a query for this same.

Scenario - 1
Mean while i also found one function which can ease your work with the help of "last_value".
feasibility version - 10g, 11g 12c

Scenario - 2
feasibility version - all versions of Oracle

select 
  id, 
  substr(max(name_copy) over (order by id),8) name_copy ,
  value,
  city
from (
select 
  id, 
  value, 
  city,
  case when name is not null then to_char(row_number() over (order by id),'fm0000000') || name end name_copy  from test
)

Change orientation of table, (Rotate table) rows to column

Some time we also use the terms to "denormalize rows" / "row flattening", here is sample query which can gave the fare idea to accomplish this task with the help of query.

Actual result:

select row_number() over(partition by deptno  order by 1) as rn, ename,deptno
from emp
where deptno = 10
/

   RN ENAME                    DEPTNO
----- -------------------- ----------
    1 CLARK                        10
    2 KING                         10
    3 MILLER                       10

Required result:

1  with t as
  2  (
  3     select
  4             row_number() over(partition by deptno  order by 1) as rn,
  5             ename,
  6             deptno
  7     from emp
  8     where deptno = 10)
  9  select ename1,ename2,ename3 from
 10  (
 11  (select ename  as ename1 ,deptno  from t where rn = 1) e1
 12  left outer join
 13  (select ename  as ename2 ,deptno from t where rn = 2) e2 on e2.deptno= e1.deptno
 14  left outer join
 15* (select ename  as ename3 ,deptno from t where rn = 3) e3 on e3.deptno= e1.deptno)
SQL> /

ENAME1               ENAME2               ENAME3
-------------------- -------------------- --------------------
CLARK                KING                 MILLER

LISTAGG function giving extra spaces in result

Lets reproduce the error:

create table emp1 as select * from emp;

alter table emp modify ename nvarchar2(20);

SQL> SELECT deptno, LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS employees
  2   FROM emp GROUP BY deptno;

    DEPTNO EMPLOYEES
---------- --------------------------------------------------------------------------
        10  C L A R K, K I N G, M I L L E R
        20  A D A M S, F O R D, J O N E S, S C O T T, S M I T H
        30  A L L E N, B L A K E, J A M E S, M A R T I N, T U R N E R, W A R D

CAUSE
LISTAGG function returns varchar2, if we execute query on column of type nvarchar2 then value will startimplicit conversion to varchar2.

SOLUTION
use LISTAGG function always on column of type VARCHAR.

Share files with Virtual machine

Some case arise when some body is working on Virtual Machine like we need to copy large data onto the virtaul machine if machine specially is Linux.

How to share folder with linux/unix?, if yiu are using VMWare then it is pretty simple.

Some persons are using SAMBA server to access share folder on virtual machines installed with UNIX/LINUX server.

What is required:-
Before proceeding further you should have linux OS, installed with GCC/GCC+ and dependent libraries rpm's. While fresh installation of OS these RPM will be available in "Development kit".

After this install "Install VMWare Tools".
How to install:

• Move to VM >Install VMWare Tools.
• you can see *.tar file will open
• Move and extract it on desktop
• Double click on *.pl file and follow the steps and accept all the paths.

1. Virtaul machine should be in Power Off mode.
2. Open setting for virtual machine.

3. Enable the "Shared Folder" option under "option" tab.
4. Add any folder from local drive. as below in image

5. Share directory you can find under "\mnt\hgfs\".

Thanks for follow up!!!!!!!! like if it works

ORA-00054: resource busy and acquire...

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

Problem:
This error comes because some times two different sessions are performing DML tasks (delete, update and insert) on same column/table. This scenario some times leads to object locking in database.

Solution:

BEGIN
FOR I IN (SELECT S.SID SID, S.SERIAL# SERIAL FROM V$LOCKED_OBJECT L, V$SESSION S WHERE L.SESSION_ID = S.SID) LOOP
   EXECUTE IMMEDIATE ('ALTER SYSTEM KILL SESSION ''' || I.SID || ',' || I.SERIAL || '''');
END LOOP;
END;
/

Some Oracle Basic Queries

Parent Child relation:
to fetch parent/child tablename/columnname using table user_constraints and user_cons_columns

select  cc.column_name as column_name, c.table_name as table_name, rc.column_name as pcolumn_name, r.table_name as ptable_name
from    user_constraints c,
        user_constraints r,
        user_cons_columns cc,
        user_cons_columns rc
where   c.constraint_type = 'R'
and     c.constraint_name = cc.constraint_name
and     r.constraint_name = rc.constraint_name
and     c.r_constraint_name = r.constraint_name
and     cc.position = rc.position
and     c.table_name = 'child_table_name';

Open office dictionary missing / Not working

Open office document all showing red or underline because of spell check grammer is not working.

Issue - missing grammer for open office
Solution - add eglish grammer or any other grammer you want to add
how :

- Go to "Tool" open - "Extension Manager"
- Click on "Get more extension online"
- download the dictionary you want to add
- Downloaded file should have extension of OTX
- Now again move to "Extension Manager" window, click on "Add" button.
- Pop will open, and locate the downloaded file
oops
error

C:\Users\*****\AppData\Roaming\OpenOffice\4\user\uno_packages\cache\uno_packages\sv4ktqd.tmp_\dict-en.oxt does not exists.

- Move the path given by error
C:\Users\*****\AppData\Roaming\OpenOffice\4\user\uno_packages\cache\uno_packages

- What you will observe that the folder "sv4ktqd.tmp_" is missing.
- If yes, create new folder with same name and copy/paste downloaded file to same folder
- Now again move to pop-up window "Extension Manager"
- Now click on add button and locate the ****.otx file from above path.

ORA-12519, TNS:no appropriate service handler found

Error
Error connecting to database: (using class oracle.jdbc.driver.OracleDriver)
Listener refused the connection with the following error:
ORA-12519, TNS:no appropriate service handler found

Error occured while trying to connect to the database


Reason
Listener is up and running
The listener could not find any available service handlers that are appropriate for the client connection. (from oracle)

Solution
Execute below commands using sys user.
alter system set processes=300 scope=spfile;
shu immediate
startup

Error retriving the viewpoint list

Error
[http-bio-8080-exec-2] 04 Sep 2015 16:21:29,366 ERROR it.eng.spagobi.analiticalmodel.document.service.ExecuteBIObjectModule.getSubObjectsList:876 - Error retriving the subObject list
java.lang.NullPointerException
at it.eng.spagobi.analiticalmodel.document.dao.SubObjectDAOHibImpl.getAccessibleSubObjects(SubObjectDAOHibImpl.java:57)
at it.eng.spagobi.analiticalmodel.document.service.ExecuteBIObjectModule.getSubObjectsList(ExecuteBIObjectModule.java:873)
at it.eng.spagobi.analiticalmodel.document.service.ExecuteBIObjectModule.initNewExecutionHandler(ExecuteBIObjectModule.java:280)
at it.eng.spagobi.analiticalmodel.document.service.ExecuteBIObjectModule.service(ExecuteBIObjectModule.java:138)
at it.eng.spago.dispatching.module.DefaultPage.invokeServiceBusiness(DefaultPage.java:352)
at it.eng.spago.dispatching.module.DefaultPage.nextStep(DefaultPage.java:302)
at it.eng.spago.dispatching.module.DefaultPage.service(DefaultPage.java:202)
at it.eng.spago.dispatching.module.ModuleCoordinator.service(ModuleCoordinator.java:102)
at it.eng.spago.dispatching.httpchannel.AdapterHTTP.service(AdapterHTTP.java:425)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:727)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at it.eng.spagobi.utilities.filters.AntiInjectionFilter.doFilter(AntiInjectionFilter.java:33)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at it.eng.spagobi.commons.filters.ProfileFilter.doFilter(ProfileFilter.java:119)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at it.eng.spagobi.commons.filters.SpagoBICoreCheckSessionFilter.doFilter(SpagoBICoreCheckSessionFilter.java:82)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at it.eng.spagobi.utilities.filters.EncodingFilter.doFilter(EncodingFilter.java:42)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:220)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:122)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:504)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:170)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:950)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:421)
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1074)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:611)
at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:314)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.lang.Thread.run(Thread.java:745)
[http-bio-8080-exec-2] 04 Sep 2015 16:21:29,448 ERROR it.eng.spagobi.analiticalmodel.document.service.ExecuteBIObjectModule.getViewpointList:1597 - Error retriving the viewpoint list
java.lang.NullPointerException
at it.eng.spagobi.analiticalmodel.document.dao.ViewpointDAOHimpl.loadAccessibleViewpointsByObjId(ViewpointDAOHimpl.java:412)
at it.eng.spagobi.analiticalmodel.document.service.ExecuteBIObjectModule.getViewpointList(ExecuteBIObjectModule.java:1595)
at it.eng.spagobi.analiticalmodel.document.service.ExecuteBIObjectModule.initNewExecutionHandler(ExecuteBIObjectModule.java:284)
at it.eng.spagobi.analiticalmodel.document.service.ExecuteBIObjectModule.service(ExecuteBIObjectModule.java:138)
at it.eng.spago.dispatching.module.DefaultPage.invokeServiceBusiness(DefaultPage.java:352)
at it.eng.spago.dispatching.module.DefaultPage.nextStep(DefaultPage.java:302)
at it.eng.spago.dispatching.module.DefaultPage.service(DefaultPage.java:202)
at it.eng.spago.dispatching.module.ModuleCoordinator.service(ModuleCoordinator.java:102)
at it.eng.spago.dispatching.httpchannel.AdapterHTTP.service(AdapterHTTP.java:425)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:727)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at it.eng.spagobi.utilities.filters.AntiInjectionFilter.doFilter(AntiInjectionFilter.java:33)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at it.eng.spagobi.commons.filters.ProfileFilter.doFilter(ProfileFilter.java:119)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at it.eng.spagobi.commons.filters.SpagoBICoreCheckSessionFilter.doFilter(SpagoBICoreCheckSessionFilter.java:82)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at it.eng.spagobi.utilities.filters.EncodingFilter.doFilter(EncodingFilter.java:42)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:220)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:122)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:504)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:170)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:950)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:421)
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1074)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:611)
at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:314)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.lang.Thread.run(Thread.java:745)


Cause : Unknown


Solution
1. Delete "idx" directory from "apache-tomcat-7.0.59\resources\idx", it can contain stale index information, so let spagobi to rebuild the indexes.

2. I have fixed url as "ip:port/SpagoBI" in "server.xml" file
ex 192.168.1.1:8080/SpagoBI and i opened the SpagoBI on localhost instead of using IP.

So use correct URL you have defined in "server.xml" file.

3. I was having trouble with Chrome browser, so i switch to Firefox browser.

Sqldeveloper Startup error

---------------------------
Oracle SQL Developer
---------------------------
Unable to find a Java Virtual Machine.
To point to a location of a Java Virtual Machine, please refer to the Oracle9i JDeveloper Install Guide (jdev\install.html).
---------------------------
OK  
---------------------------

Problem:
Your machine might be 64 bit and JDK "java.bin" you are point out, that mean you have installed with 64bit JDK.

Solution:
Deinstall 64 bit JDK java and install 32 bit JDK.


How To reset the pop error by sqldeveloper?
Go to Path "\app\******\product\11.2.0\dbhome_1\sqldeveloper\sqldeveloper\bin\sqldeveloper.conf"

Go to last line and "Edit the path for SetJavaHome accordingly".

You can edit sqldeveloper.conf file if you open notepad with administrator rights only.

Search String in all tables Sql Server

Anonymous block which can search any string from all tables. This block is SQL Server specific.

declare @SearchString nvarchar(100);
SET @SearchString = 'any string want to search';
BEGIN
 IF OBJECT_ID('tempdb..#StrResults') IS NOT NULL DROP TABLE #StrResults
    CREATE TABLE #StrResults (ColumnName nvarchar(100), ColumnValue nvarchar(500))

    SET NOCOUNT ON

    DECLARE @TableName nvarchar(100), @ColumnName nvarchar(100), @SearchString2 nvarchar(100)
    SET  @TableName = ''
    SET @SearchString2 = QUOTENAME('%' + @SearchString + '%','''')

    WHILE @TableName IS NOT NULL

    BEGIN
        SET @ColumnName = ''
        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
        )

        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    DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'int', 'decimal')
                    AND    QUOTENAME(COLUMN_NAME) > @ColumnName
            )

            IF @ColumnName IS NOT NULL

            BEGIN
                INSERT INTO #StrResults
                EXEC
                (
                    'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 500) 
                    FROM ' + @TableName + 'WITH (NOLOCK) ' +
                    ' WHERE ' + @ColumnName + ' LIKE ' + @SearchString2
                )
            END
        END    
    END

    SELECT ColumnName, ColumnValue FROM #StrResults
END

To make above Anonymous block as named procedure just replace first two lines of procedure with below lines:

CREATE PROC search_string
(
@SearchString nvarchar(100)
)
AS

Invalid length parameter passed to the substring function

Cause

This is because of negative length parameter pass to substring function.

select SUBSTRING('abc' ,1,charindex('@','abc')-1)

In above query, @ char is not find in string "abc", hence charindex return "-1" as value for length parameter.
So it cause error:
Server: Msg 536, Level 16, State 3, Line 4
Invalid length parameter passed to the substring function.

Solution:

We can use absolute "abs" function for resolution of above problem. In below examples, we are using "abs" over the charindex function so that it will always return positive length parameter to substring/left/right.

select SUBSTRING('abc @ 3' ,1,charindex('@','abc @ 3')-2)

select SUBSTRING('abc @ 3' ,1,abs(charindex('@','abc @ 3')-2))



OR

Workaround can be using reverse function:
1. first reverse the string using "reverse" function
2. then substring the string using all positive length parameter
e.x.
select  reverse(
substring(reverse('abc @ 3'), charindex('@',reverse('abc @ 3'))+2,len('abc @ 3'))
)

3. again reverse the string, to bring string in actual shape.

Universe Design tool, Unable to connect Datasource

1. Open Universe Design tool and open project efashion universe
2. I click on "detect join"
or From Webi facing below error:

Error

Database error: [Microsoft] [ODBC Driver Manager] Data source name not found and no default driver specified.
(IES 10901) (WIS 10901)

Problem:

No correct database connection defined to the universe

Solution

Go to File, click on parameters.

Create a new connection or edit existing connection pointing to the correct database.

It works!!

Universe Design Tool - Oracle jdbc connection error

Facing error while creating jdbc connection with oracle from "Universe Design Tool"

1. Open "Universe Design Tool" and create a new connection.

2. Fill correct details about connection and test it.

Error:

CS: Java Class not found in classpath : oracle.jdbc.OracleDriver

Cause : Missing JDBC driver 

Resolve:

1. Copy Oracle JDBC driver from oracle server/client installtion path and paste it somewhere on machine at safe place.
oracle installation path - "\app\******\product\11.2.0\dbhome_1\jdbc\lib"
or
Download the ojdbc driver from internet

It is good practice to paste the jdbc driver in C drive "C:\Users\******"

2. Now move to the installation directory of BO
"SAP BusinessObjects Enterprise XI 4.0\dataAccess\connectionServer\jdbc\"

3. Edit the file "oracle.sbo", open with notepad++.

Edit tab classpath, add new line highligheted in yellow color, which is pointing to the ojdbc driver (place in c drive as above).

4. Restart "universe design tool"

5. Try to create same connection again.

Print Value if No Result return by query

select coalesce(nullif(salary,-1),4) as c1 from emp;

coalesce - these function work and check data at row level.

It will ceck for each row that if column salary is null then replace value will be replcaed by 4.

Upto here it is fine for me, if any body have question in mind then they can comment on the post.

But My requirement was that if query return no result/ or zero rows then i want to print some default value.
Now as per requirement scenario has been get changed, earlier i was using coalesce function which is working at row level, now i need to control null value at dataset level

see below:
select coalesce(nullif(salary,-1),4) as c1 from emp
union
select '4'  as c1
 

BO1 Prerequisite Window Version Failed

Information: This product needs to be installed on Windows Server 2008 or a higer Windows Server Version. please verify that your version of Windows meets these requirements.

Install SAL BO 4.1 on Windows 7. Yes this is possible , u need to change one file "product.seed.xml"

• First of all extract 51046778 folder and move to below specified path 

"..\51046778\DATA_UNITS\BusinessObjectsServer_win\dunit\product.businessobjects64-4.0-core-32"

• Now open file  "product.seed.xml"
• Move to line number 1520

• Change parameter value from "server" to "Workstation"
• Save and close
• Now reinstall.

Thanks!!!!!!

The multi-part identifier could not be bound.

Error:
Msg 4104, Level 16, State 1, Line 28
The multi-part identifier "idd.key" could not be bound.

Reason:
This Error Comes because the alias you are using in query is invalid/ Wrongly text type/ or not define.

Solution:
Check that alias you are using in projection and selection is available or not.

select e.ename, d.dname
from emp e
join
dept dd
on e.deptno = d.deptno

From above example you can see that the in Projection/Selection we are using "d" alias, where as on other hand we have defined the "dd" alias of table "dept".
 

SQLSERVER Pass Variable in Dynamic SQL

create procedure dummy_ins
(
@vid nvarchar(30),
@vname nvarchar(30)
)
as
set nocount on
declare @temp_sql nvarchar(1000)
declare @symmtric_key AS [uniqueidentifier]
set @symmtric_key = KEY_GUID('Symmetric_Key1')
set @temp_sql = 'insert into dummy values (' + @vid  +','+ 'ENCRYPTBYKEY(@symmtric_key,''' + @vname+ '''))'
print @temp_sql
exec sp_executeSQL @temp_sql

exec dummy_ins '3','sdf'

ERROR:
Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@symmtric_key".

PROBLEM:
print of command - insert into dummy values (3,ENCRYPTBYKEY(@symmtric_key,'sdf'))
when sp_executeSQL execute the sql query, it found "@symmtric_key" on the way so why it is throwing error.

That mean we have enclosed the variable with single quote in sql, so the variable define is unable to pass the value.

SOLUTION:
Remove the single quotes around @symmtric_key like 'ENCRYPTBYKEY(KEY_GUID('''+ cast(@symmtric_key as nvarchar(30)) +'''),'''

see below example:
create procedure dummy_ins
(
@vid nvarchar(30),
@vname nvarchar(30)
)
as
set nocount on
declare @temp_sql nvarchar(1000)
declare @symmtric_key AS [uniqueidentifier]
set @symmtric_key = KEY_GUID('Symmetric_Key1')
set @temp_sql = 'insert into dummy values (' + @vid  +','+ 'ENCRYPTBYKEY(KEY_GUID('''+ @symmtric_key +'''),''' + @vname+ '''))'
print @temp_sql
exec sp_executeSQL @temp_sql,'@p as uniqueidentifier', @p = @symmtric_key

exec dummy_ins '14','sdf'
ERROR:
cast( guid as varchar)
Msg 402, Level 16, State 1, Procedure dummy_ins, Line 11
The data types nvarchar and uniqueidentifier are incompatible in the add operator.

From above error i feel that it will not pass key is not acceptable in this way.

SOLUTION:
correct way is here to pass variable :
see below post
SqlServer Procedure expects parameter '@' of type 'ntext/nchar/nvarchar' 

or
click on below link
http://j4info.blogspot.in/2015/06/sqlserver-procedure-expects-parameter.html

SqlServer Procedure expects parameter '@' of type 'ntext/nchar/nvarchar'

create procedure dummy_ins
(
@vid nvarchar(30),
@vname nvarchar(30)
)
as
set nocount on
declare @temp_sql nvarchar(1000)
declare @p as nvarchar(30)
set @p = 'Symmetric_Key1'
set @temp_sql = 'insert into dummy values (' + @vid  +','+ 'ENCRYPTBYKEY(KEY_GUID('''+ cast(@p as nvarchar(30)) +'''),''' + @vname+ '''))'
print @temp_sql
exec sp_executeSQL @temp_sql,'@p nvarchar(30)', @p = @p

exec dummy_ins '14','sdf'

error :
Msg 214, Level 16, State 3, Procedure sp_executesql, Line 1
Procedure expects parameter '@parameters' of type 'ntext/nchar/nvarchar'.

Problem:
Parameter value for procedure "sp_exectesql" should be in nvarchar, parameter is '@p nvarchar(30)'
and missing prefix N

solution:
create procedure dummy_ins
(
@vid nvarchar(30),
@vname nvarchar(30)
)
as
set nocount on
declare @temp_sql nvarchar(1000)
declare @p as nvarchar(30)
set @p = 'Symmetric_Key1'
set @temp_sql = 'insert into dummy values (' + @vid  +','+ 'ENCRYPTBYKEY(KEY_GUID('''+ cast(@p as nvarchar(30)) +'''),''' + @vname+ '''))'
print @temp_sql
exec sp_executeSQL @temp_sql,N'@p nvarchar(30)', @p = @p


Related POSt
SQL server encrypt data at column level
http://j4info.blogspot.in/2015/06/sql-server-encrypt-data-at-column-level.html

SQL server error : Could not find stored procedure

Error:
Msg 2812, Level 16, State 62, Procedure dummy_ins, Line 13
Could not find stored procedure 'insert into dummy values (1,ENCRYPTBYKEY(key_guid(@symmtric_key),'sdf')'.

Reason:
I created stored procedure and was executing dynamic sql query in SP

set @temp_sql = 'insert into dummy values (' + @vid +','+ 'ENCRYPTBYKEY(key_guid(@symmtric_key),''' + @vname+ ''')'
print @temp_sql
exec  @temp_sql
Here i am using "exec @temp_sql", for sqlserver it is equivalent to call procedure, so why it is giving above error.

Solution:
use keyword "sp_executeSQL" as see below:
exec sp_executeSQL @temp_sql

sqlserver Cannot specify a column width on data type int

error:
Msg 2716, Level 16, State 1, Procedure dummy_ins, Line 2
Column, parameter, or variable #1: Cannot specify a column width on data type int.

Problem :
i have created a stored procedure in sqlserver and accidently i have define size of int

create procedure dummy_ins
(
@vid int(30),

Solution:
Remove the size of int datatype or use nvarchar type.
Error of the Level 16 are caused by the user and can be fixed by the SQL Server user.

SQL server encrypt data at column level

1. SQL server master key is the root of SQL server encryption hierarchy. Check it exists or not.

SELECT * FROM sys.symmetric_keys
WHERE name = '##MS_ServiceMasterKey##';

2. Create master key - "encrypt by password" argument, it defines the password used to encrypt the key. The DMK does not directly encrypt data, but provides the ability to create keys that are used for data encryption. keep the encryption password at safe place or you can take the backup of same.

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'PA55w04D!';
GO


CREATE CERTIFICATE encrypt_certificate
WITH SUBJECT = 'Encrypt Data';
GO


3. Create Symmetric Key it is used for both encryption and decryption.
CREATE SYMMETRIC KEY Symmetric_Key1 WITH ALGORITHM = AES_128 ENCRYPTION BY CERTIFICATE encrypt_certificate;
GO


create table dummy (id int , name varbinary(2000));

We need to open symmetric key before use:
OPEN SYMMETRIC KEY Symmetric_Key1 DECRYPTION BY CERTIFICATE encrypt_certificate;

insert into dummy values (1, EncryptByKey(Key_GUID('Symmetric_Key1'),'data'));

select * from dummy;

we we can decrypt the data in table?
SELECT CONVERT(varchar, DecryptByKey(name)) AS 'Decrypted name'

Window server service failed to start

Error:
Windows could not start the SQL Server (MSSQLSERVER) service on Local Computer.
Error 1069: The service did not start due to a logon failure.

Problem:
Windows login credential fail to start service of tool.

Solution:
- Open control panel - Services
- look for sqlserver service
- Right click PROPERTIES
- Goto LOGON tab
- Add your updated credential
- press ok and start service

SP14 Create Birt report and pass parameter in dataset define in SpagoBI server

Lets create a BIRT report using spagobi studio and main task is for me to pass parameter that is define in SpagoBI server

Follow the below steps to follow up:

• Start spagoBi server and Studio
• Create or define parameter using "Profile Attribute management"
• I added "deptname" see below image for ur reference

• Open spagoBi studio using spagoBI perspective only
• create "New Birt report"

• Switch your spagoBi studio to Report design perspective. You can see this option on very right top hand side of window.

• Create JDBC datasource connection. I created the connection with oracle server with scott user.

• Create parameter which needs to pass into query. Right click on "report parameter" and create new parameter. see below image.

• Create new data-set which contains the query pull data from database . In below image i have written simple query without any parameter pass.
• but you can replace the 'SALES' with "?", so that you can pass your parameter in query.
• After replace it with ?, 
• assign value to parameter 

• go to "parameter" on left hand side (see below image)

• Design the BIRT report layout, drop your dimension and measure from left hand side onto LAYOUT.

• Test your report on studio side by clicking on "preview" tab. after it will ask for enter parameter value popup. Enter value and press ok.

• Switch spagoBI studio to "SpagoBi" perspective.
• Deploy your report 

• Create LOV for deptname

• Test your LOV and check radio button and checkbox. (see below image)

• Create "Analytical driver" for LOV "deptname"

• Move to Folder to execute your report, that u had deployed recently.
• First we have to define "DOCUMENT ANALYTICAL DRIVER DETAILS" for your document.
• Click on properties icon on your document

• Save and Execute report.
• Quite easy SpagoBi, Nothing difficult . ENJOY