Wednesday, June 17, 2015

Get the Solution of Error 3343 in MS Access Database

MS-Access is mostly used in the small organizations to manage the valuable data. A database user faces different database errors during run-time and error 3343 is one of them. Error 3343 occurs when the database has corrupted and a user tries to access the same database. Possibly following error message is display on the user’s screen:

Let’s walk through the different reasons behind this error:

Reason 1: Splitting of the database
As we all know that a database can be small or large in the size. In the most cases; user stores data in a single file of the database and this single database file grows day by day and can create trouble. Spitting of database can fix the problem, but it is not the concern here. When you try to split this database into two parts and corruption on a single part of the database may be the main reason of whole database corruption. This is very serious issue and complete database repairing is a very tough job when it large in size.

How to fix: MS-Access has inbuilt repair tool to repair the database and it is called Compact and Repair utility. With this utility, you can easily repair the corrupt database.

Note: If the user is unable to repair the database using above utility then can download other tool which can repair the database.

Reason 2: Opening a database in previous version
It is the second most possible reason behind the error 3343 in MS Access database. Default file format supported by the Access 2007 and 2010 is .accdband if you want to open the Access 2010 file into Access 2003 then, you have to convert the file of 2010 to 2003 by MS- Access. Mostly you convert the database into the earlier version for sharing with others.

How to fix: Split the database into parts and open the database file in the previous version are the main reasons of this error. To fix this error you can take help from reliable commercial software
»» 

Monday, June 15, 2015

Shaking Hands with SQL Server 2016

I am a SQL Server lover and I was eagerly waiting for the SQL Server 2016. It is good to hear that, its public preview is available to download. Mr. Satya Nadella, CEO of Microsoft announced SQL Server 2016 during ignite conference. If you thinking about to upgrade the SQL Server then, it is a best reason to start budgeting and planning for the upgrades in your organization. It has the ability to deliver mission-critical capabilities with operational analytics built-in. Top capability for the SQL Server 2016 is the always encrypted behavior and mainly designed to protect data in motion or rest.

Always Encrypted and Secure: This feature is the highlight of the SQL Server 2016. With this feature, SQL Server can perform operation on encrypted data and encryption key can be resided with the application in the customer trusted environment. Encryption and decryption of the data happens clearly inside the application which reduces the changes that have to be made to existing applications.

In-Memory OLTP & Real Time Operational Analytics:  In-Memory Online Transactional Processing (OLTP) is a major focus is fast performance. Maximum size of the durable table in the SQL Server 2016 is 2 TB where as in 2014 it is 256 GB. It also supports nested native procedure calls and natively-compiled scalar UDFs. It has new syntax extension to ALTER your bucket count values with the simple rebuild:
ALTER TABLE dbo.InMemoryTable

Mobile BI and PolyBase: SQL Server 2016 expands its scope beyond data warehousing, transaction processing and business intelligence to deliver the advanced analytics to know deeper insights of the data. Additionally, we are creating PloyBase into the SQL Server to expand the power of extract value structured and unstructured data using existing T-SQL skills. With this feature user can get the faster insights by rich visualization on different devices including mobile application on Windows, iOS and Android.

Stretch Database:  This awesome technology allows you to dynamically stretch your cold and warm transactional data into the Azure cloud platform, so your important operational data is always at hand without any worry of size. Always encrypted feature can be used with stretch database to extend the data in a more secure manner.

Additional Features

  • Security enhancements for the Row-level security and Dynamic Data Masking to round out security investment.
  • Partially support DML triggers.
  • Supports indexes on NULLable columns.
  • Supports foreign key.
  • Analysis Services and SQL Server Enterprise Information Management (EIM) tools to get an upgrade in performance, usability and scalability.
»» 

Wednesday, May 27, 2015

Easy to Deal with MySQL Error #1045

When a user formatted PC and re-installed local server (XAMPP) then sometimes user got 1045 error message. This occurs when root@localhost wasn’t granted the essential rights a user tries to access phpMyAdmin after successful installation of the local server. Here is the complete error message which occurs on screen:
#1045 Access Denied for user 'root'@'localhost' (using password: YES)

