BCP to export data from global temp table

• tonymartini777

  SSC Veteran

  Points: 276

  More actions

  February 1, 2012 at 10:40 am

  #391451

  Hi all,

  I'm getting the following error:

  SQLState = S0001, NativeError = 0Error=[Microsoft]SQLServer Native Client 10.0]Unable to open BCP host data-fileFor the following SQL code:

  USE Runtime

  GO

  CREATE TABLE ##EnergyWeekReport(

  Substationnvarchar(50) NOT NULL,

  Mondayfloat NULL,

  Tuesdayfloat NULL,

  Wednesdayfloat NULL,

  Thursday float NULL,

  Fridayfloat NULL,

  Saturday float NULL,

  Sundayfloat NULL,

  This_Weekfloat NULL,

  Last_Weekfloat NULL);

  DECLARE

  @Monfloat,

  @tuefloat,

  @Wedfloat,

  @Thufloat,

  @frifloat,

  @Satfloat,

  @sunfloat,

  @Weekfloat,

  @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 dbo.##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.

  Please advise

• Lowell

  SSC Guru

  Points: 323518

  More actions

  February 1, 2012 at 10:50 am

  #1441341

  simple syntax error.

  queryout c\test.txt

  i think that should be c:\test.txt; just missing the colon.

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• Michael Valentine Jones

  SSC Guru

  Points: 64818

  More actions

  February 1, 2012 at 10:54 am

  #1441344

  And since your running BCP from the CMDSHELL, remember that the c:\test.txt file will be on the server you are running it on, not on your PC.