Run a SSRS subscription manually

August 27, 2015

Use the following query to locate the report on which your subscription is based and execute the string in the execute_subscription field.

SELECT a.[Path] ,
a.Name ,
CONCAT(‘exec [ReportServer].dbo.AddEvent @EventType=”TimedSubscription”, @EventData=”’,b.SubscriptionID,””) execute_subscription ,
c.ExtensionSettings
FROM ReportServer.dbo.Catalog a
INNER JOIN ReportSchedule b ON b.ReportID = a.ItemID
INNER JOIN dbo.Subscriptions c ON c.SubscriptionID = b.SubscriptionID
ORDER BY a.Path;

Colours are not displayed on SSRS subscriptions sent in mhtml format

August 26, 2015

Problem: Colours are not displayed on SSRS subscriptions sent in mhtml format

Solution: Rather than selecting the colour White for example from the color selection drop down list, instead select expression and enter the value =”White” This approach will also work when specifing hex codes eg enter =”#996699″ as an expression rather than #996699

user/login impersonation extending beyond the current database

August 3, 2015

A stored procedure utilizing execute as owner (database owner = sa) is unable to call an procedure within another database whose owner is also sa.

The reason for this is that security impersonation is only valid within the database that the impersonation is performed. Any calls external to the database will result in an error.

Consider the following scenario: 2 databases, 2 different owners in the same instance. db1 owner creates db2 owner as a user within db1. He then issues an EXCUTE AS USER = ‘db2_owner’ command. If external database calls were allowed he would have full admin to db2, clearly not desirable.

To extend impersonation beyond the database you need to let the instance know that teh dataabse is trustworthy.

You can do this: ALTER DATABASE xxx SET TRUSTWORTHY ON

see https://msdn.microsoft.com/en-us/library/ms188304(v=sql.105).aspx for further information

An INSERT EXEC statement cannot be nested

August 3, 2015

move the “insert into” from nest_1 inside of the dynamic sql

USE test
GO

ALTER PROC nest_1
AS

CREATE TABLE #t (a INT)
INSERT INTO #t EXEC (‘ select 2 a’)

SELECT * FROM #t
GO

ALTER PROC nest_2
AS

CREATE TABLE #s (a INT)
INSERT INTO #s
EXEC (‘test..nest_1’)

SELECT * FROM #s
GO

exec nest_2

INSERT EXEC cannot be nested

July 17, 2015

Error: An INSERT EXEC statement cannot be nested

solution: rewrite as an openquery

select * from OPENQUERY([LOCALSERVERNAME],’EXEC dbase.schema.proc’)

Error: SQL Server not configured for data access

solution: enable data access

EXEC sp_serveroption ‘LOCALSERVERNAME’, ‘DATA ACCESS’, TRUE

Error: The OLE DB provider “SQLNCLI10” for linked server “LOCALSERVERNAME” indicates that either the object has no columns or the current user does not have permissions on that object.

solution: Add SET FMTONLY OFF;SET NOCOUNT ON

select * from OPENQUERY([LOCALSERVERNAME],’SET FMTONLY OFF;SET NOCOUNT ON;EXEC dbase.schema.proc’)

get disk queue length – powershell

June 4, 2015

DECLARE @b TABLE (output VARCHAR(MAX))
INSERT INTO @b
EXEC xp_cmdshell ‘C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -command “(get-counter ”\PhysicalDisk(1 d:)\Current Disk Queue Length” -ComputerName COMPUTERNAME).countersamples | select CookedValue”‘
;WITH a AS (SELECT *,ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) r FROM @b)
SELECT LTRIM(output)
FROM a WHERE r = 4

querying an xml column

June 2, 2015

simple example

SELECT CAST(

james
lester

‘ AS XML) xml_column
INTO #data_table

SELECT n.l.value(‘first_name[1]’,’VARCHAR(100)’) AS destination_record_count
FROM #data_table a
CROSS APPLY xml_column.nodes(‘//staff’) n(l)

SSIS – How to prevent a Task failure from failing an entire package

May 11, 2015

Sometimes you will have an SSIS package which contains volatile task where failure is sometimes allowable – for example if I file doesn’t exist you can not rename it, but you might not want the whole package to fail as a result. Here’s how to continue on failure without failing the package.

1. Create an On error handler for the task
2. Expose System Variables in the variables pane
3. Set the propagate variable value to FALSE

source: http://www.timmitchell.net/post/2013/08/05/continue-package-execution-after-error-in-ssis/

get OS process memory usage

May 7, 2015

exec xp_cmdshell ‘C:\Windows\System32\WindowsPowerShell\v1.0\Powershell.exe get-process -computername XXX ^| ft name,WorkingSet64 ‘

tempdb: DBCC SHRINKFILE: Page could not be moved because it is a work table page.

April 18, 2015

If you dont mind losing all your cached query plans try the following

DBCC SHRINKFILE: Page 4:11283400 could not be moved because it is a work table page.

DBCC FREESYSTEMCACHE (‘ALL’)

I have not needed to follow up with the following command but apparently it could help

DBCC FREEPROCCACHE

After killing all user sessions and issueing freesystemcache all I was able to shrink tempdb


Follow

Get every new post delivered to your Inbox.