Troubleshooting & diagnosing TNS SQL*Net connectivity errors

There are a variety of common network connectivity error messages, and most DBA's have seen TNS error messages these at sometime in their careers.  Here is just a small sample of possible TNS network connectivity-related errors:

• TNS-12545: Connect failed because target host or object does not exist
• ORA-12154: TNS: Could not resolve service name
• ORA-12157: TNS Internal network communication error 

Steps to troubleshoot Oracle connectivity:

To troubleshoot & diagnose Oracle connectivity problems, I like to start at the simplest, outermost level, and follow this checklist:

1. Check with "ping" using the IP address ("ping 1.1.4.3")
2. Check with "ping" using DNS name (e.g. "tnsping database")
3. Try telnet to the IP on port 1521 (telnet 1.2.3.4 1521)
4. Check with "tnsping" using TNS service name
5. Invoke SQL*Plus from the OS command line "sqlplus abcd@database".  If this fails, check to ensure that your listener has the database service defined.
6. Sign-on to SQL*Plus and connect with the TNS name
7. Within SQL*Plus, try a select * from table
   
   
   
   
   Above mentioned steps are the first sign of checking the connection error. My server was able to clear all the above seven steps. Still I am not able to connect to database via my application. Then I changed my sqlnet.ora. There were multiple entries, to which I reduced them to a single line
   
   
   names.directory_path= (tnsnames)
   
   
   Then on investigation I found out, the code that was causing trouble is:
   
   
   SQLNET.AUTHENTICATION_SERVICES = (NTS)

   names.default_domain = world
   
   
   So even if I commented these two lines in my original sqlnet.ora, it worked. Hope this works for you as well.
   
   
   
   
   
   
   
   
   
   

ALL_TABLES versus ALL_ALL_TABLES

What's the difference between ALL_TABLES and ALL_ALL_TABLES

Answer: 
1) Both views provide all tables to which the current user has access to but, in addition to the tables returned by ALL_TABLES, the ALL_ALL_TABLES will also return all object tables (system generated or not) accessible by the current user.

SQL> select *
  2    from dictionary
  3   where table_name in ('TABS','ALL_TABLES','ALL_ALL_TABLES')
  4  /

TABLE_NAME           COMMENTS
-------------------- ------------------------------------------------------------------------------------------------
ALL_ALL_TABLES       Description of all object and relational tables accessible to the user
ALL_TABLES           Description of relational tables accessible to the user
TABS                 Synonym for USER_TABLES

2) The following 3 columns that appear only in ALL_ALL_TABLES, but not in ALL_TABLES 
give you details about the object type on which the object table was created and the object identifier type used: 

OBJECT_ID_TYPE 
TABLE_TYPE_OWNER 
TABLE_TYPE 

If you want to see the difference only between the two views, you can use a select like
the following: 

SELECT * FROM ALL_ALL_TABLES 
WHERE TABLE_TYPE IS NOT NULL 
/ 

Even if you don't have object tables in your schemas, you will probably still see some of the object tables used by different Oracle features installed, like XDB, a.s.o. 

3) ALL_ALL_TABLES includes object tables as well as relational tables.


Also, this may be an interview question (e.g. how can you get all tables you have access to?) and you may leave a good impression if you respond with another question: "Do you also want object tables to be included?". :)

How to Debug or Test your Windows Service Without Installing it

When you develop a Windows Service and want to run or debug it, you get a message box with this message:

Cannot start service from the command line or a debugger.
A Windows Service must first be installed (using installutil.exe)
and then started with the ServerExplorer, Windows Services 
Administrative tool or the NET START command.

So for testing you have to first install it on your computer, but it is a long process and also boring because every time you make changes, you have to reinstall your service and test it again.

There are many ways it can be done, but I opted this way out becuase of its simplicity.....

For debugging or testing your service without installing it, make changes in Program.cs like this.

static class Program
{
    static void Main()
    {
        ServiceBase[] ServicesToRun;
        ServicesToRun = new ServiceBase[] 
 { 
      new MyService() 
 };
        ServiceBase.Run(ServicesToRun);
    }
}

Change it to:

static class Program
{
    static void Main()
    {
        #if(!DEBUG)
           ServiceBase[] ServicesToRun;
           ServicesToRun = new ServiceBase[] 
    { 
         new MyService() 
    };
           ServiceBase.Run(ServicesToRun);
         #else
           MyService myServ = new MyService();
           myServ.Process();
           // here Process is my Service function
           // that will run when my service onstart is call
           // you need to call your own method or function name here instead of Process();
         #endif
    }
}

After adding #if and #else to your main fuction, now when you press F5 or run your service, it will not show you the previous message and simply run, so attach a break point to your method which will be called by the service when it will start. With the use of this code, you can simply debug your service without installing it.

For this no need to add any extra using directive (like using System.Data or using System.IO) to your class file. It will simply as it is.

'installutil' is not recognized as an internal or external command, operable program or batch file.

You get this error when you type 'installutil.exe' in a command prompt.

Reason: .net folder path is not set in the 'PATH' variable because of which location of installutil.exe was not found.

Resolution:

a) Use the visual studio command prompt, it will work there
b) Set .NET folder path in system's 'PATH' variable . Then try it should get the path of 'installutil.exe' and you can use it directly in any command prompt.

c) Use Coding

It's is really easy to just add it to the service itself. Add a reference to System.Configuration.Install and then update your Main()-function in Program.cs like this.

static void Main(string[] args)
{
    if (Environment.UserInteractive)
    {
        string parameter = string.Concat(args);
        switch (parameter)
        {
            case "--install":
            ManagedInstallerClass.InstallHelper(new[] { Assembly.GetExecutingAssembly().Location });
            break;
            case "--uninstall":
            ManagedInstallerClass.InstallHelper(new[] { "/u", Assembly.GetExecutingAssembly().Location });
            break;
        }
    }
    else
    {
        ServiceBase[] servicesToRun = new ServiceBase[] 
                          { 
                              new ValidatorService() 
                          };
        ServiceBase.Run(servicesToRun

Then you can just call WindowsService1.exe with the --install argument and it will install the service and you can forget about InstallUtil.exe.

Error saying "The 'DisplayGroupTree' property cannot be set declaratively"

Public Property DisplayGroupTree As Boolean’ is obsolete.

In this version Crystal Reports can have a parameter panel. I now have to tell crystal reports what will be displayed in the left panel:

• the group tree
• the parameter panel
• nothing

The parameter is ToolPanelView. If you don’t want to show nothing in the left panel:

rptViewer.ToolPanelView = CrystalDecisions.Windows.Forms.ToolPanelViewType.None