Billy Pang
tuxedo_man at hotmail.com
Tue Jul 29 12:26:10 CDT 2003
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