Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2003
    Posts
    33

    Unanswered: valid query wont work as DTS task???

    i have a query which works fine when i run it from Query Analyzer.

    when i try to create a DTS script "Exectute SQL Task", then this SQL does not work within that task, and i dont know why:

    ALTER TABLE T_DESCRIP COLUMN EYE varchar(3) NOT NULL

    It throws and error that says:

    "Error Description: Deferred prepare could not be completed. Statement could not be prepared. Incorrect syntax near the keyword 'COLUMN'.

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Why would you want to put THIS into a DTS?

  3. #3
    Join Date
    Feb 2003
    Posts
    33
    im using DTS to make a schedule a copy of the database and after its finished i want to change one of the columns from a "char" type to a "varchar" type because the application that queries from that data is placing "padding" at the end of the results items because its a fixed length char field...

    in any case, whatever i am doing does it matter? does DTS limit the use of ALTER in this case?

  4. #4
    Join Date
    Jan 2004
    Location
    Colorado Springs
    Posts
    1
    I am not sure if the DTS will not allow this. However, have you thought of just adding an extra step to the scheduled job so that after your DTS completes then you can alter your table?




    Originally posted by mozkill
    im using DTS to make a schedule a copy of the database and after its finished i want to change one of the columns from a "char" type to a "varchar" type because the application that queries from that data is placing "padding" at the end of the results items because its a fixed length char field...

    in any case, whatever i am doing does it matter? does DTS limit the use of ALTER in this case?

  5. #5
    Join Date
    Feb 2003
    Posts
    33
    yeah, thats exactly what im doing... or at least trying to do...

    i am able to add indexes to my tables using this same method but for some reason the ALTER statement wont work. i suspect that there is a limitation there for some reason but the error message is vague...

    i havent had any trouble with any other SQL queries... only this one.

    (by the way, i realize i need to add indexes AFTER i alter the table)
    Last edited by mozkill; 01-23-04 at 16:48.

  6. #6
    Join Date
    Jan 2004
    Location
    Ottawa, Canada
    Posts
    53
    Why are you not defining the changed datatype on the Tranformation when you create the new database? You can control the input by CASTing the new type in the Source SQL Statement. This should allow you output the correct data type to your destination table.

    Also you could attempt to modify the "Create Table" script of the Task to create the correct type in the destination table and attempt to squeeze the data into the new field.

    I would personnaly still CAST the existing data type to match the new data type for clarity and consistency.

  7. #7
    Join Date
    Feb 2004
    Posts
    1

    Re: valid query wont work as DTS task???

    You may have 2 many GO statements in your query.
    Only 1 required at the end. I was having similar problem with large queries.


    Originally posted by mozkill
    i have a query which works fine when i run it from Query Analyzer.

    when i try to create a DTS script "Exectute SQL Task", then this SQL does not work within that task, and i dont know why:

    ALTER TABLE T_DESCRIP COLUMN EYE varchar(3) NOT NULL

    It throws and error that says:

    "Error Description: Deferred prepare could not be completed. Statement could not be prepared. Incorrect syntax near the keyword 'COLUMN'.

  8. #8
    Join Date
    Feb 2004
    Posts
    7

    Re: valid query wont work as DTS task???

    Originally posted by mozkill
    i have a query which works fine when i run it from Query Analyzer.

    when i try to create a DTS script "Exectute SQL Task", then this SQL does not work within that task, and i dont know why:

    ALTER TABLE T_DESCRIP COLUMN EYE varchar(3) NOT NULL

    It throws and error that says:

    "Error Description: Deferred prepare could not be completed. Statement could not be prepared. Incorrect syntax near the keyword 'COLUMN'.

    Hi...

    I may have found your problem. When adding columns, the ALTER TABLE statement doesn't call for the keyword COLUMN. Also, if you are adding a field that does not allow nulls to a table that is already populated, you'll need to assign the field with a default value or it will fail.

    I tried this in a quick DTS package and it worked fine...

    ALTER TABLE T_DESCRIP
    ADD EYE varchar(3) NOT NULL
    DEFAULT 'DEF' WITH VALUES

    GO


    Just replace 'DEF' above with whatever is appropriate for a default value in your case... ' ', 'VAL', CONVERT(varchar(3), ''), etc...

    Justin

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •