Locking error between SQL Server 2005 and Access 2003 (1 Viewer)

datAdrenaline

AWF VIP
Local time
Today, 06:23
Joined
Jun 23, 2008
Messages
697
Change the SQL property of qryPeopleAlphabetic

From ....
SELECT Expr1, Person_ID, DateAdded, Title, F_Name, M_Initial,
L_Name, email, Notes, FormalName, SemiFormalName, CommonName
FROM ViewPeopleAlphabetic
ORDER BY Expr1;

To ...
SELECT Expr1, Person_ID, DateAdded, Title, F_Name, M_Initial,
L_Name, email, Notes, FormalName, SemiFormalName, CommonName
FROM ViewPeopleAlphabetic

{Do the sorting in the view}

But again, with out criteria, you are pulling ALL your records ...
 

MSAccessRookie

AWF VIP
Local time
Today, 07:23
Joined
May 2, 2008
Messages
3,428
Change qryPeopleAlphabetic5 SQL property from ....

SELECT IIf(IsNull(L_Name),"",L_Name) & IIf(IsNull(L_Name),"",IIf(IsNull(F_Name),"",", ")) & IIf(IsNull(F_Name),"",F_Name) AS Expr1, Person_ID
FROM tblPeople
ORDER BY 1;

To ..

SELECT (L_Name & "") & (", " + F_Name) AS Expr1, Person_ID
FROM tblPeople
ORDER BY L_Name, F_Name;

The you avoid the VBA call to IsNull() ... the IIf() call is *probably* translated by ODBC and is not effecting you much.

But ... do take note that with out any criteria, you are pulling all the selected fields of all the records anyway.

Your observations are excellent, but this SQL code does not provide what we have now. Currently, whenever there is a First Name and no Last Name, the output is something like "Mary", This code will produce ", Mary" in such a case. That was the need for the second IIf that combined a check for First and Last Names).
 

MSAccessRookie

AWF VIP
Local time
Today, 07:23
Joined
May 2, 2008
Messages
3,428
Change the SQL property of qryPeopleAlphabetic

From ....
SELECT Expr1, Person_ID, DateAdded, Title, F_Name, M_Initial,
L_Name, email, Notes, FormalName, SemiFormalName, CommonName
FROM ViewPeopleAlphabetic
ORDER BY Expr1;

To ...
SELECT Expr1, Person_ID, DateAdded, Title, F_Name, M_Initial,
L_Name, email, Notes, FormalName, SemiFormalName, CommonName
FROM ViewPeopleAlphabetic

{Do the sorting in the view}

But again, with out criteria, you are pulling ALL your records ...

Everything that I have read indicates that ORDER BY is not allowed as part of an SQL Server View. Did I miss something?
 

datAdrenaline

AWF VIP
Local time
Today, 06:23
Joined
Jun 23, 2008
Messages
697
See my edited SQL statement for qryPeopleAlphabetic5 in the post above ... I was editing as you responded ... :)
 

MSAccessRookie

AWF VIP
Local time
Today, 07:23
Joined
May 2, 2008
Messages
3,428
Change qryPeopleAlphabetic5 SQL property from ....

SELECT IIf(IsNull(L_Name),"",L_Name) & IIf(IsNull(L_Name),"",IIf(IsNull(F_Name),"",", ")) & IIf(IsNull(F_Name),"",F_Name) AS Expr1, Person_ID
FROM tblPeople
ORDER BY 1;

To ..

SELECT (L_Name & "") & IIf(L_Name Is Null, F_Name, (", " + F_Name)) AS Expr1, Person_ID
FROM tblPeople
ORDER BY L_Name, F_Name;

The you avoid the VBA call to IsNull() ... the IIf() call is *probably* translated by ODBC and is not effecting you much.

But ... do take note that with out any criteria, you are pulling all the selected fields of all the records anyway.

Thanks again for responding, but this one has the reverse problem. Currently, whenever there is a Last Name and no First Name, the output is something like "Jones", This code will produce "Jones, " in such a case.
 

datAdrenaline

AWF VIP
Local time
Today, 06:23
Joined
Jun 23, 2008
Messages
697
Then your data has Zero Length Strings... I just did a quick test using ....

SELECT (L_Name & "") & IIf(L_Name Is Null, F_Name, (", " + F_Name)) AS Expr1

L_Name; F_Name; Result
Spaulding; <Null>; Spaulding
<Null>; Brent; Brent
Spauliding; Brent; Spaulding, Brent

...

Here is another variation ....

SELECT IIf(L_Name Is Null, F_Name, L_Name & (", " + F_Name)) & "" AS Expr1 ....


Do you really want to return a ZLS if both names are Null? ... I would think you would want to return a Null in that scenario.
 

