Quick formatting question

[mutley1]

Hi guys,

I'm building a config build script in SQL 2000 and have a quick output question. Probably an easy one, but the thing is I am trying to do it to write results to text so my boss can run it then just copy and paste the results into a doc.

Q: If I run exec master.dbo.sp_helpfile, for example, the fields are extremely long - is there an easy way to trim the output so master and mastlog etc. are just the length they need to be as opposed to with extra spaces until the next field?

TIA,

M.

 

[simian101]

jklj

 

[simian101]

--You can insert it in to a temp table then select individual fields or create a report.

--create table #sphf1 (
Name varchar(50),
fileid int,
filename varchar(500),
filegroup varchar(25),
size varchar (50),
maxsize varchar(50),
growth varchar (20),
usage varchar (25))

--insert into #sphf1 exec master.dbo.sp_helpfile

--select * from #sphf1
--select name from #sphf1

Simi

 

[simian101]

I had to comment it out... The site did not like something in the script.

Simi

 

[mutley1]

Yeah - I thought as much.....temp it, dump it, select it.........hot diggity damn.....no such thing as 1 line code returning what you want!

Cheers,

M.

 

[mutley1]

OK - that doesn't work because I'm outputting to text. The field is fine, but the header retains the length of the field in the table

Name            fileid      filename                                                                                   filegroup       size                 maxsize         growth               usage           
--------------- ----------- ------------------------------------------------------------------------------------------ --------------- -------------------- --------------- -------------------- --------------- 
master          1           D:\Program Files\Microsoft SQL Server\MSSQL\data\master.mdf                                PRIMARY         37248 KB             Unlimited       10%                  data only
mastlog         2           D:\Program Files\Microsoft SQL Server\MSSQL\data\mastlog.ldf                               NULL            2304 KB              Unlimited       10%                  log only

Filename is a good example - I set it to varchar 90.

Is there an option to supress headers when results are to TEXT?

TIA,

M.

 

[Crowley16]

have you thought about exporting to csv?

also, there are options in osql and bcp which are extremely useful for these situations, you should look it up.

--------------------
Procrastinate Now!

 

[mutley1]

Found it thanks Crowley - tools, options, results and uncheck print headers. D'oh!

Thanks,

M.