In case you ever needed this: IF OBJECT_ID('tempdb..#GeneratedScripts') IS NOT NULL DROP TABLE #GeneratedScripts; CREATE TABLE #GeneratedScripts (Id INT IDENTITY(1,1), SqlScript NVARCHAR(MAX)); -- 1. Setup global ceiling INSERT INTO #GeneratedScripts (SqlScript) VALUES ('DECLARE @TargetCeiling DATETIME = GETDATE();'); DECLARE @SchemaName NVARCHAR(256), @TableName NVARCHAR(256), @AnchorColumn NVARCHAR(256); DECLARE @Sql NVARCHAR(MAX), @ColumnUpdates NVARCHAR(MAX); DECLARE tbl_cursor CURSOR FOR SELECT DISTINCT … Continue reading Script to update the dates on AdventureWorks database
Author: Calin OPREA
An error occurred within the report server database. This may be due to a connection failure, timeout or low disk condition within the database – when migrating SSRS databases to a new server
You have restored your databases, you renamed them if needed (in my case ReportServer and ReportServerTempDB from ReportServer$Instance and ReportServer$InstanceTempDB), jobs, everything, when migrating SSRS to a new server. Yet, scheduled subscriptions still not working (oddly enough, ad-hoc ones were working, could browse reports, etc.). In my case, when I tried editing Subscriptions, I hit … Continue reading An error occurred within the report server database. This may be due to a connection failure, timeout or low disk condition within the database – when migrating SSRS databases to a new server
Wierd tuning trick with dynamic SQL when encountering unmatched indexes
Later edit: Both Uwe Ricken (https://www.linkedin.com/in/uwericken/, https://www.db-berater.de/) and Sascha Lorenz (https://www.linkedin.com/in/saschalorenz/) correctly pointed out that my little inconsisted writing was a tad too inconsistent - first of all, the title itself was NOT highlighting the very point of this blog post: unmatched indexes and how to deal with them by using dynamic SQL. Truth be … Continue reading Wierd tuning trick with dynamic SQL when encountering unmatched indexes
Stop using variables in stored procedures for no reason
Yes, writing about SQL Server every 10 years will not bring me any medals. I know, I know.Anyway, I am currently checking what is what on a new project and I got to see this in the header of a stored procedure from 10 years ago: "use @variable to suppress 'parameter sniffing'".I will NOT dwelve … Continue reading Stop using variables in stored procedures for no reason
Change the PowerBI desktop default language
For whatever reason, I ended up with Romanian as the default language for PowerBI desktop (no regional settings, no browser settings, no nothing would provide a hint to the installer that it should install and set the app to Romanian). This did not help either: https://community.powerbi.com/t5/Service/change-PowerBI-language-menus-to-english-while-maintaining/td-p/4782 I ended up in C:\Program Files\Microsoft Power BI Desktop\bin, … Continue reading Change the PowerBI desktop default language
Number of CPUs per NUMA node
A word of advice: the script assumes that you have the exact number of CPUs per NUMA node, WHICH MIGHT NOT BE THE CASE IN YOUR SCENARIO:
Remove TDE completely. Really?
I must be getting old, two blog posts in the same year! 🙂 Anyway, I made some additional testing as part of a TDE (Transparent Data Encryption, that is) implementation and I noticed couple of interesting things when you want to remove the TDE completely from the server: When checking sys.databases, tempdb is shown as … Continue reading Remove TDE completely. Really?
To OR or …?
Yeap, a year later, there we go, posting a quick blog again. A colleague of mine skyped me about a query that was running slow. Well, slow means that it ran for 90 seconds, and then it timed out, that is. Here is the little repro of the challenge: I know, I'm too lazy NOT … Continue reading To OR or …?
DATETIME and its surprises
Well, it's about time I write another blog post (with a promise that I'll do it more than once a year). I'm going to start a series of "bad habits to kick" as for me it is important that we all have a common ground on SQL Programming. 1. Â Â Â Â Â Â Â Â Â Â Consider the following scenario: SET … Continue reading DATETIME and its surprises
Create certificate from backup fails on SQL Server 2008
This is so going to bite yer ass if you don't pay attention… As usual, a very quick post: I tried to create (ok, restore) a certificate from the production server to our DR server, so that the automated restores would work. When I ran the script (yes, CREATE CERTIFICATE … FROM FILE […]), I … Continue reading Create certificate from backup fails on SQL Server 2008
SQL-PLAY 