03 May 2007 18:03
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
(sv.name=N'REPL - TEST LINKED SERVERS' and sv.category_id=N'0') )
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
Filed under: SQL Server