Francisco Tapia
my.lists at verizon.net
Tue Jul 29 02:36:10 CDT 2003
I'm running it as windows authentication at the moment for both QA and the ADP, doh I did not think to use the profiler, I will check that next... -Francisco http://rcm.netfirms.com/ On Monday, July 28, 2003 6:09 PM [GMT -8], Billy Pang <tuxedo_man at hotmail.com> wrote: : Are you tried running both sprocs in QA using the security : authentication as ADP (eg. same login)? : also, does SQL Profiler reveal anything? : : : : : :: From: "Francisco H Tapia" <my.lists at verizon.net> :: Reply-To: dba-sqlserver at databaseadvisors.com :: To: <dba-SQLServer at databaseadvisors.com> :: Subject: [dba-SQLServer]Sproc runs and then it doesn't :: Date: Mon, 28 Jul 2003 16:01:33 -0700 :: :: I have a stored procedure in my database that runs fine in QA. In :: fact I created 2 versions of this sproc and both of them run fine in :: QA. In an ADP :: that I have one sproc runs and the other does not. What would be :: different? :: :: In the where clause I am checking for >= or <. :: :: The code is "I D E N T I C A L" in both sprocs lets call them :: stp_New and stp_Old the new checks for any new entries since today :: 12:00am, and the Old :: checks for any records older than today at 12:00am. :: :: If I run the sproc in Access (ADP) it does not give me any data for :: stp_New but it does give me the correct results for stp_Old. If I :: go to QA I can run either sproc and they both execute correctly. I :: am at a loss about this :: one... anyone ever run into something similar? :: :: This is my code... :: :: ------------------------------------------------------------------------- --- :: ---------------------------- :: ALTER PROCEDURE stp_004A_lstHelpDeskNewCalls (@LoginID :: NVARCHAR(50)) AS :: :: SET NOCOUNT ON :: Declare @SetDateTime as datetime, @entryUser as uniqueidentifier :: SET @entryUser = dbo.udfEntryUSER(@LoginID) :: SET @SetDateTime = CAST ( :: CAST (DATEPART(m, getUTCdate()) AS VARCHAR(4)) + '/' + :: CAST (DATEPART(d, getUTCdate()) AS VARCHAR(4))+ '/' + :: CAST (DATEPART(yyyy, getUTCdate()) AS VARCHAR(4)) + ' :: 00:00:00' AS DATETIME) :: :: IF EXISTS (select * from tempdb.dbo.sysobjects where id = :: object_id(N'tempdb..#MaxStatus')) BEGIN :: DROP TABLE #MaxStatus :: END :: CREATE TABLE #MaxStatus :: ([HelpDeskID] [UniqueIdentifier] Not Null, :: [HelpStatusID] [int] Not Null, :: [entryTime] [dateTIME] Not Null) :: INSERT INTO #MaxStatus(HelpDeskID, HelpStatusID, entryTIME) :: Select A.HelpDeskID, A.HelpStatusID, A.entryTIME FROM :: dbo.tbl_HelpDeskStatus AS A WITH(NOLOCK) :: LEFT OUTER JOIN :: (SELECT HelpDeskID, Max(entryTIME) as entryTIME From :: dbo.tbl_HelpDeskStatus WITH(NOLOCK) Group By HelpDeskID) AS B :: ON (A.HelpDeskID = B.HelpDeskID AND A.entryTIME = B.entryTIME AND :: HelpStatusID < 2) :: :: IF EXISTS (select * from tempdb.dbo.sysobjects where id = :: object_id(N'tempdb..#HelpDeskCallDetails')) BEGIN :: DROP TABLE #HelpDeskCallDetails :: END :: CREATE TABLE #HelpDeskCallDetails :: ([HelpDeskID] [uniqueidentifier] Not Null, :: [CallNotes] [nvarchar](20) Null, :: [ContactAddrID] [uniqueidentifier] Null, :: [entryTIME] [datetime] Null, :: [entryUSER] [uniqueidentifier] Null) :: :: INSERT INTO #HelpDeskCallDetails (HelpDeskID, CallNotes, :: ContactAddrID, entryTIME, entryUSER) :: Select A.HelpDeskID, LEFT(A.CallNotes, 20), A.ContactAddrID, :: A.entryTIME, :: A.entryUSER FROM dbo.tbl_HelpDeskCallDetails AS A WITH(NOLOCK) :: INNER JOIN :: (SELECT HelpDeskID, Max(entryTIME) as entryTIME From :: dbo.tbl_HelpDeskCallDetails WITH(NOLOCK) Group By HelpDeskID) AS B :: ON (A.HelpDeskID = B.HelpDeskID AND A.entryTIME = B.entryTIME ) :: :: :: :: Select HD.CaseNumberID, M.SN,Ctc.ContactName,Co.CompanyName, :: Addr.LocationName,ZipList.State, :: HelpDeskCallDetails.CallNotes AS [Call Notes], :: HelpDeskCallDetails.entryTIME :: FROM dbo.tbl_HelpDesk HD WITH(NOLOCK) INNER JOIN dbo.tbl_Machine M :: WITH(NOLOCK) ON HD.MachineID = M.MachineID :: :: LEFT OUTER JOIN :: (SELECT * FROM #MaxStatus WITH(NOLOCK) ) AS MaxStatus :: ON HD.HelpDeskID = MaxStatus.HelpDeskID :: :: INNER JOIN :: (SELECT * FROM #HelpDeskCallDetails WITH(NOLOCK) ) AS :: HelpDeskCallDetails :: ON HD.HelpDeskID = HelpDeskCallDetails.HelpDeskID :: :: LEFT OUTER JOIN dbo.tbl_ContactAddress AS CtcAddr WITH(NOLOCK) ON :: HelpDeskCallDetails.ContactAddrID = CtcAddr.ContactAddrID :: LEFT OUTER JOIN dbo.tbl_Contacts AS Ctc WITH(NOLOCK) ON :: CtcAddr.ContactID :: = Ctc.ContactID :: LEFT OUTER JOIN dbo.tbl_Address As Addr WITH(NOLOCK) ON :: CtcAddr.AddrID = Addr.AddrID :: LEFT OUTER JOIN dbo.tbl_Company AS Co WITH(NOLOCK) ON :: Addr.CompanyID = Co.CompanyID :: LEFT OUTER JOIN dbo.tbl_AddrZipList AS ZipList WITH(NOLOCK) ON :: Addr.ZipID :: = ZipList.ZipID :: :: --THIS LINE BELOW IS WHAT CHANGES from >= to <, both run in QA, but :: only the :: OLD (<) runs in Access :: :: Where HelpDeskCallDetails.entryTIME >= @SetDateTime AND :: HelpDeskCallDetails.entryUSER = @entryUser :: :: ORDER BY HelpDeskCallDetails.entryTIME ASC :: :: :: IF EXISTS (select * from tempdb.dbo.sysobjects where id = :: object_id(N'tempdb..#MaxStatus')) BEGIN :: DROP TABLE #MaxStatus :: END :: IF EXISTS (select * from tempdb.dbo.sysobjects where id = :: object_id(N'tempdb..#HelpDeskCallDetails')) BEGIN :: DROP TABLE #HelpDeskCallDetails :: END :: :: SET NOCOUNT OFF :: :: :: -Francisco :: http://rcm.netfirms.com :: :: :: _______________________________________________ :: dba-SQLServer mailing list :: dba-SQLServer at databaseadvisors.com :: http://databaseadvisors.com/mailman/listinfo/dba-sqlserver :: http://www.databaseadvisors.com :: : : _________________________________________________________________ : STOP MORE SPAM with the new MSN 8 and get 2 months FREE* : http://join.msn.com/?page=features/junkmail : : _______________________________________________ : dba-SQLServer mailing list : dba-SQLServer at databaseadvisors.com : http://databaseadvisors.com/mailman/listinfo/dba-sqlserver : http://www.databaseadvisors.com