Monday, October 24, 2016

Cisco VPN not Working on Windows 10

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
Share:

Monday, June 20, 2016

Find Maximum Value from Multiple Columns

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

Share:

Friday, January 22, 2016

Calculating Customer's Latency

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!

Share:

Thursday, August 6, 2015

A Severe Error Occurred on The Current Command

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
Share:

Tuesday, January 27, 2015

XML Data Type is Not Supported in Distributed Queries

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
Share:

Thursday, November 27, 2014

Add Trusted Hosts to Tableau Server

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!
Share:

Wednesday, October 29, 2014

Report cannot be Displayed

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.
Share:

Tuesday, September 23, 2014

Combine Rows into a Column

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('')


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

Tuesday, November 5, 2013

Mapping User Login and Database Roles to Existing User

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.
Share:

Current Transaction Cannot be Committed

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.
Share:

Directory Lookup Failed on SQL Server

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.
Share:

About

Related Posts

Cisco VPN not Working on Windows 10

Error: Cisco VPN is not working (cannot login and connect to VPN connection). Solution: 1. Right-click on the VPN client, and Run as Ad...

About Me

My photo
Is an ordinary unNoticed man, with a little knowledge to share and high dreams to achieve. I'd be glad if I can help others, 'coz the only thing for the triumph of evil is for a good man to do nothing. I like English and Chinese language, and know a little about them. Hence, for me, most things can best be expressed in Chinese, some in English, and the rest in others ^^

About Blog

You can find a lot of debugging and deploying problems while developing applications in .NET and Visual Basic here. There are also some querying tips in SQL and typical source codes which might be useful shared here.

Popular Posts

Blogroll

Followers

Follow by Email