How to upload Excel (XLSX or XLS), CSV, Tab Delimited Text files to SQL Server

Part 1: Upload the file to any temporary folder on the server. (Recommended)

Sometimes client side files can create “access denied” or “read only” exceptions. It is recommended that we should upload the file to server before using it as data source.

For CSV or Tab Delimited text files we should upload a “Schema.ini” file along with the source file. The detailed steps are mentioned in my next post Use schema file to import data into SQL Server from CSV/Text files.

——————————————————————————————————————————

Part 2: Create connection string for source

for excel 2007 or 2010 files

string connectionstring = “Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\”” +
“[Full file path including extension]” + “\”;Extended Properties=’Excel 12.0;HDR=Yes’;”;
// e.g. “c:\foldername\filename.xlsx” or Server.MapPath(“~\foldername\filename.xlsx”)

for excel 97 – 2003 files

string connectionstring = “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\”” +
“[Full file path including extension]” + “\”;Extended Properties=’Excel 8.0;HDR=Yes’;”;
// e.g. “c:\foldername\filename.xls” or Server.MapPath(“~\foldername\filename.xls”)

for CSV or text files

string connectionstring = “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\”” +
“[folder path]” + “\”;Extended Properties=’text;HDR=Yes;FMT=Delimited()’;”;
// e.g. “c:\foldername\” or Server.MapPath(“~\foldername\”)

Note: the attribute HDR=Yes means the source file has header row. If your source file does not have a header row then change it to HDR=No.

————————————————————————————————————————————————-

Part 3: Retrieve data from the file and insert into SQL Server table.

using (OleDbConnection oledbConnection = new OleDbConnection(connectionstring))
{
  oledbConnection.Open();

  #region[required only for XLSX or XLS file]

  DataTable dbSchema = null;
  dbSchema = oledbConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables_Info, null);
  string cmdText = string.Format(“SELECT * FROM [{0}]”, dbSchema.Rows[0],

  [“TABLE_NAME”].ToString());

  #endregion[required only for XLSX or XLS file]

  #region[required only for CSV or txt files]

  string cmdText = string.Format(“SELECT * FROM [{0}]”, “[file name]”);

  // e.g filename.txt or filename.csv

  #endregion[required only for CSV or txt files]

  using (OleDbCommand oledbCommand = new OleDbCommand(cmdText, oledbConnection))
  {
    using (OleDbDataAdapter oledbAdapter = new OleDbDataAdapter(oledbCommand))
    {
      DataTable tempTable = new DataTable();
      oledbAdapter.Fill(tempTable);
      using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(“[Connection string for SQL Server]”))
      {
         sqlBulkCopy.DestinationTableName = “[Table name on SQL server]”;
         sqlBulkCopy.WriteToServer(tempTable);
         sqlBulkCopy.Close();
      }
    }
  }
}

Note:

The TABLE_NAME attribute in the database schema returns worksheet name of the Excel file. Here we are using data from first worksheet (Rows[0][“TABLE_NAME”]).

For CSV or text files, we can specify the file name as source table name.

 

VMware: expand virtual disk partitions

(Note: Always take backup of your VM before doing any disk management operations)

To expand virtual disk partitions, you should have free/unallocated space available on your virtual disk. If you don’t have then, please follow the steps mentioned in my previous post here.

Different procedures can be followed according to the guest Operating System.

1.Windows XP:
Install any disk management utility like “Partition Magic” on your guest OS.  Add the extra space to the existing drive or modify it according to your need.

2.Windows Server 2003
Third party utilities like “Partition Magic” does not support client version of windows. You need to purchase other utilities which support disk management on server OS.
If you do not want to purchase any third party tool then, you can use the alternate method.

• Step 1: Create a new VM with WINDOWS 7 or WINDOWS 2008 SERVER as a guest OS.
• Step 2: Shut Down the guest OS (Do not suspend).
• Step 3: Edit the VM settings. Under hardware tab, click on “Add” button.
• Step 4: Select hardware type as “Hard Disk”, Click Next.
• Step 5: Select “Use an existing virtual disk”. Browse and select your old VM with Windows 2003 guest OS.
• Step 6: Now run the WINDOWS 7 / 2008 VM. You will see a new hard disk drive inside your VM (In my case it’s D drive). This is actually your WINDOWS 2003 Virtual disk.
• Step 7: Now run the Disk Management utility for your windows 7 / 2008 VM (See below for details). And expand your new drive to fill the unallocated/free space.
• Step 8: Now shut down your WINDOWS 7/2008 VM. You can remove the extra hard disk added on step 5.
• Step 9: Go back and start your old WINDOWS 2003 VM. You will have the extra space you needed.

Otherwise, if you have already purchased “Partition Magic”. Then you can create a new VM with WINDOWS XP as guest OS. Then add the WINDOWS 2003 VM as second disk (step 2 to 5). Then install the partition magic on WINDOWS XP VM and use it as mentioned in step 9.

3.Windows 7 or Windows Server 2008
Microsoft provides its own inbuilt Disk Management utility for these Operating Systems. To use this go to Start Menu -> Search Programs and Files -> Type Disk Management. Then select the option under control panel “Create and format hard disk partitions” from the search result. Otherwise select Start menu -> Run -> Type “diskmgmt.msc”.

VMware: expand virtual disk size

Steps to expand virtual disk size:

1. Open your VMware virtual player or workstation. Make sure your virtual machine state is “Powerd Off”, not “Suspended”. If not, then first shutdown the virtual machine.

2. Select the virtual machine on left pane and click on “Edit virtual machine settings”

3. Then on the “Hardware” tab on “Settings” window, click on Utilities -> Expand

4. Enter the new disk size. You must have  free space available on your machine equal to the new size.

Note that this utility will only increase the disk size. It won’t change the partition. The additional space will appear as empty (unallocated) space.

To expand the existing virtual partition, please refer my next post.

Cheers.

XSLT transform datetime value to date format

If you want to transform a DateTime value to Date using XSLT then “substring-before” function can be very helpfull. e.g. if we have a source like:

<StartDateValue>2011-01-15T00:00:00+01:00</StartDateValue>

and we want to transform it like:

<StartDate>2011-01-15</StartDate>

Then we can use the substring-before function as:

<StartDate>
<xsl:value-of select=“substring-before(StartDateValue,’T’)” / >
</StartDate>

Hope it will be helpfull.

Cheers

Visual Studio 2010 Package did not load correctly

Few days back I re-installed Visual Studio on my VPC and it started giving warning messages. In my case, Visual studio was unable to load some packages. It reported errors related to various data entity packages. Here is one example

—————————
Microsoft Visual Studio
—————————
The ‘Microsoft.Data.Entity.Design.BootstrapPackage.BootstrapPackage, Microsoft.Data.Entity.Design.BootstrapPackage, Version=10.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a’ package did not load correctly.

The problem may have been caused by a configuration change or by the installation of another extension. You can get more information by running the application together with the /log parameter on the command line, and then examining the file ‘C:\Documents and Settings\Administrator\Application Data\Microsoft\VisualStudio\10.0\ActivityLog.xml’.

Continue to show this error message?
—————————
Yes No
—————————

I found a nice article regarding this on Microsoft Connect.

According to the article, I installed 3 packages from Visual Studio installation disk. They are:
1. \WCU\DAC\DACFramework_enu.msi
2. \WCU\DAC\DACProjectSystemSetup_enu.msi
3. \WCU\DAC\TSqlLanguageService_enu.msi

Usually this solves the proble. But in my case it did not help.
The reason was, I uninstalled Visual Studio 2010 from C drive and re-installed on E drive! (I did so to save some space in my C drive).
Some dlls at the old location did not get deleted with un-installation process. And re-installation did not install them back on E drive. Strange!
Every-time I ran Visual Studio, it used to search these dlls on the new path. This was the cause of the error messages.

Solution:
Someone suggested to delete all related registry entries and re-install these 3 packages.

But that was very tricky and I have absolutely zero knowledge in registry entries. So I copied these missing dlls from my previous installation folder.
i.e. “C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE”

and pasted it in the new one.
i.e. “C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE”

Note that I copied only those Dlls which were missing. To be in a safer side, I did not touch the sub-folders.

Well, I accept that this a very unprofessional and illogical solution. But that worked for me and I saved some time for me. Hope this will be helpful for those who are in same situation.

Cheers.

VMware – Attempting to load a 64-bit application, .. this cpu is not compatible with 64-bit mode.

Recently, I got this error message while trying to run a 64 bit guest OS on VMware.

“Attempting to load a 64-bit application, .. this cpu is not compatible with 64-bit mode.”

I was running a 64bit windows 7 professional OS. And the VMware workstation version was 6.5.  The guest OS was a windows server 2003 64bit R2 edition.

I just turned on Hardware Virtualization in BIOS and it fixed the problem. You can find very useful information about hardware virtualization from Microsoft  and Intel .

However some users can still face the same problem even after enabling hardwate virtualization. In this case you might need to disable Paravirtualization in vmware. More information available at VMware.

Cheers.

 

Hexadecimal equivalent of SharePoint Base Permissions

Sometimes we need hexadecimal equivalent of SharePoint permission levels. These values can be very useful to make UI changes through JavaScript/JQuery based on current logged-in user’s permission level.
I used the following code in a C# console application to get the hexadecimal equivalent of permission levels:

using System;
using Microsoft.SharePoint;

