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.