datAdrenaline

AWF VIP
Local time
Today, 06:23
Joined
Jun 23, 2008
Messages
697
>> Everything that I have read indicates that order by is not allowed as part of an SQL Server View. <<

What? ... where did you read that? ... You can definatly SORT a VIEW!!!! ....
 

MSAccessRookie

AWF VIP
Local time
Today, 07:23
Joined
May 2, 2008
Messages
3,428
>> Everything that I have read indicates that order by is not allowed as part of an SQL Server View. <<

What? ... where did you read that? ... You can definatly SORT a VIEW!!!! ....


I figured out what I forgot. I added an ORDER BY statement to one of my views and got the error message (See below). The message indicates that I cannot sort a view UNLESS it has a TOP Clause as part of the SELECT statement (The FOR XML does not apply in this case). I guess I will need to add a TOP 100 Percent Clause to all of the queries that can benefit from sorting.

Code:
Msg 1033, Level 15, State 1, Procedure ViewRDINGRED, Line 31
The ORDER BY clause is invalid in views, inline functions, derived tables, 
subqueries, and common table expressions, unless TOP or FOR XML is 
also specified.
 

datAdrenaline

AWF VIP
Local time
Today, 06:23
Joined
Jun 23, 2008
Messages
697
Maybe try this for your SQL statement of the View ...

Code:
SELECT TOP 100 Percent
(CASE
    WHEN L_Name IS NULL THEN
        ISNULL(F_Name,'')
    ELSE L_Name + ISNULL((', ' + F_Name),'')
 END),'') AS Expr1,
Person_ID,
DateAdded,
Title,
F_Name,
M_Initial,
L_Name,
email,
Notes,
ISNULL(Title + ' ','')+ISNULL(F_Name + ' ','')+ISNULL(L_Name,'') AS FormalName,
ISNULL(F_Name + ' ', Title + ' ') + ISNULL(L_Name,'') AS SemiFormalName,
CommonName,
SSMA_TimeStamp
FROM dbo.tblPeople 
ORDER BY L_Name, F_Name

Please do take note that all this SQL and any VBA I may post should be considered *AIR CODE* ... so .... no warrenties expressed or implied!!! :)
 

MSAccessRookie

AWF VIP
Local time
Today, 07:23
Joined
May 2, 2008
Messages
3,428
Maybe try this for your SQL statement of the View ...

Code:
SELECT TOP 100 Percent
(CASE
    WHEN L_Name IS NULL THEN
        ISNULL(F_Name,'')
    ELSE L_Name + ISNULL((', ' + F_Name),'')
 END),'') AS Expr1,
Person_ID,
DateAdded,
Title,
F_Name,
M_Initial,
L_Name,
email,
Notes,
ISNULL(Title + ' ','')+ISNULL(F_Name + ' ','')+ISNULL(L_Name,'') AS FormalName,
ISNULL(F_Name + ' ', Title + ' ') + ISNULL(L_Name,'') AS SemiFormalName,
CommonName,
SSMA_TimeStamp
FROM dbo.tblPeople 
ORDER BY L_Name, F_Name

Please do take note that all this SQL and any VBA I may post should be considered *AIR CODE* ... so .... no warrenties expressed or implied!!! :)


I think I need ORDER BY 1 at the bottom, because:
  1. Expr1 is the First Select Item
  2. Expr1 is more than just the concatenation of L_Name and F_Name so the sort willnot be correct
  3. Expr1 is not valid in the Order By Clause for some reason
  4. I do not want to copy the formula that creates Expr1 into the ORDER BY Clause.
 

datAdrenaline

AWF VIP
Local time
Today, 06:23
Joined
Jun 23, 2008
Messages
697
>> Expr1 is more than just the concatenation of L_Name and F_Name so the sort willnot be correct <<

Have you tried it? ... The sort key indicated is a sortof a concatenation of L_Name and F_Name, but that is exactly what your expression does, with the execption of the ", " .... With the L_Name, F_Name ORDER BY clause your data will sort on L_Name THEN F_Name so your sort keys (using my sample data) .... would be ...

Spaulding
Brent
SpauldingBrent

Which will yeild and order of ...

Brent
Spaulding
SpauldingBrent

If you use the result of Expr1, your sort keys will be ...

Spaulding
Brent
Spaudling, Brent

Which will yeild and order of ...

Brent
Spaulding
Spaudling, Brent


Which is the same order ... In addition, it is my best guess (almost virtually certain) that SQL Server will your expression to sort on when it develops its execution plan, the "1", I *believe* is basically a short cut for developers, but the engine will expand it all out upon execution! .... PLUS, if you create an INDEX on L_Name and F_Name, and sort as I have shown, your View will sort and return records MUCH faster than sorting on the expression.

