Oracle Oracle.DataAccess.Client.OracleException ORA-01843: not a valid month

This post shows how to resolve the error “Oracle.DataAccess.Client.OracleException ORA-01843: not a valid month” when using C# to insert or update the data with date. In my below statement I have SS_COMPLETED is a date column. I want to update the current date to the column so I tried.

string strSql = “UPDATE BE_SSIS_FLAG SET SS_FLAG = 1, SS_COMPLETED = ‘” + DateTime.Now.ToString(“dd/MM/yyyy hh:mm:ss”) +“‘”

I got the error below.

Oracle.DataAccess.Client.OracleException

HResult=0x80004005

Message=ORA-01843: not a valid month

Source=Oracle Data Provider for .NET

StackTrace:

at Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure, Boolean bCheck)

To correctly update the current date time to the datetime column replace ‘” + DateTime.Now.ToString(“dd/MM/yyyy hh:mm:ss”) +“‘ with CURRENT_DATE”.

string strSql = “UPDATE BE_SSIS_FLAG SET SS_FLAG = 1, SS_COMPLETED = CURRENT_DATE”;

CREATE FILE encountered operating system error 5(Access is denied.)

This post is showing you how to resolve the following error. This error has happened to me when I was trying to Attach a database.

CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file ‘C:\database\xxxxxxStaging.mdf’. (Microsoft SQL Server, Error: 5123)

You go to Windows Folder Access Control List the n you see

But if you go down to the file itself. You see this.

When you click Advanced. Then you see the Owner is gone.

This happened usually because you reinstall your operating system. Give it back an Owner then it will work again.

SQL Server find a column name in databases

The post gives a common useful SQL statement that finding a column name in databases. I always looking for it again and again, so I decided on to put this statement into the blog. The example below is to find a column with characters like “move_kin”.

SELECT c.name AS ColName, t.name AS TableName FROM
sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id WHERE c.name LIKE
‘move_kin%’

Find Long Running SQL Server Queries Using SQL Profiler

This post shows you how to achieve the steps in this article https://www.mssqltips.com/sqlservertip/4401/find-long-running-sql-server-queries-using-profiler/. What this video is doing is find the sql queries that are running longer than 10 seconds.

We choose the event SP_StartCompleted and SP_Completed.

We filter by the duration 10000, 10 second.

The video is here https://youtu.be/rVmLSKmC4l4.

SQL Server 6.0 documentation

This is a short SQL Server 6.0 documentation I found in Internet so I just keep a copy and share it.

https://1drv.ms/b/s!Aje-DdIVpfjSgiht9NahsmhAldEq?e=LIIQEk

70-229 SQL2000 Design

This is a book that I use for my MCSD back then. Should be legal to share it today.

https://1drv.ms/u/s!Aje-DdIVpfjSgiWHJSVTi67I3kd1?e=n346V7

Excel cell data type

This post shows you a workaround to handle Excel data type. The Excel empty cell data type does not belong to any common type such as string, integer, or datetime. The data type is actually called System.RuntimeType.

So if you put either a express like if (oRng.Value == -2146826246) or if (oRng.Value == “”), both of these will fail at some point of time. The only way to handle this is use the try…catch statement. In the scenario below as I know more of the cell is going to be number so I am using if (oRng.Value == -2146826246). The working code snippet for me as below.

    Object Opt = Missing.Value;

    Microsoft.Office.Interop.Excel.Application app = new Application();

    Microsoft.Office.Interop.Excel.Workbook book;

    Microsoft.Office.Interop.Excel.Worksheet xlsSheet;

    Microsoft.Office.Interop.Excel.Range oRng;

    book = app.Workbooks.Open(strFileName, Opt, Opt, Opt, Opt, Opt, Opt, Opt, Opt, Opt, Opt, Opt, Opt, Opt, Opt);

    xlsSheet = (Microsoft.Office.Interop.Excel.Worksheet)book.Sheets[intSheet];

    Type type;

    oRng = (Microsoft.Office.Interop.Excel.Range)xlsSheet.Cells[2,21];

    for (int row = 1; row < RowCount; row++)

    {

    Try

    {

            object anything = oRng.Value;

            type = anything.GetType();

            if (oRng.Value == -2146826246)

            {

                oRng.Value = 0.0;

            }

            Console.WriteLine(type.GetType().Name);

            Console.WriteLine(anything.ToString());

            oRng = (Microsoft.Office.Interop.Excel.Range)xlsSheet.Cells[row + 1, 21];

        }

        catch(Exception ex)

        {

            Console.WriteLine(ex.Message);

        }

    }