Tuesday, May 15, 2012

What happened to zone information in IE 9 status bar?

In earlier versions of IE, I used to be able to see the zone information such as “Local intranet | Protected Mode: Off” in the middle of status bar and it was very useful while troubleshooting Server Errors.  This information is no longer available in the status bar starting with IE 9.  It is now under Files > Properties.

It also includes other information such as Protocol, Type, Connection, Address, Size of the page and the timestamps as shown below:

image

Hope this helps.

Sonny

Wednesday, March 28, 2012

Export Excel Spreadsheets to SQL Server


There are many posts out there about moving data from Excel to SQL Server and there are many twists/solutions to this need.  Here is another one that I have successfully been able to use in my projects.
In the past, I have used the OPENROWSET feature in SQL Server to upload data in an Excel Spreadsheet to a temporary/staging table in SQL Server.  That way I can use that table in queries however I want to or need to, but that comes with a cost. You would need to enable ad-hoc query feature in SQL Server. That might work in development environment, but, often times, the DBAs won’t enable that in the production environment for various security reasons.
So if you are an ASP.NET/C# developer and can quickly create an ASP.NET page or a simple C# console app, you can use this technique to quickly load data from an Excel Spreadsheet to SQL Server. This also frees you from having to upload the Excel file to the server as you would have to do in the case of OPENROWSET.
Here is the class in C# to export the data from Excel to SQL Server:
ExcelDataImport.cs
Here is one way how you could use the above class:
ExcelDataImport xlsDataImport = 
   new ExcelDataImport("data.xls", "Sheet1", "aTable");
xlsDataImport.UploadDataToSqlServerDb(connStr);
You could either pass a single sheet/table name pair or an array of sheet names and corresponding table names.
Most of the code should be self-explanatory, however, some things to keep in mind while using/running this code:
  1. Names in the first row of Excel Sheet would be used as column names in the SQL Server table if the table need to be created. So, make sure first row contains valid column names. Though SQL Server allows non-standard identifiers as long as they are delimited, I prefer standard identifiers. More about this topic is at Database Identifiers.
  2. As shown in the code, you could use “*” to indicate you want to import all columns in a given Sheet or specify specific list of column names just like you would do in SQL Server.
  3. For table names, you could use fully qualified names as you would in TSQL, if no schema is specified, SQL Server would simply look in the default schema.
  4. Target table must exist in the database and it will simply append rows to the target table in the database.
  5. You could also programmatically create Tables in SQL Server from your DataTable definition as done in the sample code. Idea for this was obtained from the blog: http://clicpharmacodebits.wordpress.com/2011/01/28/create-sqlserver-table-using-smo-sql-server-management-objects-in-net/
  6. Note that we are using Microsoft.ACE.OLEDB.12.0 driver, which supports both .xls and .xlsx formats unlike the old Microsoft.Jet.OLEDB.4.0. driver, which supports only the .xls format.
Other links related to this:
How to import data from Excel to SQL Server
OPENROWSET and not Text files but Excel files

Thanks for reading.
Sonny

Saturday, February 18, 2012

Installation of UniversalIndentGUI failed

If you ever want to pretty-print or beautify your source code files in Notepad++, you might have heard about UniversalIndentGUI (UIGUI) plugin for Notepad++ (Npp).  However, when you tried to install this plugin for Npp via the Plugin Manager, you might have gotten the following error:

Installation of UniversalIndentGUI failed

There is an active ticket at UniversalIndentGUI issues list, which has been opened for 16 moths with a single comment made about 7 weeks ago. I could not find any help related to how you could resolve this error.  I also could not find much reference to this plugin either from the UniversalIndentGUI page nor from Notepad++ Plugins List.  So I have downloaded the latest release of UIGUI to see if I could manually install it as a plugin for Npp, but disappointed to find out that it is not a plugin anymore but a stand-alone Windows application.

image 

It works pretty good as a Windows application and has support for lot of languages as you can see from the above screenshot taken from the UIGUI’s home page.  However, I could not search nor could I edit the file as easily as I could in Npp, so it would really be nice if I could get it to work as a plugin.

The last known version of UIGUI plugin for 1.0.2. which is what Npp uses to install when you try to install this plugin via Plugin Manager.  This info is in the file PluginManagerPlugins.xml, which is typically located in your profile directory on Windows 7.  That is in the following directory:

%USERPROFILE%\AppData\Roaming\Notepad++\plugins\config

If you can’t find it there, then bring up the Plugin Manager dialog and click on the Settings button at the bottom.  In the Plugin Manager Settings dialog, you should be able to see the Config path setting being used by your install of Npp.

Open the PluginManagerPlugins.xml and locate the UIGUI section and you should see something similar to the following:

image

Note: I had to use the XML Tools for Notepad++ to format this file. XML Tools for Notepad++ is another Npp plugin that gives you lot of XML tools including pretty-printing XML files.  I was able to install this using the Plugin Manager.

If you try to download the plugin zip file referenced in this config file, you get redirected to http://sourceforge.net/projects/universalindent/files/, which is the home location of UIGUI files.  From here, you would want to go to uigui folder and then to UniversalIndentGUI_1.0.2 folder, and from there, you want to download the UniversalIndentGUI_1.0.2_NotepadPPplugin.zip file.  Or you might want to just use the following URL to download the plugin zip file:

http://sourceforge.net/projects/universalindent/files/uigui/UniversalIndentGUI_1.0.2/UniversalIndentGUI_1.0.2_NotepadPPplugin.zip/download

After downloading the zip and extracting it to a some temp directory on your system, as per the XML snippet, move/copy the files as shown above. $NPPDIR$ is typically “C:\Program Files (x86)\Notepad++” and $PLUGINDIR$" is “C:\Program Files (x86)\Notepad++\plugins”.  Restart Npp and you should see UIGUI listed under the Plugins menu.  You should now be able to indent/format/pretty-print a wide-variety of source code files right inside Npp.

Happy Coding!

Sonny

Windows Command Console inside Notepad++

What happened to my nifty Windows Command icon inside Notepad++ (Npp), is the question I have asked myself a few times before and I do not really remember how I got it back the last time I looked for it.

If you are wondering about what the heck is Windows Command icon in Npp, then you must not have used it already.  It is the icon you get when you installed Npp plugin called NppExec.

NppExec is a very powerful/useful plugin for Npp. It gives you Console Window (cmd.exe) functionality right inside from Notepad++.  Note that it is not an emulator of Console Window (CW) nor a command interpreter according to NppExec manual.

That means all the DOS commands such as dir, xcopy, move that you would run in the CW can directly be run right from Npp.  For example, you might have used dir or tree command and redirected their output to a text file to include it in your documentation.  Well, you no longer need to do that, you could run such commands right from Npp and you could copy the output right from the Console inside Npp and paste it into any text file that you have opened in Npp.

If I remember correctly, it used to be included in the Npp default install, but I no longer see it in my latest version (v5.9.8) of Npp. After looking around, I finally learned that it is a plugin and hence need to be installed if it did not already get installed with the default Npp install.

To install it, you could use the Plugin Manager which is another plugin that gets included with the default install of Npp.  Under Plugins menu, go to Plugin Manager and click on “Show Plugin Manager” command to bring up the Plugin Manager dialog.

image

As shown above, navigate to the NppExec line and select and click on the Install button. Follow the prompts and it will automatically download and install the plugin for you.  You might be asked to restart the Npp to complete the installation.

Once the install is complete you would see NppExec listed under the Plugins menu as shown below:

image

Either select the “Show Console Dialog” from the above menu or click on the little console window icon image in the menu bar to get started. Go ahead and explore it.

It also has advanced scripting functionality, which I have not explored yet, but looks promising.

Hope you find this useful.

Sonny