How to Specify Length of Output Column in SELECT Stmt?

  • 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

  • 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]

  • 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

  • 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]

  • 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)

  • 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

  • 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)

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

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