Custom sp_Entry_GetActiveList is not in the expected format.

Hi,

I've just started using netTiers and I'm getting this message. I'm using the latest download of netTiers with Codesmith 4.0 and the stored proc is:

 CREATE PROCEDURE [dbo].[_Entry_GetActiveList] AS

SELECT Id,Headword
FROM Entry
WHERE Active=1
GO

Am I missing something?

Dave Risley

This is a shot in the dark but are you sure your selecting all the fields from your Entry table?  I'm not sure how the netTiers providers react when fields are missing, such as your Active field.  I do know there's some switching to a DataSet or IDataReader as the return if rows do not match the fields in the entity.

 Or could it be your SP is not selecting the rowcount at the end like the generated SP's?    Select @@ROWCOUNT

Could you be a bit more explicit about when you get the message i.e. gentime, runtime?

What is your pattern for picking up custom SP's? 

swin 

It happens when I generate the code from the template.

 The pattern is _{0}_ if I've understood you correctly.

Bizzarely I think this is because the "AS" is on the first line of the proc.

Try inserting a return before the "AS" so its on a new line.

hth

swin 

Unforunately that hasn't fixed it. The proc now looks like:

 CREATE PROCEDURE [dbo].[_Entry_GetActiveList]

AS

SELECT
     [Id],
     [Active],
     [Headword],
     [XmlFragment],
     [WhoAdded],
     [WhenAdded],
     [WhoUpdated],
     [WhenUpdated]
    FROM
     dbo.[Entry]
    WHERE Active=1

Select @@ROWCOUNT

GO

I have the same problem... I try everithing but im still getting the same error.

I don't know what is the problem..

please if somebody could help. 

IF you guys are using an older set of the templates, make sure there are no more than 1 BLANK space (not tabs) between the words "CREATE", "PROCEDURE", and "<strored procedure name>".  This has been fixed quite some time ago, it should not occur anymore in the latest builds.

DaveRisley it seems you have a blank space before the "CREATE" keyword, remove that and try again.  Maybe the regex doing the matching doesn't account for that.

I'm using the templates from the download link so I assume they are the latest.

I've removed the space before CREATE and that doesn't help.

can you try..

 

CREATE PROCEDURE <your sp name>

@totalRecords INT OUTPUT

AS

BEGIN

 <your select statement>

SET @totalRecords = @@ROWCOUNT 

END
 

I Try that but im getting the same error.

I have the last version of codesmith and nettiers. I really don't understand.

Please Help!!!!!!! 

Ok i really don't understand why is the format that nettiers espect os the SP. Please if someone in the nettiers team just tell me what is the problem.

Thanks. 

Here is my sp

-------------------------------------------------------------------------------------- 

CREATE PROCEDURE [dbo].[_Negocios_Search]
(
@Neg_NegocioID varchar(10)='',
@Neg_Nombre varchar(100)='',
@Neg_Calle varchar(80)='',
@Neg_Altura varchar(8)='',
@Neg_BarrioID varchar(8)='',
@Neg_Telefono1 varchar(20)='',
@Neg_Delivery varchar(2)='',
@Neg_Reservas varchar(2)='',
@Neg_DiaAtencionID varchar(8)='',
@Neg_HorarioAtencionID varchar(8)='',
@Neg_Activo varchar(8)='',
@Rne_RubroID varchar(8)=''

) AS
 SET DATEFORMAT dmy
DECLARE @Where varchar(3000)
DECLARE @Select varchar(3000)
SELECT @Where =""
IF @Rne_RubroID <> "" and @Rne_RubroID is not null
    SELECT @Select ="Select Neg_Nombre, Neg_Calle, Neg_Altura, Neg_BarrioID, Neg_Telefono1, Neg_Delivery, Neg_Reservas, Neg_DiaAtencionID, Neg_HorarioAtencionID FROM Negocios, RubrosNegocio "
Else
    SELECT @Select ="Select Neg_Nombre, Neg_Calle, Neg_Altura, Neg_BarrioID, Neg_Telefono1, Neg_Delivery, Neg_Reservas, Neg_DiaAtencionID, Neg_HorarioAtencionID FROM Negocios "
SELECT @Where =""
if @Neg_NegocioID <> "" and @Neg_NegocioID is not null
Begin
  if @Where = ""
     SELECT @Where = " Where  Neg_NegocioID = " + @Neg_NegocioID
  Else
     SELECT @Where = @Where + " AND  Neg_NegocioID = " + @Neg_NegocioID
End
if @Neg_Nombre <> "" and @Neg_Nombre is not null
Begin
  if @Where = ""
     SELECT @Where = " Where  Neg_Nombre like '" + RTRIM(@Neg_Nombre) + "%'"
  Else
     SELECT @Where = @Where + " AND  Neg_Nombre like '" + RTRIM(@Neg_Nombre) + "%'"
End
if @Neg_Calle <> "" and @Neg_Calle is not null
Begin
  if @Where = ""
     SELECT @Where = " Where  Neg_Calle like '" + RTRIM(@Neg_Calle) + "%'"
  Else
     SELECT @Where = @Where + " AND  Neg_Calle like '" + RTRIM(@Neg_Calle) + "%'"
End
if @Neg_Altura <> "" and @Neg_Altura is not null
Begin
  if @Where = ""
     SELECT @Where = " Where  Neg_Altura = " + @Neg_Altura
  Else
     SELECT @Where = @Where + " AND  Neg_Altura = " + @Neg_Altura
End
if @Neg_BarrioID <> "" and @Neg_BarrioID is not null
Begin
  if @Where = ""
     SELECT @Where = " Where  Neg_BarrioID = " + @Neg_BarrioID
  Else
     SELECT @Where = @Where + " AND  Neg_BarrioID = " + @Neg_BarrioID
End
if @Neg_Telefono1 <> "" and @Neg_Telefono1 is not null
Begin
  if @Where = ""
     SELECT @Where = " Where  Neg_Telefono1 like '" + RTRIM(@Neg_Telefono1) + "%'"
  Else
     SELECT @Where = @Where + " AND  Neg_Telefono1 like '" + RTRIM(@Neg_Telefono1) + "%'"
End
if @Neg_Delivery <> "" and @Neg_Delivery is not null
Begin
  if @Where = ""
     SELECT @Where = " Where  Neg_Delivery like '" + RTRIM(@Neg_Delivery) + "%'"
  Else
     SELECT @Where = @Where + " AND  Neg_Delivery like '" + RTRIM(@Neg_Delivery) + "%'"
End
if @Neg_Reservas <> "" and @Neg_Reservas is not null
Begin
  if @Where = ""
     SELECT @Where = " Where  Neg_Reservas like '" + RTRIM(@Neg_Reservas) + "%'"
  Else
     SELECT @Where = @Where + " AND  Neg_Reservas like '" + RTRIM(@Neg_Reservas) + "%'"
End
if @Neg_DiaAtencionID <> "" and @Neg_DiaAtencionID is not null
Begin
  if @Where = ""
     SELECT @Where = " Where  Neg_DiaAtencionID = " + @Neg_DiaAtencionID
  Else
     SELECT @Where = @Where + " AND  Neg_DiaAtencionID = " + @Neg_DiaAtencionID
End
if @Neg_HorarioAtencionID <> "" and @Neg_HorarioAtencionID is not null
Begin
  if @Where = ""
     SELECT @Where = " Where  Neg_HorarioAtencionID = " + @Neg_HorarioAtencionID
  Else
     SELECT @Where = @Where + " AND  Neg_HorarioAtencionID = " + @Neg_HorarioAtencionID
End
if @Neg_Activo <> "" and @Neg_Activo is not null
Begin
  if @Where = ""
     SELECT @Where = " Where  Neg_Activo = " + @Neg_Activo
  Else
     SELECT @Where = @Where + " AND  Neg_Activo = " + @Neg_Activo
End
if @Rne_RubroID <> "" and @Rne_RubroID is not null
Begin
    if @Where = ""
        SELECT @Where = " Where Rne_RubroID = " + @Rne_RubroID + " AND Neg_NegocioID = Rne_NegocioID "
    Else
        SELECT @Where = @Where + " AND Rne_RubroID = " + @Rne_RubroID + " AND Neg_NegocioID = Rne_NegocioID "
End
Execute(@Select + @Where )
Select @@ROWCOUNT
SET NOCOUNT ON
RETURN

----------------------



 

This is using dynamic SQL,
(Execute(@Select + @Where)) there is no meta-data that can be found
when running this. You are dynamically passing in a dynamic string
every time you run this procedure, so there's no possible way to
determine what kind of meta-data your procedure is going to return.



On 3/29/07, elfederiko wrote:
>
>
> Ok i really don't understand why is the format that nettiers espect os the
> SP. Please if someone in the nettiers team just tell me what is the problem.
>
> Thanks.
>
> Here is my sp
>
> --------------------------------------------------------------------------------------
>
> CREATE PROCEDURE [dbo].[_Negocios_Search]
> (
> @Neg_NegocioID varchar(10)='',
> @Neg_Nombre varchar(100)='',
> @Neg_Calle varchar(80)='',
> @Neg_Altura varchar(8)='',
> @Neg_BarrioID varchar(8)='',
> @Neg_Telefono1 varchar(20)='',
> @Neg_Delivery varchar(2)='',
> @Neg_Reservas varchar(2)='',
> @Neg_DiaAtencionID varchar(8)='',
> @Neg_HorarioAtencionID varchar(8)='',
> @Neg_Activo varchar(8)='',
> @Rne_RubroID varchar(8)=''
>
> ) AS
> SET DATEFORMAT dmy
> DECLARE @Where varchar(3000)
> DECLARE @Select varchar(3000)
> SELECT @Where =""
> IF @Rne_RubroID <> "" and @Rne_RubroID is not null
> SELECT @Select ="Select Neg_Nombre, Neg_Calle, Neg_Altura, Neg_BarrioID,
> Neg_Telefono1, Neg_Delivery, Neg_Reservas, Neg_DiaAtencionID,
> Neg_HorarioAtencionID FROM Negocios, RubrosNegocio "
> Else
> SELECT @Select ="Select Neg_Nombre, Neg_Calle, Neg_Altura, Neg_BarrioID,
> Neg_Telefono1, Neg_Delivery, Neg_Reservas, Neg_DiaAtencionID,
> Neg_HorarioAtencionID FROM Negocios "
> SELECT @Where =""
> if @Neg_NegocioID <> "" and @Neg_NegocioID is not null
> Begin
> if @Where = ""
> SELECT @Where = " Where Neg_NegocioID = " + @Neg_NegocioID
> Else
> SELECT @Where = @Where + " AND Neg_NegocioID = " + @Neg_NegocioID
> End
> if @Neg_Nombre <> "" and @Neg_Nombre is not null
> Begin
> if @Where = ""
> SELECT @Where = " Where Neg_Nombre like '" + RTRIM(@Neg_Nombre) + "%'"
> Else
> SELECT @Where = @Where + " AND Neg_Nombre like '" + RTRIM(@Neg_Nombre)
> + "%'"
> End
> if @Neg_Calle <> "" and @Neg_Calle is not null
> Begin
> if @Where = ""
> SELECT @Where = " Where Neg_Calle like '" + RTRIM(@Neg_Calle) + "%'"
> Else
> SELECT @Where = @Where + " AND Neg_Calle like '" + RTRIM(@Neg_Calle) +
> "%'"
> End
> if @Neg_Altura <> "" and @Neg_Altura is not null
> Begin
> if @Where = ""
> SELECT @Where = " Where Neg_Altura = " + @Neg_Altura
> Else
> SELECT @Where = @Where + " AND Neg_Altura = " + @Neg_Altura
> End
> if @Neg_BarrioID <> "" and @Neg_BarrioID is not null
> Begin
> if @Where = ""
> SELECT @Where = " Where Neg_BarrioID = " + @Neg_BarrioID
> Else
> SELECT @Where = @Where + " AND Neg_BarrioID = " + @Neg_BarrioID
> End
> if @Neg_Telefono1 <> "" and @Neg_Telefono1 is not null
> Begin
> if @Where = ""
> SELECT @Where = " Where Neg_Telefono1 like '" + RTRIM(@Neg_Telefono1)
> + "%'"
> Else
> SELECT @Where = @Where + " AND Neg_Telefono1 like '" +
> RTRIM(@Neg_Telefono1) + "%'"
> End
> if @Neg_Delivery <> "" and @Neg_Delivery is not null
> Begin
> if @Where = ""
> SELECT @Where = " Where Neg_Delivery like '" + RTRIM(@Neg_Delivery) +
> "%'"
> Else
> SELECT @Where = @Where + " AND Neg_Delivery like '" +
> RTRIM(@Neg_Delivery) + "%'"
> End
> if @Neg_Reservas <> "" and @Neg_Reservas is not null
> Begin
> if @Where = ""
> SELECT @Where = " Where Neg_Reservas like '" + RTRIM(@Neg_Reservas) +
> "%'"
> Else
> SELECT @Where = @Where + " AND Neg_Reservas like '" +
> RTRIM(@Neg_Reservas) + "%'"
> End
> if @Neg_DiaAtencionID <> "" and @Neg_DiaAtencionID is not null
> Begin
> if @Where = ""
> SELECT @Where = " Where Neg_DiaAtencionID = " + @Neg_DiaAtencionID
> Else
> SELECT @Where = @Where + " AND Neg_DiaAtencionID = " +
> @Neg_DiaAtencionID
> End
> if @Neg_HorarioAtencionID <> "" and @Neg_HorarioAtencionID is not null
> Begin
> if @Where = ""
> SELECT @Where = " Where Neg_HorarioAtencionID = " +
> @Neg_HorarioAtencionID
> Else
> SELECT @Where = @Where + " AND Neg_HorarioAtencionID = " +
> @Neg_HorarioAtencionID
> End
> if @Neg_Activo <> "" and @Neg_Activo is not null
> Begin
> if @Where = ""
> SELECT @Where = " Where Neg_Activo = " + @Neg_Activo
> Else
> SELECT @Where = @Where + " AND Neg_Activo = " + @Neg_Activo
> End
> if @Rne_RubroID <> "" and @Rne_RubroID is not null
> Begin
> if @Where = ""
> SELECT @Where = " Where Rne_RubroID = " + @Rne_RubroID + " AND
> Neg_NegocioID = Rne_NegocioID "
> Else
> SELECT @Where = @Where + " AND Rne_RubroID = " + @Rne_RubroID + "
> AND Neg_NegocioID = Rne_NegocioID "
> End
> Execute(@Select + @Where )
> Select @@ROWCOUNT
> SET NOCOUNT ON
> RETURN
>
> ----------------------
>
>
>
>
>
>
>

What do you suggest to replace this sp and make something that nettiers understand.

should i build a class to do that? 

One thing that you can do is to create a skelaton of the query that
you want to use.

if @username is null
begin
Select col1, col2, col3 from MyTable where 1 = 0
return
end

-- Then here you would begin your dynamic SQL

On 3/29/07, elfederiko wrote:
>
>
> What do you suggest to replace this sp and make something that nettiers
> understand.
>
> should i build a class to do that?
>
>
>