ok. issue is the row separator

you defined it as \r - but it really is \n\r so the way the code is there will be a few records with hex...

I've added some print statements to the following - run it again and the filename before the error will be the one failing - would need to look at that...

try the following - untested

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER Procedure [dbo].[import_dailytracing] @filepath varchar(500), @pattern varchar(100), @tablename varchar(100)

As

Begin
-- these 2 should always be set in ALL sp's
-- ...

your issue is the blank lines at the end of the file - if this is always how you get them you have 3 options (in order of preference)

1 -...

both files worked for me

if object_id('dbo.TestTracingImportSP') is not null
drop table dbo.TestTracingImportSP

create table dbo.TestTracingImportSP
(
[Initial] varchar(200)
,[Number] varchar(200)
,[Location City] varchar(200)
,[State] varchar(200)
,[Month] varchar(200)
,[Day] varchar(200)
,[Time] varchar(200)
,[L or E] varchar(200)
,[Event] varchar(200)
,[Train ID] varchar(200)
,[Destination...

it is a double hop issue for sure - when you execute on the server the credentials are available locally so it can access the share - when you go...

using "\n" worked fine for me with your sample file.

WITH (FIRSTROW=2, LASTROW=1000, FIELDTERMINATOR=',', ROWTERMINATOR='\n');

as you state your table is partitioned maybe you can switch out each partition onto a staging table, convert the table (repeat for all partitions, one table per partition) and...

• This reply was modified 3 years, 9 months ago by frederico_fonseca.
• This reply was modified 3 years, 9 months ago by frederico_fonseca.

I work on SQL server 2014 I need to rewrite update statement with best practice CAN I Write it without subquery ?

 UPDATE  FFFFF
 SET...

That's awesome, Frederico!  Thanks for posting that.

It would appear, though, that you actually have to program for the table structure.  Is there anything like the getting data from the...

better option for me would be to use a small powershell script to download the contents and load directly to sql server.

edit: the original link only extracts current year -...

• This reply was modified 3 years, 9 months ago by frederico_fonseca.

kindly supply table and indexed DDL for all tables involved as well as row count for each table - and at same time do tell us why table TPartFeature does...

ssms is not BCP - and what you get on SSMS is in many cases totally out of sync with what  you get with other tools.

on this case SSMs applies...

your expectation may not be what you can generate from sql using BCP - not knowing what options you are using I would advise using

"-c" which will add a \r\n...

why don't you wish to use stuff?

you can use substring - but that will make your code a bit more complex.

on diff note - avoid hardcoding dbnames (use synonyms instead)...