"Enric" <En***@discussions.microsoft.com> wrote in message
news:5A8C028A-B77F-4227-A069-FE243C33E351@microsoft.com...
> Dear all,
>
> I've got an issue with a stored procedure and I can't work out.
>
> This is the stored procedure:
>
> CREATE PROCEDURE Arq_RecuperaIdentity
> AS
>
> declare @maxDTS as integer
> declare @maxDTSFICH  as integer
> declare @VALOR as integer
>
>
> SET @maxDTS = (SELECT MAX(ID) FROM ARQ_DTSDATOS)
>
> INSERT INTO arq_dtsfich(DtsId,Fich,Ruta,Resp,Tip,Act) VALUES(@maxDTS
> ,'','','','',0)
>
>
> SET @MAXDTSFICH = (SELECT IDENT_CURRENT('arq_dtsfich'))
> DELETE FROM arq_dtsfich WHERE id = @MAXDTSFICH
>
> SET @VALOR = (SELECT IDENT_CURRENT('arq_dtsfich') + 1)
> RETURN @VALOR
> GO
>
> The aforementioned sp works fine and return the right value from a QA
> session: (VALOR)
> My ASP page call it but always retrieves a NULL as value. I don't understand
> it.
> Permissions are granted for the user of the connection and I don't see
> another hindrances.
>
> The following snippet do that:
>
> Set rstCommand = Server.CreateObject("ADODB.Command")
> Set rst2000 = Server.CreateObject("ADODB.recordset")
>
> rstCommand.ActiveConnection = Conexion
> rstcommand.CommandType= adCmdStoredProc
> rstCommand.CommandText = "Arq_RecuperaIdentity" rstcommand.CommandTimeout = 0
>
> Set rst2000 = rstCommand.Execute
> if IsNull(rst2000("VALOR")) THEN
> Response.Write("sh*t")
> Response.End
> end if
>
> Does anyone have any idea what on earth is happening?
> Any advices/though are very useful.
> Enric

"Tibor Karaszi" wrote:

> Try adding SET NOCOUNT ON in the beginning of the procedure code.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
>
> "Enric" <En***@discussions.microsoft.com> wrote in message
> news:5A8C028A-B77F-4227-A069-FE243C33E351@microsoft.com...
> > Dear all,
> >
> > I've got an issue with a stored procedure and I can't work out.
> >
> > This is the stored procedure:
> >
> > CREATE PROCEDURE Arq_RecuperaIdentity
> > AS
> >
> > declare @maxDTS as integer
> > declare @maxDTSFICH  as integer
> > declare @VALOR as integer
> >
> >
> > SET @maxDTS = (SELECT MAX(ID) FROM ARQ_DTSDATOS)
> >
> > INSERT INTO arq_dtsfich(DtsId,Fich,Ruta,Resp,Tip,Act) VALUES(@maxDTS
> > ,'','','','',0)
> >
> >
> > SET @MAXDTSFICH = (SELECT IDENT_CURRENT('arq_dtsfich'))
> > DELETE FROM arq_dtsfich WHERE id = @MAXDTSFICH
> >
> > SET @VALOR = (SELECT IDENT_CURRENT('arq_dtsfich') + 1)
> > RETURN @VALOR
> > GO
> >
> > The aforementioned sp works fine and return the right value from a QA
> > session: (VALOR)
> > My ASP page call it but always retrieves a NULL as value. I don't understand
> > it.
> > Permissions are granted for the user of the connection and I don't see
> > another hindrances.
> >
> > The following snippet do that:
> >
> > Set rstCommand = Server.CreateObject("ADODB.Command")
> > Set rst2000 = Server.CreateObject("ADODB.recordset")
> >
> > rstCommand.ActiveConnection = Conexion
> > rstcommand.CommandType= adCmdStoredProc
> > rstCommand.CommandText = "Arq_RecuperaIdentity" rstcommand.CommandTimeout = 0
> >
> > Set rst2000 = rstCommand.Execute
> > if IsNull(rst2000("VALOR")) THEN
> > Response.Write("sh*t")
> > Response.End
> > end if
> >
> > Does anyone have any idea what on earth is happening?
> > Any advices/though are very useful.
> > Enric
>
>
>

"Enric" wrote:

