Is the behavior of this UPDATE SQL expected or a Bug?

Hi All,
I am using SQLServer 2000 with ServicePack 3a. Can any one tell me if the
following Update SQLis a bug or expected behavior?
CREATE TABLE [Table1] (
[col1] [int] NULL ,
[col2] [int] NULL
)
GO
CREATE TABLE [Table2] (
[col1] [int] NULL ,
[col3] [int] NULL
)
GO
insert into table1 (col1, col2) values (1,null)
insert into table1 (col1, col2) values (2,null)
insert into table2 (col1, col3) values (1,11)
insert into table2 (col1, col3) values (1,12)
insert into table2 (col1, col3) values (2,22)
go
-- Here is the UPDATE SQL. I am trying to update col2 of Table1 with
-- col3 of Table2
update table1
set col2=b.col3
from table1 a, table2 b
where a.col1=b.col1
I was expecting that the above SQL should fail because in this JOIN between
two tables there are two rows fetched from table2 for first row in table1
But against to my expectations that SQL was successful and got message
(2 row(s) affected)
And first row in table1, it has updated col2 with value 12
Am I making sense?
Thanks in advance,
Vinod"VM" <VM@.discussions.microsoft.com> wrote in message
news:4F375926-71A9-4CCD-9119-7D336F7809A4@.microsoft.com...
> Hi All,
> I am using SQLServer 2000 with ServicePack 3a. Can any one tell me if the
> following Update SQLis a bug or expected behavior?
> CREATE TABLE [Table1] (
> [col1] [int] NULL ,
> [col2] [int] NULL
> )
> GO
> CREATE TABLE [Table2] (
> [col1] [int] NULL ,
> [col3] [int] NULL
> )
> GO
> insert into table1 (col1, col2) values (1,null)
> insert into table1 (col1, col2) values (2,null)
> insert into table2 (col1, col3) values (1,11)
> insert into table2 (col1, col3) values (1,12)
> insert into table2 (col1, col3) values (2,22)
> go
> -- Here is the UPDATE SQL. I am trying to update col2 of Table1 with
> -- col3 of Table2
> update table1
> set col2=b.col3
> from table1 a, table2 b
> where a.col1=b.col1
>
> I was expecting that the above SQL should fail because in this JOIN
> between
> two tables there are two rows fetched from table2 for first row in table1
> But against to my expectations that SQL was successful and got message
> (2 row(s) affected)
>
> And first row in table1, it has updated col2 with value 12
> Am I making sense?
> Thanks in advance,
> Vinod
>
You are absolutely right to be concerned. Unfortunately this is the expected
behaviour. Books Online:
"The results of an UPDATE statement are undefined if the statement includes
a FROM clause that is not specified in such a way that only one value is
available for each column occurrence that is updated, that is if the UPDATE
statement is not deterministic."
So you get random results by design! If you want to use the UPDATE FROM
syntax then be very sure your join returns unique rows. If in doubt you may
prefer to use the ANSI standard syntax, which does fail safe:
UPDATE table1
SET col2 =
(SELECT col3
FROM table2
WHERE col1 = table1.col1);
Result:
Server: Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery
follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Vinod,
The first thing to understand about this situation is that the ANSI
standard does not allow a FROM clause in an UPDATE command. The
reason it does not is the ambiguity that you are asking about - if
there are two matching rows, which one provides the value?
The answer is that when using a FROM clause in an UPDATE, and joining
to a table with multiple rows matching a single row being updated, you
can not predict which row will contribute the value that ends up in
the matching row.
I have not tested in recent years, but at least in release 6.5 and
beyond there would have been TWO rows in the log, one for each match,
even though the "row(s) affected" would only count the row updated
once. That used to be - may still be - a great way to make the log
get really big very quickly; update OrderMaster by joining to
OrderItem, with an average of ten items per master, and log ten
updates for each row.
Hope that helps.
Roy
On Wed, 15 Feb 2006 15:55:27 -0800, "VM"
<VM@.discussions.microsoft.com> wrote:

>Hi All,
>I am using SQLServer 2000 with ServicePack 3a. Can any one tell me if the
>following Update SQLis a bug or expected behavior?
>CREATE TABLE [Table1] (
> [col1] [int] NULL ,
> [col2] [int] NULL
> )
>GO
>CREATE TABLE [Table2] (
> [col1] [int] NULL ,
> [col3] [int] NULL
> )
>GO
>insert into table1 (col1, col2) values (1,null)
>insert into table1 (col1, col2) values (2,null)
>insert into table2 (col1, col3) values (1,11)
>insert into table2 (col1, col3) values (1,12)
>insert into table2 (col1, col3) values (2,22)
>go
>-- Here is the UPDATE SQL. I am trying to update col2 of Table1 with
>-- col3 of Table2
>update table1
>set col2=b.col3
>from table1 a, table2 b
>where a.col1=b.col1
>
>I was expecting that the above SQL should fail because in this JOIN between
>two tables there are two rows fetched from table2 for first row in table1
>But against to my expectations that SQL was successful and got message
>(2 row(s) affected)
>
>And first row in table1, it has updated col2 with value 12
>Am I making sense?
>Thanks in advance,
>Vinod
>|||David and Roy,
Thankyou so much for your replies. Now I have good picture about the FROM
clause in UPDATE statement. I will be very careful from now.
Again, Thanks guys!!
Vinod
"Roy Harvey" wrote:

> Vinod,
> The first thing to understand about this situation is that the ANSI
> standard does not allow a FROM clause in an UPDATE command. The
> reason it does not is the ambiguity that you are asking about - if
> there are two matching rows, which one provides the value?
> The answer is that when using a FROM clause in an UPDATE, and joining
> to a table with multiple rows matching a single row being updated, you
> can not predict which row will contribute the value that ends up in
> the matching row.
> I have not tested in recent years, but at least in release 6.5 and
> beyond there would have been TWO rows in the log, one for each match,
> even though the "row(s) affected" would only count the row updated
> once. That used to be - may still be - a great way to make the log
> get really big very quickly; update OrderMaster by joining to
> OrderItem, with an average of ten items per master, and log ten
> updates for each row.
> Hope that helps.
> Roy
>
> On Wed, 15 Feb 2006 15:55:27 -0800, "VM"
> <VM@.discussions.microsoft.com> wrote:
>
>

Is SqlServer Express good for desktop applications?

Do you believe that SqlServer Express can work absolutely without any administration?
That is to say: my clients are people without any technical knowledge and they usually communicate never with me.
Is SSE the correct database for this status, or should I use a desktop database like VFP or Access?

Once it's set up, it'll keep working indefinitely. You'll have to do most of the management in your software so the user can copy the database, for example.

The main limition with SQL Server Express is that you have to be sure to use an instance name that another applicaiton is unlikely to use, otherwise you'll have conflicts.

The main advantage of SQL Server Express versus something like Access is that you can easily move to the full-blown SQL Server Standard with virtually no effort.

-Ryan

|||

It's a better approach to determine the needs of your application, both now and in the future, and then choose the right database engine to meet those needs. One alternative that hasn't been mentioned yet is SQL Compact Edition, which is similar to Access/Jet in that it is a file based, in process database engine. (Compared to SQL Express which is a service based engine.)

There is a paper at http://www.microsoft.com/sql/editions/compact/sscecomparison.mspx that attempts to clarify the differences between these platforms and why you would use one of the other. I generally think that you should start you planning at SQLce, since it offers a very light weight data engine that is easy to install and mange, and then work your way up the ladder if you discover application requriements that would prevent you from using it.

There is a forum dedicated to SQLce here on MSDN if you have questions about that database.

Mike

Is SqlServer Agent running?

Hi,

Can anyone give me some clues as to how I programmatically determine if Sql Server Agent is running. I'm using Sql 2005 and c#.

I have found the JobServer property in the SqlServer object but this still doesn't tell me is the service is running!!

Thanks for your help

Graham

Take a look at Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer. This class has a ServiceCollection property which represents all SQL Server services on a target machine. Find your service and check it's state.

WBR, Evergray -- Words mean nothing...|||

this is really a useful class. but do u know how to obtain the machine name from SQL server name?

cos in my application, the user just provoide server name. how can I get the machine name to configure the services on the machine?

|||

Server name is usually machine name (default instance) or machine_name\instance_name for named instances of SQL Server, so it's not a problem.

But anyway you can determine machine name using host_name() system function, if you're already connected to server.

WBR, Evergray -- Words mean nothing...|||

yup. thanks for the reply.

I found that I can use SMO to find it machine name as well.

|||how do you determine the machine name using objects in the SMO namespace?

Is SqlServer Agent running?

Hi,

Can anyone give me some clues as to how I programmatically determine if Sql Server Agent is running. I'm using Sql 2005 and c#.

I have found the JobServer property in the SqlServer object but this still doesn't tell me is the service is running!!

Thanks for your help

Graham

Take a look at Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer. This class has a ServiceCollection property which represents all SQL Server services on a target machine. Find your service and check it's state.

WBR, Evergray

--

Words mean nothing...|||

this is really a useful class. but do u know how to obtain the machine name from SQL server name?

cos in my application, the user just provoide server name. how can I get the machine name to configure the services on the machine?

|||

Server name is usually machine name (default instance) or machine_name\instance_name for named instances of SQL Server, so it's not a problem.

But anyway you can determine machine name using host_name() system function, if you're already connected to server.

WBR, Evergray

--

Words mean nothing...|||

yup. thanks for the reply.

I found that I can use SMO to find it machine name as well.

|||how do you determine the machine name using objects in the SMO namespace?

Is SqlServer Agent running?

Hi,

Can anyone give me some clues as to how I programmatically determine if Sql Server Agent is running. I'm using Sql 2005 and c#.

I have found the JobServer property in the SqlServer object but this still doesn't tell me is the service is running!!

Thanks for your help

Graham

Take a look at Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer. This class has a ServiceCollection property which represents all SQL Server services on a target machine. Find your service and check it's state.

WBR, Evergray -- Words mean nothing...|||

this is really a useful class. but do u know how to obtain the machine name from SQL server name?

cos in my application, the user just provoide server name. how can I get the machine name to configure the services on the machine?

|||

Server name is usually machine name (default instance) or machine_name\instance_name for named instances of SQL Server, so it's not a problem.

But anyway you can determine machine name using host_name() system function, if you're already connected to server.

WBR, Evergray -- Words mean nothing...|||

yup. thanks for the reply.

I found that I can use SMO to find it machine name as well.

|||how do you determine the machine name using objects in the SMO namespace?

Is Sql-Server 2005 Object Oriented DBMS

Greetings,
I would like to konw if Sqlserver 2005 supports Geographic Information
System (GIS) data, i.e., supports spatial data, like Oracle?
MTIA,
Grawshagrawsha2000@.yahoo.com wrote:
> Greetings,
> I would like to konw if Sqlserver 2005 supports Geographic Information
> System (GIS) data, i.e., supports spatial data, like Oracle?
>
> MTIA,
> Grawsha
SQL Server doesn't have specific features designed for GIS but it does
support user-defined datatypes and CLR (.NET) code in the database. You
can build "complex" types (points and vectors maybe) from .NET classes.
That may cover some of the features you have in mind.
As for OODBMS, it would help if you could be more specific. "OODBMS" is
commonly used as a marketing category or an imprecise term for any of
various different features and techniques. If you are looking for
encapsulation, inheritence and subclassing then you can achieve that
through .NET code either inside or outside the database server. If you
need an object store then you can also serialize .NET objects to a SQL
Server database.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||>> I would like to konw if Sqlserver 2005 supports Geographic Information
Searching MSDN bought up the following, not sure if helps:
http://msdn.microsoft.com/library/e...lValFuncSQL.asp
Anith

is sqlserver 2000 compatible with windows 2003 ?

Hi !

when installing sqlserver 2000 on a Windows 2003 server it explicitly
tells "server not compatible with Windows 2003" during install, but it
can carry on.
After we applied serfice pack 3a then the db server seems to run
normally, but does it risk to behave randomly afterwards ?
Is sqlserver 2000 standard edition compatible with Windows 2003 server
?
Or does it exist a specific sqlserver edition for Win2003 ?

thanks !
Patrice"Patrox" <patrice .castet @.in- fusio. NOOO.SPAAAAM.com> wrote in message
news:a8c9g0dk9utjs0iraab973ag5p1obpuunm@.4ax.com...
> Hi !
> when installing sqlserver 2000 on a Windows 2003 server it explicitly
> tells "server not compatible with Windows 2003" during install, but it
> can carry on.
> After we applied serfice pack 3a then the db server seems to run
> normally, but does it risk to behave randomly afterwards ?
> Is sqlserver 2000 standard edition compatible with Windows 2003 server
> ?
> Or does it exist a specific sqlserver edition for Win2003 ?
> thanks !
> Patrice
You may find these articles interesting.
http://www.winnetmag.com/Article/Ar...0428/40428.html
http://support.microsoft.com/defaul...kb;en-us;329329

The important point is [from MSKB article]:
"To work around this behavior, install SQL Server 2000 SP3 or later
immediately after you install SQL Server 2000."

HTH,

Rowland.|||On Mon, 26 Jul 2004 09:24:10 +0200, Patrox <patrice .castet @.in-
fusio. NOOO.SPAAAAM.com> wrote:

>Hi !
>when installing sqlserver 2000 on a Windows 2003 server it explicitly
>tells "server not compatible with Windows 2003" during install, but it
>can carry on.
>After we applied serfice pack 3a then the db server seems to run
>normally, but does it risk to behave randomly afterwards ?
>Is sqlserver 2000 standard edition compatible with Windows 2003 server
>?
>Or does it exist a specific sqlserver edition for Win2003 ?
>thanks !
>Patrice
I have SQL 2k Enterprise running on Windows 2003. I've not had any
trouble with it, and I assume the standard version would work as well.
I don't remember getting an error message when installing, though I
may just not remember it.

Is SQL Server SQL99 Compliant

Hi,

My question is if MS SQL Server 2000 Standard version is SQL99
Compliant? How about MS SQL Server 2000 Enterprise version, MS SQL
Server 6.5/7.0?

Thanks.

Hai-Chu(seapearl1023@.ms65.url.com.tw) writes:
> My question is if MS SQL Server 2000 Standard version is SQL99
> Compliant? How about MS SQL Server 2000 Enterprise version, MS SQL
> Server 6.5/7.0?

I don't think SQL 2000 complies even to the entry-level of SQL99.
SQL7 and SQL 6.5 that predate SQL-99 (I assume, since they came out
before 1999!), even less do so.

There is no difference between Enterprise and Standard Edition, since
the differences between different editions are not in language features.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||SQL Server 2000 does not conform to the ANSI SQL:1999 standard. From
what I understand, it also does not conform completely to the ANSI
standard previous to that, ANSI SQL-92.|||Gary wrote:

> SQL Server 2000 does not conform to the ANSI SQL:1999 standard. From
> what I understand, it also does not conform completely to the ANSI
> standard previous to that, ANSI SQL-92.

SQL Server 2000 does not conform and neither does anyone else's product
if you mean full compliance. All the talk about compliance is
meaningless because the marketplace just doesn't care.
--
Daniel A. Morgan
University of Washington
damorgan@.x.washington.edu
(replace 'x' with 'u' to respond)

--== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet News==--
http://www.newsfeeds.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
--= East/West-Coast Server Farms - Total Privacy via Encryption =--|||seapearl1023@.ms65.url.com.tw wrote:
> Hi,
> My question is if MS SQL Server 2000 Standard version is SQL99
> Compliant? How about MS SQL Server 2000 Enterprise version, MS SQL
> Server 6.5/7.0?
> Thanks.
> Hai-Chu
Unlike SQL-92 there is no test certifying compliance of any product to
SQL-99 or later.
So the best you will get from any DBMS vendor is an unverifiable opinion.

Cheers
Serge|||DA Morgan (damorgan@.x.washington.edu) writes:
> Gary wrote:
>> SQL Server 2000 does not conform to the ANSI SQL:1999 standard. From
>> what I understand, it also does not conform completely to the ANSI
>> standard previous to that, ANSI SQL-92.
> SQL Server 2000 does not conform and neither does anyone else's product
> if you mean full compliance.

I believe that SQL 2000 conforms to the entry-level of ANSI-92, but I
could be wrong, because as you say:

> All the talk about compliance is meaningless because the marketplace
> just doesn't care.

I wholeheartedly agree.

Even if the same syntax would run on, say, SQL Server and Oracle, the code
may still prove not to port straight away between the two engines, because
of performance issues.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Is osql standalone?

Hello all,
I want to know if I can start the osql utility from a computer without SQL
Server, MSDE, etc. Is this file standalone?
Thanks,
Sorin
You must have installed sql connectivity, and Osql, but the server stuff is
not required.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Sorin R" <Sorin R@.discussions.microsoft.com> wrote in message
news:0C835563-3A84-4F9B-ABA6-B69131FD1C89@.microsoft.com...
> Hello all,
> I want to know if I can start the osql utility from a computer without SQL
> Server, MSDE, etc. Is this file standalone?
> Thanks,
> Sorin
|||ok...thanks a lot
Sorin
"Wayne Snyder" wrote:

> You must have installed sql connectivity, and Osql, but the server stuff is
> not required.
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Sorin R" <Sorin R@.discussions.microsoft.com> wrote in message
> news:0C835563-3A84-4F9B-ABA6-B69131FD1C89@.microsoft.com...
>
>

Is osql standalone?

Hello all,
I want to know if I can start the osql utility from a computer without SQL
Server, MSDE, etc. Is this file standalone?
Thanks,
SorinYou must have installed sql connectivity, and Osql, but the server stuff is
not required.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Sorin R" <Sorin R@.discussions.microsoft.com> wrote in message
news:0C835563-3A84-4F9B-ABA6-B69131FD1C89@.microsoft.com...
> Hello all,
> I want to know if I can start the osql utility from a computer without SQL
> Server, MSDE, etc. Is this file standalone?
> Thanks,
> Sorin|||ok...thanks a lot
Sorin
"Wayne Snyder" wrote:

> You must have installed sql connectivity, and Osql, but the server stuff i
s
> not required.
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Sorin R" <Sorin R@.discussions.microsoft.com> wrote in message
> news:0C835563-3A84-4F9B-ABA6-B69131FD1C89@.microsoft.com...
>
>

Is ODBC right for me?

I am a forum newbie - appreciate your patience.
We are embarking on a new adventure to scale up an Access DB up to SQL
Server. We are not going to use the upsizing wizardry, but have chosen to
build native SQL tables instead.
The question is which route to go with the Front-End?
- Access 2003: SQL tables linked via ODBC
- Access Data Project(ADP)
- Access 2007: SQL tables linked (MDB or ACCDB?)
We were prepared to move ahead with creating an Access Data Project using
Access 2003, but then I read the following on microsoft.com ...
" Access creates front-end applications that leverage SQL Server as a
backend data source. Access forms and reports can be optimized as efficiently
as Visual Basic front-end for SQL Server. Office Access 2007 offers two ways
to connect to SQL Server data: linking to SQL Server and Access Data Projects
(ADPs).
The preferred way to connect to SQL Server is MDB file format or ACCDB file
format. This enables you to use the full flexibility of local tables and
local queries, while leveraging the full power of SQL Server. In addition,
MDB and ACCDB files link to multiple SQL Servers and a wide variety of other
data sources. Office Access 2007 contains many new features available in both
MDB and ACCDB file formats, but only a subset of those features are available
in ADPs. "
This seems to state that best practice is to use linked tables. Is this true
in the "real world"? Isn't using the layers of ODBC going to make for a
slower app? Is ADP a technology that may not be supported by MS in the near
future?
Where would I find info to learn how to optimize Access/ODBC to work as well
as a VB front-end as stated in the quote above?
Your thoughts would be appreciated!
Hi Dirn,
I'm not an Access guru but one thing I found when working with an ADP
against SQL 2005 is that the database must be in SQL 2000 compatible format
since an Access ADP isn't compatible with any schema but DBO.
You can use SQL Express if the amount of your data is within its size
limitations.
Cindy Winegarden MCSD, Microsoft Most Valuable Professional
cindy@.cindywinegarden.com
"DIRN" <DIRN@.discussions.microsoft.com> wrote in message
news:BB082997-C04F-4C65-BF6F-496C53A07E9D@.microsoft.com...

