Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

BCP Command for select statement - adding database name dynamically Expand / Collapse
Author
Message
Posted Wednesday, November 13, 2013 6:14 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, October 13, 2014 4:52 PM
Points: 101, Visits: 236
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.
Post #1513832
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse