Solved

Executing a stored procedure from cognos

Posted on 2008-06-19
2
1,969 Views
Last Modified: 2012-06-27
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
Comment
Question by:ac_davis2002
2 Comments
 
LVL 30

Accepted Solution

by:
nmcdermaid earned 500 total points
ID: 21827938
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
 

Author Comment

by:ac_davis2002
ID: 21831216
Hi

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

Cheers
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Hello, In my precious Article  (http://www.experts-exchange.com/Database/Reporting/A_15280-Create-Project-in-Microstrategy-Part-I.html)we saw the Configuration part for Microstrategy which included Metadata Creation and DataSource Preparation as …
How to increase the row limit in Jasper Server.
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…

896 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now