SSIS Error 'Microsoft.ACE.OLEDB.12.0' Provider Is Not Registered

We have created a new laptop image which windows 7 64 bit OS with 64 bit office 2010 and 64 bit SQL Server 2008 R2 Developer edition installed along with BIDS.  After we started deploying the image and using the new configuration team members who attempted to import data from excel spreadsheet in using SSIS packages reported problems.  The error message screen shot is below.

The error message was 'The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine' when I wanted to access the sheet to see the first 200 records coming from excel file.  And when click on the columns to read the column names we got another error message which also mentions that the provider is not registered on the local machine.

Our image had 64 bit of SQL Server installed but the BIDS application is 32 bit and did not have the 64 bit driver installed for Excel so we keep getting the error.

Solution for the problem was Microsoft.  MS has a 64 bit ACE driver released which can be downloaded and installed from this link.  We have downloaded and installed AccessDatabaseEngine and opened the BIDS and SSIS package then I was able to read the data from spread sheet and get the column mappings.

HTH,

Bulent

Install .NET Framework 3.5.1 using Powershell

Hi there,
I started to study SQL Server 2012 to take certification exam and I was setting up a lab environment.  In one of the steps the exercise was to install .NET Framework 3.5.1 using powershell.  .NET Framework 3.5.1 is prereq for SQL Server 2012.

To be able to install .NET Framework the I needed to start the session elevated to administrator and then execute the powershell commands.  To accomplish this I took the following steps:

1. Right click the powershell icon on the taskbar and click on 'Run As Administrator'.
2. In the powershell window type this command 'Import-Module ServerManager' then hit enter to execute (exclude the single tick before the Import and after the ServerManager)
3. execute the command 'add-windowsfeature net-framework-core' and hit enter (leave out the single ticks around the command)

Here is the screenshot of the whole process.

HTH,
Bulent

PowerShell ExecutionPolicy Access Denied To The Registry Key

Hi all,

I recently accepted an employment offer and now working in Downtown Denver.  Anyhow, I was given a laptop as my workstation.  Since it was a brand new built laptop not everything I needed was installed so I started installing/configuring the software.  One point in time I need to start scripting and I opened the power shell window.  First thing I checked to see the execution policy setting for my new built laptop.  By default a new installation is set to 'Restricted' which does not allow to run a powershell scripts and PowerShell can only be used in interactive mode.  Which is what I expected.  So I started to configure the execution policy.  Here are the steps for how to do it.

Open powershell by clicking start>run and typing 'powershell' (without the ticks) then hit enter.  This will open powershell scripting environment.

1.  Type 'Get-ExecutionPolicy' and hit enter, this will return the execution policy setting.  There are 4 available options and I need to set the execution policy to 'RemoteSigned' to be able to run the scripts I have created and some downloaded from trusted sources.  Here is the link to Microsoft that talks about the policy settings.

In my case, it was set to 'Restricted'

2.  Type 'Set-ExecutionPolicy RemoteSigned' and hit enter.  This is where I got an error message stating that   access to the registry key 'HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\PowerShell\1\ShellIds\Microsfot.PowerShell' is denied.  So when this command is executed it tries to set the execution policy for the local machine for all the users.  In my case I only need to set it for my user account not whole machine for all users.

3.  Type 'Set-ExecutionPolicy RemoteSigned -Scope CurrentUser' and hit enter.  No errors.

4.  Type 'Get-ExecutionPolicy' and hit enter and I got 'RemoteSigned' back as the configure setting.

Now I can execute powershell scripts from SQL Server Agent job to do what I need.  All of the above steps have been captured in the screen shot below.

HTH,

Bulent

VirtualBox Cannot Register The Hard Disk (UUID Already Exists)

I have been using Virtual Box to test some software on a virtual machine as opposed to running the tests on my workstation.  Everything has been working fine while I have a single virtual machine but I had to create a new virtual machine to do some more testing.

I wanted to use the existing virtual machine and not have to install it all over again.  So I copied the folder where my Virtual Machine is stored to another folder.  Then opened the VirtualBox and started to create a new virtual machine and pointed it to the new folder where I copied the contents of the folder for the first Virtual Machine and that's when I got the following error.

Then I started searching the Internet because I knew that the solution must have been available.  I came to find the solution and here is the simple fix for the problem.  We need to open a command prompt and run a command to reset the uuid for the .vdi file where we copied to initiate the second virtual machine.  Here is the steps that need to be done.
   1. Start command prompt (I recommend running it as administrator).
   2. Change your directory to VirtualBox folder.  In my case I am running x64 OS so changed my directory to "C:\Program Files\Oracle\VirtualBox\"
    3. Execute the command to reset the uuid.
        VBOXMANAGE.EXE internalcommands sethduuid <PathOfNewVDI>

