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

I created a wordcloud out of my text messages from the last 4 years

I’m a data guy; it’s kinda my thing. So, when looking for an interesting dataset to work with, why not look at the most well documented human generated set of information I have in my own hand: the conversations in my iPhone’s messages app. Like most data geeks, I don’t like to delete anything if I can help it. I was at a tech conference recently and I heard one of the presenting engineers say “I don’t delete data, even if it’s bad. I’ll move it and mark it as bad, but I still won’t delete it.” – He was speaking my language. I have 4+ years of text messages on my iPhone.

I’ve outlined the process that I went through below. Note that this isn’t a full how-to guide since there’s quite a range of skills needed to do this, and I’m not much of a tutor when it comes to these things. So, your mileage may vary, mostly depending on what your skillset is going into this. I’m doing all of this on a Windows machine, but I tried to include the OSX info where possible. Also – there are definitely better ways to do this. One such approach might involve doing all of the database work within SQLite, but since I’m much stronger with MSSQL/T-SQL, I exported the data from SQLite, imported it into MSSQL, and then wrote my queries against that data.

So, here we have it:

  1. Ran a regular backup my iPhone to my laptop using iTunes (non encrypted; no special utilities needed here).
  2. Locate the SQLite database that contains the messages within the backup. I found the filename via this article. To save you some time, the filename is ‘3d0d7e5fb2ce288813306e4d4636395e047a3d28’ and can be found in the following paths:
    1. Windows: C:\Users\YOURUSERNAME\AppData\Roaming\Apple Computer\MobileSync\Backup\**ReallyLongSeeminglyRandomNumber**\
    2. Mac OSX: /Users/YOURUSERNAME/Library/Application Support/MobileSync/Backup/**ReallyLongSeeminglyRandomNumber**\
  3. I opened that file with Navicat Premium; it’s an SQLite database, so you just point the application at the file itself; there’s no SQLite service/daemon required to do-so.
  4. The actual ‘messages’ table contains far more fields than we need. To copy just our message contents and their timestamps to another table, I executed this query:

    [php]create table [msgs] as
    select [rowid], [msgbody], [date]
    from message[/php]

  5. Within Navicat, refresh your list of tables and browse down to the newly created [msgs] table > export that file to an Excel file > connect to a MSSQL database > import the newly created Excel file into MSSQL. Navicat handles creating the new table for me.
  6. We should probably remove punctuation from the messages since having any punctuation next to a word will throw off my ability to split sentences up into individual words. I researched and found a bunch of ways to do this, but almost everything I found involved a CTE and/or creating some functions and looping through each row. I took the lazy (but effective) route and just used the built-in SQL REPLACE() function:

    [php]update [msgs] set [msgtext]=REPLACE([msgtext],’!’,”)
    update [msgs] set [msgtext]=REPLACE([msgtext],’?’,”)
    update [msgs] set [msgtext]=REPLACE([msgtext],’.’,”)
    update [msgs] set [msgtext]=REPLACE([msgtext],’,’,”)
    update [msgs] set [msgtext]=REPLACE([msgtext],’;’,”)
    update [msgs] set [msgtext]=REPLACE([msgtext],’:’,”)
    update [msgs] set [msgtext]=REPLACE([msgtext],’*’,”)
    update [msgs] set [msgtext]=REPLACE([msgtext],’)’,”)
    update [msgs] set [msgtext]=REPLACE([msgtext],'(‘,”)
    update [msgs] set [msgtext]=REPLACE([msgtext],’$’,”)
    update [msgs] set [msgtext]=REPLACE([msgtext],’%’,”)
    update [msgs] set [msgtext]=REPLACE([msgtext],’&’,”)
    update [msgs] set [msgtext]=REPLACE([msgtext],’-‘,”)
    update [msgs] set [msgtext]=REPLACE([msgtext],’+’,”)
    update [msgs] set [msgtext]=REPLACE([msgtext],’=’,”)
    update [msgs] set [msgtext]=REPLACE([msgtext],’/’,”)
    update [msgs] set [msgtext]=REPLACE([msgtext],’\’,”)
    update [msgs] set [msgtext]=REPLACE([msgtext],’"’,”)
    update [msgs] set [msgtext]=REPLACE([msgtext],””,”)
    update [msgs] set [msgtext]=REPLACE([msgtext],’~’,”)
    update [msgs] set [msgtext]=REPLACE([msgtext],’+’,”)[/php]

  7. At this point, all of the messages are in a MSSQL table called [msgs], but every row contains a full message. Since I’m creating a word-cloud, I want every word in its own row so I can easily count their recurrence. I dug around online and found this blog post outlining how to use XQuery to split a string of words into individual rows, which is exactly what I want to do. I adapted the code from the blog post and ended up with the query below. It took over 3 hours for this to iterate through each message and break it up into individual words. Granted, I’m using MSSQL in a VM, but it’s a fairly beefy vBox. In other words: be patient with this step.

    [php]– Create a table to store all of our words in
    CREATE TABLE [txtwords]([word] [nvarchar](max) NULL)

    if exists (Select * from sys.xml_schema_collections where name like ‘WordList’)
    drop XML SCHEMA COLLECTION WordList
    go
    create xml schema collection WordList as ‘
    <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
    <xs:element name="words">
    <xs:simpleType>
    <xs:list itemType="xs:string" />
    </xs:simpleType>
    </xs:element>
    </xs:schema>’

    go

    DECLARE @string nvarchar(MAX);
    DECLARE @RowID int = 0; — Default to starting at RowID 0
    DECLARE @xml_data xml(WordList)

    WHILE (1 = 1)
    BEGIN

    SELECT TOP 1 @RowID=RowID, @string=[msgtext]
    from [msgs]
    where RowID>@RowID
    order by RowID ASC

    — Exit loop if no more rows
    IF @@ROWCOUNT = 0 BREAK;

    — Turn each row/string into XML
    set @xml_data='<words>’+ replace(@string,’&’, ‘&amp;’)+'</words>’
    — execute select/insert for each individual word
    insert into txtwords ([word])
    select T.ref.value(‘.’, ‘nvarchar(100)’)
    from (Select @xml_data.query(‘
    for $i in data(/words) return
    element li { $i }
    ‘)) A(list)
    cross apply A.List.nodes(‘/li’) T(ref)

    END[/php]

  8. Since I don’t really care about ‘common’ words within my text messages, I’m going to use a “stop list” so I can exclude such words from my final query. I downloaded a list from xpo6.com and imported it into a table called [stopwords] using Navicat. I’ll spare you the details of that process since it’s very straightforward. No doubt it would be more efficient to just delete all of the words in the stop list from my [txtwords] table rather than the approach I took, but in following the theme of never deleting data, I opted to just exclude the words via a NOT IN operator in my final query.
  9. And now, the final query to provide the top 100 words, their recurrence count, and the two combined into a format I can copy/paste into the Wordle website:

    [php]select top 100 [Word], count([word]) as [UsageCount],
    convert(nvarchar,count([word])) +’: ‘+ [Word] as [WordleFormat]
    from [txtwords]
    where [word] NOT IN (select [word] from [stopwords])
    group by [word]
    order by [UsageCount] desc
    [/php]

    This produces a resultset in this format:
    SMSUsageOutputExample

  10. After spending a little time with my old pal Google, I located Wordle – a word cloud creator that allows you to provide weighted word values (which allows me to make my more common words show up larger) – I copied and pasted that ‘WordeFormat’ column into the Wordle creator. Warning: you’ll need the JRE installed to create your own word cloud using Wordle.

 

Final results (Note that I re-added the apostrophe where appropriate for readability purposes.):

  • Total messages: 111,037
  • Total word count: 1,174,999
  • Most frequently occurring word: “I’m” – occurs 10,308 times
  • Most frequently occurring number: “2” – occurs 833 times (#96 on the list)
  • Most frequently occurring verb: “know” – occurs 4,294 times (#7 on the list)
  • Most annoying phrase that I overuse: “lol” – occurs 3,654 times (#10 on the list)

The word cloud which includes weighted totals for each of my top 100 words:

SMSWordCloud

Top 10 frequently occurring words and their recurrence count:

SMSUsageTop10

 

I realize this may be useless, but the process was fun :)

Share on Facebook

I started using Microsoft OneDrive…one cool difference between using it in IE and Chrome

I normally try to “roll my own” when it comes to cloud services that I could replicate with a reasonable amount of effort. Lately though, my focus has been on other projects, and I’ve grown tired of using SFTP to manage my files on a remote server. Enter, Microsoft OneDrive – I checked out Box, Dropbox, SugarSync, and Google Drive, but decided on OneDrive even though it’s one of the only Microsoft cloud service I’m currently using (pretty much everything else is handled via Google Apps).

And a bonus, right out the gate I noticed that when I use OneDrive in Chrome, I have the option to upload an entire folder.


OneDrive-Chrome

When I use OneDrive in Internet Explorer, the option doesn’t exist; it’s just a single file ‘Upload’ option.

OneDrive-IE

How cool is that?! Microsoft’s own IE browser doesn’t support folder uploads, but they still put out the effort to build the feature in their cloud storage service so their customers can benefit, even when using a competing browser. I’m loving the new Microsoft; keep it up Mr Nadella.

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

32 bit ODBC config on 64 bit windows

Got a legacy 32 bit app that uses DSNs, but you’re running it on Windows 7 x64 or Win2008 R2? Be sure you setup your DSN under the 32 bit ODBC utility. It’s the same interface, just the 32 bit version, but the utility in Administrative Tools is for the native 64 bit platform so your 32 bit app won’t even be able to see the DSNs you create there.

Share on Facebook