February 13, 2014 at 6:07 pm
Hello
I need to export a query to a .txt file in ANSI format, i came to this but the .txt format is unicode witch is imcompatible with the machine that need to read it.
I´m using SQL 2008
I´ve this stored Procedure, that i found searching the internet.
USE [val]
GO
/****** Object: StoredProcedure [dbo].[spWriteStringToFile] Script Date: 02/14/2014 01:00:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/* procedimento para guardar um string no ficheiro*/
ALTER PROCEDURE [dbo].[spWriteStringToFile]
(
@String text,
@Path VARCHAR(255),
@Filename VARCHAR(100)
--
)
AS
DECLARE @objFileSystem int
,@objTextStream int,
@objErrorObject int,
@strErrorMessage Varchar(1000),
@Command varchar(1000),
@hr int,
@fileAndPath varchar(80)
set nocount on
select @strErrorMessage='opening the File System Object'
EXECUTE @hr = sp_OACreate 'Scripting.FileSystemObject' , @objFileSystem OUT
Select @FileAndPath=@path+'\'+@filename
if @HR=0 Select @objErrorObject=@objFileSystem , @strErrorMessage='Creating file "'+@FileAndPath+'"'
if @HR=0 execute @hr = sp_OAMethod @objFileSystem , 'CreateTextFile'
, @objTextStream OUT, @FileAndPath,2,True
if @HR=0 Select @objErrorObject=@objTextStream,
@strErrorMessage='writing to the file "'+@FileAndPath+'"'
if @HR=0 execute @hr = sp_OAMethod @objTextStream, 'Write', Null, @String
if @HR=0 Select @objErrorObject=@objTextStream, @strErrorMessage='closing the file "'+@FileAndPath+'"'
if @HR=0 execute @hr = sp_OAMethod @objTextStream, 'Close'
if @hr<>0
begin
Declare
@Source varchar(255),
@Description Varchar(255),
@Helpfile Varchar(255),
@HelpID int
EXECUTE sp_OAGetErrorInfo @objErrorObject,
@source output,@Description output,@Helpfile output,@HelpID output
Select @strErrorMessage='Error whilst '
+coalesce(@strErrorMessage,'doing something')
+', '+coalesce(@Description,'')
raiserror (@strErrorMessage,16,1)
end
EXECUTE sp_OADestroy @objTextStream
EXECUTE sp_OADestroy @objTextStream
and i execute this on my ERP
SET ANSI_WARNINGS ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
DECLARE @STR NVARCHAR(MAX);
SELECT @STR = STUFF((SELECT CHAR(13) + CHAR(10) + csvFile
FROM (SELECT bo.nmdos + '|' + CONVERT(VARCHAR(5),bo.obrano,1)+ '|' +
CONVERT(VARCHAR(20),LTRIM(RTRIM(bi.ref)),1)+'|'+CONVERT(VARCHAR(8),CAST(bi.qtt AS DECIMAL(10, 0)),1) +'|4'
FROM val.dbo.bo
LEFT OUTER JOIN val.dbo.bi ON bo.bostamp = bi.bostamp
LEFT OUTER JOIN st ON bi.ref = st.ref
WHERE bo.ndos = 23 AND bo.obrano = #2# AND bi.ref <> ''
AND (st.local = 112 or st.local=143)
)a(csvFile)
FOR XML PATH(''),TYPE).value('.', 'VARCHAR(MAX)'),1,2,'');
PRINT @STR;
DECLARE @nomef VARCHAR(200);
SELECT @nomef='#2#'+'.txt'
FROM val.dbo.bo
LEFT OUTER JOIN val.dbo.bi ON bo.bostamp = bi.bostamp
LEFT OUTER JOIN st ON bi.ref = st.ref
WHERE bo.ndos = 23 AND bo.obrano = #2# AND bi.ref <> ''
AND st.local = 112 or st.local=143;
EXECUTE val.dbo.spWriteStringToFile @STR,'\\SERVER2\Movimentos\',@nomef
Please help
February 17, 2014 at 7:03 am
For this kind of thing we have used SSIS to bcp the file out and set the relevant paths and file names etc and works quite well.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply