SunnyD


Hi,

I'm currently trying to export a single column of data to a text file, I'm having difficulties as the text length is a varchar(2000) and contains '>' and '<'. I've tried outputting via xp_cmdshell but I think I have problems as the cmd length ends up greater than 255, I've tried iSQL to a text file but only get the first 255 characters and have tried a datapump export but again only get the first 255 characters. Are there any setting to remove this limit on the latter two solutions or is there a better method to accomplish this

Im using SQL Server 2000.

Any ideas greatly appreciated.

Thank you,




Re: Output to text file

Manivannan.D.Sekaran


You can use the following command,

Code Snippet

xp_cmdshell 'bcp "Your Query" queryout "C:\Data.txt" -c -q -S"ServerName" -Usa -Psqladmin'







Re: Output to text file

SunnyD

I did look at this however due to the nature of the data I can't have credentials referenced in code...




Re: Output to text file

Arnie Rowland

If you were to look up 'bcp Utility' in Books Online, you would notice that you can use the -E switch instead of -U -P.

(Of course, that assumes you are using Windows Authentication.)






Re: Output to text file

kwodiska

Sunny,

You should be able to use DTS if you want

Ken





Re: Output to text file

rusag2

set nocount on

--This approach will parse your big string into several

--smaller strings, then you select the several smaller strings

--into your Text File. Finally, you edit the result file

--by removing the column headings, and replacing

--the (<NEWLINE> + CarriageReturn) with ''.

--This is not a pretty solution, but it works.

create table #nTemp (Big_Ol_String ntext)

create table #strOutput (MyField nchar(240))

/*

CODE HERE TO POPULATE #nTEMP

With your query

*/

declare @nTemp nchar(240)

declare @i bigint

set @i = 1

StartLoop:

select @nTemp = substring(Big_Ol_String, @i, 240) from #nTemp

if len(@nTemp) > 0

begin

insert into #strOutput

select @nTemp

set @i = @i + 240

goto StartLoop

End

--Now, dump THIS query's results

--into your text file:

select MyField + '<NEWLINE>' from #strOutput

--Then, in the output file, REPLACE ('<NEWLINE>' + CrLf) with ''

--Of course, you'll need a tiny tweak for the file headings...nocount etc.

drop table #nTemp

drop table #strOutput