Billy Pang
tuxedo_man at hotmail.com
Tue Jul 29 10:28:49 CDT 2003
so both sprocs work perfectly at home? Can you alter both sprocs so that it just does one simple select? if both sprocs work at the office, then alter the sproc again using the real code and see if it still works. also, are you running both sprocs concurrently? also, since you are using ss2k, how about replacing your temp tables with table variables? >From: "Francisco Tapia" <my.lists at verizon.net> >Reply-To: dba-sqlserver at databaseadvisors.com >To: <dba-sqlserver at databaseadvisors.com> >Subject: Re: [dba-SQLServer]Sproc runs and then it doesn't >Date: Tue, 29 Jul 2003 02:05:33 -0700 > >Billy, > I took the adp, and the database home and updated my home sql developer >copy with the office copy of the server w/ no code changes and off the cuff >copy to my hdd from the office the adp now displays the record, Sql Server >2000 SP3, but at the office it still does not and it is the same version of >the db, and access adp. > >this is most bazaar. > > >-Francisco >http://rcm.netfirms.com/ > >On Tuesday, July 29, 2003 12:36 AM [GMT -8], >Francisco Tapia <my.lists at verizon.net> wrote: > >: 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