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