SQL SERVER – Fix : Error : Msg 9803, Level 16. Invalid data for type “numeric” – Data Type Mapping for Oracle Publishers

My present article talks about an error that you will encounter when connecting to Oracle database using OPENQUERY.

The error that eventuates is as follows:

Msg 9803, Level 16, State 1, Line 1
Invalid data for type “numeric”.


As far as I can discern, the above error occurs due to numeric precision or numeric definition mismatch. The number field of SQL Server does not appropriately match with the number field of Oracle. In fact, apart from number field there are several other data types that do not match.

I am including the following sample query having NumberCol that is Integer field of SQL Server and it needs to be converted To_Char to match up. NumberCol can be matched to Numeric in Oracle as well.

FROM OPENQUERY (YourConnectionMethod, 'SELECT TO_CHAR(NumberCol) AS NumberCol FROM RemoteTbl');

Do read the article of MSDN to have a sound knowledge about Data Type Mapping for Oracle Publishers.

Please drop a line to me and let me have your doubts and questions. Your suggestions are always welcome!

Reference : Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – 2008 – Activity Monitor is Empty – Fix Activity Monitor for All Users

This article is outcome of the technical discussion of activity monitor and its behavior with my friend and SQL Expert Tejas Shah. Tejas told me that he does not like to re-write content from MSDN but rather prefer to write real life scenarios, as that prepares him to become better SQL Expert. While discussing about Activity Monitor he informed that activity monitor throws an error when there is permissions issue. He has even blogged about how to give permissions to user to launch activity monitor on his blog . Tejas asked me to write on the same subject for SQL Server 2008. Here is the article covering the discussion I had with Tejas.

I have user called ‘ActivityUser’ when turned on Activity Monitor (while logging in with user ActivityUser’) it does not have show anything in Activity Monitor.

av1 s SQL SERVER   2008   Activity Monitor is Empty   Fix Activity Monitor for All Users

The issue here is permissions issue. If user ActivityUser is given all the necessary permission it will start showing up data in Activity Monitor. Activity Monitor is new tool in SQL Server which displays activity in five sections. 1) Overview, 2) Processes, 3) Resources Waits, 4) Data File I/O, 5) Recent Expensive Queries. It is one of the new and very useful tool introduced by SQL Server.

Activity Monitor captures all the information at server level. For the same reason we need to give “View Server State” permission to user name to view data of Activity Monitor.We can give permission either using T-SQL or using SSMS.

T-SQL to give permission to user to view Activity Monitor:

av2 SQL SERVER   2008   Activity Monitor is Empty   Fix Activity Monitor for All Users

SSMS to give permission to user to view Activity Monitor:

av3 SQL SERVER   2008   Activity Monitor is Empty   Fix Activity Monitor for All Users

Once permissions is given to user, it displays the data in Activity Monitor. Click on images to enlarge the images.

av4 s SQL SERVER   2008   Activity Monitor is Empty   Fix Activity Monitor for All Users

Additionally, note that if you are user belonging to sysadmin role, you can always see all the data in Activity Monitor without additional permissions.

Reference : Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Find Current Location of Data and Log File of All the Database

As I am doing lots of experiments on my SQL Server test box, I sometime gets too many files in SQL Server data installation folder – the place where I have all the .mdf and .ldf files are stored. I often go to that folder and clean up all unnecessary files I have left there taking up my hard drive space. I run following query to find out which .mdf and .ldf files are used and delete all other files. If your SQL Server is up and running OS will not let you delete .mdf and .ldf files any way giving you error that file already in use. This list also helps sometime to do documentation of which files are in being used by which database.

SELECT name, physical_name AS current_file_location
FROM sys.master_files

Following is the output of files used by my SQL Server instance.

mastefiles SQL SERVER   Find Current Location of Data and Log File of All the Database

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Reasons to Backup Master Database – Why Should Master Database Backedup

