Yearly Archives: 2011

Excel X64 driver and SQL Server 2008 R2 on cluster node [Fix]

Posted on by
3

As we continued with the execution of our migration plans from SQL Server 2005 to 2008 R2 X64, we found challenges than needed to be resolved pretty quickly. The latest issue we encountered was the Microsoft.ACE.OLEDB.12.0 provider in SQL Server 2008 R2 X64.

With that same provider, we were able to load Excel files directly into SQL Server 2005 X64 (SP3 and SP4), but had no luck in 2008 R2.

The prerequisites are to install the Microsoft Access Database Engine 2010 Redistributable, and to add the provider, with a couple of configuration changes:

Configuring:

1
2
3
4
5
6
7
8
9
10
11
12
13
exec sp_configure 'show advanced options', 1;
reconfigure;
GO;
 
exec sp_configure 'Ad Hoc Distributed Queries', 1;
reconfigure;
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;

Testing:

1
2
3
4
SELECT *
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;Database=M:\MSSQL10_50.MWTOOLS\MSSQL\DATA\dreambox.xlsx;HDR=Yes;IMEX=1',
'SELECT * FROM [Subsets & Ads$] WHERE Headline IS NOT NULL')

OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)"

OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)"

We had no luck with the provider until we pinpointed a workaround on a blog post using the MSDASQL provider instead

Workaround:

1
2
SELECT * FROM OPENROWSET('MSDASQL',
'DRIVER=Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb);FIL=excel 12.0;DriverId=1046;DefaultDir=M:\MSSQL10_50.MWTOOLS\MSSQL\DATA\dreambox.xlsx', 'SELECT * FROM [Subsets & Ads$]')

After the change our application worked flawlessly.

Netflix on iPad/iPhone does not work when connected to a router running DD-WRT [Fix]

Posted on by
2

I was pretty frustrated when I realized that for firing up my new iPad I needed to install iTunes to my desktop. On top of that, I needed to add a credit card number to download a free application from the App Store. I worked it around by generating a one-time number from citicards.

I decided to install Netflix and did not work, even tried resintalling and changing SSIDs. The only way to make it work was by using a backup SSID from the U-Verse router.

After researching I found out that the DNSMasq package from DD-WRT thinks that the connection from Netflix on the iPad looks like a DNS Rebinding attack.

There are a couple of workarounds:

  1. Use a different DNS server in the iPad/iPhone wireless configuration, like the one provided by OpenDNS: 208.67.222.222
  2. Add a startup rule to DD-WRT to stop DNS rebinding:
1
2
3
killall dnsmasq
sed -i -e 's/^stop-dns-rebinding/#&/' /tmp/dnsmasq.conf
dnsmasq --conf-file=/tmp/dnsmasq.conf

I pursued the second workaround as it allowed me to keep the wireless connection “stock”. Netflix is now working properly on my iPad.

Stop DNS rebinding

Stop DNS rebinding

