Programmer's Laboratory

Updating Table Containing Xml Column via LinkedServer

2024-03-19T14:49:00.004+07:00

If you are trying to update a table containing XML column via Linked Server in SQL Server, and you are not able to, you are not alone.

There are 2 main issues in this scenario:
1. Accessing a table containing XML column via Linked Server
2. Updating the XML value

When trying to select the XML column of a table via linked server:

SELECT col1, col2, XmlColumn
FROM [LinkedServer].[DatabaseName].dbo.TableName

Error encountered:

Xml data type is not supported in distributed queries. Remote object '[LinkedServer].[DatabaseName].dbo.TableName' has xml column(s).

There are a few workarounds to resolve this (we will focus on using view in this post):

1. Use OPENQUERY

2. Create a view that selects a converted non-XML value from the XML column in the destination DB.

--In destination DB
CREATE VIEW vw_NonXmlColumn
AS
SELECT col1, col2,
    XmlColumn = CONVERT(NVARCHAR(MAX), XmlColumn)
FROM TableName WITH(NOLOCK)

Then, on the source DB where you create the Linked Server, you can access the view instead:

--In source DB
select col1, col2, XmlColumn
from [LinkedServer].[DatabaseName].dbo.vw_NonXmlColumn

This resolves our first issue: accessing a table containing XML column via Linked Server.

If you try to perform an update of the converted XML column:

--In source DB
UPDATE [LinkedServer].[DatabaseName].dbo.vw_NonXmlColumn
SET XmlColumn = '<new XML value>'
WHERE <condition>

You will encounter another error:

Statement(s) could not be prepared.

Update or insert of view or function '[DatabaseName].dbo.view' failed because it contains a derived or constant field.

To resolve this, we can use "Instead of Update" trigger.

On source DB, create a trigger when the view update is executed:

--In source DB
CREATE TRIGGER TR_TableName_InsteadOfUpdate ON vw_NonXmlColumn
INSTEAD OF UPDATE
AS
BEGIN
    SET NOCOUNT ON

    UPDATE TableName
    SET XmlColumn = i.XmlColumn
    FROM TableName t
    INNER JOIN inserted i
        ON i.ID = t.ID --Unique identifier of the table
END

You can now use back the same query used to update the XML column via Linked Server successfully:

--In source DB
UPDATE [LinkedServer].[DatabaseName].dbo.vw_NonXmlColumn
SET XmlColumn = '<new XML value>'
WHERE <condition>

PS: Try to simplify the update statement by using variables if necessary, if there is any issue encountered when performing the update to the view.

Creating Dynamic Pivot Table using SQL

2021-05-12T15:49:00.002+07:00

In this post, I want to share SQL tips on how to create dynamic pivot table in SQL.

The columns in the pivot table is not fixed in this case. Hence, there is a need to use dynamic SQL to achieve this.

In this example, we will be forming a pivot table containing spending by outlet from spending data.

SQL Query:

--prepare data source
create table #TmpSpending
(
	MemberID nvarchar(100),
	Outlet nvarchar(100),
	TransactDate date,
	Spending numeric(18,4)
)

insert into #TmpSpending values ('M0001', 'Outlet A', '2021/01/05', 100)
insert into #TmpSpending values ('M0001', 'Outlet B', '2021/01/15', 200)
insert into #TmpSpending values ('M0002', 'Outlet A', '2021/02/09', 300)
insert into #TmpSpending values ('M0003', 'Outlet B', '2021/01/10', 400)
insert into #TmpSpending values ('M0003', 'Outlet B', '2021/02/15', 500)

select * from #tmpspending

--temporary table for output
create table #tmpOutput (
	Outlet nvarchar(100)
)

--initial insert into #tmpOutput
insert into #tmpOutput
select distinct outlet
from #TmpSpending
order by outlet

--store the list of [month year] first
select distinct [Year] = year(transactdate), 
	[Month] = month(transactdate),
	MonthYear = left(datename(month, transactdate), 3) + ' ' + convert(varchar, year(transactdate))
into #tmpDates
from #tmpspending

--construct the table with dynamic columns using cursor
declare @year int, @month int
declare @monthyear nvarchar(8)
declare @sql nvarchar(max) = '' --dynamic sql for columns addition

declare cur1 cursor for
select [year], [month], MonthYear
from #tmpDates
order by [year], [month]

open cur1

fetch next from cur1
into @year, @month, @monthyear

while @@FETCH_STATUS = 0
begin
	
	--add column to #tmpOutput
	set @sql = 'alter table #tmpOutput add [' + @monthyear + '] numeric(18,4)'
	exec (@sql)

	--update #tmpOutput
	set @sql = 'update o '
	set @sql += 'set [' + @monthyear + '] = s.spending '
	set @sql += 'from #tmpOutput o '
	set @sql += 'inner join (	select outlet, spending = sum(spending) '
	set @sql += '				from #tmpspending '
	set @sql += '				where year(transactdate) = ' + convert(varchar, @year) + ' '
	set @sql += '				and month(transactdate) = ' + convert(varchar, @month) + ' '
	set @sql += '				group by outlet '
	set @sql += ') s on s.outlet = o.outlet '
	exec(@sql)

	--fetch next record
	fetch next from cur1
	into @year, @month, @monthyear
end

close cur1
deallocate cur1

select * from #tmpOutput

drop table #tmpDates
drop table #TmpSpending
drop table #tmpOutput

First, we prepare the data source which looks like this.

Afterwards, we use cursor (or you can use while loop) to create additional columns on the table on the fly, to form up the pivot table.

This is the end result of the pivot table.

Happy querying! :)

Activate Tableau Server Offline

2021-03-17T13:51:00.004+07:00

If you are doing Tableau server license activation offline, it may be a bit problematic, and confusing.

There is already an article published by Tableau showing how to do Tableau server license activation offline. However, the last part about how to process the activation.tlf is not mentioned.

Hence, I am creating some guide on how to do that.

After you upload the offline.tlf file on Tableau site, and get the activation.tlf back, you need to:

1. Place the activation.tlf file somewhere in the server where you have your Tableau server installed. e.g. D:\activation.tlf

2. Open command prompt as Administrator from Start menu.

3. Go to the Tableau server directory where tabadmin.bat is located. Usually it is located at C:\Program Files\Tableau\Tableau Server\<version>\bin.

cd\Program Files\Tableau\Tableau Server\<version>\bin

4. Use this command to activate using the activation.tlf file:

tabadmin activate -tlf D:\activation.tlf

5. That's all, folks.

Using Macro in Notepad++

2021-02-05T16:31:00.002+07:00

Sometimes, you need to bulk-edit text with the same patterns. However, you can't simply use text editor to find and replace the content. In this case, you can try using macro in Notepad++ to achieve that.

This is a simple video on how you can do that using Notepad++:

Split Comma-Delimited Column into Multiple Rows

2020-11-04T13:27:00.001+07:00

We had post about combining multiple rows into a column in the past. Sometimes, on the contrary, we would want to split character-delimited column into multiple rows. In this post, we will see try to split comma-delimited column into multiple rows. Sample Data:

End result after split into rows:

Query:


-- Create temporary table to store UserGroup
create table #UserGroup (GroupID int, UserGroup nvarchar(1000), Users nvarchar(1000))
insert into #UserGroup values (1, 'Group 1', 'Himura,Selvi,Superman')
INSERT INTO #UserGroup VALUES ( 2, 'Group 2', 'Luck,Smarty')
INSERT INTO #UserGroup VALUES ( 3, 'Group 3', 'Lucky')

--Display the sample data
select * from #UserGroup

--Split comma-delimited column into multiple rows
;WITH tmp(GroupID, UserGroup, Username, Users) AS
(
	SELECT GroupID, 
		UserGroup,
		convert(nvarchar(1000), LEFT(Users, CHARINDEX(',', Users + ',') - 1)),
		convert(nvarchar(1000), STUFF(Users, 1, CHARINDEX(',', Users + ','), ''))
	FROM #UserGroup
	where Users is not null
	UNION all

	SELECT GroupID, 
		UserGroup,
		convert(nvarchar(1000), LEFT(Users, CHARINDEX(',', Users + ',') - 1)),
		convert(nvarchar(1000), STUFF(Users, 1, CHARINDEX(',', Users + ','), ''))
	FROM tmp
	WHERE
		Users > ''
)
select GroupID, UserGroup, Username
from tmp
order by GroupID, UserGroup

