about xp_cmdshell..

Hello there!
I'm using xp_cmdshell in a loop to write in a file...
do you know better way to improve effiently writing on disk in a loop?
or any recommandations on xp_cmdshell uses (could we switch off the
msg feedbacks ?)
thanks a lot
++
VinceDTS, BCP, COM or .NET all seem likely to be more efficient ways of
writing to a file. What type of file are you trying to write?
David Portas
SQL Server MVP
--|||On 31 Mar 2005 01:18:03 -0800, "David Portas"
<REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote:

>DTS, BCP, COM or .NET all seem likely to be more efficient ways of
>writing to a file. What type of file are you trying to write?
I'm trying to write csv files. this, in a loop cursor which 'travels'
over 4 millions records so far...I mean, I must write row after row
because some of them are being value-changed during looping ...So I
could not use of BCP...
but what about DTS? can we use it in a loop?
thanks
Vince|||I don't understand why you think you need to do this cursor-style. Why can't
you do a set-based UPDATE for whatever data changes you require and THEN use
DTS, BCP or whatever other method to do the export?
Yes, you can export data row-by-row using DTS, but that doesn't necessarily
mean that's the best way to do it. BCP is probably a better choice for
millions of rows of data.
Comma-separated files are a poor medium for data at the best of times. For 4
million rows it seems like there ought to be a better way. What is the targe
t
system for the exported data? Can't it support other more structured file
formats? If you must delimit, avoid commas and use TAB or pipe characters
instead.
David Portas
SQL Server MVP
--|||Hello,
xp_CmdShell is not an efficient way of writing to a file. Instead you
can try with some Active-X Dll which can be instantiated by SQL Server
using sp_OACreate call. This may be a better way of doing "writing to
an external file" especially when there are large number of rows.
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D SQL Snippet
Exec @.hr =3D sp_OACreate @.tProgId, @.iobToken Output
If @.hr <> 0 Goto TrapOAErrors
Exec @.hr =3D sp_OASetProperty @.iobToken, N'FileName', @.strFileName
If @.hr <> 0 Goto TrapOAErrors
Exec @.hr =3D sp_OAMethod @.iobToken, N'OpenFileForWriting'
If @.hr <> 0 Goto TrapOAErrors
--
--
Exec @.hr =3D sp_OAMethod @.iobToken, N'WriteBufferToFile', @.bytesBuffer
If @.hr <> 0 Goto TrapOAErrors
--
--
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3DSQL Snippet
Snippet VB Active-X DLL --
'--=3D=3DFunction for Opening file for Reading=3D=3D=BB
Public Function OpenFileForWriting()
hOrgFile =3D CreateFile(m_sFileName, GENERIC_READ, FILE_SHARE_READ Or
FILE_SHARE_WRITE, ByVal 0&, OPEN_EXISTING, 0, 0)
If hOrgFile <> 0 Then
SetFilePointer hOrgFile, 0, 0, FILE_BEGIN
End If
End Function
'--=3D=3DFunction for: Closing the File Handle=3D=3D=BB
Public Function CloseFile()
CloseHandle hOrgFile
'--=3D=3DClose the file=3D=3D=BB
End Function
'--=3D=3DFunction for Retrieving the FileReadBuffer=3D=3D=BB
Public Function WriteBufferToFile(tBuff() As Byte)
lRet As Long
Dim m_lOffset As Long
Err.Clear
'--=3D=3DClear the Error Device=3D=3D=
=BB
If hOrgFile =3D 0 Then
OpenFileForWriting
If hOrgFile =3D 0 Then
ReadBlockIntoBuffer =3D ""
Return
End If
End If
WriteFile hOrgFile, tBuff(1), UBound(tBuff), Ret, ByVal 0&
'--=3D=3DCheck for errors=3D=3D=BB
If lRet <> UBound(bBytes) Then 'Failed to READ the whole file
ErrorFlag =3D True
ErrorNumber =3D Err.Number
ErrorDesc =3D Err.Description
End If
End Function
Snippent Ends ---
The Above snippet is not complete and may not work :-)
Rgds ...
--Bju.|||If you have the option of including this in a .Net project, I would
recommend a nice normal select statement, using a sql reader, loop over
the reader and perform your row by row logic changes, and use my csv
parser/writer to create the file. The writer is not free however, but
is relatively cheap. At worst, I'd still recommend basically the same
way of doing it, using another way of creating the file in place of my
writer. The writer itself is going to create files at roughly 10 mb per
second.
http://www.geocities.com/shriop/index.html