Tony Rogerson's ramblings on SQL Server

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...

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=273298

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]
FROM
msdb.dbo.sysjobs_view AS sv
WHERE
(sv.name=N'REPL - TEST LINKED SERVERS' and sv.category_id=N'0') )
open crs
fetch crs into @job_id
while @@fetch_status >= 0
begin
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
end
close crs
deallocate crs