Error Using BCP from the xp_cmdshell

• mhambleton

  SSC Enthusiast

  Points: 129

  More actions

  August 14, 2012 at 9:43 am

  #265086

  Hi,

  I'm working a project where I need to be able to create a text file from a SQL Query. I use the BCP function but continue to get the same invalid object error. I have qualified it everyway possible and learned much about the command but still get the error, and of course the file is there.

  Here’s that message:

  Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'dbo.icitem'.

  Here’s my script:

  exec master..xp_cmdshell

  'bcp "SELECT * FROM dbo.icitem " queryout "d:\IcItemOutFile.txt"

  -S"STSERVER01\LSI" -T '

  I think it may be a command level permissions issue in conjunction with the SQL. Apparently they use a different method when coming from the command line security control.

  Any suggestions on how I can get my command running?

  Thanks

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  August 14, 2012 at 10:04 am

  #1525622

  mhambleton (8/14/2012)

  ---

  Hi,

  I'm working a project where I need to be able to create a text file from a SQL Query. I use the BCP function but continue to get the same invalid object error. I have qualified it everyway possible and learned much about the command but still get the error, and of course the file is there.

  Here’s that message:

  Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'dbo.icitem'.

  Here’s my script:

  exec master..xp_cmdshell

  'bcp "SELECT * FROM dbo.icitem " queryout "d:\IcItemOutFile.txt"

  -S"STSERVER01\LSI" -T '

  I think it may be a command level permissions issue in conjunction with the SQL. Apparently they use a different method when coming from the command line security control.

  Any suggestions on how I can get my command running?

  Thanks

  Try this, replacing DBNAME with the actual name of the databse where the table dbo.icitem exists:

  exec master..xp_cmdshell

  'bcp "SELECT * FROM DBNAME.dbo.icitem " queryout "d:\IcItemOutFile.txt"

  -S"STSERVER01\LSI" -T '

• Jared

  SSC Guru

  Points: 61793

  More actions

  August 14, 2012 at 11:39 am

  #1525695

  Lynn Pettis (8/14/2012)

  ---

  mhambleton (8/14/2012)

  ---

  Hi,

  I'm working a project where I need to be able to create a text file from a SQL Query. I use the BCP function but continue to get the same invalid object error. I have qualified it everyway possible and learned much about the command but still get the error, and of course the file is there.

  Here’s that message:

  Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'dbo.icitem'.

  Here’s my script:

  exec master..xp_cmdshell

  'bcp "SELECT * FROM dbo.icitem " queryout "d:\IcItemOutFile.txt"

  -S"STSERVER01\LSI" -T '

  I think it may be a command level permissions issue in conjunction with the SQL. Apparently they use a different method when coming from the command line security control.

  Any suggestions on how I can get my command running?

  Thanks

  Try this, replacing DBNAME with the actual name of the databse where the table dbo.icitem exists:

  exec master..xp_cmdshell

  'bcp "SELECT * FROM DBNAME.dbo.icitem " queryout "d:\IcItemOutFile.txt"

  -S"STSERVER01\LSI" -T '

  +1 How does the command line know what database to use? You have to tell it because it is not executing in the scope of your SQL session.

  Jared
  CE - Microsoft