James Wiseman

I discovered this little trick today.

If you’re wanting to count the number of characters present in a given SQL Server varchar variable, or common, use the following convention

--assuming a search character contained in the variable @SeekChar

--from a variable
SELECT LEN(@StringVariableName) - LEN(REPLACE(@StringVariableName,@SeekChar,''))

--from a table field
SELECT LEN(SomeTableCol) - LEN(REPLACE(SomeTableCol, @SeekChar, ''))
FROM SomeTable

--The Oracle  PL/SQL Variant (for the hell of it).
--All we have to do is replace the LEN function with LENGTH!
SELECT LENTH(SomeTableCol)-LENGTH(REPLACE(SomeTableCol, @SeekChar, ''))
FROM SomeTable

Quick and easy and no annoying looping constructs.

Oracle, SQL Server, SQL-Strings

DO NOT REPLY EITHER WAY – THIS IS A SCAM

Free Message: Important! Our records indicate you may be entitled to £3450 from Mis-Sold Loan Insurance! To claim reply YES to this message. Thank you!

Received from: +447848390971/07848390971

This message surfaced on the same day as the decision by the British Bankers Association not to appeal against the high court ruling on Payment Protection Insurance. The adjacent BBC article reports that several million people may be worthy of such compensation – ripe pickings for our scammers.

If you’re looking to claim back any money in relation to this, the BBC outlines a series of steps you can take personally. You can also check out the PPI reclaiming guide at moneysavingexpert.com. Ultimately, this is your money, rightfully due to you, so why waste money on an opportunistic company just looking to profit from your misfortune.

In addition, this is spam text message WHICH IS ILLEGAL. Companies that use this form of marketing are equivalent to individuals who buy stolen goods. These are not the sort of companies you want to do business with!

This is one of many messages that are circulating as originally highlighted in my article Debt Settlement Order Spam Text Message. If you ever receive anything that remotely fits this template then delete it.

Again, DO NOT REPLY

And, if you can, post the number in the comments below. This will help people searching by that number alone find this article and avoid any potential trouble arising from replying.

I’d be interested to hear of any variants on this message that surface.

If you want to read more, can I recommend the above link. It contains a list of articles that I’ve written regarding this whole scam and its setup. I found the research both fascinating and appalling at the same time.

Are You a Victim?

Screenchannel Television is making a new series about debt and loans and are looking for people to share their experiences. If have been a victim of this scam, or have even considered responding and are interested in taking part, then you can read more in this article.

Reporting

You may be report these messages to your provider. The following links may help:

Vodafone – How do I report spam text messages? Forward the spam message to VSPAM (87726).

Orange – Stop spam text messages. If you are on Orange and get spam messages, please forward them to 7726 free from your Orange phone. By doing this you are helping Orange to collate information to help reduce spam messages being sent to you and others.

02 also allow you to report on 7726. See: SPAM and unwanted subscription texts

I would also try forwarding the message onto 7726 if you are with T-Mobile or Three.

You can also try NumberCop and DoNotCall.gov

Back in May 2009, the Guardian newspaper wrote an article Spam to go – the new mobile menace. It recommends a number of organisations you can contact.

To complain about an inappropriate text, call the Advertising Standards Authority 020 7492 2222 or go to www.asa.org.uk/asa/contact/

To resolve continual mobile spam despite texting “stop”, contact the ICO on 01625 54 57 45 or go to www.ico.gov.uk/complaints.aspx

For help with premium rate text spam, call PhonepayPlus on 0800 500 212 or log on to www.phonepayplus.org.uk/output/Make-a-complaint.aspx

Missold PPI, PPI, Scam, Spam, Text Message Spam, Text messaging

You might be familiar with the following scenario:

You have a SQL Server database server hosting more than one database, but you just so happen to be accessing one in particular from your development environment (Query Analyser, Management Studio, etc).

So, you run a little query to automatically connect you to this database when you connect to the server. You might try something like:

Exec sp_defaultdb @loginame='DOMAIN\UsrNam', @defdb='SomeDatabase';

So, now when you log on, your database is ready and waiting to be used without having to manually select it. I have found this scenario common in development environments, when you are typically running lots of ad-hoc SQL queries, or running in code (stored procedures, views, triggers, etc) against the database.

However if you are working on different databases on the same server, defaulting to a particular one can cause you problems if you need to run in code against a different database. On occasions I have applied stored procedures to the default database, when I meant to do it against another one on the server!

Another consequence is when a development database is refreshed or rebuilt from some external source (from a live environment, for example). Although your default database may exist by name, this is not enough for SQL Server, which fails to log you on and gives the error message:

Cannot open user default database.

You will also get this if you have chosen to rename the database.

So what to do in this instance?

Well, we can defer to the trusty ISQL command line utility, as shown in the following screenshot:

Simply follow these steps:

1. Open a DOS  command Prompt
2. Open up an ISQL Session. I used the following command:
   • -S SQLServer_Name – Specify the name of the SQL Server to which you are connecting.
   • -E – Stipulate to connect as a trusted connection (Windows Authentication)
   • -d master – The database name to which you are going to connect. Here I specified ‘master’

isql -S SQLServer_Name -E -d master

The three options user above are as follows:

Of course, you can connect however you want. For more options (also shown in the screenshot above), type in isql /? for a full list, or refer to the site on the above link.

3. Now run a version of the above script, setting the default database to master

Exec sp_defaultdb @loginame='DOMAIN\UsrNam', @defdb='master';

4. To apply this, enter ‘GO’ on the next line.

This will ensure your SQL Server defaults you to the ‘master’ database in all subsequent connections (unless you change them again). I would counsel this as general good practice, and it comes recommended by a friendly DBA I happen to know.

Command Line, Database Connection, ISQL, SQL Server