UDF gives 0 zero records on SS2k; Correct on MSDERelA

[Aubrey]

An AccessProject Census Report uses several UDFs to Select subsets of Active
Accounts. On Notebooks, with local MSDE and Anonymous Merge Replica of
Database, these work correctly. But the Desktops, connected to SS2K on
SBS2003, return 0 (zero) records. Is it the system date format? an Access
setting? or the date compare operators?

Any ideas? or suggestions to resolve this?


CREATE FUNCTION [dbo].[fctsubrptCensusReferral](@prmStartDate datetime,
@prmEndDate datetime)
RETURNS TABLE
AS
RETURN ( SELECT TOP 100 PERCENT dbo.Patients.PID, dbo.Patients.[Patient
ID #], ISNULL(dbo.Patients.[Patient Name First] + N' ', N'')
+ dbo.Patients.[Patient Name Last] AS PtName,
dbo.Patients.[Date of Separation], dbo.Patients.[Referral Date],
dbo.tblProviders.ProvName,
dbo.Patients.[Patient Name Last],
dbo.Patients.[Patient Name First]
FROM dbo.Patients LEFT OUTER JOIN
dbo.tblProviders ON dbo.Patients.AdmitRN =
dbo.tblProviders.ProvID
WHERE (dbo.Patients.[Referral Date] >= @prmStartDate) AND
(dbo.Patients.[Referral Date] <= @prmEndDate) AND
(dbo.Patients.[Patient Name Last] IS NOT NULL) AND
(dbo.Patients.[Date of Admission] IS NULL)
ORDER BY dbo.Patients.[Referral Date], dbo.Patients.[Patient Name Last],
dbo.Patients.[Patient Name First] )


Migrated an Access Application (MDB/MDE) to an Access Project (ADP/ADE). An
Access Form provides the @prmDates via ServerFilter of the form:
@prmStartDate = forms.frmMainPtCare.tbxStartDate

- - - - - - -
Aubrey Kelley

 

[Peter Yang [MSFT]]

Hello Aubrey,

It seems there are some issue with date type processing between client and
server. I suggest that you check by using part of the following where
conditions to see if the issue persists:

WHERE (dbo.Patients.[Referral Date] >= @prmStartDate) AND
(dbo.Patients.[Referral Date] <= @prmEndDate) AND
(dbo.Patients.[Patient Name Last] IS NOT NULL) AND
(dbo.Patients.[Date of Admission] IS NULL)

Please run the function directly in query design window. You may want to
use SQL profiler on SBS 2003 server to trace the the SQL query. If you run
the query directly from Query Analyzer, does the issue persist?

To narrow down the issue, you may want to install latest MDAC on both
client and driver,

899456 Release manifest for MDAC 2.8 Service Pack 1 (2.81.1117.6)
http://support.microsoft.com/?id=899456

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

=====================================================

Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.

This and other support options are available here:

BCPS:
https://partner.microsoft.com/US/technicalsupport/supportoverview/40010469

Others: https://partner.microsoft.com/US/technicalsupport/supportoverview/

If you are outside the United States, please visit our International
Support page:
http://support.microsoft.com/default.aspx?scid=/international.aspx.

=====================================================

This posting is provided "AS IS" with no warranties, and confers no rights.


--------------------
| Thread-Topic: UDF gives 0 zero records on SS2k; Correct on MSDERelA
| thread-index: AcWuMPEFfzXjSxfySn6BItK1spP/xg==
| X-WBNR-Posting-Host: 66.207.65.30
| From: =?Utf-8?B?QXVicmV5?= <[email protected]>
| Subject: UDF gives 0 zero records on SS2k; Correct on MSDERelA
| Date: Wed, 31 Aug 2005 06:36:07 -0700
| Lines: 37
| Message-ID: <[email protected]>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.access.reports
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.access.reports:37736
| X-Tomcat-NG: microsoft.public.access.reports
|
| An AccessProject Census Report uses several UDFs to Select subsets of
Active
| Accounts. On Notebooks, with local MSDE and Anonymous Merge Replica of
| Database, these work correctly. But the Desktops, connected to SS2K on
| SBS2003, return 0 (zero) records. Is it the system date format? an Access
| setting? or the date compare operators?
|
| Any ideas? or suggestions to resolve this?
|
|
| CREATE FUNCTION [dbo].[fctsubrptCensusReferral](@prmStartDate datetime,
| @prmEndDate datetime)
| RETURNS TABLE
| AS
| RETURN ( SELECT TOP 100 PERCENT dbo.Patients.PID,
dbo.Patients.[Patient
| ID #], ISNULL(dbo.Patients.[Patient Name First] + N' ', N'')
| + dbo.Patients.[Patient Name Last] AS PtName,
| dbo.Patients.[Date of Separation], dbo.Patients.[Referral Date],
| dbo.tblProviders.ProvName,
| dbo.Patients.[Patient Name Last],
| dbo.Patients.[Patient Name First]
| FROM dbo.Patients LEFT OUTER JOIN
| dbo.tblProviders ON dbo.Patients.AdmitRN =
| dbo.tblProviders.ProvID
| WHERE (dbo.Patients.[Referral Date] >= @prmStartDate) AND
| (dbo.Patients.[Referral Date] <= @prmEndDate) AND
| (dbo.Patients.[Patient Name Last] IS NOT NULL) AND
| (dbo.Patients.[Date of Admission] IS NULL)
| ORDER BY dbo.Patients.[Referral Date], dbo.Patients.[Patient Name Last],
| dbo.Patients.[Patient Name First] )
|
|
| Migrated an Access Application (MDB/MDE) to an Access Project (ADP/ADE).
An
| Access Form provides the @prmDates via ServerFilter of the form:
| @prmStartDate = forms.frmMainPtCare.tbxStartDate
|
| - - - - - - -
| Aubrey Kelley
|