asp not retrieving correct information from a query

I run an ASP script that invokes a stored procedure which creates a table,
next step in that same ASP page there's a select statement that loads data
from that recent table. That data is loaded into a recordset.

Problem is that some of the values printed to the page that are obtained by
retrieving data from that recordset are incorrect. Example, if I run that
stored proc and then run the select statement directly in query analyzer, one
of the values shows 40, but on the ASP page it's shown as 50. Curious thing
is that no data manipulation is performed whatsoever, just retrieve and show.

RE: asp not retrieving correct information from a query

Hello,

Regarding the issue, would you please show more detailed code? For example,
how did you call the store procedure, query and display the data in the
ASP page, and what is the query you executed in query analyzer? Based on
my experience, this is more like an problem in coding. So we better double
check the logic in the code.

Sincerely,

Luke Zhang

Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/de fault.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx .
==================================================

This posting is provided "AS IS" with no warranties, and confers no rights.

Re: asp not retrieving correct information from a query

Ronny Miranda wrote:
> I run an ASP script that invokes a stored procedure which creates a
> table, next step in that same ASP page there's a select statement
> that loads data from that recent table. That data is loaded into a
> recordset.
>
> Problem is that some of the values printed to the page that are
> obtained by retrieving data from that recordset are incorrect.
> Example, if I run that stored proc and then run the select statement
> directly in query analyzer, one of the values shows 40, but on the
> ASP page it's shown as 50. Curious thing is that no data manipulation
> is performed whatsoever, just retrieve and show.

Never ask a database-related question without revealing the database type
and version. It's almost always relevant.
Given your references to "query analyzer" I could guess that you are using
SQL Server, but I may be wrong, and even if I was right, I would still be in
the dark about the version.

In any case, you need to show us how to recreate your problem. Strip your
code to the bare minimum needed to reproduce your symptoms and post the
result here. Show the table creation code as well as the code used to insert
the data (again - stripped to the bare minimum to allow us to reproduce your
problem)
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: asp not retrieving correct information from a query

The environment is like this:
+windows 2000
+IIS
+ASP 3.0
+MS SQL 2000

The asp that runs the process has this portion of code:

set Conn1=Server.CreateObject ("ADODB.Connection")
set Conn2=Server.CreateObject ("ADODB.Connection")
set rs=Server.CreateObject ("ADODB.Recordset")

Conn1.Open Application("strConexion")
Conn2.Open Application("strConexion")

SQL = "spS_PrepareFunnelForecastReport '" & strFechaParam & "'"
Conn1.Execute(SQL)

SQL = "select * from temptbFunnelForecast"
SET rs = Conn2.Execute(SQL)


and the stored proc spS_PrepareFunnelForecastReport is like this:



SELECT tbStates1.zona_geografica, Usuarios1.Nombre AS Gerente,
Usuarios_1.Nombre AS PBM, Usuarios2.Nombre AS TSG, Usuarios3.Nombre AS PSG,
Usuarios4.Nombre AS IPG,
dbo.dependencias_cat.DESCRIPCION AS Cliente, dbo.dependencias_cat.ESTRATEGICA
AS Listada,
dbo.dependencias_cat.GEM AS SP,
dbo.SOLICITUDES.NOMBRE_PROY, DISTRIBUIDORES1.EMPRESA AS Mayorista,
DISTRIBUIDORES_1.EMPRESA AS Distribuidor,
dbo.PEDIDOS.NO_PARTE, dbo.CPQGOB_EQUIPOS.Activo, dbo.CPQGOB_EQUIPOS.BU,
dbo.CPQGOB_EQUIPOS.DESCRIPCION, dbo.CPQGOB_EQUIPOS.PL,
dbo.CAT_STATUS_SOLICITUD.CICLOVENTA, dbo.CPQGOB_EQUIPOS.PRECIOUSD,
dbo.PEDIDOS.CANTIDAD, dbo.CPQGOB_EQUIPOS.PRECIOUSD *
dbo.PEDIDOS.CANTIDAD AS Total, dbo.SOLICITUDES.FEC_OF_LEGAL,
dbo.SOLICITUDES.FEC_FALLO,
dbo.SOLICITUDES.FEC_ENTREGA_BIENES, dbo.SOLICITUDES.ID_SOLICITUD,
dbo.SOLICITUDES.FEC_ULTIMA_ACT,
dbo.SOLICITUDES.numero_licitacion,
dbo.CAT_STATUS_SOLICITUD.DESCRIPCION AS Status, dbo.CAT_FALLO.DESCRIPCION AS
Fallo, dbo.SOLICITUDES.DualAgregation,
dbo.SOLICITUDES.FEC_SOLICITUD, tbStates1.StateName AS
[Localidad distribuidor], tbStates_1.StateName AS [Localidad cliente]
INTO temptbFunnelForecast
FROM dbo.DISTRIBUIDORES DISTRIBUIDORES_1 RIGHT OUTER JOIN
dbo.DISTRIBUIDORES DISTRIBUIDORES1 RIGHT OUTER JOIN
dbo.CAT_STATUS_SOLICITUD INNER JOIN
dbo.SOLICITUDES INNER JOIN
dbo.dependencias_cat ON dbo.SOLICITUDES.DEPENDENCIA =
dbo.dependencias_cat.ID ON
dbo.CAT_STATUS_SOLICITUD.ID_STATUS =
dbo.SOLICITUDES.STATUS LEFT OUTER JOIN
dbo.tbStates tbStates_1 ON dbo.SOLICITUDES.ESTADO =
tbStates_1.StateId ON DISTRIBUIDORES1.ID_EMPRESA =
dbo.SOLICITUDES.ID_Mayorista ON
DISTRIBUIDORES_1.ID_EMPRESA =
dbo.SOLICITUDES.ID_EMPRESA LEFT OUTER JOIN
dbo.CAT_FALLO INNER JOIN
dbo.RESULTADOS ON dbo.CAT_FALLO.ID_FALLO =
dbo.RESULTADOS.FALLO ON
dbo.SOLICITUDES.ID_SOLICITUD =
dbo.RESULTADOS.ID_SOLICITUD LEFT OUTER JOIN
dbo.CPQGOB_EQUIPOS INNER JOIN
dbo.PEDIDOS ON dbo.CPQGOB_EQUIPOS.NO_PARTE =
dbo.PEDIDOS.NO_PARTE ON
dbo.SOLICITUDES.ID_SOLICITUD =
dbo.PEDIDOS.ID_SOLICITUD LEFT OUTER JOIN
dbo.DependenciasAdministradoresVenta INNER JOIN
dbo.Usuarios Usuarios2 ON
dbo.DependenciasAdministradoresVenta.idUsuarioTSG = Usuarios2.idUsuario INNER
JOIN
dbo.Usuarios Usuarios3 ON
dbo.DependenciasAdministradoresVenta.idUsuarioPSG = Usuarios3.idUsuario INNER
JOIN
dbo.Usuarios Usuarios4 ON
dbo.DependenciasAdministradoresVenta.idUsuarioIPG = Usuarios4.idUsuario ON
dbo.SOLICITUDES.DEPENDENCIA =
dbo.DependenciasAdministradoresVenta.idDependencia LEFT OUTER JOIN
dbo.Usuarios Usuarios1 INNER JOIN
dbo.GerenteDistritoEjecutivosCuentas ON
Usuarios1.idUsuario = dbo.GerenteDistritoEjecutivosCuentas.IdUsuarioGte RIGHT
OUTER JOIN
dbo.Usuarios Usuarios_1 INNER JOIN
dbo.DistribuidoresEjecutivosCuentas ON
Usuarios_1.idUsuario = dbo.DistribuidoresEjecutivosCuentas.idUsuario ON
dbo.GerenteDistritoEjecutivosCuentas.IdUsuarioPBM =
dbo.DistribuidoresEjecutivosCuentas.idUsuario ON
dbo.SOLICITUDES.ID_EMPRESA =
dbo.DistribuidoresEjecutivosCuentas.idDistribuidor LEFT OUTER JOIN
dbo.tbStates tbStates1 ON DISTRIBUIDORES_1.ESTADO =
tbStates1.StateId
where solicitudes.fec_solicitud >=@dtInicioFechaSolicitud
order by solicitudes.id_solicitud

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


"Bob Barrows [MVP]" wrote:

> Ronny Miranda wrote:
> > I run an ASP script that invokes a stored procedure which creates a
> > table, next step in that same ASP page there's a select statement
> > that loads data from that recent table. That data is loaded into a
> > recordset.
> >
> > Problem is that some of the values printed to the page that are
> > obtained by retrieving data from that recordset are incorrect.
> > Example, if I run that stored proc and then run the select statement
> > directly in query analyzer, one of the values shows 40, but on the
> > ASP page it's shown as 50. Curious thing is that no data manipulation
> > is performed whatsoever, just retrieve and show.
>
> Never ask a database-related question without revealing the database type
> and version. It's almost always relevant.
> Given your references to "query analyzer" I could guess that you are using
> SQL Server, but I may be wrong, and even if I was right, I would still be in
> the dark about the version.
>
> In any case, you need to show us how to recreate your problem. Strip your
> code to the bare minimum needed to reproduce your symptoms and post the
> result here. Show the table creation code as well as the code used to insert
> the data (again - stripped to the bare minimum to allow us to reproduce your
> problem)
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>
>
>

RE: asp not retrieving correct information from a query

The environment is like this:
+windows 2000
+IIS
+ASP 3.0
+MS SQL 2000

The asp that runs the process has this portion of code:

set Conn1=Server.CreateObject ("ADODB.Connection")
set Conn2=Server.CreateObject ("ADODB.Connection")
set rs=Server.CreateObject ("ADODB.Recordset")

Conn1.Open Application("strConexion")
Conn2.Open Application("strConexion")

SQL = "spS_PrepareFunnelForecastReport '" & strFechaParam & "'"
Conn1.Execute(SQL)

SQL = "select * from temptbFunnelForecast"
SET rs = Conn2.Execute(SQL)


and the stored proc spS_PrepareFunnelForecastReport is like this:



