Mike and Doris Manning
mikedorism at ntelos.net
Tue Jul 29 15:53:01 CDT 2003
If you set the default to 0, it will always return all the records.
Doris Manning
Database Administrator
Hargrove Inc.
www.hargroveinc.com
-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Francisco H
Tapia
Sent: Tuesday, July 29, 2003 2:55 PM
To: dba-sqlserver at databaseadvisors.com
Cc: SQL Server 2k List
Subject: [dba-SQLServer]Re: Sproc runs and then it doesn't
My co-worker found an option in Access for the amount of records to limit
the return to, in Access it is defaulted to 10k, for my home version it is
set to this limit as well as here at work, but at home the results are as
expected, at work I had to modify this result to > 10,000 records, I chose
as a test 100,000,000 which yields the correct results.
boy do I hate Access sometimes...
/RANT/
Now if the resulting records are limited to the 10k, why on earth would it
be blocking the 1 returned record (this is a test of course but in normal
everyday results it would return anywhere between 20 - 100 records for new
entries. Access is trying to be way too smart for it's own good... I know
I could avoid this headache and do it all VB but I do like the simple things
in access for speedy RAD, Listbox population, automatic handling of cbo's
for auto search select when you type into the box. OpenArguments...
well thanks for the vent.
/end RANT/
-Francisco
http://rcm.netfirms.com
On Tuesday, July 29, 2003 11:12 AM [GMT-8],
Francisco H Tapia <my.lists at verizon.net> wrote:
: Thanks Billy,
: that seems to have sped up the sproc from a runtime of 13seconds
: down to 8-7 seconds nice... tested on a fellow developer's pc, I was
: running Access 2k out of the box and at work I have Access2k sr1, the
: other developer has Access2k sr1 loaded at home and it ran well there
: too.
:
: so there is some other problem locally at work that breaks this.
:
:
: -Francisco
: http://rcm.netfirms.com
:
: On Tuesday, July 29, 2003 10:26 AM [GMT-8],
: Billy Pang <tuxedo_man at hotmail.com> wrote:
:
:: There are a lot of temp tables in the sprocs you are trying to
:: running. You can convert them into table variables. They work just
:: like regular temporary tables except that it is not written onto
:: disk.
::
:: look up data type - table in BOL.
::
:: ==cut here ===========================
:: SET NOCOUNT ON
:: DECLARE @my_product TABLE(PROD_ID INT IDENTITY(1,1) PRIMARY KEY,
:: PROD_NAME VARCHAR(50), PROD_DATE SMALLDATETIME DEFAULT(GETDATE()))
::
:: INSERT INTO @my_product(PROD_NAME) VALUES('red book');
:: INSERT INTO @my_product(PROD_NAME) VALUES('blue book');
:: INSERT INTO @my_product(PROD_NAME) VALUES('green book');
:: INSERT INTO @my_product(PROD_NAME) VALUES('white book');
::
:: UPDATE @my_product SET PROD_NAME = 'BLUE BOOK1' WHERE PROD_ID = 2
:: DELETE FROM @my_product where PROD_ID = 3
:: SELECT * FROM @my_product
::
:: SET NOCOUNT OFF
:: =============================
::
::
::: From: "Francisco H 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 09:14:44 -0700
:::
::: 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
:::
::
:: _________________________________________________________________
:: The new MSN 8: smart spam protection and 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
:
:
: _______________________________________________
: dba-SQLServer mailing list
: dba-SQLServer at databaseadvisors.com
: http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
: http://www.databaseadvisors.com
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com