How to Specify Length of Output Column in SELECT Stmt?

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 General Discussion » How to Specify Length of Output Column in...

How to Specify Length of Output Column in SELECT Stmt?

Rate Topic

Display Mode

Topic Options

Author

Message

dbaforever

Posted Friday, May 21, 2010 3:46 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, December 11, 2012 3:02 PM
Points: 135, Visits: 460

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 Sad

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

Post #926313

bitbucket-25253

Posted Friday, May 21, 2010 4:01 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 1:49 PM
Points: 5,122, Visits: 20,362

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
Before posting a performance problem please read

Post #926318

dbaforever

Posted Friday, May 21, 2010 7:14 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, December 11, 2012 3:02 PM
Points: 135, Visits: 460

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

Post #926356

bitbucket-25253

Posted Saturday, May 22, 2010 10:21 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 1:49 PM
Points: 5,122, Visits: 20,362

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
Before posting a performance problem please read

Post #926435

Jeff Moden

Posted Saturday, May 22, 2010 11:55 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:01 PM
Points: 33,108, Visits: 27,033

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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #926451

dbaforever

Posted Saturday, May 22, 2010 1:43 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, December 11, 2012 3:02 PM
Points: 135, Visits: 460

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

Post #926455

Jeff Moden

Posted Sunday, May 23, 2010 10:13 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:01 PM
Points: 33,108, Visits: 27,033

Thanks for the feedback, John.

--Jeff Moden

Post #926524

« Prev Topic | Next Topic »

Permissions