SQL Server 2008R2 Linked Server to Oracle 11.2.0.2 Instance

My notes on doing this particular setup using SQL Server 2008R2 and Oracle 11.2.0.2 (64bit versions) on Windows Server 2008R2 64bit Enterprise edition.

Install Oracle 11.2.02 64 bit client

Install the oracle client on the SQL Server machine where you want to add the linked server. Install the following components only using the custom installation option.

• Oracle Database Utilities
• SQL*Plus
• Oracle Net
• Oracle Provider for OLE DB
• Oracle Data Provider for .NET (I dont think this is needed, but I always install it out of habit!)

Reboot the SQL Server box

Reboot the server so that SQL Server 2008R2 can pick up the installed Oracle provider. When the server comes back up, you should see the new oracle provider appear in the linked server providers section in SQL Server Management Studio.

Configure Oracle Link Provider Properties

Right click on the OraOLEDB.Oracle provider and select properties. Check the following options and save changes.

• Dynamic parameter
• Allow inprocess

Configure TNS Names

At this point, you need to configure TNS entries for connecting to your Oracle instance. The easiest thing to do is take an existing tnsnames.ora file from a working server and copy it to the "network\admin" directory in your root oracle install path. ( in my case, my TNS names file is in the path "c:\app\product\11.2.0\client_1\network\admin\tnsnames.org")

Create Oracle Linked Server

Go back into SQL Server Management Studio and browse to the Linked Servers folder. Right click that folder and select "New Linked Server...".

In the general tab...

• Type in the name of the linked server (whatever you want) into the "Linked server:" field
• Select "Oracle Provider for OLE DB" as the Provider
• Put in any value you want for the Product name - something like "Oracle" will do.
• Set the Data source: to the TNS name of the connection you want to use from your TNS file.
• Leave the provider string empty

In the security tab...

• Select the last radio button "Be made using this security context"
• Enter the oracle account username/password in the fields under the checked radio button

...and click "OK" to finish up.

SQL Server 2008 - Dumping VARBINARY data to a file

I couldn't find a way to convert varbinary column data to a file using SQL Server 2008 Management Studio, so I did some googling and found info on using the bcp.exe command to do it (I believe this comes with the Management Studio install). In my case, I was extracting a PDF file stored as varbinary:

c:\temp>bcp "select MYVARBINARYCOL from MYTABLE where id = 1234" queryout "c:\filename.pdf" -S MYSQLSERVER\MYINSTANCE -T

Enter the file storage type of field filedata [varbinary(max)]:
Enter prefix-length of field filedata [8]: 0
Enter length of field filedata [0]:
Enter field terminator [none]:

Do you want to save this format information in a file? [Y/n] n

Starting copy...

1 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 15 Average : (66.67 rows per sec.)

I used the -T option to use windows authentication to connect to the DB. If you use password auth, you'll need to use the -U and -P switches to specify a username and password.

Understanding REST-ful Web Services

Links to more info on REST-ful web service design. Most of my focus will be using .NET WCF data services for building web services.

• RESTful Web services: The basics (IBM)
• Hypertext Transfer Protocol (Wikipedia)
• A Guide to Designing and Building RESTful Web Services with WCF 3.5 (Microsoft)
• WCF Data Services Overview (Microsoft)
• Using WCF Data Services with Entity Framework 4.1 and Code First (Microsoft)

Another Painful Day At Work - Database Column Widths

Another painful day at work trying to justify to my project managers why we should be using generic long varchar2 width/size specifications in my DB schema, for columns storing things like user entered addresses and phone numbers. Things that our web application will constrain and validate (like phone number format) - things that our database does not need to care about. For these "generic" columns, we just want to accept whatever data the web app passes to us and persist it. Nothing more.

There is an IBM article that explains my point much more clearly than I can manage in a meeting with my co-workers: http://www.ibm.com/developerworks/web/library/wa-dbdsgn1/index.html

Strings and numbers

In general, numerical types pose few problems -- just select one that is large enough to support the necessary range of values.

The attempt to find the optimal width of a string column is usually not worth the effort. You can avoid a lot of confusion later on by making all text messages of type varchar(n) and limiting yourself to a few standard string lengths and introducing aliases for them, such as: 32 bytes ("Label"), 256 bytes ("Note"), and 4k ("Text").

Even if other business requirements restrict the maximum length of certain fields to specific values, the DB schema is arguably not the best place to enforce these rules. By the time the data reaches the DB, it is too late to do anything about it (except reject it). Individual restrictions, stemming from business rules and requirements, should be enforced by the business logic layer, which handles user interaction and input validation. On the other hand, maintenance of the DB schema is considerably simplified if it is restricted to a handful of different string attributes.

Limit the use of fixed-width strings to codes of all sorts (as opposed to variable-length strings for real text). Keep in mind however, that many seemingly fixed-length codes do actually become wider over time. The prudent DB designer tries to avoid anything similar to the Y2K problem for new development work.

iOS5 iCloud - Not as open as I had hoped!

I have been using dropbox for a year or two now and when I heard that iOS5 would have iCloud features baked into it, I got all excited! Finally I can get rid of all my 3rd party cloud services and manage everything in iCloud! It made sense at first, knowing that all my music would be in the cloud anyway (for me thats about 30gig of space right there!) but now that I've played with iOS5 for a bit with cloud features enabled...not as awesome as I had hoped.  Dropbox is still the champ in the cloud storage department, if you ask me.

Lets just jump right to the kicker. All the cloud services sync up to any ios device you own, and appleTV. thats fine, but still very limited. How can I access icloud without ios? Say, with a pc or a linux laptop? The only option I've found is the icloud web interface...which is rather dissapointing.

And my first visit to icloud.com turned into an "um...what?" moment.

• Dude, where are my pictures?  Photostream isnt even viewable from here? Wacky! I'm guessing you might be able to sync up photostream with itunes on pc, but havent even looked yet - kind of pointless thing for me anyway, I'd rather have it available on the web.
• Contacts and calendar? Ok, sure, thats neat but not something I use since all that is on my iphone already.
• Mail and FindMyPhone are old services rolled into the new icloud domain. The mail is just me.com mail (if you setup a me account) and the find my phone stuff isnt cloud storage related.
• iWorks is the only service they expose that is an actual cloud based service, but its locked down to folks who paid for iworks apps on ios.  Since I dont have iworks, I cant truly test out what this service even does. I assume it renders a web app version of your files, but my guess is they wont allow file downloads (that seems to be the key point for all their icloud stuff - no direct file access, you must use ios x_x)

..but the good stuff about icloud so far for me

• Cloud backups. No more syncing my phone with itunes! Hooray! I really hope we can do iphone updates over the air in the future too so I could remove itunes from my pc if I really wanted to.
• Sync music/video from itunes over the air.  Again, nice to be able to keep my device up to date without plugging in to sync with itunes.  I could start using Linux now that I dont need itunes to keep my iphone in sync! yay!

And thats really. But those two things, cloud backup and music/video sync over the cloud are really huge!  That alone makes the service worth wile in ios5, but I really wish they would take the time to open up the icloud service to expose more data to non ios devices, even if only via web apps. Something that I can use my pc, mac or linux box to access without itunes.

Something Neat - P2PU Beginning Game Development with HTML 5

I just randomly found this site the other day and decided to give this tutorial a try since I really need a good Javascrip/HTML5 refresher ... and wow, the videos are great! (and the instructor is just ... lol)
It covers Javascript basics, building/implementing javascript objects, HTML5 canvas, jQuery and a bunch of maths for simple game physics x_x

So here you go, a link to the course for building games using pure HTML and Javascript.

http://p2pu.org/en/groups/beginning-game-development-with-html-5/

SharePoint 2010 - Removing Orphaned WebParts

While troubleshooting a completely different issue in one of our SharePoint 2010 test farms, I noticed an error reported in the "Health Report" in the central admin. The error "Missing server side dependencies" came up with an explanation saying "WebPart class [GUID] is referenced [x] times in the database [ContentDB], but is not installed on the current farm."

In my case this was a web part we used back before the site had been upgraded to sharepoint 2010. Amazingly, the web part was still working even though SharePoint 2010 was flagging it as an orphan. In this case that's fine, I wanted to remove the web part from all pages anyway.

...so how in gods name do I find what pages the WebPart is on?

Thanks to Phil's blog post "Diagnose MissingWebPart and MissingAssembly issues from the SharePoint Health Analyzer using PowerShell" I was able to figure this out. Here is my rehash of Phil's info, connecting to SQL server directly rather than using powershell.

Query Against the WebPart GUID
Snag the WebPart GUID from the error message, then use SQL server management studio to connect to your SharePoint 2010 content database.  Run the following query, replacing the GUID with your WebPart GUID.

SELECT Id, SiteId, DirName, LeafName, WebId, ListId, tp_ZoneID, tp_DisplayName

from AllDocs

      inner join AllWebParts on AllDocs.Id = AllWebParts.tp_PageUrlID

where AllWebParts.tp_WebPartTypeID = '8a860dca-4061-d270-a67b-f6bde7fc3e0a';

The DirName gives you the path to the page you want ("/MySubSite/Pages") and the LeafName gives you the name of the page ("default.aspx"). 

Map The SiteId GUID To URL
If you have more than one site collection in your farm, the SiteID tells you what collection you are dealing with. You'll want to map the SiteID to a website URL, so bust out the sharepoint powershell prompt on your central admin server and run the following command:

PS c:\> Get-SPSite

it should output a list that looks something like this:

Url                                                         ID

---                                                         --
http://MySite1                                         707f838a-56ab-475b-9a52-fdf05a8f3e7a
http://MySite2                                         11c04ca8-ad58-4147-8344-8132e5788090

The ID in the above table maps back to the SiteId from the SQL query - and there you have your URL to SiteId mapping! Hooray!

Now you can put it all together to get the full URL of the pages you need to fix.

Removing Bad WebParts
Sadly, this can be tricky part. When you start deleting the bad WebPart, keep these things in mind:

• DELETE the web part from the page. Do not use the "Remove" option, which only hides the web part instance rather than delete it.
• You can quickly view all WebParts assigned to a page and delete them by appending "contents=1" to the url. Ex: "http://mysite/Pages/default.aspx?contents=1" (Another great tip from Phil, thanks!)

• If you have the publishing feature enabled, you could have instances of the web part linked to old versions of your page.  Be sure to delete the version history of the page if necessary ... and if you do that, you will need to also empty the SharePoint recycle bin ... and -then- empty the "Second Stage" SharePoint recycle bin if you have it enabled in the central admin, which only the Farm Admin can do from the site collection root settings page.  To get to the Second Stage recycle bin, you can login to the front end site as a farm admin account and hit the url "/_layouts/AdminRecycleBin.aspx". Then pick the option "Deleted from end user Recycle Bin".

Verify All Instances Are Gone
Once you've deleted the web part from all pages, do a sanity check - run the SQL query again to confirm no records are returned.  You can also run the "Health Check" again by hand to confirm SharePoint agrees that the web part is gone.  Fire up the SharePoint PowerShell command prompt and run the command "Test-SPContentDatabase" shown below, but of course swap out the content database and web application names for your own.

PS c:\> Test-SPContentDatabase -name MYSP2010-Content -WebApplication http://MyApp