7 Replies Latest reply on Jul 16, 2021 2:15 AM by Alexander Hauthaler

    unable to write to datetime columns on sql server

    Alexander Hauthaler Active Member

      WRT_8229 Database errors occurred: FnName: Execute -- [Informatica][ODBC SQL Server Wire Protocol driver]Invalid character value. Error in parameter 12. FnName: Execute -- [Informatica][ODBC SQL Server Wire Protocol driver]String data, right truncated. Error in parameter 4. FnName: Execute -- [Microsoft][ODBC Driver Manager] Function sequence error FnName: Execute -- [Informatica][ODBC SQL Server Wire Protocol driver]Invalid character value. Error in parameter 12. FnName: Execute -- [Informatica][ODBC SQL Server Wire Protocol driver]String data, right truncated. Error in parameter 4.

       

      I want to write some datetime values to a table in Azrue sql

      The column type in the mapping, in the source, in the target, is "DateTime"

       

      Informatica is not able to write the data, because of the above error.

       

      can anybody help with this?

       

        • 1. Re: unable to write to datetime columns on sql server
          Krishna Kumar Support Moderators

          Hi Alexander,

           

          Could you please configure the job to process few records (3-4 records) and run it in verbose mode, and share the verbose log for our analysis.

           

          Support

           

          Thanks,

          Krishna

          • 2. Re: unable to write to datetime columns on sql server
            Nico Heinze Guru

            There are several potential mistakes when it comes to SQL Server and DateTime handling.

            First the range of valid dates for a DateTime starts in year 1753, not earlier. Trying to pass a value of let's say the start of the Gregorian Calendar (Oct 15th, 1582) would fail.

            Second in many cases you have some string holding a date/time value, such as "07/30/2021". Let's assume your target definition has a VARCHAR attribute here, but the SQL Server table has a DateTime column. In this case it's just a game of luck whether SQL Server will interpret 07/30/2021 correctly or not. For example, with SQL Server set to some US locale this would work fine because the standard date format for the USA is MM/DD/YYYY. However, with a British locale this can't work because the standard date format for the UK is DD/MM/YYYY. Not to mention that many countries have yet differing formats, such as the standard German format DD.MM.YYYY.

             

            is either one the case for you?

             

            Regards,

            Nico

            • 3. Re: unable to write to datetime columns on sql server
              Jharana Patra Guru

              Hi Alexander ,

               

              As suggested above please run the job in verbose mode for few records and verify the date format.

              In case its truncated or not in expected format please try below to modify the date format and see.

              TO_DATE

               

              Eg: TO_DATE( DATE_PROMISED, 'MM/DD/YY' )

              • 4. Re: unable to write to datetime columns on sql server
                Alexander Hauthaler Active Member

                Thanks to all for your help.

                 

                Meanwhile i created a ticket at informatica support.

                Source database = SQL Server 16, target database is Azure SQL.

                both source and Target tables have DATETIME datatype, so there should be no converting needed I think.

                 

                I cannot run in verbose mode, as this would create a file several GB in size.

                but here is how the dates look like

                 

                TRANSF_1_2_1> CMN_1053 [2021-07-07 14:05:11.264] EXP_UPDATES: Send OUTPUT row to IUpdateStrategyNode10: Control=[None]

                EXP_UPDATES: Send OUTPUT row to IUpdateStrategyNode10: Rowdata: ( RowType=0(insert) Src Rowid=2580047 Targ Rowid=2580047

                  src_EndDate (src_legalWarrantyEndDate:Date:): "06/06/2024 00:00:00.000000000"

                  src_ExtensionEndDate (src_legalWarrantyExtensionEndDate:Date:): "06/06/2024 00:00:00.000000000"

                  src_StartDate (src_legalWarrantyStartDate:Date:): "06/06/2021 00:00:00.000000000"

                )

                 

                READER_1_2_1> BLKR_16019 [2021-07-07 14:05:13.935] Read [2669271] rows, read [0] error rows for source table [xxxxxxxxxxxxxxx] instance name [xxxxxxxxxxxxxx]

                 

                FnName: Execute -- [Informatica][ODBC SQL Server Wire Protocol driver]Invalid character value. Error in parameter 12.

                FnName: Execute -- [Informatica][ODBC SQL Server Wire Protocol driver]String data, right truncated. Error in parameter 1.

                FnName: Execute -- [Informatica][ODBC SQL Server Wire Protocol driver]String data, right truncated. Error in parameter 2.

                FnName: Execute -- [Informatica][ODBC SQL Server Wire Protocol driver]String data, right truncated. Error in parameter 3.

                FnName: Execute -- [Informatica][ODBC SQL Server Wire Protocol driver]String data, right truncated. Error in parameter 4.

                FnName: Execute -- [Informatica][ODBC SQL Server Wire Protocol driver]String data, right truncated. Error in parameter 10.

                FnName: Execute -- [Informatica][ODBC SQL Server Wire Protocol driver]String data, right truncated. Error in parameter 11.

                FnName: Execute -- [Microsoft][ODBC Driver Manager] Function sequence error

                FnName: Execute -- [Informatica][ODBC SQL Server Wire Protocol driver]Invalid character value. Error in parameter 12.

                FnName: Execute -- [Informatica][ODBC SQL Server Wire Protocol driver]String data, right truncated. Error in parameter 1.

                FnName: Execute -- [Informatica][ODBC SQL Server Wire Protocol driver]String data, right truncated. Error in parameter 2.

                FnName: Execute -- [Informatica][ODBC SQL Server Wire Protocol driver]String data, right truncated. Error in parameter 3.

                FnName: Execute -- [Informatica][ODBC SQL Server Wire Protocol driver]String data, right truncated. Error in parameter 4.

                FnName: Execute -- [Informatica][ODBC SQL Server Wire Protocol driver]String data, right truncated. Error in parameter 10.

                FnName: Execute -- [Informatica][ODBC SQL Server Wire Protocol driver]String data, right truncated. Error in parameter 11.

                WRITER_1_*_1> CMN_1761 [2021-07-07 14:05:14.209] Timestamp Event: [Wed Jul 07 14:05:14 2021]

                WRITER_1_*_1> WRT_8425 [2021-07-07 14:05:14.209] ERROR: Writer execution failed.

                WRITER_1_*_1> CMN_1761 [2021-07-07 14:05:14.209] Timestamp Event: [Wed Jul 07 14:05:14 2021]

                WRITER_1_*_1> WRT_8114 [2021-07-07 14:05:14.209]

                Row # [200] in bad file

                • 5. Re: unable to write to datetime columns on sql server
                  Nico Heinze Guru

                  Does this error occur to some records or all records?

                   

                  Furthermore, could it be that on the target side this "table" is indeed a view? With a structure which does not fit what PowerCenter is using as its target structure?

                   

                  I know this sounds stupid and I know that you are a VERY seasoned developer, so please don't take this as an insult: maybe one or more port links are "crossed" (or shifted up / down one position) when connecting them from the last transformation to the target definition. May that be the case here? You know, depending on the actual port contents this may seem to work for some records but not work for the rest.

                   

                  Regards,

                  Nico

                  • 6. Re: unable to write to datetime columns on sql server
                    Alexander Hauthaler Active Member

                    No offense detected, Nico, your input is always very welcome! Indeed this could have been the problem. After checking again and again, i tested the mapping in Test environment and it turns out it worked without errors.

                    I began to compare Secure agent settings.

                     

                    Found 2 Setting in Custom Configuration of Secure Agent, which are different from Test env.

                    EnableArrayUpdate     Yes
                    EnableArrayUpsert     Yes

                     

                    After setting these to no, the mappings ran fine.

                    No problems any more.

                    Another colleage of mine reported, his jobs had similar problems and are running fine now again.

                     

                    I think it might be some problem with monthly connector updates and the use of these settings.

                    I am currently in contact with support to confirm my assumption.

                    • 7. Re: unable to write to datetime columns on sql server
                      Alexander Hauthaler Active Member

                      Im still not sure if this was the failure causing part, but Support said:

                       

                      "Sometimes while loading the data into SQL Server and the properties are set to yes may cause issues. After you set the property to no, the job is working as expected."

                       

                      ...therefore... case closed.