Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
add column problem

Jawab add column problem

  • Thursday, May 17, 2012 1:49 PM
     
     
    Sign In to Vote
    0

    I am having a weird problem in some environments,
    with different scripts of the same type :

    if column_name does not exist
    begin
     backup(dynamic sql)
     ALTER TABLE table_name ADD column_name data_type allow_null
    end

    back up is done and then it gives an "error near ADD" , "error near column_name"
    This type of script runs fine on most environments but then, for some it fails.
    Last time the table to change was replicated and in addition to above messages I got "transaction ended in a trigger"

    if I select and execute ALTER TABLE statement only (same session) it runs fine

    Can someone help please?


    • Edited by lenab Thursday, May 17, 2012 2:20 PM
    •  
     

All Replies

  • Thursday, May 17, 2012 2:16 PM
     
     
    Sign In to Vote
    0
    there might be some trigger that uses commit statement and due to some error it does not allow you to create column

    Shamas Saeed (if Post helpful please mark as Answer) http://shamas-saeed.blogspot.com

     
  • Thursday, May 17, 2012 2:21 PM
     
     
    Sign In to Vote
    0
    there are no triggers on modified table.
     
  • Thursday, May 17, 2012 2:23 PM
     
     
    Sign In to Vote
    0

    then try to execute both statement separate so that you can find exact error point.

    like

    if column_name does not exist
    begin

     backup(dynamic sql)

    end

    if column_name does not exist
    begin
     ALTER TABLE table_name ADD column_name data_type allow_null
    end

    Shamas Saeed (if Post helpful please mark as Answer) http://shamas-saeed.blogspot.com

     
  • Thursday, May 17, 2012 2:31 PM
     
     
    Sign In to Vote
    0
    problem is it only happens at some customers. I cannot recreate it. I was hoping someone had an issue like that before. script is very standard to add a system flag for example
     
  • Thursday, May 17, 2012 2:40 PM
     
     
    Sign In to Vote
    0
    strange but Please paste exact error statement so that i might have some good idea.

    Shamas Saeed (if Post helpful please mark as Answer) http://shamas-saeed.blogspot.com

     
  • Thursday, May 17, 2012 3:10 PM
     
     
    Sign In to Vote
    0

    Hi,

    SQL Versions are same for all the environments? 

    Regards
    Satheesh

     
  • Thursday, May 17, 2012 3:15 PM
    Moderator
     
     
    Sign In to Vote
    0
    Can you share the exact script (part of it), and also @@VERSION info? In addition, is it possible that there are DDL triggers in the database?

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

     
  • Thursday, May 17, 2012 3:56 PM
     
     
    Sign In to Vote
    0

    version: Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86) Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

    noting returned by SELECT * FROM sys.server_triggers

    this is last script that failed:

    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[table_name]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
    BEGIN
     if not exists (SELECT sysobjects.name,
                           sysobjects.id,
                           sysobjects.xtype, syscolumns.name AS NAME_COL
                      FROM sysobjects INNER JOIN syscolumns ON
                           sysobjects.id = syscolumns.id
                     WHERE sysobjects.name = 'table_name' AND
                           syscolumns.name = 'column_name')

         BEGIN
             print 'Backing up table_name'
             declare @sql_backup as char (100)
             set @sql_backup = 'SELECT * INTO [table_name_'
                               + rtrim(DATEPART(yyyy, GETDATE())) + '/'
                               + rtrim(DATEPART(mm, GETDATE())) + '/'
                               + rtrim(DATEPART(dd, GETDATE())) + '/'
                               + rtrim(DATEPART(hh, GETDATE())) + '/'
                               + rtrim(DATEPART(mi, GETDATE())) + '/'
                               + rtrim(DATEPART(ss, GETDATE())) + '/'
                               + rtrim(DATEPART(ms, GETDATE()))
                               + '] FROM table_name'
             exec(@sql_backup)

             ALTER TABLE table_name ADD
                   column_name char(1) DEFAULT 'N' NOT NULL
         END
    END

    Thanks

     
  • Thursday, May 17, 2012 4:06 PM
    Moderator
     
     Answered
    Sign In to Vote
    1

    You need to start from applying the latest service pack for that SQL Server 2005 (the latest SP is 4 and that server is on SP1).

    http://support.microsoft.com/kb/321185

    The script can also be simplified by using INFORMATION_SCHEMA.Columns view and INFORMATION_SCHEMA.Tables view to check for column and a table.

    Also, don't forget to check for the schema - could it be that the table is created in non-default schema?

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

     
  • Thursday, May 17, 2012 7:17 PM
     
     
    Sign In to Vote
    0
    I am not the only programer here and I did not write the script. Still why would a simple script like that fail? It looks ok to me.
    The owner is dbo.
    could regional settings matter?
    Regional Options: Standards - French Canada; Location - Canada
    Advanced: Language for non-Unicode programs - French Canada
     
  • Thursday, May 17, 2012 7:31 PM
    Moderator
     
     
    Sign In to Vote
    0

    It is a possibility, I guess. Do you still get the error after applying the latest SP?

    Also, could it be the case-sensitive installation so each column name and table name must be in the correct case?

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


     
  • Thursday, May 17, 2012 7:39 PM
     
     
    Sign In to Vote
    0

    I did not see this thread until now, but it seems that this is due to a DDL trigger doing something it shouldn't.

    You said there was nothing in sys.server_triggers, but what does this return:

    SELECT * FROM sys.triggers WHERE parent_class = 0

    I would also say that the declaration of the varaiable @backup_sql makes me a little nervous. It's char(100). There is little reason not to make variables for dynamic SQL as nvarchar(MAX)

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
     
  • Thursday, May 17, 2012 7:55 PM
     
     
    Sign In to Vote
    0

    Thank you guys for your interest.

    I do not like that declaration either but it seems to be long enough in this case, plus backup is actually done.

    SELECT * FROM sys.triggers WHERE parent_class = 0 returns:

    tr_MStran_altertable
    tr_MStran_alterview
    tr_MStran_alterschemaonly
    tr_MStran_altertrigger

     
  • Thursday, May 17, 2012 8:56 PM
     
     
    Sign In to Vote
    1

    Looks like we are on to something there!

    What is in tr_MStran_altertable?

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
     
  • Friday, May 18, 2012 6:08 AM
     
     
    Sign In to Vote
    0
    Have you find your soluton lenab

    Shamas Saeed (if Post helpful please mark as Answer) http://shamas-saeed.blogspot.com

     
  • Friday, May 18, 2012 1:22 PM
     
     
    Sign In to Vote
    0

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    create trigger [tr_MStran_altertable] on database for ALTER_TABLE as

     set ANSI_NULLS ON
     set ANSI_PADDING ON
     set ANSI_WARNINGS ON
     set ARITHABORT ON
     set CONCAT_NULL_YIELDS_NULL ON
     set NUMERIC_ROUNDABORT OFF
     set QUOTED_IDENTIFIER ON

     declare @EventData xml
     set @EventData=EventData()   
     exec sys.sp_MStran_ddlrepl @EventData, 1
    GO
    SET ANSI_NULLS OFF
    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    ENABLE TRIGGER [tr_MStran_altertable] ON DATABASE


    • Edited by lenab Friday, May 18, 2012 1:32 PM
    •  
     
  • Friday, May 18, 2012 6:05 PM
     
     
    Sign In to Vote
    1

    So there is replication involved. And more precisely, this is code added by Microsoft, it's not user-written code.

    This instance of SQL Server runs Service Pack 1 of SQL 2005, which is a very old version. You should mandate an installation of Service Pack 4 on this server. It is very likely that this will resolve the problem.

    Naomi suggested this earlier in the thread. I did not see the relevance of that first, since I thought it was a user error. But with this piece of information, her advice was spot on.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
     
  • Friday, May 18, 2012 6:44 PM
     
     
    Sign In to Vote
    0
    Thanks guys. I'll let network people know that this might be a solution. We'll see.