The screen shot below is from my workstation which reset the uuid and then I moved on with the VirtualBox and created the new virtual machine.  So in minutes we have a new vm running to do the test.  Simple and fast as long as you know the solution.

HTH,
Bulent

SQL Server Create Identity Field Using Select Into

While working on some data issue I needed to create a backup table on the fly using SELECT INTO statement.  I knew that it was possible but never did it my self.  So it's as simple as defining a column as identity with the data type in the select statement.  Below is statement shows how it's done.

SELECT IDENTITY(INT,1,1) AS RowID,
                *
INTO       dbo.MyTable_Backup
FROM     dbo.MyTable

Hope this helps,
Bulent

Compressing SQL Server Backup Files

Hello Database Professionals,

Starting with SQL Server 2008 Enterprise Edition we have the option of compressing backup files (this includes both BACKUP DATABASE and BACKUP LOG statements which mean not only database backups but log file backups will be compressed) to save disk space and also decrease the duration of the backup and restore operations. The backup compression was an enterprise edition only feature with SQL Server 2008 but with SQL Server 2008 R2 this feature is supported by standard and higher editions. So that was welcome news for me since I support number of SQL Server 2008 R2 Standard Edition.

I have been using T-SQL script to backup databases. The script checks the edition of the SQL Server and then builds the backup command to compress the backup file if it’s enterprise edition or just backups the database if it was a standard edition.

Before the end of 2011 I have completed deploying/upgrading to SQL Server 2008 R2. That meant that my script no longer needed to check the edition of SQL Server 2008 R2 since the compression is already supported in standard and higher editions. So I turned to global configurations and enable the backup compressions. I would like to remind the readers that enabling the backup compression creates additional CPU overhead which depends on you workload might impact your server performance. I suggest that you test your backup process and if necessary using Resource Governor (this is Enterprise Edition only feature) create a low priority compressed backup in a session whose CPU usage is limited by Resource Governor when CPU contention occurs.

Let’s start with checking the backup compression setting for the server by using the script below.

USE master

SELECT *

FROM sys.configurations

WHERE name = 'backup compression default'

ORDER BY name

Let’s check the returned result for the value_in_use column. This shows us the running value curently in effect for this option and is_dynamic column tells us the changes take effect after the RECONFIGURE statement executed. Now it’s time to execute the below script to enable the backup compression since the value_in_use is 0 for the server I am currently working.

USE master

EXEC SP_CONFIGURE 'backup compression default',1

RECONFIGURE

Let’s check the value_in_use by executing the first select statement.

USE master

SELECT *

FROM sys.configurations

WHERE name = 'backup compression default'

Now we should see that the value_in_use is 1 and we don’t need to use the optional keyword COMPRESSION in the backup database command.

By default when backup is compressed checksums are performed to detect media corruptions but if for any reason you need, you can explicitly disable the compression by using NO_COMPRESSION keyword in your backup statement.

I have seen anywhere from 35% to 80% compression ration and backup duration decrease from several hours to 30-45 minutes for the databases I administor. Your mileage will vary and you should TEST, TEST, TEST and deploy your changes for your environments. Here is the link to find out more about backup database command in MSDN.

HTH,

Bulent

Dropping Multiple SQL Server Objects in Single Line

As humans we try to find a way to work faster and efficient. As data professionals, typing less probably is another thing we want. My SQL Server tip today is about dropping multiple objects (tables, views, stored procedures, and even databases) in single drop statement. This is powerful but can be dangerous in production so please use caution. Here is a script that creates couple of tables and then drops both tables in single drop statement.

Sincerely,

Bulent

-- CREATE TABLES

USE tempdb

GO

CREATE TABLE dbo.TableT1 (t1c1 TINYINT)

CREATE TABLE dbo.TableT2 (t2c1 TINYINT)

GO

INSERT INTO dbo.TableT1 VALUES(1)

INSERT INTO dbo.TableT2 VALUES(2)

GO

-- CHECK THE TABLES CREATED

SELECT *

FROM sys.tables

WHERE name LIKE 'TableT_'

GO

-- DROP BOTH TABLES IN SINGLE DROP STATEMENT

DROP TABLE dbo.TableT1, dbo.TableT2

GO

-- CHECK THE TABLES DROPPED

SELECT *

FROM sys.tables

WHERE name LIKE 'TableT_'

GO