all rows from one table in a Varchar(max)

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 2008 Forums

Transact-SQL (2008)

all rows from one table in a Varchar(max)

Author

Topic

Clages1
Yak Posting Veteran

69 Posts

Posted - 2013-10-04 : 08:12:07

Hi, i have a table named Dec_Tmpnfe20 like this

REG CHAR(500)
AAASASDAD
VBVBVB
FSGDFSDF
1234
AJSSSS

Below i have working fine a SP that get all this rows, and
put in @Text1 =varchar(max)

then i get @Text1 = AAASASDAD VBVBVB FSGDFSDF 1234 AJSSSS

then i get @Text1 and create a TXT file

the questions is:

is there a Better way to do this?

tks
Carlos Lages
Dec

alter PROCEDURE [dbo].[Dec_AppendToTxtFile]
(@FileName varchar(255) ,
@ret char(7) output
)
AS

DECLARE @FS int,
@OLEResult int,
@FileID int,
@Text1 varchar(max)

set @Text1 = ''
set @Ret = 'OK'

---This cursor read each row from table Dec_Tmpnfe20 (has only one column char(500)
-- and Append this row in a variable @Txt1 that could be 2.000.000 character

DECLARE @reg Varchar(2000)

DECLARE cursorx cursor for

SELECT rtrim(reg) FROM Dec_Tmpnfe20

OPEN cursorx

FETCH NEXT FROM cursorx
INTO @reg

WHILE @@FETCH_STATUS = 0
BEGIN

SELECT @Text1 = @Text1 + CAST(@reg AS VARCHAR(MAX))

FETCH NEXT FROM cursorx
INTO @reg

END

CLOSE cursorx
DEALLOCATE cursorx

-- here is the End of Cursor, @text1 must be all rows from all rows of Dec_Tmpnfe20

--- as rotinas abaixo simplesmentes criam o arquivo xxxx.txt com conteudo de @Text1

--the routine below just get @Text1 and create a TXT file

EXECUTE @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @FS OUT

IF @OLEResult <> 0
BEGIN
SET @ret = 'CREATE'
goto fim
END

-- Delete the TXT file First
execute @OLEResult = sp_OAMethod @FS , 'DeleteFile', NULL , @FileName

--Open a file
execute @OLEResult = sp_OAMethod @FS, 'OpenTextFile', @FileID OUT, @FileName, 8, 1

IF @OLEResult <> 0
BEGIN
SET @ret = 'OPEN'
goto fim
END

--Write Text1
execute @OLEResult = sp_OAMethod @FileID, 'WriteLine', Null, @Text1

IF @OLEResult <> 0
BEGIN
SET @ret = 'WRITE'
goto fim
END

FIM:

EXECUTE @OLEResult = sp_OADestroy @FileID
EXECUTE @OLEResult = sp_OADestroy @FS

print @ret
return

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2013-10-04 : 08:30:01

select @Text1 = coalesce(@Text1,'') + CAST(@reg AS VARCHAR(MAX)) from Dec_Tmpnfe20

should produce the string rather than the cursor processing.
But don't you want a delimiter between rows?

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

Clages1
Yak Posting Veteran

69 Posts

Posted - 2013-10-04 : 09:42:26

Tks, in basic Tests is working fine

But don't you want a delimiter between rows? NO
because in fact, the table has

<infCpl>reserva</infCpl>
</infNFe></NFe></enviNFe>
etc

when i concatenate all rows, i get the full XML

carlos Lages
Dec

by the way is there a easy way to send @Text1 to a TXTfile?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-04 : 10:22:35

quote:
Originally posted by Clages1

Tks, in basic Tests is working fine

But don't you want a delimiter between rows? NO
because in fact, the table has

<infCpl>reserva</infCpl>
</infNFe></NFe></enviNFe>
etc

when i concatenate all rows, i get the full XML

carlos Lages
Dec

by the way is there a easy way to send @Text1 to a TXTfile?

yep..you can use bcp

http://technet.microsoft.com/en-us/library/ms162802.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources