StrataFrame Forum » StrataFrame Database Deployment Toolkit » Issues » Stored Proc Issue

Stored Proc Issue

Geoff Hirst — Thu, 06 Dec 2007 09:32:14 GMT

Chaps,

I have a stored proc, that returns the full contents of a table, some 21 columns. Now, I have no parameters on this proc, as I intend to use it for list population etc.

Thing is, it won't deploy. I get an error that says a problem occurred with a stored proc. A problem near the word 'SELECT'.

Now if I add

@ID int

at the top of the proc, it deploys fine.

Proc is

Select ID,JobNo,ProjectName,WorkDescription,ClientID,ClientContact,ContactDetails,JobManager,JobArchitect,

JobType,ClientType,EnduserCLientType,SourceofWork,Timebased,FixedFee,PercentageValueOfProject,

ExpensesID,ProjectFee,EstimatedProjectValue,ProjectPercentage,ProjectFee2 from Job

If I replace the text with SELECT * from Job it also works fine.

Any Ideas chaps?

thanks

Geoff

RE: Stored Proc Issue

StrataFrame Team — Thu, 06 Dec 2007 09:32:14 GMT

Hrm... that is strange. I can't remember the code explicitly, but we might be trying to Regex off the AS on the front, and if we don't find one, we add it. Maybe your sproc doesn't pass our Regex, so we can't find out that the AS is missing. I can't remember.

However, if you posted an example sproc of one that deploys fine without the AS and one that requires the AS to deploy, then I might be able to figure something out.

RE: Stored Proc Issue

Geoff Hirst — Wed, 05 Dec 2007 04:54:18 GMT

Chaps,

Thanks for this. Indeed in this case adding the 'AS' does resolve the issue.

What I do have though are other procedures in the DDT, that do not have the AS in the proc, but return only a small number of columns and they have deployed fine.

If you can replicate this I would be interested to know.

thanks Geoff

RE: Stored Proc Issue

StrataFrame Team — Mon, 03 Dec 2007 09:02:11 GMT

Yep, Peter is right, the AS is not optional, even without parameters.

RE: Stored Proc Issue

StrataFrame Team — Mon, 03 Dec 2007 09:01:34 GMT

Are you deploying through the DDT?

If you are, then pay close attention to the labels of the Stored Procedure form... they list the code that will be executed when deploying the procedure...

After the "CreateProcedure" you would still need to place the AS keyword, event if you don't use any parameters. So, the textbox would contain:

AS
Select ID,JobNo,ProjectName,WorkDescription,ClientID,ClientContact,ContactDetails,JobManager,JobArchitect, JobType,ClientType,EnduserCLientType,SourceofWork,Timebased,FixedFee,PercentageValueOfProject, ExpensesID,ProjectFee,EstimatedProjectValue,ProjectPercentage,ProjectFee2 from Job

RE: Stored Proc Issue

Peter Jones — Sun, 02 Dec 2007 20:06:25 GMT

Hi Geoff,

If don't think the AS is optional in a proc, e.g.

CREATE PROCEDURE dbo.myProc

SELECT * FROM MyTable

Maybe omitting the AS is causing the problem?

Cheers, Peter