I have a UDF that when I run by two different users, gives two different
orders of data columns. One follows the syntax of the function and the othe
r
is mis-ordered but returns this way consistently. Has anyone else
encountered this?Can you post the ddl?
AMB
"ZachB" wrote:
> I have a UDF that when I run by two different users, gives two different
> orders of data columns. One follows the syntax of the function and the ot
her
> is mis-ordered but returns this way consistently. Has anyone else
> encountered this?|||Not sure what you mean by posting the ddl but here's the syntax of the UDF:
CREATE FUNCTION dbo. MISMOqryMINRegistrationCldDateNE(@.Enter_
Begin_Date
datetime,
@.Enter_End_Date datetime)
RETURNS TABLE
AS
RETURN ( SELECT TOP 100 PERCENT dbo.dbo_Tracking_File_Ext.f755#MERS,
dbo.MERS.MERS_MINNumber AS [MISMO MERS], dbo.tblLoanDetails.FirstSecond,
dbo.dbo_Tracking_File_Ext.f422#ClosingDate AS [MV Note
Date], dbo.tblLoanInfo.ClsdTDDate AS NoteDate,
dbo.LOAN_DETAILS.ClosingDate AS [MISMO ClosingDate],
dbo.tblLoanDetails.[Loan Amount],
dbo.LOAN_DETAILS.DisbursementDate AS [Funding Date],
dbo.dbo_Tracking_File_Ext.F251#CompanyName1,
dbo.dbo_Tracking_File_Ext.f252#CompanyName2,
dbo.GENERIC_ENTITY_LenderName._UnparsedName AS [MISMO CompanyName1],
' ' AS [MISMO CompanyName2],
dbo.qryBorrJoin.firstname, dbo.qryBorrJoin.B1MI, dbo.qryBorrJoin.Name AS
B1LastName, dbo.qryBorrJoin.BSSN,
dbo.qryBorrJoin.CoFirstName, dbo.qryBorrJoin.B2MI,
dbo.qryBorrJoin.coLastName, dbo.qryBorrJoin.CBSSN, dbo.tblLoanInfo.[Security
Address Street],
dbo.tblLoanInfo.City, dbo.tblLoanInfo.State,
dbo.tblLoanInfo.ZIP, dbo.dbo_Tracking_File.f555#property_county,
dbo.PROPERTY._County AS [MISMO property_county],
dbo.dbo_Tracking_File_Ext.f519#TrusteeName,
dbo.GENERIC_ENTITY_Trustee._UnparsedName AS [MISMO
TrusteeName], dbo.tblStateLookup.StateTrustVMort,
dbo.tblClosedLoan.LoanNumber,
dbo.[tblPurpose Lookup].PurpComerica,
dbo.tblClosedLoan.CommitID, dbo.tblClosedLoan.SandDYN,
dbo.tblClosedLoan.PSStatus
FROM dbo.tblStateLookup RIGHT OUTER JOIN
dbo.dbo_Tracking_File RIGHT OUTER JOIN
dbo.qryBorrJoin INNER JOIN
dbo.tblLoanInfo INNER JOIN
dbo.tblLoanDetails INNER JOIN
dbo.tblClosedLoan ON dbo.tblLoanDetails.LoanDetailID =
dbo.tblClosedLoan.NCLoanNumber ON
dbo.tblLoanInfo.[Acct Number] =
dbo.tblLoanDetails.[Loan Number] ON
dbo.qryBorrJoin.[Loan Number] = dbo.tblLoanInfo.[Acct
Number] LEFT OUTER JOIN
dbo.[tblPurpose Lookup] ON dbo.tblLoanInfo.Purpose =
dbo.[tblPurpose Lookup].[Purpose Lookup] LEFT OUTER JOIN
dbo.dbo_Tracking_File_Ext ON
dbo.tblClosedLoan.LoanNumber = dbo.dbo_Tracking_File_Ext.Loan_ID ON
dbo.dbo_Tracking_File.Loan_ID =
dbo.tblClosedLoan.LoanNumber ON dbo.tblStateLookup.StateID =
dbo.tblLoanInfo.State LEFT OUTER JOIN
dbo._CLOSING_DOCUMENTS LEFT OUTER JOIN
dbo.GENERIC_ENTITY_LenderName ON
dbo._CLOSING_DOCUMENTS.CLDC_ID = dbo.GENERIC_ENTITY_LenderName.CLDC_ID LEFT
OUTER JOIN
dbo.GENERIC_ENTITY_Trustee RIGHT OUTER JOIN
dbo.RECORDABLE_DOCUMENT ON
dbo.GENERIC_ENTITY_Trustee.RCDO_ID = dbo.RECORDABLE_DOCUMENT.RCDO_ID ON
dbo._CLOSING_DOCUMENTS.CLDC_ID =
dbo.RECORDABLE_DOCUMENT.CLDC_ID RIGHT OUTER JOIN
dbo.MORTGAGE_TERMS_MaxAppl LEFT OUTER JOIN
dbo.LOAN_APPLICATION ON
dbo.MORTGAGE_TERMS_MaxAppl.APPL_ID = dbo.LOAN_APPLICATION.APPL_ID LEFT OUTER
JOIN
dbo.PROPERTY ON dbo.MORTGAGE_TERMS_MaxAppl.APPL_ID =
dbo.PROPERTY.APPL_ID ON
dbo._CLOSING_DOCUMENTS.LOAN_ID =
dbo.LOAN_APPLICATION.LOAN_ID LEFT OUTER JOIN
dbo.LOAN_DETAILS ON dbo._CLOSING_DOCUMENTS.CLDC_ID =
dbo.LOAN_DETAILS.CLDC_ID LEFT OUTER JOIN
dbo.MERS ON dbo.MORTGAGE_TERMS_MaxAppl.APPL_ID =
dbo.MERS.APPL_ID ON
dbo.tblClosedLoan.LoanNumber =
dbo.MORTGAGE_TERMS_MaxAppl.LenderLoanIdentifier
WHERE (dbo.tblLoanInfo.ClsdTDDate BETWEEN @.Enter_Begin_Date AND
@.Enter_End_Date) AND (dbo.tblClosedLoan.SandDYN = 0 OR
dbo.tblClosedLoan.SandDYN IS NULL) AND
(dbo.tblClosedLoan.PSStatus <> N'rescinded')
ORDER BY dbo.qryBorrJoin.Name, dbo.qryBorrJoin.firstname )|||ZachB,
When you say "gives two different orders of data columns", Do you mean
different sort of the result or that the column list is different?. How are
you querying this table function?
The "order by" clause used inside the function does not guarantee any order
of the result when you use:
declare @.sd datetime
declare @.ed datetime
set @.sd = '20050101'
set @.ed = '20050321'
select col1, col2, ..., coln
from dbo.MISMOqryMINRegistrationCldDateNE(@.sd, @.ed) as t
you have to use an "order by" clause again if you want the order of the rows
to be consistent.
select col1, col2, ..., coln
from dbo.MISMOqryMINRegistrationCldDateNE(@.sd, @.ed) as t
order by col1, ...
AMB
"ZachB" wrote:
> Not sure what you mean by posting the ddl but here's the syntax of the UDF
:
> CREATE FUNCTION dbo. MISMOqryMINRegistrationCldDateNE(@.Enter_
Begin_Date
> datetime,
> @.Enter_End_Date datetime)
> RETURNS TABLE
> AS
> RETURN ( SELECT TOP 100 PERCENT dbo.dbo_Tracking_File_Ext.f755#MERS,
> dbo.MERS.MERS_MINNumber AS [MISMO MERS], dbo.tblLoanDetails.FirstSecond,
> dbo.dbo_Tracking_File_Ext.f422#ClosingDate AS [MV No
te
> Date], dbo.tblLoanInfo.ClsdTDDate AS NoteDate,
> dbo.LOAN_DETAILS.ClosingDate AS [MISMO ClosingDate],
> dbo.tblLoanDetails.[Loan Amount],
> dbo.LOAN_DETAILS.DisbursementDate AS [Funding Date],
> dbo.dbo_Tracking_File_Ext.F251#CompanyName1,
> dbo.dbo_Tracking_File_Ext.f252#CompanyName2,
> dbo.GENERIC_ENTITY_LenderName._UnparsedName AS [MISMO CompanyName1],
> ' ' AS [MISMO CompanyName2],
> dbo.qryBorrJoin.firstname, dbo.qryBorrJoin.B1MI, dbo.qryBorrJoin.Name AS
> B1LastName, dbo.qryBorrJoin.BSSN,
> dbo.qryBorrJoin.CoFirstName, dbo.qryBorrJoin.B2MI,
> dbo.qryBorrJoin.coLastName, dbo.qryBorrJoin.CBSSN, dbo.tblLoanInfo.[Securi
ty
> Address Street],
> dbo.tblLoanInfo.City, dbo.tblLoanInfo.State,
> dbo.tblLoanInfo.ZIP, dbo.dbo_Tracking_File.f555#property_county,
> dbo.PROPERTY._County AS [MISMO property_county],
> dbo.dbo_Tracking_File_Ext.f519#TrusteeName,
> dbo.GENERIC_ENTITY_Trustee._UnparsedName AS [MISMO
> TrusteeName], dbo.tblStateLookup.StateTrustVMort,
> dbo.tblClosedLoan.LoanNumber,
> dbo.[tblPurpose Lookup].PurpComerica,
> dbo.tblClosedLoan.CommitID, dbo.tblClosedLoan.SandDYN,
> dbo.tblClosedLoan.PSStatus
> FROM dbo.tblStateLookup RIGHT OUTER JOIN
> dbo.dbo_Tracking_File RIGHT OUTER JOIN
> dbo.qryBorrJoin INNER JOIN
> dbo.tblLoanInfo INNER JOIN
> dbo.tblLoanDetails INNER JOIN
> dbo.tblClosedLoan ON dbo.tblLoanDetails.LoanDetailID
=
> dbo.tblClosedLoan.NCLoanNumber ON
> dbo.tblLoanInfo.[Acct Number] =
> dbo.tblLoanDetails.[Loan Number] ON
> dbo.qryBorrJoin.[Loan Number] = dbo.tblLoanInfo.[Acc
t
> Number] LEFT OUTER JOIN
> dbo.[tblPurpose Lookup] ON dbo.tblLoanInfo.Purpose =
> dbo.[tblPurpose Lookup].[Purpose Lookup] LEFT OUTER JOIN
> dbo.dbo_Tracking_File_Ext ON
> dbo.tblClosedLoan.LoanNumber = dbo.dbo_Tracking_File_Ext.Loan_ID ON
> dbo.dbo_Tracking_File.Loan_ID =
> dbo.tblClosedLoan.LoanNumber ON dbo.tblStateLookup.StateID =
> dbo.tblLoanInfo.State LEFT OUTER JOIN
> dbo._CLOSING_DOCUMENTS LEFT OUTER JOIN
> dbo.GENERIC_ENTITY_LenderName ON
> dbo._CLOSING_DOCUMENTS.CLDC_ID = dbo.GENERIC_ENTITY_LenderName.CLDC_ID LEF
T
> OUTER JOIN
> dbo.GENERIC_ENTITY_Trustee RIGHT OUTER JOIN
> dbo.RECORDABLE_DOCUMENT ON
> dbo.GENERIC_ENTITY_Trustee.RCDO_ID = dbo.RECORDABLE_DOCUMENT.RCDO_ID ON
> dbo._CLOSING_DOCUMENTS.CLDC_ID =
> dbo.RECORDABLE_DOCUMENT.CLDC_ID RIGHT OUTER JOIN
> dbo.MORTGAGE_TERMS_MaxAppl LEFT OUTER JOIN
> dbo.LOAN_APPLICATION ON
> dbo.MORTGAGE_TERMS_MaxAppl.APPL_ID = dbo.LOAN_APPLICATION.APPL_ID LEFT OUT
ER
> JOIN
> dbo.PROPERTY ON dbo.MORTGAGE_TERMS_MaxAppl.APPL_ID =
> dbo.PROPERTY.APPL_ID ON
> dbo._CLOSING_DOCUMENTS.LOAN_ID =
> dbo.LOAN_APPLICATION.LOAN_ID LEFT OUTER JOIN
> dbo.LOAN_DETAILS ON dbo._CLOSING_DOCUMENTS.CLDC_ID =
> dbo.LOAN_DETAILS.CLDC_ID LEFT OUTER JOIN
> dbo.MERS ON dbo.MORTGAGE_TERMS_MaxAppl.APPL_ID =
> dbo.MERS.APPL_ID ON
> dbo.tblClosedLoan.LoanNumber =
> dbo.MORTGAGE_TERMS_MaxAppl.LenderLoanIdentifier
> WHERE (dbo.tblLoanInfo.ClsdTDDate BETWEEN @.Enter_Begin_Date AND
> @.Enter_End_Date) AND (dbo.tblClosedLoan.SandDYN = 0 OR
> dbo.tblClosedLoan.SandDYN IS NULL) AND
> (dbo.tblClosedLoan.PSStatus <> N'rescinded')
> ORDER BY dbo.qryBorrJoin.Name, dbo.qryBorrJoin.firstname )|||The UDF is being called from an MS Access .adp project. The "order" of the
data columns is different meaning in one case it shows Column A, Column B,
Column C, Column D but in the other it shows Column B, Column C, Column A,
Column D (Even if the syntax says SELECT Column A, Column B, Column C, Colum
n
D.
row 1 test1 test2 test3 test4
vs.
row 1 test2 test3 test1 test4
"Alejandro Mesa" wrote:
> ZachB,
> When you say "gives two different orders of data columns", Do you mean
> different sort of the result or that the column list is different?. How ar
e
> you querying this table function?
> The "order by" clause used inside the function does not guarantee any orde
r
> of the result when you use:
> declare @.sd datetime
> declare @.ed datetime
> set @.sd = '20050101'
> set @.ed = '20050321'
> select col1, col2, ..., coln
> from dbo.MISMOqryMINRegistrationCldDateNE(@.sd, @.ed) as t
> you have to use an "order by" clause again if you want the order of the ro
ws
> to be consistent.
> select col1, col2, ..., coln
> from dbo.MISMOqryMINRegistrationCldDateNE(@.sd, @.ed) as t
> order by col1, ...
>
> AMB
> "ZachB" wrote:
>|||ZachB,
Can you trace the statements sent to sql server by the project?
AMB
"ZachB" wrote:
> The UDF is being called from an MS Access .adp project. The "order" of th
e
> data columns is different meaning in one case it shows Column A, Column B,
> Column C, Column D but in the other it shows Column B, Column C, Column A,
> Column D (Even if the syntax says SELECT Column A, Column B, Column C, Col
umn
> D.
> row 1 test1 test2 test3 test4
> vs.
> row 1 test2 test3 test1 test4
> "Alejandro Mesa" wrote:
>|||Not that I'm aware of. I know you can check the properties of a particular
spid under Current Activity and see what syntax is or has just been run. Bu
t
I would assume that since the two users are hitting the same .adp they
shouldn't be passing different statements.
The MS Access .adp menu option states:
Open table 'dbo.MISMOqryMINRegistrationCldDateNE'
and this is just one example. This mis-ordering happens consisently across
several UDFs that I'm working with.
"Alejandro Mesa" wrote:
> ZachB,
> Can you trace the statements sent to sql server by the project?
>
> AMB|||Use Profiler to trace activities in the server. You can read about it in BOL
.
AMB
"ZachB" wrote:
> Not that I'm aware of. I know you can check the properties of a particula
r
> spid under Current Activity and see what syntax is or has just been run.
But
> I would assume that since the two users are hitting the same .adp they
> shouldn't be passing different statements.
> The MS Access .adp menu option states:
> Open table 'dbo.MISMOqryMINRegistrationCldDateNE'
> and this is just one example. This mis-ordering happens consisently acros
s
> several UDFs that I'm working with.
> "Alejandro Mesa" wrote:
>|||Anyone else? While I'm learning to trace, has anyone ever had a UDF return
data in different COLUMN order for different users? Let me know. Thanks i
n
advance.
"ZachB" wrote:
> I have a UDF that when I run by two different users, gives two different
> orders of data columns. One follows the syntax of the function and the ot
her
> is mis-ordered but returns this way consistently. Has anyone else
> encountered this?|||"ZachB" <ZachB@.discussions.microsoft.com> wrote in message
news:5966BAE6-5CC4-4055-A8D9-454B2E5A2E9C@.microsoft.com...
> Anyone else? While I'm learning to trace, has anyone ever had a UDF
return
> data in different COLUMN order for different users? Let me know. Thanks
in
> advance.
>
Wild Guesses:
1. The clients are executing different code. One client has been updated
and the other hasn't.
2. The clients are attached to different databases, one on test and one on
production.
3. It isn't really happening - The client preferences are different so that
on one client things appear differently but aren't actually different.
(e.g. hidden display controls, etc)
4. You are using Select * and there is a weird caching thing going on.
Good Luck.
Jim
