suppressing osql column padding
-
Hi All,
A newbie here.
I'm using osql to generate a comma delimited file but the column data is padded with spaces. Would like to get the same formatting as when saving Query Analyzer's grid results as a .csv - namely that all padding is suppressed. Have googled until brain dead.
Any and all help is greatly appreciated.
-
char() columns will pad the data with blanks. An easy solution would be to use the RTRIM() function to remove the spaces.
-
or mess with ansi padding
declare @tb table (col1 int identity(1,1) primary key
, mychar10 char(10) not null default('a')
, myvarchar10 varchar(10) not null default('b') );
set nocount on;
insert into @tb default values;
insert into @tb default values;
insert into @tb default values;
insert into @tb default values;
insert into @tb default values;
insert into @tb default values;
set nocount off;
SET ANSI_PADDING ON ;
select cast(col1 as varchar(10))
+ ',' + mychar10
+ ',' + cast(mychar10 as varchar(10))
+ ',' + myvarchar10
+ ',' + rtrim(mychar10) + ', end'
from @tb;
SET ANSI_PADDING OFF ;
select cast(col1 as varchar(10))
+ ',' + mychar10
+ ',' + cast(mychar10 as varchar(10))
+ ',' + myvarchar10
+ ',' + rtrim(mychar10) + ', end'
from @tb;
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
It's looking like I don't have much choice but to build the output using rtrim()+",". Didn't want to have to resort to this but it seems osql uses a predefined column size regardless of the actual size in the table. In that same vein ansi_padding doesn't help either (and don't want to have to rebuild the tables either - call me lazy). It would have been nice to use the same queries built for Query Analyzer but alas.
Thanks for you help guys!
-
Did you take a look as to how DTS may help you to solve this problem ?
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Not yet. Started reading up but it seemed like something I just don't have the time to play with and figure out at the moment - seems to have a lot of bells and whistles. Eventually though cause it does sound promising.
-
it's _the_ way for importing / exporting data.
Even the wizard is intuitive
EM (enterprise manager) \ databases \tables got to the righthand side, right-click and choose ALL tasks \ import or Export data
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
have used the wizard a couple of times and must agree it's very friendly. however, i'm involved in a conversion/consolidation project that is centralizing several community colleges. we're having to reload our target databases constantly as we treak the process so it would be nice to run these extracts via something like a bat file - one click and i'm done - which is why i was looking at osql. does dts have a command line interface callable from a bat file? hate asking this question w/o doing more digging but since i have your ear...
-
Yes it has.
In fact, you can prepare and save your package (if you used the wizard, you must have seen the panel where it asks "run immediate , save as ..." .
This saved package can then be scheduled as a sqlagent job and run frequently.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Excellent! Just never made the connection between "save as" and running it as an agent. Like I said, I'm a rookie. It appears I have some reading to do this weekend. Enjoy and thanks much!
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply