Archive for September, 2013

query ssis package store packages

September 30, 2013

SELECT [name]
,[id]
,[description]
,[createdate]
,CAST(CAST([packagedata] as varbinary(max)) as xml) AS PackageSource
,[packagetype]
,[vermajor]
,[verminor]
,[verbuild]
,[vercomments]
,[verid]
,[isencrypted]
FROM [msdb].[dbo].[sysssispackages]

WHERE CAST(CAST([packagedata] as varbinary(max)) as VARCHAR(MAX)) LIKE ‘%search_criteria%’

Advertisements

User is unable to set up an email address in the To field of a subscription, it is greyed out.

September 30, 2013

Problem: User is unable to set up an email address in the To field of a subscription, it is greyed out.

Solution:

  • Locate RSReportServer.config in C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer
  • Change SendEmailToUserAlias to False
  • Restart report server service

De-duping a table using a CTE

September 30, 2013

DECLARE @duplications TABLE (value1 VARCHAR(3),value2 VARCHAR(3))

INSERT INTO @duplications VALUES
(‘AAA’,’BBB’),
(‘AAA’,’BBB’),
(‘BBB’,’BBB’),
(‘AAA’,’CCC’),
(‘BBB’,’BBB’)

SELECT * FROM @duplications;

WITH cte AS(
SELECT *,ROW_NUMBER() OVER (PARTITION BY value1,value2 ORDER BY (SELECT NULL)) AS rn
FROM @duplications)
DELETE FROM cte
WHERE rn!=1

SELECT * FROM @duplications