Convert SQL Field of Type text to nvarchar(max)

To enable database tables to handle the extended character set languages I was looking to convert fields from type text to ntext.

Aware of the pending loss of support for the field types text and ntext I also chose to convert these fields to nvarchar(max).

SQL server 2016 is removing support for field type text and ntext.

In this example table I had a field called body which was of type text.

The field was to retain its name but to be changed to a type which would support a greater international language.

To prepare for SQL server 2016 I chose to also change it to type nvarchar(max).

Given below is the SQL applied to the table portfolio.

alter table dbo.Portfolio add body2 text
go
update dbo.Portfolio set body2 = body
go
alter table dbo.Portfolio drop column body
go
alter table dbo.Portfolio add body nvarchar(max)
go
update dbo.Portfolio set body = body2
go
alter table dbo.Portfolio drop column body2
go

As can be seen above I begin by adding a second field of type text, called body2.

The field body is then copied to this be field body2.

The old field can now be dropped. You may prefer to check at this point that the data has been copied.

Now to create the new version of the body, giving a type of nvarchar(max).

Once more the data is copied. This time back to the body field.

Perhaps another check of the data copy?

And finally deletion of the temporary field.

Details of what’s in and what’s out on SQL server 2016.

DotNetNuke How to Add alt Text to an Image

Alt text can be added to a DotNetNuke image either at the time of adding the image to the text content, or subsequently if required.

DotNetNuke manages uploaded images and documents through the File Manager.

I prefer to use the file manager to upload and manage my images and documents separately from when inserting content to the page.

With the background work done, images and documents uploaded, I can compose my content for the page, without the disruption involved in uploading an image.

While composing the page content I can click on the image icon within the editor to popup the Image Manager dialogue to make my selection.

DotNetNuke adding image alt text add media

Using the popup Image Manager navigate to and select the image which you wish to insert into your text.

DotNetNuke Adding Image Alt Text

Add further details regards the image, for example the size of the image, width or height, and the element which we are interested in the alt text.

Later on, if you decide to change the text right click on the image to select the properties option, this will open the Properties dialogue.

DotNetNuke adding image alt text image properties

Why add ALT Text to an Image? Well adding alt text to an image is good for SEO, lets visitors understand the context in which you are using the image and for those who are using a screen reader to view your website the text will be spoken.

Change Windows 2008 User Password Lost Current Password

Having lost the current password for a Windows 2008 server user account. How do I change the password?

If I follow the control panel link to the user and look to reset the password, a part of the dialogue requires entry of the current password. Which I didn’t have!

Its possible to get around this requirement from a command prompt.

Right click on command prompt, from the menu, and select to run as administrator

Enter

net user username *

When asked, enter the new password, and re enter for confirmation.

Obliged to confirm that the password change is correct and the anticipated value has been entered I once more tried logging in with a remote desktop session from another computer.

Using the command prompt as opposed to the user control panel I was able to reset a user password without the knowledge of the current password.

 

WooCommerce How to Remove “Showing the single result”

I wished to hide the WooCommerce message “Showing the single result”.

I considered using CSS to hide the relevant content on the page.

This is often a simple approach. Inspecting the page content at a given point with the browser tool to show the div wrapping the content with its id or class.

But WooCommerce reuses the same div when there are more results to be shown.

Therefore simply hiding the div is not an acceptable approach.

Another idea is to test the content to see if it matches and then hide it. Using either JavaScript or jQuery this can be done. But this is getting complicated.

The content will be first displayed as the page is loaded, prior to being removed once the JavaScript Is run. Better to hide first and then show.

Is there not a better way?

If the changes which you are making in the WooCommerce template files are limited it’s much better to interact with the functions.php file of your theme or child theme.

In the theme file functions.php add

remove_action( ‘woocommerce_before_shop_loop’, ‘woocommerce_result_count’, 20 );

Updates to WooCommerce, whilst they try to limit the update changes to the templates, these do occur. This may result in reviewing and  modifying your copy of the files.

But by editing in just the one file with simple additions this reduces this over head and additional work.

Edit the file functions.php to remove the result count, thus hiding the text Showing the single result.

MSSQL Table Search and Replace

Consider a database table of posts or pages.

The website address has changed, perhaps from the development URL to the live website

Or maybe the website’s URL is to change from example.co.uk to example.com.

The tables are to be updated, ideally searching and replacing the old value with a new one.

Given below is a search and replace for the table post, replacing the URL entries in the field pbody.

UPDATE
post
SET
pbody = REPLACE(pbody,'example.co.uk','example.com')
WHERE
pbody LIKE '%example.co.uk%'

A search and replace of a MS SQL table replacing a website URL or correcting an error.

Install ExpressMaint SQL Backup utility

ExpressMaint is a utility which may be used to automate the backup of SQL Server databases.

It can be used to create and save a backup of each of the databases within an SQL Server. Better still it can be set to age the files, deleting all those older than, say, a month.

The ExpressMaint project home page is here: https://expressmaint.codeplex.com/

Download a copy of the zip file ExpressMaint.zip. Extract the ExpressMaint.exe file contained within to your scripts directory.

I’ll assume that the exe file has been added to the folder as: z:\ExpressMaint.exe.

I use a batch file to call the exe file, passing the relevant parameters.

And I have created a backup directory for the databases at z:\backup.

The batch file for running ExpressMaint is located at: z:\expmaint.cmd.

Contents of which are given below

 "z:\expressmaint.exe" -S webserver\SQLserver -D ALL_USER -T DB -R "z:\dbBackup" -RU WEEKS -RV 4 -B "z:\dbBackup" -BU WEEKS -BV 4 -V -TO 20

In the above change the name of the server webserver and Sqlserver as appropriate.

shows example for individual database location

I created a scheduled task to run daily.

On Windows Server 2012 I found that expressmaint.exe wasn’t running as a scheduled task.

Searching for more information about the cause of the issue. I found this on stack exchange, which recommend using a later version of ExpressMaint:

I am actually using expressmaint with sql server 2012 express so u shouldn’t have any problems. make sure u use

https://expressmaint.codeplex.com/downloads/get/91612 which is version 2.0.0.1 and NOT 2.0.0.0

 

Stack Exchange article reference is:

http://dba.stackexchange.com/questions/24750/is-it-possible-to-use-expressmaint-with-sql-server-2012-express/120387

Following the referenced link and using that version of ExpressMaint worked.

Increase the DotNetNuke Maximum File Upload Size

I find the default limit on file upload size in DotNetNuke can be too restrictive.

The default DNN upload file size is 8Mb.

The setting is within the web.config file in the root of the DNN website file installation.

Look for the section <system.web>.

    <!-- allow large file uploads -->
    <httpRuntime useFullyQualifiedRedirectUrl="true" maxRequestLength="8192" requestLengthDiskThreshold="8192" />

Increase the value of the maxRequestLength parameter to clear the file size which is an issue. Doubling to 16M may be sufficient.

    <!-- allow large file uploads -->
    <httpRuntime shutdownTimeout="120" executionTimeout="900" useFullyQualifiedRedirectUrl="true" maxRequestLength="12288" requestLengthDiskThreshold="12288" requestValidationMode="2.0" requestPathInvalidCharacters="&lt;,&gt;,*,%,:,\,?" fcnMode="Single" />
    <httpCookies httpOnlyCookies="true" requireSSL="false" domain="" />

To edit the file download a copy from the server via FTP using Filezilla. The file can then be edited with a text editor such as notepad.

For simplicity of editing with colour discrimination of tags and parameters you may find one of the HTML editors useful. Must admit if it’s a one off then whatever editor is to hand is the best option.

At the time you are downloading this one configuration file why not take a backup of the whole website?

With an increased file size capability it will take longer to upload the files to your website. You may also wish to increase this time out.

If you are experiencing file upload restrictions on images then maybe you should reconsider the size or quality of the image which you are uploading. Larger, slow to load images, can have a detrimental effect on your website, visitors maybe become impatient leaving slow to load pages and it is considered a black mark on your SEO score.

References

http://www.dnnsoftware.com/wiki/working-with-large-files

Where’s the Windows 10 Control Panel?

Accessing the Control Panel in Windows 10 is one of those of course how obvious moments.

I found that the main control panel home page in Windows 10 was access by simply right clicking on the Windows logo at the bottom left hand corner of the screen.

Right click on the Windows logo to show a menu of admin related options.

Windows 10 open the control panel

The control panel is listed here amongst the other admin actions.

Where is the Windows 10 Services Control Panel?

In Windows 10 the Services control panel is to be found grouped on the Task Manager.

To access the services list begin by opening the Task Manager – right click on the Windows icon in the bottom left of the screen

Windows 10 open task manager

Clicking on Task Manager from this list opens the grouped Task Manager control panel.

Windows 10 task manager

Onto this panel has been grouped a number of the previously discrete individual views including the Services control panel.

Windows 10 task manager services

Click on the last tab to show the Services.

WooCommerce Configure Order Only no Payment

I wished to configure a WordPress e-commerce website using the popular WooCommerce plugin to take orders only, without taking payment.

I have previously used WooCommerce for product brochure management, hiding the prices to remove the shopping basket.

WooCommerce was to be used as a product brochure with an order created listing the product items and quantity.

Payment, order fulfillment and tracking would be made otherwise.

I considered the use of cash on delivery (cod) but found this neat snippet which is added in the functions.php file

add_filter('woocommerce_cart_needs_payment', '__return_false');

This allows the prices to be left in place.

An order is created in the usual way. At the end the order is completed and details emailed to the website owner, but no monetary transaction implemented.

I also removed all but cash on delivery (COD) from the payment gateways.

References

http://stackoverflow.com/questions/16729643/removing-payment-gateways-from-woocommerce