January 18, 2012 at 2:54 am
Hello,
Just a simple but puzzling question; When I execute the following T-SQL the @MaxLen variable appears to hold the desired value, but when used within the LEFT clause doesn't appear to have any affect. If I replace the @MaxLen variable with a constant of the same value, it works as expected.
Here's the code:
DECLARE@MaxLenINT= (SELECT MAX(LEN(Name)) FROM sys.filegroups);
PRINT @MaxLen;
SELECTLEFT(name,@MaxLen) ,
CASE is_read_only
WHEN 0 THEN 'Read Write'
WHEN 1 THEN 'Read Only'
ELSE 'Unknown'
END AS AccessMode
FROMsys.filegroups
WHen I run this the 'Name' column is not truncated to the length specified in the @MaxLen variable.
Try it for yourself and please let me know if I've done something stupid or this is a genuine oddity.
January 18, 2012 at 3:08 am
raotor (1/18/2012)
Hello,Just a simple but puzzling question; When I execute the following T-SQL the @MaxLen variable appears to hold the desired value, but when used within the LEFT clause doesn't appear to have any affect. If I replace the @MaxLen variable with a constant of the same value, it works as expected.
Here's the code:
DECLARE@MaxLenINT= (SELECT MAX(LEN(Name)) FROM sys.filegroups);PRINT @MaxLen;
SELECTLEFT(name,@MaxLen) ,
CASE is_read_only
WHEN 0 THEN 'Read Write'
WHEN 1 THEN 'Read Only'
ELSE 'Unknown'
END AS AccessMode
FROMsys.filegroups
WHen I run this the 'Name' column is not truncated to the length specified in the @MaxLen variable.
Try it for yourself and please let me know if I've done something stupid or this is a genuine oddity.
Please post it with detailed information. From this information what I see is everthing seems to be ok.
I think your filegroup name may contain spaces. Please check again.
Post with actual data and results for others to help you.
-- Babu
January 18, 2012 at 3:23 am
Works ok for me:
DECLARE@MaxLenINT= (SELECT MAX(LEN(Name)) FROM sys.filegroups);
SELECT
[name],
[MaxLen] = @MaxLen,
[Shortname] = LEFT(name,@MaxLen),
[ShortLength] = LEN(LEFT(name,@MaxLen)),
CASE is_read_only
WHEN 0 THEN 'Read Write'
WHEN 1 THEN 'Read Only'
ELSE 'Unknown'
END AS AccessMode
FROMsys.filegroups
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
January 18, 2012 at 3:32 am
baabhu (1/18/2012)
raotor (1/18/2012)
Hello,Just a simple but puzzling question; When I execute the following T-SQL the @MaxLen variable appears to hold the desired value, but when used within the LEFT clause doesn't appear to have any affect. If I replace the @MaxLen variable with a constant of the same value, it works as expected.
Here's the code:
DECLARE@MaxLenINT= (SELECT MAX(LEN(Name)) FROM sys.filegroups);PRINT @MaxLen;
SELECTLEFT(name,@MaxLen) ,
CASE is_read_only
WHEN 0 THEN 'Read Write'
WHEN 1 THEN 'Read Only'
ELSE 'Unknown'
END AS AccessMode
FROMsys.filegroups
WHen I run this the 'Name' column is not truncated to the length specified in the @MaxLen variable.
Try it for yourself and please let me know if I've done something stupid or this is a genuine oddity.
Please post it with detailed information. From this information what I see is everthing seems to be ok.
I think your filegroup name may contain spaces. Please check again.
Post with actual data and results for others to help you.
-- Babu
Please find attached two report files.
Result_1 is the output using the code as above.
Result_2 is the output with the @MaxLen variable substituted with the constant value of 9 (Which is the longest filegroup name in my result set).
Also bear in mind that my results are set as "Results To Text" for viewing.
January 18, 2012 at 3:45 am
This was removed by the editor as SPAM
January 18, 2012 at 3:48 am
Can't see those report files.
You are using SQL Server 2998, aren't you?
John
January 18, 2012 at 3:56 am
John Mitchell-245523 (1/18/2012)
Can't see those report files.You are using SQL Server 2998, aren't you?
John
I believe I'm pretty up to date as I'm using SS2008R2 SP1.
Did you get the actual attachments? II'm still pretty new at all this, so I may havbe screwed up with the attaching bit! 🙂
January 18, 2012 at 4:01 am
raotor (1/18/2012)
Did you get the actual attachments?
No, there's nothing attached to your previous post. Click on Edit Post and then click on Edit Attachments to start uploading them.
John
January 18, 2012 at 4:22 am
I think it should've worked now. There should be a .rar format attachment on this post with the Result_1 and Result_2 report files.
January 18, 2012 at 6:15 am
raotor (1/18/2012)
I think it should've worked now. There should be a .rar format attachment on this post with the Result_1 and Result_2 report files.
I think this is the property of the SQLserver management studio.
I just realized both the results have same datalenght.
DECLARE@MaxLentinyint= (SELECT MAX(LEN(Name)) FROM sys.filegroups);
select 'Max',@MaxLen;
SELECTdatalength(LEFT(name,convert(int,@MaxLen))) ,CASE is_read_onlyWHEN 0 THEN 'Read Write'WHEN 1 THEN 'Read Only'ELSE 'Unknown'END AS AccessModeFROMsys.filegroups;
set @MaxLen = 7
SELECTdatalength(LEFT(name,7)) ,CASE is_read_onlyWHEN 0 THEN 'Read Write'WHEN 1 THEN 'Read Only'ELSE 'Unknown'END AS AccessModeFROMsys.filegroups;
Both will return the same datalength.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply