Why does this query result in node names of 256 characters? I have applied SUBSTRING() to limit it to 32.
SELECT
SUBSTRING(CONVERT(VARCHAR(MAX), NodeName),1,32) AS NODENAME
,LEN(SUBSTRING(CONVERT(VARCHAR(MAX), NodeName),1,32))
,SUBSTRING(CONVERT(VARCHAR(MAX), SERVERPROPERTY('ComputerNamePhysicalNetBIOS')),1,32) AS CURRENT_OWNER
FROM sys.dm_os_cluster_nodes;
UPDATE:
The data will come from the machine this query is running on. Here is the result. I would like for the lines not to exceed the screen width. I am trying to limit the names to 32 characters.
PS C:\> sqlcmd -S DBINSTANCENAME
1> SELECT
2> SUBSTRING(CONVERT(VARCHAR(MAX), NodeName),1,32) AS NODENAME
3> ,LEN(SUBSTRING(CONVERT(VARCHAR(MAX), NodeName),1,32))
4> ,SUBSTRING(CONVERT(VARCHAR(MAX), SERVERPROPERTY('ComputerNamePhysicalNetBIOS')),1,32) AS CURRENT_OWNER
5> FROM sys.dm_os_cluster_nodes;
6> GO
NODENAME CURRENT_OWNER
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ANODESQL78D 15 TX102WPD2SQL78C
ANODESQL78C 15 TX102WPD2SQL78C
(2 rows affected)
The reason is because you've got a CONVERT in there which is changing the column to a varchar(MAX). When using SUBSTRING on a varchar(MAX) the return data type is a varchar(MAX). MAX length data is treated as a different data type to a numerical length, and SQL Server won't implicitly convert between the 2 when there's no other data type involved.
The solution is simple, don't CONVERT the value to a MAX length:
thom@ptah:~$ sqlcmd -Q "SELECT SUBSTRING(CONVERT(nvarchar(256), NodeName),1,32) AS NODENAME, LEN(SUBSTRING(CONVERT(nvarchar(256), NodeName),1,32)) ,SUBSTRING(CONVERT(nvarchar(256), SERVERPROPERTY('ComputerNamePhysicalNetBIOS')),1,32) AS CURRENT_OWNER FROM sys.dm_os_cluster_nodes;"
NODENAME CURRENT_OWNER
-------------------------------- ----------- --------------------------------
(0 rows affected)