> ... The question is which route to go with the Front-End?
> - Access 2003: SQL tables linked via ODBC
> - Access Data Project(ADP)
> - Access 2007: SQL tables linked (MDB or ACCDB?) ...
|||Cindy & Van T.
Thanks for taking time to respond to my question. I still have three
lindering wunderabouts:
- Which approach (ADP vs. Linked Tables) to a Access FE / SQL Server BE app
does MS consider best practice with the current revs of their Access & SQL
products?
- If, linked tables - how to choose between MDB and ACCDB?
- What did they really mean in the quote above about making an Access
FE as effiecient as a VB FE?
If you have any additional info or know of a resource who might be of
assistance please respond further.
Your help is so incredibly appreciated.
Best Regards,
David Hogan
"Van T. Dinh" wrote:

> ADP is still supported in Access 2007 which means that it will be supported
> to at least end of 2016 (5 years mainstream support + 5 years extended
> support).
> I am sure there are lots of ADPs in the real-world but the majority of
> Access databases still use MDB format.
> In my experience, there is no problems using ODBC provided you follow a few
> simple techniques on reducing the amount of data being pulled through the
> network (this applies to all Client / Server database applications, anyway).
> There are a number of articles in the Microsoft KB, e.g:
> http://support.microsoft.com/kb/208858
> and
> http://support.microsoft.com/kb/209551
> --
> HTH
> Van T. Dinh
> MVP (Access)
>
> "DIRN" <DIRN@.discussions.microsoft.com> wrote in message
> news:BB082997-C04F-4C65-BF6F-496C53A07E9D@.microsoft.com...
>
>
|||Van
Thanks again for the wisdom. Access "Vision" document was very helpful. I
will begin to start looking for material on how to code an application
optimzed for a SQL server BE. I think your thoughts on minimizing network
traffic are right on.
I appreciate your generous assistance!
David
"Van T. Dinh" wrote:

> * Definitely Linked Tables from what I have heard for some time now. When
> Access 2000 & Access 2002 came out, Microsoft was pushing ADP but it is
> definitely not the case with Access 2007. In fact, I think the new ACCDB
> format utilises the new JET engine (and JET engine was supposed to be in
> maintenance mode only!)
> See http://www.tinyurl.com/y2yjzn
> and have a look at the first paragraph of page 10 from the Microsoft
> document above and I am sure you can infer what Microsoft impplies.
> * I haven't done much testing with Access 2007 + new ACCDB format but AFAIK,
> you need A2007 to use ACCDB. If you have a uniform enviroment where
> everyone has A2007, ACCDB will probably be better (since the Microsoft
> Access / JET engineers have exclusive control of the engine which enable
> them to tune it to suit Access while the JET 4 engine is shared in the OS so
> they can't change it easily). If you have a mixed environment, I think it
> is safer to stick to the MDB / MDE format for the moment.
> * I am confused about "VB" nowadays since it could mean either the old VB6
> or VB.Net or VB2005 but don't you have to use some sort of interface such as
> OLEDB to access data regardles of the BE engine? In my databases, I used
> ODBC-linked Tables (for Forms , Reports, etc ...) but in code, I use mostly
> ADO / OLEDB for SQL Server to access and manipulate data and the speed is
> quite fast.
> I think the major bottleneck in most database application is network
> transmission bandwidth, not the database engine or the interface to the
> database (I once added a million simple records one at a time to a JET Table
> using DAO Recordset code and it took only about 3-4 seconds). I concentrate
> on reducing the need to transfer data across the network to a minimum, e.g.
> getting the SQL Server to do most processing and only pass back the
> processed result to Access FE.
> --
> HTH
> Van T. Dinh
> MVP (Access)
>
> "DIRN" <DIRN@.discussions.microsoft.com> wrote in message
> news:04CD459E-1686-41C1-90AF-A773661CF283@.microsoft.com...
>
>

Is ODBC right for me?

I am a forum newbie - appreciate your patience.
We are embarking on a new adventure to scale up an Access DB up to SQL
Server. We are not going to use the upsizing wizardry, but have chosen to
build native SQL tables instead.
The question is which route to go with the Front-End?
- Access 2003: SQL tables linked via ODBC
- Access Data Project(ADP)
- Access 2007: SQL tables linked (MDB or ACCDB?)
We were prepared to move ahead with creating an Access Data Project using
Access 2003, but then I read the following on microsoft.com ...
" Access creates front-end applications that leverage SQL Server as a
backend data source. Access forms and reports can be optimized as efficientl
y
as Visual Basic front-end for SQL Server. Office Access 2007 offers two ways
to connect to SQL Server data: linking to SQL Server and Access Data Project
s
(ADPs).
The preferred way to connect to SQL Server is MDB file format or ACCDB file
format. This enables you to use the full flexibility of local tables and
local queries, while leveraging the full power of SQL Server. In addition,
MDB and ACCDB files link to multiple SQL Servers and a wide variety of other
data sources. Office Access 2007 contains many new features available in bot
h
MDB and ACCDB file formats, but only a subset of those features are availabl
e
in ADPs. "
This seems to state that best practice is to use linked tables. Is this true
in the "real world"? Isn't using the layers of ODBC going to make for a
slower app? Is ADP a technology that may not be supported by MS in the near
future?
Where would I find info to learn how to optimize Access/ODBC to work as well
as a VB front-end as stated in the quote above?
Your thoughts would be appreciated!Hi Dirn,
I'm not an Access guru but one thing I found when working with an ADP
against SQL 2005 is that the database must be in SQL 2000 compatible format
since an Access ADP isn't compatible with any schema but DBO.
You can use SQL Express if the amount of your data is within its size
limitations.
Cindy Winegarden MCSD, Microsoft Most Valuable Professional
cindy@.cindywinegarden.com
"DIRN" <DIRN@.discussions.microsoft.com> wrote in message
news:BB082997-C04F-4C65-BF6F-496C53A07E9D@.microsoft.com...

> ... The question is which route to go with the Front-End?
> - Access 2003: SQL tables linked via ODBC
> - Access Data Project(ADP)
> - Access 2007: SQL tables linked (MDB or ACCDB?) ...|||ADP is still supported in Access 2007 which means that it will be supported
to at least end of 2016 (5 years mainstream support + 5 years extended
support).
I am sure there are lots of ADPs in the real-world but the majority of
Access databases still use MDB format.
In my experience, there is no problems using ODBC provided you follow a few
simple techniques on reducing the amount of data being pulled through the
network (this applies to all Client / Server database applications, anyway).
There are a number of articles in the Microsoft KB, e.g:
http://support.microsoft.com/kb/208858
and
http://support.microsoft.com/kb/209551
HTH
Van T. Dinh
MVP (Access)
"DIRN" <DIRN@.discussions.microsoft.com> wrote in message
news:BB082997-C04F-4C65-BF6F-496C53A07E9D@.microsoft.com...
>I am a forum newbie - appreciate your patience.
> We are embarking on a new adventure to scale up an Access DB up to SQL
> Server. We are not going to use the upsizing wizardry, but have chosen to
> build native SQL tables instead.
> The question is which route to go with the Front-End?
> - Access 2003: SQL tables linked via ODBC
> - Access Data Project(ADP)
> - Access 2007: SQL tables linked (MDB or ACCDB?)
> We were prepared to move ahead with creating an Access Data Project using
> Access 2003, but then I read the following on microsoft.com ...
> " Access creates front-end applications that leverage SQL Server as a
> backend data source. Access forms and reports can be optimized as
> efficiently
> as Visual Basic front-end for SQL Server. Office Access 2007 offers two
> ways
> to connect to SQL Server data: linking to SQL Server and Access Data
> Projects
> (ADPs).
> The preferred way to connect to SQL Server is MDB file format or ACCDB
> file
> format. This enables you to use the full flexibility of local tables and
> local queries, while leveraging the full power of SQL Server. In addition,
> MDB and ACCDB files link to multiple SQL Servers and a wide variety of
> other
> data sources. Office Access 2007 contains many new features available in
> both
> MDB and ACCDB file formats, but only a subset of those features are
> available
> in ADPs. "
> This seems to state that best practice is to use linked tables. Is this
> true
> in the "real world"? Isn't using the layers of ODBC going to make for a
> slower app? Is ADP a technology that may not be supported by MS in the
> near
> future?
> Where would I find info to learn how to optimize Access/ODBC to work as
> well
> as a VB front-end as stated in the quote above?
> Your thoughts would be appreciated!|||Cindy & Van T.
Thanks for taking time to respond to my question. I still have three
lindering wunderabouts:
- Which approach (ADP vs. Linked Tables) to a Access FE / SQL Server BE app
does MS consider best practice with the current revs of their Access & SQL
products?
- If, linked tables - how to choose between MDB and ACCDB?
- What did they really mean in the quote above about making an Access
FE as effiecient as a VB FE?
If you have any additional info or know of a resource who might be of
assistance please respond further.
Your help is so incredibly appreciated.
Best Regards,
David Hogan
"Van T. Dinh" wrote:

> ADP is still supported in Access 2007 which means that it will be supporte
d
> to at least end of 2016 (5 years mainstream support + 5 years extended
> support).
> I am sure there are lots of ADPs in the real-world but the majority of
> Access databases still use MDB format.
> In my experience, there is no problems using ODBC provided you follow a fe
w
> simple techniques on reducing the amount of data being pulled through the
> network (this applies to all Client / Server database applications, anyway
).
> There are a number of articles in the Microsoft KB, e.g:
> http://support.microsoft.com/kb/208858
> and
> http://support.microsoft.com/kb/209551
> --
> HTH
> Van T. Dinh
> MVP (Access)
>
> "DIRN" <DIRN@.discussions.microsoft.com> wrote in message
> news:BB082997-C04F-4C65-BF6F-496C53A07E9D@.microsoft.com...
>
>|||* Definitely Linked Tables from what I have heard for some time now. When
Access 2000 & Access 2002 came out, Microsoft was pushing ADP but it is
definitely not the case with Access 2007. In fact, I think the new ACCDB
format utilises the new JET engine (and JET engine was supposed to be in
maintenance mode only!)
See http://www.tinyurl.com/y2yjzn
and have a look at the first paragraph of page 10 from the Microsoft
document above and I am sure you can infer what Microsoft impplies.
* I haven't done much testing with Access 2007 + new ACCDB format but AFAIK,
you need A2007 to use ACCDB. If you have a uniform enviroment where
everyone has A2007, ACCDB will probably be better (since the Microsoft
Access / JET engineers have exclusive control of the engine which enable
them to tune it to suit Access while the JET 4 engine is shared in the OS so
they can't change it easily). If you have a mixed environment, I think it
is safer to stick to the MDB / MDE format for the moment.
* I am confused about "VB" nowadays since it could mean either the old VB6
or VB.Net or VB2005 but don't you have to use some sort of interface such as
OLEDB to access data regardles of the BE engine? In my databases, I used
ODBC-linked Tables (for Forms , Reports, etc ...) but in code, I use mostly
ADO / OLEDB for SQL Server to access and manipulate data and the speed is
quite fast.
I think the major bottleneck in most database application is network
transmission bandwidth, not the database engine or the interface to the
database (I once added a million simple records one at a time to a JET Table
using DAO Recordset code and it took only about 3-4 seconds). I concentrate
on reducing the need to transfer data across the network to a minimum, e.g.
getting the SQL Server to do most processing and only pass back the
processed result to Access FE.
HTH
Van T. Dinh
MVP (Access)
"DIRN" <DIRN@.discussions.microsoft.com> wrote in message
news:04CD459E-1686-41C1-90AF-A773661CF283@.microsoft.com...
> Cindy & Van T.
> Thanks for taking time to respond to my question. I still have three
> lindering wunderabouts:
> - Which approach (ADP vs. Linked Tables) to a Access FE / SQL Server BE
> app
> does MS consider best practice with the current revs of their Access & SQL
> products?
> - If, linked tables - how to choose between MDB and ACCDB?
> - What did they really mean in the quote above about making an Access
> FE as effiecient as a VB FE?
> If you have any additional info or know of a resource who might be of
> assistance please respond further.
> Your help is so incredibly appreciated.
> Best Regards,
> David Hogan|||Van
Thanks again for the wisdom. Access "Vision" document was very helpful. I
will begin to start looking for material on how to code an application
optimzed for a SQL server BE. I think your thoughts on minimizing network
traffic are right on.
I appreciate your generous assistance!
David
"Van T. Dinh" wrote:

> * Definitely Linked Tables from what I have heard for some time now. When
> Access 2000 & Access 2002 came out, Microsoft was pushing ADP but it is
> definitely not the case with Access 2007. In fact, I think the new ACCDB
> format utilises the new JET engine (and JET engine was supposed to be in
> maintenance mode only!)
> See http://www.tinyurl.com/y2yjzn
> and have a look at the first paragraph of page 10 from the Microsoft
> document above and I am sure you can infer what Microsoft impplies.
> * I haven't done much testing with Access 2007 + new ACCDB format but AFAI
K,
> you need A2007 to use ACCDB. If you have a uniform enviroment where
> everyone has A2007, ACCDB will probably be better (since the Microsoft
> Access / JET engineers have exclusive control of the engine which enable
> them to tune it to suit Access while the JET 4 engine is shared in the OS
so
> they can't change it easily). If you have a mixed environment, I think it
> is safer to stick to the MDB / MDE format for the moment.
> * I am confused about "VB" nowadays since it could mean either the old VB6
> or VB.Net or VB2005 but don't you have to use some sort of interface such
as
> OLEDB to access data regardles of the BE engine? In my databases, I used
> ODBC-linked Tables (for Forms , Reports, etc ...) but in code, I use mostl
y
> ADO / OLEDB for SQL Server to access and manipulate data and the speed is
> quite fast.
> I think the major bottleneck in most database application is network
> transmission bandwidth, not the database engine or the interface to the
> database (I once added a million simple records one at a time to a JET Tab
le
> using DAO Recordset code and it took only about 3-4 seconds). I concentra
te
> on reducing the need to transfer data across the network to a minimum, e.g
.
> getting the SQL Server to do most processing and only pass back the
> processed result to Access FE.
> --
> HTH
> Van T. Dinh
> MVP (Access)
>
> "DIRN" <DIRN@.discussions.microsoft.com> wrote in message
> news:04CD459E-1686-41C1-90AF-A773661CF283@.microsoft.com...
>
>|||You're welcome ... Glad to help ...
HTH
Van T. Dinh
MVP (Access)
"DIRN" <DIRN@.discussions.microsoft.com> wrote in message
news:7444F6AD-25F7-4DB5-B9E2-FD249F9C4C40@.microsoft.com...
> Van
> Thanks again for the wisdom. Access "Vision" document was very helpful. I
> will begin to start looking for material on how to code an application
> optimzed for a SQL server BE. I think your thoughts on minimizing network
> traffic are right on.
> I appreciate your generous assistance!
> David
>

Is MSDE supported by Microsoft SQL Server 2000 driver for JDBC?

I have J2EE web application, that uses Datasources, which works fine with SQL
Server 2000 Developer Edition running on Windows 2000 Server but I am having
issues with running the same code against MSDE version of SQL Server 2000
running locally on Windows XP Professional.
Is MSDE version of SQL server supported by Microsoft SQL Server 2000 driver
forJDBC?
The release notes says:
The following versions of SQL Server will be supported for use with the SQL
Server 2000 Driver for JDBC SP2:
? SQL Server 2000 Standard and Enterprise Editions*
? SQL Server 2000 Standard and Enterprise Editions with Service Pack 1 or
higher*
? SQL Server 2000 Enterprise Edition (64-bit)*
MSDE should act the same as any other flavor of SQL 2000 with the exception
of a few well documented limitations. Likely, you have not enabled Network
protocals?
Also, the docs you reference below sound very old. SQL needs to be at SP3 or
3A. Any current download of MSDE will be at the 3A SP level.
-Andrew
"Kris" <Kris@.discussions.microsoft.com> wrote in message
news:AC61CEC6-2D96-418E-810B-E59F8B87356B@.microsoft.com...
> I have J2EE web application, that uses Datasources, which works fine with
SQL
> Server 2000 Developer Edition running on Windows 2000 Server but I am
having
> issues with running the same code against MSDE version of SQL Server 2000
> running locally on Windows XP Professional.
> Is MSDE version of SQL server supported by Microsoft SQL Server 2000
driver
> forJDBC?
> The release notes says:
> The following versions of SQL Server will be supported for use with the
SQL
> Server 2000 Driver for JDBC SP2:
> . SQL Server 2000 Standard and Enterprise Editions*
> . SQL Server 2000 Standard and Enterprise Editions with Service Pack 1 or
> higher*
> . SQL Server 2000 Enterprise Edition (64-bit)*

Is MSDE supported by Microsoft SQL Server 2000 driver for JDBC

I have J2EE web application, that uses Datasources, which works fine with SQL
Server 2000 Developer Edition running on Windows 2000 Server but I am having
issues with the same code running locally on Windows XP Professional against
MSDE version of SQL Server 2000.
Is MSDE version of SQL server supported by Microsoft SQL Server 2000 driver
forJDBC?
The release notes says:
The following versions of SQL Server will be supported for use with the SQL
Server 2000 Driver for JDBC SP2:
? SQL Server 2000 Standard and Enterprise Editions*
? SQL Server 2000 Standard and Enterprise Editions with Service Pack 1 or
higher*
? SQL Server 2000 Enterprise Edition (64-bit)*
Do you have specifics on the issues? For instance, could it be your Windows
Firewall? If it is on, it must be configured to allow traffic (locally) on
ports 1433 (TCP) and 1434 (UDP). I don't know any specifics about the JDBC
drivers, other than make sure that you have the same SQL Server SP
(preferably SP3/SP3a) and check your MDAC version against the other
machine's.
"Kris" <Kris@.discussions.microsoft.com> wrote in message
news:A3942A4E-398A-48FA-B363-A4D392E2ECBB@.microsoft.com...
>I have J2EE web application, that uses Datasources, which works fine with
>SQL
> Server 2000 Developer Edition running on Windows 2000 Server but I am
> having
> issues with the same code running locally on Windows XP Professional
> against
> MSDE version of SQL Server 2000.
> Is MSDE version of SQL server supported by Microsoft SQL Server 2000
> driver
> forJDBC?
> The release notes says:
> The following versions of SQL Server will be supported for use with the
> SQL
> Server 2000 Driver for JDBC SP2:
> . SQL Server 2000 Standard and Enterprise Editions*
> . SQL Server 2000 Standard and Enterprise Editions with Service Pack 1 or
> higher*
> . SQL Server 2000 Enterprise Edition (64-bit)*

Is MSDE supported by Microsoft SQL Server 2000 driver for JDBC

I have J2EE web application, that uses Datasources, which works fine with SQ
L
Server 2000 Developer Edition running on Windows 2000 Server but I am having
issues with the same code running locally on Windows XP Professional against
MSDE version of SQL Server 2000.
Is MSDE version of SQL server supported by Microsoft SQL Server 2000 driver
forJDBC?
The release notes says:
The following versions of SQL Server will be supported for use with the SQL
Server 2000 Driver for JDBC SP2:
? SQL Server 2000 Standard and Enterprise Editions*
? SQL Server 2000 Standard and Enterprise Editions with Service Pack 1 or
higher*
? SQL Server 2000 Enterprise Edition (64-bit)*Do you have specifics on the issues? For instance, could it be your Windows
Firewall? If it is on, it must be configured to allow traffic (locally) on
ports 1433 (TCP) and 1434 (UDP). I don't know any specifics about the JDBC
drivers, other than make sure that you have the same SQL Server SP
(preferably SP3/SP3a) and check your MDAC version against the other
machine's.
"Kris" <Kris@.discussions.microsoft.com> wrote in message
news:A3942A4E-398A-48FA-B363-A4D392E2ECBB@.microsoft.com...
>I have J2EE web application, that uses Datasources, which works fine with
>SQL
> Server 2000 Developer Edition running on Windows 2000 Server but I am
> having
> issues with the same code running locally on Windows XP Professional
> against
> MSDE version of SQL Server 2000.
> Is MSDE version of SQL server supported by Microsoft SQL Server 2000
> driver
> forJDBC?
> The release notes says:
> The following versions of SQL Server will be supported for use with the
> SQL
> Server 2000 Driver for JDBC SP2:
> . SQL Server 2000 Standard and Enterprise Editions*
> . SQL Server 2000 Standard and Enterprise Editions with Service Pack 1 or
> higher*
> . SQL Server 2000 Enterprise Edition (64-bit)*