locked
adding new column in my linked server RRS feed

  • Question

  • Hi,

    I am using sql server 2000 and I want to add a new column in my linked server table. Below is the query am using


    ALTER TABLE servername.dbname.dbo.tablename ADD columnname INT

    But I am getting below error


    Msg 117, Level 15, State 1, Line 1
    The object name 'servername.dbname.dbo.' contains more than the maximum number of prefixes. The maximum is 2.

    Please advise.

    Thanks,
    Rakesh.

    Please mark as helpful and propose as answer if you find this as correct!!! Thanks, Rakesh.

    Friday, July 27, 2012 5:20 AM

Answers

  • Hi guys,

    There is a way to do it. Below is the script,


    EXECUTE servername.[dbname].dbo.sp_executesql  N'ALTER TABLE tablename ADD columnname INT'




    Cheers,
    Rakesh.

    Please mark as helpful and propose as answer if you find this as correct!!! Thanks, Rakesh.

    • Proposed as answer by Naomi N Friday, July 27, 2012 12:49 PM
    • Marked as answer by Iam_Rakesh Monday, July 30, 2012 6:50 AM
    • Unmarked as answer by Iam_Rakesh Monday, July 30, 2012 6:50 AM
    • Marked as answer by Iam_Rakesh Monday, December 3, 2012 8:24 AM
    Friday, July 27, 2012 12:37 PM

All replies

  • You may try sending this script

    USE myServer.DbName

    and then ALTER TABLE command with just dbo.TableName

    Alternatively you may try running this using OpenQuery method.


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


    My blog

    Friday, July 27, 2012 5:23 AM
  • Hello Rakesh,

    See MSDN ALTER TABLE (Transact-SQL):
    ALTER TABLE [ database_name . [ schema_name ] . | schema_name . ] table_name

    Alter tables on a linked server is not supported, you have to do the alternation on the target server itself.


    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing



    • Edited by Olaf HelperMVP Friday, July 27, 2012 6:06 AM
    • Proposed as answer by Naomi N Friday, July 27, 2012 6:07 AM
    • Marked as answer by Iam_Rakesh Friday, July 27, 2012 7:18 AM
    • Unmarked as answer by Iam_Rakesh Friday, July 27, 2012 12:34 PM
    • Unproposed as answer by Iam_Rakesh Friday, July 27, 2012 12:34 PM
    Friday, July 27, 2012 6:05 AM
  • Hi guys,

    There is a way to do it. Below is the script,


    EXECUTE servername.[dbname].dbo.sp_executesql  N'ALTER TABLE tablename ADD columnname INT'




    Cheers,
    Rakesh.

    Please mark as helpful and propose as answer if you find this as correct!!! Thanks, Rakesh.

    • Proposed as answer by Naomi N Friday, July 27, 2012 12:49 PM
    • Marked as answer by Iam_Rakesh Monday, July 30, 2012 6:50 AM
    • Unmarked as answer by Iam_Rakesh Monday, July 30, 2012 6:50 AM
    • Marked as answer by Iam_Rakesh Monday, December 3, 2012 8:24 AM
    Friday, July 27, 2012 12:37 PM