Thanks for the warning, Uday. I think it should be ok in this case as
the dataset is not large and the db is relatively small :-)
-----Original Message-----
From: Uday Shivamurthy
[mailto:mssqldba-ezmlmshield-x58672934.[Email address protected]
Sent: 30 October 2005 09:09
To: LazyDBA Discussion
Subject: RE: SQL Server/Access view processing
A small spanner in the works for you Carol.
I had to spend weeks literally undoing all this "nested views" stuff on
one of the busiest OLTP databases. The load these things can impose on
busy dbs is not funny.
Regards,
Uday
.....................................
Direct: +61-2-98059739
Mobile: +61-401 705907
Email: [Email address protected]
-----Original Message-----
From: Carol Green
[mailto:mssqldba-ezmlmshield-x39537714.[Email address protected]
Sent: Thursday, 27 October 2005 5:38 PM
To: LazyDBA Discussion
Subject: RE: SQL Server/Access view processing
Thanks for your suggestions.
In the end I got round the problem by using nested views - one to do the
aggregate and one to add the column with the subquery. This ran fine.
Carol.
-----Original Message-----
From: Jay Grubb
[mailto:mssqldba-ezmlmshield-x98339255.[Email address protected]
Sent: 26 October 2005 19:19
To: LazyDBA Discussion
Subject: RE: SQL Server/Access view processing
You're running into a few things here:
1) For access to update, it must have a Unique Key to map the data to
the underlying tables. This is also the case for SQL Server views. The
issue is that you must have the same Key in both. Unless the 4 columns
are unique for SQLServer, you'll have problems.
2) Access doesn't process SQL Server views. If you make an Access
Query, it would do the processing locally.
3) #Deleted values happen when Access can't make a good key value
mapping, the resultant key is not unique, or if the datatype is mapped
incorrectly. Access does not have the exact same datatypes as SQL
Server, and sometimes it just guesses, and does it incorrectly.
Jay Grubb
Technical Consultant
OpenLink Software
Web: http://www.openlinksw.com:
Product Weblogs:
Virtuoso: http://www.openlinksw.com/weblogs/virtuoso
UDA: http://www.openlinksw.com/weblogs/uda
Universal Data Access & Virtual Database Technology Providers
-----Original Message-----
From: Carol Green
[mailto:mssqldba-ezmlmshield-x43469141.[Email address protected]
Sent: Wednesday, October 26, 2005 10:48 AM
To: LazyDBA Discussion
Subject: SQL Server/Access view processing
Hello All,
I recently created an aggregate view in SQL Server which returned around
2000 records in 4 columns. In Access I created a linked table which
linked to the view and made a combination of all 4 columns the unique
record identifier. Fine.
I then changed the value of columnC in the view so that it was obtained
via a subquery. However, this made Access run like a DOG (a slow,
arthritic one) and some of the lines just said #Deleted instead of the
values I could see when I opened view in SQL Server.
I tried recreating the linked table but this had no effect. Taking out
the subquery made the table open ok again.
The view runs instantaneously in SQL Server (with the subquery), so my
question is this:
Where does the processing of the view take place - on the server or the
client pc?
Also, why do some of the rows show as #Deleted when they are unique and
clearly there in SQL Server?
Yours confusedly,
Carol.
This email and any files transmitted within it are private and
confidential.
If you are not the intended recipient, this email and any attachments
within it have been transmitted to you in error.
You are prohibited from using, copying, distributing or otherwise using
the information contained within this email.
Nothing in this email message amounts to a contractual or legal
commitment on the part of Optilan unless confirmed by a communication
signed on behalf of the company.
Any email cannot be guaranteed to be secure, error free or free from
viruses. Although every possible care is taken by Optilan, Optilan does
not accept any liability whatsoever for any loss or damage which may be
caused as a result of the transmission of this message by email.
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY Website :
http://www.LazyDBA.com To unsubscribe:
http://www.lazydba.com/unsubscribe.html
--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.361 / Virus Database: 267.12.5/149 - Release Date:
10/25/2005
--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.361 / Virus Database: 267.12.5/149 - Release Date:
10/25/2005
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.361 / Virus Database: 267.12.5/149 - Release Date:
10/25/2005
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY Website :
http://www.LazyDBA.com To unsubscribe:
http://www.lazydba.com/unsubscribe.html
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY Website :
http://www.LazyDBA.com To unsubscribe:
http://www.lazydba.com/unsubscribe.html
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY Website :
http://www.LazyDBA.com To unsubscribe:
http://www.lazydba.com/unsubscribe.html
MS Sql Server LazyDBA home page