Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003
    Posts
    47

    Unanswered: remove columns created by replication process

    One of our users tried to set replication solution in a sqlserver (the idea was given up).
    SQL Server added in each table a column related to replication.
    We want to remove theses columns and I used the following script :


    select 'ALTER TABLE dbo.'+object_name(id)+' DROP CONSTRAINT '+object_name(constid)+' GO'
    +'ALTER TABLE dbo.'+object_name(id)+' DROP COLUMN '+'msrepl_tran_version GO'
    from sysconstraints where object_name(constid) like '%msrep%'

    Question:
    1. I want to know how to introduce a carraige return in order to have some thing like this :


    ...
    ALTER TABLE dbo.T_CommandCopyFile
    DROP CONSTRAINT DF__T_Command__msrep__44AB0736
    GO
    ALTER TABLE dbo.T_CommandCopyFile
    DROP COLUMN msrepl_tran_version
    ...
    2. Is there any other solution to do this more simply ?

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd use:
    Code:
    DECLARE @crlf CHAR(2)
    SET @crlf = Char(13) + Char(10)
    -PatP

  3. #3
    Join Date
    Oct 2003
    Posts
    47
    Could you give me more explanation (why : char(13)+char(10))
    I tried only char(13) only and I noticed the result (space in the beginning of the line).

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You really want the history lesson?

    Ok, back in the days of CP/M, there was hot debate as to what constituted a "line end". The Unix crew wanted Line Feed (0x0a). The OASIS crew wanted Carriage Return (0x0d). Nobody would budge.

    Teletypes needed both, and CR took longer to execute than LF did, so it was always sent first. Since nobody could make a "command decision" Gary Kildall made the call that they'd use what the teletypes wanted, to make it easier to print files and vex both of the software camps!

    MS-DOS basically picked up where CP/M left off, so it followed the same convention. Windoze is the GUI that was later bolted on to MS-DOS, so it used the same convention... You see where we are headed here, right?

    Anywho, the short answer boils down to Transact SQL sees a "line end" as being a carriage return followed by a line feed, aka Char(13) + Char(10) to us hydro-carbon based types.

    -PatP

Posting Permissions

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