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.
-
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
-
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 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply