MS-SQL Server - sp_OACreate - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

MS-SQL Server - sp_OACreate

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-05-05 : 14:55:25

Jayakumar K P writes "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.CSV'

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
---------------------------------------------------"

nr
SQLTeam MVY

12543 Posts

Posted - 2004-05-05 : 15:03:46

@object is the handle of the object created so should be non-zero.
@Result is the return code of the create and should be zero.

If you just need csv files then I would use bcp and get rid of the cursors.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources