Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1985
  • Last Modified:

Executing a stored procedure from cognos

Hi I have created a stored procedure (sqlserver) please see attached.

I am trying to call the procedure from cognos as I would like to use the vaules within a report but

I am getting the following error

At least one expression in the Select clause is missing the AS clause to make it a proper alias.

the stored procedures runs ok from query analyser, could someone please give me there opion on why this is failing?

Cheers
CREATE PROCEDURE Empcount AS
 
--Declare Start and End variables (these can also be passed into a stored procedure)
DECLARE @startdate 	smalldatetime,
	@endDate   	smalldatetime
 
--this drops the hours and minutes and sets them to 00:00
Set @startdate  = convert(varchar,'Nov 1, 2008' ,111)
Set @endDate  = convert(varchar,'Dec 31 2008' ,111)
 
--Declare EnD Date tmp variable to be used in the loop
DECLARE @tmpEndDate	smalldatetime
--Declare tmp table to act as a temporary holding place for your data
DECLARE @tmpTable	TABLE(
			StartDate	smalldatetime,
			EndDate		smalldatetime,
			noemp 		integer)
--Set @startdate to beginning of month span
Set @startdate =  DATEADD(dd,-(DAY(DATEADD(m,1,@startdate))-1),@startdate)
--set @endDate to end of month span
Set @endDate =  DATEADD(dd, -DAY(DATEADD(m,1,@endDate)), DATEADD(m,1,@endDate))
 
--loop through each month
While @startdate < @endDate
BEGIN
         --set @tmpEndDate to last minute of month
	Set @tmpEndDate = dateadd(n,-1,dateadd(m,1,@startdate))
         --insert values in table
	Insert into @tmpTable(StartDate,EndDate,noemp) 
		Select @startdate as startdate, @tmpEndDate as enddate, NoEmployees as noemp 
		      from CTContracts 
		      Where Fromdate  <= @tmpEndDate And  EndDate >= @startdate
		      and LKContractTypeID = 8
         --increment @startdate 
	Set @startdate = dateadd(m,1,@startdate)
END
 
--Now you can do what you want with the results
--Select * from @tmpTable
Select Min(StartDate) as StartDate, Max(EndDate) as EndDate, Sum(noemp) as noemp from @tmpTable
GO

Open in new window

0
ac_davis2002
Asked:
ac_davis2002
1 Solution
 
nmcdermaidCommented:
First thing to try is put SET NOCOUNT ON at the start of your procedure definition


CREATE PROCEDURE Empcount AS
 
SET NOCOUNT ON

--Declare Start and End variables (these can also be passed into a stored procedure)
DECLARE @startdate       smalldatetime,
      @endDate         smalldatetime
 
--this drops the hours and minutes and sets them to 00:00
Set @startdate  = convert(varchar,'Nov 1, 2008' ,111)
Set @endDate  = convert(varchar,'Dec 31 2008' ,111)
 
--Declare EnD Date tmp variable to be used in the loop
DECLARE @tmpEndDate      smalldatetime
--Declare tmp table to act as a temporary holding place for your data
DECLARE @tmpTable      TABLE(
                  StartDate      smalldatetime,
                  EndDate            smalldatetime,
                  noemp             integer)
--Set @startdate to beginning of month span
Set @startdate =  DATEADD(dd,-(DAY(DATEADD(m,1,@startdate))-1),@startdate)
--set @endDate to end of month span
Set @endDate =  DATEADD(dd, -DAY(DATEADD(m,1,@endDate)), DATEADD(m,1,@endDate))
 
--loop through each month
While @startdate < @endDate
BEGIN
         --set @tmpEndDate to last minute of month
      Set @tmpEndDate = dateadd(n,-1,dateadd(m,1,@startdate))
         --insert values in table
      Insert into @tmpTable(StartDate,EndDate,noemp)
            Select @startdate as startdate, @tmpEndDate as enddate, NoEmployees as noemp
                  from CTContracts
                  Where Fromdate  <= @tmpEndDate And  EndDate >= @startdate
                  and LKContractTypeID = 8
         --increment @startdate
      Set @startdate = dateadd(m,1,@startdate)
END
 
--Now you can do what you want with the results
--Select * from @tmpTable
Select Min(StartDate) as StartDate, Max(EndDate) as EndDate, Sum(noemp) as noemp from @tmpTable
GO
0
 
ac_davis2002Author Commented:
Hi

No change I'm still getting the error. any other suggestions?

Cheers
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now