Bulk Insert Runs Twice!

I have a simple Bulk Insert statement I want to run in a stored procedure.
Here it is:
Set @.bulk_cmd = 'BULK INSERT MyTable
FROM ''C:\mydump.txt''
WITH (FIELDTERMINATOR = ''\t'',
ROWTERMINATOR = '''+CHAR(13)+CHAR(10)+''')'
EXEC(@.bulk_cmd)
It runs fine but does the insert twice. There are 35 records in the file,
you can watch it load 35 records twice. The records are field terminated wit
h
a tab, and row terminated with CR\LF. I have tried numerous other terminatio
n
characters and all does the same. The actual txt file was created using DTS
from a table.
I am using SQL Server 2000.
Any ideas would be appreciated.
MGAre you checking the target table to confirm that it really is inserting the
rows twice, or do you think that's what is happening because you see this in
the messages:
(35 rows affected)
(35 rows affected)
You see it twice because the top message is from the BULK INSERT inside the
sp, and the bottom is the sp reported how many rows where affected. If you
want to only see the message once, put SET NOCOUNT ON at the top of your sp.
"mgcap" wrote:

> I have a simple Bulk Insert statement I want to run in a stored procedure.
> Here it is:
> Set @.bulk_cmd = 'BULK INSERT MyTable
> FROM ''C:\mydump.txt''
> WITH (FIELDTERMINATOR = ''\t'',
> ROWTERMINATOR = '''+CHAR(13)+CHAR(10)+''')'
> EXEC(@.bulk_cmd)
> It runs fine but does the insert twice. There are 35 records in the file,
> you can watch it load 35 records twice. The records are field terminated w
ith
> a tab, and row terminated with CR\LF. I have tried numerous other terminat
ion
> characters and all does the same. The actual txt file was created using DT
S
> from a table.
> I am using SQL Server 2000.
> Any ideas would be appreciated.
> MG|||Mark,
Many thanks. I feel like a goof. That was it. The real issue was that I was
running the routine numerous times, not truncing the table every time, then
seeing more record in the table that 35. That along with the double message
threw me. I should have known better.
Thanks,
Mark
"Mark Williams" wrote:
> Are you checking the target table to confirm that it really is inserting t
he
> rows twice, or do you think that's what is happening because you see this
in
> the messages:
> (35 rows affected)
> (35 rows affected)
> You see it twice because the top message is from the BULK INSERT inside th
e
> sp, and the bottom is the sp reported how many rows where affected. If you
> want to only see the message once, put SET NOCOUNT ON at the top of your s
p.
>
> --
>
> "mgcap" wrote:
>