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.
SELECT* FROMOPENROWSET('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)"
We had no luck with the provider until we pinpointed a workaround on a blog post using the MSDASQL provider instead
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:
Use a different DNS server in the iPad/iPhone wireless configuration, like the one provided by OpenDNS: 208.67.222.222
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.
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]
-- Take the Average for each Product Subcategory and attach to the detail level data SELECT AVG(StandardCost)OVER(PARTITIONBY[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(PARTITIONBY[ProductSubcategoryKey]ORDERBY StandardCost,[ProductKey])AS sequence_min ,RANK()OVER(PARTITIONBY[ProductSubcategoryKey]ORDERBY StandardCost DESC,[ProductKey]DESC)AS sequence_max ,[ProductKey] ,StandardCost FROM[dbo].[DimProduct] WHERE StandardCost ISNOTNULL
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(PARTITIONBY[ProductSubcategoryKey]ORDERBY StandardCost,[ProductKey])AS sequence_min ,RANK()OVER(PARTITIONBY[ProductSubcategoryKey]ORDERBY StandardCost DESC,[ProductKey]DESC)AS sequence_max ,[ProductKey] ,StandardCost FROM[dbo].[DimProduct] WHERE StandardCost ISNOTNULL ) SELECT[ProductSubcategoryKey] ,COUNT([ProductKey])AS Quantity ,AVG(StandardCost)AS Average_Cost FROM CTE_sequence WHERE sequence_min >0 AND sequence_max >0 GROUPBY[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.