SQL Server Performance

sp_addarticle has only max 30 characters in procedure name in its three ins, del, upd stored procedures

Discussion in 'SQL Server 2005 Replication' started by WingSzeto, Dec 7, 2007.

  1. WingSzeto Member

    We are using SQL 2K5 sp2 standard with push transactional replication.
    I am using sp_addarticle and sp_addsubscription to add a table into our publisher database. I just recently discovered that the three stored procedures (ins, del, upd) created by this publishing method in the subscriber database will only have a max 30 characters in length. For example, say I am publishing the following two tables.
    tblEmp_ID_HandBook
    tblEmp_ID_HandBook_Rev
    When I use the sp_addarticle and sp_addsubscription to publish these two tables, below is what appears in the delivery format in the article properties of the corresponding tables. I will only show the 'Insert stored procedure' for the example. If you count the below procedure names, each has exactly 30 characters in length even though the second table name is longer than the first table name.
    tblEmp_ID_HandBook -> sp_MSins_dbtbltblEmp_ID_HandBook
    tblEmp_ID_HandBook_Rev -> sp_MSins_dbtbltblEmp_ID_HandBook
    If I do the publishing in SQL mgmt studio then the insert stored procedure will show correct name.
    tblEmp_ID_HandBook -> sp_MSins_dbtbltblEmp_ID_HandBook
    tblEmp_ID_HandBook_Rev -> sp_MSins_dbtbltblEmp_ID_HandBook_Rev
    Can someone try a similar publishing like above for me, I need to know if this is a bug or something wrong in our replication setup?
    wingman
  2. satya Moderator

    Hope you have upto date service pack levels on all the SQL instances. but I have never seen such a behaviour and may think its a service pack issus on the client tools as well.
  3. WingSzeto Member

    We are on built 9.0.3042, which is sp2. As far as I know, this is the latest service pack for SQL 2K5. Do you mean the hot fixes?
    I have tested it again on the same table and another existing table, again it truncated the procedure name after using the sp_addarticle and sp_addsubscription as I described in my original email. However, if I create a new table with the same length in name as the existing table, it didn't truncate the name when I published the table using the same two stored procedures. I don't understand why this is happening. Any idea?
    wingman

Share This Page