We have different servers to use MySQL database like: WAMP, MAMP, LAMP and XAMPP. WAMP is for Windows, LAMP for Linux, MAMP for Mac OS X and XAMPP for all operating systems. It also supports perl, mercury mail, filezilla and some scripts. In this blog, I will try to help users to get rid from this error for any local host server by the use of phpMyAdmin.

User can try following methods to resolve this error. Your problem might be fixed using the first method or you have to perform all three.

First Method

  1. Start the command prompt and go to the XAMPP directory: cd ../../Program Files/xampp
  2. Now go to xampp->mysql->bin
  3. Now run the following command: mysql.exe --user=root --password= 
  4. After this, MySQL command line will start and we can change the –user= and –password= accordingly.
Second Method: In this method we create the batch file to make it easier. To do this just follows simple steps:

  1. Create a file mysql_command.bat in the XAMPP folder and edit this file with any text editor and paste in this:
      @echo off
      echoMySQL command line...
      mysql\bin\mysql.exe --user=root --password=
  2. Save the file and change the username and password if you changed username and password. Now run command.bat and command prompt will display with MySQL command line in running condition.
Third Method:Open the file which is located in localhost ->xampp ->phpmyadmin->

  1. Open this file using any text editor.
  2. Now find the code $cfg['Servers'][$i]['password'] = ''; // MySQL password
  3. Now change the password which you have created in step 1.
  4. Click on the Save button.
»» 

Thursday, May 21, 2015

Save More than 35% on Purchase of Stellar Database Repair Bundle

Recently I saw a discussion on forum about database recovery tool and I was very surprised to discover that many people do not think that these tools actually work. Sometimes when a user tried many options to recover and repair the database and unable to do it then, a third party tool can be the ray of hope. Stellar Data Recovery presents a database bundle which is tested by the team of experts. The most important thing for any tool is a reliable source which can provide useful software. This blog is about Database Bundle which includes Database recovery software for SQL Server, MySQL and Oracle. For many businesses, a database recovery tool plays a major role in their work and these tools are safe and secure.

This database bundle consist three tools:

Stellar Phoenix SQL Database Repair: It is a perfect tool that facilitates complete recovery of .MDF and .NDF file ofthe SQL server database. It supports MS SQL server 2014, 2012, 2008 and all lower versions. This tremendous tool recovers tables, triggers, keys, indexes, rules and constraints. It has the ability to self-reconnect in case of interruption during data recovery process and capable to repair much kind of corruption errors like: suspected database, consistency corruption, possible schema corruption and clustered index leaf node corruption. 

Price: $599

Key Features:
  • Recovery of foreign keys, primary keys, unique keys and identity.
  • Recovery of triggers, tables and view.
  • Supports sequence objects.
  • Supports ROW compressed and PAGE compressed data.
  • Supports XML data types and XML data indexes.
  • It allows finding of object name in the tree view.
  • It shows the preview of the recoverable database object.
Stellar Phoenix Database Repair for MySQL: It is a professional MySQL database recovery tool that carefully repairs corrupt MySQL databases to recover valuable objects such as primary key, triggers and views from it.Software supports MySQL versions 6.x, 5.x, 4.x and 3.x versions.

Price: $449

Key Features:
  • Customizable user interface.
  • It repairs all data types except spatial data types.
  • It also repairs MySQL database created on Linux platform.
  • Shows the details of ongoing process in a message log pane. 
Stellar Phoenix Oracle Database Recovery: Oracle database recovery process can be very straight forward with this tool because this tool perfectly repairs a corrupt.DBF file created in oracle 9i, 10g and 11g. It searches all Oracle databases present in the system and enables preview of recoverable database objects. Moreover, the tool creates the log report of the recovery process. A heavily damaged database can be optimally repaired and restored by this amazing tool.

Price: $799

Key Features:
  • Recovers schemas, triggers, roles, index, synonyms and sequence.
  • Recovers database query that links in .txt file.
  • Recovers cluster and cluster tables.
  • Recovers stored-procedures and user-defined data functions.
  • It is able to recover nested table, nested array type, nested object type etc.
  • It simply recovers package and package body.

»» 

Wednesday, May 13, 2015

Why Regular Consistency Check is Important?

Consistency Check always plays a very important role in every database administrator’s life.The number of individuals does not like to check it on the daily basis.For a regular user, this is the way of continuous checking of database corruption, which can happen due to numerous reasons and no-one can predict it. It checks the logical and physical integrity of the database objects in the specified database. The best point is that it also recommends suitable repairing options depending upon the level of corruption for repairing the selected database. Now the million dollar question comes in very DBA mind; what is the best suitable time-interval to run this consistency check on the database.

When a user should run consistency check:Well it depends on the situation. Here I have explained two examples.

  • Suppose you have an unreliable I/O subsystem and your database is running on the same machine then it is indicating that your database is on high risk. A database might be corrupt or damaged anytime. Running consistency check at the earlier stage helps you in finding the problem as it starts. A minor corruption can cause a big data loss from the database. 
  • Suppose you have an accurate I/O subsystem, with up-to-date drivers and firmware. You have an inclusive backup strategy which you have tested and you have full confident that, it will recover from the data corruption with minimum downtime and data loss. In this case a user may be comfortable with consistency check once in a week. 
The important factor is that, how many times user runs the consistency check and how much comfortable with I/O subsystems and capability to recover from corruption problem? Loss of integrity in your database today then, User probably runs DBCC CHECKDB daily.

Note:If a user does not aware about consistency check and its benefits; it can be digestive but, If the user is so much aware of it then, the consistency check is always expected. Even if you think that you cannot run it because it takes too much time/resources etc.

Detect corruption on other way:When a database corruption detected by the regular operation. It has been noticed that many databases do not use (read/update) their many parts on the daily basis. It means the unused parts of the database got corruption and user is not aware of it because user is not running the consistency check to know the problem. It means that users are not trying to know that I/O subsystem causing a problem. By this problem, the database can be corrupt and it affects the other part of it.

During improvement or migration: I have seen many times that people always use consistency check after migration but not before. After every update DBCC CHECKDB has been improved especially from 2000 to 2005. Suppose you run the consistency check on the migrated/upgraded database and got corruption then, you will perform following options like

  • Run repair options: repair_fast, repair_rebuild, and repair_allow_data_loss
  • Restore from the backups: it might be possible that your backup has been corrupt too. 
It has been noticed that, corruption always available on before but discovered after upgrades. So always run the consistency check before any upgrade.
»» 

Tuesday, May 5, 2015

Easily Share Excel Workbooks using Excel Services in SharePoint 2013

Excel services are very indispensable for many organizations and you can use these services with SharePoint 2013. It gives various facilities to users and can be easily manageable.It is a shared service; user can publish his/her Excel’s workbooks to SharePoint server 2013 very easily.

Note: Excel services are only available in the enterprise edition of SharePoint Server 2013.

Excel services is an advanced tool with significant merits. It can be connected to reports and external data sources. SharePoint has a document library where users can publish workbooks in this document library.Document library has numerous excel services like Excel Web Access, Excel Calculation services and Excel Web Services for Programmatic Access etc.

It supports Sharing, Security, Management, and Excel workbook services in the browser by providing the following:

  • Management of Workbook by Global Setting – It includes security setting, session management, load balancing, memory utilization, external data connection, and workbook cache. This option is available on the Manage Excel Service Application page.You must be the member of the Administrator for Excel Services service application that you are configuring.

  • Trusted File Location – It is used to define the location of Excel workbook. User can manage the workbooks because they are explicitly enabled and Excel Services only loads from trusted file location. In the figure, the red arrow shows the location of Trusted File Location on the page.

  • Sharing of Workbooks –Excel 2013 workbooks can be converted to SharePoint Server Document Library to give the access to other browser-based user. Excel services loads the workbook and refreshes the external data (If required) for the user. A user can interact with this data by expanding, filtering, sorting and by passing in parameters. It gives ability to do analysis of published workbooks.

  • Business Intelligence Dashboard – With the help of Excel and Excel Services we can build a browser-based dashboard library to store and create Web Part pages. It includes Web Part pages with status lists and PerformancePoint dashboards for SharePoint status indicators.  

  • Report Building –It is one of the most important features of Excel Services.We can build the reports that we have created in Excel and make it visible to others. 

»» 

Sunday, April 12, 2015

How To Deal with Error Message 7105 in SQL Server

Microsoft SQL server supports LOB (Large Object) data types to store the very large amount (Maximum size is 4 Gigabytes) of data. These data types are CLOB (Character LOB), BLOB (Binary LOB) and DBCLOB (Double-Byte Character LOB).Structures of these data types are different from regular data types. Sometimes SQL server generates Error 7105 due to LOB referred by database page can’t be accessible. Continue reading this article, you will find resolution for this.

Error Message 7105: When error 7105 occurs in SQL server then we can see the following error message on the screen-

Msg 7105, Level 22, State 6, Line 1
The Database ID 11, Page (255:177), slot 1 for LOB data type node does not exist. This is usually caused by transactions that can read uncommitted data on a data page. Run DBCC CHECKTABLE

Note:In this error Severity Level is 22 which mean connection is terminated by the server.

Reasons of error 7105 in MS SQL: This error can be occur by the following reasons –

  1. Database corruption issue lies within a database page or LOB (Large Object) structure.
  2. The query that fails with READ UNCOMMITED ISOLATION or NOLOCK query hint.
  3. When SQL server engine causing the query to fail with the same error.

Solutions: You can use the following methods to repair SQL Database-

  1. Run DBCC CHECKDB against the database or DBCC CHECKTABLE against the table where the problem was encountered.
  2. If the problem is linked with the database query then rewrite and rerun the same.
  3. If the problem is related to SQL server engine then update the server with latest service pack.
  4. Restore the .mdf file from the last backup. 
»» 

Tuesday, December 30, 2014

How to Fix SQL Server Error 8928

SQL server database corruption is a like a worst nightmare for every SQL server database users. For any organization; small to big, database integrity and availability is the most important part. Database administrator (DBA) is responsible for it, if you are a DBA of SQL server database then it’s your responsibility to manage the database and make a proper disaster plan to handle any kinds of database corruption scenarios. In this article, we will learn about a particular error message that appears during selection of data from a table.

Select data from table;

Select * from My_table_name

But received below error message

Msg 8928, Level 16, State 1, Line 55
Object ID 245575913, index ID 0, partition ID 72057594040549376, alloc unit ID 72057594045792256 (type In-row data): Page (1:288) could not be processed. See other errors for details.
CHECKDB found 0 allocation errors and 4 consistency errors in table '[Table_Name]'
(object ID 1326627769).
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB

As the error message indicates, there is a corruption in the specified database. So don’t use the DBCC CHECKDB command with repair option first because you will loss amount of data from the database after executing it.

Restore from backup: It is suggested to restore the database from clean & updated full backup. It is the best solution to handle any kind of SQL database corruption.

Note: Before using your backup, you will need to verify it. ‘Restore verifyonly’ command checks the SQL server database backup and ensures that you are restoring your database from consistence backup. While restore only command ensures that backup is complete and can be used in all format.

If you don’t have updated backup then you can go with Repair command but you will need to put your database in single user mode first.

ALTER Database Database-Name SET SINGLE-USER

Now you can run DBCC CHECKDB with repair_allow_data_loss on the database.

DBCC CHECKDB (Database-Name, Repair_Allow_Data_Loss)

After running this command; you will get a message on your screen.

The error has been repaired.

Conclusion: It is very hard to recover data from corrupt database files without backup. Sometimes corrupt database file can be repaired, sometimes not. So always make a backup of database and regularly update it. Make this habit and add in your daily routine. Most of the times it has been observed that SQL database data lost during manual repairing process, to avoid this situation DBA’s can use some tools to repair corrupt SQL database.

Note: ALL third party SQL database repair software are paid, there is no free tool available
»» 

Wednesday, November 26, 2014

Create Access Database Backup – Manual & Automatically

You should regularly backup your database to safeguard your data from any uncertain data loss situations. Backup saves your money and precious time invested on the database to build it. Without backup, it’s like nightmare to recover data from loss or corrupt Access database file. An Access database gets corrupt or damaged to several reasons, as explained here and you can easily repair corrupt database by reading this article. If you are database administrator then it’s your responsibility to take backup of database on regular time interval and make sure that backup is working perfectly. Time interval for backup update depends upon your business requirement need.    

