OSQL output stored procedure column formatting problem

Question

Post reply

OSQL output stored procedure column formatting problem

Viewing 15 posts - 16 through 30 (of 32 total)

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

Jan-155192 SSCrazy Points: 2613 More actions · Answer 1

It should give you the possibility to have a recordset out of your stored procedure that you still can manipulate:

select RTRIM(column) from openrowset(linkedserver, storedprocedure).

nbryson SSChasing Mays Points: 659 More actions · Answer 2

nbryson

SSChasing Mays

Points: 659

May 16, 2007 at 8:56 am

#706833

Hi

It still doesn't trim

Cheers

Nickie

nbryson SSChasing Mays Points: 659 More actions · Answer 3

Hi

And just to add to my excitement - I tried running this as a DTS package and there are no trailing spaces. Aaarrggghhh.

Only trouble is I need it to output a different filename every time it runs. It runs daily and should have the date in the name to differentiate it.

Any help would be good.

Cheers

Nickie

Jan-155192 SSCrazy Points: 2613 More actions · Answer 4

nbryson,

I have a last suggestion before I give up.

It seems that the TRIM never works. It trims only blanks. Imagine that there is a nonprintable but not that is also non-blank. I have seen this before in downloads from mainframe.

If you SELECT CONVERT(binary,REPLICATE(5,' ')) you will see that the binary of an empty string is 0x00000000000

Do this with your query SELECT CONVERT(binary, column) FROM OPENQUERY(....) and check if it is really ending in 00000 or if there is something else.

Hope this finally helps!

Jan

nbryson SSChasing Mays Points: 659 More actions · Answer 5

Firstly - Thanks for everybody's help/input

The first couple of rows returns

0x530059005300570048004F00530050000000000000000000000000000000

0x330031003100320036003400480000000000000000000000000000000000

I've tried this as a dts to textfile and it works ok e.g. all columns dont have trailing spaces. I thought I'd use OSQL so I could use the date in the file name as I need to change the filename everytime its exported and schedule it to run daily.

Cheers

Nickie

-=JLK=- SSCertifiable Points: 5315 More actions · Answer 6

I am able to replicate his problem. I created a quick SP:

if exists (select 1 from sysobjects where id = object_id('uspTest_') and type = 'P') drop procedure uspTest_

go

create procedure uspTest_

as

begin

set nocount on

set ansi_padding off

create table #t1 (col1 varchar(20), col2 varchar(20), col3 varchar(20))

insert into #t1 values ('123','456','789')

select rtrim(col1), rtrim(col2), rtrim(col3) from #t1

end

Running exec dbo.uspTest_ in Query Analyzer with results to text produces:

123,456,789

Running it through osql using:

"C:\Program Files\Microsoft SQL Server\80\Tools\BINN\osql" -S"." -s"," -E -d"JLKTest" -t60 -n -Q"dbo.uspTest_" -o"d:\temp\test.out" -h-1 -u -x -I -R

Produces the following output:

123&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp,456&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp,789

I have been unable to find any combination of switches that will eliminate the padding with trailing blanks in the output column. I even switched the command to use a custom ODBC connection with ansi_padding off and still I get those pesky character spacings. I'm ready to conceed that I'm out of ideas.

James.

nbryson SSChasing Mays Points: 659 More actions · Answer 7

Good stuff - at least its not just me going mad. Just need to find a work around now.

Cheers

Nickie

-=JLK=- SSCertifiable Points: 5315 More actions · Answer 8

Ok, I found a work around. Instead of using osql I used isqlw with a -C option and specified a configuration file that I created with Query Analyzer (tools,options), on the result tab change results to text, output format to comma delimited (csv), and unleslect all the other options on the tab (especially the Print Column headers). Then return to the "General" tab and use the "Save" button to save the configuration to a file. In this case I called the file "test.sqc".

Next you have to move you "command" to a seperate .sql file (because isqlw only takes input files not straight queries).

Then you can run your command like this:

"C:\Program Files\Microsoft SQL Server\80\Tools\BINN\isqlw" -S"." -s"," -E -d"JLKTest" -i"d:\temp\test.sql" -o"d:\temp\test.out" -C"d:\temp\test.sqc"

Output comes out without the trailing blanks!

James.

nbryson SSChasing Mays Points: 659 More actions · Answer 9

Great - I have to leave work now - i may try later on from home, or first thing tomorrow, but i will let you know how i get on.

Thanks for your efforts.

Cheers

Nickie

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 10

Unlike DTS, Query Analyzer, and BCP, OSQL was NOT designed to scan the data and size the output accordingly. Since you don't want headers, BCP is the best bet. DO NOT USE ISQL!!! It is severly deprecated compared to OSQL or BCP and it will eventually bite you.

If you really want to do this using OSQL, you'll need to mod you sproc to something like this...

if exists (select 1 from sysobjects where id = object_id('uspTest_') and type = 'P') drop procedure uspTest_

go

create procedure uspTest_

as

begin

set nocount on

create table #t1 (col1 varchar(20), col2 varchar(20), col3 varchar(20))

insert into #t1 values ('123','456','789')

select col1 + ','

+ col2 + ','

+ col3

from #t1

end

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

-=JLK=- SSCertifiable Points: 5315 More actions · Answer 11

Jeff: Yep, that's simpler and better. Nice call. If he can't alter the orginal SP he shoud be able to create a second one that calls the first one and then alters the output as specified. Once I duplicated the problem I got wrapped around the axle trying to make osql work!

James.

nbryson SSChasing Mays Points: 659 More actions · Answer 12

Hi

I managed to get this set up and working using Jeffs BCP suggestion. I want to also try Jeffs OSQL method, but as usual other things get shoved up the priority list. I will try this next week.

Big Thanks to JLK and Jeff.

Nickie

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 13

Thanks for the feed back James and Nickie...

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

nbryson SSChasing Mays Points: 659 More actions · Answer 14

The bcp command I used was

SELECT @FileName = 'C:\systemwatch' + CONVERT(VARCHAR(11),GETDATE(),112) + '.csv'

SET @bcpCommand = 'bcp "my select statement" queryout "'

SET @bcpCommand = @bcpCommand + @FileName + '" -ST6SQL -T -c -t,'

Thanks

Nickie

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 15

Good choice... BCP was definitely the way to go especially since you didn't want header info. Nice job and thanks for the feedback.

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