> Tibor. It wasn't work
>
> ..
> declare @maxDTS as integer
> declare @maxDTSFICH  as integer
> declare @VALOR as integer
>
> SET NOCOUNT ON
>
> SET @maxDTS = (SELECT MAX(ID) FROM ARQ_DTSDATOS)
> ..
>
> I know that must be a stupid thing but what's the next step?
> I've updated that stored procedure with a ONLY and SIMPLE
> " select * from arq_dtsfich " and from my ASP page take the value
>
> "Tibor Karaszi" wrote:
>
> > Try adding SET NOCOUNT ON in the beginning of the procedure code.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "Enric" <En***@discussions.microsoft.com> wrote in message
> > news:5A8C028A-B77F-4227-A069-FE243C33E351@microsoft.com...
> > > Dear all,
> > >
> > > I've got an issue with a stored procedure and I can't work out.
> > >
> > > This is the stored procedure:
> > >
> > > CREATE PROCEDURE Arq_RecuperaIdentity
> > > AS
> > >
> > > declare @maxDTS as integer
> > > declare @maxDTSFICH  as integer
> > > declare @VALOR as integer
> > >
> > >
> > > SET @maxDTS = (SELECT MAX(ID) FROM ARQ_DTSDATOS)
> > >
> > > INSERT INTO arq_dtsfich(DtsId,Fich,Ruta,Resp,Tip,Act) VALUES(@maxDTS
> > > ,'','','','',0)
> > >
> > >
> > > SET @MAXDTSFICH = (SELECT IDENT_CURRENT('arq_dtsfich'))
> > > DELETE FROM arq_dtsfich WHERE id = @MAXDTSFICH
> > >
> > > SET @VALOR = (SELECT IDENT_CURRENT('arq_dtsfich') + 1)
> > > RETURN @VALOR
> > > GO
> > >
> > > The aforementioned sp works fine and return the right value from a QA
> > > session: (VALOR)
> > > My ASP page call it but always retrieves a NULL as value. I don't understand
> > > it.
> > > Permissions are granted for the user of the connection and I don't see
> > > another hindrances.
> > >
> > > The following snippet do that:
> > >
> > > Set rstCommand = Server.CreateObject("ADODB.Command")
> > > Set rst2000 = Server.CreateObject("ADODB.recordset")
> > >
> > > rstCommand.ActiveConnection = Conexion
> > > rstcommand.CommandType= adCmdStoredProc
> > > rstCommand.CommandText = "Arq_RecuperaIdentity" rstcommand.CommandTimeout = 0
> > >
> > > Set rst2000 = rstCommand.Execute
> > > if IsNull(rst2000("VALOR")) THEN
> > > Response.Write("sh*t")
> > > Response.End
> > > end if
> > >
> > > Does anyone have any idea what on earth is happening?
> > > Any advices/though are very useful.
> > > Enric
> >
> >
> >

"Enric" wrote:

> I've tried use a TABLE variable but unfortunately I obtain null agai:
>
> declare @t1 table(c1 INTEGER)
> SET @VALOR = (SELECT IDENT_CURRENT('arq_dtsfich') + 1)
> insert into @t1 values(@VALOR)
> select * from @t1
> GO
>
> Help!
>
> "Enric" wrote:
>
> > Tibor. It wasn't work
> >
> > ..
> > declare @maxDTS as integer
> > declare @maxDTSFICH  as integer
> > declare @VALOR as integer
> >
> > SET NOCOUNT ON
> >
> > SET @maxDTS = (SELECT MAX(ID) FROM ARQ_DTSDATOS)
> > ..
> >
> > I know that must be a stupid thing but what's the next step?
> > I've updated that stored procedure with a ONLY and SIMPLE
> > " select * from arq_dtsfich " and from my ASP page take the value
> >
> > "Tibor Karaszi" wrote:
> >
> > > Try adding SET NOCOUNT ON in the beginning of the procedure code.
> > >
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > http://www.karaszi.com/sqlserver/default.asp
> > > http://www.solidqualitylearning.com/
> > >
> > >
> > > "Enric" <En***@discussions.microsoft.com> wrote in message
> > > news:5A8C028A-B77F-4227-A069-FE243C33E351@microsoft.com...
> > > > Dear all,
> > > >
> > > > I've got an issue with a stored procedure and I can't work out.
> > > >
> > > > This is the stored procedure:
> > > >
> > > > CREATE PROCEDURE Arq_RecuperaIdentity
> > > > AS
> > > >
> > > > declare @maxDTS as integer
> > > > declare @maxDTSFICH  as integer
> > > > declare @VALOR as integer
> > > >
> > > >
> > > > SET @maxDTS = (SELECT MAX(ID) FROM ARQ_DTSDATOS)
> > > >
> > > > INSERT INTO arq_dtsfich(DtsId,Fich,Ruta,Resp,Tip,Act) VALUES(@maxDTS
> > > > ,'','','','',0)
> > > >
> > > >
> > > > SET @MAXDTSFICH = (SELECT IDENT_CURRENT('arq_dtsfich'))
> > > > DELETE FROM arq_dtsfich WHERE id = @MAXDTSFICH
> > > >
> > > > SET @VALOR = (SELECT IDENT_CURRENT('arq_dtsfich') + 1)
> > > > RETURN @VALOR
> > > > GO
> > > >
> > > > The aforementioned sp works fine and return the right value from a QA
> > > > session: (VALOR)
> > > > My ASP page call it but always retrieves a NULL as value. I don't understand
> > > > it.
> > > > Permissions are granted for the user of the connection and I don't see
> > > > another hindrances.
> > > >
> > > > The following snippet do that:
> > > >
> > > > Set rstCommand = Server.CreateObject("ADODB.Command")
> > > > Set rst2000 = Server.CreateObject("ADODB.recordset")
> > > >
> > > > rstCommand.ActiveConnection = Conexion
> > > > rstcommand.CommandType= adCmdStoredProc
> > > > rstCommand.CommandText = "Arq_RecuperaIdentity" rstcommand.CommandTimeout = 0
> > > >
> > > > Set rst2000 = rstCommand.Execute
> > > > if IsNull(rst2000("VALOR")) THEN
> > > > Response.Write("sh*t")
> > > > Response.End
> > > > end if
> > > >
> > > > Does anyone have any idea what on earth is happening?
> > > > Any advices/though are very useful.
> > > > Enric
> > >
> > >
> > >