BCP Command for select statement - adding database name dynamically

• skb 44459

  SSCarpal Tunnel

  Points: 4768

  More actions

  November 13, 2013 at 6:14 am

  #300138

  BCP Command for select statement - adding database name dynamically.

  DECLARE @cmd VARCHAR(2048)

  DECLARE @IpSqlCommand VARCHAR(2048)

  DECLARE @IpFileName VARCHAR(1000)

  SET @IpFileName = '\\server\user\test.txt'

  SET @IpSQLCommand = 'Select * from employee'

  Set @Cmd = @Cmd + 'bcp "'

  Set @Cmd = @Cmd + @IpSQLCommand + '"'

  Set @Cmd = @Cmd + ' Queryout '

  Set @Cmd = @Cmd + '"' + @IpFileName + '"'

  Set @Cmd = @Cmd + ' -T -c -t^|'

  EXEC xp_cmdshell @Cmd

  Getting an Error

  SQLState = S0002, NativeError = 208

  Error = [Microsoft][SQL Native Client][SQL Server]Invalid object name 'employee'.

  If I change my sql statement

  SET @IpSQLCommand = 'Select * dev1_phil_app..from employee'

  It works fine. My Real SQL Statement is very complicated and I want this program to run for other databases also which has a same structure.