Import Excel into SQL 2008 64 bit

Last couple days, I was working on a solution to import some excel file into SQL and then generate report from the server.

There're a lot of ways to import an excel file, I tried a few approaches, unfortunately due to lack of permission to muck around with server settings in the company, I was unable to proceed with most of these approaches.

Approach 1 : (ISSUE: 32 bit office installed, 64bit SQL installed)

Step 1. Install 64-bit Microsoft.ACE.OLEDB.12.0 driver  (Failed on this one, Don't have server admin rights to install 64 bit version drivers.)

Microsoft Access Database Engine 2010 Redistributable

Step 2. Configure ACE OLE DB properties

EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
GO
EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
GO

Step 3. Configure linked server using Microsoft.ACE.OLEDB.12.0 provider

SELECT * INTO #ExcelTemp FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=V:\database\Export\Service KPI Report 2013.xls','select * from [2013$]')

I did run step 2&3 on my local machine (XP sp3, 32bit SQL 2008 R2), work like a charm.
Possible issues:
you may come across:

Msg 15281, Level 16, State 1, Line 1

SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.

This will fix it :

sp_configure 'show advanced options', 1;

RECONFIGURE;

GO

sp_configure 'Ad Hoc Distributed Queries', 1;

RECONFIGURE;

GO

Also this error is common:

Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. Access denied.

Msg 7350, Level 16, State 2, Line 1

Cannot get the column information from OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

Fixes:

USE [master]

GO

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1

GO

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1

GO

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'NestedQueries', 1

GO

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'NonTransactedUpdates', 1

GO

-------------------------------SEPERATOR ------------------------------------------------------------

Approach 2: LINKED Server
Same issue with the first approach, 32 bit office and 64 bit SQL. on all 32 bit servers, everything goes smoothly, where as 64 bit SQL will have errors and not be able to proceed.

EXEC sp_addlinkedserver
    @server = 'ExcelServer',
    @srvproduct = 'Excel', 
    @provider = 'Microsoft.ACE.OLEDB.12.0',
    @datasrc = 'C:\Test\test.xlsx',
    @provstr = 'Excel 12.0;IMEX=1;HDR=YES;'

-------------------------------SEPERATOR ------------------------------------------------------------

Approach 3: SSIS

Easy enough, end up doing this. 

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);