How to Specify Length of Output Column in SELECT Stmt?

Question

Post reply

How to Specify Length of Output Column in SELECT Stmt?

dbaforever

Hall of Fame

Points: 3740
More actions
May 21, 2010 at 3:46 pm

#221891

hi experts,
This is 2005.
I want my report to contain everything on just 1 line, so I need to trum the length of the output columns.
SET @sqlcmd = 'sqlcmd -Q '
SET @cmd = '"SELECT ServerName as Server, LoginName as FailedLogin, ClientName as ComputerName, LoginDate from DBA.dbo.AuditDatabaseLogins WHERE AuditType = ''Login Failed'' AND LoginDate between ''' + convert(varchar(10), @PriorMonthStart, 112) + ''' and ''' + convert(varchar(10), @PriorMonthEnd, 112) + ''' " -o C:\AuditReports\TestSqlCmd40.txt'
SET @sqlcmd = @sqlcmd + @cmd;
Print @sqlcmd;
EXEC xp_cmdshell @sqlcmd,no_output;
In the table, ServerName is varchar(50) so that column on the report is 50 chars wide 🙁
I tried SELECT CAST(ServerName char(15)) as Server - this got me nowhere fast.
I'm sure it's simple to narrow the column width - does anyone know how please?
Thanks, John

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

Try this:

SELECT LEFT(RTRIM(@@servername),10)

RTRIM to remove any trailing blanks

LEFT (in this case 10 characters) - counting from left to right.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

dbaforever Hall of Fame Points: 3740 More actions · Answer 2

Thank you, BitBucket! Your suggestion worked perfectly.

SET @sqlcmd = 'sqlcmd -Q '

SET @cmd = '"SELECT LEFT(RTRIM(ServerName),15) as Server, LEFT(RTRIM(LoginName),22) as FailedLogin, LEFT(RTRIM(ClientName),20) as ComputerName, LoginDate from DBA.dbo.AuditDatabaseLogins WHERE AuditType = ''Login Failed'' AND LoginDate between ''' + convert(varchar(10), @PriorMonthStart, 112) + ''' and ''' + convert(varchar(10), @PriorMonthEnd, 112) + ''' " -o C:\AuditReports\TestSqlCmd44.txt'

SET @sqlcmd = @sqlcmd + @cmd;

Print @sqlcmd;

EXEC xp_cmdshell @sqlcmd,no_output;

John

Ron McCullough SSC Guru Points: 63877 More actions · Answer 3

John Bates-251278

Your welcome and thanks for the feed back.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Jeff Moden SSC Guru Points: 1004432 More actions · Answer 4

Duplicate post... please see the following for another answer. THIS is why you shouldn't duplicate posts, folks...

http://www.sqlservercentral.com/Forums/Topic926260-149-1.aspx

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

dbaforever Hall of Fame Points: 3740 More actions · Answer 5

You are correct, Jeff Moden.

I posted the other thread hurriedly, intending to post it to another site that I use. Just got in a rush and should have been more careful.

BTW, your solution works equally well. Thanks.

John

Jeff Moden SSC Guru Points: 1004432 More actions · Answer 6

Thanks for the feedback, John.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)