Ecriture .TXT à partir de T SQL

Tu peux toujours essayer avec xp_cmdhsell

genre:

@str='montexte'
exec master.dbo.xp_cmdhsell 'echo '+@str+'> c:monfichier.txt'



"robbiew33" a écrit :

A partir du procédure stockée où je retourne une chaine, Comment puis-je
écrire dans un fichier texte (.txt)

Merci.

Bonjour,

Autre solution :
DECLARE @FS int
DECLARE @FileID int

EXEC tp_File_OpenFile 'C:SQLtest.txt', @FS OUTPUT, @FileID OUTPUT

EXEC tp_File_WriteFile @FileID, '1. ceci est un test'
EXEC tp_File_WriteFile @FileID, '2. ceci est un autre test'

EXEC tp_File_CloseFile @FS, @FileID

avec les procédures suivantes :

<<<

/*-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
<StoredProcedure name="tp_File_CloseFile">

<Object> That store procedure is used to close an opened file
</Object>

<History Author = "Philippe TROTIN - ptrotin@microsoft.com" Date =
"07/01/2004"> Core 2.1 </History>

</StoredProcedure>
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------*/
CREATE PROCEDURE tp_File_CloseFile
(
@FS int, -- File system object reference
@FileID int -- File id reference
)
AS
BEGIN
SET NOCOUNT ON

DECLARE @OLEResult int

-- Close file
EXECUTE @OLEResult = sp_OADestroy @FileID
EXECUTE @OLEResult = sp_OADestroy @FS

END
GO

/*-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
<StoredProcedure name="tp_File_OpenFile">

<Object> That store procedure is used to open a file </Object>

<History Author = "Philippe TROTIN - ptrotin@microsoft.com" Date =
"07/01/2004"> Core 2.1 </History>

<Sample>
DECLARE @FS int
DECLARE @FileID int

EXEC tp_File_OpenFile 'C:SQLtest.txt', @FS OUTPUT, @FileID OUTPUT

EXEC tp_File_WriteFile @FileID, '1. ceci est un test'
EXEC tp_File_WriteFile @FileID, '2. ceci est un autre test'

EXEC tp_File_CloseFile @FS, @FileID
</Sample>

</StoredProcedure>
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------*/
CREATE PROCEDURE tp_File_OpenFile
(
@FileName nvarchar(250), -- Name of the file to create
@FS int OUTPUT, -- File system object reference
@FileID int OUTPUT -- File id reference
)
AS
BEGIN
SET NOCOUNT ON

DECLARE @OLEResult int

-- Create scripting object
EXECUTE @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @FS OUT
IF @OLEResult <> 0 RETURN -1

--Ouvre le fichier (2 = ForWriting, 8 = ForAppending)
EXECUTE @OLEResult = sp_OAMethod @FS, 'OpenTextFile', @FileID OUT,
@FileName, 2, 1
IF @OLEResult <> 0 RETURN -1

END
GO

/*-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
<StoredProcedure name="tp_File_WriteFile">

<Object> That store procedure is used to write info in an opened file
</Object>

<History Author = "Philippe TROTIN - ptrotin@microsoft.com" Date =
"07/01/2004"> Core 2.1 </History>

</StoredProcedure>
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------*/
CREATE PROCEDURE tp_File_WriteFile
(
@FileID int, -- File id reference
@Text nvarchar(4000), -- Text to write
@NewLine int = 1
)
AS
BEGIN
SET NOCOUNT ON

DECLARE @OLEResult int

-- Write the text in the file
IF @NewLine = 1
BEGIN
EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', Null, @Text
END
ELSE
BEGIN
EXECUTE @OLEResult = sp_OAMethod @FileID, 'Write', Null, @Text
END

IF @OLEResult <> 0 RETURN -1

END
GO

----------------------------------------------------------------------
Philippe TROTIN - Microsoft Service France

"Julien" <Julien@discussions.microsoft.com> wrote in message
news:CAC9F139-134A-4751-A71E-B44FA12C1510@microsoft.com...

Tu peux toujours essayer avec xp_cmdhsell

genre:

@str='montexte'
exec master.dbo.xp_cmdhsell 'echo '+@str+'> c:monfichier.txt'



"robbiew33" a écrit :

A partir du procédure stockée où je retourne une chaine, Comment puis-je
écrire dans un fichier texte (.txt)

Merci.