Tag Archives: MSSQL

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

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

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 the table size and rowcount for tables within a MSSQL DB

Incredibly handy query; originally from here.

 

Share on Facebook