The most interesting thing about writing blog at SQLAuthority.com is follow up question. Just a day before I wrote article about SQL SERVER – Restore Master Database – An Easy Solution, right following it, I received email from user requesting reason for importance of backing up master database.

Master database contains all the system level information of server. Information about all the login account, system configurations and information required to access all the other database are stored in master database. If master database is damaged, it will be difficult to use any other database in SQL Server and that makes it most important database of the SQL Server.

Let us understand the important of the master database using an example. We will take example of SQL Server DBA and follow his timeline. Make sure to understand it correctly, as I have small question at the end of the timeline.

9:00 AM – DBA takes backup of the master database.
10:00 AM – DBA creates new Database named AfterMaster.
11:00 AM – DBA restores the master database backup taken at 9:00 AM.
12:00 PM – I have following two questions for the DBA :

Question 1) What will be the state of the database AfterMaster? If AfterMaster database will be in active state after restoring master database?
Question 2) What should be the next step after restoring master database?

Let us understand the answer of question.

Answer 1) Once master database is restored it will have no record of AfterMaster database in its system database and it will not recognize it.
Answer 2) If master database is restored from backup all the operation which are done after last master database backup should be repeated in order to bring SQL Server in the current operational state. In our case, the database files (ldf and mdf) of AfterMaster database will still exists on server. They should be reattached to the server. You can search about how to attach mdf and ldf file at Search@SQLAuthority.com.

It is clear from our example that master database contains user login, files, filegroups and server wide settings.

In summary, it is extremely important to take backup of the master database.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Restore Master Database – An Easy Solution

Today we will go over two step easy method to restore ‘master’ database. It is really unusal to have need of restoring the master database. In very rare situation this need should arises. It is important to have full backup of master database, without full backup file of master database it can not be restored.

It is necessary to start SQL Server in single user mode before master database can be restored. It is very easy to start SQL Server server in single user mode. Follow the tutorial SQL SERVER – Start SQL Server Instance in Single User Mode.

Once SQL Server instance is running in single user mode, immediately connect it using sqlcmd and run following command to restore the master database.

RESTORE DATABASE master FROM DISK = 'C:\BackupLocation\master.back' WITH REPLACE;

restoremaster SQL SERVER   Restore Master Database   An Easy Solution

I have tested it couple of times and it has worked fine for me. If you encounter any error please leave a comment and I will do my best to solve it.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Start SQL Server Instance in Single User Mode

There are certain situation when user wants to start SQL Server Engine in “single user” mode from the start up.

To start SQL Server in single user mode is very simple procedure as displayed below.

Go to SQL Server Configuration Manager and click on  SQL Server 2005 Services. Click on desired SQL Server instance and right click go to properties. On the Advance table enter param ‘-m;‘ before existing params in Startup Parameters box.

Make sure that you entered semi-comma after -m. Once that is completed, restart SQL Server services to take this in effect. Once this is done, now you will be only able to connect SQL Server using sqlcmd.

singleuser SQL SERVER   Start SQL Server Instance in Single User Mode

Make sure to remove newly added params after required work is completed to restart it in multi user mode.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Shrinking NDF and MDF Files – A Safe Operation

Just a day ago I have received following email from Siddhi and I found it interesting so I am sharing with all of you.

Hello Pinal,

I have seen many blogs from you on SQL server and i have always found them useful and easy to understand. Thanks for all the information you provide.

I have one query about shrinking NDF and MDF files.

Can we shrink NDF and MDF files?? If you do so is there any data loss?

I have been shrinking the .LDF files every now and then but I am not too sure about NDF and MDF files.

Can you please answer my query.

Waiting for your early response.


Shrinking MDF and NDF file is possible and there is no chance of data loss.

It is not always advisable to shrink those file as those files are usually growing. There are cases when one database is separated in multiple database of any large table is dropped from database MDF and NDF can contain large empty space. This is the time they should be shrank. Shrinking database can be many hours process but it there are very slim chances of data lose.
Following is the script to shrink whole database.