SELECT tbStates1.zona_geografica, Usuarios1.Nombre AS Gerente,
Usuarios_1.Nombre AS PBM, Usuarios2.Nombre AS TSG, Usuarios3.Nombre AS PSG,
Usuarios4.Nombre AS IPG,
dbo.dependencias_cat.DESCRIPCION AS Cliente, dbo.dependencias_cat.ESTRATEGICA
AS Listada,
dbo.dependencias_cat.GEM AS SP,
dbo.SOLICITUDES.NOMBRE_PROY, DISTRIBUIDORES1.EMPRESA AS Mayorista,
DISTRIBUIDORES_1.EMPRESA AS Distribuidor,
dbo.PEDIDOS.NO_PARTE, dbo.CPQGOB_EQUIPOS.Activo, dbo.CPQGOB_EQUIPOS.BU,
dbo.CPQGOB_EQUIPOS.DESCRIPCION, dbo.CPQGOB_EQUIPOS.PL,
dbo.CAT_STATUS_SOLICITUD.CICLOVENTA, dbo.CPQGOB_EQUIPOS.PRECIOUSD,
dbo.PEDIDOS.CANTIDAD, dbo.CPQGOB_EQUIPOS.PRECIOUSD *
dbo.PEDIDOS.CANTIDAD AS Total, dbo.SOLICITUDES.FEC_OF_LEGAL,
dbo.SOLICITUDES.FEC_FALLO,
dbo.SOLICITUDES.FEC_ENTREGA_BIENES, dbo.SOLICITUDES.ID_SOLICITUD,
dbo.SOLICITUDES.FEC_ULTIMA_ACT,
dbo.SOLICITUDES.numero_licitacion,
dbo.CAT_STATUS_SOLICITUD.DESCRIPCION AS Status, dbo.CAT_FALLO.DESCRIPCION AS
Fallo, dbo.SOLICITUDES.DualAgregation,
dbo.SOLICITUDES.FEC_SOLICITUD, tbStates1.StateName AS
[Localidad distribuidor], tbStates_1.StateName AS [Localidad cliente]
INTO temptbFunnelForecast
FROM dbo.DISTRIBUIDORES DISTRIBUIDORES_1 RIGHT OUTER JOIN
dbo.DISTRIBUIDORES DISTRIBUIDORES1 RIGHT OUTER JOIN
dbo.CAT_STATUS_SOLICITUD INNER JOIN
dbo.SOLICITUDES INNER JOIN
dbo.dependencias_cat ON dbo.SOLICITUDES.DEPENDENCIA =
dbo.dependencias_cat.ID ON
dbo.CAT_STATUS_SOLICITUD.ID_STATUS =
dbo.SOLICITUDES.STATUS LEFT OUTER JOIN
dbo.tbStates tbStates_1 ON dbo.SOLICITUDES.ESTADO =
tbStates_1.StateId ON DISTRIBUIDORES1.ID_EMPRESA =
dbo.SOLICITUDES.ID_Mayorista ON
DISTRIBUIDORES_1.ID_EMPRESA =
dbo.SOLICITUDES.ID_EMPRESA LEFT OUTER JOIN
dbo.CAT_FALLO INNER JOIN
dbo.RESULTADOS ON dbo.CAT_FALLO.ID_FALLO =
dbo.RESULTADOS.FALLO ON
dbo.SOLICITUDES.ID_SOLICITUD =
dbo.RESULTADOS.ID_SOLICITUD LEFT OUTER JOIN
dbo.CPQGOB_EQUIPOS INNER JOIN
dbo.PEDIDOS ON dbo.CPQGOB_EQUIPOS.NO_PARTE =
dbo.PEDIDOS.NO_PARTE ON
dbo.SOLICITUDES.ID_SOLICITUD =
dbo.PEDIDOS.ID_SOLICITUD LEFT OUTER JOIN
dbo.DependenciasAdministradoresVenta INNER JOIN
dbo.Usuarios Usuarios2 ON
dbo.DependenciasAdministradoresVenta.idUsuarioTSG = Usuarios2.idUsuario INNER
JOIN
dbo.Usuarios Usuarios3 ON
dbo.DependenciasAdministradoresVenta.idUsuarioPSG = Usuarios3.idUsuario INNER
JOIN
dbo.Usuarios Usuarios4 ON
dbo.DependenciasAdministradoresVenta.idUsuarioIPG = Usuarios4.idUsuario ON
dbo.SOLICITUDES.DEPENDENCIA =
dbo.DependenciasAdministradoresVenta.idDependencia LEFT OUTER JOIN
dbo.Usuarios Usuarios1 INNER JOIN
dbo.GerenteDistritoEjecutivosCuentas ON
Usuarios1.idUsuario = dbo.GerenteDistritoEjecutivosCuentas.IdUsuarioGte RIGHT
OUTER JOIN
dbo.Usuarios Usuarios_1 INNER JOIN
dbo.DistribuidoresEjecutivosCuentas ON
Usuarios_1.idUsuario = dbo.DistribuidoresEjecutivosCuentas.idUsuario ON
dbo.GerenteDistritoEjecutivosCuentas.IdUsuarioPBM =
dbo.DistribuidoresEjecutivosCuentas.idUsuario ON
dbo.SOLICITUDES.ID_EMPRESA =
dbo.DistribuidoresEjecutivosCuentas.idDistribuidor LEFT OUTER JOIN
dbo.tbStates tbStates1 ON DISTRIBUIDORES_1.ESTADO =
tbStates1.StateId
where solicitudes.fec_solicitud >=@dtInicioFechaSolicitud
order by solicitudes.id_solicitud

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

"Luke Zhang [MSFT]" wrote:

> Hello,
>
> Regarding the issue, would you please show more detailed code? For example,
> how did you call the store procedure, query and display the data in the
> ASP page, and what is the query you executed in query analyzer? Based on
> my experience, this is more like an problem in coding. So we better double
> check the logic in the code.
>
> Sincerely,
>
> Luke Zhang
>
> Microsoft Online Community Support
> ==================================================
> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/de fault.aspx#notif
> ications.
>
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx .
> ==================================================
>
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>
>
>

Re: asp not retrieving correct information from a query

Given that my server does not have a database with any of the tables
mentioned in your stored procedure, I'm going to have a hard time
reproducing your symptoms on my server, aren't I? ;-)

Please, strip all the tables and columns out that are not related to
reproducing your symptoms. Provide a CREATE TABLE script to allow me to
create the table(s) needed to repro your symptoms. Provide INSERT ... VALUES
statements to insert sample data into the tables. Provide a revised,
simplified stored procedure creation script.


One question, though. Have you verified that the temptbFunnelForecast table
actually gets created?
OK, two more questions: Where have you taken care of making sure
temptbFunnelForecast is not present when you run the
spS_PrepareFunnelForecastReport procedure?

Further points to consider:
Your use of dynamic sql is leaving you vulnerable to hackers using sql
injection:
http://mvp.unixwiz.net/techtips/sql-injection.html
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23

This post describes a better, more secure way to execute your procedures:

http://tinyurl.com/jyy0


Ronny Miranda wrote:
> The environment is like this:
> +windows 2000
> +IIS
> +ASP 3.0
> +MS SQL 2000
>
> The asp that runs the process has this portion of code:
>
> set Conn1=Server.CreateObject ("ADODB.Connection")
> set Conn2=Server.CreateObject ("ADODB.Connection")
> set rs=Server.CreateObject ("ADODB.Recordset")
>
> Conn1.Open Application("strConexion")
> Conn2.Open Application("strConexion")
>
> SQL = "spS_PrepareFunnelForecastReport '" & strFechaParam & "'"
> Conn1.Execute(SQL)
>
> SQL = "select * from temptbFunnelForecast"
> SET rs = Conn2.Execute(SQL)
>
>
> and the stored proc spS_PrepareFunnelForecastReport is like this:
>
>
>
> SELECT tbStates1.zona_geografica, Usuarios1.Nombre AS Gerente,
> Usuarios_1.Nombre AS PBM, Usuarios2.Nombre AS TSG, Usuarios3.Nombre
> AS PSG, Usuarios4.Nombre AS IPG,
> dbo.dependencias_cat.DESCRIPCION AS Cliente,
> dbo.dependencias_cat.ESTRATEGICA AS Listada,
> dbo.dependencias_cat.GEM AS SP,
> dbo.SOLICITUDES.NOMBRE_PROY, DISTRIBUIDORES1.EMPRESA AS Mayorista,
> DISTRIBUIDORES_1.EMPRESA AS Distribuidor,
> dbo.PEDIDOS.NO_PARTE, dbo.CPQGOB_EQUIPOS.Activo,
> dbo.CPQGOB_EQUIPOS.BU,
> dbo.CPQGOB_EQUIPOS.DESCRIPCION, dbo.CPQGOB_EQUIPOS.PL,
> dbo.CAT_STATUS_SOLICITUD.CICLOVENTA,
> dbo.CPQGOB_EQUIPOS.PRECIOUSD, dbo.PEDIDOS.CANTIDAD,
> dbo.CPQGOB_EQUIPOS.PRECIOUSD *
> dbo.PEDIDOS.CANTIDAD AS Total, dbo.SOLICITUDES.FEC_OF_LEGAL,
> dbo.SOLICITUDES.FEC_FALLO,
> dbo.SOLICITUDES.FEC_ENTREGA_BIENES, dbo.SOLICITUDES.ID_SOLICITUD,
> dbo.SOLICITUDES.FEC_ULTIMA_ACT,
> dbo.SOLICITUDES.numero_licitacion,
> dbo.CAT_STATUS_SOLICITUD.DESCRIPCION AS Status,
> dbo.CAT_FALLO.DESCRIPCION AS Fallo, dbo.SOLICITUDES.DualAgregation,
> dbo.SOLICITUDES.FEC_SOLICITUD,
> tbStates1.StateName AS [Localidad distribuidor], tbStates_1.StateName
> AS [Localidad cliente]
> INTO temptbFunnelForecast
> FROM dbo.DISTRIBUIDORES DISTRIBUIDORES_1 RIGHT OUTER JOIN
> dbo.DISTRIBUIDORES DISTRIBUIDORES1 RIGHT OUTER
> JOIN dbo.CAT_STATUS_SOLICITUD INNER JOIN
> dbo.SOLICITUDES INNER JOIN
> dbo.dependencias_cat ON
> dbo.SOLICITUDES.DEPENDENCIA = dbo.dependencias_cat.ID ON
> dbo.CAT_STATUS_SOLICITUD.ID_STATUS =
> dbo.SOLICITUDES.STATUS LEFT OUTER JOIN
> dbo.tbStates tbStates_1 ON
> dbo.SOLICITUDES.ESTADO = tbStates_1.StateId ON
> DISTRIBUIDORES1.ID_EMPRESA = dbo.SOLICITUDES.ID_Mayorista ON
> DISTRIBUIDORES_1.ID_EMPRESA =
> dbo.SOLICITUDES.ID_EMPRESA LEFT OUTER JOIN
> dbo.CAT_FALLO INNER JOIN
> dbo.RESULTADOS ON dbo.CAT_FALLO.ID_FALLO =
> dbo.RESULTADOS.FALLO ON
> dbo.SOLICITUDES.ID_SOLICITUD =
> dbo.RESULTADOS.ID_SOLICITUD LEFT OUTER JOIN
> dbo.CPQGOB_EQUIPOS INNER JOIN
> dbo.PEDIDOS ON dbo.CPQGOB_EQUIPOS.NO_PARTE =
> dbo.PEDIDOS.NO_PARTE ON
> dbo.SOLICITUDES.ID_SOLICITUD =
> dbo.PEDIDOS.ID_SOLICITUD LEFT OUTER JOIN
> dbo.DependenciasAdministradoresVenta INNER JOIN
> dbo.Usuarios Usuarios2 ON
> dbo.DependenciasAdministradoresVenta.idUsuarioTSG =
> Usuarios2.idUsuario INNER JOIN
> dbo.Usuarios Usuarios3 ON
> dbo.DependenciasAdministradoresVenta.idUsuarioPSG =
> Usuarios3.idUsuario INNER JOIN
> dbo.Usuarios Usuarios4 ON
> dbo.DependenciasAdministradoresVenta.idUsuarioIPG =
> Usuarios4.idUsuario ON
> dbo.SOLICITUDES.DEPENDENCIA =
>
>
> dbo.DependenciasAdministradoresVenta.idDependencia LEFT OUTER JOIN
> dbo.Usuarios Usuarios1 INNER JOIN
> dbo.GerenteDistritoEjecutivosCuentas ON
> Usuarios1.idUsuario =
> dbo.GerenteDistritoEjecutivosCuentas.IdUsuarioGte RIGHT OUTER JOIN
> dbo.Usuarios Usuarios_1 INNER JOIN
> dbo.DistribuidoresEjecutivosCuentas ON
> Usuarios_1.idUsuario = dbo.DistribuidoresEjecutivosCuentas.idUsuario
> ON
> dbo.GerenteDistritoEjecutivosCuentas.IdUsuarioPBM =
> dbo.DistribuidoresEjecutivosCuentas.idUsuario ON
> dbo.SOLICITUDES.ID_EMPRESA =
>
> dbo.DistribuidoresEjecutivosCuentas.idDistribuidor LEFT OUTER JOIN
> dbo.tbStates tbStates1 ON DISTRIBUIDORES_1.ESTADO =
> tbStates1.StateId
> where solicitudes.fec_solicitud >=@dtInicioFechaSolicitud
> order by solicitudes.id_solicitud
>
> ------------------------------------------------------------ -----
>
>
> "Bob Barrows [MVP]" wrote:
>
>> Ronny Miranda wrote:
>>> I run an ASP script that invokes a stored procedure which creates a
>>> table, next step in that same ASP page there's a select statement
>>> that loads data from that recent table. That data is loaded into a
>>> recordset.
>>>
>>> Problem is that some of the values printed to the page that are
>>> obtained by retrieving data from that recordset are incorrect.
>>> Example, if I run that stored proc and then run the select statement
>>> directly in query analyzer, one of the values shows 40, but on the
>>> ASP page it's shown as 50. Curious thing is that no data
>>> manipulation
>>> is performed whatsoever, just retrieve and show.
>>
>> Never ask a database-related question without revealing the database
>> type and version. It's almost always relevant.
>> Given your references to "query analyzer" I could guess that you are
>> using SQL Server, but I may be wrong, and even if I was right, I
>> would still be in the dark about the version.
>>
>> In any case, you need to show us how to recreate your problem. Strip
>> your code to the bare minimum needed to reproduce your symptoms and
>> post the result here. Show the table creation code as well as the
>> code used to insert the data (again - stripped to the bare minimum
>> to allow us to reproduce your problem)
>> --
>> Microsoft MVP - ASP/ASP.NET
>> Please reply to the newsgroup. This email account is my spam trap so
>> I don't check it very often. If you must reply off-line, then remove
>> the "NO SPAM"

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: asp not retrieving correct information from a query

Thank you Bob, I'll post the scripts on monday, I'm working on it as of now.

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

"Bob Barrows [MVP]" wrote:

> Given that my server does not have a database with any of the tables
> mentioned in your stored procedure, I'm going to have a hard time
> reproducing your symptoms on my server, aren't I? ;-)
>
> Please, strip all the tables and columns out that are not related to
> reproducing your symptoms. Provide a CREATE TABLE script to allow me to
> create the table(s) needed to repro your symptoms. Provide INSERT ... VALUES
> statements to insert sample data into the tables. Provide a revised,
> simplified stored procedure creation script.
>
>
> One question, though. Have you verified that the temptbFunnelForecast table
> actually gets created?
> OK, two more questions: Where have you taken care of making sure
> temptbFunnelForecast is not present when you run the
> spS_PrepareFunnelForecastReport procedure?
>
> Further points to consider:
> Your use of dynamic sql is leaving you vulnerable to hackers using sql
> injection:
> http://mvp.unixwiz.net/techtips/sql-injection.html
> http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23
>
> This post describes a better, more secure way to execute your procedures:
>
> http://tinyurl.com/jyy0
>
>
> Ronny Miranda wrote:
> > The environment is like this:
> > +windows 2000
> > +IIS
> > +ASP 3.0
> > +MS SQL 2000
> >
> > The asp that runs the process has this portion of code:
> >
> > set Conn1=Server.CreateObject ("ADODB.Connection")
> > set Conn2=Server.CreateObject ("ADODB.Connection")
> > set rs=Server.CreateObject ("ADODB.Recordset")
> >
> > Conn1.Open Application("strConexion")
> > Conn2.Open Application("strConexion")
> >
> > SQL = "spS_PrepareFunnelForecastReport '" & strFechaParam & "'"
> > Conn1.Execute(SQL)
> >
> > SQL = "select * from temptbFunnelForecast"
> > SET rs = Conn2.Execute(SQL)
> >
> >
> > and the stored proc spS_PrepareFunnelForecastReport is like this:
> >
> >
> >
> > SELECT tbStates1.zona_geografica, Usuarios1.Nombre AS Gerente,
> > Usuarios_1.Nombre AS PBM, Usuarios2.Nombre AS TSG, Usuarios3.Nombre
> > AS PSG, Usuarios4.Nombre AS IPG,
> > dbo.dependencias_cat.DESCRIPCION AS Cliente,
> > dbo.dependencias_cat.ESTRATEGICA AS Listada,
> > dbo.dependencias_cat.GEM AS SP,
> > dbo.SOLICITUDES.NOMBRE_PROY, DISTRIBUIDORES1.EMPRESA AS Mayorista,
> > DISTRIBUIDORES_1.EMPRESA AS Distribuidor,
> > dbo.PEDIDOS.NO_PARTE, dbo.CPQGOB_EQUIPOS.Activo,
> > dbo.CPQGOB_EQUIPOS.BU,
> > dbo.CPQGOB_EQUIPOS.DESCRIPCION, dbo.CPQGOB_EQUIPOS.PL,
> > dbo.CAT_STATUS_SOLICITUD.CICLOVENTA,
> > dbo.CPQGOB_EQUIPOS.PRECIOUSD, dbo.PEDIDOS.CANTIDAD,
> > dbo.CPQGOB_EQUIPOS.PRECIOUSD *
> > dbo.PEDIDOS.CANTIDAD AS Total, dbo.SOLICITUDES.FEC_OF_LEGAL,
> > dbo.SOLICITUDES.FEC_FALLO,
> > dbo.SOLICITUDES.FEC_ENTREGA_BIENES, dbo.SOLICITUDES.ID_SOLICITUD,
> > dbo.SOLICITUDES.FEC_ULTIMA_ACT,
> > dbo.SOLICITUDES.numero_licitacion,
> > dbo.CAT_STATUS_SOLICITUD.DESCRIPCION AS Status,
> > dbo.CAT_FALLO.DESCRIPCION AS Fallo, dbo.SOLICITUDES.DualAgregation,
> > dbo.SOLICITUDES.FEC_SOLICITUD,
> > tbStates1.StateName AS [Localidad distribuidor], tbStates_1.StateName
> > AS [Localidad cliente]
> > INTO temptbFunnelForecast
> > FROM dbo.DISTRIBUIDORES DISTRIBUIDORES_1 RIGHT OUTER JOIN
> > dbo.DISTRIBUIDORES DISTRIBUIDORES1 RIGHT OUTER
> > JOIN dbo.CAT_STATUS_SOLICITUD INNER JOIN
> > dbo.SOLICITUDES INNER JOIN
> > dbo.dependencias_cat ON
> > dbo.SOLICITUDES.DEPENDENCIA = dbo.dependencias_cat.ID ON
> > dbo.CAT_STATUS_SOLICITUD.ID_STATUS =
> > dbo.SOLICITUDES.STATUS LEFT OUTER JOIN
> > dbo.tbStates tbStates_1 ON
> > dbo.SOLICITUDES.ESTADO = tbStates_1.StateId ON
> > DISTRIBUIDORES1.ID_EMPRESA = dbo.SOLICITUDES.ID_Mayorista ON
> > DISTRIBUIDORES_1.ID_EMPRESA =
> > dbo.SOLICITUDES.ID_EMPRESA LEFT OUTER JOIN
> > dbo.CAT_FALLO INNER JOIN
> > dbo.RESULTADOS ON dbo.CAT_FALLO.ID_FALLO =
> > dbo.RESULTADOS.FALLO ON
> > dbo.SOLICITUDES.ID_SOLICITUD =
> > dbo.RESULTADOS.ID_SOLICITUD LEFT OUTER JOIN
> > dbo.CPQGOB_EQUIPOS INNER JOIN
> > dbo.PEDIDOS ON dbo.CPQGOB_EQUIPOS.NO_PARTE =
> > dbo.PEDIDOS.NO_PARTE ON
> > dbo.SOLICITUDES.ID_SOLICITUD =
> > dbo.PEDIDOS.ID_SOLICITUD LEFT OUTER JOIN
> > dbo.DependenciasAdministradoresVenta INNER JOIN
> > dbo.Usuarios Usuarios2 ON
> > dbo.DependenciasAdministradoresVenta.idUsuarioTSG =
> > Usuarios2.idUsuario INNER JOIN
> > dbo.Usuarios Usuarios3 ON
> > dbo.DependenciasAdministradoresVenta.idUsuarioPSG =
> > Usuarios3.idUsuario INNER JOIN
> > dbo.Usuarios Usuarios4 ON
> > dbo.DependenciasAdministradoresVenta.idUsuarioIPG =
> > Usuarios4.idUsuario ON
> > dbo.SOLICITUDES.DEPENDENCIA =
> >
> >
> > dbo.DependenciasAdministradoresVenta.idDependencia LEFT OUTER JOIN
> > dbo.Usuarios Usuarios1 INNER JOIN
> > dbo.GerenteDistritoEjecutivosCuentas ON
> > Usuarios1.idUsuario =
> > dbo.GerenteDistritoEjecutivosCuentas.IdUsuarioGte RIGHT OUTER JOIN
> > dbo.Usuarios Usuarios_1 INNER JOIN
> > dbo.DistribuidoresEjecutivosCuentas ON
> > Usuarios_1.idUsuario = dbo.DistribuidoresEjecutivosCuentas.idUsuario
> > ON
> > dbo.GerenteDistritoEjecutivosCuentas.IdUsuarioPBM =
> > dbo.DistribuidoresEjecutivosCuentas.idUsuario ON
> > dbo.SOLICITUDES.ID_EMPRESA =
> >
> > dbo.DistribuidoresEjecutivosCuentas.idDistribuidor LEFT OUTER JOIN
> > dbo.tbStates tbStates1 ON DISTRIBUIDORES_1.ESTADO =
> > tbStates1.StateId
> > where solicitudes.fec_solicitud >=@dtInicioFechaSolicitud
> > order by solicitudes.id_solicitud
> >
> > ------------------------------------------------------------ -----
> >
> >
> > "Bob Barrows [MVP]" wrote:
> >
> >> Ronny Miranda wrote:
> >>> I run an ASP script that invokes a stored procedure which creates a
> >>> table, next step in that same ASP page there's a select statement
> >>> that loads data from that recent table. That data is loaded into a
> >>> recordset.
> >>>
> >>> Problem is that some of the values printed to the page that are
> >>> obtained by retrieving data from that recordset are incorrect.
> >>> Example, if I run that stored proc and then run the select statement
> >>> directly in query analyzer, one of the values shows 40, but on the
> >>> ASP page it's shown as 50. Curious thing is that no data
> >>> manipulation
> >>> is performed whatsoever, just retrieve and show.
> >>
> >> Never ask a database-related question without revealing the database
> >> type and version. It's almost always relevant.
> >> Given your references to "query analyzer" I could guess that you are
> >> using SQL Server, but I may be wrong, and even if I was right, I
> >> would still be in the dark about the version.
> >>
> >> In any case, you need to show us how to recreate your problem. Strip
> >> your code to the bare minimum needed to reproduce your symptoms and
> >> post the result here. Show the table creation code as well as the
> >> code used to insert the data (again - stripped to the bare minimum
> >> to allow us to reproduce your problem)
> >> --
> >> Microsoft MVP - ASP/ASP.NET
> >> Please reply to the newsgroup. This email account is my spam trap so
> >> I don't check it very often. If you must reply off-line, then remove
> >> the "NO SPAM"
>
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>
>
>

RE: asp not retrieving correct information from a query

Thank you for the code. I want to wait for your script also since I cannot
test the code without the underly tables and records. I also suggest you
may run the asp application in debug mode, and set a break point at
following line:

Conn1.Execute(SQL)

Once this line has been executed, you may switch to query analyzer and
execute "select * from temptbFunnelForecast" right way, (please make sure
you are working on same database, ) and then execute following code:

SQL = "select * from temptbFunnelForecast"
SET rs = Conn2.Execute(SQL)

To see what you get.

Sincerely,

Luke Zhang

Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/de fault.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx .
==================================================

This posting is provided "AS IS" with no warranties, and confers no rights.

RE: asp not retrieving correct information from a query

I have the script files ready, but where can I upload them for you to check?

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

"Luke Zhang [MSFT]" wrote:

> Thank you for the code. I want to wait for your script also since I cannot
> test the code without the underly tables and records. I also suggest you
> may run the asp application in debug mode, and set a break point at
> following line:
>
> Conn1.Execute(SQL)
>
> Once this line has been executed, you may switch to query analyzer and
> execute "select * from temptbFunnelForecast" right way, (please make sure
> you are working on same database, ) and then execute following code:
>
> SQL = "select * from temptbFunnelForecast"
> SET rs = Conn2.Execute(SQL)
>
> To see what you get.
>
> Sincerely,
>
> Luke Zhang
>
> Microsoft Online Community Support
> ==================================================
> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/de fault.aspx#notif
> ications.
>
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx .
> ==================================================
>
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>
>
>

Re: asp not retrieving correct information from a query

Ronny Miranda wrote:
> I have the script files ready, but where can I upload them for you to
> check?
>

It's just text, Paste it into the reply to this message. Remember, the
operative word for repro scripts is "small" ;-)
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: asp not retrieving correct information from a query

=====================================
~~~~~ THESE ARE THE SQL SCRIPTS ~~~~~


/* ===================== CREATE TABLES ===============================*/

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[CAT_STATUS_SOLICITUD]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[CAT_STATUS_SOLICITUD]


if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[dependencias_cat]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
drop table [dbo].[dependencias_cat]


if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tbStates]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tbStates]


if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[CAT_FALLO]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[CAT_FALLO]


if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[CPQGOB_EQUIPOS]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)
drop table [dbo].[CPQGOB_EQUIPOS]


if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[PEDIDOS]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[PEDIDOS]


if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[GerenteDistritoEjecutivosCuentas]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[GerenteDistritoEjecutivosCuentas]


if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Usuarios]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Usuarios]


if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[DistribuidoresEjecutivosCuentas]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[DistribuidoresEjecutivosCuentas]


if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[DependenciasAdministradoresVenta]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[DependenciasAdministradoresVenta]


if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[DOCUMENTOS]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[DOCUMENTOS]


if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[SOLICITUDES]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[SOLICITUDES]


if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[DISTRIBUIDORES]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)
drop table [dbo].[DISTRIBUIDORES]


if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[RESULTADOS]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[RESULTADOS]
GO

