Latest Event Updates

SSRS Subscription List

Posted on Updated on

If you need to get all SSRS that has a Subscription.

SELECT SubscriptionOwner = u.UserName
,s.ModifiedDate
,s.[Description]
,s.EventType
,s.DeliveryExtension
,s.LastStatus
,s.LastRunTime
,sch.NextRunTime
,ScheduleName = sch.Name
,ReportName = c.Name
,ReportPath = c.[Path]
,ReportDescription = c.[Description]
FROM ReportServer.dbo.Subscriptions AS s
INNER JOIN ReportServer.dbo.Users AS u ON s.OwnerID = u.UserID
INNER JOIN ReportServer.dbo.[Catalog] AS c ON s.Report_OID = c.ItemID
INNER JOIN ReportServer.dbo.ReportSchedule AS rs ON s.Report_OID = rs.ReportID
AND s.SubscriptionID = rs.SubscriptionID
INNER JOIN ReportServer.dbo.Schedule AS sch ON rs.ScheduleID = sch.ScheduleID
ORDER BY u.UserName
,c.[Path];

This entry was posted in SSRS and tagged , .

Populate States Table

Posted on

I always find myself recreating a States table for web applications. Here it is.

CREATE TABLE [States](

[StatesID] [int] IDENTITY(1,1) NOT NULL,
[Abbreviation] [nchar](2) NOT NULL,
[Name] [nvarchar](128) NOT NULL,

CONSTRAINT [PK_States] PRIMARY KEY CLUSTERED
( [StatesID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY])
ON [PRIMARY]

GO

INSERT INTO [States] VALUES
(‘AL’, ‘Alabama’),
(‘AK’, ‘Alaska’),
(‘AZ’, ‘Arizona’),
(‘AR’, ‘Arkansas’),
(‘CA’, ‘California’),
(‘CO’, ‘Colorado’),
(‘CT’, ‘Connecticut’),
(‘DE’, ‘Delaware’),
(‘DC’, ‘District of Columbia’),
(‘FL’, ‘Florida’),
(‘GA’, ‘Georgia’),
(‘HI’, ‘Hawaii’),
(‘ID’, ‘Idaho’),
(‘IL’, ‘Illinois’),
(‘IN’, ‘Indiana’),
(‘IA’, ‘Iowa’),
(‘KS’, ‘Kansas’),
(‘KY’, ‘Kentucky’),
(‘LA’, ‘Louisiana’),
(‘ME’, ‘Maine’),
(‘MD’, ‘Maryland’),
(‘MA’, ‘Massachusetts’),
(‘MI’, ‘Michigan’),
(‘MN’, ‘Minnesota’),
(‘MS’, ‘Mississippi’),
(‘MO’, ‘Missouri’),
(‘MT’, ‘Montana’),
(‘NE’, ‘Nebraska’),
(‘NV’, ‘Nevada’),
(‘NH’, ‘New Hampshire’),
(‘NJ’, ‘New Jersey’),
(‘NM’, ‘New Mexico’),
(‘NY’, ‘New York’),
(‘NC’, ‘North Carolina’),
(‘ND’, ‘North Dakota’),
(‘OH’, ‘Ohio’),
(‘OK’, ‘Oklahoma’),
(‘OR’, ‘Oregon’),
(‘PA’, ‘Pennsylvania’),
(‘PR’, ‘Puerto Rico’),
(‘RI’, ‘Rhode Island’),
(‘SC’, ‘South Carolina’),
(‘SD’, ‘South Dakota’),
(‘TN’, ‘Tennessee’),
(‘TX’, ‘Texas’),
(‘UT’, ‘Utah’),
(‘VT’, ‘Vermont’),
(‘VA’, ‘Virginia’),
(‘WA’, ‘Washington’),
(‘WV’, ‘West Virginia’),
(‘WI’, ‘Wisconsin’),
(‘WY’, ‘Wyoming’);

This entry was posted in SQL.

Show all current running Stored Procedures

Posted on Updated on

There are times when you need to figure out what Stored Procedures are currently running and if they are blocking any if all other processes.

SELECT sqltext.TEXT,
req.session_id,
req.status,
req.command,
req.cpu_time,
req.total_elapsed_time,
req.start_time,
req.blocking_session_id,
req.wait_type,
req.wait_time,
req.last_wait_type,
req.reads,
req.writes,
req.logical_reads,
req.row_count
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
WHERE DB_NAME(database_id) = ‘MyDatabaseName‘

This entry was posted in SQL and tagged , , .

Concatenate rows into CSV

Posted on Updated on

If you have the need to take multiple rows into 1 comma separated row then this should do the trick. One of the biggest usages for this is when you’re using SSRS with multiple parameters.

DECLARE @COLUMNS VARCHAR(MAX)

SELECT @COLUMNS =
COALESCE (@COLUMNS + ‘,’ + CAST(FirstName AS NVARCHAR) + ”,” + CAST(FirstName AS NVARCHAR) + ”)
FROM MyCustomers

SELECT @COLUMNS

This entry was posted in SQL and tagged , , , , .

SELECT Linked Server

Posted on Updated on

If you already have a Linked Server setup, here are a way to access the Linked Server tables.

SELECT * FROM LinkedServer..Schema.Table

In the example below

Linked Server name: CACHE

Schema: SQLUser

Table: P

SELECT * FROM CACHE..SQLUser.P

This entry was posted in SQL and tagged , .

OpenRowSet

Posted on Updated on

Using OpenRowSet to read all data from an Excel worksheet into a global temp table.

INSERT INTO ##ABC
SELECT *
FROM
OPENROWSET(‘Microsoft.Jet.OLEDB.4.0‘,
‘Excel 8.0;Database=D:\EDI\SAMPLE_051311_1.xls‘, [Sheet1$])

This entry was posted in SQL and tagged , , .

CDO Email

Posted on Updated on

For those who are interested in using SQL cdosysmail to email embedded HTML follow this code as a model. Keep in mind that you are required to have the Stored Procedure sp_send_cdosysmail.

SET NOCOUNT ON
— SETUP VARIABLES
 DECLARE @body1 VARCHAR(MAX)
 DECLARE @subjecttitle VARCHAR(100)
 DECLARE @email VARCHAR(200)
DECLARE @today DATETIME

SET @today = (SELECT CONVERT(VARCHAR,GETDATE()-1,101))
SET @subjecttitle = ‘Daily Sales Summary Report for ‘ + CONVERT(VARCHAR,@today)

— PROCESS EMAIL DATA
 SET @body1=’

‘
SELECTTOP 20 @body1 = @body1 +’
‘
FROM dbo.TransactionHeader
WHERE DIMENSION = 1001 AND RECEIPTDATE = @today
GROUP BY DIMENSION— SEND THE EMAIL
exec master.dbo.sp_send_cdosysmail
@From=’cbuisr@gmail.com’,
@To =’cbuisr@gmail.com’,
@Subject = @subjecttitle,
@Body =  @body1

Act Plan Var $ Var % LY Comp
‘ + CONVERT(VARCHAR,DIMENSION) + ‘ ‘+ CONVERT(VARCHAR,SUM(CONVERT(DECIMAL(12,2),TOTALBEFORETAX))) + ‘ ‘ + ‘VAR $’ + ‘ ‘+ ‘Var %’ + ‘ ‘ + ‘LY’ + ‘ ‘ + ‘Comp’ + ‘

This entry was posted in SQL and tagged , , , .