Improve company productivity with a Business Account.Sign Up

x
?
Solved

Executing a stored procedure from cognos

Posted on 2008-06-19
2
Medium Priority
?
1,989 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 2000 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Today, still in the boom of Apple, PC's and products, nearly 50% of the computer users use Windows as graphical operating systems. If you are among those users who love windows, but are grappling to keep the system's hard drive optimized, then you s…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Watch the video to learn how one can deal with PST file corruption issue with an outstanding Kernel for Outlook PST Repair Tool easily. Using this tool, non-technical users can swiftly perform the repair process to restore their essential data witho…
Watch the video to know the simple way to remove or recover or reset lost or forgotten passwords of Outlook PST file. With Kernel Outlook Password Recovery tool such operation is very easy to perform. It is a freeware with limitation to use with 500…

579 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