Topic
9 replies Latest Post - ‏2012-12-03T07:27:40Z by SystemAdmin
SystemAdmin
SystemAdmin
1143 Posts
ACCEPTED ANSWER

Pinned topic Uploading file from informix to sqlserver through bcp

‏2012-05-16T15:07:19Z |
Hi All,

I have created a bcp shell script to upload some files to sql server database!
the script will first do ftp to retrive the files from some other informix server then it will upload the file to sqlserver database

It's retriving the file successfuly but while uploading it to sql server it gives the error message "Error = Microsofthttp://SQL Server Native Client 11.0String data, right truncation"

I have noticed that after I open the file in word pad and just save it without even doing any changes then it will allow uploading

I think while saving the file some things happening at windows side because i compared the original file with old file and found some difference in bytes (the new one is greater than old one)

Could you advise some solution

WBR
Ahmed
Updated on 2012-12-03T07:27:40Z at 2012-12-03T07:27:40Z by SystemAdmin
  • andreasl
    andreasl
    331 Posts
    ACCEPTED ANSWER

    Re: Uploading file from informix to sqlserver through bcp

    ‏2012-05-16T15:15:48Z  in response to SystemAdmin
    Hi Ahmed,

    so on your way to MS?

    Well, your problem sounds like it has to do with the different ways a line break is encoded under Unix and Windows (in Windows this takes two bytes while in Unix only one, so the difference in size should exactly be the number of rows in your file).

    unix2dos would be your friend if it exists on your Unix - if not, try the same on some Linux box.

    Much luck,
    Andreas
    • SystemAdmin
      SystemAdmin
      1143 Posts
      ACCEPTED ANSWER

      Re: Uploading file from informix to sqlserver through bcp

      ‏2012-05-16T16:03:21Z  in response to andreasl
      Hi Andreas,

      Yes dear i could not get informix net and now i'm taking the data directly from informix to sql server!

      unix2dos will not work in patch and shell scripts as i know!
      I need the file to be uploaded automaticly from informix to sqlserver without and manual interfere

      I have done every thing, only this problem i'm facing while uploading the file via bcp command

      Please advise!

      WBR
      Ahmed
      • SystemAdmin
        SystemAdmin
        1143 Posts
        ACCEPTED ANSWER

        Re: Uploading file from informix to sqlserver through bcp

        ‏2012-05-17T07:11:20Z  in response to SystemAdmin
        Hi Ahmed,
        you've mentioned you use the FTP to get the file from unix to windows box. The FTP protocol can use one of 2 transfer modes - binary and ascii. In binary mode the files are transferred 'as is' i.e. the target file is a binary copy of the source one. In ascii mode the line breaks are converted in case they are different. So when you move a text file from Unix to Windows in ascii mode, the '0xA' (unix line break) is converted to '0xA0xD' (windows line break) automatically.
        You do not mention which tool or API do you use in your script for the FTP transfer, but if it is the standard ftp client in Windows, you can automate it in this way:
        • prepare a text file 'test.ftp' containing the FTP commands like:
        user User1 password
        cd /unix_directory/with/unload_file
        ascii
        get unload_file
        bye

        • then run the ftp client as 'ftp -s:test.ftp unix.host.name'

        Cheers, Tomas
        • SystemAdmin
          SystemAdmin
          1143 Posts
          ACCEPTED ANSWER

          Re: Uploading file from informix to sqlserver through bcp

          ‏2012-05-17T07:55:08Z  in response to SystemAdmin
          Hi Tomas,

          You are absolutely right, i have already changed from bin to asci and it worked

          Cheers!
          Ahmed
        • SystemAdmin
          SystemAdmin
          1143 Posts
          ACCEPTED ANSWER

          Re: Uploading file from informix to sqlserver through bcp

          ‏2012-11-22T06:14:50Z  in response to SystemAdmin
          Hi Tomas,

          I hope you are doing well!
          I need your assistance in configuring a new tape drive for Compaq Proliant ML350.
          Currently we are using "hp SureStore DAT24" but it got damaged and we got a new tape which is "hp SureStore DAT72"

          The DAT72 doesnot work, i think it require some configuration!
          Could you please help me in that?!

          Appreciating your support!

          Regards,
          Ahmed
          UAE, Dubai
          • SystemAdmin
            SystemAdmin
            1143 Posts
            ACCEPTED ANSWER

            Re: Uploading file from informix to sqlserver through bcp

            ‏2012-11-22T12:33:25Z  in response to SystemAdmin
            Hi Ahmed,
            this forum is intended to discuss the problems related to IBM Informix products family.
            Unfortunately i'm not familiar with HP hardware administration. If the new tape drive doesn't work in your system, you should consult the OS documentation, the documentation delivered with the tape drive and perhaps contact the HP support.
            If I've misunderstood you question and your tape works as expected at the OS level but you have problems with getting it working with IBM Informix backup utilities (ontape,onload/onunload), please describe the symptoms of the problem (plus the exact version of IBM Informix Server and the operating system you're using).

            Kind regards, Tomas
            • SystemAdmin
              SystemAdmin
              1143 Posts
              ACCEPTED ANSWER

              Re: Uploading file from informix to sqlserver through bcp

              ‏2012-11-24T06:58:17Z  in response to SystemAdmin
              Hi Tomas,

              Thanks for your kind reply, in fact this is something related to Unix operating system, in case of installing a new tape drive i believe we should configure it through unix!
              We are using Unix OS and the informix version 5.05 is installed on it.
              For backup we are using tape, the current tape is HP DAT24 and the new one which i need to install is DAT72 so i think that for new tape we have to change the configuration in unix, but i don't know exactly how to do it.

              Please try to help out!

              Thanks a lot
              Ahmed
              • SystemAdmin
                SystemAdmin
                1143 Posts
                ACCEPTED ANSWER

                Re: Uploading file from informix to sqlserver through bcp

                ‏2012-12-01T10:00:00Z  in response to SystemAdmin
                Hi Tomas,

                Did you get any chance to look into my issue?

                Regards,
                Ahmed
                • SystemAdmin
                  SystemAdmin
                  1143 Posts
                  ACCEPTED ANSWER

                  Re: Uploading file from informix to sqlserver through bcp

                  ‏2012-12-03T07:27:40Z  in response to SystemAdmin
                  Hi Ahmed,
                  i'm sorry, but as I told you previously, if you have problem with HW or its configuration in operating system, you need to contact the HW provider. You didn't described the symptoms of your problem, but even if you did, i don't have any HP-UX box with a tape drive to investigate on.
                  So again, please contact HP support to get help with the tape drive configuration in OS. Once it will be working properly in OS and if there'll be problems to use it with Informix, we can look at that.
                  Regards,
                  Tomas