David Mineer's Blog

Using MSSQL I needed to add a column to every table in a legacy database because I wanted to see whenever a new record was added. Needed a 'createdate' field that defaulted to getdate(), Instead of having to do each table one at a time I was able to find a query that did it all for me. Saved me a couple of hours. Thanks to Add a column if it doesn't exist to all tables?

Here is the code:

Earning my money now cause I just shaved off a couple hours of billable time for my client.

Microsoft SQL Server

This entry was posted on April 18, 2013 at 10:32 PM and has received 38 views. There are currently 0 comments. Print this entry.

I inherited a system that has a few quirks. Great system but some of the stuff is hard to deal with. I was asked to make the phone number search work better. They are having a hard time with it cause you have to enter the phone number exactly like it is in the field. i.e. (444) 843-2234 and if you don't include everything it doesn't return any results. Not very convenient.

What we wanted was to be able to type any portion of that phone number, including just a few digits, i.e. 444843 and have the matching records come up. So I need to do two things. Remove any non numeric characters from the inputted value and search the database field which had non numeric characters.

The solution, and it seems to work, even though it feels totally hackish the following:

To clean up the inputted value I use:

To search the database with that value I used:

Now this assumes a consistent phone format like this '(444) 843-2234' which is the case in our database.

That saved a lot of reworking code and it works like a charm.

This entry was posted on October 11, 2012 at 10:43 PM and has received 306 views. There are currently 0 comments. Print this entry.

I recently inherited an old WordPress site that nobody had used for over a year. So nobody could remember their username and password. I needed the admin password to be able to go in and make the adjustments that they had asked me to make. I have access to the database on the back end and all the files on the server via ftp so I started searching for solutions.

I found this page that seems to be the goto place for resetting a wordpress password. I needed to reset the admin one. All the info seems to be here: Resetting Your Password

I tried several of those inlcuding FTP and the Emergency Password Reset Script. This site is using Microsoft SQL Server so some of the others didn't apply. I had tried simply adding my own email to the admin user account in the database but that didn't work. I got a mail error.

Then, searching google, I found this page: Is it possible to calculate MD5 hash directly in T-SQL language?

The solution was found on that page where there is the following SQL statement:

This worked and created the password and I was able to login.

This entry was posted on October 6, 2012 at 8:58 AM and has received 215 views. There are currently 0 comments. Print this entry.

I am trying to understand the varchar(max) data type. I cam across a really old article but it had the exact answers I was looking for. Specifically when do I go from using varchar with some number i.e. varchar(700) to varchar(max)

From This blog post:

I was browsing google searching for information on data types as I was creating a new table in my db. Just wanted to make sure I was doing things right and I came across this snippet notifying of the following:

ntext, text, and image data types will be removed in a future version of Microsoft SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead.

Fixed and variable-length data types for storing large non-Unicode and Unicode string and binary data. Unicode data uses the UNICODE UCS-2 character set.

So in this particular table I did use varchar(MAX) instead of text. Didn't even know there was varchar(max) or I forgot.

This entry was posted on March 2, 2012 at 2:36 PM and has received 371 views. There are currently 0 comments. Print this entry.

Wasn't sure how to title this post. My users were complaining of a slow lookup. Thing is, it was only slow in certain regions. Other regions were fine. I found the offending queries and compared two. I only changed the id which caused the lookup to filter based on region. For example. Find all companies in California, or find all companies in Texas.

When filtering by Texas it was substantially slower. In SQL Server Management studio I choose "Query/Include actual Estimation Plan" and ran the two queries. It gives a lot of good info but in the header for the slow one I also see something about a Missing index. It is in green on my screen. Right clicking on that header area I have an option "Missing Index Details" which, when selected gives me the sql to create the query that I need. I did it manually but could have run that script to add the needed index.

Adding this recommended index took the query time from 6 seconds to less than 1 second (almost instant). Sweet. Now off to optimize some other queries (could go on all day :-) ).

This entry was posted on November 16, 2011 at 5:26 PM and has received 260 views. There are currently 0 comments. Print this entry.

So I am trying to export data from an old billing application we use to a new one. Having a problem transferring notes because there is one field that contains multiple messages separated by line breaks or something. So when I open a sample of the export in excel some lines are messed up because it kicks down to the next line when it encounters one of these line breaks. The solution was found here

And here is the exact code I used to fix that particular column that was messed up:

REPLACE(CAST(message AS VARCHAR(MAX)), CHAR(13) + CHAR(10), '') as msg

This entry was posted on August 16, 2011 at 3:58 PM and has received 469 views. There are currently 0 comments. Print this entry.

I am trying to prepare some data for import and needed the zip and the zip +4 in separate fields. But in my data source they are one field. I remembered the "mid" function from way back and wondered what the sql alternative was. It is "substring". So what I needed was:

This entry was posted on August 11, 2011 at 10:51 AM and has received 375 views. There are currently 0 comments. Print this entry.

All of the sudden today we started to get ajax errors on our website. We hadn't done anything or checked in any code, no database changes, nothing. Couldn't figure out what was happening but a lot of the ajax functionality we use is the built in coldfusion ajax stuff. Running one of these ajax calls by itself we got an error about the datasource. Turns out the log file for that database was HUGE. It was full and causing the problem. I contacted my hosting provider and they sent me a link to an article: http://www.sqlcleanup.com/2008/sql-2005-truncating-log-files-and-recovering-space/

To summarize; the fix was easy and I have done this before, you just need to shrink the log file as explained in the article above:

To figure out the name of your database and log files:

Then, to actually truncate the log file for a database you run the following (This is all SQL)

This entry was posted on August 1, 2011 at 8:19 PM and has received 342 views. There are currently 0 comments. Print this entry.

In a previous post just a couple of days ago I explained how I returned some data from a many-to-many join. In reality I don't use a many-to-many join although I always consider it such. I use a many-to-one on each of the 2 outside tables and 2 one-to-many relations on the middle linking table. The rational is that many-to-many are hard and I usually need some real information from the linking table anyway, like, for example, the date the link was created.

Looking at the Coldfusion ORM mailing list I came across a thread which included a post by Joe Rinehart that links to the hibernate documentation that tells you how they feel about them.

Do not use exotic association mappings:

Practical test cases for real many-to-many associations are rare. Most of the time you need additional information stored in the "link table". In this case, it is much better to use two one-to-many associations to an intermediate link class. In fact, most associations are one-to-many and many-to-one. For this reason, you should proceed cautiously when using any other association style.

(http://docs.jboss.org/hibernate/core/3.3/reference/en/html/best-practices.html)

This entry was posted on July 23, 2011 at 11:17 AM and has received 1285 views. There are currently 5 comments. Print this entry.