Francisco H Tapia
my.lists at verizon.net
Tue Jul 29 11:55:32 CDT 2003
perhaps I'm not reviewing the profiler correctly, but this is what I see from QA EXEC stp_004A_lstHelpDeskTESTCalls 'troy', 1 (this is the merged sproc from the previous 2) but the same execution from Access looks like this but also returns data in QA. exec sp_executesql N' EXEC "stp_004A_lstHelpDeskTESTCalls" @P1 , @P2 ', N'@P1 nvarchar(4), at P2 bit', N'troy', 1 Another step I tried... was to call the sproc via an ADO command and the recordset is populated as expected. SO, my conclusion is that ACCESS is trying to be tooo smart and is gumming up the process. Thanks for any other tips. -Francisco http://rcm.netfirms.com On Tuesday, July 29, 2003 9:14 AM [GMT-8], Francisco H Tapia <my.lists at verizon.net> wrote: : 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 : : : _______________________________________________ : dba-SQLServer mailing list : dba-SQLServer at databaseadvisors.com : http://databaseadvisors.com/mailman/listinfo/dba-sqlserver : http://www.databaseadvisors.com