Saturday, January 9, 2016

SQLServer - Integer Format

Query1
select id from table_name

Query2
select cast(format(row_number() over (order by id)  ,'0000#') as varchar) from table_name

Results of above two query in below picture.




I ran same query in SQLServer Version 2008, as "format" function is not available in 2008, so below is the altrenative lof same query in SQLServer 2008 version.

Error
Msg 195, Level 15, State 10, Line 1
'format' is not a recognized built-in function name.


select RIGHT ('0000'+ CAST(row_number() over (order by id) AS varchar), 5) from tabe_name


Friday, January 8, 2016

SQLSERVER - licensed limit of 10240 MB per Databae

After failed for Database (Microsoft.SqlServer.Smo)

Error:
An Exception occured while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)

CREATE DATABASE or ALTER DATABASE failed because the resulting cumulative database
size would exceed you licensed limit of 10240 MB per Databae.
(Microsoft SQL Server, Error:1827)



Limitation:
If you are using MS SQL Server express edition you will not able to create database having size greater than 10 GB.

Thursday, January 7, 2016

Kettle log contains string - ERROR (version 5.4.0.1-130, build 1 from ** by buildguy)

ERROR (version 5.4.0.1-130, build 1 from ** by buildguy)

In kettle log i have found a string continuously printing until job completed.

How to remove this string from logs?


  • Open Job for which you are generating logs, (In my case i have another "Job step" in main job and generating separate logs for that job )
  • Double click on "Job Entry Step"
  • Popup can be seen (Heading - Executing a Job)
  • Move to tab "Logging Settings"
  • Change Loglevel from "Error Logging Only" to "Basic "

Friday, January 1, 2016

Table Input/Evaluate rows number in a table/Execute SQL step, On error abort all job sequence

This error is somewhere related to my previous post



kettle - Filter step not working initialize both input steps  link


picture 1
i have some set of queries as you can see in above picture highlighted in green box , bearing type of 's' and 't'. That mean i have to filter records with field "type"

If type - 'S' then - execute query with connection 1
If type - 'T' then - execute query with connection 2

In previous post i suggest to use step "Simple evaluation" at job level to filter rows, it works fine
but i faced some another condition on the way :  

REQUIREMENT
 I have to execute all the four queries with their respective type of connection, and log the result, i.e. query execution success and failure

OOPS what happen !!!!!!!!
look in the picture the query highlighted with yellow color having wrong table name, now it is obvious query got fails while execution and it leads to abort the whole job process.
OH IT NOT MET TO MY SCENARIO (scenario is log the result, for query execution success and failure)

SOLUTION
to met above condition i change the orientation of my data-set, as highlighted in RED BOX.
Set variable for each different column

Test Query at job level ..see below image(job in loop)



web stats