Hi!
I'm new to sql 2005 and need to run an automated report that is a fairly simple fixed width ragged right report. Problem is I need a header in the report that has static text, plus the rowcount of the report.
I am using an SSIS package to call the script. I think that's the way to go because I need it to run on Friday's at 3:00. Then I'll have a scheduled event to fire it off via sFTP from my server.(Althoug h Im opent to ANY Suggestions)
Is there a way to write out the header in the one tsql statement, then write out the second select to the same file? Or do I need to set up SSIS to run 2 TSQL scripts (The header and the report) and write them out to the same file?
I gotta think it SHOULD be as easy as
SELECT 'Headerrowcount '+ltrim(Str(cou nt(UserName))) + 'HRBATCH' AS header
FROM db_owner.PS_HR_ Hrs
WHERE Reported is NULL
Then do some sql magic code here to append the following select file
SELECT EmplID, Convert(VarChar ,DateWorked,111 ),'STSSH', CAST(REPLACE(ST R(HoursWorked,9 , 5), SPACE(1), '0') AS nchar(9)), HRAccountCode, CAST(REPLACE(ST R(EmployeePayRa te,18, 6), SPACE(1), '0') AS nchar(18)), 'A_STUDSUM', HRAccountCodeOv erride
FROM db_owner.PS_HR_ Hrs
WHERE reported is NULL
to get:
Header24HRBTCH
10068800 2007/06/04STSSH012.0000 0 A108145 00000000007.500 000 A_STUDSUM
10068800 2007/06/05STSSH002.0000 0 A108145 00000000007.500 000 A_STUDSUM ...