Inserting values into a database table I wished to add a date and time. For this I used a convert CONVERT(datetime, ’26/05/2014 14:21:00′) However this will fail where the day value is too high. A value greater than 12 will be taken as an invalid month. Also the day and month will be saved swapped. […]
Category: SQL
Get Table Foreign Key Details by SQL
How to view the foreign key settings within a database administered using MyLittleAdmin? Looking to find information about foreign keys between tables, via the control panel for a website I was using MyLittleAdmin to manage the associated database. I was able to view the keys associated with a particular table but unlike the SQL Management […]
Turn off Identity Whilst Inserting SQL Table Row
Inserting table rows including the index idents will give an error similar to: Cannot insert explicit value for identity column in table TableName when IDENTITY_INSERT is set to OFF. I was copying the contents of a database table from one installation of DotNetNuke to another. Wishing to maintain the table structure, parent and child references […]
Read More… from Turn off Identity Whilst Inserting SQL Table Row
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 […]
Read More… from Convert SQL Field of Type text to nvarchar(max)
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 […]
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 […]
List Large Tables
An enlarged DotNetNuke database can affect performance and also be an indication of a more serious issue. Listing table sizes can help to understand and resolve issues. I have used this piece of SQL to check whether the site log or the event log is over sized on a website. CREATE TABLE #temp ( table_name […]
Convert Text to nText
Changing a column having type text to its corresponding type ntext is a little more complicated than changing some of the other field types, for example varchar to nvarchar. To change a field from type varchar to nvarchar simply requires the column type to be changed, using some code like below: IF EXISTS (SELECT * […]
SQL Password Must Change
I have created a number of users and databases using the SQL Server Management Studio. Unfortunately, whilst creating a new user login, unticking of the entries relating to the password policy can be forgotten: Enforce password expiration User must change password at next login With these entries ticked and enforced an automated login from a […]
List MySQL server Databases
To list the databases that exist in a MySQL server, use the show databases SQL command: show databases; From the command line, begin by logging into MYSQL using the command mysql -u [USERNAME] -p Where -u [USERNAME] is the administrative user for the MYSQL instance and -p forces a password request. mysql> show databases; +—————–+ […]
