Thursday, May 3, 2007 6:03 PM tonyrogerson

SQL2005 SP2 Regression :- Cannot manage a server using Management Studio where server name > 30 characters.

So, anybody use SQL Instances? Anybody got a server name > 30 characters? Well, you are stuck on SP1 until a hotfix is produced to fix this regression in Management Studio. If you find this important, the fact that they've hard coded (picked out the blue no doubt) 30 characters for a server name instead of using sysname then show your disgust and disapprovement at my connect entry...

SQL Agent Jobs and Maint plans are not editable when using SP2 tools against servers with a server name > 30 characters.

After creating a job, trying to double click or script the job SSMS gives a binary or string truncation error. This occurs when the total server name length is > 30 characters, for instance our server name is: EN-SQL2K5-PROD1\EN_SQL2K5_PROD1.

This also affects maintanence plans, trying to edit an existing maint plan fails with the same binary or string truncation error.

This is a new bug introduced by SP2 and is not fixed by cumulative hotfix 3159.

This is a serious blocking issue that prevents DBA's using SMSS on SP2 for servers with name > 30 characters in length.

On profiling what SMSS sends to SQL Server the problem is the developer assumes a max server length of nvarchar(30) - frankly where is this arbitary number come from when max server name is sysname (nvarchar(128)).

The script that SMSS runs is shown below.....

create table #tmp_sp_help_jobserver
(server_id int null, server_name nvarchar(30) null, enlist_date datetime null, last_poll_date datetime null, last_run_date int null, last_run_time int null, last_run_duration int null, last_run_outcome tinyint null, last_outcome_message nvarchar(1024) null, job_id uniqueidentifier null)

declare @job_id uniqueidentifier
declare crs cursor local fast_forward
for ( SELECT
sv.job_id AS [JobID]
msdb.dbo.sysjobs_view AS sv
('REPL - TEST LINKED SERVERS' and sv.category_id=N'0') )
open crs
fetch crs into @job_id
while @@fetch_status >= 0
insert into #tmp_sp_help_jobserver(server_id, server_name, enlist_date, last_poll_date, last_run_date, last_run_time, last_run_duration, last_run_outcome, last_outcome_message)
        exec msdb.dbo.sp_help_jobserver @job_id = @job_id, @show_last_run_details = 1
    update #tmp_sp_help_jobserver set job_id = @job_id where job_id is null
    fetch crs into @job_id
close crs
deallocate crs


Filed under:


# Non c' pace per il SP2

Friday, May 4, 2007 8:38 AM by l.bianchi

# No Excuse

Saturday, May 5, 2007 12:21 AM by SQL Musings

I know writing and working on SQL Server is hard. It's a huge project and just managing that is hard....

# re: SQL2005 SP2 Regression :- Cannot manage a server using Management Studio where server name > 30 characters.

Friday, May 11, 2007 9:20 AM by Colin Leversuch-Roberts

sounds like an undefined varchar to me. This is the type of error that just should not happen. btw. is 3161 going to be publicly available do you know?

# jobs using regressions

Monday, May 26, 2008 6:04 PM by jobs using regressions

Pingback from  jobs using regressions