June 3, 2009 at 6:00 am
Hi There
Thanks for any advice in advance.
I have a SQL server I wish to replace and it has a legacy Stored Procedure that gathers information from the database and then exports it out to a csv text file.
I have build a new SQL server and copied the Stored Procedures across, I have the enabled Ole Automation on the new Server.
When running the Stored procedure on the new server it does not create the file?
I am not quite sure where to begin with this issue. I have put a copy of the sp_appendtofile stored procedure below:
--
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[sp_AppendToFile](@FileName varchar(255), @Text1 varchar(255)) AS
DECLARE @FS int, @OLEResult int, @FileID int
EXECUTE @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @FS OUT
IF @OLEResult <> 0 PRINT 'Scripting.FileSystemObject'
--
Cheers
Dave
June 3, 2009 at 7:24 am
david.ainsworth (6/3/2009)
Hi ThereThanks for any advice in advance.
I have a SQL server I wish to replace and it has a legacy Stored Procedure that gathers information from the database and then exports it out to a csv text file.
I have build a new SQL server and copied the Stored Procedures across, I have the enabled Ole Automation on the new Server.
When running the Stored procedure on the new server it does not create the file?
I am not quite sure where to begin with this issue. I have put a copy of the sp_appendtofile stored procedure below:
--
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[sp_AppendToFile](@FileName varchar(255), @Text1 varchar(255)) AS
DECLARE @FS int, @OLEResult int, @FileID int
EXECUTE @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @FS OUT
IF @OLEResult 0 PRINT 'Scripting.FileSystemObject'
--
Cheers
Dave
did you paste your whole proc? i didn't see the sp_OAMethod to write to the file, andthat's critical i have a lot more steps to write the data than you pasted.
here's the proc I use that works fine:
CREATE function dbo.Ufn_WriteToFile
(
@FileName varchar(1000), @Text1 varchar(1000)
)
RETURNS VARCHAR(100)
AS
BEGIN
DECLARE @status VARCHAR(100), @eof VARCHAR(10)
SET @status = 'SUCCESS'
DECLARE @FS int, @OLEResult int, @FileID int
EXECUTE @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @FS OUT
IF @OLEResult 0
SET @status= 'Error: Scripting.FileSystemObject'
--Open a file
execute @OLEResult = sp_OAMethod @FS, 'OpenTextFile', @FileID OUT,@FileName, 8, 1 -- Append if required (8)
--execute @OLEResult = sp_OAMethod @FS, 'OpenTextFile', @FileID OUT,@FileName, 1 --(1) replaces the contents with the data passed does not append
IF @OLEResult 0
SET @status ='Error: OpenTextFile'
--Write Text1
execute @OLEResult = sp_OAMethod @FileID, 'WriteLine', Null, @Text1
IF @OLEResult 0
SET @status= 'Error : WriteLine'
EXECUTE @OLEResult = sp_OADestroy @FileID
EXECUTE @OLEResult = sp_OADestroy @FS
RETURN @status
END
GO
Lowell
June 3, 2009 at 7:57 am
Thank you for your assistance, there is a second Stored Procedure that runs a cursor to obtain the data to be written to the file. The only information in this SP for the file creation are:
--
SET @FilePath = 'C:\SAPExport.TXT'
SELECT @Output = 'G3,' + @BOMCode + ',' + CAST(@NewQty AS NVARCHAR(10))
EXEC sp_AppendToFile @FilePath, @Output
--
These two Stored Procedures work on the old server but not the new server.
June 3, 2009 at 8:15 am
ALTER PROCEDURE [dbo].[sp_AppendToFile](@FileName varchar(255), @Text1 varchar(255)) AS
DECLARE @FS int, @OLEResult int, @FileID int
EXECUTE @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @FS OUT
IF @OLEResult 0 PRINT 'Scripting.FileSystemObject'
if that is your whole proc, it's missing a couple of lines of code... there is no doubt.
the code never even references the variables @FileName or @Text1 , so clearly it couldn't append that to a file.
Lowell
June 4, 2009 at 6:08 am
The full stored procedure is quite lengthy, I have reduced it down to the following:
declare@DaysSMALLINT
declare @FilePathNVARCHAR(20)
Declare @OutputNVARCHAR(160)
SET @Days = -2
SET @FilePath = 'C:\SAPExport.txt'
SELECT @output = AFC.dbo.OITM.ItemCode
FROM AFC.dbo.OITM
INNER JOIN AFC.dbo.ITM1 ON AFC.dbo.ITM1.ItemCode = AFC.dbo.OITM.ItemCode
WHERE (AFC.dbo.ITM1.PriceList = 12) AND (AFC.dbo.OITM.QryGroup5 = 'Y')
AND ((AFC.dbo.OITM.UpdateDate BETWEEN DATEADD(Day, -2, GETDATE()) AND GETDATE()) )
ORDER BY AFC.dbo.OITM.ItemCode
EXEC telic.dbo.sp_AppendToFile @FilePath,@output
print @output
The Select Statement should write an Item Code into a file, it reports that the command completed successfully but no file was created.
June 4, 2009 at 6:33 am
no no no...
i mean your procedure sp_AppendToFile. the procedure that calls sp_AppendToFile is not the issue.
sp_AppendTo is incomplete.
your sp_AppendTo is an exact copy of the function i posted, except it was changed to a procedure instead of a function.
If what you posted is the "whole" procedure, it would explain why nothing gets saved to disk...
the code you posted only creates a windows handle, and never goes on to use the Filesystem object.
your WHOLE proc should look something like below, if it doesn't, then that is the reason you are having problems.
CREATE procedure dbo.sp_AppendToFile
(
@FileName varchar(1000), @Text1 varchar(1000)
)
AS
BEGIN
DECLARE @status VARCHAR(100), @eof VARCHAR(10)
SET @status = 'SUCCESS'
DECLARE @FS int, @OLEResult int, @FileID int
EXECUTE @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @FS OUT
IF @OLEResult 0
SET @status= 'Error: Scripting.FileSystemObject'
--Open a file
execute @OLEResult = sp_OAMethod @FS, 'OpenTextFile', @FileID OUT,@FileName, 8, 1 -- Append if required (8)
--execute @OLEResult = sp_OAMethod @FS, 'OpenTextFile', @FileID OUT,@FileName, 1 --(1) replaces the contents with the data passed does not append
IF @OLEResult 0
SET @status ='Error: OpenTextFile'
--Write Text1
execute @OLEResult = sp_OAMethod @FileID, 'WriteLine', Null, @Text1
IF @OLEResult 0
SET @status= 'Error : WriteLine'
EXECUTE @OLEResult = sp_OADestroy @FileID
EXECUTE @OLEResult = sp_OADestroy @FS
SELECT @status
END
GO
Lowell
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply