Post reply

DTS and data types

  • December 11, 2002 at 10:56 am

    #105389

    Good afternoon,

    I have a DTS datapump that takes a Boolean field from a Progress database table into a SQL Server 7 Table. The field in Progress is populated by "TRUE" "FALSE". I don't know what kind of Progress datatype it is. It is pumped into a SQL bit field as "0" "1". It does not cause a problem.

    Now, I have written some VBScript to get a little more selective of the rows I want to bring over by opening a recordset with a SQL statement through Progress ODBC. I then insert the rows into my SQL table with a simple INSERT statement from the same script. In this context the Progress Boolean field causes an error:

    "Error converting Varchar to bit. Process has stopped."

    Why does it work in the pump context and not in the script? Is it to do with bulk insert? How can I convert TRUE, FALSE to 0, 1 in my script?

    As always thank for any help!

    Jonathan

  • December 11, 2002 at 11:11 am

    #444125

    VB is receiving "TRUE" and "FALSE" as literals. You will need to convert this to a 1 or a 0 in your script.

    You can also try CBln(column). Or is it CBoolean()?

    Edited by - don1941 on 12/11/2002 11:11:55 AM

  • December 11, 2002 at 1:40 pm

    #444146

    Thanks Don,

    So this is really a VBScript issue not SQL server?? That would explain why it works in the DTS pump environment.

    Thanks again

  • December 13, 2002 at 9:45 am

    #444360

    OK I'm still hung up on this...

    An ADO recordset is opened from VBScript with a SQL query through ODBC to a Progress database. One of the fields contains only TRUE or FALSE. I loop through the recordset inserting each record into a local SQL 7 table. I get a SQL ODBC error "Problem converting varchar to bit datatype" . The field that is to receive the Progress T/F is of bit type.

    VBScript assigns the T/F field as Boolean. Its SQL that is calling it varchar. None of the VBSript convert functions seem to help and if I use CONVERT(bit,T/F) in the SQL statement I get the same error as above.

    Do I need to actually change the Progress T/F to -1/0 with an if-then statement in the script before the field is passed to SQL Server or is there a quicker way?

    Thanks for any help!

  • December 13, 2002 at 9:49 am

    #444362

    Hey Jonathan,

    I don't know anything about Progress, but why not go ahead and try an explicit conversion in the vb script code? Unless you are bringing in tens of millions of records, the time it takes will be of little consequence...

    Michael

    Michael Weiss


    Michael Weiss

  • December 13, 2002 at 9:53 am

    #444364

    PS -

    I am not sure if this makes a difference or not, but in SQL Server, a Bit data type can be either 0 or 1...BOL states that any non-zero value will be converted to 1. Rather than convert to -1 or 0, I would convert to 1 or 0...just a suggestion.

    Michael

    Michael Weiss


    Michael Weiss

  • December 13, 2002 at 11:26 am

    #444378

    Thanks Michael for both of your suggestions.

    I bit the bullet and did the brute force conversion. It does not slow things down much at all.

    Thanks again

    Jonathan

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply