[dba-SQLServer]Sproc runs and then it doesn't

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