Windowed Functions empowering analytics [#TSQL2sday]

Posted on by
3
T-SQL Tuesday #16

T-SQL Tuesday #16

This blog entry is participating in the T-SQL Tuesday #16 for the month of February, hosted by Jes Schultz Borland (Blog|Twitter).

Back in 2001 I was involved in a project migrating an e-commerce site to a new platform. We were mapping and moving data from an Oracle 8.1.5 to an Oracle 8.1.7 instance. The framework was the beloved Dynamo running on Sun Solaris. The Dynamo engineers decided to use sequencers on each entity (e.g. product, category, etc). I was trying to figure out the best reusable PL/SQL block to move the data creating the correct sequence until I was pointed out that Oracle 8.1.6 supported Analytical Functions. After reading and practicing I discovered the power of those functions.

For the purpose of this blog I will refer the Analytical Functions as Windowed Functions instead. They are very useful as it allows the user to crunch measures of subsets of data maintaining the “raw” detail level data. For example, it allows us to get the average price of a product category while also displaying the unit price of each product. It can also help us rank a product by its price and “window” it by category.

Examples

Basic Aggregation:

1
2
3
4
5
-- Qty of products and average price
SELECT
    COUNT(productKey) AS [Quantity]
   ,AVG(StandardCost) AS [AverageCost]
FROM [dbo].[DimProduct]

Distribution of Data:

1
2
3
4
5
6
-- Tier by Product Cost (4 tiers)
SELECT
    NTILE(4) OVER(ORDER BY [StandardCost]) AS [Tier]
   ,productKey
   ,StandardCost
FROM [dbo].[DimProduct]

Average per Category with raw data:

1
2
3
4
5
6
7
-- Take the Average for each Product Subcategory and attach to the detail level data
SELECT
    AVG(StandardCost) OVER(PARTITION BY [ProductSubcategoryKey]) AS [AverageCostSubPerCat]
   ,productKey
   ,[ProductSubcategoryKey]
   ,StandardCost
FROM [dbo].[DimProduct]

Ranking:

1
2
3
4
5
6
7
8
-- Rank Products based on the minimum and maximum cost per Product Subcategory
SELECT [ProductSubcategoryKey]
   ,RANK() OVER(PARTITION BY [ProductSubcategoryKey] ORDER BY StandardCost, [ProductKey]) AS sequence_min
   ,RANK() OVER(PARTITION BY [ProductSubcategoryKey] ORDER BY StandardCost DESC, [ProductKey] DESC) AS sequence_max
   ,[ProductKey]
   ,StandardCost
FROM [dbo].[DimProduct]
WHERE StandardCost IS NOT NULL

Averages excluding certain ranks:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- Get the Average and Qty. of the products per category excluding the minimum and maximum cost per Product Subcategory (above)
WITH CTE_sequence AS (
  SELECT [ProductSubcategoryKey]
     ,RANK() OVER(PARTITION BY [ProductSubcategoryKey] ORDER BY StandardCost, [ProductKey]) AS sequence_min
     ,RANK() OVER(PARTITION BY [ProductSubcategoryKey] ORDER BY StandardCost DESC, [ProductKey] DESC) AS sequence_max
     ,[ProductKey]
     ,StandardCost
  FROM [dbo].[DimProduct]
  WHERE StandardCost IS NOT NULL
)
SELECT [ProductSubcategoryKey]
  ,COUNT([ProductKey]) AS Quantity
  ,AVG(StandardCost) AS Average_Cost
FROM CTE_sequence
WHERE sequence_min > 0
  AND sequence_max > 0
GROUP BY [ProductSubcategoryKey]

There are more functions that can be used and the user even has the option to dice the data further by using HAVING and slicing with GROUP BY CUBE | ROLLUP which plots the data in “pivotable” format.

Cisco E2000 Router is solid when flashed with DD-WRT [Networking]

Posted on by
3

Over a year ago I owned a Linksys WRT-54G V4 router configured as the main and a Netgear WNR8348 configured as a repeater bridge. Both running DD-WRT on wireless G. The solution was rock solid.

After several months, I decided to go the N route and got a Trendnet TEW-637AP and sold the Linksys. A very bad move. The Trendnet would not work as a repeater bridge (or client bridge), so I installed it as main router instead. The Netgear WNR8348, then configured as repeater bridge, started dropping packets and lost connectivity consistently.

Later I replaced the Netgear with an Asus RT-N12 and gave it away. I ended up in worse shape as the Asus lost connectivity every 15 minutes when copying large files; Netflix streaming was just impossible. I tried all kinds of configs (N, G, mixed, 20 Mhz, 40 Mhz, different power settings, you name them). It just did not work.

I was frustrated to the point of thinking about running physical cable but did not execute as it was going to be a very challenging task.

Last attempt to migrate to Wireless N:

I decided to give it a try to a pair of Cisco E2000 routers to replace my repeater bridges which got flashed with DD-WRT as soon as they arrived. I was really surprised with the positive results they gave me after transferring ISO files over wireless, to the point that they were maxing out my PC LAN port consistently. The only change I made was to lower the TX rate to 50 mW (default is 70 mW). The main Asus streaming wireless router is configured to serve N Only connection at channel 7 (2.4 Ghz) and Turbo Channel Width (40 Mhz).

Maxing out the LAN port

Maxing out the LAN port

From now on, I will stick with Cisco or Linksys for my networking needs.

This is how my home network is configured currently:

Home Network

Home Network

Migration to 2008 R2. Not as easy as Copy & Paste [Performance]

Posted on by
Reply

We have gone through several migrations already, mixing and matching sources (SQL 2000, 2005, X86, X64, Enterprise to Standard, etc). We now got most of our databases running under SQL Server 2008 R2.

In every instance we have found performance issues related to HyperThreading, bad plans, missing registry key or outdated iSCSI drivers. We have reached a point where we are now ready to analyze and solve the challenges soon after the migration has been performed.

We recently migrated a core DB, from 2005 to 2008 R2, from X86 to X64 and from Enterprise Edition to Standard Edition. System was effective but the application was sporadically timing out. After digging through the logs, collecting info based on server side traces, and using Adam Machanic’s sp_whoisactive tool, we found the culprit. Eight services pulling data using 1 stored procedure was bringing the box to it’s knees. With updated stats, better hardware and more memory available, we never expected this to happen. But it did.

We added an index and narrowed the scope of a where clause inside the stored procedure. The procedure went from ~30 mins and ~100 million reads to 10 seconds and ~15,000 reads.

Small changes, huge differences. The key is to spot the issue, understand and work it around. Then schedule a production push and QA the process.

Reads out of hand

Things look very good now.