Backup an Access Database: You can create backup of an Access database manually or automatically. Manual backup creation of database is very easy and essential; there is some 3rd party software that performs automated backup of file system. You can also use DropBox directory for online backup. To do this, you need to copy the database file into your DropBox directory. If you don’t know about this (read here), it is a great piece of software that synchronizes the files & folders on your machine to the Internet (Online).   

Create Database Backup Manually: It is very easy and quick process. It just takes around 30 minutes to accomplish the backup process and these 30 minutes can save your valuable time and some dollars. Please note that backup process time duration may be higher for the larger database. Following steps are required to be done to create a backup of an Access database. 

  • Open your Access database that you want to create backup.

  • Click on the Office button and go-to the ‘manage’ tag.
  • Click on the ‘Back Up Database’ from ‘Manage this database’ tab.

  • Now ‘Save as’ dialog box will be open. Choose the location where you want to save the backup and review the name of database, by default MS Access named the backup as database name with the date and it is the good idea. It helps during the database restoration process. If you want to change the database backup name then change it. It’s totally depends upon you.
  • Done 
Create Database Backup automatically when Database is Open: Follow below steps to accomplish the automatic backup of your database.
  1. Create a table within your Access database and named it as BackupDetails.
  2. Add a VBA module with code.
  3. Now create macros that automatically run when Access database is loaded. To create macros, see the follow the following steps.
Create Tab ->Macro->Named the Macro

Important Tips:
  • Add database backup as a regular task in your daily routine.
  • If your database contains sensitive information then encrypt it with password.
  • For safely purpose, make an offsite backup of your database.
»» 

Friday, October 17, 2014

Backup Encryption in Microsoft SQL server 2014

Microsoft releases newer version of SQL Server that is SQL server 2014. It is very close to the previous version of SQL Server 2012 release. For many, it is very hard to upgrade the newer version of SQL server because it requires money & resources. However, the newer version of SQL Server has many new features for organizations that can justify the cost and upgrade. The most notable features in SQL server 2014 are In-Memory OLTP, enhancement in Always-on, and enhancement in backup.

In this article, we will learn and concentrate on backup enhancement in SQL server 2014.

SQL server 2014 supports in-built backup encryption for the database. All the previous SQL server versions did not support database backup encryption that required a third party software to accomplish this task. With SQL server 2014, DBAs can encrypt the database backup while creating the backup of database. It can be accomplish by specifying the encryption algorithm.

Benefits of SQL Backup Encryption:
  • It helps in securing the data of SQL server database backup. When you try to create a backup of yourdesired database, SQL server 2014 will provide the option for backup data encryption.
  • It supports multiple encryption algorithms: AES 128, AES 192, AES 256, and DES up to AES 256 bit.
  • It is helpful for all the SQL server databases that are encrypted using TDE.
How to Encrypt SQL Backup: You can encrypt the SQL database backup during backing up the database by following methods:
  1. Maintenance Plan Wizard
  2. Using T-SQL
Maintenance Plan Wizard: It creates maintenance plan that is used by database agent on regular basis to perform the task. You can use it to perform database administrative tasks like integrity check, backup, statistics update, shrink database, cleanup history, rebuilt index, recognize index, and many more,  when you required.

Perform following steps to encrypt SQL database backup with Maintenance plan wizard.
  • Right click on the desired database, you want to create backup.
  • Now select ‘Tasks’ and then ‘Back Up’, a backup dialog box will be open.
  • Right click on the ‘Backup Devices’ and select ‘Back up a Database’.
  • You are on Backup option page, Tick ‘encrypt backup’ as shown in the below image.
  • Now select your desired backup algorithm & certificate key from the drop down list.
  • Click on the ‘OK’ button to accomplish the backup encryption operation.

Using T-SQL:

You can easily encrypt the backup of SQL server database with T-SQL during backup creation. Run below T-SQL command to accomplish the task:

TO DISK = D'\\ServerLocation\Backup\Database-Name_backup.bak'
,ENCRYPTION (Algorithm-Name, SERVER CERTIFICATE = Key or Certificat)
,STATS = 10 GO


It is very important to make a backup copy of your ‘Certificate or Asymmetric key’. During backup restoration or for attaching purpose, certificate or asymmetric key is required. Without it, you can not restore or attach your database on another server.
»»