SELECT * FROM FU_Catalog.sys.dm_db_session_space_usage
select C.UserName, D.RoleName, D.Description, E.Path, E.Name
from dbo.PolicyUserRole A
inner join dbo.Policies B on A.PolicyID = B.PolicyID
inner join dbo.Users C on A.UserID = C.UserID
inner join dbo.Roles D on A.RoleID = D.RoleID
inner join dbo.Catalog E on A.PolicyID = E.PolicyID
WHERE path = ‘XXX’
order by C.UserName
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] ,
CONCAT(‘exec [ReportServer].dbo.AddEvent @EventType=”TimedSubscription”, @EventData=”’,b.SubscriptionID,””) execute_subscription ,
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;
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
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
move the “insert into” from nest_1 inside of the dynamic sql
ALTER PROC nest_1
CREATE TABLE #t (a INT)
INSERT INTO #t EXEC (‘ select 2 a’)
SELECT * FROM #t
ALTER PROC nest_2
CREATE TABLE #s (a INT)
INSERT INTO #s
SELECT * FROM #s
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’)
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)
FROM a WHERE r = 4
‘ AS XML) xml_column
SELECT n.l.value(‘first_name’,’VARCHAR(100)’) AS destination_record_count
FROM #data_table a
CROSS APPLY xml_column.nodes(‘//staff’) n(l)
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