Category Archives: IT Tips

Information that I’ve used in my IT job that may be useful to others.

How to create a unique filtered index as a substitute for unique constraints in MSSQL

You can enforce unique constraints on a column in SQL using a basic ‘create constraint’ command:

…but doing so on a column that allows null values will yield an error like the one below:

In these instances, your best bet is a unique filtered index. There’s some pretty in-depth info from Redgate on the subject, but the syntax is pretty similar to creating a normal nonclustered index, just with the addition of a filter and unique clause:

And voila – success:

Share on Facebook

Variable reference for common date calculations

I spend a good portion of a typical workday writing T-SQL code. Often enough, I need to calculate a date variable based on the current date. Here’s my reference scratch for some commonly used date calculations.

Share on Facebook

PHP Manager Fails to install on Windows 2016

Most of my web-dev projects are PHP based, and most of the data I work with is in MSSQL, so I end up using a combination of IIS + PHP + MSSQL pretty often. I just spun up my first Windows 2016 server, and promptly encountered issues installing PHP using Microsoft’s own Web Platform Installer tool (even using the latest version; 5.0 as of this writing). I was able to get PHP and the SQL drivers to install, but PHP Manager kept failing.

The fix: Change the registry key below from the default decimal value of 10 to a lower value; changing to 9 worked for me. Close regedit, re-run WPI and the installation will complete successfully. Go back into regedit and change the value back to 10 to prevent issues with future installations/updates of other IIS related apps that might check that value.

Edit: also make sure you have .Net 3.5 installed. WPI doesn’t prompt for it as a dependency, but the PHP Manager install will fail without it.

Edit v2: Apparently WPI should no longer be trusted for installing PHP Manager for IIS. Grab the MSI file for your version of IIS from this Git repo: https://github.com/edgardo001/PHPManagerForIIS-Versiones.

Share on Facebook

Fix for issue when attaching a MDF file: File activation failure. The physical file name “DBName.mdf” may be incorrect.

When attaching an existing MDF file in MSSQL, you may receive an error like this: “File activation failure. The physical file name ‘DBName.ldf’ may be incorrect” in combination with “CREATE DATABASE is aborted.” In my case, I’m trying to attach the AdventureWorks database and don’t have the transaction log file (MS doesn’t provide it), so my SQL instance is trying to create it for me but running into a permissions issue trying to actually create the LDF file. It seems odd, since the SQL Server Service IS running as an account with appropriate permissions on the DATA folder. Keeping in mind, the “File activation failure” message is normal because it’s a reference to SQL’s attempt to open the log file that the database used to have. It’s the fact that creating/attaching the DB also fails that’s actually a problem.

sql-attach-db-error

This is caused by not being smart enough to turn off UAC before launching SSMS. You can get around it by running SSMS with elevated privileges: right-click on SSMS > Run as Administrator – or running SSMS locally and connecting to the SQL server over the network.

Share on Facebook

How to enable the display of all errors in PHP – at Runtime

If you’re like me, and love to develop in production, you can’t enable error reporting via your php.ini file – just add these two lines of code to your scripts and you’ll be drowning in notices, errors, and the like in no time:

Or, you can add the following config options to your .htaccess file:

 

Share on Facebook

Fix for MSSQL TEXT datatype being truncated to 4096 characters in PHP

The PHP MSSQL driver provided by Microsoft is set to limit the length of data returned from a MSSQL query in a TEXT datatype to 4096 characters. You can easily correct this by changing the following lines in your php.ini file (commented out by default):

 

Share on Facebook

How to list all databases and their last restore date/time in MSSQL

This quick query pulls some basic info from the DMVs into a CTE so the resultset can be partitioned and the latest row pulled out. Super simple; check out the master.sys.databases view if you’d like to pull out some more details.

Share on Facebook

How to create a script to re-create all constraints in SQL

Share on Facebook

How to disable and enable all constraints on all tables in MSSQL

I find myself making backend changes lately within databases with a ton of check constraints. To disable all of the constraints on all tables within the current SQL database, the following commands come in handy.

To disable:

EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

To re-enable:

EXEC sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

Share on Facebook

A simple script to delete old spam on CPanel servers (or other Maildir based mailboxes)

I manage a CPanel server that gets hit with a ton of spam. Disk space is cheap, but there are better things to waste it on than old spam. Create a shell script with the commands below and set it to run automatically via cron, and you’ll clear out messages older than 30 days from your spam folders:

Note that the ‘Spam’ folder on my server is called ‘.spam’ but depending on your IMAP namespace and other variables, you may need to tweak that part of the script.

Share on Facebook