BCP out data to text files using SQL 2000

I am exporting data from a table with one column (varchar
255), to a flat text file using BCP out utility. If the
table contains around a 1000 or more rows sometimes the
records in the text file is not generated is the same
order as it was in the table.
I know if I add a clustered index column to the table and
use a select query I probably could get the results I
need. But I would like to keep that as a last resort, as
it would involve many stored procedure changes.
Note: not as much of a problem with SQL 6.5. After
upgrading to SQL 2000 it has become much more of an issue.
Thanks
Adding a clustered index does not guarantee data order from a select, bcp
etc. You must specify an ORDER BY clause to read the data in the proper
order. With that said you may want to ensure you have MAXDOP set to 1 if
you have a multiprocessor system when importing or exporting to get the best
chance of it going into the file in that order. Ideally you should have
some column that determines the proper order if that is important.
Andrew J. Kelly SQL MVP
"BC" <anonymous@.discussions.microsoft.com> wrote in message
news:bdbf01c479b3$d8a9f3a0$a501280a@.phx.gbl...
> I am exporting data from a table with one column (varchar
> 255), to a flat text file using BCP out utility. If the
> table contains around a 1000 or more rows sometimes the
> records in the text file is not generated is the same
> order as it was in the table.
> I know if I add a clustered index column to the table and
> use a select query I probably could get the results I
> need. But I would like to keep that as a last resort, as
> it would involve many stored procedure changes.
> Note: not as much of a problem with SQL 6.5. After
> upgrading to SQL 2000 it has become much more of an issue.
> Thanks
|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> schrieb im Newsbeitrag
news:OV6JPbbeEHA.4068@.TK2MSFTNGP11.phx.gbl...
> Adding a clustered index does not guarantee data order from a select,
bcp
> etc. You must specify an ORDER BY clause to read the data in the proper
> order. With that said you may want to ensure you have MAXDOP set to 1
if
> you have a multiprocessor system when importing or exporting to get the
best
> chance of it going into the file in that order. Ideally you should have
> some column that determines the proper order if that is important.
Additional remark: OP should never rely on the order of data in a table.
AFAIK there is no guarantee that data is drawn from a table with a certain
order other than specifying the desired order. Even if there is a
clustered index, you should explicitely specify the order. After all,
relational databases manage record sets rather lists of records.
Kind regards
robert

> --
> Andrew J. Kelly SQL MVP
>
> "BC" <anonymous@.discussions.microsoft.com> wrote in message
> news:bdbf01c479b3$d8a9f3a0$a501280a@.phx.gbl...
>