Sunday, August 26, 2012

PL/SQL Developer : Initialization error Could not locate OCI dll

When try to run PL/SQL Developer to connect to oracle Database the below error is shown 

---------------------------
PL/SQL Developer
---------------------------
Initialization error
Could not locate OCI dll

OracleHomeKey: SOFTWARE\ORACLE\KEY_OraClient11g_home
OracleHomeDir: D:\app\aneesh.thilakan\product\11.2.0\client_1
---------------------------
OK  
---------------------------



Solution:
1. Copy oci.dll from folder c:\app\user\product\11.2.0\client_1

2. Paste it in the folder  c:\app\user\product\11.2.0\client_1\bin

You had done!

Now open your PL/SQL Developer.

Tuesday, August 14, 2012

System.Data.SqlClient.SqlException: Generating user instances in SQL Server is disabled. Use sp_configure 'user instances enabled' to generate user instances

I. When I try to run ASP.net application with SQL Server database an error shown as below:


Server Error in '/' Application.
--------------------------------------------------------------------------------
Generating user instances in SQL Server is disabled. Use sp_configure 'user instances enabled' to generate user instances.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Generating user instances in SQL Server is disabled. Use sp_configure 'user instances enabled' to generate user instances.

Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.  

II. When I try to connect to a SQL Server database from visual studio server explorer an error shown as below:  
---------------------------
Microsoft Visual Studio
---------------------------
Generating user instances in SQL Server is disabled. Use sp_configure 'user instances enabled' to generate user instances.
---------------------------
OK
---------------------------

Solution
This error is due to SQL Server Express has disabled user instance genereation. Simply apply the below steps to enable user instance generation.

1. Open SQL Server 2005 Management Studio Express.

2. Connect to the default instance of that server (eg: Computer Name\SQLEXPRESS)

3. Open a new query window and use master database

4. Execute sp_configure as below:
exec sp_configure 'user instances enabled', 1

--Configuration option 'user instances enabled' changed from 0 to 1. Run the RECONFIGURE statement to install.

5. Execute RECONFIGURE as:
RECONFIGURE

--Command(s) completed successfully.

6. Go to services and restart the SQL Server Instance currently running.



Note: Sample connection string format in web.config should be as below :

connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=

|DataDirectory|\DBName.mdf;Integrated Security=True;User Instance=True;Initial Catalog=DBName;"

User Instances
User instance is a new feature supported by Microsoft SQL Server 2005 Express Edition, which is only available when using the .NET Framework Data Provider for SQL Server (SqlClient). A user instance is a separate instance of the SQL Server Express Database Engine that is generated by a parent instance. User instances allow users who are not administrators on their local computers to attach and connect to SQL Server Express databases. To generate user instances, a parent instance of SQL Server Express must be running. User instances are enabled by default when SQL Server Express is installed, and they can be explicitly enabled or disabled by a system administrator executing the sp_configure system stored procedure on the parent instance.

-- Enable user instances.
sp_configure 'user instances enabled', '1'

-- Disable user instances.
sp_configure 'user instances enabled', '0'

Sunday, August 12, 2012

Visual Studio Server Explorer Oracle Procedure Run Debug Error

When try to debug an oracle procedure/package  from visual studio server explorer an error shows to inform the user that he has insufficient privilege for debugging as below.

Visual Studio Server Explorer Oracle Procedure Run Debug Error
---------------------------
Microsoft Visual Studio
---------------------------
Server Explorer
An error occurred while executing the procedure/function: ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_DEBUG_JDWP", line 68
ORA-06512: at line 1
---------------------------
OK
---------------------------

Solution
The following privileges are required for debugger:

1. DEBUG CONNECT SESSION
Connect the current session to a debugger that uses the Java Debug Wire Protocol (JDWP).

2.DEBUG ANY PROCEDURE
Debug all PL/SQL and Java code in any database object. Granting this privilege is equivalent to granting the DEBUG object privilege on all applicable objects in the database.

Syntax
GRANT DEBUG CONNECT SESSION TO user_name ;
GRANT DEBUG ANY PROCEDURE TO user_name;