>> Expr1 is not valid in the Order By Clause for some reason <<

Aliases can not be used to sort on ... if you wishes, you could use your UNSORTED SQL Statement as the FROM clause of another query, then sort on the alias name at that level ... but I don't recommend that for this case.

>> I do not want to copy the formula that creates Expr1 into the ORDER BY Clause. <<

Thats fine if your don't want to copy it ... but the engine will use the expression anyway and thus slow down your View.
 

MSAccessRookie

AWF VIP
Local time
Today, 07:23
Joined
May 2, 2008
Messages
3,428
>> Expr1 is more than just the concatenation of L_Name and F_Name so the sort willnot be correct <<

Have you tried it? ... The sort key indicated is a sortof a concatenation of L_Name and F_Name, but that is exactly what your expression does, with the execption of the ", " .... With the L_Name, F_Name ORDER BY clause your data will sort on L_Name THEN F_Name so your sort keys (using my sample data) .... would be ...

Spaulding
Brent
SpauldingBrent

Which will yeild and order of ...

Brent
Spaulding
SpauldingBrent

If you use the result of Expr1, your sort keys will be ...

Spaulding
Brent
Spaudling, Brent

Which will yeild and order of ...

Brent
Spaulding
Spaudling, Brent


Which is the same order ... In addition, it is my best guess (almost virtually certain) that SQL Server will your expression to sort on when it develops its execution plan, the "1", I *believe* is basically a short cut for developers, but the engine will expand it all out upon execution! .... PLUS, if you create an INDEX on L_Name and F_Name, and sort as I have shown, your View will sort and return records MUCH faster than sorting on the expression.

>> Expr1 is not valid in the Order By Clause for some reason <<

Aliases can not be used to sort on ... if you wishes, you could use your UNSORTED SQL Statement as the FROM clause of another query, then sort on the alias name at that level ... but I don't recommend that for this case.

>> I do not want to copy the formula that creates Expr1 into the ORDER BY Clause. <<

Thats fine if your don't want to copy it ... but the engine will use the expression anyway and thus slow down your View.

I believe that we have tried doing a compound key before, but I am always willing to try it again.

Thanks for the clarification regarding aliases.

I was unaware that the expression formula was used. I always thought the the data was selected into t temporary table and the contents of that table were used, thus the ability to Identify the Column numbers.

I will make the proposed adjustments, and try it again soon.
 

SQL_Hell

SQL Server DBA
Local time
Today, 11:23
Joined
Dec 4, 2003
Messages
1,360
>> Everything that I have read indicates that order by is not allowed as part of an SQL Server View. <<

What? ... where did you read that? ... You can definatly SORT a VIEW!!!! ....

Yep indeed you can
 

MSAccessRookie

AWF VIP
Local time
Today, 07:23
Joined
May 2, 2008
Messages
3,428
Maybe try this for your SQL statement of the View ...

Code:
SELECT TOP 100 Percent
(CASE
    WHEN L_Name IS NULL THEN
        ISNULL(F_Name,'')
    ELSE L_Name + ISNULL((', ' + F_Name),'')
 END),'') AS Expr1,
Person_ID,
DateAdded,
Title,
F_Name,
M_Initial,
L_Name,
email,
Notes,
ISNULL(Title + ' ','')+ISNULL(F_Name + ' ','')+ISNULL(L_Name,'') AS FormalName,
ISNULL(F_Name + ' ', Title + ' ') + ISNULL(L_Name,'') AS SemiFormalName,
CommonName,
SSMA_TimeStamp
FROM dbo.tblPeople 
ORDER BY L_Name, F_Name

Please do take note that all this SQL and any VBA I may post should be considered *AIR CODE* ... so .... no warrenties expressed or implied!!! :)

I tried this as suggested, and the code works, but the sort order is incorrect. In the current System (Sorting by Expr1), Null records appear on top to allow the users to modify any records that were entered improperly.

This code is acting very strangely. On the Server Side (using the SSMS Tools, It sorts according to the ORDER BY clause, and the order is very close (but not exactly the same) to the original. I do believe that I could convince the users to accept the slight differences.

HOWEVER, On the Access Side, it does not appear to sort at all. The records are presented sorted by the Person_ID (Which is the PK for the table) instead of by the ORDER BY statement. The following code is used on the Access Side. It looks like the sort on hte server did not work after all.
Code:
SELECT ViewPeopleAlphabetic.Expr1, 
    ViewPeopleAlphabetic.Person_ID, ViewPeopleAlphabetic.DateAdded,
    ViewPeopleAlphabetic.Title, ViewPeopleAlphabetic.F_Name,
    ViewPeopleAlphabetic.M_Initial, ViewPeopleAlphabetic.L_Name, 
    ViewPeopleAlphabetic.email, ViewPeopleAlphabetic.Notes, 
    ViewPeopleAlphabetic.FormalName, 
    ViewPeopleAlphabetic.SemiFormalName, 
    ViewPeopleAlphabetic.CommonName
FROM ViewPeopleAlphabetic;
 

datAdrenaline

AWF VIP
Local time
Today, 06:23
Joined
Jun 23, 2008
Messages
697
>> and the order is very close (but not exactly the same) <<

Did you check your data for Zero Length Strings? ... Nulls will bubble up to the top with the sort order specified. But, it may be important to note that ZLS's come AFTER Nulls in the sort order.... So ... if you have a F_Name and L_Name that are both ZLS's they will come AFTER a record with F_Name and L_Name set to Null.

>> HOWEVER, On the Access Side, it does not appear to sort at all. The records are presented sorted by the Person_ID (Which is the PK for the table) instead of by the ORDER BY statement. <<

How are you displaying the data? in a Form or in the Datasheet view of the Query Object? ... or a Datasheet view of the linked table that is bound to this view? ... Either way, each one of those objects has a property named "OrderBy" that, if set to something, will be applied AFTER the records are retreived ...

Please check the properties of all those objects to ensure there is nothing set in the OrderBy PROPERTY ...

TableDefs: View in table design view (You will get a message telling you some properties can't be modified, click Yes to open to the design view anyway), when in design view, expose the Table properties (in A2003... View > Properties), you will see the OrderBy propery ... ensure its cleared. While you are in the linked table properties, check to see if there are any Indexes on it ... if so, let me know what they are, and if any are considered the primary key index.

QueryDefs: View the query in design view. Click the grey space where are the source tables are shown, then view the properties of the Query Object by selecting View > Properties ... ensure the OrderBy propery is cleared.

Forms: View the form in design view. Click on the grey space OUTSIDE of any section (ie: just beyond the form width) to ensure the Form is the object selected. Then view the properties of the form (View > Properties). The OrderBy property can be found on the "All" tab as well as the "Data" tab. Again ... ensure that this property is cleared ...
 

MSAccessRookie

AWF VIP
Local time
Today, 07:23
Joined
May 2, 2008
Messages
3,428
>> and the order is very close (but not exactly the same) <<

Did you check your data for Zero Length Strings? ... Nulls will bubble up to the top with the sort order specified. But, it may be important to note that ZLS's come AFTER Nulls in the sort order.... So ... if you have a F_Name and L_Name that are both ZLS's they will come AFTER a record with F_Name and L_Name set to Null.

That may indeed be the case, but the actual result appears to be moot because Access seems to ignore the ORDER BY statement.

>> HOWEVER, On the Access Side, it does not appear to sort at all. The records are presented sorted by the Person_ID (Which is the PK for the table) instead of by the ORDER BY statement. <<

How are you displaying the data? in a Form or in the Datasheet view of the Query Object? ... or a Datasheet view of the linked table that is bound to this view? ... Either way, each one of those objects has a property named "OrderBy" that, if set to something, will be applied AFTER the records are retreived ...

For the purpose of verification only, I am using a datasheet view. The application uses the query as the source behind a combo box that allows the users to select a customer name. The dropdown list displayed by the combo is also out of order in the same way that the datasheet is.

Please check the properties of all those objects to ensure there is nothing set in the OrderBy PROPERTY ...

TableDefs: View in table design view (You will get a message telling you some properties can't be modified, click Yes to open to the design view anyway), when in design view, expose the Table properties (in A2003... View > Properties), you will see the OrderBy propery ... ensure its cleared. While you are in the linked table properties, check to see if there are any Indexes on it ... if so, let me know what they are, and if any are considered the primary key index.

QueryDefs: View the query in design view. Click the grey space where are the source tables are shown, then view the properties of the Query Object by selecting View > Properties ... ensure the OrderBy propery is cleared.

Forms: View the form in design view. Click on the grey space OUTSIDE of any section (ie: just beyond the form width) to ensure the Form is the object selected. Then view the properties of the form (View > Properties). The OrderBy property can be found on the "All" tab as well as the "Data" tab. Again ... ensure that this property is cleared ...

  1. I found an OrderBy Property in the Table tblPeople (ORDER BY tblpeople.Person_ID), and removed it.
  2. I did not see any OrderBy Properties in any of the SQL Server Views.
  3. I will be checking the remaining queries this afternoon after lunch.
  4. We will be doing more testing this afternoon after I have finished my search.
You may be on to something with the OrderBy Property search (see above and please feel free to comment if you wish). I will report back after initial testing is completed, although I am still skeptical that any of the changes described above will accomplish what I need.
 

Users who are viewing this thread

Top Bottom