Exporting to Excel with bcp

I was recently looking for a quick way to export SQL Server table data (over 1,00,000 records) to a Excel file.

I found that the bcp command line tool can export SQL Server table data to a .xls file but when I export it to a Excel 2007 (.xlsx) file, I couldn't open the file. It throws a warning - "Excel cannot open the file because the file format or extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file".

The fix to this issue reported in ASP.NET Forums which points to a MS KB article, is complicated.

I wondered, if bcp can export to a .xls file why can't it smoothly export to a .xlsx file?

Curiously, I was able to able to export over 7 lakh records to a .xls file (which is supposed to have a row limit of ~65K rows in 22seconds) with bcp and the .xls file opens fine in Excel 2007 displaying all the 7 lakh records.

Vinod helped me understand how the process actually works -

Fundamentally, you need to understand that the BCP command doesn’t actually write as a real XLS file. Try to open it in Notepad and it would be a mere csv format. It is just that Excel seamlessly opens it without any problem. Now with .XLSX extension Excel 2007 expects the same to be in the XML format (which it isn’t actually) and hence the error.

BTW, the format of XLS or XLSX doesn’t determine the number of rows. It is the version of Office (2003/2007) you are running.

Read More

Travel Planning & Reservation Tips using Online Tools

A number of online tools & services have emerged for travelers in India in the last couple of years that help you plan & complete reservations, faster than ever.

What is the fastest and/or cheapest way to travel between two places in India?
Try 90di.com's online travel search engine. It gets you possible routes by bus, train, flight or train & flight. The best part is (in most cases) you get to view the fare & duration in the search results that are delivered amazingly fast. You can choose to sort on the results by Departure time, Arrival time, Duration, Transits, Fare. The search results contain links to book tickets online through a service provider's website.

You can also send your travel search queries in subject field of email to the emailID 90di.com@gmail.com and you will get your search result in your inbox within minutes. For instance, to check availability of Second Class AC reservation on Coromandal Express (Train # 2841) on 25/Aug/2009, type "AVL train 2841 HWH to MAS on 25/Aug/2009 2A" as your email subject & send.


When is the latest date by which I can get confirmed (or wait-listed) train tickets?
Use Cleartrip's Availability calendar for Indian Railways trains. It shows you availability starting from the current day to the next 3 months.

How to find train or flight info when you don't have access to the Internet?
You can dial 139 from your landline, mobile & CDMA phones for railway inquiries or try Indian Railway's SMS Enquiry Numbers: 57886, 54959, 5676747. I have tried & found Google India's SMS service to be a better proposition. To find the PNR Status, SMS "pnr {number}" to 9-77-33-00000. You can similarly find a train's schedule ("train schedule 2926"), trains between stations ("train delhi to mumbai"), train seat availability ("train avail 1018 Bangalore to Mumbai on 24-04"), train fare ("train fare 1018 Bangalore to Mumbai for SL")

To check the status of flight IT 302, send SMS with the query "it302"

Also see:
Make the most of IRCTC
E-Commerce in India

Read More

Microsoft Office Labs Community Clips

I knew that there were free screencasting tools available, but I did not know about the Microsoft Office Labs Community Clips until I read about it on Channel 10. Community Clips provides a little app that on installing integrates into Office applications. It lets you record a clip or a tutorial in Office and then share it with others online on SoapBox. As the SoapBox service will terminate soon, uploading the WMV file of the screencast is currently disabled.

I hope they bring out a new avatar of SoapBox that uses Silverlight instead of Flash to run the videos while also supporting subtitles.

Update(22-Aug-09): Community Clips works not just for Office applications but also any application that is currently open. Once Community Clips is invoked, it sits in the system tray.


It can be managed with these hot keys -

• To start recording: Windows key + Alt + R
• To stop recording: Windows key + Alt + T
• To pause recording: Windows key + Alt + P

This screencasting tool can also be put to good use for error reporting. It is not uncommon for developers to receive complaints on their application although it has been aggressively tested & found to work great on the developer machine. In addition to programmatic error logging, certain issues arising due to a browser toolbar or other environmental settings on the end-user's computer which are hard to replicate on other computers can be better captured with a video recording using the screencasting tool.

Read More

Spelling & Grammar Check in Browsers

It's good to see browsers picking the good features of each other & in some cases refining them so that it becomes a standard in subsequent versions of other browsers. Tabbed browsing, In-line Search, "Find As You Type", Undo Close Window (Ctrl+Shift+T), built-in Developer Tools are all innovative features started by one browser brand & supported by the rest.

An interesting new feature that has come up in Firefox, Safari & Chrome that I'm really finding useful is Spell-Check. Every time there is a typo or a spelling mistake in a text area within a web page that you are typing into, the red squiggles show up when you commit a spelling mistake & you can pick up the correct spelling from the context menu. Safari 4 also introduces Grammar Check, although it's not as refined as we see in MS Word. I hope Spelling & Grammar Check go on to become a standard browser feature.

Also see:
Keyboard shortcuts common to Firefox & IE 8
The Collective Decisions of Competitors

Read More

Virtual TechDays

If you work with Microsoft technologies, there have been a lot of new developments that you may have to keep pace with to stay current. Microsoft India is conducting free online sessions (nearly 50 in all) from August 19 to 21st on the following topics:

• Web Development
• Windows 7 and Client Development
• Microsoft’s Cloud (Azure) Platform
• SQL Server
• Windows 7

The Virtual TechDays sessions will be delivered by experts through live streaming. Register to take part.

Read More

Free SQL Server Tools

SQL Server Magazine has compiled The Mega Guide to Free SQL Server Tools that has over 80 tools for under these categories -

• Administration
• Backup and Recovery
• Best Practice and Compliance
• Business Intelligence
• Change Management
• Code Formatters
• Database Comparison
• Database Security
• Development
• Job Management
• Log Management
• Performance and Monitoring
• Prevent SQL Server Injection Attacks
• Web Administration

Check them out....

I'll list my favorites among them here after I try them out.

Also see:
Free SQL Server 2008 Learning Resources
Free SQL Server Videos
Database Schema Samples

Read More

HOW TO access a HTML Label tag's text programmatically

On a technical Forum that I frequent, there was a question on how to get the Label tag's text associated with a radio button (or for that matter any other control). The easiest way I found is to use jQuery -
alert($("label[for='radio1']").text()); //radio1 is the ID of a radio button

You would need to throw in a reference to the jQuery library when you run that statement. The better way is to link to the online copy here - http://code.jquery.com/jquery-latest.js

Is the label tag important anyway? I didn't think so until I read this in W3Schools -

The label element does not render as anything special for the user. However, it provides a usability improvement for mouse users, because if the user clicks on the text within the label element, it toggles the control.

The for attribute of the label tag should be equal to the id attribute of the related element to bind them together.

Did you know an ASP.NET radiobutton control renders as an INPUT tag with type "Radio" and the radio button's text is rendered with a LABEL tag? So clicking on the radiobutton's text would be as good as clicking it.

Also see: The optgroup tag

Read More

ASP.NET MVC vs. Web Forms

The July edition of MSDN mag has an interesting article by Dino Esposito that compares Web Forms And ASP.NET MVC. The author presents an excellent 100 feet view of ASP.NET MVC and I loved reading it. As I'm going to re-read it & recommend it to others, I've made a Reader's Digest version of it that squeezes the 3500+ worded article to about 500 words -

Benefits of ASP.NET Web Forms

• ..stable and mature, supported by heaps of third party controls and tools.
• To simulate stateful programming over the Web, ASP.NET Web Forms introduced features such as viewstate, postbacks, and an overall event-driven paradigm.
• ..componentization of some ASP best practices
• Productivity and rapid development of data-driven, line-of-business applications

Drawbacks of Web Forms

• ..separation of concerns (SoC) has not been a natural fit with the Web Forms paradigm.
• Automated testing of an ASP.NET Web Forms application is hard
• ..postback model .. makes it harder for search engines to rank ASP.NET pages high.
• ..excessively large viewstate is problematic

Benefits of ASP.NET MVC

• ASP.NET MVC is a completely new framework for building ASP.NET applications, designed from the ground up with SoC and testability in mind....a working environment that enables, not penalizes, test-driven development (TDD)
• No postbacks
• No viewstate
• ..stateless behavior, full control over every single bit of HTML, total script and CSS freedom.
• ..leaner run-time stack ... the run-time stack of ASP.NET MVC is simpler and the difference is due to the lack of a page lifecycle.
• unparalleled level of extensibility

Drawbacks of ASP.NET MVC

• ..full control over HTML, JavaScript, and CSS, ASP.NET MVC means that you enter the Web elements manually....a step backward in terms of usability and productivity.
• ..not something you can easily learn by experimenting....this may be the source of decreased productivity for the average Web Forms developer.

Interesting facts

• ..the size of the viewstate decreased significantly in the transition from ASP.NET 1.1 to ASP.NET 2.0 when Microsoft introduced a much more efficient serialization algorithm.
• Ruby-on-Rails has proved that MVC can also be a successful pattern for Web applications; and ASP.NET MVC confirms this.
• ASP.NET MVC today lacks some level of abstraction for creating standard pieces of HTML. HTML helpers are just an interesting attempt to speed up HTML creation
• Web Forms embraces the RAD philosophy whereas ASP.NET MVC is TDD-oriented
• Web Forms goes toward an abstraction of the Web that simulates a stateful environment, while ASP.NET MVC leverages the natural statelessness of the Web and guides you towards building applications that are loosely coupled and inherently testable, search-engine friendly, and with full control of HTML.
• ASP.NET MVC is not the only way to get SoC in ASP.NET.
• Web Forms was designed to abstract the Web machinery.
• ASP.NET MVC exposes Web architecture.
• ASP.NET MVC was designed with testability and Dependency Injection in mind.
• ASP.NET MVC takes you towards a better design of the code.
• ASP.NET MVC is young and lacks a component model.
• ASP.NET MVC is not anti-Web Forms.
• Web Forms is a model based on the "Page Controller" pattern.
• ASP.NET Web Forms doesn't prevent unit testing, but it requires much discipline and repetitive boilerplate coding to do so.
• ..in ASP.NET MVC intrinsic objects are mockable as they expose interface and base classes.
• ..control over HTML and SEO-friendly URLs, both advantages of ASP.NET MVC, can be achieved to some extent in Web Forms. ASP.NET 3.5 SP1, in particular, includes the URL Routing and History API for SEO

Also see:
ASP.NET MVC: What is it and should I use it?

Read More

Selecting multiple attachments at once in GMail

I came across the multi-select attachments in GMail feature by accident but later found that this has been documented or rather blogcumented. You have to hold down the Ctrl key and click on each file you want to attach to your message.

However, there is a twist. This feature works in all browsers (IE, Safari, Chrome, Opera) except Firefox, the browser I normally use with GMail.


In Firefox it shows the File Upload control while in other browsers, it displays the Open dialog box to choose files.



No wonder I've been missing that feature all these days.

Related posts:
HOW TO easily verify your website design in different browsers/OSs
HOW TO test a web page in different versions of IE
HOW TO make web pages "cross browser"

Read More

Mobile OS Upgrades

In a mailing list that I'm part of, there was a question on why installing a new OS on a Mobile is difficult. MVP Saurav's response was interesting and I quote him (with permission) -

Mobile Phone ecosystem is not like a PC. On a PC a user can upgrade the hardware as he wants. If he wants a higher megapixel webcam he can simply buy one & install it. However, this isn't possible on a regular mobile phone. A Mobile phone OS isn't like a PC OS. The reason Apple is able to give free upgrade to iPhone 3G is because they control the iPhone ecosystem. They make the hardware so they know the hardware won't change & it is just one hardware configuration. On the other hand, there are many Windows Mobile phone manufacturers with different configuration due to which Microsoft cannot make a "One size Fits all" OS upgrade. This is the reason, its upto the Device manufacturer to provide OS upgrade & not Microsoft.

Read More