A very useful tool in SQL Server is the database email service. For our latest project we needed to enable email updates on the recording of new data in our system. These instructions require the SQL Database Email service to already be configured, if you need instructions on how to do this please see my… Continue reading Create a SQL Server Database Email trigger, when a tables records are updated.
Setting up SQL Server database email
To enable the database email service in SQL Server, you need to take a few steps to configure in your database. The benefits of enabling this service are you can automate emails for query results, job success or failures or even trigger emails based on table changes in your system, great for notifications. First of… Continue reading Setting up SQL Server database email
SQL Server query to get all stored procedures, tables and views execute permissions
To list all Stored Procedures in your database that an account has execute permissions on, simply update the "Execute as user" statement below.. EXECUTE AS user = 'myUsername' SELECT SUSER_NAME(), USER_NAME(); select name, has_perms_by_name(name, 'OBJECT', 'EXECUTE') as has_execute from sys.procedures revert; To list all permissions applied explicitly to tables and views for a username, use… Continue reading SQL Server query to get all stored procedures, tables and views execute permissions
SQL Server query to list all server agent jobs, owners and reassign ownership
If you are auditing security in your system a useful query can list all of your SQL Server Agent Jobs in MS SQL with the account ownership. SELECT s.name AS JobName, l.name AS JobOwner FROM msdb..sysjobs s LEFT JOIN master.sys.syslogins l ON s.owner_sid = l.sid WHERE l.name IS NOT NULL ORDER by l.name Or with… Continue reading SQL Server query to list all server agent jobs, owners and reassign ownership
Using an iMac 5k Retina 2015+ as a secondary Windows monitor
It is possible, and free, with a caveat. My workaround uses Windows 10 screen projection. I'll begin this explaining the caveat before explaining the method, the solution I found as a workaround uses wireless projection. I found there was little to no lag for extended periods and use this solution when developing using my windows… Continue reading Using an iMac 5k Retina 2015+ as a secondary Windows monitor
Identifying unused or inefficient indexes in SQL
SQL Server, like other relational databases, utilises indexing as a way to improve query performance. In a nutshell indexing is used to find data fast. However, it's important to remember that indexing comes at a cost, and as a database administrator, or developer, understanding how indexing actually works is fundamental to maintaining a performant system.… Continue reading Identifying unused or inefficient indexes in SQL
Get the min and max value from a JavaScript array
var _array = [10, 30, 2, 7, 90]; Math.max.apply(Math,_array); // 90 Math.min.apply(Math,_array); // 2
Fixing a Nintendo Switch Joycon Drifting Stick
My family loves the Nintendo Switch, so you can imagine how disappointed my young son was when he could no longer turn to the left on Mario Kart. The drifting stick had broken, I’m not sure how but anyway I didn’t fancy buying a new controller at £39.99, especially as I’d only recently paid £69.99… Continue reading Fixing a Nintendo Switch Joycon Drifting Stick
Swift abs function to get an absolute value
Abs can be used to get the absolute value of a calculation that may have a positive or negative value. As an example without Abs we could go for… var difference: Int if currentValue > targetValue { difference = currentValue - targetValue } else if targetValue > currentValue { difference = targetValue - currentValue }… Continue reading Swift abs function to get an absolute value
Search SQL Stored Procedures for text
On occasion I have found it useful to search for stored procedures based on containing text. It doesn't happen often as your naming convention should be enough to explain the purpose of your procedure, and be easy to find. However, in the real world, when looking at legacy systems it may be of use. Another… Continue reading Search SQL Stored Procedures for text
Phil's learning log 