Friday, October 6, 2017

How to convert from Julian date to date in SQL


DECLARE @jdejulian INT;
SET @jdejulian = 117258;
SELECT
    DATEADD(dd, CAST(RIGHT(RIGHT('0' + CAST(@jdejulian AS VARCHAR), 6),3) AS INT) - 1, DATEADD(yy, CAST(LEFT(RIGHT('0' + CAST(@jdejulian AS VARCHAR), 6),3) AS INT), 0));

Saturday, September 2, 2017

How to show list of unused SSRS reports for given number of days

USE ReportServer
GO
DECLARE @NotUsedDays INT

SELECT @NotUsedDays = 30

SELECT Name,Path,LastUsedDate,NotUsedsince=DATEDIFF(DD,LastUsedDate,GETDATE())
  FROM dbo.catalog C
  JOIN (
 SELECT ReportID,LastUsedDate= MAX(timestart)
FROM dbo.executionlog
  GROUP BY ReportID
  ) E
ON C.ItemID = E.ReportID
 WHERE DATEDIFF(DD,LastUsedDate,GETDATE()) >= @NotUsedDays
 ORDER BY NotUsedsince ASC

Monday, July 3, 2017

Execute SSRS Report Subscriptions Manually

/*Execute SSRS Subscription Manually*/
/*Connect to Database ReportServer*/
SELECT
S.ScheduleID AS SQLAgent_Job_Name     
,SUB.Description AS Sub_Desc     
,SUB.DeliveryExtension AS Sub_Del_Extension     
,C.Name AS ReportName     
,C.Path AS ReportPath
FROM ReportSchedule RS     
INNER JOIN Schedule S ON (RS.ScheduleID S.ScheduleID)     
INNER JOIN Subscriptions SUB ON (RS.SubscriptionID SUB.SubscriptionID)     
INNER JOIN [Catalog] C ON (RS.ReportID C.ItemID AND SUB.Report_OID C.ItemID)
WHERE     C.Name LIKE '' --Enter Report Name to find Job_Name
/*Connect to Database MSDB on the Reporting Server*//*Enter SQLAgent_Job_Name to execute the subscription based on Job ID*/
USE msdbEXEC sp_start_job @job_name '' Enter SQLAgent_Job_Name

Friday, June 23, 2017

Convert GETDATE() to YYYYMMDD

How to convert getdate() to YYYYMMDD:

SELECT CONVERT(VARCHAR(35),@date,112)

Search text in stored procedure in SQL Server

How to search a text from all my database stored procedures. 

SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES 
WHERE ROUTINE_DEFINITION LIKE '%Foo%' 
AND ROUTINE_TYPE='PROCEDURE'

Wednesday, June 21, 2017

What if REPLACE function is not working on sql table column

160 is Unicode NO-BREAK SPACE so that's what you need to replace:


replace(UserName, char(160), '')
,LTRIM(RTRIM((ISNULL(REPLACE(REPLACE(REPLACE(City,CHAR(9),''),CHAR(10),''),CHAR(13),''),'')))) AS City

Monday, June 12, 2017

How to apply Count on multiple distinct columns and use Having clause

If you group by these columns then you already only get those unique records and then you can use count(*) to get how many duplicates you have.
select A,B,C, count(*) 
from table_name 
group by A,B,C 
HAVING count(*) > 1