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

Saturday, April 29, 2017

CASE WHEN with between function in DAX


I used this nested IF function to create a new column based on the Sum Rain mm column.

Column = IF('Weather daily'[Sum Rain mm]<=3,"Dry" ,IF('Weather daily'[Sum Rain mm] > 3 && 'Weather daily'[Sum Rain mm] <= 1,"Rainy Day",IF('Weather daily'[Sum Rain mm]> 10 && 'Weather daily'[Sum Rain mm] <= 50,"Wet Day", IF('Weather daily'[Sum Rain mm] >= 50,"Heavy Rain") )))

Monday, April 24, 2017

Deleting Rows Based on a Table Lookup or CTE

There will be times when you’ll want to delete rows from a table based on data in another table. For example, suppose you want to delete rows from the SalesStaff table for only those salespeople who had no sales in the preceding year. However, the SalesStaff table does not contain this information, but the vSalesPerson view does.

DELETE SalesStaff
WHERE StaffID IN
  (
    SELECT BusinessEntityID
    FROM Sales.vSalesPerson
    WHERE SalesLastYear = 0
  );