bcp export data from global temp table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

bcp export data from global temp table

Author

Topic

tonymartini777
Starting Member

2 Posts

Posted - 2012-02-01 : 12:49:03

Hi all,

I'm getting the following error:

SQLState = S0001, NativeError = 0Error=[Microsoft]SQLServer Native Client 10.0]Unable to open BCP host data-file

For the following SQL code:

USE Runtime
GO
CREATE TABLE ##EnergyWeekReport(
Substation nvarchar(50) NOT NULL,
Monday float NULL,
Tuesday float NULL,
Wednesday float NULL,
Thursday float NULL,
Friday float NULL,
Saturday float NULL,
Sunday float NULL,
This_Week float NULL,
Last_Week float NULL);

DECLARE
@Mon float,
@Tue float,
@Wed float,
@Thu float,
@Fri float,
@Sat float,
@Sun float,
@Week float,
@preWeek float,

@dateIN datetime,

@Filename nvarchar(50),
@bcpcmd nvarchar(500);

-- Assign values
SET @dateIN = '2012-1-23';

SET @Mon = dbo.fnSEL_Monday_MWh(@dateIN, 'CB02_SS1_351S_1P7_MWh', 'CB02_SS2_351S_1P7_MWh');
SET @Tue = dbo.fnSEL_Tuesday_MWh(@dateIN, 'CB02_SS1_351S_1P7_MWh', 'CB02_SS2_351S_1P7_MWh');
SET @Wed = dbo.fnSEL_Wednesday_MWh(@dateIN, 'CB02_SS1_351S_1P7_MWh', 'CB02_SS2_351S_1P7_MWh');
SET @Thu = dbo.fnSEL_Thursday_MWh(@dateIN, 'CB02_SS1_351S_1P7_MWh', 'CB02_SS2_351S_1P7_MWh');
SET @Fri = dbo.fnSEL_Friday_MWh(@dateIN, 'CB02_SS1_351S_1P7_MWh', 'CB02_SS2_351S_1P7_MWh');
SET @Sat = dbo.fnSEL_Saturday_MWh(@dateIN, 'CB02_SS1_351S_1P7_MWh', 'CB02_SS2_351S_1P7_MWh');
SET @Sun = dbo.fnSEL_Sunday_MWh(@dateIN, 'CB02_SS1_351S_1P7_MWh', 'CB02_SS2_351S_1P7_MWh');
SET @Week = dbo.fnSEL_WeekTot_MWh(@dateIN, 'CB02_SS1_351S_1P7_MWh', 'CB02_SS2_351S_1P7_MWh');
SET @preWeek = dbo.fnSEL_preWeekTot_MWh(@dateIN, 'CB02_SS1_351S_1P7_MWh', 'CB02_SS2_351S_1P7_MWh');

INSERT INTO ##EnergyWeekReport
SELECT '138kV Line 1',@Mon, @Tue, @Wed, @Thu, @Fri, @Sat, @Sun, @Week, @preWeek;

SET @bcpcmd = 'bcp "SELECT * FROM ##EnergyWeekReport" queryout c\test.txt -c -T -S ww-historian';
EXEC xp_cmdshell @bcpcmd ;

DROP TABLE ##EnergyWeekReport
GO

I'm running this in management studio on the machine where the database resides. I am logged in as an 'aaAdministrator' with all the god rights needed.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-01 : 13:04:11

is c\test.txt path exists in your machine where db resides?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

tonymartini777
Starting Member

2 Posts

Posted - 2012-02-01 : 14:36:20

Ugh.. I see my path syntax is incorrect it needs to be c:\test.txt - I was missing the :

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-02 : 10:24:51

is it working fine now with changed path?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources