Francisco H Tapia
my.lists at verizon.net
Tue Jul 29 11:14:44 CDT 2003
That's right Billy, If unmodiffied Database / sprocs and Unmoddifed Front End Access ADP return the data via Access and QA as expected at home the machine is an AMD2000+ w/ 1gig of ram Win2k SP2 + ss2kdev SP3 WORK: Dual 800 Intel Xeon w/1gig of ram Win2kServer SP2 +ss2kserver SP3 well I tried just as you suggested... and no dice :( the >= still doesnt return via access what do you mean Table Variables? Thanks -Francisco http://rcm.netfirms.com On Tuesday, July 29, 2003 8:28 AM [GMT-8], Billy Pang <tuxedo_man at hotmail.com> wrote: : 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