Rename a Column Name - SQL Sever

Recently I had a requirement to change the old table column name. therefore I needed a query to execute and rename column name. Following simple query script help me to fix it.

Script for Rename any column

SP_RENAME  'TableName.OldColumnName','NewColumnName','Column'

E.g.

USE [DataBase]
GO

SP_RENAME  'LKP_Item_Rate.Item','ItemRate','Column'

GO

Query is useful. when we have lot of data in the table and we needed to change the table columns without drop it.

Pagination in MDX

IF you want to Pagination the set of data using MDX. Following article might be helpful.We go through this using example

E.g. you need to display set of data in Page Number = 2 and Page Size = 10

Following mention two ways might be help.

Using SUBSET () Function

Subset function returns a subset that contains the specified number of tuples, beginning at the specified start position. The start position is based on a zero-based index. Therefore when we called page number we are reduce -1 from page number and passes to start position and multiply by page size because we need to get correct index point to start the page.

SELECT {[Measures]. [Measure1]

              } ON COLUMNS,

              SUBSET

                     (

                     ORDER

                           ({Dimension.ALLMEMBERS}, NULL, BASC)

                           , (2 -1)* 10

                           , '10

                     )

                     ON ROWS

FROM [Cube]

Using TAIL () and HEAD () Functions

The Head function returns the specified number of tuples from the beginning of the specified set. The order of elements is preserved. The default value of Count is 1

The Tail function returns the specified number of tuples from the end of the specified set. The order of elements is preserved. The default value of Count is 1

Process of this query here is.

When we multiply page number and page size you have 20 rows count starting from head to bottom. But we need final 10 rows only. For that we use tail function.

SELECT {[Measures]. [Measure1]

              } ON COLUMNS,

              TAIL (

                     HEAD (

                           {Dimension.ALLMEMBERS}

                     , 2 * 10)

              , 10)

       )} ON ROWS 

FROM [Cube]

When we do the pagination my most preferable function is subset function.

Because when we only have 1 value in return. But when we think about it should not capture in page 2. But using tail and head function that display it. If you use subset function that does not display.

Therefore it depends on what requirement you have and what output you need. 

Error: Hexadecimal value 0x1D, is an invalid character

When I run the SSRS report of mine I found this error. SSRS report just simply display this error while running. When you looking at this error we must have realize this is a data issue. This data issue came from because of one of the dimension attribute has hexadecimal value.

Exact error:

“An error occurred during local report processing. An error has occurred during report processing. Cannot read the next data row for the dataset MainDataSet. The server send an unrecognizable response. “Hexadecimal value 0x1D, is an invalid character. Line 22, position 347777558.”

We can fix this error in many ways. Of course we are not going to change this in report. It is a data issue so we mainly focus to our databases. First need to identify what data generate this error. Using following query you can identify the data that affect to this error.

Select NUMBER AS Error Data

From TableName

Where NUMBER like '%' + Char (ASCII (0x1D)) +'%'

Using following query we can temporary fix the issue in database.

Select REPLACE (NUMBER, Char(Ascii(0x1D)), char(0)) AS cleance_Data

From TableName

Where NUMBER like '%' + Char (ASCII (0x1D)) +'%'

I am not going to fix the issue in database level. As a fix I did it inside the SSAS cube level. It may be not the best solution. But in my scenario cube level fix is sufficient for me.

Go to the SSAS cube dimension and double click an open the dimension. Inside the dimension attribute properties. There is Key column and Name column. Expand both key column and Name column then you can see the property call InvalidXMLCharacters. There are three options here preserve, remove and replace. Use remove option. 

Then you can run report without any hexadecimal value error.

SQL Server: SET IDENTITY_INSERT {ON | OFF} Statement

I needed to add a column to one of our main table. I get the employee table as an example. I needed to add   “Unknown” Employee with a unique surrogate key. When we think surrogate key column is an identity column to that table. Following statement helped me to insert that row to employee table.

Syntax

SET IDENTITY_INSERT [database name]. [Schema name].table {ON | OFF} 

If we want to force a value of our choice needed to insert then we use SET IDENTITY_INSERT table Name ON

E.g.  Created Example table with an identity column

CREATE TABLE [dbo].[Employee](

            [Emp_ID] [int] IDENTITY(1,1) NOT NULL,

            [Emp_Code] [varchar](20) NULL,

            [Emp_FullName] [varchar](35) NULL,

            [Emp_LastName] [varchar](152) NULL

)

           

GO

Pushing data to the identity column table without the command. When you execute the query you can see the following error:

Cannot insert explicit value for identity column in table 'Employee' when IDENTITY_INSERT is set to OFF.

INSERT INTO [dbo].[Employee]

           ([Emp_ID]

           ,[Emp_Code]

           ,[Emp_FullName]

           ,[Emp_LastName]

           )

     VALUES

           (0

           ,1

           ,'UNKNOWN'

           ,'UNKNOWN')

Using SET IDENTITY_INSERT <Table Name> ON command helps to push data to the table.

SET IDENTITY_INSERT [dbo].[Employee] ON

INSERT INTO [dbo].[Employee]

           ([Emp_ID]

           ,[Emp_Code]

           ,[Emp_FullName]

           ,[Emp_LastName]

           )

     VALUES

           (0

           ,1

           ,'UNKNOWN'

           ,'UNKNOWN')

    

SET IDENTITY_INSERT [dbo].[Employee] OFF

Ref: https://msdn.microsoft.com/en-us/library/ms188059.aspx

SQL Server :SET NOCOUNT { ON | OFF } Statement

Syntax

SET NOCOUNT { ON | OFF } 

Using the above syntax we can prevent showing affected rows by any TSQL query statement. Using SET NOCOUNT ON message inside the stored procedure we can improve performance by a significant margin. If there limited amount of data transferring SET NOCOUNT ON statement not much affected but if we have loops and large amount of data this statement can be useful

E.g.

CREATE PROCEDURE [dbo].[USP_Xxxxx]

AS

SET NOCOUNT ON

  BEGIN

      BEGIN TRY

          INSERT INTO [Dbo].[Xxxxx]

SET NOCOUNT ON: count row is not returned. Just a successfully completed message

SET NOCOUNT OFF: count is returned

Ref: https://msdn.microsoft.com/en-us/library/ms189837.aspx