Stored Proc Name Length Limit?

Question

Stored Proc Name Length Limit?

Jason Bunn

SSCommitted

Points: 1713
More actions
June 26, 2008 at 12:03 pm

#381380

Hello,
When I use sp_addarticle to create an article:
declare @pub_name sysname
declare @sql_text nvarchar(4000)
declare @err nvarchar(500)
set @pub_name = 'Acclaim_Repl'
declare @article_name sysname;
set @article_name ='CLIENT_ADDRESS_STD_HIST'
set @sql_text = 'sp_addarticle ' +
'@publication = N''' + @pub_name + ''', ' +
'@article = N''' + @article_name + ''', ' +
'@source_table = N''' + @article_name + ''', ' +
'@destination_table = N''STAGE_' + @article_name + ''', ' +
'@type = N''logbased'', ' +
'@description = N''ETL ' + @article_name + ''', ' +
'@destination_owner = N''dbo'',' +
'@source_owner = N''dbo''';
exec(@sql_text);
I see, as the stored proc names (in replication properties):
[sp_MSins_dboSTAGE_CLIENT_ADDRE]
[sp_MSupd_dboSTAGE_CLIENT_ADDRE]
[sp_MSdel_dboSTAGE_CLIENT_ADDRE]
Is there a 30 character limit somewhere? This is SQL 2005 to SQL 2005 replication, both using version 9.00.3054 (SP2, no cumulative updates). I've looked around, and even the BOL examples have more than 30 characters...
Thanks,
Jaso

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Ian Scarlett SSC-Insane Points: 23197 More actions · Answer 1

The name of a stored procedure has a data type of "sysname", which is 128 characters.

The schemas for the replication metadata all use sysname, so MS have obviously decided to limit the replication procedure names to 30 chars for reasons best known to themselves!

noeld SSC Guru Points: 96590 More actions · Answer 2

Jason Bunn (6/26/2008)
Hello,
When I use sp_addarticle to create an article:
declare @pub_name sysname
declare @sql_text nvarchar(4000)
declare @err nvarchar(500)
set @pub_name = 'Acclaim_Repl'
declare @article_name sysname;
set @article_name ='CLIENT_ADDRESS_STD_HIST'
set @sql_text = 'sp_addarticle ' +
'@publication = N''' + @pub_name + ''', ' +
'@article = N''' + @article_name + ''', ' +
'@source_table = N''' + @article_name + ''', ' +
'@destination_table = N''STAGE_' + @article_name + ''', ' +
'@type = N''logbased'', ' +
'@description = N''ETL ' + @article_name + ''', ' +
'@destination_owner = N''dbo'',' +
'@source_owner = N''dbo''';
exec(@sql_text);
I see, as the stored proc names (in replication properties):
[sp_MSins_dboSTAGE_CLIENT_ADDRE]
[sp_MSupd_dboSTAGE_CLIENT_ADDRE]
[sp_MSdel_dboSTAGE_CLIENT_ADDRE]
Is there a 30 character limit somewhere? This is SQL 2005 to SQL 2005 replication, both using version 9.00.3054 (SP2, no cumulative updates). I've looked around, and even the BOL examples have more than 30 characters...
Thanks,
Jaso

We use SP2 with the minimum CU. We are at 09.00.3152. You can check here for the versions.

We definitely have more than 30 chars on the replication procs.

Where exactly is "replication properties" you mention?

* Noel

Jason Bunn SSCommitted Points: 1713 More actions · Answer 3

the "properties" I was referring to is at:

Replication->Local Publications->right click on your publisher and go to "properties".

We think we found the error. The parameter "source_table" in the sp_addarticle procedure is deprecated. Apparently, as side effect seems to be that the stored procs are limited to 30 characters. I suppose that's effective enforcement of a guideline not to use deprecated commands! 🙂

We changed it to "source_object" and it appears to work well with what we've tested thus far.

noeld SSC Guru Points: 96590 More actions · Answer 4

Right but then you continue to "articles"->"article properties", right 😉

And yes definitely that was a good catch. I never came accross this because somehow "we" listened to MS advice 😉

This could have definitely taken hours for us to troubleshoot in case someone messed up.

Thanks for posting the answer 😀

* Noel