x002548's Blog

Not Just a Number - Brett Kaiser
posts - 89, comments - 586, trackbacks - 39

Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'DESC'.

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

 

Print | posted on Monday, April 25, 2005 9:04 AM | Filed Under [ SQL Server ]

Feedback

Gravatar

# re: Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'DESC'.

error occured when you try to execute bcp, maybe this 'bcb' contains some errors ? SQL statment is valid
4/25/2005 12:16 PM | mehal
Gravatar

# The Error is that a reserve word causes bcp to fail

That's what the example is for. It's suppose to fail. You'll notice there is very little difference in the bcp's.

Syntactically they are all correct. I don't understand the internals of the bulk copy program (bcp) but I'm sure it's reading the sql server catalog and is not handling the reserve words correctly.

I wonder if Kalen Delaney's internals book cover's bcp.

4/25/2005 12:31 PM | Brett
Gravatar

# re: Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'DESC'.

Brett, just use the -q option:

EXEC master..xp_cmdshell 'bcp Northwind.dbo.myView99 in d:\data\Northwind_dbo_myTable99.dat -Usa -P -S -c -q'
GO
4/25/2005 7:01 PM | Tara
Gravatar

# re: Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'DESC'.

help
10/30/2005 4:17 PM | eric taylor
Gravatar

# re: Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'DESC'.

help
10/30/2005 4:17 PM | eric taylor
Gravatar

# re: Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'DESC'.

how do I fix this
1/10/2006 12:33 AM | miguel
Gravatar

# re: Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'DESC'.

i have problem to connect the database
4/18/2006 1:56 PM | virendra
Gravatar

# re: Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'DESC'.

a
7/27/2006 5:58 AM | s
Gravatar

# re: Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'exec'.

d
8/23/2006 3:28 PM | d
Gravatar

# re: Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'DESC'.

Having a similar problem, and this worked great!!!
1/12/2007 2:38 PM | Isaac
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET