Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to Specify Length of Output Column in SELECT Stmt? Expand / Collapse
Author
Message
Posted Friday, May 21, 2010 3:46 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 21, 2014 7:49 PM
Points: 150, Visits: 497
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
Post #926313
Posted Friday, May 21, 2010 4:01 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:52 AM
Points: 5,568, Visits: 24,753
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
Posted Friday, May 21, 2010 7:14 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 21, 2014 7:49 PM
Points: 150, Visits: 497
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
Posted Saturday, May 22, 2010 10:21 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:52 AM
Points: 5,568, Visits: 24,753
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
Posted Saturday, May 22, 2010 11:55 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:18 AM
Points: 36,751, Visits: 31,202
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #926451
Posted Saturday, May 22, 2010 1:43 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 21, 2014 7:49 PM
Points: 150, Visits: 497
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
Posted Sunday, May 23, 2010 10:13 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:18 AM
Points: 36,751, Visits: 31,202
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #926524
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse