WITH keyword in SQL Server
Most people are using “WITH (NOLOCK)” instead of “ (NOLOCK)” and sometimes they asked you to put it “WITH” in front of it. But it is not necessary for the reason below
WITH is not required for 2 reasons:
1. The following table hints are allowed with and without the WITH keyword: NOLOCK, READUNCOMMITTED, UPDLOCK, REPEATABLEREAD, SERIALIZABLE, READCOMMITTED, FASTFIRSTROW, TABLOCK, TABLOCKX, PAGLOCK, ROWLOCK, NOWAIT, READPAST, XLOCK, and NOEXPAND
2. "WITH" keyword is deprecated feature in the future SQL server
Omitting the WITH keyword is a deprecated feature and will be removed in a future version of Microsoft SQL Server. Always specify WITH in new development work, and modify applications that currently omit this keyword.
Link:
http://msdn.microsoft.com/en-us/library/ms187373.aspx
Delayed Script/Waiting in Javascript
I was struggling in finding out of how to make a simple delegate like in AJAX request through javascript. It takes me a few days to figure this out
Basically the problems are:
-I need to execute a piece code of javascript after getting the ticket from web service function
-The webservice function might not be responding to the first request because it waits for the state of the other external component
-The webservice will give the response when the external component is ready
-The client does not know when the external component is ready, neither the web service. But it wil be ready within 1-5 minutes which again depending external component
Possible Solution:
-Using the setTimeOut(function(){acquireTicket();}, 300000) will cause the application to wait for 5 mins before calling the web service , this approach will slowing down the user experience and waste of time because the external component can be ready earlier than 5 mins
-Using the while loop is not good because it makes the browser freezing while waiting and it will wasting the processing power because of the looping
Recommended Solution:
-Recall the function by itself using setTimeout Function using parameter to indicate whether it should go out of the loop or not
-The web service will be checked for every 2 seconds to check the response from the external component. Once the external component is ready then it will move on to execute the next line of code
-
Access javascript object properties with invalid character
ParseJSON returning you an object from your AJAX Call, the problem that I have is my object properties has invalid character (e.g “#”)
Assuming jsonData is my variable that contains the following information
I’d like to grab the property of “#innerxml”
Normally I can do this to get the property of an object but in this case I can’t due to invalid character
(jQuery.parseJSON(jsonData)[0]).#innerxml
So How do I access an object which has properties where one of the property name is using an invalid character (e.g “#”)
I can access with the following style
(jQuery.parseJSON(jsonData)[0])['#innerxml']
Umbraco Installation in Shared Web Hosting
I tried to install umbraco in shared webhosting from the control panel but instead the error that I’m getting is
The application could not be installed: Error occured in Web App Gallery module
From my research it seems that there is an error in the Web App Gallery module, some article it says that the Web App Gallery module is out of date. But this is shared web hosting so there is nothing much I can do to fix this. So how to install umbraco manually?
1. Create a blank DB in your SQL Server 2008 for your Umbraco
2. Create a SQL User for the Umbraco DB in the step 1
3. Download umbraco from http://umbraco.codeplex.com
4. Extract the content to the downloaded package to the root of your domain wwwroot folder via FTP
5. Make sure the default website is already created
6. Make sure the .NET framework is being set to .NET 4.0 (Integrated)
7. Run the installation through www.yourdomain.com/install/default.aspx
8. Follow the steps, it will ask you the database name and the database user along the way
9. Once the installation finished, please make sure you delete the install folder for security purpose
Cleanup File name from invalid characters
I have a function that is used to save a file base on the user input (basically the user can type whichever filename and whichever path) and the code is not handling the invalid filename or path hence what it does is just throwing .NET general exception like below
To fix it I created a new function that basically get the invalid path characters and invalid filename characters from the system and remove invalid character in the input (file name). By doing this the user does not need to replace the character.
If you use Path.GetFileName it will actually remove the illegal character automatically but the way it removes the illegal character is so aggressive
e.g Path.GetFileName(“c:\workflow\Clearance:Photo ID Badge:Access abc-123.ist”) will return Access abc-123.ist
Well this problem itself will give different argument like why do we let people put the garbage character in?and why don’t we give the validation?or the other argument is “why do we need to change the input from the user without letting them knowing it?
236 ”’ <summary>
237 ”’ this function is used to clean up invalid/illegal characters from filename and replace it with blank
238 ”’ </summary>
239 ”’ <param name=”FileName”></param>
240 ”’ <returns></returns>
241 ”’ <remarks></remarks>
242 Private Function CleanFileName(ByVal FileName As String) As String
243 Dim invalid As String = New String(Path.GetInvalidFileNameChars()) + New String(Path.GetInvalidPathChars())
244 Dim originalPath As String = FileName.Substring(0, FileName.LastIndexOf(“\”) + 1)
245 FileName = FileName.Substring(FileName.LastIndexOf(“\”))
246
247 For Each c As Char In invalid
248 FileName = FileName.Replace(c.ToString(), “”)
249 Next
250
251 ‘readd the path
252 FileName = originalPath + FileName
253
254 Return FileName
255 End Function
Web Service Tester
This is an application to access your Web Service/API without worrying to create your own client application for the sake of testing. Personally, I’m using soapUI (an open source application to test web service)
http://sourceforge.net/projects/soapui/files/
I’d prefer to use tool this because it provides you a transparent communication between the client and the Web Service and it’s really simple to use and configure. It’d save you a lot of time to debug the issue for your client. You can trace it down whether the issue is in the client or in the web service itself
Profiling CLR for .NET application
CLR Profiler can be used to analyse how your object is allocated in memory and which object has taken the most memory. It also can be used to detect memory leak
To download CLR profiler for .NET 2.0 please visit http://www.microsoft.com/download/en/details.aspx?id=13382 , there is another version for .NET framework 1.1
-Extract the file into your desired location
-you also need to run “regsvr32 ProfilerOBJ.dll” otherwise you will get dialog message that mentioned it’s waiting for the CLR and you will not get any result
- Run the CLRProfiler.exe in Binariesx86 if your app is 32 bit and Binariesx64 if your app is 64 bit
For profiling windows app, you can do the following steps:
1. Select the exe file
2. Do stuff/interact with your application
3. Once you are finished then you can close your application or select “Kill Application”
4. Upon completion it will give you the screen below
For profiling ASP.NET app, you can do the following steps:
1. Select Profile ASP.NET from file menu
2. It will restart your IIS with the necessary trace
3. Select “Start ASP.NET”
4. Once you are finished then select “Kill ASP.NET”
5. It will restart the IIS and remove the trace added before
6. Upon completion it will give you the screen below
*You can use this for service as well –
-
- image0021
-
- image0031
-
- image0041
-
- image007
This is the information for GC
-
- image0051
-
- image0061
Remapping svc extension in IIS 7 extension handlers
If you open your WCF/ svc file in your localhost through browser and it just display blank page then what might happen is you don’t have handler associated with svc extension
To verify it, go to your IIS and go to IIS Handler Mappings and try to find an svc extension entry, but if you couldn’t find extension SVC exists on the list, then you can remap/re-add the svc extension by running
C:\Windows\Microsoft.NET\Framework64\v3.0\Windows Communication Foundation\ServiceModelReg.exe –i
*if you are still using 32 bit then you just need to replace Framework64 with Framework
SQL Statistical information – Index usage, etc and also tips on how to find the most costly SQL Server queries using DMV’s
This is just an information for the users that might not know this feature in SQL Server Management Studio, eventhough I’m sure most of people should have known this feature
Basically SQL Server stores all the database usage information including index usage, disk usage, top transaction etc. *This information is not available from the backup file
1. Right click your database, Go to Reports, Go to Standard Reports and you can find all the out of the box reports
Clicking one of the report will give you detailed information – in this sample, I’m using Index Usage Statistics
How to find the top 20 most expensive queries by the total execution time, run this query against the database
SELECTTOP20qs.sql_handle,qs.execution_count,qs.total_worker_time ASTotal_CPU,total_CPU_inSeconds = --Converted from microsecondsqs.total_worker_time/1000000,average_CPU_inSeconds = --Converted from microseconds(qs.total_worker_time/1000000) / qs.execution_count,qs.total_elapsed_time,total_elapsed_time_inSeconds = --Converted from microsecondsqs.total_elapsed_time/1000000,st.text,qp.query_planFROMsys.dm_exec_query_stats ASqsCROSSAPPLY sys.dm_exec_sql_text(qs.sql_handle) ASstCROSSapply sys.dm_exec_query_plan (qs.plan_handle) ASqpORDERBYqs.total_worker_time DESC
-It will give you this result set
- Clicking the query_plan will give you detailed query plan
Usage of Pivot in SQL Server
The usage of PIVOT keyword
In SQL Server we can transform a horizontal rows to be vertically represented by using Pivot Keyword
Run this query to have sample data
CREATE TABLE OrderItems(Order_No INT, ProductName VARCHAR(255), TotalQty INT)
GO
INSERT INTO OrderItems(Order_No, ProductName, TotalQty)
VALUES (112, ‘Sprite’, 5)
GO
INSERT INTO OrderItems(Order_No, ProductName, TotalQty)
VALUES (112, ‘Coke’, 7)
GO
INSERT INTO OrderItems(Order_No, ProductName, TotalQty)
VALUES (112, ‘Lemonade’, 2)
GO
INSERT INTO OrderItems(Order_No, ProductName, TotalQty)
VALUES (113, ‘Apple Juice’, 8 )
GO
INSERT INTO OrderItems(Order_No, ProductName, TotalQty)
VALUES (113, ‘Diet Coke’, 2)
GO
INSERT INTO OrderItems(Order_No, ProductName, TotalQty)
VALUES (114, ‘Coke’, 15)
GO
INSERT INTO OrderItems(Order_No, ProductName, TotalQty)
VALUES (114, ‘Lemonade’, 13)
GO
Running simple select from OrderItems will give you this table
Now we want to look it statistically – maybe for reporting purpose. Run the query below
SELECT Order_No, Sprite, Coke, Lemonade, [Apple Juice], [Diet Coke]
FROM (
SELECT Order_No, ProductName, TotalQty
FROM OrderItems) ItemList
PIVOT (SUM(TotalQty) FOR ProductName IN (Sprite, Coke, Lemonade, [Apple Juice], [Diet Coke])) AS PivotResult
ORDER BY Order_No
GO
It will give you this result set
