May 16, 2007 at 5:52 am
Hi
I have the following osql statement...
SELECT @FullPathFileName = 'C:\systemwatch' + CONVERT(VARCHAR(11),GETDATE(),112) + '.csv'
SET @StrRights = 'osql -Sxxx -dxxx -E -q"dbo.systemwatchrun" -s"," -h-1 -n -o"' + @FullPathFileName +'"'
The problem is with the output putting in trailing spaces e.g.
311250A ,Y144H ,20070514,20070514,C5 ,11 ,10 ,19501231,3112503155,18057 ,DG9 7SE ,22242 ,2 ,1349849
I cant find a way to get rid of them.
Does anybody have any ideas.
Nickie
May 16, 2007 at 6:38 am
It would be more helpful to see the Stored Procedure definition then the manner in which it is executed. Do you have the ability to edit the Stored Procedure? If so then your should alter the final select statement (or what ever produces the actual output) and place the "rtrim" command around each column that is giving you trouble.
For example if you final select statement is:
Select x, y, z from tablename
Then change it to:
Select rtrim(x) as x, rtrim(y) as y, rtrim(z) as z
HTH,
James.
May 16, 2007 at 6:41 am
Nickie,
Isn't there a part of your query missing: -q"dbo.systemwatchrun"?
Anyway, I would just use RTRIM's in the attributes. The padding with blanks has to do with your ANSI settings; but the ANSI settings that where active when creating the table. So, if you would not want spaces to be added, you would have te recreate the table. And this could possibly affect the behaviour of existing queries.
Simplest is just to use the RTRIMs over here!
regards,
Jan
May 16, 2007 at 6:47 am
Hi
Already tried rtrim and it doesn't have any affect.
The query is just calling a stored procedure called systemwatchrun and I do have the rights to edit the SP.
I've also just tried to create a new table with all the fields as varchars and insert the result set to that and then use this as the output of the sp and it still pads the spaces.
Regards
Nickie
May 16, 2007 at 7:05 am
What you get is a result of SP execution.
In order to change that result you need change SP.
If you cannot change this SP then create another one which will be a copy of systemwatchrun and apply RTRIM inside of that SP.
If you cannot do anything then you can do nothing.
_____________
Code for TallyGenerator
May 16, 2007 at 7:07 am
Without seeing the entire script I can only give a suggestion rather than a solution. First I would talk with the DBA/Developer of the SP and see if you can get it altered, or a second comparable SP created (that would be the easiest). Otherwise My suggestion is you work the following into your script:
Create a table that looks like your expected output, for example
"Create table #t1 (col1 varchar(100), col2 varchar(100), etc ...)"
Then execute the SP using the following:
insert into #t1 execute dbo.systemwatchrun
Then your final step is to select rtrim(col1), rtrim(col2), etc.... from #t1.
Now your output from the script will be as you want it.
Not the most eloquent solution but it should work.
James.
May 16, 2007 at 7:08 am
Yes, sorry I missed the fact that it is a stored procedure because there is not EXEC but that is not mandatory of course.
Just another idea if you really have the constraint that you cannot change the sp: you could consider creating a linked server to your own server and execute SELECT( trim(colum),... FROM OPENQUERY(linkedserver, 'EXEC dbo.systemwatchrun').
It is a bit bizarre to reconnect to your own server, but it should work.
Jan
May 16, 2007 at 7:15 am
Hi
I have sa on the server and I can change the procedure. I have tried rtrim on the final select, I have also tried outputting the results to a new table with all columns varchars and tried rtrim ltrim and returning that. But still the same results.
Wierd
May 16, 2007 at 7:18 am
It would be helpful to see the actaul SP. Try adding -O (that is a capital O as an additional parameter to the osql call. That option prevents some screen formating. Can't hurt though probably won't help.
May 16, 2007 at 7:19 am
It seems to me that whatever the size of the columns regardless of the data within them it outputs that size. e.g. if i have varchar(10) and the field is only populated with 5 chars then it will output the 5 chars and pads out 5.
Cheers
Nickie
May 16, 2007 at 7:31 am
I can assure you: it's 100% not right.
I lost count of my reports created using bcp. And only case when I've got pads is when I've got CHAR or NCHAR values returned.
Something wrong in that SP.
_____________
Code for TallyGenerator
May 16, 2007 at 7:52 am
ok - i'll post the last part of the procedure .... but even worse now that I have created nvarchar(100) is that the padding is now much bigger (my guess is due to the size of the columns) e.g.
010122T
,
Y104H
,
20070427
,
20070502
,
AB
,
18
,
40
,
19220101
,
0101223560
,
18112
,
DG9 7BY
,
20467
,
1
,
2490667
I've created this table just for testing this problem
create table systemwatchrunoutput
(
column1 nvarchar(100),
column2 nvarchar(100),
column3 nvarchar(100),
column4 nvarchar(100),
column5 nvarchar(100),
column6 nvarchar(100),
column7 nvarchar(100),
column8 nvarchar(100),
column9 nvarchar(100),
column10 nvarchar(100),
column11 nvarchar(100),
column12 nvarchar(100),
column13 nvarchar(100),
column14 nvarchar(100)
)
And the final select of the procedure is
select rtrim(column1),
rtrim(column2),
rtrim(column3),
rtrim(column4),
rtrim(column5),
rtrim(column6),
rtrim(column7),
rtrim(column8),
rtrim(column9),
rtrim(column10),
rtrim(column11),
rtrim(column12),
rtrim(column13),
rtrim(column14)
from systemwatchrunoutput
order by column1
May 16, 2007 at 8:00 am
Of course, you have just created a new table with the same ANSI settings and ansi padding on.
I repeat this trick: a linked server to your own server and execute SELECT( trim(colum),... FROM OPENQUERY(linkedserver, 'EXEC dbo.systemwatchrun')
It works and is simpler.
May 16, 2007 at 8:07 am
ok - i'll give that a whirl and let you know how I get on.
May 16, 2007 at 8:36 am
OK - did that but have the same results - i've tried using set ansi_padding off etc but to no avail. Anything else I should try on my server.
Viewing 15 posts - 1 through 15 (of 32 total)
You must be logged in to reply to this topic. Login to reply