IBM notes is not opening

Hi friends,
New office and new mail system i,e IBM NOtes
System restarted and IBM notes was not opening.
To resolve this issue

Come to DOS, navigate to
C:\Program Files (x86)\IBM\Notes

then use NSD -Kill to stop all processes:

Restart IBM notes. it will work 

SQL query is taking too long as number of rows returned is huge volume

Hi Friends,
Today I faced a issue where the sql query was taking a long time to diplay data. This was because data in the main table had huge volume. The query is something like below

Select A.col1,A.col2,A.col3,A.col4,B.col1,B.col2,C.col1......
from TableA A
left join TableB B
on...
left join TableC C
on....
left Join TableD D
on...
Where
some filters
Date filter
Group By clause

To improve the performance we used CTE
i,e the Table A had 20 lakh records and each left join would take very long time

So a CTE was introduced first to get required data and then do left join

With CTE
As
(
Select A.col1,A.col2,A.col3,A.col4
from TableA A
Where Date Condition
)
Select CTE.col1, CTE.col2, CTE.col3 CTEA.col4,B.col1,B.col2,C.col1......
from TableA A
left join TableB B
on...
left join TableC C
on....
left Join TableD D
on...
Where
some filters
Date filter
Group By clause


The reason why this worked is because SQL query execution happens in the below order

From
Where
Select
GroupBY
OrderBy

So when we put filter in the initial from clause the number of rows returned were less . And then we can apply left joins 

Incorrect syntax near ',' in SQL dynamic query or error while passing multi value parameter to dynamic sql

Hi Friends
We encounter the below error when you have to pass multi value paramter in your dynamic sql query


Incorrect syntax near ',' in SQL dynamic query

Scenario

 Declare @SQLQuery AS NVarchar(4000)
 Declare @EN AS NVarchar(2000)
   

Set @EN = ('John Smith,John Micheal')


    Set @SQLQuery = 'Select * From tblEmployees where EmployeeName
IN (''''' +@EN  +''''')'

Exec (@SQLQuery)

We get the error Incorrect syntax near ',' in SQL dynamic query

SOlUTION - Declare another variable and then use replace function to handle the multi value parameters

 Declare @SQLQuery AS NVarchar(4000)
 Declare @EN AS NVarchar(2000)
   

Declare @Ename AS varchar(MAX)

Set @EName = ('John Smith,John Micheal')

SET @EN = REPLACE (@Ename,',',''',''')


    Set @SQLQuery = 'Select * From tblEmployees where EmployeeName
IN (''''' +@EN  +''''')'

Exec (@SQLQuery)


Super!! issue is fixed

Hyperion Report shows blank after installation on Windows 10

Hi Friends,
Today I faced the below issue


Hyperion Report was showing blank after installation on Windows 10.
To resolve this issue, we checked the version of Internet explorer. We understood that Hyperion does not work with the latest version of IE in windows 10

Hence we installed it on a different machine in which windows version supported older version of IE

Use MERGE JOIN statement in TSQL or dynamic sql query using Linked server and select statement

Hi Friends,
Today I had a chance to write a Merge join statement using dynamic query for linked server. The statement goes like below

Merge into targettable TT
using (Select * from ..openquery(select * from sourcetable)) ST
on tt.Date = SR.Date
When Matched
Update
When Not Matched
Insert. 

Apply date filter to your open query in sql

Hi Freinds,
Today I had issue in applying date filter to openquery. I could not apply getdate to the coulmn as it said getdate was not allowed. When I used cast, it said column has encountered null with the below message

"An unexpected NULL value was returned for column "[MSDASQL].DateAdded" from OLE DB provider "MSDASQL" for linked server"

To overcome this error, we first declared a date parameter. We had another column which accepted the date value in some other format i,e if date was 03/03/2019 then this column would store 43495(something like this, dont remember the exact value)
i,e if it is 
we then declared processing date as a paramter to accept the date value

Requirement is - Get only rows from source table in openquery where date equals todayand today minus1 or yesterday

Then we passed this date parameter in the query
'select * from openquery....where datecolumn >= ' +@ProcesingDate + '

Replace first occurrence of string in using Stuff in Date column SQl

Hi Friends,
Below is the scenario in which the date column had a blank value at the begining of date i,e instead of mm/dd/yyyy it is m/dd/yyyy . We have to replace the 0 value in m with 02 with blank 2 or space 2. but for months like 10,11,12 he value had to be retained, We have used case statement to handle this.





select * from table name u
where  u.Datecolumn between
case when left(convert (varchar,cast(@BeginDate as date),101),1) = '0' then
stuff(convert (varchar,cast(@BeginDate as date),101),1,1,' ') else
convert (varchar,cast(@BeginDate as date),101) end
And
case when left(convert (varchar,cast(@EndDate as date),101),1) = '0' then
stuff(convert (varchar,cast(@EndDate as date),101),1,1,' ') else
convert (varchar,cast(@EndDate as date),101) end

or

select * from table name u
where  u.Datecolumn  =
case when left(convert (varchar,cast('2018-02-01 11:37:45.167' as date),101),1) = '0' then
stuff(convert (varchar,cast('2018-02-01 11:37:45.167' as date),101),1,1,' ') else
convert (varchar,cast('2018-02-01 11:37:45.167' as date),101) end