Articles - Joel Lipman .Com

T-SQL concatenate an incremental row number

: Joel Lipman; Transact-SQL; 24 May 2013; Hits: 9640

What?
This is a quick article on how to use a counter added to the end of a field column of data.

Eh. What?
Assume the following two tables exist:

: Joel Lipman; SQL Server Integration Services; 14 May 2013; Hits: 7280

What?
Some methods of removing trailing spaces, tabs, carriage returns and line feeds (new lines).

How?
First in Transact-SQL:

: Joel Lipman; Transact-SQL; 14 May 2013; Hits: 31853

What?
A very quick note in case I forget this one. If you are trying to join two tables and receiving the error "Conversion failed when converting the varchar value 'B110' to data type int" then read on.

How?

: Joel Lipman; Joomla; 09 May 2013; Hits: 29699

What?
A quick article on a frequent question I get asked. This refers to an issue within the Joomla CMS (version 2.5.x) where it fails to email users who reset their password.

How?
A few things to check:

: Joel Lipman; Joomla; 07 May 2013; Hits: 9813

- Applies to Joomla 2.5.x +, 3.4.x

What?
So I can't stand the way the default "Search" component in Joomla works. The default is to sort the results by popularity (hits) or by most recently added (depending on your version) which I have never seen in any other system.

I've googled and binged but could not find anything that documents how to bring it into line with other search systems. So here we go, hope this helps you.

Why?
I've created search systems for a plethora of other systems. The aim of this article is to enhance the Joomla search into par with Google and Wikipedia (or near enough).

How?

: Joel Lipman; SQL Server Integration Services; 23 April 2013; Hits: 26622

What?
This is a quick article to remind me on how to skip blank rows when using a Flat file as a data source. I would receive another Microsoft error as clear as mud:

copy raw

Error: The conditional operation failed.

Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "component 
"MyDerivedColumns" (4228)" failed because error code 0xC0049063 occurred, and the error
row disposition on "output column "DC_MyDate" (7349)" specifies failure on error. An 
error occurred on the specified object of the specified component.  There may be error 
messages posted before this with more information about the failure.

Error: The conditional operation failed.
Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "component
"MyDerivedColumns" (4228)" failed because error code 0xC0049063 occurred, and the error
row disposition on "output column "DC_MyDate" (7349)" specifies failure on error. An
error occurred on the specified object of the specified component. There may be error
messages posted before this with more information about the failure.

Why?
So the solution must be obvious to you by now. At least that's what Microsoft people think to themselves every time they see the error they programmed in.

Apparently this problem also happens when you have a Data File of varying column numbers. My solution below will also fix this.

How?

Category: SQL Server Integration Services :: Article: 505

: Joel Lipman; Excel; 12 April 2013; Hits: 6053

What?
This is a quick note to myself so that I never use parentheses in the column headings again. Basically I have a pivot table in Microsoft Excel 2010 with the projects down the left (in the first column) and the days of the week along the top.

Why?
The excel report would hit a bug where it couldn't work out that 10 (Wednesday) happened after 8 (Monday).

How?
See the following screenshot and note the dates for Monday, Tuesday, Wednesday, and Thursday:

: Joel Lipman; Joomla; 04 April 2013; Hits: 8126

What?
This is a quick article on what I change the error page for Joomla websites to. I like a clean error page, for a demo visit http://www.joellipman.com/my_error_page.

Applies to Joomla 1.6.x, 1.7.x, 2.5.x

How?

Backup the file \templates\system\error.php
Create a replacement file called error.php including the below code
Change the message "The Page you are looking for..." to what you want.
Change the link "www.joellipman.com" to the "www.yoursitename.com".
[Optional] If you want the image that creates the shadow you can download it here. I put it in the folder \templates\system\images.

: Joel Lipman; SQL Server Integration Services; 04 April 2013; Hits: 22055

What?
A quick article on how to skip rows or even specify a range to extract from the Excel file when using as the DataSource.

Why?
If you are simply using a text file as your data source, then the options in the connection manager will let you skip rows and specify column datatypes. You don't get that with Excel but you can still control the data range.

How?
My example is that I have an excel sheet where the first 10 rows are descriptions to the column, and then I have 38 columns ("A" to "AL"):

: Joel Lipman; Hypertext Markup Language; 30 March 2013; Hits: 5560

What?
A quick article on if you were given a webpage coded in HTML, what methods in AutoHotkey could you use to separate out the HTML Tables into a ListView.

Why?
I want a snippet of code that replicates any HTML table.

How?
I've been trying various ways so I'm posting them here. My opinions on them change with the weather so until I do some benchmarking I won't know which ones best:

: Joel Lipman; Web-Development; 27 March 2013; Hits: 13832

Just a pretty picture on how hackers have created an infographic of the internet:

Source: BitBucket.Org - Internet Census 2012

Country Lookup by IP address CSV

: Joel Lipman; MySQL; 17 March 2013; Hits: 7580

What?
So this is an article to serve as documentation for one of the data sources available to download for FREE from this site.

Why?
It's useful to have a CSV list of all the IP ranges and their allocated country. Our use here was to create a refreshable country by IP address list that we can use when parsing our website logs to check where visitors were from.

Note: we also use and love Advanced Web Statistics (awstats) but this was so that you could have your own country lookup script.

The Download
Should contain the following:

create_countriesipranges_table.sql: SQL to create a pre-populated table into a MySQL database.
iana_ipv4_address_space_registry.csv: comma separated values list of all countries, regions and their respective IP ranges.
ip_lookup.php: to use the database
License.txt: Released under GNU/GPL
ReadMe.txt

What is it?
I wanted my own IP lookup script to tell me the country that is allocated a given IP address, this is what I use.

The scripts in the folder you have downloaded are to create a lookup function using a MySQL database and a PHP script. As well as a CSV file equivalent.

How to use:

Download from my site: «Download»
Run the SQL script against a test database,
1. Modify the table name to use if necessary.
2. Uncomment the drop statement if running for the second time.
Modify the PHP file to use database login credentials,
1. $db_host = "localhost"; // Database host name
2. $db_user = "my_database_user"; // User for database authentication
3. $db_pass = "my_database_password"; // Password for database authentication
4. $db_name = "my_database"; // Database name
5. $db_table = "mytable_countryipranges";// Database table
Upload the PHP file to your website
Test by using a web browser to visit: http://www.mysite.com/ip_lookup.php?ipv4=123.123.123.123 where www.mysite.com is your website where you uploaded this script. // should return China

Any questions, suggestions?
We are looking to add the ability to generate this data using other sources and in time a region by IP for more accuracy.

Feel free to feedback. It's FREE to download unlike others which is probably why I made the generator in the first place but I do like to make people sign up so I can gauge the level of interest.

Credit where Credit is Due:

RSS Feed

Related Articles

Joes Revolver Map

Joes Word Cloud

Accreditation

Donate & Support