The columns are all the correct collation, as is the format file. I wonder if, since the server default collation is the _AI one, the BULK INSERT is using the...

OK. But I take it there's no way to keep it as a "disabled" constraint, as you can with FK checks? The goal is to have the same sort of...

Why would you have to keep the table locked if you used ignore_constraints?

Ok -- but just to be extra clear, you're saying that I should actually use a literal , instead of say \b (or whatever the escape sequence is for bel)?

Cool... so if I understand you correctly, the change you're proposing is just to use a non-textual ASCII character (in this case ASCII 7), since it wouldn't show up in...

Sure:

"ID"|"Name"|"URL"

443|Sally Mae|http://www.google.com/privacypolicy/ours.asp?myval1=12345_Standard&secondaryopt=abc|123

123|Apples|http://123.456.789.123/amazing_g?hl=en&groups=dogs|cats&referer=http://www.cnn.com/International/Russia

456|Apples|http://www.mywebsite.com/stupidvar=abc|123

789|Oranges|http://www.mywebsite.com/approach=north|west&a=2&b=4&c=23

In this case, the source of the problem is a pipe in a URL, in a pipe-delimited field. When generating these files, we would be able to escape the...

Hey, I have run into one more issue, which is the question of escaping. Since the pipe is the only character that is guaranteed to be a field separator in...

The files are CSV-style like:

"Andrew"|"Smith"|"39"|"Horseback Riding"

"Elaine"|"Jones"|"22"|"Hiking"

Looking at how much data processing I have to handle, I think...

Well, the values to be deleted...

Thanks, that worked like a charm. I just did something like:

INSERT INTO

(columns in order) SELECT (columns in order) FROM OPENROWSET (BULK [path], FORMATFILE=[path], FIRSTROW = 2) AS Z

I...

One other issue I did encounter... these format files ask us to provide both a column order and a column name. I had always thought that the point of SQL...

That worked!! Great idea, I hadn't thought about creating a dummy column.

It seems to all be working now, and crazy fast too.

The one thing is, right now I have found...

OK, I actually figured out the problem with the EOF. It looks like the format files don't allow you to surround terminators with single quotes, only double quotes. So a...

Sure, sorry. Here is what I am working with right now:

10.0

4

1 SQLCHAR 0 ...

Tried it, although it's not ideal because it requires me to change security settings so I probably can't deploy it to my client's computer. Anyway, I keep getting errors like:...