Category Archives: Uncategorized

How to make the Microsoft sqlsrv driver for PHP ignore text returned from the database

Ta-da:

sqlsrv_configure(‘WarningsReturnAsErrors’,0);

Share on Facebook

How to reset a Cisco ASA 5505 to factory defaults via console

I don’t often need to reset a Cisco ASA 5505 to factory defaults, but when I do, it’s a giant pain in the ass. I won’t go into the details of what each command does, but rest assured that following this guide will reset your ASA to defaults.

 Share on Facebook

Create a table variable of dates within a range

Whether to iterate through them, or join to the table, sometimes a table containing nothing but dates within a specific range comes in handy. Here’s my code snippet for creating such a table:

 

This will yield a table of these values (thru the @endDate):

 

This can also be modified to dateadd() by month, week, etc.. or use a dynamic date range by changing the @startDate and @endDate to calculated dates instead of static values.Share on Facebook

Error 403.14 when publishing ASP.Net MVC project to IIS

I’ve run into this before and didn’t write down the solution, and I just spent another 4 hours trying to troubleshoot this problem for another project, so this is a note to future me.

When publishing an ASP.Net MVC application to another server on IIS, you have to right-click on the folder you’re publishing to (in IIS) and select ‘Convert to Application’ – make sure the application you specify is configured for the correct version of the .Net Framework, and you’re golden. If you forget this step, you’ll get errors like this:

Parser Error Message: Could not load type ‘myProjectNamespace.Default’

…which will just lead you to hating yourself for wasting so much time on such a dumb issue.Share on Facebook

How to bring a MSSQL database online out of standby/read-only

I run into this a lot – we have a standby/log-shipping database that’s used for reporting, but eventually we may need to bring it online and don’t have a transaction log to restore then bring it online with. Instead, just run:

…and you should be set. In some instances, depending on where your standby database originated from, you may run into errors like this one:

During startup of warm standby database DBNAME (database ID 6), its standby file (‘s:\MSSQL\DBNAME_Standby.bak’) was inaccessible to the RESTORE statement. The operating system error was ‘2(The system cannot find the file specified.)’. Diagnose the operating system error, correct the problem, and retry startup.

To get around that, run this:

And boom, your database is writable.

 Share on Facebook

How to kill all open connections on a database in MSSQL

This handy script will kill all open connections to a database. Handy if you need to take it offline and someone just won’t let it die.

 

 Share on Facebook

Configuring IIS to get around CORS errors when setting up a CDN site

I’m starting to use a lot of shared libraries in my projects (JQuery, Font Awesome, Bootstrap, etc…). Rather than have a ton of copies floating around on my dev machine, and rather than use public content delivery networks (CDNs), I setup my own so I can throw whatever I want onto that host. But, there’s a catch. In the interest of security and preventing cross-site scripting attacks, IIS doesn’t want to cough up files from the domain it’s configured for when the browser is pulling content using a different hostname. For example: the site in my browser is http://mysite.com, but my personal CDN is http://cdn.mysite.com – they’re totally separate sites in IIS, and could potentially be on separate servers.

If you look in the DevTools menu (F12) within Chrome, you’ll see an error like this:

Access to Font at ‘http://cdn.mysite.com /font-awesome/fonts/fontawesome-webfont.woff2?v=4.5.0’ from origin ‘http://mysite.com’ has been blocked by CORS policy: No ‘Access-Control-Allow-Origin’ header is present on the requested resource. Origin ‘http://mysite.com’ is therefore not allowed access.

To set your IIS configuration to allow this, open IIS Manager > Browse to your site > HTTP Response Headers > Add > (Enter the info below) > click OK > Restart IIS (restart might not be necessary).

In reality, you should NOT leave an asterisk in there for a production environment. List the hostname you’ll be allowing access from. I’m told there’s no mechanism to allow multiple hostnames without some URLRewrite voodoo. I’m leaving an asterisk for now since it’s my dev laptop and it’s firewalled off anyway.

Note: I continued to get this error in Chrome and messed with it for the better part of an hour. In the end, it appears that Chrome was still caching the old responses and it just took a long time for me to realize I needed to clear my cache. Or maybe me screwing around with MIME types for the .woff files is what did it, even though reverting the change didn’t re-break it. Who knows.

 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

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