Using SQL Cursor for Looping

2019-09-19T15:03:00.000+07:00

If you ever heard of SQL cursor, this is probably not a new thing to you. If you never heard or use cursor in SQL before, we usually use cursor to replace the ordinary looping in SQL. The performance is generally better compared to using WHILE or ordinary looping method, depending on how it is implemented. Reason being, cursor is stored in memory.

I have a good example on usage of cursor in SQL.
In CRM & loyalty, we are looking at membership and loyalty points. Customers earn points when they transact or purchase something.
In this scenario, there is a points cap for members transactions, in which customers can only earn x number of points from their purchase.

We want to retrospectively calculate the correct points earned given customer's transactions and the points they earned.

Example of the records:

Points is capped at member-level. This means, different customer / member can have different points cap, denoted in the last column in the example above.
Since we are looking at member-level, we will look at the re-arranged version of the records.

This is the end result we are looking at. Observe the last field in the table. The content in the Correct_PointsEarned is always within the points cap for each member, in the order of the transaction sequence. Once the points cap has been met, the Correct_PointsEarned will be 0 for that member.

You should familiarize yourself with how to create a cursor first.
This is a template of how a cursor looks like:

--START CURSOR
declare cur1 cursor for
select *
from 
order by 

open cur1

fetch next from cur1
into @zzz, @sss, ......

while @@FETCH_STATUS = 0
BEGIN
 --do the necessary processing here
 
 
 --fetch next record
 fetch next from cur1
 into @zzz, @sss, ......
END

close cur1
deallocate cur1
--END CURSOR

This is the script to create temp table to populate the data:

create table #TmpPointsCap (
 TransactAutoID bigint identity(1,1),
 MemberID nvarchar(100),
 TransactDate date,
 TransactPoints numeric(18,4),
 PointsCap numeric(18,4),
 Correct_PointsEarned numeric(18,4) default(0.00),
 AddedOn datetime default(getdate())
)

insert into #TmpPointsCap (MemberID, TransactDate, TransactPoints, PointsCap)
values ('M0001', '2019/01/01', 100, 700)

insert into #TmpPointsCap (MemberID, TransactDate, TransactPoints, PointsCap)
values ('M0002', '2019/01/02', 200, 500)

insert into #TmpPointsCap (MemberID, TransactDate, TransactPoints, PointsCap)
values ('M0004', '2019/01/02', 400, 300)

insert into #TmpPointsCap (MemberID, TransactDate, TransactPoints, PointsCap)
values ('M0001', '2019/01/03', 500, 700)

insert into #TmpPointsCap (MemberID, TransactDate, TransactPoints, PointsCap)
values ('M0003', '2019/01/03', 700, 1000)

insert into #TmpPointsCap (MemberID, TransactDate, TransactPoints, PointsCap)
values ('M0001', '2019/01/05', 200, 700)

insert into #TmpPointsCap (MemberID, TransactDate, TransactPoints, PointsCap)
values ('M0002', '2019/01/06', 100, 500)

insert into #TmpPointsCap (MemberID, TransactDate, TransactPoints, PointsCap)
values ('M0003', '2019/01/06', 300, 1000)

insert into #TmpPointsCap (MemberID, TransactDate, TransactPoints, PointsCap)
values ('M0002', '2019/01/06', 400, 500)

insert into #TmpPointsCap (MemberID, TransactDate, TransactPoints, PointsCap)
values ('M0002', '2019/01/08', 200, 500)

insert into #TmpPointsCap (MemberID, TransactDate, TransactPoints, PointsCap)
values ('M0003', '2019/01/08', 100, 1000)

insert into #TmpPointsCap (MemberID, TransactDate, TransactPoints, PointsCap)
values ('M0004', '2019/01/09', 100, 300)

insert into #TmpPointsCap (MemberID, TransactDate, TransactPoints, PointsCap)
values ('M0005', '2019/01/10', 200, 200)

--Original table content
select * from #Tmppointscap

--The data sorted in the order we want
select TransactAutoID, MemberID, TransactDate, TransactPoints, PointsCap, Correct_PointsEarned = 0
from #TmpPointsCap
order by MemberID, TransactDate, AddedOn

