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 meant that to best recreate and copy the table I should maintain the existing index identify values.

The table used the table unique identity index to create a parent and child relationship between some of the rows.

To export the table I was able to select all of the table content and display it as a table.

The table of data could then be copied into an editor (Geany) to add the necessary insert SQL.

To properly maintain the table data and allow a true reproduction the interlinking between the rows must be maintained. To adhesive this the index ident must be imported.

A table will automatically assign the route ident when the route is inserted. If the field is included as part of the insert statement the insert will fail with an error.

To overcome the block on the insert into the ident field i turned off the restriction at the start of my SQL insert and restore it again afterwards.

For a table with an orienting set on one of the fields performing a simple insert will fail beaches the identity field doesn’t permit is valid to be set.

To overcome this I top and tailed the insert statements with identity on and off

SET IDENTITY_INSERT GalleryAlbum ON

Insert of table rows SQL

SET IDENTITY_INSERT GalleryAlbum OFF

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.

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.

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 sysname ,
row_count INT,
reserved_size VARCHAR(50),
data_size VARCHAR(50),
index_size VARCHAR(50),
unused_size VARCHAR(50))
SET NOCOUNT ON
INSERT #temp
EXEC sp_msforeachtable 'sp_spaceused ''?'''
SELECT a.table_name,
a.row_count,
COUNT(*) AS col_count,
a.data_size
FROM #temp a
INNER JOIN information_schema.columns b
ON a.table_name collate database_default
= b.table_name collate database_default
GROUP BY a.table_name, a.row_count, a.data_size
ORDER BY CAST(REPLACE(a.data_size, ' KB', '') AS integer) DESC
DROP TABLE #temp

The SQL may be executed from either within the DotNetNuke website or, where the website is failing, from the SQL Management Studio.

To run the SQL on the website, as a host user open the page Host > SQL.

List large tables host sql pagePaste the above code into the box and click on Run Script at the bottom of the page.

list-large-tables-host-sql-page-paste-sqlThe tables within the database will be listed together with their associated size, see example listing below:

list-large-tables-host-sql-page-run-scriptAs can be seen the tables from the database are listed  showing their name; row count; column count and data size, listed with the data size descending.

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 * FROM dbo.sysobjects WHERE id = object_id(N'{databaseOwner}{objectQualifier}VNTweb_Fruit_Products') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
ALTER TABLE {databaseOwner}{objectQualifier}VNTweb_Fruit_Products
ALTER COLUMN image_large nvarchar(100)
GO

However, the same is not true when changing a column of type text to type ntext. In this case the process is a little longer requiring the creation of a temporary column:

  1. create a temporary column of type ntext
  2. copy the existing column data to the new temporary column
  3. delete the existing column
  4. create a new column (with the original name) of type ntext
  5. copy the contents from the temporary column to the new column
  6. delete the temporary column

Example SQL code for transferring a column of type text to ntext is given below:

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'{databaseOwner}{objectQualifier}VNTweb_Fruit_Products') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
ALTER TABLE {databaseOwner}{objectQualifier}VNTweb_Fruit_Products ADD TempDescription ntext
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'{databaseOwner}{objectQualifier}VNTweb_Fruit_Products') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
UPDATE {objectQualifier}VNTweb_Fruit_Products SET TempDescription = Description
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'{databaseOwner}{objectQualifier}VNTweb_Fruit_Products') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
ALTER TABLE {databaseOwner}{objectQualifier}VNTweb_Fruit_Products DROP COLUMN Description
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'{databaseOwner}{objectQualifier}VNTweb_Fruit_Products') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
ALTER TABLE {databaseOwner}{objectQualifier}VNTweb_Fruit_Products ADD Description ntext
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'{databaseOwner}{objectQualifier}VNTweb_Fruit_Products') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
UPDATE {objectQualifier}VNTweb_Fruit_Products SET Description = TempDescription
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'{databaseOwner}{objectQualifier}VNTweb_Fruit_Products') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
ALTER TABLE {databaseOwner}{objectQualifier}VNTweb_Fruit_Products DROP COLUMN TempDescription
GO

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 CMS website such as DotNetNuke will fail. There is no support for the password change dialogue.

To change the password policy for the account run the following as a new query (top left hand corner)

ALTER LOGIN USERNAME WITH PASSWORD=’PASSWORD’

Where USERNAME is the name of the user and PASSWORD is the new password.

Following this it is now possible to revisit the properties for the user and to uncheck the checkbox for enforce password expiration.

SQL Union Select Join Command

The SQL UNION command is used to gather the entries from two tables which meet the given criteria.

The SQL Union command is used to select entries from two tables It requires that the items in each of the select are in the same order and that each comparable item in the same position is of the same data type and length.

Consider a typical example of two sets of employees located in two different countries, those in England and France:

England – table name =  Users_GB

Ref. (UserId) First Name (FName) Last Name (LName)
006 Peter Smith
008 Steven Jones
023 Ruth Mortimer
034 Kurt Fulmore
184 Linda Loveridge

France – table name = Users_FR

