Post reply

Export to non-standard formats

  • 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.

     

  • March 31, 2004 at 7:24 am

    #501272

    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.

  • March 31, 2004 at 7:35 am

    #501275

    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

     

  • March 31, 2004 at 7:56 am

    #501286

    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.

  • March 31, 2004 at 8:46 am

    #501304

    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.

     

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

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