And this is how we use cursor to achieve the correct point earned in the last column:

--Temporary variables to store the table fields
declare @MemberID nvarchar(100), @TransactAutoID bigint, @TransactPoints numeric(18,4), @PointsCap numeric(18,4)
declare @PrevMemberID nvarchar(100) = ''
declare @RunningPoints numeric(18,4) = 0


--START CURSOR
declare cur1 cursor for
select MemberID, TransactAutoID, TransactPoints, PointsCap
from #TmpPointsCap
order by MemberID, TransactDate, AddedOn

open cur1

fetch next from cur1
into @MemberID, @TransactAutoID, @TransactPoints, @PointsCap

while @@FETCH_STATUS = 0
BEGIN
 --do the necessary processing here
 if @MemberID <> @PrevMemberID
 begin
  --set initial running points as the 1st TransactPoints
  set @RunningPoints = 0
 end
 
 if @RunningPoints < @PointsCap and (@RunningPoints + @TransactPoints) > @PointsCap
 begin
  --if after adding the TransactPoints, it is bigger than the pointscap
  --only earn at most, as much as the pointscap
  update #TmpPointsCap
  set Correct_PointsEarned = @PointsCap - @RunningPoints
  where TransactAutoID = @TransactAutoID

  --Update RunningPoints = PointsCap
  set @RunningPoints += @TransactPoints
 end
 else if (@RunningPoints + @TransactPoints) = @PointsCap
 begin
  --earning all the transactpoints just good
  update #TmpPointsCap
  set Correct_PointsEarned = @TransactPoints
  where TransactAutoID = @TransactAutoID
  
  set @RunningPoints += @TransactPoints
 end
 else if @RunningPoints < @PointsCap
 begin
  --update the running points
  set @RunningPoints += @TransactPoints
  
  --update the correct points earned in the table
  update #TmpPointsCap
  set Correct_PointsEarned = TransactPoints
  where TransactAutoID = @TransactAutoID
 end
 else
 begin
  --not earning any points anymore
  update #TmpPointsCap
  set Correct_PointsEarned = 0
  where TransactAutoID = @TransactAutoID
 end
 
 set @PrevMemberID = @MemberID

 --fetch next record
 fetch next from cur1
 into @MemberID, @TransactAutoID, @TransactPoints, @PointsCap
END

close cur1
deallocate cur1
--END CURSOR

--Check the temp table again after updated
select TransactAutoID, MemberID, TransactDate, TransactPoints, PointsCap, Correct_PointsEarned
from #TmpPointsCap
order by MemberID, TransactDate, AddedOn

Good Luck, and Happy Query-ing!

Connecting to SQL Instance with Non-Default Port on Pentaho

2019-09-10T06:28:00.000+07:00

If you are trying to connect to SQL instance on Pentaho, and your SQL instance is using non-default port, then you need to fill in the connection string a bit differently.

This is how we normally fill in the connection to SQL instance, when connecting to default port.

If you are connecting to some other ports, you need to explicitly type in the connection parameters inside the IP field.

Dual Axis not Working in Tableau

2017-12-19T10:36:00.001+07:00

Problem:
When creating chart with Dual Axis, Synchronize Axis is disabled and cannot be clicked.

Cause:
Tableau identified this as a bug, though it is supposed to have been fixed on later version, but it somehow still happens.

Solution:
1. To enable Synchronize Axis, make sure that the data type of both the measures are the same.

2. If you are using Integer data type, problem persists even if the data types are the same. You should convert the measures to float.
Notice that now the Synchronize Axis is enabled after we convert the data type to float.

Format it with 0 decimal points if you want it to appear as integer.

And this is how the end result looks like.

PS: I know the graph is ugly, but I hope you get what you need :)

Tableau Server Unable to Start due to Blocked Ports

2017-11-23T16:28:00.003+07:00

Problem:
Sometimes you might need to use different (from the default port) port on Tableau. By default, SSL is using port 443. For my case, I use SSL and port 8443 for Tableau Server. After you changed the port, and tried to start Tableau service, you get this error:
*** Tableau Server Gateway requires port 443,
*** in use by process 4.
*** Tableau Server unable to start due to blocked ports.

Solution:
There are 2 files you need to change:
1. C:\Tableau\Tableau Server\data\tabsvc\config\httpd.conf
2. C:\Program Files\Tableau\Tableau Server\10.3\apache\conf\original\extra\httpd-ssl.conf

Change the port to listen to 8443 (replace 443 to 8443) on these 2 files.

Frame cannot Load - Content Security Policy Issue

2017-11-08T16:36:00.000+07:00

Problem:
You are using iframe to load a site from within a website, and the frame does not load.
In my case, I am using https for both the website and the iframe.

Error:
If you press F12 on the browser, you will see error similar to:

Refused to frame 'https://iframe_site' because it violates the following Content Security Policy directive: default-src 'self' 'unsafe-inline' 'unsafe-eval'...... (this can be found in web.config). Note that 'script-src' was not explicitly set, so 'default-src' is used as a fallback.

Solution:
Add the iframe_site to content-security-policy on the web.config.

<httpProtocol>
 <customHeaders>
  <add name="Content-Security-Policy" value="default-src 'self' 'unsafe-inline' 'unsafe-eval' iframe_site;" />
 </customHeaders>
</httpProtocol>

PS: If you are using port for the iframe site, you would need to include the port when adding into web.config.

Cisco VPN not Working on Windows 10

2016-10-24T08:57:00.000+07:00

Error:
Cisco VPN is not working (cannot login and connect to VPN connection).

Solution:
1. Right-click on the VPN client, and Run as Administrator.
2. Open regedit and go to HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\CVirtA and rename:

x86 - "@oem8.ifn,%CVirtA_Desc%;Cisco Systems VPN Adapter" to "Cisco Systems VPN Adapter"
x64 - "@oem8.ifn,%CVirtA_Desc%;Cisco Systems VPN Adapter for 64-bit Windows" to "Cisco Systems VPN Adapter for 64-bit Windows"

Source partially from: IT that should just work

Find Maximum Value from Multiple Columns

2016-06-20T13:14:00.000+07:00

A colleague asked about how to get maximum value from multiple columns. After searching it online, I found a pretty good way to get it, and thought it would be good to share here.

For example, I have a table with MemberID and 3 date fields. I want to get the last date from the 3 date fields for each MemberID.

The expected end result look like:

Creating the sample data:

create table #member ( MemberID nvarchar(100), modifiedon datetime, deletedon datetime, addedon datetime )
insert into #member values ('M1', GETDATE(), GETDATE(), GETDATE())
insert into #member values ('M2', GETDATE()-1, GETDATE()-2, GETDATE()-3)
insert into #member values ('M3', GETDATE()-3, GETDATE()-2, GETDATE()-1)

Query to get the max for multiple columns:

SELECT 
 MemberID, 
 LastUpdateDate =
 ( SELECT MAX(LastUpdateDate)
  FROM (VALUES (modifiedon),(deletedon),(addedon)) AS UpdateDate(LastUpdateDate)
 )
FROM #member

Calculating Customer's Latency

2016-01-22T14:39:00.000+07:00

There were times when I was asked to retrieve the latency of the customers in my work. At first, I was confused about how to retrieve this. After some thought, I find a solution, which is actually pretty simple, and the SQL usage is widely available. Unfortunately, when I tried searching it online, the SQL technique is seldom associated with latency problem in retail industry.

This is an illustration of what we want to achieve.
Given members and their transaction date. We want to find the average latency of members. To get the average latency, we would need to get the difference in days, between member's 1st and 2nd visit, 2nd and 3rd visit, 3rd and 4th visit, etc. The average of these differences will be the average latency of the members.

To do this on SQL, we can rank the member's transaction based on the TransactDate, and then self-join to the same set of records, but shift the ranking by one order. This way, we will get the difference of days between a transaction and the subsequent transaction.

Here are the SQL queries to prepare the data:


--Create temp table
CREATE TABLE #Transact
(
 MemberID NVARCHAR(100),
 RecieptNo NVARCHAR(100),
 TransactDate DATE
)

--Insert dummy data
INSERT INTO #Transact VALUES ('M001', 'R001', '2015/11/20')
INSERT INTO #Transact VALUES ('M001', 'R002', '2015/12/30')
INSERT INTO #Transact VALUES ('M001', 'R003', '2016/01/05')

INSERT INTO #Transact VALUES ('M002', 'R004', '2015/09/12')
INSERT INTO #Transact VALUES ('M002', 'R005', '2015/09/15')
INSERT INTO #Transact VALUES ('M002', 'R006', '2015/09/25')
INSERT INTO #Transact VALUES ('M002', 'R007', '2015/10/30')

Here are the SQL queries to get the Latency per member:


--Store the ranked transaction into temp table. Alternately, use common table expression
SELECT MemberID, TransactDate, RankNo = ROW_NUMBER() OVER(PARTITION BY MemberID ORDER BY TransactDate)
INTO #1
FROM #Transact

--Get the average latency per member
SELECT MemberID, Latency = AVG(CONVERT(NUMERIC(18,2), Latency))
FROM ( SELECT a.MemberID,
   Latency = DATEDIFF(DAY, a.TransactDate, b.TransactDate)
  FROM #1 a
  INNER JOIN #1 b
   ON b.MemberID = a.MemberID
   AND b.RankNo = (a.RankNo + 1) 
) aa GROUP BY aa.MemberID

That's all folks!

A Severe Error Occurred on The Current Command

2015-08-06T16:01:00.001+07:00

SQL Error:
A severe error occurred on the current command. The results, if any, should be discarded.

Solution:
Disconnect and connect to the SQL database again.

Source: Stackoverflow

XML Data Type is Not Supported in Distributed Queries

2015-01-27T15:23:00.001+07:00

Error: Xml data type is not supported in distributed queries. Remote object '<ServerIP>\<ServerInstance>.<DatabaseName>.<Owner>.<TableName>' has xml column(s).

Problem:
This error occurs when trying to select data from table on different server (using linked server) containing XML data type (even though the column with XML data type is not selected).

Solution:
Create a view on the remote server, and then select the view instead of directly selecting from the table containing XML data type.

e.g.
<ServerInstance> = 10.0.0.101
<DatabaseName> = DB1
<Owner> = dbo
<TableName> = Table1

Instead of using:

SELECT * FROM [10.0.0.101.DB1].dbo.Table1

Create a view on the remote server:

CREATE VIEW vGetTable1
AS
SELECT * FROM dbo.Table1

And call the view:

SELECT * FROM [10.0.0.101.DB1].dbo.vGetTable1

Add Trusted Hosts to Tableau Server

2014-11-27T16:00:00.000+07:00

Problem: After deploying Tableau dashboards, you are not able to display the dashboard using trusted ticket method. Error returned: "Could not locate unexpired trusted ticket <ticket_number>".

Solution: Although there could be various reason for this error, you might want to check if the IP address in which the Tableau dashboard is going to be displayed, is already added to the trusted hosts in the Tableau server. If it is not yet added, add it to the trusted hosts.
Note: Make sure not to overwrite the existing trusted hosts already added previously.

Assume the host (where the Tableau dashboard is going to be displayed) is: 10.0.0.99

1. Go to the Tableau server, and look for dataserver.properties, located in C:\ProgramData\Tableau\Tableau Server\data\tabsvc\config\ (location might vary).
Search for trusted_hosts, and take note of the existing trusted hosts already added previously.
e.g. trusted_hosts=10.0.0.91, 10.0.100.92. We will include these IPs again later.

2. Add the IP address(es) where Tableau dashboard is going to be displayed. Open Command Prompt, then go to C:\Program Files (x86)\Tableau\Tableau Server\7.0\bin\ (location might vary), and use the command

tabadmin set wgserver.trusted_hosts "<Trusted IP Addresses>"

to add the trusted hosts. Don't forget to also include the existing trusted IP addresses previously added, separated by comma if there is more than 1 IP address.

e.g.

tabadmin set wgserver.trusted_hosts "10.0.0.91, 10.0.100.92, 10.0.0.99"

3. Save the configuration by using the command:

tabadmin config

4. Check the file dataserver.properties on step 1 and look for trusted_hosts. Now, the content of the trusted hosts should look like trusted_hosts=10.0.0.91, 10.0.100.92, 10.0.0.99.

5. Restart tableau server by using the command:

tabadmin restart

or run services.msc, then look for Tableau Server (tabsvc) service, and then restart the service.

6. Verify if the Tableau dashboard can be displayed properly (in this case, from 10.0.0.99).

7. That's all, folks!

Report cannot be Displayed

2014-10-29T16:32:00.001+07:00

Error:
- Event not fired.
- Microsoft.Reporting.WebForms.HttpHandlerInputException: Missing URL parameter: IterationId
(This is only part of the long error message).
- Report loads forever (never stops loading).

Solution:
On the aspx page, add attribute: EnableEventValidation="false"

For my particular case, I was trying to display a report (SSRS), and it never stopped loading. However, for my case, it was because of the version of the Internet Explorer (yes, the report is only compatible with IE for mine). After changing the version of the IE through developer tool (F12), I managed to display the report properly. You can also try to display the report from another machine using the intended IE version to see if it displays properly.

Combine Rows into a Column

2014-09-23T14:43:00.002+07:00

Happened to find a way to combine rows into a column. I find it quite useful, and it comes in handy.

For illustration, suppose we have UserGroup & Users. We want to list the users belonging to each group.

UserGroup:

Users:

If we do normal joining of the 2 tables, it will look like:

If we combine the values on the rows into a column, it will look like:

Here is the query to do so:

-- Create temporary table to store UserGroup
SELECT GroupID = 1, UserGroup = 'Group 1' INTO #UserGroup
INSERT INTO #UserGroup VALUES ( 2, 'Group 2' )
INSERT INTO #UserGroup VALUES ( 3, 'Group 3' )

-- Create temporary table to store Users
SELECT UserID = 'Superman', GroupID = 1 INTO #Users
INSERT INTO #Users VALUES ( 'Luck', 2 )
INSERT INTO #Users VALUES ( 'Himura', 1 )
INSERT INTO #Users VALUES ( 'Selvi', 1 )
INSERT INTO #Users VALUES ( 'Smarty', 2 )
INSERT INTO #Users VALUES ( 'Lucky', 3 )

-- Normal Join
SELECT a.UserGroup, b.UserID
FROM #UserGroup a
LEFT JOIN #Users b
 ON b.GroupID = a.GroupID
ORDER BY a.UserGroup

-- Combine Rows into Column
SELECT UserGroup, Users = STUFF(( SELECT ', ' + UserID FROM #Users a
    WHERE a.GroupID = b.GroupID
    ORDER BY a.UserID
    FOR XML PATH('') )
   , 1, 2, '')
FROM #UserGroup b
ORDER BY UserGroup

-- This is the key query to combine rows into column
SELECT UserID + ', ' FROM #Users
WHERE GroupID = 1
FOR XML PATH('')

If you are concatenating a field which contains some HTML tag, it would be automatically encoded. To avoid that, consider using this query:

-- Combine Rows into Column
SELECT UserGroup, Users = STUFF(( SELECT ', ' + UserID FROM #Users a
    WHERE a.GroupID = b.GroupID
    ORDER BY a.UserID
    FOR XML PATH,TYPE).value('.[1]','nvarchar(max)')
   , 1, 2, '')
FROM #UserGroup b
ORDER BY UserGroup

Update: Looks like I posted something similar quite some time ago, but using different method: Selecting several rows into one row in SQL

Mapping User Login and Database Roles to Existing User

2013-11-05T12:57:00.001+07:00

Error:
User, group, or role 'xxx' already exists in the current database. (.Net SqlClient Data Provider)

Explanation:
This error happens when you try to add user mapping to the server logins using username which already exists in the database to be assigned.

In my case, the user exists in both server logins and database logins, but the user mapping and database roles on the server logins for this user cannot be modified although the user mapping is incorrect, because it already exists on the database logins.

Solution:
Run this script to update the user mapping accordingly.

USE <database_name>
EXEC sp_change_Users_login 'update_one', '<login_username>', '<login_username>'

This will update the user mapping and database roles on server logins to follow the ones on the database logins.

Current Transaction Cannot be Committed

2013-11-05T12:51:00.001+07:00

Error:
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.

This error occurs when you try to perform other operations when any transaction fails in try catch section, soon after it goes to catch section.

Solution:
You have to ROLLBACK first on the catch section before doing any other operations.

Directory Lookup Failed on SQL Server

2013-11-05T12:45:00.001+07:00

When executing script on SQL server, this error occurs:
Directory lookup for the file <filepath> failed with the operating system error 2(The system cannot find the file specified.)

Directory lookup for the file <filepath> failed with the operating system error 3(The system cannot find the path specified.)

Solution:
Check the directory used in the SQL script. Make sure that the directory used in the SQL script point to the server location, if the script is not directly executed from the server.

Parameter Sniffing

2013-07-03T09:30:00.001+07:00

Parameter sniffing has a weird symptom which can take your hours to find out what actually is happening. The stored procedure you created is running on one side, but when you deploy it on another machine or server, it never ends executing despite the fact that everything is the same.

This problem usually occurs when the query contains LIKE condition. However, it is not limited to this case. Query without LIKE condition can also face this problem.

For example, here is the normal stored procedure:

CREATE PROCEDURE dbo.spr_GetData
@Parameter1 INT,
@Parameter2 VARCHAR(200)
AS
SELECT *
FROM dbo.Table1
WHERE Column1 = @Parameter1
AND Column2 LIKE '%' + @Parameter2 + '%'

To avoid parameter sniffing, there is a need to create new variables to store the values fetched from the parameters.

CREATE PROCEDURE dbo.spr_GetData
@Parameter1 INT,
@Parameter2 VARCHAR(200)
AS

DECLARE
@DummyParameter1 INT = @Parameter1,
@DummyParameter2 VARCHAR(200) = @Parameter2

SELECT *
FROM dbo.Table1
WHERE Column1 = @DummyParameter1
AND Column2 LIKE '%' + @DummyParameter2 + '%'

Error Converting Data Type Int to Nvarchar

2013-05-30T16:39:00.001+07:00

Error: Error converting data type int to nvarchar

This error occurs when a stored procedure which returns a value is used.

e.g

CREATE PROCEDURE [dbo].[spr_SP1]
@ColA INT,
@AutoID INT OUTPUT
AS
BEGIN
 INSERT INTO TableA ( ColA )
 VALUES ( @ColA )

 SELECT @AutoID = SCOPE_IDENTITY();
END

When adding the parameter to the command on the code, usually the code used is:

command.Parameters.AddWithValue("@AutoID", System.DBNull.Value)
command.Parameters("@AutoID").Size = 4
command.Parameters("@AutoID").Direction = ParameterDirection.Output

However, this portion of code would possibly cause error during deployment. To avoid the error, use the code below instead:

Dim param = New System.Data.SqlClient.SqlParameter()
param.ParameterName = "@AutoID"
param.Direction = ParameterDirection.Output
param.Size = 8
command.Parameters.Add(param)

An Error Occurred While Parsing EntityName

2013-04-08T15:57:00.000+07:00

Error: An Error occurred while parsing EntityName. Line 17, position 211.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Xml.XmlException: An error occurred while parsing EntityName. Line 17, position 211.

Source error: responseXml.LoadXml(response)

This error occurs because there is an ampersand (&) in the returned XML command.

Solution: Use “and” or other conjunctions instead of ampersand (&). Another option is to replace the string containing & with "%26".

Could not Load Type from Assembly

2012-11-29T14:05:00.001+07:00

Error:
Could not load type 'xxxxxx' from assembly 'AssemblyName, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null'.

This error occurs when trying to open a webpage. The project is built successfully. However, when accessing particular page, the error pops out. The error message is actually quite long. This is only part of the error message.

Solution:
On Visual Studio, go to menu Tools -> Options -> Debugging -> General -> Disable "Enable Just My Code" options.

PS: I am not sure if there is another more feasible solution. This might only be a workaround. There could be side effects. As of now, it is still working fine after this option is disabled.

Update:
Looks like this does not really solve the issue. I find out that if the project or solution is rebuilt, it will go to normal when the web is run. If it doesn't, rebuild, then try it again.

However, when the web is run on debug mode, some pages will get this error, thus not viewable. Another workaround is to view the web in browser (not in debug mode), then attach the process to the web. On Visual Studio, after the web is viewed in browser, go to menu Tools -> Attach to Process -> look for the web server process under which the web is run, e.g. WebDev.WebServer20.EXE