CREATE TABLE [dbo].[RESULTADOS] (
[ID_SOLICITUD] [int] NOT NULL ,
[FALLO] [int] NULL ,
[FALLO_LEGAL_CALIF] [bit] NOT NULL ,
[FALLO_LEGAL_QUE] [varchar] (255) NULL ,
[FALLO_TECNICO_CALIF] [bit] NOT NULL ,
[FALLO_TECNICO_QUE] [varchar] (255) NULL ,
[FALLO_ECONOMICO_CALIF] [bit] NOT NULL ,
[FALLO_ECONOMICO_QUE] [varchar] (6800) NULL ,
[FALLO_DEFINITIVO_CALIF] [bit] NOT NULL ,
[FALLO_DEFINITIVO_QUE] [varchar] (255) NULL ,
[HP] [bit] NOT NULL ,
[IBM] [bit] NOT NULL ,
[DELL] [bit] NOT NULL ,
[ACER] [bit] NOT NULL ,
[GATEWAY] [bit] NOT NULL ,
[LANIX] [bit] NOT NULL ,
[TOSHIBA] [bit] NOT NULL ,
[COMPAQ] [bit] NOT NULL ,
[OTRA] [bit] NOT NULL ,
[CUALOTRA] [varchar] (50) NULL ,
[DISTRIBUIDOR] [varchar] (255) NULL ,
[GANO_ESCRITORIO] [int] NULL ,
[GANO_SERVIDORES] [int] NULL ,
[GANO_PORTATILES] [int] NULL ,
[GANO_OTRAS] [int] NULL ,
[RAZON_PERDIDA] [bit] NOT NULL ,
[DIFERENCIAL] [varchar] (20) NULL ,
[HPM] [nvarchar] (20) NULL
) ON [PRIMARY]
GO




CREATE TABLE [dbo].[DISTRIBUIDORES] (
[ID_EMPRESA] [int] ,
[EMPRESA] [varchar] (255) NULL ,
[CORPORATIVO] [varchar] (255) NULL ,
[ESQ_COMERCIAL] [int] NULL ,
[ESQ_SOPORTE] [int] NULL ,
[CanBuySupplies] [int] NULL ,
[CanBuyHardware] [int] NULL ,
[limite_credito] [float] NULL ,
[alerta_amarilla] [int] NULL ,
[alerta_roja] [int] NULL ,
[plancuenta] [bit] NULL ,
[VOL_VENTAS_ANUAL] [varchar] (15) NULL ,
[VOL_VENTAS_B] [varchar] (15) NULL ,
[NO_EMPLEADOS] [tinyint] NULL ,
[SECTOR1] [tinyint] NULL ,
[SECTOR2] [tinyint] NULL ,
[SECTOR3] [tinyint] NULL ,
[ALCANCES] [int] NULL ,
[CALLE_NO] [varchar] (150) NULL ,
[COLONIA] [varchar] (60) NULL ,
[DELEGACION] [varchar] (60) NULL ,
[CP] [varchar] (6) NULL ,
[CIUDAD] [varchar] (40) NULL ,
[ESTADO] [varchar] (4) NULL ,
[TEL1] [varchar] (15) NULL ,
[TEL2] [varchar] (15) NULL ,
[FAX] [varchar] (15) NULL ,
[DIR_GRAL] [varchar] (60) NULL ,
[DIR_GRAL_MAIL] [varchar] (100) NULL ,
[GTE_VENTAS] [varchar] (60) NULL ,
[GTE_VENTAS_MAIL] [varchar] (100) NULL ,
[GTE_SOTEC] [varchar] (60) NULL ,
[GTE_SOTEC_MAIL] [varchar] (100) NULL ,
[RESP_CONCURSOS] [varchar] (60) NULL ,
[RESP_CONCURSOS_MAIL] [varchar] (255) NULL ,
[RESP_CONCURSOS_PIN] [varchar] (80) NULL ,
[RESP_CONCURSOS_TEL2] [varchar] (15) NULL ,
[ICM1] [varchar] (60) NULL ,
[ICM1_MAIL] [varchar] (40) NULL ,
[ICM2] [varchar] (60) NULL ,
[ICM2_MAIL] [varchar] (40) NULL ,
[ICC1] [varchar] (60) NULL ,
[ICC1_MAIL] [varchar] (40) NULL ,
[ICC2] [varchar] (60) NULL ,
[ICC2_MAIL] [varchar] (40) NULL ,
[ICCN1] [varchar] (60) NULL ,
[ICCN1_MAIL] [varchar] (40) NULL ,
[ULT_LIC_CONCURSADA] [varchar] (40) NULL ,
[ULT_LIC_GANADA] [varchar] (40) NULL ,
[OM_DELL] [bit] ,
[OM_HP] [bit] ,
[OM_ACER] [bit] ,
[OM_GTWY] [bit] ,
[OM_IBM] [bit] ,
[OM_SUN] [bit] ,
[OM_LANIX] [bit] ,
[OM_OLIV] [bit] ,
[OM_MAC] [bit] ,
[OM_OTROS] [bit] ,
[OM_CUAL] [varchar] (255) NULL ,
[INTEGRADOR] [text] NULL ,
[VA_1] [int] NULL ,
[VA_2] [int] NULL ,
[VA_3] [int] NULL ,
[VECES_AYUDA_CPQ] [int] NULL ,
[INF_RESULTADOS] [bit] ,
[EXPED_EN_ORDEN] [bit] ,
[SERV_HARDWARE] [bit] ,
[SERV_SOFTWARE] [bit] ,
[SERV_CONFIGURACION] [bit] ,
[SERV_CABLEADO] [bit] ,
[SERV_PTO_PTO] [bit] ,
[SERV_INST_REDES] [bit] ,
[SERV_RES_PROB] [bit] ,
[SERV_INST_HYS] [bit] ,
[SERV_ON_SITE] [bit] ,
[SERV_BRID_GATE] [bit] ,
[SERV_TEL] [bit] ,
[BI_SERVERS] [varchar] (5) NULL ,
[BI_PC] [varchar] (5) NULL ,
[BI_ENLACES] [tinyint] NULL ,
[SO_LAN_MGR] [bit] ,
[SO_3COM] [bit] ,
[SO_BANYAN] [bit] ,
[SO_PC_LAN] [bit] ,
[SO_PC_LAV_SVR] [bit] ,
[SO_NETWARE] [bit] ,
[SO_OS2] [bit] ,
[SO_UNIX] [bit] ,
[SO_XENIX] [bit] ,
[SO_SUN_RIVER] [bit] ,
[SO_OPEN_VMS] [bit] ,
[SO_VMS] [bit] ,
[SO_DIGITAL_UNIX] [bit] ,
[DB_SQL_BASE] [bit] ,
[DB_ORACLE] [bit] ,
[DB_INGRESS] [bit] ,
[DB_SQL_SERVER] [bit] ,
[DB_INFORMIX] [bit] ,
[DB_OTROS] [bit] ,
[CERTIF_COMERCIALES] [text] NULL ,
[CERTIF_SERVICIO] [text] NULL ,
[LOGIN] [varchar] (20) NULL ,
[PASSWORD] [varchar] (20) NULL ,
[STATUS] [int] NULL ,
[MAIL_ENVIADO] [binary] (1) NULL ,
[SO_LINUX] [tinyint] NULL ,
[SO_SOLARIS] [tinyint] NULL ,
[SO_HP_UX] [tinyint] NULL ,
[SO_AIX] [tinyint] NULL ,
[OTRO_CUAL] [varchar] (20) NULL ,
[AccesoClausulas] [bit] ,
[CreditHold] [binary] (10) NULL ,
[RFC] [varchar] (13)
)


ALTER TABLE [dbo].[DISTRIBUIDORES] ADD
CONSTRAINT [DF_DISTRIBUIDORES_ESQ_SOPORTE] DEFAULT (0) FOR [ESQ_SOPORTE],
CONSTRAINT [DF_DISTRIBUIDORES_CanBuySupplies] DEFAULT (0) FOR
[CanBuySupplies],
CONSTRAINT [DF_DISTRIBUIDORES_CanBuyHardware] DEFAULT (1) FOR
[CanBuyHardware],
CONSTRAINT [DF_DISTRIBUIDORES_OM_OLIV] DEFAULT (0) FOR [OM_OLIV],
CONSTRAINT [DF_DISTRIBUIDORES_OM_MAC] DEFAULT (0) FOR [OM_MAC],
CONSTRAINT [DF_DISTRIBUIDORES_AccesoClausulas] DEFAULT (0) FOR
[AccesoClausulas],
CONSTRAINT [DF_DISTRIBUIDORES_CreditHold] DEFAULT (0) FOR [CreditHold]







CREATE TABLE [dbo].[CAT_STATUS_SOLICITUD] (
[ID_STATUS] [int] ,
[DESCRIPCION] [varchar] (50) ,
[CICLOVENTA] [int] NULL
) ON [PRIMARY]


CREATE TABLE [dbo].[dependencias_cat] (
[ID] [int] ,
[DESCRIPCION] [varchar] (255) NULL ,
[ESTRATEGICA] [bit] ,
[GEM] [bit] NULL ,
[Industria] [nvarchar] (20) NULL
)


CREATE TABLE [dbo].[tbStates] (
[StateId] [float] NULL ,
[StateName] [nvarchar] (255) NULL ,
[Abbreviation] [nvarchar] (255) NULL ,
[CountryId] [float] NULL ,
[zona_geografica] [nvarchar] (20) NULL ,
[EjecutivoCuenta] [int] NULL
) ON [PRIMARY]


CREATE TABLE [dbo].[CAT_FALLO] (
[ID_FALLO] [int] ,
[DESCRIPCION] [varchar] (50) ,
[CICLOVENTA] [int] NULL
) ON [PRIMARY]


CREATE TABLE [dbo].[CPQGOB_EQUIPOS] (
[NO_PARTE] [varchar] (20) ,
[DESCRIPCION] [varchar] (255) NULL ,
[PRECIO] [float] NULL ,
[PRECIOUSD] [float] NULL ,
[Activo] [bit] ,
[Supplies] [int] NULL ,
[PL] [varchar] (10) NULL ,
[BU] [varchar] (30) NULL ,
[Familia] [nvarchar] (50) NULL
) ON [PRIMARY]


CREATE TABLE [dbo].[PEDIDOS] (
[ID_SOLICITUD] [int] NULL ,
[NO_PARTE] [varchar] (11) NULL ,
[CANTIDAD] [int] NULL ,
[ID_FOLIOTEMP] [int] NULL ,
[PRECIO] [float]
) ON [PRIMARY]


CREATE TABLE [dbo].[GerenteDistritoEjecutivosCuentas] (
[IdUsuarioGte] [int] ,
[IDUsuarioPBM] [int]
) ON [PRIMARY]


CREATE TABLE [dbo].[Usuarios] (
[idUsuario] [int] ,
[login] [varchar] (128) ,
[password] [varchar] (128) ,
[Nombre] [varchar] (255) ,
[tipoUsuario] [int] ,
[entidad] [int] NULL ,
[EMail] [varchar] (255) NULL
) ON [PRIMARY]


CREATE TABLE [dbo].[DistribuidoresEjecutivosCuentas] (
[idDistribuidor] [int] ,
[idUsuario] [int] ,
[idUsuarioShadow] [int] NULL
) ON [PRIMARY]


CREATE TABLE [dbo].[DependenciasAdministradoresVenta] (
[idDependencia] [int] ,
[idUsuarioTSG] [int] ,
[idUsuarioPSG] [int] NULL ,
[idUsuarioIPG] [int] NULL
) ON [PRIMARY]


CREATE TABLE [dbo].[DOCUMENTOS] (
[ID_DOCTO] [int] IDENTITY (1, 1) ,
[DOCUMENTO] [image] NULL ,
[NOMBRE] [varchar] (255) NULL ,
[DESCRIPCION] [varchar] (255) NULL ,
[DocumentoMuestra] [varchar] (50) NULL ,
[Supplies] [int] NULL ,
[Hardware] [int] NULL ,
[IdSector] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]


CREATE TABLE [dbo].[SOLICITUDES] (
[ID_SOLICITUD] [int] ,
[ID_EMPRESA] [int] NULL ,
[DEPENDENCIA] [int] NULL ,
[ESTADO] [int] NULL ,
[AREA] [varchar] (900) NULL ,
[SUBDIRECCION] [varchar] (255) NULL ,
[MAYORISTA] [varchar] (60) NULL ,
[NUMERO_LICITACION] [varchar] (255) NULL ,
[TIPO_LICITACION] [varchar] (5) NULL ,
[CLASE_LICITACION] [varchar] (1) NULL ,
[NOMBRE_PROY] [varchar] (255) NULL ,
[FEC_PUBLIC] [smalldatetime] NULL ,
[FEC_BASES] [smalldatetime] NULL ,
[FEC_JUNTA_ACL] [smalldatetime] NULL ,
[FEC_OF_LEGAL] [smalldatetime] NULL ,
[FEC_FALLO] [smalldatetime] NULL ,
[FEC_ENTREGA_BIENES] [smalldatetime] NULL ,
[DESC_PRESENCIA] [text] NULL ,
[FACTORES_CRITICOS] [text] NULL ,
[FEC_ULTIMA_ACT] [datetime] NULL ,
[FEC_SOLICITUD] [datetime] NULL ,
[STATUS] [int] NULL ,
[CUENTA] [int] NULL ,
[ALPHA_SERVERS] [bit] ,
[ALPHA_WORKSTATION] [bit] ,
[NETWORKING] [bit] ,
[STORAGE_WORKS] [bit] ,
[HIMALAYAS] [bit] ,
[CARTA_LIBRE] [text] NULL ,
[ID_FOLIOTEMP] [int] NULL ,
[RequiereBusinessCase] [bit] ,
[ID_Mayorista] [int] NULL ,
[IPG] [bit] NULL ,
[PSG] [bit] NULL ,
[HP_Proliant] [bit] NULL ,
[HPOX] [bit] NULL ,
[HP_Alpha_Server] [bit] NULL ,
[HP_Storage_Works] [bit] NULL ,
[HP_SW] [bit] NULL ,
[HasSupplies] [int] NULL ,
[comentarios] [text] NULL ,
[bitacora] [int] NULL ,
[factor_decision] [text] NULL ,
[specialprice] [int] NULL ,
[avalado] [bit] NULL ,
[bases] [bit] NULL ,
[certificado] [bit] NULL ,
[cartasimpresas] [bit] NULL ,
[FacturaHP] [int] NULL ,
[razones] [nvarchar] (1000) NULL ,
[Proyecto] [int] NULL ,
[DualAgregation] [int] NULL ,
[IdSector] [int] NULL
)

================================================
(CONTINUED IN THE NEXT POST)

Re: asp not retrieving correct information from a query

(CONTINUED FROM PREVIOUS POST)


/* LOS FOLIOS QUE VAN AL 70% - Folios con status completa y fallo ganada. */

UPDATE temptbFunnelForecast
SET cicloventa = 70
WHERE id_solicitud in
(
SELECT distinct id_solicitud
FROM temptbFunnelForecast
WHERE status = 'completa' and fallo = 'ganada' and cicloventa is null
)


/* ================== OBTENER EL REPORTE FINAL REAL ==================*/
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO


~~~~~ THESE IS THE ASP PAGE ~~~~~
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<%


Dim Conn
Dim rs
dim SQL

strConexion = "Provider=SQLOLEDB.1;Password=password;Persist Security
Info=True;User ID=testUser;Initial Catalog=testGobierno;DATABASE=testGobierno"

strFechaParam = Request("dfFecha")


if (strFechaParam="") then
strFechaParam = "2006-07-01"
end if

set Conn1=Server.CreateObject ("ADODB.Connection")
set Conn2=Server.CreateObject ("ADODB.Connection")
set rs=Server.CreateObject ("ADODB.Recordset")

Conn1.Open strConexion
Conn2.Open strConexion

'SQL = "spS_PrepareFunnelForecastReport '" & strFechaParam & "'"
'Conn1.Execute(SQL)

SQL = "select * from temptbFunnelForecast"

SET rs = Conn2.Execute(SQL)


' XL Heading
'Response.ContentType = "application/vnd.ms-excel"
%>

Re: asp not retrieving correct information from a query

(CONTINUED FROM PREVIOUS POST)



/* ========= POPULATE TABLES =====================*/


INSERT INTO [CAT_STATUS_SOLICITUD]
([ID_STATUS],[DESCRIPCION],[CICLOVENTA])VALUES(1,'revisión' ,50)
INSERT INTO [CAT_STATUS_SOLICITUD]
([ID_STATUS],[DESCRIPCION],[CICLOVENTA])VALUES(2,'aceptada', 60)
INSERT INTO [CAT_STATUS_SOLICITUD]
([ID_STATUS],[DESCRIPCION],[CICLOVENTA])VALUES(3,'declinada' ,0)
INSERT INTO [CAT_STATUS_SOLICITUD]
([ID_STATUS],[DESCRIPCION],[CICLOVENTA])VALUES(4,'rechazada' ,0)
INSERT INTO [CAT_STATUS_SOLICITUD]
([ID_STATUS],[DESCRIPCION],[CICLOVENTA])VALUES(5,'completa', NULL)
INSERT INTO [CAT_STATUS_SOLICITUD]
([ID_STATUS],[DESCRIPCION],[CICLOVENTA])VALUES(6,'incompleta ',60)
INSERT INTO [CAT_STATUS_SOLICITUD]
([ID_STATUS],[DESCRIPCION],[CICLOVENTA])VALUES(7,'cancelada' ,0)
INSERT INTO [CAT_STATUS_SOLICITUD]
([ID_STATUS],[DESCRIPCION],[CICLOVENTA])VALUES(8,'aplazada', 60)
INSERT INTO [CAT_STATUS_SOLICITUD]
([ID_STATUS],[DESCRIPCION],[CICLOVENTA])VALUES(9,'caduca',0)
INSERT INTO [CAT_STATUS_SOLICITUD]
([ID_STATUS],[DESCRIPCION],[CICLOVENTA])VALUES(10,'Ordenado' ,90)
INSERT INTO [CAT_STATUS_SOLICITUD]
([ID_STATUS],[DESCRIPCION],[CICLOVENTA])VALUES(11,'Embarcado ',95)
INSERT INTO [CAT_STATUS_SOLICITUD]
([ID_STATUS],[DESCRIPCION],[CICLOVENTA])VALUES(12,'Facturado ',100)


