March 13, 2013 at 12:51 pm
This one is probably very simple, but I can't find the answer to save my life.
I have a query that I run in which the output has to be in text mode (not grid mode). One of the output columns is text that often is several hundred characters long. This of course makes my output extremely wide. I'd like to format this one column so that it's only 50 characters wide but the text is wrapped to several lines tall.
Is this possible?
March 13, 2013 at 12:59 pm
SSMS -> Options -> Query Results -> SQL Server -> Results to Text -> Maximum Number of Characters displayed in each column
Does that help?
March 13, 2013 at 1:08 pm
Nope, didn't help unfortunately. Here's the query, if that helps.
selectcast(A.name as varchar(30)) as User_Name,
cast(A.type_desc as char(15)) as Account_Type,
A.create_date as Date_Created,
case is_rolemember('appconfiguration',A.name) when 1 then 'appconfiguration | ' else '' end
+ case is_rolemember('data_analysis',A.name) when 1 then 'data_analysis | ' else '' end
+ case is_rolemember('db_accessadmin',A.name) when 1 then 'db_accessadmin | ' else '' end
+ case is_rolemember('db_backupoperator',A.name) when 1 then 'db_backupoperator | ' else '' end
+ case is_rolemember('db_cmsalert',A.name) when 1 then 'db_cmsalert | ' else '' end
+ case is_rolemember('db_datareader',A.name) when 1 then 'db_datareader | ' else '' end
+ case is_rolemember('db_datawriter',A.name) when 1 then 'db_datawriter | ' else '' end
+ case is_rolemember('db_dcas',A.name) when 1 then 'db_dcas | ' else '' end
+ case is_rolemember('db_ddladmin',A.name) when 1 then 'db_ddladmin | ' else '' end
+ case is_rolemember('db_denydatareader',A.name) when 1 then 'db_denydatareader | ' else '' end
+ case is_rolemember('db_denydatawriter',A.name) when 1 then 'db_denydatawriter | ' else '' end
+ case is_rolemember('db_directupdates',A.name) when 1 then 'db_directupdates | ' else '' end
+ case is_rolemember('db_nuanceapplication',A.name) when 1 then 'db_nuanceapplication | ' else '' end
+ case is_rolemember('db_owner',A.name) when 1 then 'db_owner | ' else '' end
+ case is_rolemember('db_securityadmin',A.name) when 1 then 'db_securityadmin | ' else '' end
+ case is_rolemember('db_storedprocexec',A.name) when 1 then 'db_storedprocexec | ' else '' end
+ case is_rolemember('integrationengine',A.name) when 1 then 'integrationengine | ' else '' end
as DB_Roles,
case is_srvrolemember('Public',A.name) when 1 then 'Public | ' else '' end
+ case is_srvrolemember('bulkadmin',A.name) when 1 then 'BulkAdmin | ' else '' end
+ case is_srvrolemember('dbcreator',A.name) when 1 then 'DBCreator | ' else '' end
+ case is_srvrolemember('DiskAdmin',A.name) when 1 then 'DiskAdmin | ' else '' end
+ case is_srvrolemember('ProcessAdmin',A.name) when 1 then 'ProcessAdmin | ' else '' end
+ case is_srvrolemember('SecurityAdmin',A.name) when 1 then 'SecurityAdmin | ' else '' end
+ case is_srvrolemember('ServerAdmin',A.name) when 1 then 'ServerAdmin | ' else '' end
+ case is_srvrolemember('SetupAdmin',A.name) when 1 then 'SetupAdmin | ' else '' end
+ case is_srvrolemember('SysAdmin',A.name) when 1 then 'SysAdmin | ' else '' end
as Server_Roles
fromsys.database_principals A left join
master.sys.server_role_members B on A.principal_id = B.member_principal_id left join
master.sys.server_principals C on B.role_principal_id = C.principal_id
whereA.type <> 'R'
andA.name not in ('dbo', 'guest', 'information_schema')
orderby A.name
Notice how the DB_Roles and Server_Roles columns are super wide, even with the Maximum set in the options like you suggested. I need these last two columns to wrap to 50 characters or so.
March 13, 2013 at 2:05 pm
Mick Opalak (3/13/2013)
Notice how the DB_Roles and Server_Roles columns are super wide, even with the Maximum set in the options like you suggested. I need these last two columns to wrap to 50 characters or so.
What does this mean? You only want the first 50 characters? I ran this and none of the rows wrap to multiple lines when the output is set to text.
Why does is matter if you set the output to text instead of grid? Just curious how the output format makes a difference for your usage.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 13, 2013 at 3:04 pm
No, I'd like not just the first 50 characters but all of the characters in the field. I'd like the first 50 characters, then a carriage return, the next 50 characters, etc., like when you use Word Wrap in Excel.
This query is part of a larger query which makes up a text report. That is why I use text output and not grid output.
March 13, 2013 at 3:28 pm
Mick Opalak (3/13/2013)
No, I'd like not just the first 50 characters but all of the characters in the field. I'd like the first 50 characters, then a carriage return, the next 50 characters, etc., like when you use Word Wrap in Excel.This query is part of a larger query which makes up a text report. That is why I use text output and not grid output.
There was a thread around here recently to add carriage returns after a certain number of characters. I will see if I can find it.
I would say that if this is part of a report why not make it an actual report and then you can control the column width. You are trying to use the wrong tool for a report imho.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 13, 2013 at 4:26 pm
Mick Opalak (3/13/2013)
No, I'd like not just the first 50 characters but all of the characters in the field. I'd like the first 50 characters, then a carriage return, the next 50 characters, etc., like when you use Word Wrap in Excel.This query is part of a larger query which makes up a text report. That is why I use text output and not grid output.
This should get you started; I wish I had a few more minutes to finish this...
;WITH blah(x) AS (SELECT REPLICATE('xx',45) UNION ALL SELECT 'dog' UNION ALL SELECT 'cat')
SELECT x=CASE --STUFF(x,50,1,CHAR(13))
WHEN LEN(x)<=50 THEN x
WHEN LEN(x)>50 AND LEN(x)<=100 THEN STUFF(x,50,1,CHAR(13))
ELSE NULL --I have not figured this out yet....
END
FROM blah
-- Itzik Ben-Gan 2001
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply