none
[bcp] Incorrect syntax near the keyword 'User'

    Question

  • How can I restore a database with bcp when one of the tables is a restricted keyword?

    >bcp XXXXX.dbo.User in "c:\XXXXX\1T_109User.dat" -f "c:\XXXXX\1T_109User.xml" -E -T -S XXXXX
    SQLState = 37000, NativeError = 156
    Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Incorrect syntax near the keyword 'User'.

    XXXXX>bcp [XXXXX].[dbo].[User] in "c:\XXXXX\1T_109User.dat" -f "c:\XXXXX\1T_109User.xml" -E -T -S XXXXX
    SQLState = S1000, NativeError = 0
    Error = [Microsoft][SQL Server Native Client 10.0]Unable to open BCP host data-file

    From SQL profiler:

     set fmtonly on select * from XXXXX.dbo.User where 1=2 set fmtonly off (Syntax error when run)

     set fmtonly on select * from [XXXXX].[dbo].[User] where 1=2 set fmtonly off (works)

    Monday, November 12, 2012 9:36 PM

Answers

  • Nevermind; turns out the "Unable to open BCP host data-file" error was actually legit, as the backup bcp script had the same problem. Surrounding the name with [] on both backup and restore made it work.

    I still think bcp should be able to deal properly with tables with restricted names.

    • Marked as answer by tcqwerty Monday, November 12, 2012 9:44 PM
    Monday, November 12, 2012 9:44 PM