Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2011
    Posts
    85

    Question Unanswered: Help to solve ANSI_PADDING OFF issue in a Proc

    Hi All

    I'm posting an issue of a Procedure that I'm currently trying to resolve. This PROC can be used to clone a table [Without Data].
    As the minimum parameters you only have to pass the target table name and the new table name.

    The problem i have with this Proc is. If there is a table with some columns like char, varchar, binary, and varbinary that created without
    ANSI_PADDING [Which means ANSI_PADDING OFF]

    Eg
    sposhipparty_id int
    partyname varchar 255
    party_id int
    partytype int
    code varchar 255
    codelist varchar 255 ANSI_PADDING OFF
    orgname varchar 255 ANSI_PADDING OFF

    When cloning the table all varchar cols will be created as ANSI_PADDING ON. But I need to clone the table
    exactly as the old table. I know ANSI_PADDING ON will be good and many reason there to prove ANSI_PADDING OFF is not recommended.
    But the requirement is to clone the table exactly as it is. So if the is ANSI_PADDING OFF then cloned new table must be the same.

    I'm attaching the code of the Proc. our table starts with dbo. [but we have written the proc to work without dbo. also]
    Please help me to find what is wrong here.
    If you search the part = -- tp_core_copytableschema: Create new table
    Here we clone the table as ANSI_PADDING ON. Then in the section = -- tp_core_copytableschema: Alter columns for ANSI PADDING
    we alter the columns which were originally was as ANSI_PADDING OFF

    We identify this from the logic written under = -- If the table has any columns that are not ANSI PADDED

    Please let me know where is the problem

    Thank You
    PROC.txt
    Last edited by gvee; 03-11-15 at 09:37.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    For what it's worth, when I had to achieve something similar to this I used SMO.
    Basically a little console app written in C# that generates the same scripts as Right-click > Script as > Create to. The whole thing took only a few hours to get working as I wanted, with a couple more added on for polishing it up at a later date: total <1 day development.
    I found this process a whole heap easier than meddling with T-SQL (which is an approach I have taken before, on a SQL 6.5 instance).
    George
    Home | Blog

  3. #3
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    I found this http://www.mindsdoor.net/dmo/dmoscriptalldatabases.html on the Net.

    The length of code in your solution and in the link above, indicate that it is not a trivial task. If you solve this problem, next week you may encounter another situation your code doesn't cover yet. That's why I think George's suggestion is the best approach to your problem: let Microsoft take care about the gritty details and "simply" use the result they provide. I wrote "simply", cuz I don't use SMO (SQL Server Management Objects) myself.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I hadn't touched SMO until I attempted this for the first time. If you know a little bit of .NET then it's pretty easy to pick up and roll with it.

    Who knows, maybe one day I will release the source of my project, when I polish it off well enough!
    George
    Home | Blog

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    This may be a clue as to why your resulting column ends up with ANSI_PADDING ON (BOL - since 2005 mind you):

    Code:
    The following are criteria for type_name of an altered column:   
    
    
    • The previous data type must be implicitly convertible to the new data type.
    • type_name cannot be timestamp.
    • ANSI_NULL defaults are always on for ALTER COLUMN; if not specified, the column is nullable.
    • ANSI_PADDING padding is always ON for ALTER COLUMN.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  6. #6
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    >> I'm posting an issue of a Procedure that I'm currently trying to resolve. This PROC can be used to clone a table [Without Data].
    As the minimum parameters you only have to pass the target table name and the new table name. <<

    WHY?? The goal of any database is to reduce redundancy, not increase it. The nature of a set oriented language like SQL is that we do not split these sets into multiple tables.

    >> The problem I have with this Proc is. If there is a table with some columns like CHAR(n), VARCHAR(n), BINARY(n), and VARBINARY(n) that created without* ANSI_PADDING [Which means ANSI_PADDING OFF]*<<

    WHY? You are inventing your own personal language with a proprietary language feature.

    >> I'm attaching the code of the Proc. our table starts with dbo. [but we have written the proc to work without dbo. also]
    Please help me to find what is wrong here.*<<

    We do not like to open strange files on the Internet; do you?

    Why are you trying to do system level things in SQL and not with utilities at the proper level?

Posting Permissions

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