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.(Although 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(count(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(STR(HoursWorked,9, 5), SPACE(1), '0') AS nchar(9)), HRAccountCode, CAST(REPLACE(STR(EmployeePayRate,18, 6), SPACE(1), '0') AS nchar(18)), 'A_STUDSUM', HRAccountCodeOverride
FROM db_owner.PS_HR_Hrs
WHERE reported is NULL
to get:
Header24HRBTCH
10068800 2007/06/04STSSH012.00000 A108145 00000000007.500000 A_STUDSUM
10068800 2007/06/05STSSH002.00000 A108145 00000000007.500000 A_STUDSUM ...