Call today 0845 643 6463

SQL Server

  • How to Handle Deleted Rows in an Incremental Load

    Python Dictionaries; what do I need to know?September 4th, 2026 in Big Data, Blog, Data Lakehouse, Data Warehouse, ETL, SQL, SQL Server, SSIS, Uncategorised

    Most data warehouse issues are apparent: a pipeline fails, an alert fires, you get a phone call at an unsociable hour. Deleted source data doesn’t do this. A row is removed from the source, and no one notices. It sits in the warehouse silently skewing measures and KPIs, until the finance director asks why a cancelled order is still in the revenue report. What’s causing this? Incremental loads use watermarks to avoid re-extracting…

    » Read more

  • SQL Server vs Fabric Warehouse: T-SQL gotchas nobody warns you about

    SQL Server vs Fabric Warehouse: T-SQL gotchas nobody warns you aboutJuly 23rd, 2026 in Blog, Data Warehouse, Fabric, Microsoft Fabric, SQL, SQL Server

    Fabric Warehouse looks just like SQL Server. It uses T-SQL, has schemas, stored procs and more. Yet, you migrate your first proper workload and see “that’s not supported” errors you never had to think about before. Fabric Warehouse isn’t SQL Server. Under the bonnet it’s storing everything as Delta Parquet in OneLake, and that storage format shapes what T-SQL can and can’t do. Here’s what actually trips…

    » Read more

  • My Broken Query Runs Successfully in Synapse, Not That I’m Complaining, But How?

    My Broken Query Runs Successfully in Synapse, Not That I'm Complaining, But How?May 14th, 2026 in Blog, SQL, SQL Server, Synapse

    I had one of those moments this week, I spent too long trying to figure out how something that I knew was broken, was running fine and throwing no errors. Can it be called troubleshooting if it isn’t causing trouble? I have a Synapse pipeline that’s been running just fine for months, due to some migration work, I needed to take a look at the source query and found it was broken. I had it open in SSMS on the other monitor, ran it,…

    » Read more

  • Reset Row Number within a Window in SQL

    July 24th, 2025 in Blog, SQL, SQL Server

    In this blog I will show you how to reset the row number within a partition window when using the ROW_NUMBER() function in SQL. The following is a sample dataset showing customer subscriptions with start/end dates, the RenewedSubscriptionID represents the subscription that was renewed to start a new subscription, for example SubscriptionID 101 was renewed to create SubscriptionID 102. The goal is to identify the sequence of renewals by customer.…

    » Read more

  • Verifying Potentially Stolen Passwords 

    • Change the Power BI Partner Logo to a vector instead of an imageApril 3rd, 2025 in Blog, Purple Frog Systems, SQL, SQL Server

    You’re currently being DDoS’ed and receive a ransom note. The attackers claim they’ve hacked your system and provide a list of usernames and passwords as “proof”. But did they really breach your system, or are they bluffing? Let’s investigate by checking the list against the database of hashed passwords.  Retrieve the Stored Password Hash from the database DECLARE @StoredPasswordHash VARBINARY(64) =…

    » Read more

  • Power BI Dataset Gives Mashup Timeout Error While Refreshing

    July 26th, 2024 in Blog, Data Warehouse, Power BI, PowerBI, SQL, SQL Server, Uncategorised

    Help – my Power BI dataset is giving mashup timeout error on refresh The mashup timeout error Incremental refreshes are a great benefit to have in order to reduce refresh times – but not when they give a mashup timeout error. Although they are meant to be a performance enhancement, they aren’t always the best approach as they fire parallel queries to the source and apply date time filters. Despite the default timeout being 2 hours, or 5…

    » Read more

  • Using Stored Procedure as a Power BI Source (with parameters)

    April 9th, 2024 in Blog, Business Intelligence, Power BI, PowerBI, SQL, SQL Server

    Using a SQL Server stored procedure as a source in Power BI We might be most familiar with using a view as the source to our PBI datasets, a series of views creating a series of tables. This can be great but when handling more complex view queries with lookups, you might find yourself with a severely long refresh and even an error. This is where a stored procedure is required – this gives you more control layer by layer in the source query.…

    » Read more

  • TRY_CAST Arithmetic Overflow Error

    March 7th, 2024 in Blog, SQL, SQL Server

    TRY_CAST() Arithmetic Overflow Error – How? It is expected that at some point you will receive some bad data – such as the wrong range, form or data type. Sometimes you receive valid data but the result of a calculation is when it goes wrong, a simple multiplication takes your data from good to bad, the difference between your cube processing and not processing, the difference between your clients receiving vital data and not receiving…

    » Read more

  • Exporting to a CSV file from SSMS – Simple BCP Method

    June 19th, 2023 in BCP, Blog, CSV, Excel, Export, Purple Frog Systems, SQL, SQL Server

    Export table contents to a CSV file from SQL Server  It may be useful to export a tables contents to a CSV file for some exploratory analysis, to feed an ETL and much more. This can be done with some simple SQL commands that exist as a stored procedure, SQL agent job or just a one off piece of code. The export can be the whole table or filtered results but one thing to keep in mind is the number of rows and result size as it may become…

    » Read more

  • @@ROWCOUNT in Synapse Dedicated SQL Pool

    June 6th, 2023 in Blog, SQL Server, Synapse

    If you’ve been working with dedicated SQL pools in Synapse, you may have noticed that the SQL Server system variable @@RowCount is not supported. I will show you a workaround in synapse to get the same information. First, use the query label option to give your query an identifier that you can use to track it: SELECT * FROM Dim.Date OPTION (LABEL = ‘MyQuery’) You can then use DMVs to find your query using the specified label…

    » Read more