| Groups | Blog | Home |
inetserver asp db : asp not retrieving correct information from a query
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
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/default.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.
[quoted text, click to view]
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"
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 ----------------------------------------------------------------- [quoted text, click to view] "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" > >
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 ---------------------------------------------------------- [quoted text, click to view] "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/default.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. > > >
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 [quoted text, click to view] 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. >>
Thank you Bob, I'll post the scripts on monday, I'm working on it as of now.
----------------------------------------- [quoted text, click to view] "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
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/default.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.
I have the script files ready, but where can I upload them for you to check?
------------------------------------------------- [quoted text, click to view] "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/default.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. > > >
=====================================
~~~~~ 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 ,
(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(3119,'DEPEND01',0,0,NULL) INSERT INTO [dependencias_cat] ([ID],[DESCRIPCION],[ESTRATEGICA],[GEM],[Industria])VALUES(3589,'DEPEND02',0,0,NULL) INSERT INTO [dependencias_cat] ([ID],[DESCRIPCION],[ESTRATEGICA],[GEM],[Industria])VALUES(81,'DEPEND03, FUERZA AEREA Y ARMADA, S.N.C.',0,1,NULL) INSERT INTO [dependencias_cat] ([ID],[DESCRIPCION],[ESTRATEGICA],[GEM],[Industria])VALUES(3437,'DEPEND04 DE C.V.',0,0,NULL) INSERT INTO [tbStates] ([StateId],[StateName],[Abbreviation],[CountryId],[zona_geografica],[EjecutivoCuenta])VALUES(9.000000000000000e+000,'Distrito Federal','DF',1.000000000000000e+000,'Centro',NULL) INSERT INTO [tbStates] ([StateId],[StateName],[Abbreviation],[CountryId],[zona_geografica],[EjecutivoCuenta])VALUES(2.400000000000000e+001,'Sonora','SON',1.000000000000000e+000,'Norte',3279) INSERT INTO [tbStates] ([StateId],[StateName],[Abbreviation],[CountryId],[zona_geografica],[EjecutivoCuenta])VALUES(8.000000000000000e+000,'Chihuahua','CHI',1.000000000000000e+000,'Norte',2506) INSERT INTO [tbStates] ([StateId],[StateName],[Abbreviation],[CountryId],[zona_geografica],[EjecutivoCuenta])VALUES(6.000000000000000e+000,'Colima','COL',1.000000000000000e+000,'Occidente',2611) INSERT INTO [tbStates] ([StateId],[StateName],[Abbreviation],[CountryId],[zona_geografica],[EjecutivoCuenta])VALUES(3.000000000000000e+000,'Baja California Sur','BCS',1.000000000000000e+000,'Occidente',2506) INSERT INTO [tbStates] ([StateId],[StateName],[Abbreviation],[CountryId],[zona_geografica],[EjecutivoCuenta])VALUES(3.000000000000000e+001,'Veracruz','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],[Supplies],[PL],[BU],[Familia])VALUES('PA716A','Puerto X',2.990000000000000e+002,2.600000000000000e+001,1,0,'9F','PSG','9F') INSERT INTO [CPQGOB_EQUIPOS] ([NO_PARTE],[DESCRIPCION],[PRECIO],[PRECIOUSD],[Activo],[Supplies],[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],[Supplies],[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],[Supplies],[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],[entidad],[EMail])VALUES(2931,'victor.lomas','36245','Victor ',5,0,'email@email.em') INSERT INTO [usuarios] ([idUsuario],[login],[password],[Nombre],[tipoUsuario],[entidad],[EMail])VALUES(3041,'agustin','58398','Agustin ',10,0,'email@email.em') INSERT INTO [usuarios] ([idUsuario],[login],[password],[Nombre],[tipoUsuario],[entidad],[EMail])VALUES(3240,'aaron','70702','Aaron ',2,0,'email@email.em') INSERT INTO [usuarios] ([idUsuario],[login],[password],[Nombre],[tipoUsuario],[entidad],[EMail])VALUES(3275,'apolinar','99741','Apolinar',2,0,'email@email.em') INSERT INTO [usuarios] ([idUsuario],[login],[password],[Nombre],[tipoUsuario],[entidad],[EMail])VALUES(3371,'dionisio','76402','Dionisio',5,0,'email@email.em') INSERT INTO [usuarios] ([idUsuario],[login],[password],[Nombre],[tipoUsuario],[entidad],[EMail])VALUES(3788,'eri','98025','Erika',8,0,'email@email.em') INSERT INTO [usuarios] ([idUsuario],[login],[password],[Nombre],[tipoUsuario],[entidad],[EMail])VALUES(3790,'jorge','94209','Jorge',8,0,'email@email.em') INSERT INTO [usuarios] ([idUsuario],[login],[password],[Nombre],[tipoUsuario],[entidad],[EMail])VALUES(3911,'iris','29767','Iris Michelle',9,0,'email@email.em') INSERT INTO [usuarios] ([idUsuario],[login],[password],[Nombre],[tipoUsuario],[entidad],[EMail])VALUES(3914,'fernando','22491','Fernando ',9,0,'email@email.em') INSERT INTO [usuarios] ([idUsuario],[login],[password],[Nombre],[tipoUsuario],[entidad],[EMail])VALUES(3920,'gabriel','65865','Gabriel ',10,0,'email@email.em') INSERT INTO [DistribuidoresEjecutivosCuentas] ([idDistribuidor],[idUsuario],[idUsuarioShadow])VALUES(75,3240,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)
(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" %> <TABLE> <TR> <td style="border:solid 1px LightGrey;background:LightGrey "> zona_geografica </TD> <td style="border:solid 1px LightGrey;background:LightGrey "> Gerente </TD> <td style="border:solid 1px LightGrey;background:LightGrey "> PBM </TD> <td style="border:solid 1px LightGrey;background:LightGrey "> TSG </TD> <td style="border:solid 1px LightGrey;background:LightGrey "> PSG </TD> <td style="border:solid 1px LightGrey;background:LightGrey "> IPG </TD> <td style="border:solid 1px LightGrey;background:LightGrey "> Cliente </TD> <td style="border:solid 1px LightGrey;background:LightGrey "> Listada </TD> <td style="border:solid 1px LightGrey;background:LightGrey "> SP </TD> <td style="border:solid 1px LightGrey;background:LightGrey "> NOMBRE_PROY </TD> <td style="border:solid 1px LightGrey;background:LightGrey "> Mayorista </TD> <td style="border:solid 1px LightGrey;background:LightGrey "> Distribuidor </TD> <td style="border:solid 1px LightGrey;background:LightGrey "> NO_PARTE </TD> <td style="border:solid 1px LightGrey;background:LightGrey "> Activo </TD> <td style="border:solid 1px LightGrey;background:LightGrey "> BU </TD> <td style="border:solid 1px LightGrey;background:LightGrey "> DESCRIPCION </TD> <td style="border:solid 1px LightGrey;background:LightGrey "> PL </TD> <td style="border:solid 1px LightGrey;background:LightGrey "> CICLOVENTA </TD> <td style="border:solid 1px LightGrey;background:LightGrey "> PRECIOUSD </TD> <td style="border:solid 1px LightGrey;background:LightGrey "> CANTIDAD </TD> <td style="border:solid 1px LightGrey;background:LightGrey "> Total </TD> <td style="border:solid 1px LightGrey;background:LightGrey "> FEC_OF_LEGAL </TD> <td style="border:solid 1px LightGrey;background:LightGrey "> FEC_FALLO </TD> <td style="border:solid 1px LightGrey;background:LightGrey "> FEC_ENTREGA_BIENES </TD> <td style="border:solid 1px LightGrey;background:LightGrey "> ID_SOLICITUD </TD> <td style="border:solid 1px LightGrey;background:LightGrey "> FEC_ULTIMA_ACT </TD> <td style="border:solid 1px LightGrey;background:LightGrey "> numero_licitacion </TD> <td style="border:solid 1px LightGrey;background:LightGrey "> Status </TD> <td style="border:solid 1px LightGrey;background:LightGrey "> Fallo </TD> <td style="border:solid 1px LightGrey;background:LightGrey "> DualAgregation </TD> <td style="border:solid 1px LightGrey;background:LightGrey "> FEC_SOLICITUD </TD> <td style="border:solid 1px LightGrey;background:LightGrey "> Localidad distribuidor </TD> <td style="border:solid 1px LightGrey;background:LightGrey "> Localidad cliente </TD> </TR> <% ' ESCRIBIR LOS CONTENIDOS DE LAS CELDAS do while not rs.EOF strFallo = rs("Fallo") 'intCicloVenta = rs("CICLOVENTA") 'if (trim(strFallo)="perdida") then intCicloVenta=0 end if %> <TR> <td style="border:solid 1px LightGrey;"><%=rs("zona_geografica") %></td> <td style="border:solid 1px LightGrey;"><%=rs("Gerente") %></td> <td style="border:solid 1px LightGrey;"><%=rs("PBM") %></td> <td style="border:solid 1px LightGrey;"><%=rs("TSG") %></td> <td style="border:solid 1px LightGrey;"><%=rs("PSG") %></td> <td style="border:solid 1px LightGrey;"><%=rs("IPG") %></td> <td style="border:solid 1px LightGrey;"><%=rs("Cliente") %></td> <td style="border:solid 1px LightGrey;"><%=rs("Listada") %></td> <td style="border:solid 1px LightGrey;"><%=rs("SP") %></td> <td style="border:solid 1px LightGrey;"><%=rs("NOMBRE_PROY") %></td> <td style="border:solid 1px LightGrey;"><%=rs("Mayorista") %></td> <td style="border:solid 1px LightGrey;"><%=rs("Distribuidor") %></td> <td style="border:solid 1px LightGrey;"><%=rs("NO_PARTE") %></td> <td style="border:solid 1px LightGrey;"><%=rs("Activo") %></td> <td style="border:solid 1px LightGrey;"><%=rs("BU") %></td> <td style="border:solid 1px LightGrey;"><%=rs("DESCRIPCION") %></td> <td style="border:solid 1px LightGrey;"><%=rs("PL") %></td> <td style="border:solid 1px LightGrey;"><%=rs("CICLOVENTA") %></td> <td style="border:solid 1px LightGrey;"><%=rs("PRECIOUSD") %></td> <td style="border:solid 1px LightGrey;"><%=rs("CANTIDAD") %></td> <td style="border:solid 1px LightGrey;"><%=rs("Total") %></td> <td style="border:solid 1px LightGrey;"><%=rs("FEC_OF_LEGAL") %></td> <td style="border:solid 1px LightGrey;"><%=rs("FEC_FALLO") %></td> <td style="border:solid 1px LightGrey;"><%=rs("FEC_ENTREGA_BIENES") %></td> <td style="border:solid 1px LightGrey;"><%=rs("ID_SOLICITUD") %></td> <td style="border:solid 1px LightGrey;"><%=rs("FEC_ULTIMA_ACT") %></td> <td style="border:solid 1px
[quoted text, click to view]
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"
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/default.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.
Yes, that's correct.
[quoted text, click to view] "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/default.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. > > >
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.
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. [quoted text, click to view] "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. > > >
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/default.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.
Don't see what you're looking for? Try a search.
|
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |