Excel connection to sp won't refresh

• peteroc

  SSChasing Mays

  Points: 638

  More actions

  March 21, 2012 at 9:01 pm

  #253525

  Hi,

  I've been creating a few parameterised reports in excel which link to sql server stored procedures. This has been working very well but the latest one I've created is failing to refresh.

  The sp's that are working are all basic selects. The one that isn't is creating a table, executing a couple of other sp's to insert data into the table then selecting from that. Is it possible to make this work and return the data to excel?

  Here's the code:

  CREATE procedure dbo.sp_EHC (@DateStart DateTime, @DateEnd DateTime)

  as

  DECLARE @rc int

  IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'EHC')

  DROP TABLE EHC

  Create Table EHC (

  OperationDate datetime,

  MachineNumber int,

  Model nvarchar(30),

  Make nvarchar(50),

  Type nvarchar(50),

  ClockHours int,

  OperatorHours float(8),

  FuelUsage int,

  CostCode nvarchar(20),

  Units int,

  Rate int,

  Amount int)

  Insert EHC

  EXEC @rc = [MSDSQL].[dbo].[sp_EquipmentHourlyCostReport] @DateStart, @DateEnd

  Insert EHC

  EXEC @rc = [MSDSQL].[dbo].[sp_EquipmentHourlyCostReportTL] @DateStart, @DateEnd

  Select * from dbo.EHC

  GO

  Any help would be much appreciated,

  Peter

• peteroc

  SSChasing Mays

  Points: 638

  More actions

  March 21, 2012 at 10:00 pm

  #1462513

  ...scratch that... I've just recreated the connection from excel and it's all good!