bcp fails to import data near reserved word.
[Doooh]Alrighty then. Thanks Tara (again) for pointing out the obvious. The resolution to this problem is the -q option. As BOL states, it sets quoted identifiers on in the context of the bcp thread. So that solves that problem, and I'm sure it's one that I won't forget. It's curious why there isn't a problem with the bcp out though.[/Doooh]
BOL
-q
Executes the SET QUOTED_IDENTIFIERS ON statement in the connection between the bcp utility and an instance of SQL Server. Use this option to specify a database, owner, table, or view name that contains a space or a quotation mark. Enclose the entire three-part table or view name in double quotation marks (" ").
Who knew? OK, probably everyone. I have never run into this before (mostly because I don't use reserve words as column names). The work around I used was to create a view. If anyone has a slicker way, or better, an explanation as to why bcp does fail, I'm all ears...or is that eyes? In any case....something for future reference so I don't have to remember it, and hopefully someone else might benefit.
USE Northwind
GO
SET NOCOUNT ON
CREATE TABLE myTable99(Col1 int, [DESC] varchar(8000))
GO
INSERT INTO myTable99 (Col1, [DESC])
SELECT 1, 'I' UNION ALL
SELECT 2, 'Me' UNION ALL
SELECT 3, 'Mine'
GO
SELECT * FROM myTable99
EXEC master..xp_cmdshell 'bcp Northwind.dbo.myTable99 out d:\data\Northwind_dbo_myTable99.dat -Usa -P -S -c'
GO
TRUNCATE TABLE myTable99
GO
EXEC master..xp_cmdshell 'bcp Northwind.dbo.myTable99 in d:\data\Northwind_dbo_myTable99.dat -Usa -P -S -c -q'
GO
SELECT * FROM myTable99
GO
CREATE VIEW myView99
AS
SELECT Col1, [DESC] AS Col2 FROM myTable99
GO
EXEC master..xp_cmdshell 'bcp Northwind.dbo.myView99 in d:\data\Northwind_dbo_myTable99.dat -Usa -P -S -c'
GO
SELECT * FROM myTable99
GO
SET NOCOUNT OFF
DROP VIEW myView99
DROP TABLE myTable99
GO