Schedule DAC service to run without making it a Windows Service

The DAC service is dependent on the database connections which can sometimes be unpredictable because of network fluctuations. DAC services can also fail to start if the server has to be rebooted or the database crashes due to load on the database.

Since DAC is not a 'Windows' service, we noticed it frequently crashed, sending emails like :
'DAC Server shutting down' which were not very helpful.

After which scheduled loads would not run as the 'server' was down.
To mitigate this, we moved DAC to 'scheduled tasks '. This way we set it up to restart everyday at 4:00 AM before the load was scheduled to run. You can add other schedules as well to ensure that it restarts on every reboot or twice a day.

Since then we have never had a problem of missed loads or the server being down at odd hours of the day.

Thanks for reading.
Anisha

Select Serial Number in a SQL query

I was recently asked this question about listing records from a database and even though this is unrelated with most of this blog, I thought it has earned its place as part of this blog.

The question was:

How do I select records from a database table and display the results as a list in the UI (which uses a SQL query to access the DB)

Consider a simple 'Grocery Shopping' database from which we want to display information in the UI/web page based on selected Shopper as follows:

Shopper1 bought

1. Eggs -$3

2. Bread - $2.5

3. Cream -$6

OR

Shopper2 bought

1. Cheese -$5

2. Eggs -$3

3. Ham -$9

4. Bread -$2.5

We have two tables:

To create the above result, using either Oracle or SQL server, you can use the following query:

SELECT
l.Shopper_name,
row_number() over (order by i.item#) as srno,
i.item_name,
i.price
FROM
Shopping_list as l, Inventory as i
WHERE l.shopper_name :=selected_shopper --Prompted value through SQL
GROUP BY l.shopper_name,i.item_name,i.price
ORDER BY l.shopper_name




After getting the results as a list, you can use your code to format it appropriately in the UI.



Another use for this type of query could be to see the number of times a given item appeared in the 'Shopping_List' table Eg: List the Shoppers who bought Eggs with Serial Numbers attached.



Anisha