SQL Server AT TIME ZONE error

Problem Being that I work for a global company, I decided we should store our datetimes using the datetimeoffset data type in SQL Server and that we could report in the local timezone or whatever timezone is desired. I send the timezone to a stored procedure as a parameter. Inside the stored procedure I use AT TIME ZONE to convert dates to the desired timezone. I was using TimeZoneInfo.Local.StandardName as the value I sent which worked great in Eastern Standard Time and many other timezones for that matter.

SSRS 2014 Permission Error After Install

Problem After successfully installing SQL Server Reporting Services 2014, a permissions error is received when accessing the Reports site, http://localhost/Reports_SQLEXPRESS. The message states the following: User ‘domain\user’ does not have required permissions. Verify that sufficient permissions have been granted and Windows User Account Control (UAC) restrictions have been addressed. The goal is to get to the Site Settings section of the website to add permission. Solution Start Internet Explorer using the Run as administrator option.

Backup compression in TDE enabled databases

Problem I had databases on a SQL Server 2008R2 instance using TDE (Transparent Data Encryption). Even though backup compression was enabled, the backups did not compress. I had googled this problem a few years ago and found that backups don’t compress in 2008R2 when TDE is enabled, and that it should work once I upgraded to SQL Server 2016 or later. Recently, a SQL Server 2017 instance was deployed and I began moving databases for testing.

PowerShell export SSRS report to PDF

On occasion, I need to produce a report at the end of a production run or for a morning emailed summary. If I had access to SQL Server Enterprise Edition, then the later task of the morning summary may have been accomplished using a subscription. My solution leverages Microsoft.ReportViewer.WinForms.dll. To find out if you have the version referenced by the script, you can run the following PowerShell command. 1 [System.

Common Table Expression (CTE) Use Cases

CTEs can … aid readability of a query be used as an alternative to derived tables (aka nested subqueries) be referenced multiple times be used for recursive queries Here are some use cases. Filtering or Grouping on Windowing Function Here are the top two cities by population for each state in the table. 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 DECLARE @CityList TABLE ( City VARCHAR(30) NOT NULL, StateCode CHAR(2) NOT NULL, [Population] INT NOT NULL ); INSERT INTO @CityList (City, StateCode, [Population]) VALUES ('Portland', 'ME', 66363), ('Lewiston', 'ME', 36491), ('Bangor', 'ME', 33011), ('South Portland', 'ME', 25068), ('Manchester', 'NH', 109830), ('Nashua', 'NH', 86704), ('Concord', 'NH', 42733), ('Derry', 'NH', 33109), ('Dover', 'NH', 30168), ('Rochester', 'NH', 29935), ('Merrimack', 'NH', 25494), ('Boston', 'MA', 625087), ('Worcester', 'MA', 181631), ('Springfield', 'MA', 153155), ('Lowell', 'MA', 107584), ('Cambridge', 'MA', 106038), ('New Bedford', 'MA', 95183), ('Brockton', 'MA', 94316), ('Quincy', 'MA', 92909); WITH CityRank AS ( SELECT ROW_NUMBER() OVER(PARTITION BY StateCode ORDER BY [Population] DESC) AS PopulationOrder, City, StateCode, [Population] FROM @CityList ) SELECT City, StateCode, [Population] FROM CityRank WHERE PopulationOrder <= 2 ORDER BY StateCode, PopulationOrder; Nested Aggregate Functions Here it’s using the same numbers as Population above but let’s call it TotalSales for this example, and we will calculate the average sales for a state.