Following is the script to shrink single file.
DBCC SHRINKFILE (logicalLogFileName)

To find logicalLogFileName following command has to be ran.
USE dbName
EXEC sp_helpfile

Let us understand this using database AdventureWorks.
/* Shrink Whole AdventureWorks Database */
/* Get the Logical File Name */
USE AdventureWorks
EXEC sp_helpfile
/* Shrink MDF File of AdventureWorks Database */
DBCC SHRINKFILE (AdventureWorks_Data)
Following image of the same process show when whole process is done there will be resultset with information about the new states of the database files.
shrinkdb SQL SERVER   Shrinking NDF and MDF Files   A Safe Operation

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQLAuthority News – CWE/SANS TOP 25 Most Dangerous Programming Errors

I just came across very interesting article from SANS Institute. Experts from more than 30 US and international cyber security organizations have released list of 25 most dangerous programming errors and their resolution. It may be possible that many of the programmers may not understand what this errors are and how to implement their solution. As said this are 25 most dangerous errors and all the developers should atleast know what they are so they do not are prevented from origin. Here are four major advantages listed by SANS.

  • Software buyers will be able to buy much safer software.
  • Programmers will have tools that consistently measure the security of the software they are writing.
  • Colleges will be able to teach secure coding more confidently.
  • Employers will be able to ensure they have programmers who can write more secure code.

Following is the list of 25 most dangerous programming errors.

CATEGORY: Insecure Interaction Between Components

CWE-20: Improper Input Validation
CWE-116: Improper Encoding or Escaping of Output
CWE-89: Failure to Preserve SQL Query Structure (aka ‘SQL Injection’)
CWE-79: Failure to Preserve Web Page Structure (aka ‘Cross-site Scripting’)
CWE-78: Failure to Preserve OS Command Structure (aka ‘OS Command Injection’)
CWE-319: Cleartext Transmission of Sensitive Information
CWE-352: Cross-Site Request Forgery (CSRF)
CWE-362: Race Condition
CWE-209: Error Message Information Leak

CATEGORY: Risky Resource Management

CWE-119: Failure to Constrain Operations within the Bounds of a Memory Buffer
CWE-642: External Control of Critical State Data
CWE-73: External Control of File Name or Path
CWE-426: Untrusted Search Path
CWE-94: Failure to Control Generation of Code (aka ‘Code Injection’)
CWE-494: Download of Code Without Integrity Check
CWE-404: Improper Resource Shutdown or Release
CWE-665: Improper Initialization
CWE-682: Incorrect Calculation

CATEGORY: Porous Defenses

CWE-285: Improper Access Control (Authorization)
CWE-327: Use of a Broken or Risky Cryptographic Algorithm
CWE-259: Hard-Coded Password
CWE-732: Insecure Permission Assignment for Critical Resource
CWE-330: Use of Insufficiently Random Values
CWE-250: Execution with Unnecessary Privileges
CWE-602: Client-Side Enforcement of Server-Side Security

Please go to SANS site and click on each error to find their resolution.

Reference : Pinal Dave (http://blog.SQLAuthority.com), list of 25 most dangerous programming errors

SQLAuthority News – Security White Papers

Microsoft Dynamics AX 2009 White Paper: Configuring Kerberos Authentication with Role Centers
This document describes how to configure Kerberos authentication with Enterprise Portal and Role Centers. Kerberos authentication is required to display reports created using Microsoft SQL Server Reporting Services and Microsoft SQL Server Analysis Services on Role Center pages.

Microsoft Dynamics AX 2009 White Paper: Configuring Enterprise Portal and Role Centers with SQL Reporting

This document contains checklists and information to help administrators set up and configure Microsoft Dynamics AX 2009 Enterprise Portal and Role Centers with Microsoft SQL Server® Reporting Services® and Microsoft SQL Server Analysis Services.

Reference : Pinal Dave (http://blog.SQLAuthority.com)