namespace GetSPBasePermissions
{
class Program
{
static void Main(string[] args)
{
foreach (SPBasePermissions enm in Enum.GetValues(typeof(SPBasePermissions)))
{
string enmName = enm.ToString();
string enumValue = String.Format("0x{1:X}",
enmName, (UInt64)Enum.Parse(typeof(SPBasePermissions), enmName));
Console.WriteLine(enmName + " : " + enumValue);
}
Console.ReadLine();
}
}
}


This program will give the following output:

EmptyMask : 0x0
ViewListItems : 0x1
AddListItems : 0x2
EditListItems : 0x4
DeleteListItems : 0x8
ApproveItems : 0x10
OpenItems : 0x20
ViewVersions : 0x40
DeleteVersions : 0x80
CancelCheckout : 0x100
ManagePersonalViews : 0x200
ManageLists : 0x800
ViewFormPages : 0x1000
Open : 0x10000
ViewPages : 0x20000
AddAndCustomizePages : 0x40000
ApplyThemeAndBorder : 0x80000
ApplyStyleSheets : 0x100000
ViewUsageData : 0x200000
CreateSSCSite : 0x400000
ManageSubwebs : 0x800000
CreateGroups : 0x1000000
ManagePermissions : 0x2000000
BrowseDirectories : 0x4000000
BrowseUserInfo : 0x8000000
AddDelPrivateWebParts : 0x10000000
UpdatePersonalWebParts : 0x20000000
ManageWeb : 0x40000000
UseClientIntegration : 0x1000000000
UseRemoteAPIs : 0x2000000000
ManageAlerts : 0x4000000000
CreateAlerts : 0x8000000000
EditMyUserInfo : 0x10000000000
EnumeratePermissions : 0x4000000000000000
FullMask : 0x7FFFFFFFFFFFFFFF

How To – Enable FILESTREAM in SQL Server 2008

To enable FILESTREAM in SQL Server 2008:

1. Go to Start Menu -&gt; All Programs -&gt; Microsoft SQL Server 2008 -&gt; Configuration Tools -&gt; SQL Server Configuration Manager

2. Select “SQL Server Services” from the left side tree view.

3.

4. Right click the SQL Server instance on which you want to enable FILESTREAM option. (e.g. “SQL Server (SQLEXPRESS)” or “SQL Server (MSSQLSERVER)”) Select properties.

5.

6. On properties window, go to FILESTREAM tab.

7.

8. Select “Enable FILESTREAM for Transact-SQL access”.

9. Then select “Enable FILESTREAM for file I/O streaming access” and/or “Allow remote clients to have access to FILESTREAM data” based on your requirement.

10. You can change the default “Windows Share Name”.

Database diagram support objects cannot be installed

While trying to create database diagrams in SQL server 2008 or 2005, you might get this error. This is because the database owner does not have a login ID mapped to it. Most of the time, this error occurs for databases which are restored from a backup. But sometimes it also happens for newly/locally created databases also.

Error Message: “Database diagram support objects cannot be installed because this database does not have a valid owner. To continue, first use the Files page of the Database Properties dialog box or the ALTER AUTHORIZATION statement to set the database owner to a valid login, then add the database diagram support objects.”

To come over this problem, please run the following SQL Query in SQL Server.

EXEC sp_dbcmptlevel ‘DBName’, ‘100’;

— Replace ‘100’ with ‘90’ if you are using SQL Server 2005

go

ALTER AUTHORIZATION ON DATABASE::DBName TO “OwnerID”

go

use DBName

go

EXECUTE AS USER = N’dbo’ REVERT

Go

If it gives any error about the Owner ID (e.g. Could not obtain information about ******, error code 0x5.) then replace “OwnerID” with “sa”. It should work.

But Microsoft says this feature will be removed from future versions. For more details check Here

How to solve – “Login failed for user ‘sa’. Microsoft SQL Server, Error: 18456”

One of the common reasons for this error is wrong security configuration in SQL Server. This happens when we upgrade our SQL server instance from a previous version which was configured for “Windows Authentication” only. Or, mixed authentication mode was not selected while installing the SQL server.

To solve this please follow the two steps mentioned below.

Step 1:

Connect to the database using windows authentication.

Expand the “Security” option in object explorer and go to “Logins”. Right click on user “sa” and select “properties”.

In the “Properties” window, go to “Status” tab. Grant permission to connect to database. And change the login option to “Enabled”. Click “Ok”.

Step 2:

Right click on the SQL Server in object explorer and select “properties”.

Then go to “Security” tab. Check if “SQL Server and Windows Authentication mode” option is selected. If not, then select this option and click “Ok”.

You will get an notification window to restart the SQL server instance.

To restart the service, right click on the database name in object explorer and select restart. (Or, you can restart through SQL Server configuration manager / windows Services)

Now you should be able to login using “sa” credentials.