Export to non-standard formats

Question

Post reply

Export to non-standard formats

rschaeferhig

SSCrazy Eights

Points: 8776
More actions
March 31, 2004 at 5:00 am

#84549

I posted this in the DTS Forum (http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=19&messageid=109100) since my original approach was DTS, but since there haven't been any responses I'm reposting here as a more general question.
I have an SP that extracts four fields from a database based on parameters. I need to take the resultant rowset and export it to a flat file in fixed-length field format with a space between the fields. DTS supports fixed-length, but without the space, or standard delimited formats for text-output connections.
Is there another way to do this? (I've tried using ActiveX in DTS to loop through the recordset from the SP with no success there either. All code posted in the above DTS post).
I'm in a time crunch on this and would appreciate any help anyone can provide.
Thanks in advance.

Viewing 5 posts - 1 through 4 (of 4 total)

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

David Burrows SSC Guru Points: 65142 More actions · Answer 1

You can do it in dts to a fixed length file by selecting the data and adding extra columns for the spaces, e.g

SELECT col1,

' ' as Space1,

col2,

' ' as Space1,

col3,

' ' as Space2,

col4

FROM

then match each field to the output and you have fixed length data separated by a space.

Far away is close at hand in the images of elsewhere.
Anon.

rschaeferhig SSCrazy Eights Points: 8776 More actions · Answer 2

The problem is not how to format the output, the problem is I can't get at my @#%$ data. I tried DTS with a SQL Task that executed my SP and put the output in a Rowset Global Variable, then an ActiveX task that read the Rowset GV. That didn't work, it said the recordset didn't exist. The code was copied almost line-for-line from MSDN!

Then I tried the following ActiveX script. It didn't work either, kept telling me the recordset was closed. I added the Msgbox to display the recordset state and it is, in fact, closed! If I run the SP in the sqlString variable in QA I get back records. When I run it here it doesn't work. Again, code copied from MSDN, just changed the server name to my server and the SQL to execute my SP.

Note: as noted in my first post if I change the SQL from executing an SP to a simple SELECT the ActiveX task works. Is there some magic to using an SP in this mode?

Here's the code:

'**********************************************************************

' Visual Basic ActiveX Script

'************************************************************************

Function Main() dim countr dim conn dim RS set conn = CreateObject("ADODB.Connection") cnString = "Provider=SQLOLEDB.1;Data source=CORPSQL001;Initial Catalog=ETSBatch;Integrated Security=SSPI;" conn.open cnString
msgbox "Conn State: "&cstr(conn.state)&":" <== Indicates conn is open set RS = CreateObject("ADODB.Recordset") sqlString = "EXEC dbo.Mainframe_Extract_LateStart 'HCO' " <= works in QA!! RS.Open sqlString, conn msgbox "RS State: "&cstr(rs.state)&":" <== Indicates RS is closed While (NOT RS.eof) MsgBox "The JobName is " & RS.Fields("JobName").value RS.MoveNext Wend RS.Close Main = DTSTaskExecResult_Success End Function

David Burrows SSC Guru Points: 65142 More actions · Answer 3

Why are you using activex.

Change your proc (dbo.Mainframe_Extract_LateStart) to include the spaces

In the DTS Transform Data Task specify the query as

EXEC dbo.Mainframe_Extract_LateStart 'HCO'

p.s. if your proc does anything with temp tables or multiple selects you will need to use

SET NOCOUNT ON

to create only one output recordset

Far away is close at hand in the images of elsewhere.
Anon.

rschaeferhig SSCrazy Eights Points: 8776 More actions · Answer 4

SET NOCOUNT ON!!!

I can't believe I forgot that...

Including the spaces in the output from the SP was a stroke of genius (OK, perhaps obvious, but I didn't see that one either). Now I can map the fields from the SP directly to the text output connection.

Thanks David.