?
Solved

Help with writing to file with Office automation via SQL

Posted on 2009-04-21
3
Medium Priority
?
307 Views
Last Modified: 2013-11-17
I have created the attached SQL code to write out to a text file.  The header part of the code writes fine to the file, but I'm not getting the detail to wrtie or the trailer.  The code runs and is syntactically correct, just can't figure out why it won't write to the file for the detail and trailer.
DECLARE 
        --CREATE HEADER RECORD
        @record_type char(2),
        @extract_dte varchar(10),
        @extract_time varchar(8),
        @trans_id char(1),
        @version char(3),
        @delimiter char(25),
        @head_del char(1),              
        
        --CREATE DETAIL RECORDS
        @id char(2),
        @cur_schl_cde char(4),
        @res_dist_cde char(9),
        @Lname varchar(60),
        @Fname varchar(60),
        @Mname varchar(60),
        @suffix varchar(10),
        @gender char(1),
        @dob varchar(10),
        @grade_lvl varchar(3),
        @HUid varchar(6),
        @ssn varchar(9),
        @ethicity varchar(1),
        @pasecID varchar(10),
        @cur_schl_yr varchar(4),
                
        --CREATE TRAILER RECORD
        @trailer_recordType char(2),
        @num_records int,
        
        --FILE SYSTEM OBJECT VARIABLE
        @FileLocation varchar(1000),
        @FileLoctxt varchar(1000),
        @FS int,
        @OLE int,
        @FileID int,
        @FileDrive varchar(10),         
        @TextRow varchar(1000)
 
        --ASSIGN VALUES TO VARIABLES
        SET @record_type = 'TH'   
        SET @extract_dte = CONVERT(VARCHAR(10), GETDATE(), 101)
        SET @extract_time = CONVERT(VARCHAR(8), GETDATE(), 108)         
        SET @trans_id = '1'
        SET @version = '1.0'
        SET @delimiter = 'delimiter=,'   
        SET @head_del = ' '
        SET @trailer_recordType = 'TT'
        SET @id = 'ID'
        SET @cur_schl_cde = '9999'
        SET @res_dist_cde = '413005206'
        SET @cur_schl_yr = '2008'
        SET @FileDrive = 'C:'
        SET @FileLocation = RTRIM(@FileDrive) + '\PaSecureID'
        SET @FileLoctxt = @FileLocation + 'paSecureID.txt'
 
EXECUTE @OLE = sp_OACreate 'Scripting.FileSystemObject', @FS OUT
IF @OLE <> 0 PRINT 'Scripting.FileSystemObject'
 
--Open a file
EXECUTE @OLE = sp_OAMethod @FS, 'OpenTextFile', @FileID OUT, @FileLoctxt, 2, 1
IF @OLE <> 0 PRINT 'OpenTextFile'
 
 
--WRITE HEADER RECORD
SET @TextRow = @record_type + ' ' + @extract_dte + ' ' + @extract_time + ' ' + @trans_id + ' ' + @version + ' '+ @delimiter
execute @OLE = sp_OAMethod @FileID, 'WriteLine', Null, @TextRow
IF @OLE <> 0 PRINT 'WriteLine'
 
--CREATE DETAIL RECORDS CURSOR
DECLARE recordCursor CURSOR READ_ONLY FOR
SELECT               
         name_master.last_name,   
         name_master.first_name,   
         name_master.middle_name,   
         name_master.suffix,
         biograph_master.gender,
         CONVERT(VARCHAR(10), biograph_master.birth_dte, 101),
         grade_cde.grade_cde,
         stud_term_sum_div.id_num,
         biograph_master.ssn,
         biograph_master.ethnic_group    
          
FROM     stud_term_sum_div,   
         name_master, biograph_master, grade_cde 
WHERE    ( stud_term_sum_div.id_num = name_master.id_num ) and
         ( stud_term_sum_div.id_num = biograph_master.id_num) and 
         ( stud_term_sum_div.class_cde = grade_cde.class_cde) and 
         ((stud_term_sum_div.div_cde = 'UG') and 
         (stud_term_sum_div.yr_cde = '0809') and
         (stud_term_sum_div.trm_cde = 'SP')and 
         (stud_term_sum_div.num_of_crs > 0))
  
 
--WRITE DETAIL RECORDS
OPEN recordCursor
FETCH NEXT FROM recordCursor INTO
        @Lname, @Fname, @Mname, @suffix, @gender, @dob,
        @grade_lvl, @HUid, @ssn, @ethicity
While @@Fetch_Status = 0
Begin
SET @num_records = 0
SET @TextRow = @id + ',' + @cur_schl_cde + ',' + @res_dist_cde 
        + ',' + @Lname + ',' + @Fname + ',' + @Mname + ',' + @suffix
        + ',' + @gender + ',' + @dob + ',' + @grade_lvl + ',' + @HUid
        + ',' + @ssn + ',' + @ethicity + ',' + @pasecID + ',' + @res_dist_cde
        + ',' + @cur_schl_yr
        
        execute @OLE = sp_OAMethod @FileID, 'WriteLine', Null, @TextRow
        
        IF @OLE <> 0 PRINT 'WriteLine'
        SET @num_records = @num_records + 1
FETCH NEXT FROM recordCursor INTO
        @Lname, @Fname, @Mname, @suffix, @gender, @dob,
        @grade_lvl, @HUid, @ssn, @ethicity
End
        Close recordCursor
        Deallocate recordCursor
 
--WRITE TRAILER RECORD
SET @TextRow = @trailer_recordType + ' ' + @trans_id + ' ' + @num_records
execute @OLE = sp_OAMethod @FileID, 'WriteLine', Null, @TextRow
IF @OLE <> 0 PRINT 'WriteLine'
 
--CLOSE FILES
 
 
EXECUTE @OLE = sp_OADestroy @FileID

Open in new window

0
Comment
Question by:jasonbrandt3
  • 2
3 Comments
 
LVL 25

Accepted Solution

by:
reb73 earned 1500 total points
ID: 24218808
I tried running your code with a few adaptations to suit a parent/child relationship structure and got a problem with line 122 where everything was attempted to be cast as a number because @num_records is of type int. When I corrected it the file was created with header/detail/trailer lines as expected..

Try changing line 122 as follows -

SET @TextRow = @trailer_recordType + ' ' + @trans_id + ' ' + CAST(@num_records as varchar(10))


0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24222118
Yep, typecasting will cause problems. Detail lines are being dynamically converted as characters / strings  into the variables, so, need to check all other vaiables being strung together.  Also, any NULLs would kill the concatenation as well.

Do you need to use OA procedures to output your CSV file ?  
Could probably do via a view and BCP if you are interested...

0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24225586
Just noticed, you do not actually close the file after writing. Need to do that...


execute @OLE = sp_OAMethod @FileID, 'Close'
IF @OLE <> 0 PRINT 'Close'
 

Then you can destroy your object at the very end...
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
In real business world data are crucial and sometimes data are shared among different information systems. Hence, an agreeable file transfer protocol need to be established.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question