Terminating Database Restoring process

You may occasionally want to terminate an ongoing restoration process of your database backup.
In MS SQL 2008 R2, I don't find there's an easy way of doing so, currently I don't believe there's a cancel selection or w/e for this kind of purpose. So in this case, we'll have to do it manually and brutally.


To start with:
Stop your SQL Server Agent service

Then stop SQL Server Service.

Delete/rename the .Bak file you were using to restore your database.
Delete the log files or db files has been created from the restore

Start both services.

Relog into SSMS and check, you probably will still see 'DB(Restoring.....)'

To get rid of this, do a manual restore from Wizard, using any .bak file you want name it to 'DB'.

Once done, this should go away.

T-SQL MERGE (Ver2)

A reminder,

Merge ...
WHEN Matched And (Search condition) THEN
Update Set ****

Update can only be use once in Merge statement, so if there're multiple conditions, you will need to specify all of them in (Search Condition).

WHEN Not Matched cannot use Update, Only Delete/Insert can be used.

Example:
To update Product RRP with source table, conditions: update using source if source RRP less than current RRP, or if current RRP is 0 but source isn't.


Merge dbo.Products ip
USING (Select vendor_code, min(RRP) as Rp FROM zab_Supplierpricelist Where RRP > 0 and vendor_code > ''
Group by  vendor_code) as ms
ON ip.zextpartno = ms.vendor_code AND ip.status in ('A','R') WHEN Matched and ((ip.ZRRP > ms.Rp) OR (ip.ZRRP =0 AND ms.RP > 0)) THEN
UPDATE SET ip.ZRRP = ms.RP;

Background worker and progress bars tutorial

I have recently knocked up a simple tutorial of the use of background worker and progress bars when there's a need of use such. General purpose is to make a more user friendly interface, as sometimes the program will hang if the processing time takes more than a few sec. ( some users will then think the program is dead and may force shut down the program.)
ppt link:
https://sites.google.com/site/maimailearns2vb/first/Back%20Ground%20Worker%20n%20Progress%20Bars.pptx?attredirects=0&d=1

Windows Server 2008 R2 Printer Log Enabling

'Start' -- 'Printers & faxes' -- right click select 'Server Properties' -- 'Advanced' -- Check 'Log spooler information events' --- Restart Machine. 

To Check Log

Server Manager --> Event Viewer --> Applications and Service Logs --> Microsoft --> Windows --> Print Service

Race Condition Errors when deleting copying network file

I have encountered a strange issue since last Friday, which never happened before on our servers. I have a program sending out reports to customers/suppliers on daily basis, one of the functions are sending out the file with same name, say "Your List". 

Everything goes perfectly smooth until last Friday, i saw some errors: Error creating csv/xls file. I then went thru the program to see if i could replicate the issue, unfortunately it runs all good when I tried the same functions on my own. So I added a log to see what's the actual error was, turns out to be : Cannot copy the file to "Your List", it's been accessed by another user. Well since it runs overnight, and no one is using the file at all times, I can only assume this is caused by trying to copying the file before the Deleting process has finished.

Well this does happen and a lot on network file copying/deleting.

I tried to give a delay between deleting and copying, well it does help a little, but it's not always a good idea as error will still come at some stage.

In my case, I changed the file name to add either customer name or supplier name, which make those files all different, and fixed the problem. Although it'll be better to know an alternative way of solving the issue without changing the name.

Merge IN SQL 2008

I recently came across a scenario where i need to run a loop to update/insert multiple records into a existing table. There's a very easy and clean way of doing so in SQL 2008, with the 'Merge' method, it is achievable in just a couple lines of codes.

What i did was:

Step1 : Create a temp table

CREATE TABLE #Stock  -- source
(
  ProductCode Varchar(50) NOT NULL PRIMARY KEY,
  Quantity INT NOT NULL,
    CONSTRAINT Quantity_Default_2 DEFAULT 0
);
--Insert Record into the temp table for later update merge
Insert INTO #Stock(ProductCode, Quantity)
Select ProductCode, Quantity FROM dbo.Products WHERE Category = 'blahblah'
;

Step2: Merge temp table with target table
--Merge with target table

Merge [integration_products] ip
USING #Stock ms
ON ip.ProductCode = ms.ProductCode
WHEN Matched THEN   -- Matching Case DO update
UPDATE
SET ip.QtyOnhand = ms.Quantity + ip.QtyOnhand
WHEN Not Matched By Target THEN  --Insert
    INSERT ([ProductCode],[Status],[Category],[MinQuantity],[MaxQuantity],[CustOrders],[CustBackOrders],[SuppOrders],[QtyOnHand])
      VALUES (ms.ProductCode, ms.[Status], ms.[Category], 0,0,0,0,0,ms.Quantity);

SSIS Excel column datatype problem Ver2.0:

I have come across another similar situation today when using one of the excel file received couple days back. turns out the 'quantity' column has numbers and strings like (5,10, "100+"). Problem with this is during the validation of the package, "100+" are discarded by the package and returned to me as "Null" values.
In this case unfortunately only thing I could do is parse the whole sheet in as one column and then write script to differentiate columns.