I'm dealing with a server running SQL Server 2000 SP1, and the logical filenames for one of the databases appear to contain trailing spaces. That is, this query:
select replace(name, ' ', 'X')
from sysfiles
Returns the expected names plus a long string of Xs.
How can I deal with this? I've tried running ALTER DATABASE... MODIFY FILE using the name (with and without spaces) and get an error message telling me the file does not exist.
This isn't due to the fact that the names actually have spaces, it's due to the fact that Sql Server stores this data in a field of the type nchar(128), which is a fixed-width character data type (i.e. the data stored will always be 128 characters long, padded with spaces if necessary). So, when you query for that value, you'll always get a type that is 128 characters in length. If you want to trim the trailing space padding of the datatype, simply cast it to a variable-length datatype, or explicitly trim it, like this:
select cast(name as nvarchar(128)) as name from sysfiles
or
select rtrim(name) as name from sysfiles
or if you are planning to insert to another table, it will implicitly cast if the target datatype is variable length and compatible:
create table #files (name nvarchar(128));
insert #files (name) select name from sysfiles;
select name from #files;
If you slightly modify your statement above, you should notice that there are no trailing X's any longer:
select replace(cast(name as nvarchar(128)), ' ', 'X') from sysfiles
