February 29, 2008 at 2:08 pm
I have a procedure that builds an @Body varchar(4000) parameter. It involves a few cursors and lots of other params.
i.e.
Create Proc s_Proc
@body varchar(4000) out
as
SET @Body = @OrderNumber + ',' + @Company +','+ @CompanyLocation+'^' + char(13)
The cursor loops thru this and creates a "Body" that if printed would look like this:
32115,TestCompany,New York^
33990,ABCCompany,Chicago^
55666,XYZCo.,Miami^
I can execute the proc and it looks great! Everything is formatted correctly.
exec dbo.s_Proc @Body out
Print @Body
I'm attempting to create a txt file that contains this "body". So far I have a few options that I've played around with.
1- DTS
2- sp_OaCreate and sp_OAMethod
I am not real familiar with either in regards to creating txt files.
I tried a DTS package but it seems that the file has to be delimitted (which it's not) or
it has to be fixed width (which it's not).
I've played around with some existing code of sp_OaCreate and sp_OaMethod but I'm not familiar with the sp_OaMethod "methods".
I've noticed a few methods from existing code that refer to 'WriteLine' and I guess this appends to an existing file line by line.
Is there a method that can just write a whole file formatted the way I want with the @Body param?
Any one have any suggestions or links? Thanks.
February 29, 2008 at 2:27 pm
Okay so I found out some more info about sp_OACreate and the methods...
I was able to write a txt file with this using my @Body output param. The only problem I have is that the @Body is written on 1 line and not formatted with the Carriage Returns that I need.
Can that be done with one sp_OAMethod 'WriteLine' command? or do I have to calling 'WriteLine' to append to it?
February 29, 2008 at 2:35 pm
Okay I found the answer to the next line problem... I wasn't including Char(10) with my CHar(13) at the end of my lines.
Char(13) will produce a new line in SQL Print commands but it won't in TXT Files.
I had to put Char(13) + Char(10) at the end.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply