Monthly Archives: June 2015

Query Times Out in Stimulsoft but run fine in SSMS

I have this query (which uses linked server as well) runs fine in SQL Server Management Studio (local and remote) but when run in Stimulsoft, it times out. It takes under 5 seconds to run in SSMS but times out in Stimulsoft each time. I did increase timeout period to 90 but still has this issue. I am not sure what is the problem?

exec SPR_HRM_GetSalaries @FromDate=’01-Jun-15′, @ToDate =’25-Jun-15′

Quartz.Net JobListener Example

Download Code – > Quartz_Net_Example

JobListeners in Quartz.NET are kind of event listeners but in t his case they listen to the JOB. They are executed when the job is about to be executed or when the JOB has executed. The following is a simple C# Console Application that demonstrates the concept. The main JOB simply prints a msg that says “Hello, this message is from the JOB”. The listeners also print messages to the console when the job is about to be executed and when the JOB has finished executing.

Note that you will need to download Quartz.NET library to run this example. Simply right click on project, select NUGET Packages, type Quartz and download the very first entry Quartz.NET.

Links: Job Listeners in Quartz.NET

Query – Which product customer bought right after a purchase

Lets assume we have this cvs data (scroll below). It shows customerID, ProductID and the time the product was purchased. We are required to figure out which product customer bought right after a purchase . We want the output in this form.

purchase history

We are most likely going to useĀ  SQL PIVOT but the output form cannot be directly obtained by simply PIVOTING the data. The reason being each product will have it own column in the pivot table which we do not want (will be too much to digest the data). Here is the query that will do the trick. It uses PIVOT but it also uses row_number() and assigned a psuedo prod names to each product (prod1, prod2, prod3,… and so on) which simplies the pivoting

The result output is this

pivot output

 

CVS Data to play with

You can import this into SQL Server using the import feature in SSMS. Make sure you change the product column to int type after import (by default it will be stored in varchar)

Customer,Product,Time
76815823,41624,58:44.0
32442296,770524,18:43.0
16560010,77575,21:04.0
76815823,771532,43:17.0
108152308,772542,19:01.0
108152308,772542,53:38.0
226005589,772348,11:31.0
108152308,772543,10:58.0
85526986,772614,53:14.0
16010767,770095,16:55.0
201300728,772348,24:22.0
226005589,770861,21:48.0
201300728,770406,27:23.0
226005589,77496,30:04.0
16010767,23059,25:28.0
187686099,200677,43:20.0
16010767,772552,41:44.0
16010767,770476,47:42.0
16010767,772295,50:41.0
16010767,772543,53:17.0
16010767,772184,57:20.0
16010767,772183,59:59.0
164662752,100259,02:38.0
187686099,771606,10:01.0
168439174,200322,21:11.0
187686099,200497,23:17.0
108152308,772544,43:27.0
190017804,771984,30:46.0
16010767,772182,05:34.0
190017804,77975,41:34.0
168439174,200678,41:06.0
16010767,772351,41:45.0