Message=ORA-01843: not a valid month

This short blog is to resolve .NET C# insert a record to Oracle table with date format

You will very likely get the error Message=ORA-01843: not a valid month when you insert a record to Oracle table with date format using .NET C#.

You might get a link like https://www.tekstream.com/resources/ora-01843-not-a-valid-month/ and telling you to do like.

SELECT * FROM MYTABLE WHERE MYTABLE.DATEIN = TO_DATE(15/07/99, ‘DD/MM/YY’);

So I will do this to my statement

INSERT INTO BILLS_OF_LADING (NBR, LINE_ID, CREATED, CREATOR) VALUES (‘TESTIN1’, ‘MAE’, TO_DATE(2001/03/19, ‘YYYY/MM/DD’), ‘HILMY’)

Unfortunately the error is still there because you also need the single quotation mark for the date itself

INSERT INTO BILLS_OF_LADING (NBR, LINE_ID, CREATED, CREATOR) VALUES (‘TESTIN1’, ‘MAE’, TO_DATE(‘2001/03/19’, ‘YYYY/MM/DD’), ‘HILMY’)

Advertisements
Posted in .Net, Community, Oracle | Tagged , , | Leave a comment

Visual Studio 2015 an error occurred while importing settings for ‘all languages’

This short blog shows you how to resolve the error “error occurred while importing settings for ‘all languages’” in Visual Studio 2015.

When you login to certain users on your computer beside Administrator then you might not see any project template.

You go to Tools -> Import and Export Setting… and you get the error below.

an error occurred while importing settings for ‘all languages’ code 5455

Start cmd with Run as administrator

Run the following command.

C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common\IDE\devenv /resetuserdata

Posted in .Net, Community, Computers and Internet | Tagged , , , | Leave a comment

Microsoft Kerberos Configuration Manager for SQL Server

Why Kerberos?

Kerberos authentication provides a highly secure method to authenticate client and server entities (security principals) on a network. To use Kerberos authentication with SQL Server, a Service Principal Name (SPN) must be registered with Active Directory, which plays the role of the Key Distribution Center in a Windows domain. In addition, many customers also enable delegation for multi-tier applications using SQL Server. In such a setup, it may be difficult to troubleshoot the connectivity problems with SQL Server when Kerberos authentication fails.

Here are some additional reading materials for your reference.

Why use this tool?

The Kerberos Configuration Manager for SQL Server is a diagnostic tool that helps troubleshoot Kerberos related connectivity issues with SQL Server, SQL Server Reporting Services, and SQL Server Analysis Services. It can perform the following functions:

  • Gather information on OS and Microsoft SQL Server instances installed on a server.
  • Report on all SPN and delegation configurations and Always On Availability Group Listeners installed on a server.
  • Identify potential problems in SPNs and delegations.
  • Fix potential SPN problems.

This release (v4.0) adds support for Always On Availability Group Listeners.

To download the tool https://www.microsoft.com/en-us/download/details.aspx?id=39046.

Reference:

https://blogs.msdn.microsoft.com/sqlreleaseservices/released-microsoft-kerberos-configuration-manager-for-sql-server-4-0/

Posted in .Net, Community, Computers and Internet | Tagged , | Leave a comment

SSMS 17.3 New Import Flat File Wizard

This short writing lets you know the New Import Flat File Wizard in SSMS 17.3.
Import Flat File is one of the most common features to use for any kind of developer. The new import flat file wizard will get you default text type is nvarchar than varchar. That is the most likely part for me.

If you are lazy to watch this video, https://channel9.msdn.com/Shows/Data-Exposed/Introducing-the-new-Import-Flat-File-Wizard-in-SSMS-173?term=ssms%20flat%20file. Then you can see the Import Flat File from the following screen on top of Import Data. I have tried it and it is working well for now. But Ctrl-Z does not work in Modify Columns page.

Posted in .Net, Community, Computers and Internet | Tagged , , , | Leave a comment

SQL Server Create parent child XML using Select

I have a parent and child table in SQL Server database.

The parent table name ATAHeader

SELECT Header.txnDateTime, Header.[fileName] FROM ATAHeader Header

The child table name ATA

SELECT ATAInformation.[MCKey], ATAInformation.[MCValue], ATAInformation.SCN FROM [dbo].[ATA] ATAInformation

We would like to produce the XML hierarchy like the one below.

<Header>

    <txnDateTime>2017-09-25T13:35:41</txnDateTime>

    <fileName>REQ_MYTPP-J33-S_JATA_20170925133541.xml</fileName>

     <ATAInformation>

         <MCKey></MCKey>

         <MCValue></MCValue>

         <SCN>152292</SCN>

    </ATAInformation>

</Header>

You will need to follow the SQL statement below to produce the XML.

SELECT Header.txnDateTime, Header.[fileName]ATAInformation.[MCKey], ATAInformation.[MCValue], ATAInformation.SCN FROM [dbo].[ATAHeader] Header inner
join [dbo].[ATA] ATAInformation 
on Header.fileName = ATAInformation.fileName FOR
XML
AUTO, ELEMENTS

Posted in .Net, Community, Computers and Internet | Tagged , , , | Leave a comment

Disable Hexadecimal value in Visual Studio

This short writing is for the Visual Studio displaying Hexadecimal during debugging.

To turn it off just right the variable in the Locals Windows and uncheck the Hexadecimal Display.

Posted in .Net, Cloud, Community, Computers and Internet | Tagged , , | Leave a comment

Convert Excel Sheet to csv file for SSIS

You can use the code below to concert Excel to csv. You need to add the Microsoft.Office.Interop.Excel. The reason I am doing this is, I have been facing many issues to read the Excel from SSIS to the database for example, there is a data in Excel cell but appears to be null in database. So I move the Excel data to csv then use the csv file as the source for the database.

class  Program

{

static  void Main(string[] args)

  {

       ReadExcelFile.ReadExcel(@”C:\temp\QC\input\QC_BookingData.xlsx”, 62, 25, “,”int.Parse(args[0]));

  } 

}

using System;

using Microsoft.Office.Interop.Excel;

using System.Collections.Generic;

using System.IO;

using System.Reflection;

using System.Runtime.InteropServices;

namespace ConAppExcelDelimiter

{
class  ReadExcelFile

      { 
public static  void ReadExcel(String strFileName, int RowCount, int ColCount, string strDelimiter, int intSheet)

              StreamWriter sw = new StreamWriter(@”c:\temp\QC\input\Test” + intSheet + “.csv”);

              String strCell = “”;
int col;
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];

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


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

           {

                    strCell = “”;
for (col = 0; col < ColCount; col++)

                    {

                        oRng = (Microsoft.Office.Interop.Excel.Range)xlsSheet.Cells[row + 1, col + 1];
 if (col < ColCount – 1)

                         {

                                strCell = strCell + oRng.Text.ToString() + strDelimiter;

                        }
else

                       { 

                             strCell = strCell + oRng.Text.ToString();

                       }

             }

              sw.WriteLine(strCell);

     }

       Console.WriteLine(strCell);

       sw.Close();

       book.Close(false, false, Missing.Value);

      app.Quit();

      book = null;

      app = null;

       }

    }

}

Posted in .Net, Cloud, Community, Computers and Internet, Microsoft Office | Tagged , , | Leave a comment