xp_cmdshell BCP

• SQL_Surfer

  SSCrazy Eights

  Points: 8320

  More actions

  July 5, 2016 at 5:28 pm

  #400361

  The same command works in SQL 2005 but just returns null in 2014. I'm going nuts with this.

  xp_cmdshell 'bcp "select top 10 * from dbo.customer_master" queryout ''C:\customers_master.txt'' -T -e C:\customers_mastererr.txt'

  Anybody running into this issue? This is new server. Do I need to configure anything for Xp_Cmdshell or BCP?

  I'd really appreciate your help.

  Its is not even generating error file for me to see what is going wrong.

• SQL_Surfer

  SSCrazy Eights

  Points: 8320

  More actions

  July 5, 2016 at 6:00 pm

  #1888597

  Here is the actual SQL script. It works in 2005...But just shows NULL on 2014...Please help!!!!

  DECLARE @server VARCHAR(500)

  DECLARE @DATABASE VARCHAR(150)

  DECLARE @TABLE VARCHAR(150)

  DECLARE @QUERY VARCHAR(1020)

  DECLARE @FILE VARCHAR(250)

  DECLARE @PATH VARCHAR(250)

  DECLARE @TYPE VARCHAR(10)

  DECLARE @BCP VARCHAR(5000)

  SET @server = 'MyServer'

  SET @DATABASE = 'Database'

  SET @TABLE = 'Table'

  SET @QUERY = 'SELECT * FROM Table WHERE Client= ''1261'''

  SET @FILE = 'Table.txt'

  SET @PATH = 'C:\Data\'

  SET @TYPE = 'QueryOUt'

  SET @BCP = 'BCP "' + @QUERY + '" ' + @TYPE + ' "' + @PATH + @FILE + '" -o"' + @PATH + @FILE + '.log' + '" -S"' + @server + '" -T -t"|" -r"" -c -q -'

  PRINT @BCP

  EXEC sys.XP_CMDSHELL @BCP

• Sergiy

  SSC Guru

  Points: 110208

  More actions

  July 5, 2016 at 9:09 pm

  #1888605

  You do not use @DATABASE anywhere in @BCP query.

  The database must have been set up as default for the user on that SQL 2005 instance, therefore the query worked there.

  Now you most likely querying master database, with the corresponding outcome.

  _____________
  Code for TallyGenerator