how to take a backup of a table which is created from a stored procedure

Question

I have a stored procedure hello which generates a table. I want to take the backup of that table every week using sql job. Can someone tell me a command for that. So far I have tried to take a backup of an existing table employee in database emp using the command:

DECLARE @table VARCHAR(128),
@file VARCHAR(255),
@cmd VARCHAR(512)
SET @table = 'dbo.employee' --  Table Name which you want to backup
SET @file = 'C:\Users\vibhav.sarraf\Documents\New_folder\' + @table + '_' + CONVERT(CHAR(8), GETDATE(), 112)
+ '.dat'
SET @cmd = 'bcp ' + @table + ' out ' + @file + ' -n -T '
EXEC emp..xp_cmdshell @cmd

which generates a 0 Byte file with and gives an error:

Error

[Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid object name 'dbo.employee'.

which I think is because I am not using use emp;

Note:- I dont want to create any table in my database

Answer 1

DECLARE @query VARCHAR(128),
@file VARCHAR(255),
@cmd VARCHAR(512),
@database varchar(128)
SET @database = 'jiradb'
SET @query = '"hello"' --where hello is the stored procedure
SET @file = 'C:\Users\vibhav.sarraf\Documents\New_folder\query.txt'
SET @cmd = 'bcp ' + @query + ' queryout ' + @file + ' -c -T -d ' + @database
EXEC jiradb..xp_cmdshell @cmd

Answer 2

The bcp program runs outside the current script, hence doesn't knows anything about the "current database". You need to explicitly tell it where that table is located by setting the database in the command line (otherwise it ends up looking for it in master.

So, change it to this:

SET @cmd = 'bcp ' + @table + ' out ' + @file + ' -n -T -d' + @database

where @database is the name of the DB containing the table in question.

Look at the bcp documentation for details.