Articles - Joel Lipman .Com

Joomla WHERE clause with OR

: Joel Lipman; Joomla; 16 July 2014; Hits: 6481

What?
A quick article on how to use the where clause in a joomla database query.

Why?
In response to a member, I use the old form where I can include the whole SQL statement:

copy raw

$db->query('SELECT * FROM #__myTable WHERE condition1=true or condition2=true')

$db->query('SELECT * FROM #__myTable WHERE condition1=true or condition2=true')

Method #1 - chain:

Category: Joomla :: Article: 569

: Joel Lipman; SQL Server; 07 July 2014; Hits: 9455

Applies To:

MS SQL Server 2008 R2
MS Windows 7 Enterprise (Client)
MS Excel 2010

What?
A really quick note on how to insert a carriage return or new line into the column name/alias (the header). It might seem trivial but these little aesthetic changes done at the database level can save some time.

Why?
I have an Excel report which dynamically gets its content from a data source located on a database on the other side of the world. I want the header in the column "Academic Week" to break across two lines so that the column doesn't expand to the width of "Academic Week" and instead expands to the width of the word "Academic".

What I have:

copy raw

Academic Week    Monday      Tuesday     Wednesday   Thursday    Friday
---------------- ----------- ----------- ----------- ----------- -----------
1                14-Jul-2014 15-Jul-2014 16-Jul-2014 17-Jul-2014 18-Jul-2014
2                21-Jul-2014 22-Jul-2014 23-Jul-2014 24-Jul-2014 25-Jul-2014
...

Academic Week Monday Tuesday Wednesday Thursday Friday
---------------- ----------- ----------- ----------- ----------- -----------
1 14-Jul-2014 15-Jul-2014 16-Jul-2014 17-Jul-2014 18-Jul-2014
2 21-Jul-2014 22-Jul-2014 23-Jul-2014 24-Jul-2014 25-Jul-2014
...

What I want:

copy raw

Academic 
Week      Monday      Tuesday     Wednesday   Thursday    Friday
--------- ----------- ----------- ----------- ----------- -----------
1         14-Jul-2014 15-Jul-2014 16-Jul-2014 17-Jul-2014 18-Jul-2014
2         21-Jul-2014 22-Jul-2014 23-Jul-2014 24-Jul-2014 25-Jul-2014
...

Academic
Week Monday Tuesday Wednesday Thursday Friday
--------- ----------- ----------- ----------- ----------- -----------
1 14-Jul-2014 15-Jul-2014 16-Jul-2014 17-Jul-2014 18-Jul-2014
2 21-Jul-2014 22-Jul-2014 23-Jul-2014 24-Jul-2014 25-Jul-2014
...

How?
To do this in a select query resultset, you insert the special character references "CHAR(10)" [line feed] and "CHAR(13)" [carriage return] but to do this in the name of the column heading, the answer is a much simpler one,

Category: SQL Server :: Article: 568

: Joel Lipman; SQL Server Integration Services; 13 June 2014; Hits: 18425

What?
This is an article to demonstrate a quick step-by-step on having an SSIS package loop through a directory/folder of files in order to populate a database table. We could add each file as a separate connection manager but this is inefficient and not versatile enough to accommodate files that get added later.

Why?
I am creating an extract SSIS package intended to take a text file as its source and to populate a database table with this data. Note that this only works if all the text files to be used as source data have the same number of columns and where the column widths match.

How?
I've adapted my real working product with an example. As my work was for a Personnel/HR project, and data confidentiality is somewhat important, some of the images will be censored or data changed in the example below.

: Joel Lipman; SQL Server Integration Services; 09 June 2014; Hits: 5612

What?
I started getting this error:

copy raw

The task with the name "Data Flow Task" and the creation name "DTS.Pipeline.2" is not registered for use on this computer

The task with the name "Data Flow Task" and the creation name "DTS.Pipeline.2" is not registered for use on this computer

Why?
We use a combination of MS Visual Studio 2008, 2010 and 2012. VS2008 for our old SSIS packages and VS2010 for database solutions running against SQL Server 2008 R2. We can use VS2012 for both but this requires upgrading all the packages and then making them suitable for our new SQL Server 2012 instances.

How?
This applies to a workstation (hopefully you're not doing development directly on the server). I re-installed sql server setup, re-ran the repair to no avail. A clue came from the MS site for an older version of the pipeline in that you access the SQL Server Configuration Manager. This may not be the fix for you but it was for me:

Open Start > All Programs > SQL Server 2012 (or your latest)
Expand "Configuration Tools" and open "Sql Server Configuration Mnaager".
Right-click on "SQL Server Integration Services 10.0" and select "STOP".
Right-click on "SQL Server (SQLEXPRESS)" and select "STOP".

Category: SQL Server Integration Services :: Article: 566

Convert to Proper Case in T-SQL

: Joel Lipman; Transact-SQL; 04 June 2014; Hits: 26280

What?
So this is an article exploring how to convert UPPERCASE text into mixed case. The feed is originally for a personnel feed so it won't be converting long paragraphs of English text. Instead it will be applied to names and addresses as well as job titles and departments.

Why?
We wanted a T-SQL version despite having successfully built a custom script component for SSIS.

How?
You can search my site for how to do this in VB or C#. This version is entirely using T-SQL and we're going to use a function so no dynamic SQL (execute, evaluate).

: Joel Lipman; SQL Server Integration Services; 03 June 2014; Hits: 10338

What?
This article is so that I don't spend so long in googling what the TxScript referenced assembly is when creating an SSIS custom component.

How?
This is simply the "Script Component" under "Extensions". No MSDN website said this... obvious to Microsoft staff so why should their documentation include it?

ReCaptcha alternative: Integrate PlayThru in JComments 3.0 for Joomla 3.2

: Joel Lipman; Joomla; 27 April 2014; Hits: 182935

Applies to:

Joomla CMS v3.2.3 (Joomla.org)
jComments v3.0.0 (JoomlaTune.com)
PlayThru v1.1.8 (AreYouAHuman.com)

What?
This article is a follow on from my article JComments 2.3 with PlayThru (Joomla 2.5.x) but this is for a later version of the components using the alternative called PlayThru by AreYouAHuman.Com.

Why?
The kCaptcha used by the jComments extension is easily automated and no longer blocks spam comments. Google's ReCaptcha is overrun by click farms and is unable to authenticate engaging visitors.

How?
So I have come up with what I consider a pretty stable solution. I adapted it from various legacy solutions when using Google's ReCaptcha.

: Joel Lipman; SQL Server Reporting Services; 15 April 2014; Hits: 6311

Applies to:

Microsoft SQL Server Reporting Services 2012
Microsoft Visual Studio 2012 Premium
Microsoft Windows 7 Enterprise

What?
So I have a column in an SSRS report which displays a date. Being rather pernickety, I would like a question mark to display if there is no date to populate the field.

Why?
At the moment, the expression in there is something like this:

copy raw

=Format(Fields!MyCompletionTime.Value, "dd/MM/yyyy HH:mm:ss")
-- yields 11/02/2014 11:21:32

=Format(Fields!MyCompletionTime.Value, "dd/MM/yyyy HH:mm:ss")
-- yields 11/02/2014 11:21:32

Sounds good, where's the problem?

Category: SQL Server Reporting Services :: Article: 562

: Joel Lipman; SQL Server Reporting Services; 11 April 2014; Hits: 7223

What?
This article describes a solution our reporting server administrators found to cut the loading time of the first report of the day.

Why?
Take any SSRS report, if one of us was the first to run it on that day, it would take an additional 60 seconds to get with it and display the report. Any subsequent running of the report loaded it almost immediately.

How?

: Joel Lipman; SQL Server Reporting Services; 08 April 2014; Hits: 12973

What?
Sounds easy but actually if you use an image which has transparent areas, ie. has one color which will be transparent, the transparent pixels will be colored in with the page background color.

Why?
I want an image to display per row as a status marker for 3 different types of results: Success, Failure, Unknown. My images are circles with the background being transparent. I want the first column to display an image based on the status result.

If I simply insert an image, the report would use the background color of the report.
How?

: Joel Lipman; SQL Server Reporting Services; 08 April 2014; Hits: 7997

Applies to:

Microsoft SQL Server Reporting Services (SSRS) 2008 R2
Microsoft Visual Studio 2012
Microsoft Business Intelligence Development Studio

What?
I recently revamped our standard report with a new template comprising of a single Tablix which contained the images of the corners and sides of the report. It looked beautiful if simply displayed on a single page (the top half of the page) as long as the end-user doesn't scroll. If the page was scrolled, what should display went behind my report and instead the whole report acted as a header on top of the scrolling content and would only ever display, the top rows of the dataset.

Why?
A lot of articles out there on the net are offering solutions that do the exact opposite. Basically, I want the opposite result of "Fixed headers while scrolling".

How?

: Joel Lipman; Web-Development; 03 April 2014; Hits: 6293

What?
There are a lot of articles & posts out there that cover the same topic, but as this took me the good part of an hour just to find out, I'm posting it here so I never have to look for it again.

A client changed their website domain address and wanted any person visiting the old domain to be redirected to the new domain.

Why?
I warned that a 301 site gets removed from the Google directory and true to form Google have removed it. Not sure why nobody believed me when I raised the alarm but hey-ho.

How?
I'm going to show you how to do this with a .htaccess file:

Credit where Credit is Due:

RSS Feed

Related Articles

Joes Revolver Map

Joes Word Cloud

Accreditation

Donate & Support