INSERT INTO [dependencias_cat]
([ID],[DESCRIPCION],[ESTRATEGICA],[GEM],[Industria])VALUES(3 119,'DEPEND01',0,0,NULL)
INSERT INTO [dependencias_cat]
([ID],[DESCRIPCION],[ESTRATEGICA],[GEM],[Industria])VALUES(3 589,'DEPEND02',0,0,NULL)
INSERT INTO [dependencias_cat]
([ID],[DESCRIPCION],[ESTRATEGICA],[GEM],[Industria])VALUES(8 1,'DEPEND03,
FUERZA AEREA Y ARMADA, S.N.C.',0,1,NULL)
INSERT INTO [dependencias_cat]
([ID],[DESCRIPCION],[ESTRATEGICA],[GEM],[Industria])VALUES(3 437,'DEPEND04 DE
C.V.',0,0,NULL)


INSERT INTO [tbStates]
([StateId],[StateName],[Abbreviation],[CountryId],[zona_geog rafica],[EjecutivoCuenta])VALUES(9.000000000000000e+000,'Dis trito
Federal','DF',1.000000000000000e+000,'Centro',NULL)
INSERT INTO [tbStates]
([StateId],[StateName],[Abbreviation],[CountryId],[zona_geog rafica],[EjecutivoCuenta])VALUES(2.400000000000000e+001,'Son ora','SON',1.000000000000000e+000,'Norte',3279)
INSERT INTO [tbStates]
([StateId],[StateName],[Abbreviation],[CountryId],[zona_geog rafica],[EjecutivoCuenta])VALUES(8.000000000000000e+000,'Chi huahua','CHI',1.000000000000000e+000,'Norte',2506)
INSERT INTO [tbStates]
([StateId],[StateName],[Abbreviation],[CountryId],[zona_geog rafica],[EjecutivoCuenta])VALUES(6.000000000000000e+000,'Col ima','COL',1.000000000000000e+000,'Occidente',2611)
INSERT INTO [tbStates]
([StateId],[StateName],[Abbreviation],[CountryId],[zona_geog rafica],[EjecutivoCuenta])VALUES(3.000000000000000e+000,'Baj a
California Sur','BCS',1.000000000000000e+000,'Occidente',2506)
INSERT INTO [tbStates]
([StateId],[StateName],[Abbreviation],[CountryId],[zona_geog rafica],[EjecutivoCuenta])VALUES(3.000000000000000e+001,'Ver acruz','VER',1.000000000000000e+000,'Sur-Este',2818)

INSERT INTO [CAT_FALLO]
([ID_FALLO],[DESCRIPCION],[CICLOVENTA])VALUES(1,'ganada',70)
INSERT INTO [CAT_FALLO]
([ID_FALLO],[DESCRIPCION],[CICLOVENTA])VALUES(2,'perdida',0)
INSERT INTO [CAT_FALLO]
([ID_FALLO],[DESCRIPCION],[CICLOVENTA])VALUES(3,'desierta',0 )
INSERT INTO [CAT_FALLO]
([ID_FALLO],[DESCRIPCION],[CICLOVENTA])VALUES(4,'Cancelada', NULL)
INSERT INTO [CAT_FALLO] ([ID_FALLO],[DESCRIPCION],[CICLOVENTA])VALUES(5,'No
Participo',NULL)
INSERT INTO [CAT_FALLO]
([ID_FALLO],[DESCRIPCION],[CICLOVENTA])VALUES(6,'Pospuesta', NULL)


INSERT INTO [CPQGOB_EQUIPOS]
([NO_PARTE],[DESCRIPCION],[PRECIO],[PRECIOUSD],[Activo],[Sup plies],[PL],[BU],[Familia])VALUES('PA716A','Puerto
X',2.990000000000000e+002,2.600000000000000e+001,1,0,'9F','P SG','9F')
INSERT INTO [CPQGOB_EQUIPOS]
([NO_PARTE],[DESCRIPCION],[PRECIO],[PRECIOUSD],[Activo],[Sup plies],[PL],[BU],[Familia])VALUES('U5000E','Servicio
AA',1.437500000000000e+003,1.250000000000000e+002,1,0,'MG',' PSG','MG')
INSERT INTO [CPQGOB_EQUIPOS]
([NO_PARTE],[DESCRIPCION],[PRECIO],[PRECIOUSD],[Activo],[Sup plies],[PL],[BU],[Familia])VALUES('U7848E','HServicio
02',2.403500000000000e+003,2.090000000000000e+002,1,0,'MG',' PSG','MG')
INSERT INTO [CPQGOB_EQUIPOS]
([NO_PARTE],[DESCRIPCION],[PRECIO],[PRECIOUSD],[Activo],[Sup plies],[PL],[BU],[Familia])VALUES('U4818PE','escritorio
NO',1.381150000000000e+003,1.201000000000000e+002,1,0,'R6',' IPG','R6')


INSERT INTO [GerenteDistritoEjecutivosCuentas]
([IdUsuarioGte],[IDUsuarioPBM])VALUES(3920,3240)
INSERT INTO [GerenteDistritoEjecutivosCuentas]
([IdUsuarioGte],[IDUsuarioPBM])VALUES(3041,3275)


INSERT INTO [usuarios]
([idUsuario],[login],[password],[Nombre],[tipoUsuario],[enti dad],[EMail])VALUES(2931,'victor.lomas','36245','Victor ',5,0,'email@email.em')
INSERT INTO [usuarios]
([idUsuario],[login],[password],[Nombre],[tipoUsuario],[enti dad],[EMail])VALUES(3041,'agustin','58398','Agustin ',10,0,'email@email.em')
INSERT INTO [usuarios]
([idUsuario],[login],[password],[Nombre],[tipoUsuario],[enti dad],[EMail])VALUES(3240,'aaron','70702','Aaron ',2,0,'email@email.em')
INSERT INTO [usuarios]
([idUsuario],[login],[password],[Nombre],[tipoUsuario],[enti dad],[EMail])VALUES(3275,'apolinar','99741','Apolinar',2,0,' email@email.em')
INSERT INTO [usuarios]
([idUsuario],[login],[password],[Nombre],[tipoUsuario],[enti dad],[EMail])VALUES(3371,'dionisio','76402','Dionisio',5,0,' email@email.em')
INSERT INTO [usuarios]
([idUsuario],[login],[password],[Nombre],[tipoUsuario],[enti dad],[EMail])VALUES(3788,'eri','98025','Erika',8,0,'email@em ail.em')
INSERT INTO [usuarios]
([idUsuario],[login],[password],[Nombre],[tipoUsuario],[enti dad],[EMail])VALUES(3790,'jorge','94209','Jorge',8,0,'email@ email.em')
INSERT INTO [usuarios]
([idUsuario],[login],[password],[Nombre],[tipoUsuario],[enti dad],[EMail])VALUES(3911,'iris','29767','Iris Michelle',9,0,'email@email.em')
INSERT INTO [usuarios]
([idUsuario],[login],[password],[Nombre],[tipoUsuario],[enti dad],[EMail])VALUES(3914,'fernando','22491','Fernando ',9,0,'email@email.em')
INSERT INTO [usuarios]
([idUsuario],[login],[password],[Nombre],[tipoUsuario],[enti dad],[EMail])VALUES(3920,'gabriel','65865','Gabriel ',10,0,'email@email.em')

INSERT INTO [DistribuidoresEjecutivosCuentas]
([idDistribuidor],[idUsuario],[idUsuarioShadow])VALUES(75,32 40,NULL)
INSERT INTO [DistribuidoresEjecutivosCuentas]
([idDistribuidor],[idUsuario],[idUsuarioShadow])VALUES(1321, 3275,NULL)

INSERT INTO [DependenciasAdministradoresVenta]
([idDependencia],[idUsuarioTSG],[idUsuarioPSG],[idUsuarioIPG ])VALUES(81,2931,3788,3914)
INSERT INTO [DependenciasAdministradoresVenta]
([idDependencia],[idUsuarioTSG],[idUsuarioPSG],[idUsuarioIPG ])VALUES(3119,3416,NULL,NULL)
INSERT INTO [DependenciasAdministradoresVenta]
([idDependencia],[idUsuarioTSG],[idUsuarioPSG],[idUsuarioIPG ])VALUES(3437,3144,-1,3912)
INSERT INTO [DependenciasAdministradoresVenta]
([idDependencia],[idUsuarioTSG],[idUsuarioPSG],[idUsuarioIPG ])VALUES(3589,3371,3790,3911)


Insert into [DISTRIBUIDORES] ([ID_EMPRESA], [EMPRESA]) values (197,
'DISTRIBUIDORES001, S.A. DE C.V')
Insert into [DISTRIBUIDORES] ([ID_EMPRESA], [EMPRESA]) values
(853,'DISTRIBUIDORES002')
Insert into [DISTRIBUIDORES] ([ID_EMPRESA], [EMPRESA]) values (1326,'
DISTRIBUIDORES003')
Insert into [DISTRIBUIDORES] ([ID_EMPRESA], [EMPRESA]) values (1321,'
DISTRIBUIDORES004')
Insert into [DISTRIBUIDORES] ([ID_EMPRESA], [EMPRESA]) values (75,'
DISTRIBUIDORES005')

DELETE FROM SOLICITUDES
Insert into [SOLICITUDES] ([ID_SOLICITUD], [ID_EMPRESA], [DEPENDENCIA],
[ESTADO], [AREA], [SUBDIRECCION], [MAYORISTA], [NUMERO_LICITACION],
[TIPO_LICITACION], [CLASE_LICITACION], [NOMBRE_PROY], [FEC_PUBLIC],
[FEC_BASES], [FEC_JUNTA_ACL], [FEC_OF_LEGAL], [FEC_FALLO],
[FEC_ENTREGA_BIENES], [DESC_PRESENCIA], [FACTORES_CRITICOS],
[FEC_ULTIMA_ACT], [FEC_SOLICITUD], [STATUS], [CUENTA], [ALPHA_SERVERS],
[ALPHA_WORKSTATION], [NETWORKING], [STORAGE_WORKS], [HIMALAYAS],
[CARTA_LIBRE], [ID_FOLIOTEMP], [RequiereBusinessCase], [ID_Mayorista], [IPG],
[PSG], [HP_Proliant], [HPOX], [HP_Alpha_Server], [HP_Storage_Works], [HP_SW],
[HasSupplies], [comentarios], [bitacora], [factor_decision], [specialprice],
[avalado], [bases], [certificado], [cartasimpresas], [FacturaHP], [razones],
[Proyecto], [DualAgregation], [IdSector])
values(36443, 75, 3119, 3, 'DIRECCION GENERAL DE ADMINISTRACION Y FINANZAS',
'DE RECURSOS MATERIALES', 'DFX DE MEXICO', '06100001', '1', 'E', 'ADQUISICION
DE BIENES',NULL,NULL,NULL,NULL, NULL,NULL,NULL, 'CARTAS DE APOYO', NULL,
NULL, 4, 4, 0, 0, 0, 0, 0, '', 0, 0, NULL, 0, 0, 0, 0, 0, 0, 0, 0, '', 0, '',
NULL, 0, 0, 0, 0, NULL, NULL, 0, NULL, 1)


Insert into [SOLICITUDES] ([ID_SOLICITUD], [ID_EMPRESA], [DEPENDENCIA],
[ESTADO], [AREA], [SUBDIRECCION], [MAYORISTA], [NUMERO_LICITACION],
[TIPO_LICITACION], [CLASE_LICITACION], [NOMBRE_PROY], [FEC_PUBLIC],
[FEC_BASES], [FEC_JUNTA_ACL], [FEC_OF_LEGAL], [FEC_FALLO],
[FEC_ENTREGA_BIENES], [DESC_PRESENCIA], [FACTORES_CRITICOS],
[FEC_ULTIMA_ACT], [FEC_SOLICITUD], [STATUS], [CUENTA], [ALPHA_SERVERS],
[ALPHA_WORKSTATION], [NETWORKING], [STORAGE_WORKS], [HIMALAYAS],
[CARTA_LIBRE], [ID_FOLIOTEMP], [RequiereBusinessCase], [ID_Mayorista], [IPG],
[PSG], [HP_Proliant], [HPOX], [HP_Alpha_Server], [HP_Storage_Works], [HP_SW],
[HasSupplies], [comentarios], [bitacora], [factor_decision], [specialprice],
[avalado], [bases], [certificado], [cartasimpresas], [FacturaHP], [razones],
[Proyecto], [DualAgregation], [IdSector])
values(36444, 1321, 3589, 8, 'DIRECCION GENERAL DE ADMINISTRACION Y
FINANZAS', 'DE RECURSOS MATERIALES', 'DFX DE MEXICO', '06100001', '1', 'E',
'ADQUISICION DE BIENES', '', '', '', '', '', '', ' ', 'CARTAS DE APOYO',NULL,
NULL, 4, 4, 0, 0, 0, 0, 0, '', 0, 0, NULL, 0, 0, 0, 0, 0, 0, 0, 0, '', 0, '',
NULL, 0, 0, 0, 0, NULL, NULL, 0, NULL, 1)


Insert into [SOLICITUDES] ([ID_SOLICITUD], [ID_EMPRESA], [DEPENDENCIA],
[ESTADO], [AREA], [SUBDIRECCION], [MAYORISTA], [NUMERO_LICITACION],
[TIPO_LICITACION], [CLASE_LICITACION], [NOMBRE_PROY], [FEC_PUBLIC],
[FEC_BASES], [FEC_JUNTA_ACL], [FEC_OF_LEGAL], [FEC_FALLO],
[FEC_ENTREGA_BIENES], [DESC_PRESENCIA], [FACTORES_CRITICOS],
[FEC_ULTIMA_ACT], [FEC_SOLICITUD], [STATUS], [CUENTA], [ALPHA_SERVERS],
[ALPHA_WORKSTATION], [NETWORKING], [STORAGE_WORKS], [HIMALAYAS],
[CARTA_LIBRE], [ID_FOLIOTEMP], [RequiereBusinessCase], [ID_Mayorista], [IPG],
[PSG], [HP_Proliant], [HPOX], [HP_Alpha_Server], [HP_Storage_Works], [HP_SW],
[HasSupplies], [comentarios], [bitacora], [factor_decision], [specialprice],
[avalado], [bases], [certificado], [cartasimpresas], [FacturaHP], [razones],
[Proyecto], [DualAgregation], [IdSector])
values(36445, 1321, 81, 6, 'DIRECCION GENERAL DE ADMINISTRACION Y FINANZAS',
'DE RECURSOS MATERIALES', 'DFX DE MEXICO', '06100001', '1', 'E', 'ADQUISICION
DE BIENES',NULL,NULL,NULL,NULL, NULL,NULL,NULL, 'CARTAS DE APOYO',GETDATE(),
GETDATE(), 4, 4, 0, 0, 0, 0, 0, '', 0, 0, NULL, 0, 0, 0, 0, 0, 0, 0, 0, '',
0, '', NULL, 0, 0, 0, 0, NULL, NULL, 0, NULL, 1)

Insert into [SOLICITUDES] ([ID_SOLICITUD], [ID_EMPRESA], [DEPENDENCIA],
[ESTADO], [AREA], [SUBDIRECCION], [MAYORISTA], [NUMERO_LICITACION],
[TIPO_LICITACION], [CLASE_LICITACION], [NOMBRE_PROY], [FEC_PUBLIC],
[FEC_BASES], [FEC_JUNTA_ACL], [FEC_OF_LEGAL], [FEC_FALLO],
[FEC_ENTREGA_BIENES], [DESC_PRESENCIA], [FACTORES_CRITICOS],
[FEC_ULTIMA_ACT], [FEC_SOLICITUD], [STATUS], [CUENTA], [ALPHA_SERVERS],
[ALPHA_WORKSTATION], [NETWORKING], [STORAGE_WORKS], [HIMALAYAS],
[CARTA_LIBRE], [ID_FOLIOTEMP], [RequiereBusinessCase], [ID_Mayorista], [IPG],
[PSG], [HP_Proliant], [HPOX], [HP_Alpha_Server], [HP_Storage_Works], [HP_SW],
[HasSupplies], [comentarios], [bitacora], [factor_decision], [specialprice],
[avalado], [bases], [certificado], [cartasimpresas], [FacturaHP], [razones],
[Proyecto], [DualAgregation], [IdSector])
values(36446, 1321, 3437, 30, 'DIRECCION GENERAL DE ADMINISTRACION Y
FINANZAS', 'DE RECURSOS MATERIALES', 'DFX DE MEXICO', '06100001', '1', 'E',
'ADQUISICION DE BIENES', NULL,NULL,NULL,NULL, NULL,NULL,NULL, 'CARTAS DE
APOYO', NULL,NULL, 4, 4, 0, 0, 0, 0, 0, '', 0, 0, NULL, 0, 0, 0, 0, 0, 0, 0,
0, '', 0, '', NULL, 0, 0, 0, 0, NULL, NULL, 0, NULL, 1)

INSERT INTO [pedidos]
([ID_SOLICITUD],[NO_PARTE],[CANTIDAD],[ID_FOLIOTEMP],[PRECIO ])VALUES(36446,'U4818PE',10,0,1.38)

INSERT INTO [pedidos]
([ID_SOLICITUD],[NO_PARTE],[CANTIDAD],[ID_FOLIOTEMP],[PRECIO ])VALUES(36445,'U5000E',1,0,1.437500000000000e+003)

INSERT INTO [pedidos]
([ID_SOLICITUD],[NO_PARTE],[CANTIDAD],[ID_FOLIOTEMP],[PRECIO ])VALUES(36443,'PA716A',1,0,2.990000000000000e+002)

INSERT INTO [pedidos]
([ID_SOLICITUD],[NO_PARTE],[CANTIDAD],[ID_FOLIOTEMP],[PRECIO ])VALUES(36436,'PA716A',1,0,2.990000000000000e+002)

INSERT INTO [pedidos]
([ID_SOLICITUD],[NO_PARTE],[CANTIDAD],[ID_FOLIOTEMP],[PRECIO ])VALUES(36436,'PA716A',1,0,2.990000000000000e+002)
INSERT INTO [pedidos]
([ID_SOLICITUD],[NO_PARTE],[CANTIDAD],[ID_FOLIOTEMP],[PRECIO ])VALUES(36437,'PA716A',1,0,2.990000000000000e+002)
INSERT INTO [pedidos]
([ID_SOLICITUD],[NO_PARTE],[CANTIDAD],[ID_FOLIOTEMP],[PRECIO ])VALUES(36438,'PA716A',1,0,2.990000000000000e+002)
INSERT INTO [pedidos]
([ID_SOLICITUD],[NO_PARTE],[CANTIDAD],[ID_FOLIOTEMP],[PRECIO ])VALUES(36439,'PA716A',1,0,2.990000000000000e+002)
INSERT INTO [pedidos]
([ID_SOLICITUD],[NO_PARTE],[CANTIDAD],[ID_FOLIOTEMP],[PRECIO ])VALUES(36440,'PA716A',1,0,2.990000000000000e+002)
INSERT INTO [pedidos]
([ID_SOLICITUD],[NO_PARTE],[CANTIDAD],[ID_FOLIOTEMP],[PRECIO ])VALUES(36441,'PA716A',1,0,2.990000000000000e+002)
INSERT INTO [pedidos]
([ID_SOLICITUD],[NO_PARTE],[CANTIDAD],[ID_FOLIOTEMP],[PRECIO ])VALUES(36442,'PA716A',1,0,2.990000000000000e+002)


Insert into [SOLICITUDES] ([ID_SOLICITUD], [ID_EMPRESA], [DEPENDENCIA],
[ESTADO], [AREA], [SUBDIRECCION], [MAYORISTA], [NUMERO_LICITACION],
[TIPO_LICITACION], [CLASE_LICITACION], [NOMBRE_PROY], [FEC_PUBLIC],
[FEC_BASES], [FEC_JUNTA_ACL], [FEC_OF_LEGAL], [FEC_FALLO],
[FEC_ENTREGA_BIENES], [DESC_PRESENCIA], [FACTORES_CRITICOS],
[FEC_ULTIMA_ACT], [FEC_SOLICITUD], [STATUS], [CUENTA], [ALPHA_SERVERS],
[ALPHA_WORKSTATION], [NETWORKING], [STORAGE_WORKS], [HIMALAYAS],
[CARTA_LIBRE], [ID_FOLIOTEMP], [RequiereBusinessCase], [ID_Mayorista], [IPG],
[PSG], [HP_Proliant], [HPOX], [HP_Alpha_Server], [HP_Storage_Works], [HP_SW],
[HasSupplies], [comentarios], [bitacora], [factor_decision], [specialprice],
[avalado], [bases], [certificado], [cartasimpresas], [FacturaHP], [razones],
[Proyecto], [DualAgregation], [IdSector])
values(36436, 1321, 3437, 30, 'DIRECCION GENERAL DE ADMINISTRACION Y
FINANZAS', 'DE RECURSOS MATERIALES', 'DFX DE MEXICO', '06100001', '1', 'E',
'ADQUISICION DE BIENES', NULL,NULL,NULL,NULL, NULL,NULL,NULL, 'CARTAS DE
APOYO', NULL,NULL, 4, 4, 0, 0, 0, 0, 0, '', 0, 0, NULL, 0, 0, 0, 0, 0, 0, 0,
0, '', 0, '', NULL, 0, 0, 0, 0, NULL, NULL, 0, NULL, 1)

Insert into [SOLICITUDES] ([ID_SOLICITUD], [ID_EMPRESA], [DEPENDENCIA],
[ESTADO], [AREA], [SUBDIRECCION], [MAYORISTA], [NUMERO_LICITACION],
[TIPO_LICITACION], [CLASE_LICITACION], [NOMBRE_PROY], [FEC_PUBLIC],
[FEC_BASES], [FEC_JUNTA_ACL], [FEC_OF_LEGAL], [FEC_FALLO],
[FEC_ENTREGA_BIENES], [DESC_PRESENCIA], [FACTORES_CRITICOS],
[FEC_ULTIMA_ACT], [FEC_SOLICITUD], [STATUS], [CUENTA], [ALPHA_SERVERS],
[ALPHA_WORKSTATION], [NETWORKING], [STORAGE_WORKS], [HIMALAYAS],
[CARTA_LIBRE], [ID_FOLIOTEMP], [RequiereBusinessCase], [ID_Mayorista], [IPG],
[PSG], [HP_Proliant], [HPOX], [HP_Alpha_Server], [HP_Storage_Works], [HP_SW],
[HasSupplies], [comentarios], [bitacora], [factor_decision], [specialprice],
[avalado], [bases], [certificado], [cartasimpresas], [FacturaHP], [razones],
[Proyecto], [DualAgregation], [IdSector])
values(36437, 1321, 3437, 30, 'DIRECCION GENERAL DE ADMINISTRACION Y
FINANZAS', 'DE RECURSOS MATERIALES', 'DFX DE MEXICO', '06100001', '1', 'E',
'ADQUISICION DE BIENES', NULL,NULL,NULL,NULL, NULL,NULL,NULL, 'CARTAS DE
APOYO', NULL,NULL, 4, 4, 0, 0, 0, 0, 0, '', 0, 0, NULL, 0, 0, 0, 0, 0, 0, 0,
0, '', 0, '', NULL, 0, 0, 0, 0, NULL, NULL, 0, NULL, 1)

Insert into [SOLICITUDES] ([ID_SOLICITUD], [ID_EMPRESA], [DEPENDENCIA],
[ESTADO], [AREA], [SUBDIRECCION], [MAYORISTA], [NUMERO_LICITACION],
[TIPO_LICITACION], [CLASE_LICITACION], [NOMBRE_PROY], [FEC_PUBLIC],
[FEC_BASES], [FEC_JUNTA_ACL], [FEC_OF_LEGAL], [FEC_FALLO],
[FEC_ENTREGA_BIENES], [DESC_PRESENCIA], [FACTORES_CRITICOS],
[FEC_ULTIMA_ACT], [FEC_SOLICITUD], [STATUS], [CUENTA], [ALPHA_SERVERS],
[ALPHA_WORKSTATION], [NETWORKING], [STORAGE_WORKS], [HIMALAYAS],
[CARTA_LIBRE], [ID_FOLIOTEMP], [RequiereBusinessCase], [ID_Mayorista], [IPG],
[PSG], [HP_Proliant], [HPOX], [HP_Alpha_Server], [HP_Storage_Works], [HP_SW],
[HasSupplies], [comentarios], [bitacora], [factor_decision], [specialprice],
[avalado], [bases], [certificado], [cartasimpresas], [FacturaHP], [razones],
[Proyecto], [DualAgregation], [IdSector])
values(36438, 1321, 3437, 30, 'DIRECCION GENERAL DE ADMINISTRACION Y
FINANZAS', 'DE RECURSOS MATERIALES', 'DFX DE MEXICO', '06100001', '1', 'E',
'ADQUISICION DE BIENES', NULL,NULL,NULL,NULL, NULL,NULL,NULL, 'CARTAS DE
APOYO', NULL,NULL, 4, 4, 0, 0, 0, 0, 0, '', 0, 0, NULL, 0, 0, 0, 0, 0, 0, 0,
0, '', 0, '', NULL, 0, 0, 0, 0, NULL, NULL, 0, NULL, 1)

Insert into [SOLICITUDES] ([ID_SOLICITUD], [ID_EMPRESA], [DEPENDENCIA],
[ESTADO], [AREA], [SUBDIRECCION], [MAYORISTA], [NUMERO_LICITACION],
[TIPO_LICITACION], [CLASE_LICITACION], [NOMBRE_PROY], [FEC_PUBLIC],
[FEC_BASES], [FEC_JUNTA_ACL], [FEC_OF_LEGAL], [FEC_FALLO],
[FEC_ENTREGA_BIENES], [DESC_PRESENCIA], [FACTORES_CRITICOS],
[FEC_ULTIMA_ACT], [FEC_SOLICITUD], [STATUS], [CUENTA], [ALPHA_SERVERS],
[ALPHA_WORKSTATION], [NETWORKING], [STORAGE_WORKS], [HIMALAYAS],
[CARTA_LIBRE], [ID_FOLIOTEMP], [RequiereBusinessCase], [ID_Mayorista], [IPG],
[PSG], [HP_Proliant], [HPOX], [HP_Alpha_Server], [HP_Storage_Works], [HP_SW],
[HasSupplies], [comentarios], [bitacora], [factor_decision], [specialprice],
[avalado], [bases], [certificado], [cartasimpresas], [FacturaHP], [razones],
[Proyecto], [DualAgregation], [IdSector])
values(36439, 1321, 3437, 30, 'DIRECCION GENERAL DE ADMINISTRACION Y
FINANZAS', 'DE RECURSOS MATERIALES', 'DFX DE MEXICO', '06100001', '1', 'E',
'ADQUISICION DE BIENES', NULL,NULL,NULL,NULL, NULL,NULL,NULL, 'CARTAS DE
APOYO', NULL,NULL, 4, 4, 0, 0, 0, 0, 0, '', 0, 0, NULL, 0, 0, 0, 0, 0, 0, 0,
0, '', 0, '', NULL, 0, 0, 0, 0, NULL, NULL, 0, NULL, 1)

Insert into [SOLICITUDES] ([ID_SOLICITUD], [ID_EMPRESA], [DEPENDENCIA],
[ESTADO], [AREA], [SUBDIRECCION], [MAYORISTA], [NUMERO_LICITACION],
[TIPO_LICITACION], [CLASE_LICITACION], [NOMBRE_PROY], [FEC_PUBLIC],
[FEC_BASES], [FEC_JUNTA_ACL], [FEC_OF_LEGAL], [FEC_FALLO],
[FEC_ENTREGA_BIENES], [DESC_PRESENCIA], [FACTORES_CRITICOS],
[FEC_ULTIMA_ACT], [FEC_SOLICITUD], [STATUS], [CUENTA], [ALPHA_SERVERS],
[ALPHA_WORKSTATION], [NETWORKING], [STORAGE_WORKS], [HIMALAYAS],
[CARTA_LIBRE], [ID_FOLIOTEMP], [RequiereBusinessCase], [ID_Mayorista], [IPG],
[PSG], [HP_Proliant], [HPOX], [HP_Alpha_Server], [HP_Storage_Works], [HP_SW],
[HasSupplies], [comentarios], [bitacora], [factor_decision], [specialprice],
[avalado], [bases], [certificado], [cartasimpresas], [FacturaHP], [razones],
[Proyecto], [DualAgregation], [IdSector])
values(36440, 1321, 3437, 30, 'DIRECCION GENERAL DE ADMINISTRACION Y
FINANZAS', 'DE RECURSOS MATERIALES', 'DFX DE MEXICO', '06100001', '1', 'E',
'ADQUISICION DE BIENES', NULL,NULL,NULL,NULL, NULL,NULL,NULL, 'CARTAS DE
APOYO', NULL,NULL, 4, 4, 0, 0, 0, 0, 0, '', 0, 0, NULL, 0, 0, 0, 0, 0, 0, 0,
0, '', 0, '', NULL, 0, 0, 0, 0, NULL, NULL, 0, NULL, 1)

Insert into [SOLICITUDES] ([ID_SOLICITUD], [ID_EMPRESA], [DEPENDENCIA],
[ESTADO], [AREA], [SUBDIRECCION], [MAYORISTA], [NUMERO_LICITACION],
[TIPO_LICITACION], [CLASE_LICITACION], [NOMBRE_PROY], [FEC_PUBLIC],
[FEC_BASES], [FEC_JUNTA_ACL], [FEC_OF_LEGAL], [FEC_FALLO],
[FEC_ENTREGA_BIENES], [DESC_PRESENCIA], [FACTORES_CRITICOS],
[FEC_ULTIMA_ACT], [FEC_SOLICITUD], [STATUS], [CUENTA], [ALPHA_SERVERS],
[ALPHA_WORKSTATION], [NETWORKING], [STORAGE_WORKS], [HIMALAYAS],
[CARTA_LIBRE], [ID_FOLIOTEMP], [RequiereBusinessCase], [ID_Mayorista], [IPG],
[PSG], [HP_Proliant], [HPOX], [HP_Alpha_Server], [HP_Storage_Works], [HP_SW],
[HasSupplies], [comentarios], [bitacora], [factor_decision], [specialprice],
[avalado], [bases], [certificado], [cartasimpresas], [FacturaHP], [razones],
[Proyecto], [DualAgregation], [IdSector])
values(36441, 1321, 3437, 30, 'DIRECCION GENERAL DE ADMINISTRACION Y
FINANZAS', 'DE RECURSOS MATERIALES', 'DFX DE MEXICO', '06100001', '1', 'E',
'ADQUISICION DE BIENES', NULL,NULL,NULL,NULL, NULL,NULL,NULL, 'CARTAS DE
APOYO', NULL,NULL, 4, 4, 0, 0, 0, 0, 0, '', 0, 0, NULL, 0, 0, 0, 0, 0, 0, 0,
0, '', 0, '', NULL, 0, 0, 0, 0, NULL, NULL, 0, NULL, 1)

Insert into [SOLICITUDES] ([ID_SOLICITUD], [ID_EMPRESA], [DEPENDENCIA],
[ESTADO], [AREA], [SUBDIRECCION], [MAYORISTA], [NUMERO_LICITACION],
[TIPO_LICITACION], [CLASE_LICITACION], [NOMBRE_PROY], [FEC_PUBLIC],
[FEC_BASES], [FEC_JUNTA_ACL], [FEC_OF_LEGAL], [FEC_FALLO],
[FEC_ENTREGA_BIENES], [DESC_PRESENCIA], [FACTORES_CRITICOS],
[FEC_ULTIMA_ACT], [FEC_SOLICITUD], [STATUS], [CUENTA], [ALPHA_SERVERS],
[ALPHA_WORKSTATION], [NETWORKING], [STORAGE_WORKS], [HIMALAYAS],
[CARTA_LIBRE], [ID_FOLIOTEMP], [RequiereBusinessCase], [ID_Mayorista], [IPG],
[PSG], [HP_Proliant], [HPOX], [HP_Alpha_Server], [HP_Storage_Works], [HP_SW],
[HasSupplies], [comentarios], [bitacora], [factor_decision], [specialprice],
[avalado], [bases], [certificado], [cartasimpresas], [FacturaHP], [razones],
[Proyecto], [DualAgregation], [IdSector])
values(36442, 1321, 3437, 30, 'DIRECCION GENERAL DE ADMINISTRACION Y
FINANZAS', 'DE RECURSOS MATERIALES', 'DFX DE MEXICO', '06100001', '1', 'E',
'ADQUISICION DE BIENES', NULL,NULL,NULL,NULL, NULL,NULL,NULL, 'CARTAS DE
APOYO', NULL,NULL, 4, 4, 0, 0, 0, 0, 0, '', 0, 0, NULL, 0, 0, 0, 0, 0, 0, 0,
0, '', 0, '', NULL, 0, 0, 0, 0, NULL, NULL, 0, NULL, 1)

update SOLICITUDES set fec_solicitud = '2006-07-15'
UPDATE SOLICITUDES set STATUS = 1 WHERE ID_SOLICITUD IN (36446,
36444,36445,36443,36436,36436,36437)

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[spS_PrepareFunnelForecastReport]') and OBJECTPROPERTY(id,
N'IsProcedure') = 1)
drop procedure [dbo].[spS_PrepareFunnelForecastReport]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO



CREATE PROCEDURE spS_PrepareFunnelForecastReport
(
@dtInicioFechaSolicitud datetime = getdate
)
AS


/* REPORTE DE FUNNEL Y FORECAST GENERAL */
/* MODIFICADO EL 2006-07-19 POR MIRANRON */

/*---- QUITAR TABLA TEMP ----*/
if exists (select * from dbo.sysobjects where id =
object_id(N'temptbFunnelForecast') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table temptbFunnelForecast


SELECT tbStates1.zona_geografica, Usuarios1.Nombre AS Gerente,
Usuarios_1.Nombre AS PBM, Usuarios2.Nombre AS TSG, Usuarios3.Nombre AS PSG,
Usuarios4.Nombre AS IPG,
dbo.dependencias_cat.DESCRIPCION AS Cliente, dbo.dependencias_cat.ESTRATEGICA
AS Listada,
dbo.dependencias_cat.GEM AS SP,
dbo.SOLICITUDES.NOMBRE_PROY, DISTRIBUIDORES1.EMPRESA AS Mayorista,
DISTRIBUIDORES_1.EMPRESA AS Distribuidor,
dbo.PEDIDOS.NO_PARTE, dbo.CPQGOB_EQUIPOS.Activo, dbo.CPQGOB_EQUIPOS.BU,
dbo.CPQGOB_EQUIPOS.DESCRIPCION, dbo.CPQGOB_EQUIPOS.PL,
dbo.CAT_STATUS_SOLICITUD.CICLOVENTA, dbo.CPQGOB_EQUIPOS.PRECIOUSD,
dbo.PEDIDOS.CANTIDAD, dbo.CPQGOB_EQUIPOS.PRECIOUSD *
dbo.PEDIDOS.CANTIDAD AS Total, dbo.SOLICITUDES.FEC_OF_LEGAL,
dbo.SOLICITUDES.FEC_FALLO,
dbo.SOLICITUDES.FEC_ENTREGA_BIENES, dbo.SOLICITUDES.ID_SOLICITUD,
dbo.SOLICITUDES.FEC_ULTIMA_ACT,
dbo.SOLICITUDES.numero_licitacion,
dbo.CAT_STATUS_SOLICITUD.DESCRIPCION AS Status, dbo.CAT_FALLO.DESCRIPCION AS
Fallo, dbo.SOLICITUDES.DualAgregation,
dbo.SOLICITUDES.FEC_SOLICITUD, tbStates1.StateName AS
[Localidad distribuidor], tbStates_1.StateName AS [Localidad cliente]
INTO temptbFunnelForecast
FROM dbo.DISTRIBUIDORES DISTRIBUIDORES_1 RIGHT OUTER JOIN
dbo.DISTRIBUIDORES DISTRIBUIDORES1 RIGHT OUTER JOIN
dbo.CAT_STATUS_SOLICITUD INNER JOIN
dbo.SOLICITUDES INNER JOIN
dbo.dependencias_cat ON dbo.SOLICITUDES.DEPENDENCIA =
dbo.dependencias_cat.ID ON
dbo.CAT_STATUS_SOLICITUD.ID_STATUS =
dbo.SOLICITUDES.STATUS LEFT OUTER JOIN
dbo.tbStates tbStates_1 ON dbo.SOLICITUDES.ESTADO =
tbStates_1.StateId ON DISTRIBUIDORES1.ID_EMPRESA =
dbo.SOLICITUDES.ID_Mayorista ON
DISTRIBUIDORES_1.ID_EMPRESA =
dbo.SOLICITUDES.ID_EMPRESA LEFT OUTER JOIN
dbo.CAT_FALLO INNER JOIN
dbo.RESULTADOS ON dbo.CAT_FALLO.ID_FALLO =
dbo.RESULTADOS.FALLO ON
dbo.SOLICITUDES.ID_SOLICITUD =
dbo.RESULTADOS.ID_SOLICITUD LEFT OUTER JOIN
dbo.CPQGOB_EQUIPOS INNER JOIN
dbo.PEDIDOS ON dbo.CPQGOB_EQUIPOS.NO_PARTE =
dbo.PEDIDOS.NO_PARTE ON
dbo.SOLICITUDES.ID_SOLICITUD =
dbo.PEDIDOS.ID_SOLICITUD LEFT OUTER JOIN
dbo.DependenciasAdministradoresVenta INNER JOIN
dbo.Usuarios Usuarios2 ON
dbo.DependenciasAdministradoresVenta.idUsuarioTSG = Usuarios2.idUsuario INNER
JOIN
dbo.Usuarios Usuarios3 ON
dbo.DependenciasAdministradoresVenta.idUsuarioPSG = Usuarios3.idUsuario INNER
JOIN
dbo.Usuarios Usuarios4 ON
dbo.DependenciasAdministradoresVenta.idUsuarioIPG = Usuarios4.idUsuario ON
dbo.SOLICITUDES.DEPENDENCIA =
dbo.DependenciasAdministradoresVenta.idDependencia LEFT OUTER JOIN
dbo.Usuarios Usuarios1 INNER JOIN
dbo.GerenteDistritoEjecutivosCuentas ON
Usuarios1.idUsuario = dbo.GerenteDistritoEjecutivosCuentas.IdUsuarioGte RIGHT
OUTER JOIN
dbo.Usuarios Usuarios_1 INNER JOIN
dbo.DistribuidoresEjecutivosCuentas ON
Usuarios_1.idUsuario = dbo.DistribuidoresEjecutivosCuentas.idUsuario ON
dbo.GerenteDistritoEjecutivosCuentas.IdUsuarioPBM =
dbo.DistribuidoresEjecutivosCuentas.idUsuario ON
dbo.SOLICITUDES.ID_EMPRESA =
dbo.DistribuidoresEjecutivosCuentas.idDistribuidor LEFT OUTER JOIN
dbo.tbStates tbStates1 ON DISTRIBUIDORES_1.ESTADO =
tbStates1.StateId
where solicitudes.fec_solicitud >=@dtInicioFechaSolicitud
order by solicitudes.id_solicitud

/*--- ACTUALIZAR CICLO VENTA A 0 PARA STATUS perdida ---*/
UPDATE temptbFunnelForecast
SET cicloventa = 0
WHERE id_solicitud in
(
SELECT distinct id_solicitud
FROM temptbFunnelForecast
WHERE status = 'completa' and fallo = 'perdida' and cicloventa is null
)


/*--- ACTUALIZAR CICLO VENTA A 0 PARA STATUS desierta ---*/
UPDATE temptbFunnelForecast
SET cicloventa = 0
WHERE id_solicitud in
(
SELECT distinct id_solicitud
FROM temptbFunnelForecast
WHERE status = 'completa' and fallo = 'desierta' and cicloventa is null
)


/*LAS OPORTUNIDADES QUE VAN AL 20% - Folios en revisión sin fechas y sin
producto */

UPDATE temptbFunnelForecast SET CicloVenta = 20 WHERE id_solicitud IN
(
SELECT id_solicitud FROM temptbFunnelForecast
WHERE PL IS NULL
AND Fec_Of_Legal IS NULL
AND Fec_Fallo IS NULL
AND Fec_Entrega_bienes IS NULL
AND Status like 'revisi%'
)


/*LAS OPORTUNIDADES QUE VAN AL 40% - Folios en revisión sin fechas y con
producto */

UPDATE temptbFunnelForecast SET CicloVenta = 40 WHERE id_solicitud IN
(
SELECT id_solicitud FROM temptbFunnelForecast
WHERE PL IS NOT NULL
AND Fec_Of_Legal IS NULL
AND Fec_Fallo IS NULL
AND Fec_Entrega_bienes IS NULL
AND Status like 'revisi%'
)

/*LAS OPORTUNIDADES QUE VAN AL 50% - Folios en revisión con fechas y con
producto. */

UPDATE temptbFunnelForecast SET CicloVenta = 50 WHERE id_solicitud IN
(
SELECT id_solicitud FROM temptbFunnelForecast
WHERE PL IS NOT NULL
AND Fec_Of_Legal IS NOT NULL
AND Fec_Fallo IS NOT NULL
AND Fec_Entrega_bienes IS NOT NULL
AND Status like 'revisi%'
)


=====================================


(CONTINUED IN NEXT POST)

Re: asp not retrieving correct information from a query

Hello,

Thank you for the code. I run the script on my server to build the tables
and store procedure, and then build an ASP page to reproduce the problem.
In the ASP page, I get 12 records. In the query analyzer window, I execute
"select * from temptbFunnelForecast", I also got 12 records. In the table
from "temptbFunnelForecast", it is actually 12 records there. I notice the
data was added by the store procedure "spS_PrepareFunnelForecastReport", is
the "spS_PrepareFunnelForecastReport" suppose to add 12 records from your
logic and data?

Sincerely,

Luke Zhang

Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/de fault.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx .
==================================================

This posting is provided "AS IS" with no warranties, and confers no rights.

Re: asp not retrieving correct information from a query

Yes, that's correct.



"Luke Zhang [MSFT]" wrote:

> Hello,
>
> Thank you for the code. I run the script on my server to build the tables
> and store procedure, and then build an ASP page to reproduce the problem.
> In the ASP page, I get 12 records. In the query analyzer window, I execute
> "select * from temptbFunnelForecast", I also got 12 records. In the table
> from "temptbFunnelForecast", it is actually 12 records there. I notice the
> data was added by the store procedure "spS_PrepareFunnelForecastReport", is
> the "spS_PrepareFunnelForecastReport" suppose to add 12 records from your
> logic and data?
>
> Sincerely,
>
> Luke Zhang
>
> Microsoft Online Community Support
> ==================================================
> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/de fault.aspx#notif
> ications.
>
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx .
> ==================================================
>
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>
>
>

Re: asp not retrieving correct information from a query

Hello,

How is it going now? Can you get same result as mine with the sample, you
provided?

Sincerely,

Luke Zhang

Microsoft Online Community Support
This posting is provided "AS IS" with no warranties, and confers no rights.

Re: asp not retrieving correct information from a query

Hello Luke, thank you for effort but I wasn't able to make it "work" with so
little records so I guess that problem is somewhere else. Thanks again.



"Luke Zhang [MSFT]" wrote:

> Hello,
>
> How is it going now? Can you get same result as mine with the sample, you
> provided?
>
> Sincerely,
>
> Luke Zhang
>
> Microsoft Online Community Support
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>
>
>

Re: asp not retrieving correct information from a query

Hello,

In my test, I first run the ASP page to get a result, and then run the
query in Query Analyzer immediately to get another result. If you follow
this oder with your actual data, can you find the same problem.

BTW, please make sure there is no another application or code access the
database at same time, and only one client (your account) is test the
application these also may help us narrow down the issue.

Sincerely,

Luke Zhang

Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/de fault.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx .
==================================================

This posting is provided "AS IS" with no warranties, and confers no rights.