Ref. (UserId) First Name (FName) Last Name (LName)
007 Linda Loveridge
026 Louisa Revel
027 Mark Mitchell
101 Alan Brown
128 James Woodrow

SELECT FName,LName FROM Users_GB
UNION
SELECT FName,LName FROM Users_FR

As given above the command will select distinct entries, where entries are common to both tables only one entry will be returned.

UNION combined table

First Name (FName) Last Name (LName)
Peter Smith
Steven Jones
Ruth Mortimer
Kurt Fulmore
Linda Loveridge
Louisa Revel
Mark Mitchell
Alan Brown
James Woodrow

To return all entries from both tables the Union all command is used:
SELECT FName,LName FROM Users_GB
UNION ALL
SELECT FName,LName FROM Users_FR

UNION ALL combined table

First Name (FName) Last Name (LName)
Peter Smith
Steven Jones
Ruth Mortimer
Kurt Fulmore
Linda Loveridge
Linda Loveridge
Louisa Revel
Mark Mitchell
Alan Brown
James Woodrow

MSSQL Data Types

A summary of the MS SQL data types is given in the table below:

Data Type Syntax Comments
int int Whole number from -2^63 (-9,223,372,036,854,775,808) to
2^63-1 (9,223,372,036,854,775,807), inclusive.
smallint bigint Whole number from -2^31 (-2,147,483,648) through 2^31 –
1 (2,147,483,647), inclusive.
smallint smallint Whole number from 2^15 (-32,768) through 2^15 – 1
(32,767), inclusive.
tinyint tinyint Whole number from0 through 255
bit bit Equivelent to True/False takes value of 0 or 1
numeric numeric(p,s) Where p is a precision value; s is a scale value. For example, numeric(8,3)
represents a number 8 digits in length, 5 before the decimal point and 3 after.
decimal decimal(p,s) Where p is a precision value; s is a scale value.
money money Monetary data values ranging from -2^63
(-922,337,203,685,477.5808) to 2^63 – 1 (+922,337,203,685,477.5807),
inclusive.
smallmoney smallmoney Monetary data values ranging from -214,748.3648 to
+214,748.3647, inclusive.
float float Floating point number, having the the two data ranges:
-1.79E + 308 to -2.23E – 308, 0 and 2.23E + 308 to 1.79E + 308, inclusive.
real real Floating point number, having the the two data ranges:
-3.40E + 38 through -1.18E – 38, 0 and 1.18E – 38 through 3.40E + 38., inclusive.
datetime datetime Date time having a range of 1st January 1753, to 31st
December 9999
smalldatetime smalldatetime Date time having range of 1st January 1900, to 31st
December 2079
char char Variable length non-unicode data having a maximum length
of 8000 characters.
varchar varchar(x) Variable length non-unicode data having a maximum length
of 8000 characters. This is refined using the paramater x.
text text Variable length non-unicode data having a maximum length
of 2^31-1 characters.
nchar nchar Unicode equivelent to char. Variable length non-unicode
data having a maximum length of 4000 characters.
nvarchar nvarchar(x) Unicode equivelent to varchar. Variable length non-unicode data having a maximum length
of 4000 characters.
ntext ntext Unicode euivelent to text. Variable length non-unicode
data having a maximum length of 2^31-1 characters.
cursor cursor A reference to a cursor
sql_variant sql_variant Stores values of various SQL data types, except text,
ntext, timestamp and sql_variant.
table table Stores a result set for future processing
timestamp timestamp A unique number, updated each time a row is updated.
uniqueidentifier uniqueidentifier A globally unique identifier (GUID).

Create a New Database User

A new Database SQL user can be added from the DotNetNuke Host SQL page. This user can then be used as a replacement for the existing user in the connection string in the web.config file.

In the example below a new user called , dnnDbUser, will be created and added as a db owner of the dnnDB database. The password assigned to the user will be dnnDbPword

To add the new user login of dnnDbUser enter and run the code:

exec sp_addlogin ‘dnnDbUser’, ‘dnnDbPword’

The new user needs to be given the required permissions:

exec sp_grantdbaccess ‘dnnDbUser’

Lastly, to assign the database access to the user:

exec sp_addrolemember @rolename=’db_owner’,@membername=’dnnDbUser’

Get Stored Procedure Using SQL

Not having backend access to a DotNetNuke website, or readily able to download a backup of the database, my thoughts turned to how to review a stored procedure with SQL from the SQL host page in DotNetNuke.

SELECT
text
FROM
syscomments
WHERE
id = (SELECT id FROM sysobjects WHERE name = ‘StoredProcedureName’)
ORDER BY
colid

Given the following:

  • Name = Stored Procedure Name
  • Colid = Multiple lines, their sequence.
  • Replace StoredProcedureName with the name of the stored procdure of interest

For reference, in case you can’t remember the name of the stored procedure, and seeing them all might be sufficient to refresh your memory:

SELECT
*
FROM
sysobjects
WHERE
type = ‘P’ AND category = 0
ORDER BY
name

Given the following:– Get Stored Procedures

  • Type = ‘P’ (Stored Procedure)
  • Category = 0 (User Created)