Hi All,

I will explain you what I am doing. We are creating some Access reports and the backend is SQL Server 2000 [Stored Procedures], when the report is loading we have to create some .CSV Files automatically which should be stored in the Client Machine.

So I am creating a cursor for a Query and concatinating row by row to a Text File. Here the following code is not giving any error at all. But when I execute the sp_OACreate then Non-Zero Values are coming as Output which should not be. I want to know whether this code is correct or not. If not correct please provide me some sample code if possible.


Thanks in Advance.


with regards,
Jayakumar K P

---------------------------------------------------
Beginning of Procedure
---------------------------------------------------


CREATE PROCEDURE [dbo].[Sp_CSV]

(
@Param_Key varchar(36)
)

AS


DECLARE @StartDate DATETIME--- Declaring the Start Date Variable
DECLARE @EndDate DATETIME--- Declaring the End Date Variable
DECLARE @CHILD VARCHAR(30)
DECLARE @SEX VARCHAR(30)
DECLARE @DOB VARCHAR(30)
DECLARE @ETHNIC VARCHAR(30)
DECLARE @DISAB VARCHAR(30)
DECLARE @ALLRECORDS VARCHAR(500)
DECLARE @TextToWrite VARCHAR(500)
Declare @FileName varchar(100)
Declare @Result int
Declare @Object int
Declare @src varchar(4000)
Declare @desc varchar(4000)
Declare @FileID varchar(4000)
DECLARE @STAVALUE VARCHAR(100)
DECLARE @ENDYEAR VARCHAR(4)


-- This Query is to Get the Start Date and End Date using the Param
Key
Passed to the Report
SELECT @StartDate = Period_Start_Date, @EndDate = Period_End_Date FROM
LSR_REPORT.REPORT_PARAMETERS WHERE Parameter_Key = @Param_Key



SELECT @STAVALUE=sta_value from STANDING_DATA WHERE sta_data_type ='LACODE' and sta_end_date is null

SELECT @ENDYEAR = YEAR(@ENDDATE)

SET @FileName ='\\jayakumarkp\reports\'+@STAVALUE+@ENDYEAR+'C1.C SV'


DECLARE Child CURSOR for
SELECT
chi_903_id ,
chi_gender,
chi_dob ,
chi_ethnicity ,
chi_disability
FROM
TableA

OPEN Child
FETCH NEXT FROM Child INTO @CHILD, @SEX, @DOB, @ETHNIC, @DISAB

WHILE @@FETCH_STATUS = 0
BEGIN
IF @DISAB IS NULL
SET @DISAB = ''

SET @ALLRECORDS = @CHILD + ',' + @SEX + ',' + @DOB + ',' + @ETHNIC +','+ @DISAB +','
SET @TextToWrite = @ALLRECORDS
EXECUTE @Result = sp_OACreate 'Scripting.FileSystemObject', @Object OUT

print @Object
/*
Here @Object is displaying Non Zero Values

16711422
33488638
50265854
67043070
83820286
100597502
117374718
134151934
150929150
167706366
184483582
201260798
218038014
234815230
251592446
268369662
285146878
301924094
318701310
335478526
*/

EXECUTE @Result = sp_OAMethod @Object, 'OpenTextFile', @FileID OUT, @FileName, 8, 1
EXECUTE @Result = sp_OAMethod @FileID, 'WriteLine', NULL, @TextToWrite
FETCH NEXT FROM Child INTO @CHILD, @SEX, @DOB, @ETHNIC, @DISAB
END


CLOSE Child
DEALLOCATE Child






---------------------------------------------------
End of Procedure
---------------------------------------------------