I know this is probably a duplicate of another question but i've searched all over and tried everything but it still seems to be giving me the same error of.We recently migrated from SQL SERVER 2005 to 2008 .After migration We are facing this Problem.I am using classic ASP,SQL and HTML.
Below is My SQL SERVER 2008 PROCEDURE. I noticed one strange thing after migration that in my procedure there are red under lines for temproary table and warning "invalid object " .But when I ran this procedure Manually it return me 6 records
USE [Fastrack] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO
ALTER PROCEDURE [dbo].[SP_ASH_WEB_S] @GMID VARCHAR(10), @DIR_YR CHAR(4)
AS
SET NOCOUNT ON
SELECT DIV_MGR_ID,
'' DMGR_LST_NM,
MGR_ID,
' ' MGR_LST_NM,
SUM(REV_ASG) TOT_ASG,
SUM(REV_HDL) TOT_SVC,
SUM(REV_SLD) CORE_TOT_SLD
INTO #S_GM_AUDIT
FROM
RPT_ASH
WHERE
DIV_MGR_ID = @GMID
GROUP BY
DIV_MGR_ID,
MGR_ID
UPDATE #S_GM_AUDIT
SET DMGR_LST_NM = D.LST_NM,
MGR_LST_NM = M.LST_NM
FROM #S_GM_AUDIT A, ASM M, DSM D
WHERE A.MGR_ID = M.MGR_ID AND
A.DIV_MGR_ID = D.DIV_MGR_ID AND
M.AFF_CD = 'S' AND D.AFF_CD = 'S'
BACKUP LOG FASTRACK TO DISK='NUL:'
SELECT * FROM #S_GM_AUDIT
DROP TABLE #S_GM_AUDIT
Below is the ASP code which is calling this procedure and giving error on line While NOT ors.EOF
<% Option Explicit
Dim DbObj, SQL, oRs, intRowColor, FNM, GMID, DIR_YR, AFF_CD, YEAR_IND
FNM = Request.QueryString("FNM")
GMID = Request.QueryString("GMID")
DIR_YR = Request.QueryString("DIR_YR")
AFF_CD = Request.QueryString("AFF")
YEAR_IND = Request.QueryString("YEAR_IND")
%>
<% Server.ScriptTimeout = 300 %>
<head>
<meta NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
</head>
<%
IF AFF_CD = "P" THEN
IF YEAR_IND = "N" THEN
SQL = "SP_CEO_N_GMGR_CUS_AUDIT_WEB_P '" & GMID & "', '" & DIR_YR & "
ELSE
SQL = "SP_GMGR_CUS_AUDIT_WEB_P '" & GMID & "', '" & DIR_YR & "'"
END IF
END IF
OpenConnection()
Set oRs = DbObj.Execute(SQL)
%>
<form method="get">
<table BORDER="0" WIDTH="100%" cellpadding="0" cellspacing="0">
<%
dim totasg,totsvc,coretotsld,grandtotasg,grandtotsvc,grandcoretotsld
WHILE NOT oRs.EOF
totasg = oRs.Fields("tot_asg")
totsvc = oRs.Fields("tot_svc")
coretotsld = oRs.Fields("core_tot_sld")
grandtotasg = grandtotasg + totasg
grandtotsvc = grandtotsvc + totsvc
grandcoretotsld = grandcoretotsld + coretotsld
if intRowColor = "0" then
intRowColor = "1"
Aucun commentaire:
Enregistrer un commentaire