[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

Hi Folks!
Okay so here is the error message which took lot many days to resolve from my IT team. But by the grace of God we were able to fix this issue by following the below steps

1. Ensure the tnsnames file or the notepad file in which you have defined the oracle connections names are correct. Even a single quotation mark difference will cause error.
So if the connection is working on a different computer, take the notepad contents from that system and paste in your system.
2. The same dSN would be present under 'USER DSN' . delete it where it is not required.
Usually Oracle uses "System dsn" and hence any duplicates in USER DSN can be deleted.

We found that the USER DSN had the same conncetion string but no drivers installed for that. when we deleted in USER DSN then it worked!!!

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

SSRS - display SQL Agent job running status in ssrs report

Hi friends
if you want to display sql server agent job status as running then make the below code changes first in stored procedure of your sql stored procedure


IF EXISTS(SELECT 1
          FROM msdb.dbo.sysjobs J
          JOIN msdb.dbo.sysjobactivity A
              ON A.job_id=J.job_id
          WHERE J.name=N'Your Job Name'
          AND A.run_requested_date IS NOT NULL
          AND A.stop_execution_date IS NULL
         )
    PRINT 'The job is running!'
ELSE
    PRINT 'The job is not running.'

Then come back to SSRS. Right click on properties of your tablix and find the field "No Rows Message". In this message enter SQL Agent job is running and hence no data is displayed