I can't view the AdventureWorksDB database diagram even after the ALTER
AUTHORIZATION statement.
Thanks..>I can't view the AdventureWorksDB database diagram even after the ALTER
> AUTHORIZATION statement.
Typically the problem is that the database is not in 90 compatibility level.
You might try verifying the compatibility level of the database (EXEC
sp_dbcmptlevel AdventureWorks)
and if it doesn't return 90, change it (EXEC sp_dbcmptlevel AdventureWorks,
90; ).
If that doesn't solve the problem, you'll need to provide more details such
as the error message your getting.
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/pr...oads/books.mspx
"mmc" <mmc@.discussions.microsoft.com> wrote in message
news:2F41CDED-5E5E-4AFF-9DA7-81C92FC06179@.microsoft.com...
>I can't view the AdventureWorksDB database diagram even after the ALTER
> AUTHORIZATION statement.
> Thanks..|||The sp_dbcmptlevel AdventureWorks returned a "90".
The error everytime i try to open the diagram:
"The database diagram support objects can not be installed because because
this database does not have a valid owner. To continue, first use the Files
page of the Database Properties dialog box or the ALTER STATEMENT to set the
database owner to a valid login, then add add the database diagram support
objects".
I am logged in using "sa". Is "sa" valid in 2005?
Thanks...
"Gail Erickson [MS]" wrote:
> Typically the problem is that the database is not in 90 compatibility leve
l.
> You might try verifying the compatibility level of the database (EXEC
> sp_dbcmptlevel AdventureWorks)
> and if it doesn't return 90, change it (EXEC sp_dbcmptlevel AdventureWorks
,
> 90; ).
> If that doesn't solve the problem, you'll need to provide more details suc
h
> as the error message your getting.
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> Download the latest version of Books Online from
> http://www.microsoft.com/technet/pr...oads/books.mspx
> "mmc" <mmc@.discussions.microsoft.com> wrote in message
> news:2F41CDED-5E5E-4AFF-9DA7-81C92FC06179@.microsoft.com...
>
>|||The sp_dbcmptlevel AdventureWorks returned a "90".
The error everytime i try to open the diagram:
"The database diagram support objects can not be installed because because
this database does not have a valid owner. To continue, first use the Files
page of the Database Properties dialog box or the ALTER STATEMENT to set the
database owner to a valid login, then add add the database diagram support
objects".
I am logged in using "sa". Is "sa" valid in 2005?
Thanks...
"Gail Erickson [MS]" wrote:
> Typically the problem is that the database is not in 90 compatibility leve
l.
> You might try verifying the compatibility level of the database (EXEC
> sp_dbcmptlevel AdventureWorks)
> and if it doesn't return 90, change it (EXEC sp_dbcmptlevel AdventureWorks
,
> 90; ).
> If that doesn't solve the problem, you'll need to provide more details suc
h
> as the error message your getting.
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> Download the latest version of Books Online from
> http://www.microsoft.com/technet/pr...oads/books.mspx
> "mmc" <mmc@.discussions.microsoft.com> wrote in message
> news:2F41CDED-5E5E-4AFF-9DA7-81C92FC06179@.microsoft.com...
>
>|||> I am logged in using "sa". Is "sa" valid in 2005?
Yes, sa is valid in 2005. I noticed in the Books Online topic
"Understanding Database Diagram Ownership (Visual Database Tools) ", that it
says the following:
"To use Database Diagram Designer it must first be set up by a member of the
db_owner role (a role of Microsoft SQL Server databases) to control access
to diagrams." I'm not sure why sa wouldn't have permissions to do this, but
as an experiment, please use the ALTER AUTHORIZATION statement and change
the ownership to dbo and try again.
BTW, there is no AdventureWorks diagram that comes with the sample database,
but the support objects mentioned in the error message are required to
create a diagram so they get created when you just click on the Database
Diagram folder if they don't alread exist. I mention this just so you know
that once we get this figured out, there won't be a diagram there anyway
(but you can certainly create one on your own). If what you're really
looking for is an existing diagram of AdventureWorks, you can download an
.html or .vsd version from here:
http://www.microsoft.com/downloads/...displaylang=en.
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/pr...oads/books.mspx
"mmc" <mmc@.discussions.microsoft.com> wrote in message
news:006CC94E-AAC5-40D9-A351-71584899C02A@.microsoft.com...[vbcol=seagreen]
> The sp_dbcmptlevel AdventureWorks returned a "90".
> The error everytime i try to open the diagram:
> "The database diagram support objects can not be installed because because
> this database does not have a valid owner. To continue, first use the
> Files
> page of the Database Properties dialog box or the ALTER STATEMENT to set
> the
> database owner to a valid login, then add add the database diagram support
> objects".
> I am logged in using "sa". Is "sa" valid in 2005?
> Thanks...
>
> "Gail Erickson [MS]" wrote:
>|||>I can't view the AdventureWorksDB database diagram even after the ALTER
> AUTHORIZATION statement.
Typically the problem is that the database is not in 90 compatibility level.
You might try verifying the compatibility level of the database (EXEC
sp_dbcmptlevel AdventureWorks)
and if it doesn't return 90, change it (EXEC sp_dbcmptlevel AdventureWorks,
90; ).
If that doesn't solve the problem, you'll need to provide more details such
as the error message your getting.
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/pr...oads/books.mspx
"mmc" <mmc@.discussions.microsoft.com> wrote in message
news:2F41CDED-5E5E-4AFF-9DA7-81C92FC06179@.microsoft.com...
>I can't view the AdventureWorksDB database diagram even after the ALTER
> AUTHORIZATION statement.
> Thanks..|||The sp_dbcmptlevel AdventureWorks returned a "90".
The error everytime i try to open the diagram:
"The database diagram support objects can not be installed because because
this database does not have a valid owner. To continue, first use the Files
page of the Database Properties dialog box or the ALTER STATEMENT to set the
database owner to a valid login, then add add the database diagram support
objects".
I am logged in using "sa". Is "sa" valid in 2005?
Thanks...
"Gail Erickson [MS]" wrote:
> Typically the problem is that the database is not in 90 compatibility leve
l.
> You might try verifying the compatibility level of the database (EXEC
> sp_dbcmptlevel AdventureWorks)
> and if it doesn't return 90, change it (EXEC sp_dbcmptlevel AdventureWorks
,
> 90; ).
> If that doesn't solve the problem, you'll need to provide more details suc
h
> as the error message your getting.
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> Download the latest version of Books Online from
> http://www.microsoft.com/technet/pr...oads/books.mspx
> "mmc" <mmc@.discussions.microsoft.com> wrote in message
> news:2F41CDED-5E5E-4AFF-9DA7-81C92FC06179@.microsoft.com...
>
>|||The sp_dbcmptlevel AdventureWorks returned a "90".
The error everytime i try to open the diagram:
"The database diagram support objects can not be installed because because
this database does not have a valid owner. To continue, first use the Files
page of the Database Properties dialog box or the ALTER STATEMENT to set the
database owner to a valid login, then add add the database diagram support
objects".
I am logged in using "sa". Is "sa" valid in 2005?
Thanks...
"Gail Erickson [MS]" wrote:
> Typically the problem is that the database is not in 90 compatibility leve
l.
> You might try verifying the compatibility level of the database (EXEC
> sp_dbcmptlevel AdventureWorks)
> and if it doesn't return 90, change it (EXEC sp_dbcmptlevel AdventureWorks
,
> 90; ).
> If that doesn't solve the problem, you'll need to provide more details suc
h
> as the error message your getting.
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> Download the latest version of Books Online from
> http://www.microsoft.com/technet/pr...oads/books.mspx
> "mmc" <mmc@.discussions.microsoft.com> wrote in message
> news:2F41CDED-5E5E-4AFF-9DA7-81C92FC06179@.microsoft.com...
>
>|||mmc wrote:
> The sp_dbcmptlevel AdventureWorks returned a "90".
> The error everytime i try to open the diagram:
> "The database diagram support objects can not be installed because because
> this database does not have a valid owner. To continue, first use the File
s
> page of the Database Properties dialog box or the ALTER STATEMENT to set t
he
> database owner to a valid login, then add add the database diagram support
> objects".
> I am logged in using "sa". Is "sa" valid in 2005?
> Thanks...
>
The error message is complaining about an invalid database owner. What
user is shown as the owner of the AdventureWorks database?|||> I am logged in using "sa". Is "sa" valid in 2005?
Yes, sa is valid in 2005. I noticed in the Books Online topic
"Understanding Database Diagram Ownership (Visual Database Tools) ", that it
says the following:
"To use Database Diagram Designer it must first be set up by a member of the
db_owner role (a role of Microsoft SQL Server databases) to control access
to diagrams." I'm not sure why sa wouldn't have permissions to do this, but
as an experiment, please use the ALTER AUTHORIZATION statement and change
the ownership to dbo and try again.
BTW, there is no AdventureWorks diagram that comes with the sample database,
but the support objects mentioned in the error message are required to
create a diagram so they get created when you just click on the Database
Diagram folder if they don't alread exist. I mention this just so you know
that once we get this figured out, there won't be a diagram there anyway
(but you can certainly create one on your own). If what you're really
looking for is an existing diagram of AdventureWorks, you can download an
.html or .vsd version from here:
http://www.microsoft.com/downloads/...displaylang=en.
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/pr...oads/books.mspx
"mmc" <mmc@.discussions.microsoft.com> wrote in message
news:006CC94E-AAC5-40D9-A351-71584899C02A@.microsoft.com...[vbcol=seagreen]
> The sp_dbcmptlevel AdventureWorks returned a "90".
> The error everytime i try to open the diagram:
> "The database diagram support objects can not be installed because because
> this database does not have a valid owner. To continue, first use the
> Files
> page of the Database Properties dialog box or the ALTER STATEMENT to set
> the
> database owner to a valid login, then add add the database diagram support
> objects".
> I am logged in using "sa". Is "sa" valid in 2005?
> Thanks...
>
> "Gail Erickson [MS]" wrote:
>
Showing posts with label view. Show all posts
Showing posts with label view. Show all posts
Sunday, March 25, 2012
AdventureWorksDB
I can't view the AdventureWorksDB database diagram even after the ALTER
AUTHORIZATION statement.
Thanks..>I can't view the AdventureWorksDB database diagram even after the ALTER
> AUTHORIZATION statement.
Typically the problem is that the database is not in 90 compatibility level.
You might try verifying the compatibility level of the database (EXEC
sp_dbcmptlevel AdventureWorks)
and if it doesn't return 90, change it (EXEC sp_dbcmptlevel AdventureWorks,
90; ).
If that doesn't solve the problem, you'll need to provide more details such
as the error message your getting.
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
"mmc" <mmc@.discussions.microsoft.com> wrote in message
news:2F41CDED-5E5E-4AFF-9DA7-81C92FC06179@.microsoft.com...
>I can't view the AdventureWorksDB database diagram even after the ALTER
> AUTHORIZATION statement.
> Thanks..|||The sp_dbcmptlevel AdventureWorks returned a "90".
The error everytime i try to open the diagram:
"The database diagram support objects can not be installed because because
this database does not have a valid owner. To continue, first use the Files
page of the Database Properties dialog box or the ALTER STATEMENT to set the
database owner to a valid login, then add add the database diagram support
objects".
I am logged in using "sa". Is "sa" valid in 2005?
Thanks...
"Gail Erickson [MS]" wrote:
> >I can't view the AdventureWorksDB database diagram even after the ALTER
> > AUTHORIZATION statement.
> Typically the problem is that the database is not in 90 compatibility level.
> You might try verifying the compatibility level of the database (EXEC
> sp_dbcmptlevel AdventureWorks)
> and if it doesn't return 90, change it (EXEC sp_dbcmptlevel AdventureWorks,
> 90; ).
> If that doesn't solve the problem, you'll need to provide more details such
> as the error message your getting.
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> Download the latest version of Books Online from
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> "mmc" <mmc@.discussions.microsoft.com> wrote in message
> news:2F41CDED-5E5E-4AFF-9DA7-81C92FC06179@.microsoft.com...
> >I can't view the AdventureWorksDB database diagram even after the ALTER
> > AUTHORIZATION statement.
> > Thanks..
>
>|||The sp_dbcmptlevel AdventureWorks returned a "90".
The error everytime i try to open the diagram:
"The database diagram support objects can not be installed because because
this database does not have a valid owner. To continue, first use the Files
page of the Database Properties dialog box or the ALTER STATEMENT to set the
database owner to a valid login, then add add the database diagram support
objects".
I am logged in using "sa". Is "sa" valid in 2005?
Thanks...
"Gail Erickson [MS]" wrote:
> >I can't view the AdventureWorksDB database diagram even after the ALTER
> > AUTHORIZATION statement.
> Typically the problem is that the database is not in 90 compatibility level.
> You might try verifying the compatibility level of the database (EXEC
> sp_dbcmptlevel AdventureWorks)
> and if it doesn't return 90, change it (EXEC sp_dbcmptlevel AdventureWorks,
> 90; ).
> If that doesn't solve the problem, you'll need to provide more details such
> as the error message your getting.
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> Download the latest version of Books Online from
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> "mmc" <mmc@.discussions.microsoft.com> wrote in message
> news:2F41CDED-5E5E-4AFF-9DA7-81C92FC06179@.microsoft.com...
> >I can't view the AdventureWorksDB database diagram even after the ALTER
> > AUTHORIZATION statement.
> > Thanks..
>
>|||> I am logged in using "sa". Is "sa" valid in 2005?
Yes, sa is valid in 2005. I noticed in the Books Online topic
"Understanding Database Diagram Ownership (Visual Database Tools) ", that it
says the following:
"To use Database Diagram Designer it must first be set up by a member of the
db_owner role (a role of Microsoft SQL Server databases) to control access
to diagrams." I'm not sure why sa wouldn't have permissions to do this, but
as an experiment, please use the ALTER AUTHORIZATION statement and change
the ownership to dbo and try again.
BTW, there is no AdventureWorks diagram that comes with the sample database,
but the support objects mentioned in the error message are required to
create a diagram so they get created when you just click on the Database
Diagram folder if they don't alread exist. I mention this just so you know
that once we get this figured out, there won't be a diagram there anyway
(but you can certainly create one on your own). If what you're really
looking for is an existing diagram of AdventureWorks, you can download an
.html or .vsd version from here:
http://www.microsoft.com/downloads/details.aspx?familyid=0F6E0BCF-A1B5-4760-8D79-67970F93D5FF&displaylang=en.
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
"mmc" <mmc@.discussions.microsoft.com> wrote in message
news:006CC94E-AAC5-40D9-A351-71584899C02A@.microsoft.com...
> The sp_dbcmptlevel AdventureWorks returned a "90".
> The error everytime i try to open the diagram:
> "The database diagram support objects can not be installed because because
> this database does not have a valid owner. To continue, first use the
> Files
> page of the Database Properties dialog box or the ALTER STATEMENT to set
> the
> database owner to a valid login, then add add the database diagram support
> objects".
> I am logged in using "sa". Is "sa" valid in 2005?
> Thanks...
>
> "Gail Erickson [MS]" wrote:
>> >I can't view the AdventureWorksDB database diagram even after the ALTER
>> > AUTHORIZATION statement.
>> Typically the problem is that the database is not in 90 compatibility
>> level.
>> You might try verifying the compatibility level of the database (EXEC
>> sp_dbcmptlevel AdventureWorks)
>> and if it doesn't return 90, change it (EXEC sp_dbcmptlevel
>> AdventureWorks,
>> 90; ).
>> If that doesn't solve the problem, you'll need to provide more details
>> such
>> as the error message your getting.
>> --
>> Gail Erickson [MS]
>> SQL Server Documentation Team
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights
>> Download the latest version of Books Online from
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
>> "mmc" <mmc@.discussions.microsoft.com> wrote in message
>> news:2F41CDED-5E5E-4AFF-9DA7-81C92FC06179@.microsoft.com...
>> >I can't view the AdventureWorksDB database diagram even after the ALTER
>> > AUTHORIZATION statement.
>> > Thanks..
>>|||mmc wrote:
> The sp_dbcmptlevel AdventureWorks returned a "90".
> The error everytime i try to open the diagram:
> "The database diagram support objects can not be installed because because
> this database does not have a valid owner. To continue, first use the Files
> page of the Database Properties dialog box or the ALTER STATEMENT to set the
> database owner to a valid login, then add add the database diagram support
> objects".
> I am logged in using "sa". Is "sa" valid in 2005?
> Thanks...
>
The error message is complaining about an invalid database owner. What
user is shown as the owner of the AdventureWorks database?|||Thanks. I'll just download the diagram.
"Gail Erickson [MS]" wrote:
> > I am logged in using "sa". Is "sa" valid in 2005?
> Yes, sa is valid in 2005. I noticed in the Books Online topic
> "Understanding Database Diagram Ownership (Visual Database Tools) ", that it
> says the following:
> "To use Database Diagram Designer it must first be set up by a member of the
> db_owner role (a role of Microsoft SQL Server databases) to control access
> to diagrams." I'm not sure why sa wouldn't have permissions to do this, but
> as an experiment, please use the ALTER AUTHORIZATION statement and change
> the ownership to dbo and try again.
> BTW, there is no AdventureWorks diagram that comes with the sample database,
> but the support objects mentioned in the error message are required to
> create a diagram so they get created when you just click on the Database
> Diagram folder if they don't alread exist. I mention this just so you know
> that once we get this figured out, there won't be a diagram there anyway
> (but you can certainly create one on your own). If what you're really
> looking for is an existing diagram of AdventureWorks, you can download an
> ..html or .vsd version from here:
> http://www.microsoft.com/downloads/details.aspx?familyid=0F6E0BCF-A1B5-4760-8D79-67970F93D5FF&displaylang=en.
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> Download the latest version of Books Online from
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> "mmc" <mmc@.discussions.microsoft.com> wrote in message
> news:006CC94E-AAC5-40D9-A351-71584899C02A@.microsoft.com...
> > The sp_dbcmptlevel AdventureWorks returned a "90".
> > The error everytime i try to open the diagram:
> > "The database diagram support objects can not be installed because because
> > this database does not have a valid owner. To continue, first use the
> > Files
> > page of the Database Properties dialog box or the ALTER STATEMENT to set
> > the
> > database owner to a valid login, then add add the database diagram support
> > objects".
> > I am logged in using "sa". Is "sa" valid in 2005?
> > Thanks...
> >
> >
> > "Gail Erickson [MS]" wrote:
> >
> >> >I can't view the AdventureWorksDB database diagram even after the ALTER
> >> > AUTHORIZATION statement.
> >>
> >> Typically the problem is that the database is not in 90 compatibility
> >> level.
> >> You might try verifying the compatibility level of the database (EXEC
> >> sp_dbcmptlevel AdventureWorks)
> >> and if it doesn't return 90, change it (EXEC sp_dbcmptlevel
> >> AdventureWorks,
> >> 90; ).
> >>
> >> If that doesn't solve the problem, you'll need to provide more details
> >> such
> >> as the error message your getting.
> >>
> >> --
> >> Gail Erickson [MS]
> >> SQL Server Documentation Team
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights
> >> Download the latest version of Books Online from
> >> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> >>
> >> "mmc" <mmc@.discussions.microsoft.com> wrote in message
> >> news:2F41CDED-5E5E-4AFF-9DA7-81C92FC06179@.microsoft.com...
> >> >I can't view the AdventureWorksDB database diagram even after the ALTER
> >> > AUTHORIZATION statement.
> >> > Thanks..
> >>
> >>
> >>
>
>|||mmc <mmc@.discussions.microsoft.com> wrote :
> > The error everytime i try to open the diagram:
> > "The database diagram support objects can not be installed because because
> > this database does not have a valid owner. To continue, first use the Files
> > page of the Database Properties dialog box or the ALTER STATEMENT to set the
> > database owner to a valid login, then add add the database diagram support objects".
Gail Erickson [MS] wrote:
> I noticed in the Books Online topic
> "Understanding Database Diagram Ownership (Visual Database Tools) ", that it
> says the following:
> "To use Database Diagram Designer it must first be set up by a member of the
> db_owner role (a role of Microsoft SQL Server databases) to control access
> to diagrams." I'm not sure why sa wouldn't have permissions to do this, but
> as an experiment, please use the ALTER AUTHORIZATION statement and change
> the ownership to dbo and try again.
I am able to reproduce the error on SQL Server 2005 Express Edition
SP1, with Management Studio Express SP1. The owner of the database was
a windows login (the windows user that was currently logged-on) which
is a member of the Administrators group. In Security / Logins there is
a login for BUILTIN\Administrators. I followed the instructions
described in the error message to change the database owner to 'sa' and
then the creation of database diagram support objects succeeded.
However, I think it should also work if the database owner is a windows
login (for a user which is member of a windows group that has a SQL
login), because these are the default installation options.
Razvan|||I think you are right about the windows login although I haven't tried it yet
. I am testing this using SQL authentication. It just doesn't make sense to
be able to do almost anything using "sa", but then you have to be in a
windows domain to view the diagram. This is, I guess, one of the subtle
differences between 2000 and 2005.
"Razvan Socol" wrote:
> mmc <mmc@.discussions.microsoft.com> wrote :
> > > The error everytime i try to open the diagram:
> > > "The database diagram support objects can not be installed because because
> > > this database does not have a valid owner. To continue, first use the Files
> > > page of the Database Properties dialog box or the ALTER STATEMENT to set the
> > > database owner to a valid login, then add add the database diagram support objects".
> Gail Erickson [MS] wrote:
> > I noticed in the Books Online topic
> > "Understanding Database Diagram Ownership (Visual Database Tools) ", that it
> > says the following:
> > "To use Database Diagram Designer it must first be set up by a member of the
> > db_owner role (a role of Microsoft SQL Server databases) to control access
> > to diagrams." I'm not sure why sa wouldn't have permissions to do this, but
> > as an experiment, please use the ALTER AUTHORIZATION statement and change
> > the ownership to dbo and try again.
> I am able to reproduce the error on SQL Server 2005 Express Edition
> SP1, with Management Studio Express SP1. The owner of the database was
> a windows login (the windows user that was currently logged-on) which
> is a member of the Administrators group. In Security / Logins there is
> a login for BUILTIN\Administrators. I followed the instructions
> described in the error message to change the database owner to 'sa' and
> then the creation of database diagram support objects succeeded.
> However, I think it should also work if the database owner is a windows
> login (for a user which is member of a windows group that has a SQL
> login), because these are the default installation options.
> Razvan
>|||mmc wrote:
> It just doesn't make sense to
> be able to do almost anything using "sa", but then you have to be in a
> windows domain to view the diagram.
Currently, it's the opposite situation: if you want to create the
diagramming support objects in AdventureWorks and the database owner is
a windows user, you have to change the owner to 'sa'.
However, I tried the same thing on a newly created database and the
creation of diagramming support objects succeeded, even if the database
owner was the same windows user (as the one which was the owner of
AdventureWorks, when the operation initially failed). It looks like
there was a problem with the way AdventureWorks was installed, because
when I looked at "Database Properties / Files / Owner" for the new
database, the owner was specified; but for AdventureWorks, the Owner
textbox (in the Files page of the Database Properties window) was
blank; however, the owner was shown for AdventureWorks in the General
page of the Database Properties window.
Razvansql
AUTHORIZATION statement.
Thanks..>I can't view the AdventureWorksDB database diagram even after the ALTER
> AUTHORIZATION statement.
Typically the problem is that the database is not in 90 compatibility level.
You might try verifying the compatibility level of the database (EXEC
sp_dbcmptlevel AdventureWorks)
and if it doesn't return 90, change it (EXEC sp_dbcmptlevel AdventureWorks,
90; ).
If that doesn't solve the problem, you'll need to provide more details such
as the error message your getting.
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
"mmc" <mmc@.discussions.microsoft.com> wrote in message
news:2F41CDED-5E5E-4AFF-9DA7-81C92FC06179@.microsoft.com...
>I can't view the AdventureWorksDB database diagram even after the ALTER
> AUTHORIZATION statement.
> Thanks..|||The sp_dbcmptlevel AdventureWorks returned a "90".
The error everytime i try to open the diagram:
"The database diagram support objects can not be installed because because
this database does not have a valid owner. To continue, first use the Files
page of the Database Properties dialog box or the ALTER STATEMENT to set the
database owner to a valid login, then add add the database diagram support
objects".
I am logged in using "sa". Is "sa" valid in 2005?
Thanks...
"Gail Erickson [MS]" wrote:
> >I can't view the AdventureWorksDB database diagram even after the ALTER
> > AUTHORIZATION statement.
> Typically the problem is that the database is not in 90 compatibility level.
> You might try verifying the compatibility level of the database (EXEC
> sp_dbcmptlevel AdventureWorks)
> and if it doesn't return 90, change it (EXEC sp_dbcmptlevel AdventureWorks,
> 90; ).
> If that doesn't solve the problem, you'll need to provide more details such
> as the error message your getting.
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> Download the latest version of Books Online from
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> "mmc" <mmc@.discussions.microsoft.com> wrote in message
> news:2F41CDED-5E5E-4AFF-9DA7-81C92FC06179@.microsoft.com...
> >I can't view the AdventureWorksDB database diagram even after the ALTER
> > AUTHORIZATION statement.
> > Thanks..
>
>|||The sp_dbcmptlevel AdventureWorks returned a "90".
The error everytime i try to open the diagram:
"The database diagram support objects can not be installed because because
this database does not have a valid owner. To continue, first use the Files
page of the Database Properties dialog box or the ALTER STATEMENT to set the
database owner to a valid login, then add add the database diagram support
objects".
I am logged in using "sa". Is "sa" valid in 2005?
Thanks...
"Gail Erickson [MS]" wrote:
> >I can't view the AdventureWorksDB database diagram even after the ALTER
> > AUTHORIZATION statement.
> Typically the problem is that the database is not in 90 compatibility level.
> You might try verifying the compatibility level of the database (EXEC
> sp_dbcmptlevel AdventureWorks)
> and if it doesn't return 90, change it (EXEC sp_dbcmptlevel AdventureWorks,
> 90; ).
> If that doesn't solve the problem, you'll need to provide more details such
> as the error message your getting.
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> Download the latest version of Books Online from
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> "mmc" <mmc@.discussions.microsoft.com> wrote in message
> news:2F41CDED-5E5E-4AFF-9DA7-81C92FC06179@.microsoft.com...
> >I can't view the AdventureWorksDB database diagram even after the ALTER
> > AUTHORIZATION statement.
> > Thanks..
>
>|||> I am logged in using "sa". Is "sa" valid in 2005?
Yes, sa is valid in 2005. I noticed in the Books Online topic
"Understanding Database Diagram Ownership (Visual Database Tools) ", that it
says the following:
"To use Database Diagram Designer it must first be set up by a member of the
db_owner role (a role of Microsoft SQL Server databases) to control access
to diagrams." I'm not sure why sa wouldn't have permissions to do this, but
as an experiment, please use the ALTER AUTHORIZATION statement and change
the ownership to dbo and try again.
BTW, there is no AdventureWorks diagram that comes with the sample database,
but the support objects mentioned in the error message are required to
create a diagram so they get created when you just click on the Database
Diagram folder if they don't alread exist. I mention this just so you know
that once we get this figured out, there won't be a diagram there anyway
(but you can certainly create one on your own). If what you're really
looking for is an existing diagram of AdventureWorks, you can download an
.html or .vsd version from here:
http://www.microsoft.com/downloads/details.aspx?familyid=0F6E0BCF-A1B5-4760-8D79-67970F93D5FF&displaylang=en.
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
"mmc" <mmc@.discussions.microsoft.com> wrote in message
news:006CC94E-AAC5-40D9-A351-71584899C02A@.microsoft.com...
> The sp_dbcmptlevel AdventureWorks returned a "90".
> The error everytime i try to open the diagram:
> "The database diagram support objects can not be installed because because
> this database does not have a valid owner. To continue, first use the
> Files
> page of the Database Properties dialog box or the ALTER STATEMENT to set
> the
> database owner to a valid login, then add add the database diagram support
> objects".
> I am logged in using "sa". Is "sa" valid in 2005?
> Thanks...
>
> "Gail Erickson [MS]" wrote:
>> >I can't view the AdventureWorksDB database diagram even after the ALTER
>> > AUTHORIZATION statement.
>> Typically the problem is that the database is not in 90 compatibility
>> level.
>> You might try verifying the compatibility level of the database (EXEC
>> sp_dbcmptlevel AdventureWorks)
>> and if it doesn't return 90, change it (EXEC sp_dbcmptlevel
>> AdventureWorks,
>> 90; ).
>> If that doesn't solve the problem, you'll need to provide more details
>> such
>> as the error message your getting.
>> --
>> Gail Erickson [MS]
>> SQL Server Documentation Team
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights
>> Download the latest version of Books Online from
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
>> "mmc" <mmc@.discussions.microsoft.com> wrote in message
>> news:2F41CDED-5E5E-4AFF-9DA7-81C92FC06179@.microsoft.com...
>> >I can't view the AdventureWorksDB database diagram even after the ALTER
>> > AUTHORIZATION statement.
>> > Thanks..
>>|||mmc wrote:
> The sp_dbcmptlevel AdventureWorks returned a "90".
> The error everytime i try to open the diagram:
> "The database diagram support objects can not be installed because because
> this database does not have a valid owner. To continue, first use the Files
> page of the Database Properties dialog box or the ALTER STATEMENT to set the
> database owner to a valid login, then add add the database diagram support
> objects".
> I am logged in using "sa". Is "sa" valid in 2005?
> Thanks...
>
The error message is complaining about an invalid database owner. What
user is shown as the owner of the AdventureWorks database?|||Thanks. I'll just download the diagram.
"Gail Erickson [MS]" wrote:
> > I am logged in using "sa". Is "sa" valid in 2005?
> Yes, sa is valid in 2005. I noticed in the Books Online topic
> "Understanding Database Diagram Ownership (Visual Database Tools) ", that it
> says the following:
> "To use Database Diagram Designer it must first be set up by a member of the
> db_owner role (a role of Microsoft SQL Server databases) to control access
> to diagrams." I'm not sure why sa wouldn't have permissions to do this, but
> as an experiment, please use the ALTER AUTHORIZATION statement and change
> the ownership to dbo and try again.
> BTW, there is no AdventureWorks diagram that comes with the sample database,
> but the support objects mentioned in the error message are required to
> create a diagram so they get created when you just click on the Database
> Diagram folder if they don't alread exist. I mention this just so you know
> that once we get this figured out, there won't be a diagram there anyway
> (but you can certainly create one on your own). If what you're really
> looking for is an existing diagram of AdventureWorks, you can download an
> ..html or .vsd version from here:
> http://www.microsoft.com/downloads/details.aspx?familyid=0F6E0BCF-A1B5-4760-8D79-67970F93D5FF&displaylang=en.
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> Download the latest version of Books Online from
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> "mmc" <mmc@.discussions.microsoft.com> wrote in message
> news:006CC94E-AAC5-40D9-A351-71584899C02A@.microsoft.com...
> > The sp_dbcmptlevel AdventureWorks returned a "90".
> > The error everytime i try to open the diagram:
> > "The database diagram support objects can not be installed because because
> > this database does not have a valid owner. To continue, first use the
> > Files
> > page of the Database Properties dialog box or the ALTER STATEMENT to set
> > the
> > database owner to a valid login, then add add the database diagram support
> > objects".
> > I am logged in using "sa". Is "sa" valid in 2005?
> > Thanks...
> >
> >
> > "Gail Erickson [MS]" wrote:
> >
> >> >I can't view the AdventureWorksDB database diagram even after the ALTER
> >> > AUTHORIZATION statement.
> >>
> >> Typically the problem is that the database is not in 90 compatibility
> >> level.
> >> You might try verifying the compatibility level of the database (EXEC
> >> sp_dbcmptlevel AdventureWorks)
> >> and if it doesn't return 90, change it (EXEC sp_dbcmptlevel
> >> AdventureWorks,
> >> 90; ).
> >>
> >> If that doesn't solve the problem, you'll need to provide more details
> >> such
> >> as the error message your getting.
> >>
> >> --
> >> Gail Erickson [MS]
> >> SQL Server Documentation Team
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights
> >> Download the latest version of Books Online from
> >> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> >>
> >> "mmc" <mmc@.discussions.microsoft.com> wrote in message
> >> news:2F41CDED-5E5E-4AFF-9DA7-81C92FC06179@.microsoft.com...
> >> >I can't view the AdventureWorksDB database diagram even after the ALTER
> >> > AUTHORIZATION statement.
> >> > Thanks..
> >>
> >>
> >>
>
>|||mmc <mmc@.discussions.microsoft.com> wrote :
> > The error everytime i try to open the diagram:
> > "The database diagram support objects can not be installed because because
> > this database does not have a valid owner. To continue, first use the Files
> > page of the Database Properties dialog box or the ALTER STATEMENT to set the
> > database owner to a valid login, then add add the database diagram support objects".
Gail Erickson [MS] wrote:
> I noticed in the Books Online topic
> "Understanding Database Diagram Ownership (Visual Database Tools) ", that it
> says the following:
> "To use Database Diagram Designer it must first be set up by a member of the
> db_owner role (a role of Microsoft SQL Server databases) to control access
> to diagrams." I'm not sure why sa wouldn't have permissions to do this, but
> as an experiment, please use the ALTER AUTHORIZATION statement and change
> the ownership to dbo and try again.
I am able to reproduce the error on SQL Server 2005 Express Edition
SP1, with Management Studio Express SP1. The owner of the database was
a windows login (the windows user that was currently logged-on) which
is a member of the Administrators group. In Security / Logins there is
a login for BUILTIN\Administrators. I followed the instructions
described in the error message to change the database owner to 'sa' and
then the creation of database diagram support objects succeeded.
However, I think it should also work if the database owner is a windows
login (for a user which is member of a windows group that has a SQL
login), because these are the default installation options.
Razvan|||I think you are right about the windows login although I haven't tried it yet
. I am testing this using SQL authentication. It just doesn't make sense to
be able to do almost anything using "sa", but then you have to be in a
windows domain to view the diagram. This is, I guess, one of the subtle
differences between 2000 and 2005.
"Razvan Socol" wrote:
> mmc <mmc@.discussions.microsoft.com> wrote :
> > > The error everytime i try to open the diagram:
> > > "The database diagram support objects can not be installed because because
> > > this database does not have a valid owner. To continue, first use the Files
> > > page of the Database Properties dialog box or the ALTER STATEMENT to set the
> > > database owner to a valid login, then add add the database diagram support objects".
> Gail Erickson [MS] wrote:
> > I noticed in the Books Online topic
> > "Understanding Database Diagram Ownership (Visual Database Tools) ", that it
> > says the following:
> > "To use Database Diagram Designer it must first be set up by a member of the
> > db_owner role (a role of Microsoft SQL Server databases) to control access
> > to diagrams." I'm not sure why sa wouldn't have permissions to do this, but
> > as an experiment, please use the ALTER AUTHORIZATION statement and change
> > the ownership to dbo and try again.
> I am able to reproduce the error on SQL Server 2005 Express Edition
> SP1, with Management Studio Express SP1. The owner of the database was
> a windows login (the windows user that was currently logged-on) which
> is a member of the Administrators group. In Security / Logins there is
> a login for BUILTIN\Administrators. I followed the instructions
> described in the error message to change the database owner to 'sa' and
> then the creation of database diagram support objects succeeded.
> However, I think it should also work if the database owner is a windows
> login (for a user which is member of a windows group that has a SQL
> login), because these are the default installation options.
> Razvan
>|||mmc wrote:
> It just doesn't make sense to
> be able to do almost anything using "sa", but then you have to be in a
> windows domain to view the diagram.
Currently, it's the opposite situation: if you want to create the
diagramming support objects in AdventureWorks and the database owner is
a windows user, you have to change the owner to 'sa'.
However, I tried the same thing on a newly created database and the
creation of diagramming support objects succeeded, even if the database
owner was the same windows user (as the one which was the owner of
AdventureWorks, when the operation initially failed). It looks like
there was a problem with the way AdventureWorks was installed, because
when I looked at "Database Properties / Files / Owner" for the new
database, the owner was specified; but for AdventureWorks, the Owner
textbox (in the Files page of the Database Properties window) was
blank; however, the owner was shown for AdventureWorks in the General
page of the Database Properties window.
Razvansql
AdventureWorks_Data.mdf, where is it?
I'm using 'Using Data Grid View' in 101 samples and it wants to access the AdventureWorks_Data.mdf.
I cannot find this database anywhere, even searching the Microsoft web sites.
Does anyone know where it's located?
Thanks.
If anyone is interested it's at http://www.microsoft.com/downloads/details.aspx?familyid=2adbc1a8-ae5c-497d-b584-eab6719300cd&displaylang=en
and it's called AdventureWorksDB.msi
CU all
|||
Tony Scarpelli wrote:
If anyone is interested it's at http://www.microsoft.com/downloads/details.aspx?familyid=2adbc1a8-ae5c-497d-b584-eab6719300cd&displaylang=en
and it's called AdventureWorksDB.msi
CU all
The page cannot be found
|||Go here : http://www.microsoft.com/downloads/details.aspx?FamilyId=E719ECF7-9F46-4312-AF89-6AD8702E4E6E&displaylang=en|||Thanks...|||
you can get it from
http://www.microsoft.com/downloads/details.aspx?FamilyId=E719ECF7-9F46-4312-AF89-6AD8702E4E6E&displaylang=en
|||Please help,
The AdventureWorks_Data.mdf file is for SQL Server 2008. That file could not work when I tried to install it on my SQL Server 2005 Express Edition with Advanced features.
Does anyone know of the write database?
Thanks.
AdventureWorks_Data.mdf, where is it?
I'm using 'Using Data Grid View' in 101 samples and it wants to access the AdventureWorks_Data.mdf.
I cannot find this database anywhere, even searching the Microsoft web sites.
Does anyone know where it's located?
Thanks.
If anyone is interested it's at http://www.microsoft.com/downloads/details.aspx?familyid=2adbc1a8-ae5c-497d-b584-eab6719300cd&displaylang=en
and it's called AdventureWorksDB.msi
CU all
|||
If anyone is interested it's at http://www.microsoft.com/downloads/details.aspx?familyid=2adbc1a8-ae5c-497d-b584-eab6719300cd&displaylang=en
and it's called AdventureWorksDB.msi
CU all
The page cannot be found
|||Go here : http://www.microsoft.com/downloads/details.aspx?FamilyId=E719ECF7-9F46-4312-AF89-6AD8702E4E6E&displaylang=en|||Thanks...|||
you can get it from
http://www.microsoft.com/downloads/details.aspx?FamilyId=E719ECF7-9F46-4312-AF89-6AD8702E4E6E&displaylang=en
|||Please help,
The AdventureWorks_Data.mdf file is for SQL Server 2008. That file could not work when I tried to install it on my SQL Server 2005 Express Edition with Advanced features.
Does anyone know of the write database?
Thanks.
AdventureWorks_Data.mdf, where is it?
I'm using 'Using Data Grid View' in 101 samples and it wants to access the AdventureWorks_Data.mdf.
I cannot find this database anywhere, even searching the Microsoft web sites.
Does anyone know where it's located?
Thanks.
If anyone is interested it's at http://www.microsoft.com/downloads/details.aspx?familyid=2adbc1a8-ae5c-497d-b584-eab6719300cd&displaylang=en
and it's called AdventureWorksDB.msi
CU all
|||
If anyone is interested it's at http://www.microsoft.com/downloads/details.aspx?familyid=2adbc1a8-ae5c-497d-b584-eab6719300cd&displaylang=en
and it's called AdventureWorksDB.msi
CU all
The page cannot be found
|||Go here : http://www.microsoft.com/downloads/details.aspx?FamilyId=E719ECF7-9F46-4312-AF89-6AD8702E4E6E&displaylang=en|||Thanks...|||
you can get it from
http://www.microsoft.com/downloads/details.aspx?FamilyId=E719ECF7-9F46-4312-AF89-6AD8702E4E6E&displaylang=en
|||Please help,
The AdventureWorks_Data.mdf file is for SQL Server 2008. That file could not work when I tried to install it on my SQL Server 2005 Express Edition with Advanced features.
Does anyone know of the write database?
Thanks.
AdventureWorks_Data.mdf, where is it?
I'm using 'Using Data Grid View' in 101 samples and it wants to access the AdventureWorks_Data.mdf.
I cannot find this database anywhere, even searching the Microsoft web sites.
Does anyone know where it's located?
Thanks.If anyone is interested it's at http://www.microsoft.com/downloads/details.aspx?familyid=2adbc1a8-ae5c-497d-b584-eab6719300cd&displaylang=en
and it's called AdventureWorksDB.msi
CU all
|||
Tony Scarpelli wrote:
If anyone is interested it's at http://www.microsoft.com/downloads/details.aspx?familyid=2adbc1a8-ae5c-497d-b584-eab6719300cd&displaylang=en
and it's called AdventureWorksDB.msi
CU all
The page cannot be found
|||Go here : http://www.microsoft.com/downloads/details.aspx?FamilyId=E719ECF7-9F46-4312-AF89-6AD8702E4E6E&displaylang=en|||Thanks...|||you can get it from
http://www.microsoft.com/downloads/details.aspx?FamilyId=E719ECF7-9F46-4312-AF89-6AD8702E4E6E&displaylang=en
|||Please help,
The AdventureWorks_Data.mdf file is for SQL Server 2008. That file could not work when I tried to install it on my SQL Server 2005 Express Edition with Advanced features.
Does anyone know of the write database?
Thanks.
AdventureWorks_Data.mdf, where is it?
I'm using 'Using Data Grid View' in 101 samples and it wants to access the AdventureWorks_Data.mdf.
I cannot find this database anywhere, even searching the Microsoft web sites.
Does anyone know where it's located?
Thanks.
If anyone is interested it's at http://www.microsoft.com/downloads/details.aspx?familyid=2adbc1a8-ae5c-497d-b584-eab6719300cd&displaylang=en
and it's called AdventureWorksDB.msi
CU all
|||
If anyone is interested it's at http://www.microsoft.com/downloads/details.aspx?familyid=2adbc1a8-ae5c-497d-b584-eab6719300cd&displaylang=en
and it's called AdventureWorksDB.msi
CU all
The page cannot be found
|||Go here : http://www.microsoft.com/downloads/details.aspx?FamilyId=E719ECF7-9F46-4312-AF89-6AD8702E4E6E&displaylang=en|||Thanks...|||
you can get it from
http://www.microsoft.com/downloads/details.aspx?FamilyId=E719ECF7-9F46-4312-AF89-6AD8702E4E6E&displaylang=en
|||Please help,
The AdventureWorks_Data.mdf file is for SQL Server 2008. That file could not work when I tried to install it on my SQL Server 2005 Express Edition with Advanced features.
Does anyone know of the write database?
Thanks.
|||I found it here:
http://www.codeplex.com/MSFTDBProdSamples/Release/ProjectReleases.aspx?ReleaseId=4004
suc6
sqlAdventureWorks_Data.mdf, where is it?
I'm using 'Using Data Grid View' in 101 samples and it wants to access the AdventureWorks_Data.mdf.
I cannot find this database anywhere, even searching the Microsoft web sites.
Does anyone know where it's located?
Thanks.
If anyone is interested it's at http://www.microsoft.com/downloads/details.aspx?familyid=2adbc1a8-ae5c-497d-b584-eab6719300cd&displaylang=en
and it's called AdventureWorksDB.msi
CU all
|||
If anyone is interested it's at http://www.microsoft.com/downloads/details.aspx?familyid=2adbc1a8-ae5c-497d-b584-eab6719300cd&displaylang=en
and it's called AdventureWorksDB.msi
CU all
The page cannot be found
|||Go here : http://www.microsoft.com/downloads/details.aspx?FamilyId=E719ECF7-9F46-4312-AF89-6AD8702E4E6E&displaylang=en|||Thanks...|||
you can get it from
http://www.microsoft.com/downloads/details.aspx?FamilyId=E719ECF7-9F46-4312-AF89-6AD8702E4E6E&displaylang=en
|||Please help,
The AdventureWorks_Data.mdf file is for SQL Server 2008. That file could not work when I tried to install it on my SQL Server 2005 Express Edition with Advanced features.
Does anyone know of the write database?
Thanks.
|||
I found it here:
http://www.codeplex.com/MSFTDBProdSamples/Release/ProjectReleases.aspx?ReleaseId=4004
suc6
Thursday, March 22, 2012
Adventureworks sample reports
Newbie Q. Can I view Adventureworks sample reports with SSX? Storefront? Thanks.
Sorry - what is SSX?
Regarding Storefront - do you mean these folks: http://www.storefront.com/?
What is it you're trying to do? Do you mean can you deliver reports to be printed using a standard online printing solution? If so then yes it is possible. No we can't do it out of the box, but you can write a delivery extension to send report output to the online service provider if they expose an API for submitting requests programmatically. You can learn about delivery extensions here: http://msdn2.microsoft.com/library/ms154672.aspx
Hope that helps,
-Lukasz
This posting is provided "AS IS" with no warranties, and confers no rights.
Hi Lukasz,
I have seen other posts refering to SQL Server Express using the acronym SSX.
I have been trying to run Adventureworks samples/reports and keep getting error messages that I don't have the application/version to load/run files with the extension .csproj or .rptproj. I have downloaded/installed SSX, Adventureworks samples, VBX, CSX, Visual Web Developer, SSNSX and the .Net Framework 2.0 SDK.
Storefront is a sample project included in the Adventureworks sample package. It is intended to illustrate a sample web page for the fictitous Adventureworks company.
I wish to be able to view sample reports included in the sample package.
I have been successful at connecting to the database. I can view database stucture/tables/links. I have not been able to view a report, form or any "front-end" views.
Being new to these programs and this level of programing, I don't know if I the programs listed above lack this functionality or if I haven't gone thru the correct procedures to run/view these samples.
Any help would be greatly appreciated.
Thanks in advance,
Randy
|||Wow - was I ever off the mark 
Ok - here's what you're looking for. I don't know about csproj - I don't think the web developer allows you to compile C# projects. You might need to invest in a full edition of Visual Studio 2005 C# to be able to compile these.
In order to handle rptproj you'll need to download the evaluation edition of reporting services (http://www.microsoft.com/sql/downloads/trial-software.mspx) and install the report designer component.
SQL Server Express (also SSX/SSE, adnausium) doesn't come with Reporting Services yet - we're working on a SKU that provides this functionality as we speak. So you'll need the evaluation edition of reporting services.
Hope that helps,
-Lukasz
This posting is provided "AS IS" with no warranties, and confers no rights.
Thursday, March 8, 2012
ADSI and Reporting Services Permission issue.
I have a stored proc that calls on a view in sql server. The view is
actually my Active Directory LDAP query. I have already created a
linked server and account for my user name jkim.
I have a report that polls data from this stored proc. After I set the
datasource which uses an account "sqluser", I try to the run the report
and I get this:
An error has occurred during report processing. (rsProcessingAborted)
Get Online Help
Query execution failed for data set 'DispatchActivity'.
(rsErrorExecutingCommand) Get Online Help
OLE DB provider 'ADSDSOObject' reported an error. The provider
indicates that the user did not have the permission to perform the
operation.
The data source uses the account "sqluser" which is an account that's
stored in the report server. I'm pretty sure this is what's causing
the error. Does 'sqluser' have to be an actual Active Directory user
for it to have access to the ADSDSOObject?
I don't know if I'm making any sense.nevermind, i figured it out.
i didn't check the "use as windows authentication" checkbox in the
datasource.
actually my Active Directory LDAP query. I have already created a
linked server and account for my user name jkim.
I have a report that polls data from this stored proc. After I set the
datasource which uses an account "sqluser", I try to the run the report
and I get this:
An error has occurred during report processing. (rsProcessingAborted)
Get Online Help
Query execution failed for data set 'DispatchActivity'.
(rsErrorExecutingCommand) Get Online Help
OLE DB provider 'ADSDSOObject' reported an error. The provider
indicates that the user did not have the permission to perform the
operation.
The data source uses the account "sqluser" which is an account that's
stored in the report server. I'm pretty sure this is what's causing
the error. Does 'sqluser' have to be an actual Active Directory user
for it to have access to the ADSDSOObject?
I don't know if I'm making any sense.nevermind, i figured it out.
i didn't check the "use as windows authentication" checkbox in the
datasource.
ADP vs. MDB: Speed
I have a situation with an ODBC linked view in an Access 2000 MDB with a SQL
7 back end. The view is scrolling very slowly. However, if I open the view
in an ADP file, it scrolls quickly.
I needed to use an ODBC link for the view because it needs to be editable.
Otherwise, I would have used a pass-through query.
In previous discussions about using an MDB file vs. an ADP file as a front
end for SQL Server, the impression I got was that both were about the same,
but that the MDB was a more mature technology and less problematic than the
ADP technology. However, the speed difference I'm noticing with the ADP file
in regards to this view is significant and is very disconcerting re. using
an MDB file.
Any thoughts/comments/suggestions would be appreciated. I've reproduced the
view's SQL below for reference.
Thanks,
Neil
SQL for view in question:
SELECT INVTRY.[Index], INVTRY.TITLE, INVTRY.AUTHILL1,
INVTRY.attFirstEdition, INVTRY.attSigned,
ISNULL(INVTRY.attSignedPD, ' ') SignedCond, INVTRY.YRPUB,
INVTRY.PRICE, INVTRY.Web, INVTRY.Status,
INVTRY.WebStatusPending, INVTRY.ActivateDate,
INVTRY.DeactivateDate, INVTRY.WebAddedBatchID,
INVTRY.AllowDuplicate, INVTRY.WebAction,
INVTRY.WebActionPending, INVTRY.DateModified,
INVTRY.DateWebActionApplied, INVTRY.JIT, INVTRY.MImage,
INVTRY.HImage, INVTRY.AdCode,
CASE WHEN INVTRY.WebAddedBatchID IS NOT NULL
THEN - 1 ELSE 0 END AS OnWeb
FROM vwInventory_Dupes INNER JOIN
(WebStatus INNER JOIN
(INVTRY INNER JOIN
tabStatus ON INVTRY.Status = tabStatus.Status) ON
WebStatus.WebStatus = INVTRY.Web) ON
(vwInventory_Dupes.YearPub = INVTRY.YRPUB) AND
(vwInventory_Dupes.SignedCond = ISNULL(INVTRY.attSignedPD,
' ')) AND (vwInventory_Dupes.Signed = INVTRY.attSigned) AND
(vwInventory_Dupes.FirstEd = INVTRY.attFirstEdition) AND
(vwInventory_Dupes.Author = INVTRY.AUTHILL1) AND
(vwInventory_Dupes.TITLE = INVTRY.TITLE)
WHERE (((tabStatus.ForWeb) = 1) AND ((WebStatus.IncludeDupe)
= 1))
SQL for vwInventory_Dupes, used as subquery:
SELECT INVTRY.TITLE, INVTRY.AUTHILL1 Author,
Cast(attFirstEdition AS tinyint) FirstEd,
Cast(attSigned AS tinyint) Signed,
ISNULL(INVTRY.attSignedPD, ' ') SignedCond,
INVTRY.YRPUB YearPub
FROM WebStatus INNER JOIN
(INVTRY INNER JOIN
tabStatus ON INVTRY.Status = tabStatus.Status) ON
WebStatus.WebStatus = INVTRY.Web
WHERE (((tabStatus.ForWeb) = 1) AND ((WebStatus.IncludeDupe)
= 1))
GROUP BY INVTRY.TITLE, INVTRY.AUTHILL1,
Cast(attFirstEdition AS tinyint), Cast(attSigned AS tinyint),
ISNULL(INVTRY.attSignedPD, ' '), INVTRY.YRPUB
HAVING (((COUNT(INVTRY.[INDEX])) > 1))
The ways ADPs and MDBs use recordsets are very different, so sometimes one or
the other will be faster.
By default, the MDB opens a Dynaset which means it first opens a connection to
grab the list of keys for the records, then reads the actual records as needed
for display by doing individual selects of 10 records aby explicit key. This
can help speed when each row has a lot of data because only the rows that are
actually viewd will be read, not all of them. On the other hand, if the table
has a very large number of rows, Access will spend a lot of time in the
background reading the whole list of keys, and if the key lookup for each row
is slow for some reason, everything will be sluggish.
An ADP, a static recordset is normally used, and an absolute cap of 10,000
rows is applied, so if your recordset has more than that, you just won't get
all the rows. Static means in loads all the data at once, then you browse
through it in memeory. That's very fast once you load the data, but if each
row has a lot of data, especially memo fields and such, it can take a long
time to initially load.
On Sun, 20 Mar 2005 08:31:48 GMT, "Neil" <njones@.pxdy.com> wrote:
>I have a situation with an ODBC linked view in an Access 2000 MDB with a SQL
>7 back end. The view is scrolling very slowly. However, if I open the view
>in an ADP file, it scrolls quickly.
>I needed to use an ODBC link for the view because it needs to be editable.
>Otherwise, I would have used a pass-through query.
>In previous discussions about using an MDB file vs. an ADP file as a front
>end for SQL Server, the impression I got was that both were about the same,
>but that the MDB was a more mature technology and less problematic than the
>ADP technology. However, the speed difference I'm noticing with the ADP file
>in regards to this view is significant and is very disconcerting re. using
>an MDB file.
>Any thoughts/comments/suggestions would be appreciated. I've reproduced the
>view's SQL below for reference.
>Thanks,
>Neil
>SQL for view in question:
>SELECT INVTRY.[Index], INVTRY.TITLE, INVTRY.AUTHILL1,
> INVTRY.attFirstEdition, INVTRY.attSigned,
> ISNULL(INVTRY.attSignedPD, ' ') SignedCond, INVTRY.YRPUB,
> INVTRY.PRICE, INVTRY.Web, INVTRY.Status,
> INVTRY.WebStatusPending, INVTRY.ActivateDate,
> INVTRY.DeactivateDate, INVTRY.WebAddedBatchID,
> INVTRY.AllowDuplicate, INVTRY.WebAction,
> INVTRY.WebActionPending, INVTRY.DateModified,
> INVTRY.DateWebActionApplied, INVTRY.JIT, INVTRY.MImage,
> INVTRY.HImage, INVTRY.AdCode,
> CASE WHEN INVTRY.WebAddedBatchID IS NOT NULL
> THEN - 1 ELSE 0 END AS OnWeb
>FROM vwInventory_Dupes INNER JOIN
> (WebStatus INNER JOIN
> (INVTRY INNER JOIN
> tabStatus ON INVTRY.Status = tabStatus.Status) ON
> WebStatus.WebStatus = INVTRY.Web) ON
> (vwInventory_Dupes.YearPub = INVTRY.YRPUB) AND
> (vwInventory_Dupes.SignedCond = ISNULL(INVTRY.attSignedPD,
> ' ')) AND (vwInventory_Dupes.Signed = INVTRY.attSigned) AND
> (vwInventory_Dupes.FirstEd = INVTRY.attFirstEdition) AND
> (vwInventory_Dupes.Author = INVTRY.AUTHILL1) AND
> (vwInventory_Dupes.TITLE = INVTRY.TITLE)
>WHERE (((tabStatus.ForWeb) = 1) AND ((WebStatus.IncludeDupe)
> = 1))
>SQL for vwInventory_Dupes, used as subquery:
>SELECT INVTRY.TITLE, INVTRY.AUTHILL1 Author,
> Cast(attFirstEdition AS tinyint) FirstEd,
> Cast(attSigned AS tinyint) Signed,
> ISNULL(INVTRY.attSignedPD, ' ') SignedCond,
> INVTRY.YRPUB YearPub
>FROM WebStatus INNER JOIN
> (INVTRY INNER JOIN
> tabStatus ON INVTRY.Status = tabStatus.Status) ON
> WebStatus.WebStatus = INVTRY.Web
>WHERE (((tabStatus.ForWeb) = 1) AND ((WebStatus.IncludeDupe)
> = 1))
>GROUP BY INVTRY.TITLE, INVTRY.AUTHILL1,
> Cast(attFirstEdition AS tinyint), Cast(attSigned AS tinyint),
> ISNULL(INVTRY.attSignedPD, ' '), INVTRY.YRPUB
>HAVING (((COUNT(INVTRY.[INDEX])) > 1))
>
|||You may try to add a virtual index on your linked view:
http://support.microsoft.com/kb/q209123/
If this doesn't help, then you will have to use an unbound form coupled with
SQL pass-through queries (or ADO objects) to resolve your speed problem with
MDB. Another solution could be to go with TS to simulate a high-speed LAN.
I don't know where you got the impression that MDB were about the same as
ADP in term of speed. It has been repeated a number of times that the use
of MDB' linked tables and views is only a workable solution for small
databases on a fast Lan.
S. L.
"Neil" <njones@.pxdy.com> wrote in message
news:Uxa%d.15606$cN6.15135@.newsread1.news.pas.eart hlink.net...
>I have a situation with an ODBC linked view in an Access 2000 MDB with a
>SQL 7 back end. The view is scrolling very slowly. However, if I open the
>view in an ADP file, it scrolls quickly.
> I needed to use an ODBC link for the view because it needs to be editable.
> Otherwise, I would have used a pass-through query.
> In previous discussions about using an MDB file vs. an ADP file as a front
> end for SQL Server, the impression I got was that both were about the
> same, but that the MDB was a more mature technology and less problematic
> than the ADP technology. However, the speed difference I'm noticing with
> the ADP file in regards to this view is significant and is very
> disconcerting re. using an MDB file.
> Any thoughts/comments/suggestions would be appreciated. I've reproduced
> the view's SQL below for reference.
> Thanks,
> Neil
> SQL for view in question:
> SELECT INVTRY.[Index], INVTRY.TITLE, INVTRY.AUTHILL1,
> INVTRY.attFirstEdition, INVTRY.attSigned,
> ISNULL(INVTRY.attSignedPD, ' ') SignedCond, INVTRY.YRPUB,
> INVTRY.PRICE, INVTRY.Web, INVTRY.Status,
> INVTRY.WebStatusPending, INVTRY.ActivateDate,
> INVTRY.DeactivateDate, INVTRY.WebAddedBatchID,
> INVTRY.AllowDuplicate, INVTRY.WebAction,
> INVTRY.WebActionPending, INVTRY.DateModified,
> INVTRY.DateWebActionApplied, INVTRY.JIT, INVTRY.MImage,
> INVTRY.HImage, INVTRY.AdCode,
> CASE WHEN INVTRY.WebAddedBatchID IS NOT NULL
> THEN - 1 ELSE 0 END AS OnWeb
> FROM vwInventory_Dupes INNER JOIN
> (WebStatus INNER JOIN
> (INVTRY INNER JOIN
> tabStatus ON INVTRY.Status = tabStatus.Status) ON
> WebStatus.WebStatus = INVTRY.Web) ON
> (vwInventory_Dupes.YearPub = INVTRY.YRPUB) AND
> (vwInventory_Dupes.SignedCond = ISNULL(INVTRY.attSignedPD,
> ' ')) AND (vwInventory_Dupes.Signed = INVTRY.attSigned) AND
> (vwInventory_Dupes.FirstEd = INVTRY.attFirstEdition) AND
> (vwInventory_Dupes.Author = INVTRY.AUTHILL1) AND
> (vwInventory_Dupes.TITLE = INVTRY.TITLE)
> WHERE (((tabStatus.ForWeb) = 1) AND ((WebStatus.IncludeDupe)
> = 1))
> SQL for vwInventory_Dupes, used as subquery:
> SELECT INVTRY.TITLE, INVTRY.AUTHILL1 Author,
> Cast(attFirstEdition AS tinyint) FirstEd,
> Cast(attSigned AS tinyint) Signed,
> ISNULL(INVTRY.attSignedPD, ' ') SignedCond,
> INVTRY.YRPUB YearPub
> FROM WebStatus INNER JOIN
> (INVTRY INNER JOIN
> tabStatus ON INVTRY.Status = tabStatus.Status) ON
> WebStatus.WebStatus = INVTRY.Web
> WHERE (((tabStatus.ForWeb) = 1) AND ((WebStatus.IncludeDupe)
> = 1))
> GROUP BY INVTRY.TITLE, INVTRY.AUTHILL1,
> Cast(attFirstEdition AS tinyint), Cast(attSigned AS tinyint),
> ISNULL(INVTRY.attSignedPD, ' '), INVTRY.YRPUB
> HAVING (((COUNT(INVTRY.[INDEX])) > 1))
>
|||On Sun, 20 Mar 2005 12:32:10 -0500, "Sylvain Lafontaine" <sylvain aei ca (fill
the blanks, no spam please)> wrote:
>You may try to add a virtual index on your linked view:
>http://support.microsoft.com/kb/q209123/
>If this doesn't help, then you will have to use an unbound form coupled with
>SQL pass-through queries (or ADO objects) to resolve your speed problem with
>MDB. Another solution could be to go with TS to simulate a high-speed LAN.
>I don't know where you got the impression that MDB were about the same as
>ADP in term of speed. It has been repeated a number of times that the use
>of MDB' linked tables and views is only a workable solution for small
>databases on a fast Lan.
Frankly, I find that statement ludicrous. Many developers including myself
have had excelent results using MDBs as front-ends to various kinds of SQL
Server back-end for many years before there was such a thing as an ADP. When
MDBs are slow, the workarounds to fix it are far less arduous than the
workarounds required in ADPs to simply make them function in many cases.
|||The view in question only returns 1154 rows. So we're not talking a large
number here. And only returning 24 fields.
There was a modification recently where three additional fields were added
to the view, and that seemed to slow things down significantly. I'm not sure
if there's some boundary at around 24 fields, or if something else is going
on. But the linked view is *very* slow, taking several seconds just to bring
up the first screen, and then, whenever the scroll bar goes down by one,
several more seconds just to refresh. The ADP view, on the other hand, is
fast, with no delay at all, either in bringing up the data or in scrolling.
Neil
"Steve Jorgensen" <nospam@.nospam.nospam> wrote in message
news:kq9r31tdjl3tv8v10kourkov1psurko5ug@.4ax.com...
> The ways ADPs and MDBs use recordsets are very different, so sometimes one
> or
> the other will be faster.
> By default, the MDB opens a Dynaset which means it first opens a
> connection to
> grab the list of keys for the records, then reads the actual records as
> needed
> for display by doing individual selects of 10 records aby explicit key.
> This
> can help speed when each row has a lot of data because only the rows that
> are
> actually viewd will be read, not all of them. On the other hand, if the
> table
> has a very large number of rows, Access will spend a lot of time in the
> background reading the whole list of keys, and if the key lookup for each
> row
> is slow for some reason, everything will be sluggish.
> An ADP, a static recordset is normally used, and an absolute cap of 10,000
> rows is applied, so if your recordset has more than that, you just won't
> get
> all the rows. Static means in loads all the data at once, then you browse
> through it in memeory. That's very fast once you load the data, but if
> each
> row has a lot of data, especially memo fields and such, it can take a long
> time to initially load.
> On Sun, 20 Mar 2005 08:31:48 GMT, "Neil" <njones@.pxdy.com> wrote:
>
|||> You may try to add a virtual index on your linked view:
> http://support.microsoft.com/kb/q209123/
Yes, these are added when you first attach the view or table. When the
object doesn't have a primary key (as with views) Access prompts you for
which field(s) to use as pk. It then stores that information. You can't
update the data otherwise. So it's already there.
> If this doesn't help, then you will have to use an unbound form coupled
> with SQL pass-through queries (or ADO objects) to resolve your speed
> problem with MDB.
As noted, the reason for not using pass-through is because it needs to be
updatable.
>Another solution could be to go with TS to simulate a high-speed LAN.
What is "TS"?
> I don't know where you got the impression that MDB were about the same as
> ADP in term of speed. It has been repeated a number of times that the use
> of MDB' linked tables and views is only a workable solution for small
> databases on a fast Lan.
From discussions in this newsgroup.
Neil
> S. L.
> "Neil" <njones@.pxdy.com> wrote in message
> news:Uxa%d.15606$cN6.15135@.newsread1.news.pas.eart hlink.net...
>
|||TS = Terminal Server; you can also use Citrix. This is a quick fix for
getting a multi-user access to an Access' MDB file over the Internet or to
solve the possibility of corruption when you LAN network is not rock solid.
However, this solution come with a price ($).
With the use of unbound forms as the solution, you are responsible for
sending the modifications to the data back to the database. This will give
you the possibility to use the result of a read-only SQL pass-through in a
form but, obviously, with the obligation of having more coding work to be
done. Of course, some of this work can be partially automated. You will
find more information on that subject on books dealing with Access and
SQL-Server. (Personally, I prefer to use ADP but it has many bugs.)
By using the profiler on the SQL-Server, it is also possible that you will
see something that will give you the possibility of resolving the speed
problem of you linked views by having a better understanding of what Access
is doing when it communicates with the server.
S. L.
"Neil" <njones@.pxdy.com> wrote in message
news:FCk%d.633$H06.566@.newsread3.news.pas.earthlin k.net...
> Yes, these are added when you first attach the view or table. When the
> object doesn't have a primary key (as with views) Access prompts you for
> which field(s) to use as pk. It then stores that information. You can't
> update the data otherwise. So it's already there.
>
> As noted, the reason for not using pass-through is because it needs to be
> updatable.
>
> What is "TS"?
>
> From discussions in this newsgroup.
> Neil
>
>
|||Are you talking about the WAN or a LAN?
In the case of the WAN, all tests that I have done in the past indicate that
MDB files are much slower to access a SQL-Server backend. The use of linked
views will greatly reduce the performance hit but even with them, I don't
see the benefice of creating hundred of views instead of creating hundred of
stored procedures. If you want to get some decent speed over the internet,
there are a lot more of work to do using MDB than using ADP and even then,
I'm not really sure if the use of linked views will achieve the same level
of performance.
You are entitled to your opinion but all the tests that I have done in the
past clearly indicate that the use of MDB to access a SQL-Server over the
internet is only, at its best, a kludge.
For a LAN, the problem is of course much less severe but at the condition
that the network is not already overcrowded.
S. L.
"Steve Jorgensen" <nospam@.nospam.nospam> wrote in message
news:vvgr31l3buv11blqbv3utfk22uhgoa3vab@.4ax.com...
> On Sun, 20 Mar 2005 12:32:10 -0500, "Sylvain Lafontaine" <sylvain aei ca
> (fill
> the blanks, no spam please)> wrote:
>
> Frankly, I find that statement ludicrous. Many developers including
> myself
> have had excelent results using MDBs as front-ends to various kinds of SQL
> Server back-end for many years before there was such a thing as an ADP.
> When
> MDBs are slow, the workarounds to fix it are far less arduous than the
> workarounds required in ADPs to simply make them function in many cases.
>
|||Did you specify a field or set of fields to be used as the primary key for the
view when you created the link? If not, it will be slower, and will not be
updateable.
On Sun, 20 Mar 2005 19:58:33 GMT, "Neil" <njones@.pxdy.com> wrote:
>The view in question only returns 1154 rows. So we're not talking a large
>number here. And only returning 24 fields.
>There was a modification recently where three additional fields were added
>to the view, and that seemed to slow things down significantly. I'm not sure
>if there's some boundary at around 24 fields, or if something else is going
>on. But the linked view is *very* slow, taking several seconds just to bring
>up the first screen, and then, whenever the scroll bar goes down by one,
>several more seconds just to refresh. The ADP view, on the other hand, is
>fast, with no delay at all, either in bringing up the data or in scrolling.
>Neil
>
>"Steve Jorgensen" <nospam@.nospam.nospam> wrote in message
>news:kq9r31tdjl3tv8v10kourkov1psurko5ug@.4ax.com.. .
>
|||Well, perhaps the reason for our difference of opinion is that I never thought
it was particularly appropriate to connect directly to a database server
across a WAN at all. I usually recommend using a terminal server or rewriting
the app as a Web application or some other kind of 3-tier application.
On Sun, 20 Mar 2005 16:49:55 -0500, "Sylvain Lafontaine" <sylvain aei ca (fill
the blanks, no spam please)> wrote:
>Are you talking about the WAN or a LAN?
>In the case of the WAN, all tests that I have done in the past indicate that
>MDB files are much slower to access a SQL-Server backend. The use of linked
>views will greatly reduce the performance hit but even with them, I don't
>see the benefice of creating hundred of views instead of creating hundred of
>stored procedures. If you want to get some decent speed over the internet,
>there are a lot more of work to do using MDB than using ADP and even then,
>I'm not really sure if the use of linked views will achieve the same level
>of performance.
>You are entitled to your opinion but all the tests that I have done in the
>past clearly indicate that the use of MDB to access a SQL-Server over the
>internet is only, at its best, a kludge.
>For a LAN, the problem is of course much less severe but at the condition
>that the network is not already overcrowded.
>S. L.
>"Steve Jorgensen" <nospam@.nospam.nospam> wrote in message
>news:vvgr31l3buv11blqbv3utfk22uhgoa3vab@.4ax.com.. .
>
7 back end. The view is scrolling very slowly. However, if I open the view
in an ADP file, it scrolls quickly.
I needed to use an ODBC link for the view because it needs to be editable.
Otherwise, I would have used a pass-through query.
In previous discussions about using an MDB file vs. an ADP file as a front
end for SQL Server, the impression I got was that both were about the same,
but that the MDB was a more mature technology and less problematic than the
ADP technology. However, the speed difference I'm noticing with the ADP file
in regards to this view is significant and is very disconcerting re. using
an MDB file.
Any thoughts/comments/suggestions would be appreciated. I've reproduced the
view's SQL below for reference.
Thanks,
Neil
SQL for view in question:
SELECT INVTRY.[Index], INVTRY.TITLE, INVTRY.AUTHILL1,
INVTRY.attFirstEdition, INVTRY.attSigned,
ISNULL(INVTRY.attSignedPD, ' ') SignedCond, INVTRY.YRPUB,
INVTRY.PRICE, INVTRY.Web, INVTRY.Status,
INVTRY.WebStatusPending, INVTRY.ActivateDate,
INVTRY.DeactivateDate, INVTRY.WebAddedBatchID,
INVTRY.AllowDuplicate, INVTRY.WebAction,
INVTRY.WebActionPending, INVTRY.DateModified,
INVTRY.DateWebActionApplied, INVTRY.JIT, INVTRY.MImage,
INVTRY.HImage, INVTRY.AdCode,
CASE WHEN INVTRY.WebAddedBatchID IS NOT NULL
THEN - 1 ELSE 0 END AS OnWeb
FROM vwInventory_Dupes INNER JOIN
(WebStatus INNER JOIN
(INVTRY INNER JOIN
tabStatus ON INVTRY.Status = tabStatus.Status) ON
WebStatus.WebStatus = INVTRY.Web) ON
(vwInventory_Dupes.YearPub = INVTRY.YRPUB) AND
(vwInventory_Dupes.SignedCond = ISNULL(INVTRY.attSignedPD,
' ')) AND (vwInventory_Dupes.Signed = INVTRY.attSigned) AND
(vwInventory_Dupes.FirstEd = INVTRY.attFirstEdition) AND
(vwInventory_Dupes.Author = INVTRY.AUTHILL1) AND
(vwInventory_Dupes.TITLE = INVTRY.TITLE)
WHERE (((tabStatus.ForWeb) = 1) AND ((WebStatus.IncludeDupe)
= 1))
SQL for vwInventory_Dupes, used as subquery:
SELECT INVTRY.TITLE, INVTRY.AUTHILL1 Author,
Cast(attFirstEdition AS tinyint) FirstEd,
Cast(attSigned AS tinyint) Signed,
ISNULL(INVTRY.attSignedPD, ' ') SignedCond,
INVTRY.YRPUB YearPub
FROM WebStatus INNER JOIN
(INVTRY INNER JOIN
tabStatus ON INVTRY.Status = tabStatus.Status) ON
WebStatus.WebStatus = INVTRY.Web
WHERE (((tabStatus.ForWeb) = 1) AND ((WebStatus.IncludeDupe)
= 1))
GROUP BY INVTRY.TITLE, INVTRY.AUTHILL1,
Cast(attFirstEdition AS tinyint), Cast(attSigned AS tinyint),
ISNULL(INVTRY.attSignedPD, ' '), INVTRY.YRPUB
HAVING (((COUNT(INVTRY.[INDEX])) > 1))
The ways ADPs and MDBs use recordsets are very different, so sometimes one or
the other will be faster.
By default, the MDB opens a Dynaset which means it first opens a connection to
grab the list of keys for the records, then reads the actual records as needed
for display by doing individual selects of 10 records aby explicit key. This
can help speed when each row has a lot of data because only the rows that are
actually viewd will be read, not all of them. On the other hand, if the table
has a very large number of rows, Access will spend a lot of time in the
background reading the whole list of keys, and if the key lookup for each row
is slow for some reason, everything will be sluggish.
An ADP, a static recordset is normally used, and an absolute cap of 10,000
rows is applied, so if your recordset has more than that, you just won't get
all the rows. Static means in loads all the data at once, then you browse
through it in memeory. That's very fast once you load the data, but if each
row has a lot of data, especially memo fields and such, it can take a long
time to initially load.
On Sun, 20 Mar 2005 08:31:48 GMT, "Neil" <njones@.pxdy.com> wrote:
>I have a situation with an ODBC linked view in an Access 2000 MDB with a SQL
>7 back end. The view is scrolling very slowly. However, if I open the view
>in an ADP file, it scrolls quickly.
>I needed to use an ODBC link for the view because it needs to be editable.
>Otherwise, I would have used a pass-through query.
>In previous discussions about using an MDB file vs. an ADP file as a front
>end for SQL Server, the impression I got was that both were about the same,
>but that the MDB was a more mature technology and less problematic than the
>ADP technology. However, the speed difference I'm noticing with the ADP file
>in regards to this view is significant and is very disconcerting re. using
>an MDB file.
>Any thoughts/comments/suggestions would be appreciated. I've reproduced the
>view's SQL below for reference.
>Thanks,
>Neil
>SQL for view in question:
>SELECT INVTRY.[Index], INVTRY.TITLE, INVTRY.AUTHILL1,
> INVTRY.attFirstEdition, INVTRY.attSigned,
> ISNULL(INVTRY.attSignedPD, ' ') SignedCond, INVTRY.YRPUB,
> INVTRY.PRICE, INVTRY.Web, INVTRY.Status,
> INVTRY.WebStatusPending, INVTRY.ActivateDate,
> INVTRY.DeactivateDate, INVTRY.WebAddedBatchID,
> INVTRY.AllowDuplicate, INVTRY.WebAction,
> INVTRY.WebActionPending, INVTRY.DateModified,
> INVTRY.DateWebActionApplied, INVTRY.JIT, INVTRY.MImage,
> INVTRY.HImage, INVTRY.AdCode,
> CASE WHEN INVTRY.WebAddedBatchID IS NOT NULL
> THEN - 1 ELSE 0 END AS OnWeb
>FROM vwInventory_Dupes INNER JOIN
> (WebStatus INNER JOIN
> (INVTRY INNER JOIN
> tabStatus ON INVTRY.Status = tabStatus.Status) ON
> WebStatus.WebStatus = INVTRY.Web) ON
> (vwInventory_Dupes.YearPub = INVTRY.YRPUB) AND
> (vwInventory_Dupes.SignedCond = ISNULL(INVTRY.attSignedPD,
> ' ')) AND (vwInventory_Dupes.Signed = INVTRY.attSigned) AND
> (vwInventory_Dupes.FirstEd = INVTRY.attFirstEdition) AND
> (vwInventory_Dupes.Author = INVTRY.AUTHILL1) AND
> (vwInventory_Dupes.TITLE = INVTRY.TITLE)
>WHERE (((tabStatus.ForWeb) = 1) AND ((WebStatus.IncludeDupe)
> = 1))
>SQL for vwInventory_Dupes, used as subquery:
>SELECT INVTRY.TITLE, INVTRY.AUTHILL1 Author,
> Cast(attFirstEdition AS tinyint) FirstEd,
> Cast(attSigned AS tinyint) Signed,
> ISNULL(INVTRY.attSignedPD, ' ') SignedCond,
> INVTRY.YRPUB YearPub
>FROM WebStatus INNER JOIN
> (INVTRY INNER JOIN
> tabStatus ON INVTRY.Status = tabStatus.Status) ON
> WebStatus.WebStatus = INVTRY.Web
>WHERE (((tabStatus.ForWeb) = 1) AND ((WebStatus.IncludeDupe)
> = 1))
>GROUP BY INVTRY.TITLE, INVTRY.AUTHILL1,
> Cast(attFirstEdition AS tinyint), Cast(attSigned AS tinyint),
> ISNULL(INVTRY.attSignedPD, ' '), INVTRY.YRPUB
>HAVING (((COUNT(INVTRY.[INDEX])) > 1))
>
|||You may try to add a virtual index on your linked view:
http://support.microsoft.com/kb/q209123/
If this doesn't help, then you will have to use an unbound form coupled with
SQL pass-through queries (or ADO objects) to resolve your speed problem with
MDB. Another solution could be to go with TS to simulate a high-speed LAN.
I don't know where you got the impression that MDB were about the same as
ADP in term of speed. It has been repeated a number of times that the use
of MDB' linked tables and views is only a workable solution for small
databases on a fast Lan.
S. L.
"Neil" <njones@.pxdy.com> wrote in message
news:Uxa%d.15606$cN6.15135@.newsread1.news.pas.eart hlink.net...
>I have a situation with an ODBC linked view in an Access 2000 MDB with a
>SQL 7 back end. The view is scrolling very slowly. However, if I open the
>view in an ADP file, it scrolls quickly.
> I needed to use an ODBC link for the view because it needs to be editable.
> Otherwise, I would have used a pass-through query.
> In previous discussions about using an MDB file vs. an ADP file as a front
> end for SQL Server, the impression I got was that both were about the
> same, but that the MDB was a more mature technology and less problematic
> than the ADP technology. However, the speed difference I'm noticing with
> the ADP file in regards to this view is significant and is very
> disconcerting re. using an MDB file.
> Any thoughts/comments/suggestions would be appreciated. I've reproduced
> the view's SQL below for reference.
> Thanks,
> Neil
> SQL for view in question:
> SELECT INVTRY.[Index], INVTRY.TITLE, INVTRY.AUTHILL1,
> INVTRY.attFirstEdition, INVTRY.attSigned,
> ISNULL(INVTRY.attSignedPD, ' ') SignedCond, INVTRY.YRPUB,
> INVTRY.PRICE, INVTRY.Web, INVTRY.Status,
> INVTRY.WebStatusPending, INVTRY.ActivateDate,
> INVTRY.DeactivateDate, INVTRY.WebAddedBatchID,
> INVTRY.AllowDuplicate, INVTRY.WebAction,
> INVTRY.WebActionPending, INVTRY.DateModified,
> INVTRY.DateWebActionApplied, INVTRY.JIT, INVTRY.MImage,
> INVTRY.HImage, INVTRY.AdCode,
> CASE WHEN INVTRY.WebAddedBatchID IS NOT NULL
> THEN - 1 ELSE 0 END AS OnWeb
> FROM vwInventory_Dupes INNER JOIN
> (WebStatus INNER JOIN
> (INVTRY INNER JOIN
> tabStatus ON INVTRY.Status = tabStatus.Status) ON
> WebStatus.WebStatus = INVTRY.Web) ON
> (vwInventory_Dupes.YearPub = INVTRY.YRPUB) AND
> (vwInventory_Dupes.SignedCond = ISNULL(INVTRY.attSignedPD,
> ' ')) AND (vwInventory_Dupes.Signed = INVTRY.attSigned) AND
> (vwInventory_Dupes.FirstEd = INVTRY.attFirstEdition) AND
> (vwInventory_Dupes.Author = INVTRY.AUTHILL1) AND
> (vwInventory_Dupes.TITLE = INVTRY.TITLE)
> WHERE (((tabStatus.ForWeb) = 1) AND ((WebStatus.IncludeDupe)
> = 1))
> SQL for vwInventory_Dupes, used as subquery:
> SELECT INVTRY.TITLE, INVTRY.AUTHILL1 Author,
> Cast(attFirstEdition AS tinyint) FirstEd,
> Cast(attSigned AS tinyint) Signed,
> ISNULL(INVTRY.attSignedPD, ' ') SignedCond,
> INVTRY.YRPUB YearPub
> FROM WebStatus INNER JOIN
> (INVTRY INNER JOIN
> tabStatus ON INVTRY.Status = tabStatus.Status) ON
> WebStatus.WebStatus = INVTRY.Web
> WHERE (((tabStatus.ForWeb) = 1) AND ((WebStatus.IncludeDupe)
> = 1))
> GROUP BY INVTRY.TITLE, INVTRY.AUTHILL1,
> Cast(attFirstEdition AS tinyint), Cast(attSigned AS tinyint),
> ISNULL(INVTRY.attSignedPD, ' '), INVTRY.YRPUB
> HAVING (((COUNT(INVTRY.[INDEX])) > 1))
>
|||On Sun, 20 Mar 2005 12:32:10 -0500, "Sylvain Lafontaine" <sylvain aei ca (fill
the blanks, no spam please)> wrote:
>You may try to add a virtual index on your linked view:
>http://support.microsoft.com/kb/q209123/
>If this doesn't help, then you will have to use an unbound form coupled with
>SQL pass-through queries (or ADO objects) to resolve your speed problem with
>MDB. Another solution could be to go with TS to simulate a high-speed LAN.
>I don't know where you got the impression that MDB were about the same as
>ADP in term of speed. It has been repeated a number of times that the use
>of MDB' linked tables and views is only a workable solution for small
>databases on a fast Lan.
Frankly, I find that statement ludicrous. Many developers including myself
have had excelent results using MDBs as front-ends to various kinds of SQL
Server back-end for many years before there was such a thing as an ADP. When
MDBs are slow, the workarounds to fix it are far less arduous than the
workarounds required in ADPs to simply make them function in many cases.
|||The view in question only returns 1154 rows. So we're not talking a large
number here. And only returning 24 fields.
There was a modification recently where three additional fields were added
to the view, and that seemed to slow things down significantly. I'm not sure
if there's some boundary at around 24 fields, or if something else is going
on. But the linked view is *very* slow, taking several seconds just to bring
up the first screen, and then, whenever the scroll bar goes down by one,
several more seconds just to refresh. The ADP view, on the other hand, is
fast, with no delay at all, either in bringing up the data or in scrolling.
Neil
"Steve Jorgensen" <nospam@.nospam.nospam> wrote in message
news:kq9r31tdjl3tv8v10kourkov1psurko5ug@.4ax.com...
> The ways ADPs and MDBs use recordsets are very different, so sometimes one
> or
> the other will be faster.
> By default, the MDB opens a Dynaset which means it first opens a
> connection to
> grab the list of keys for the records, then reads the actual records as
> needed
> for display by doing individual selects of 10 records aby explicit key.
> This
> can help speed when each row has a lot of data because only the rows that
> are
> actually viewd will be read, not all of them. On the other hand, if the
> table
> has a very large number of rows, Access will spend a lot of time in the
> background reading the whole list of keys, and if the key lookup for each
> row
> is slow for some reason, everything will be sluggish.
> An ADP, a static recordset is normally used, and an absolute cap of 10,000
> rows is applied, so if your recordset has more than that, you just won't
> get
> all the rows. Static means in loads all the data at once, then you browse
> through it in memeory. That's very fast once you load the data, but if
> each
> row has a lot of data, especially memo fields and such, it can take a long
> time to initially load.
> On Sun, 20 Mar 2005 08:31:48 GMT, "Neil" <njones@.pxdy.com> wrote:
>
|||> You may try to add a virtual index on your linked view:
> http://support.microsoft.com/kb/q209123/
Yes, these are added when you first attach the view or table. When the
object doesn't have a primary key (as with views) Access prompts you for
which field(s) to use as pk. It then stores that information. You can't
update the data otherwise. So it's already there.
> If this doesn't help, then you will have to use an unbound form coupled
> with SQL pass-through queries (or ADO objects) to resolve your speed
> problem with MDB.
As noted, the reason for not using pass-through is because it needs to be
updatable.
>Another solution could be to go with TS to simulate a high-speed LAN.
What is "TS"?
> I don't know where you got the impression that MDB were about the same as
> ADP in term of speed. It has been repeated a number of times that the use
> of MDB' linked tables and views is only a workable solution for small
> databases on a fast Lan.
From discussions in this newsgroup.
Neil
> S. L.
> "Neil" <njones@.pxdy.com> wrote in message
> news:Uxa%d.15606$cN6.15135@.newsread1.news.pas.eart hlink.net...
>
|||TS = Terminal Server; you can also use Citrix. This is a quick fix for
getting a multi-user access to an Access' MDB file over the Internet or to
solve the possibility of corruption when you LAN network is not rock solid.
However, this solution come with a price ($).
With the use of unbound forms as the solution, you are responsible for
sending the modifications to the data back to the database. This will give
you the possibility to use the result of a read-only SQL pass-through in a
form but, obviously, with the obligation of having more coding work to be
done. Of course, some of this work can be partially automated. You will
find more information on that subject on books dealing with Access and
SQL-Server. (Personally, I prefer to use ADP but it has many bugs.)
By using the profiler on the SQL-Server, it is also possible that you will
see something that will give you the possibility of resolving the speed
problem of you linked views by having a better understanding of what Access
is doing when it communicates with the server.
S. L.
"Neil" <njones@.pxdy.com> wrote in message
news:FCk%d.633$H06.566@.newsread3.news.pas.earthlin k.net...
> Yes, these are added when you first attach the view or table. When the
> object doesn't have a primary key (as with views) Access prompts you for
> which field(s) to use as pk. It then stores that information. You can't
> update the data otherwise. So it's already there.
>
> As noted, the reason for not using pass-through is because it needs to be
> updatable.
>
> What is "TS"?
>
> From discussions in this newsgroup.
> Neil
>
>
|||Are you talking about the WAN or a LAN?
In the case of the WAN, all tests that I have done in the past indicate that
MDB files are much slower to access a SQL-Server backend. The use of linked
views will greatly reduce the performance hit but even with them, I don't
see the benefice of creating hundred of views instead of creating hundred of
stored procedures. If you want to get some decent speed over the internet,
there are a lot more of work to do using MDB than using ADP and even then,
I'm not really sure if the use of linked views will achieve the same level
of performance.
You are entitled to your opinion but all the tests that I have done in the
past clearly indicate that the use of MDB to access a SQL-Server over the
internet is only, at its best, a kludge.
For a LAN, the problem is of course much less severe but at the condition
that the network is not already overcrowded.
S. L.
"Steve Jorgensen" <nospam@.nospam.nospam> wrote in message
news:vvgr31l3buv11blqbv3utfk22uhgoa3vab@.4ax.com...
> On Sun, 20 Mar 2005 12:32:10 -0500, "Sylvain Lafontaine" <sylvain aei ca
> (fill
> the blanks, no spam please)> wrote:
>
> Frankly, I find that statement ludicrous. Many developers including
> myself
> have had excelent results using MDBs as front-ends to various kinds of SQL
> Server back-end for many years before there was such a thing as an ADP.
> When
> MDBs are slow, the workarounds to fix it are far less arduous than the
> workarounds required in ADPs to simply make them function in many cases.
>
|||Did you specify a field or set of fields to be used as the primary key for the
view when you created the link? If not, it will be slower, and will not be
updateable.
On Sun, 20 Mar 2005 19:58:33 GMT, "Neil" <njones@.pxdy.com> wrote:
>The view in question only returns 1154 rows. So we're not talking a large
>number here. And only returning 24 fields.
>There was a modification recently where three additional fields were added
>to the view, and that seemed to slow things down significantly. I'm not sure
>if there's some boundary at around 24 fields, or if something else is going
>on. But the linked view is *very* slow, taking several seconds just to bring
>up the first screen, and then, whenever the scroll bar goes down by one,
>several more seconds just to refresh. The ADP view, on the other hand, is
>fast, with no delay at all, either in bringing up the data or in scrolling.
>Neil
>
>"Steve Jorgensen" <nospam@.nospam.nospam> wrote in message
>news:kq9r31tdjl3tv8v10kourkov1psurko5ug@.4ax.com.. .
>
|||Well, perhaps the reason for our difference of opinion is that I never thought
it was particularly appropriate to connect directly to a database server
across a WAN at all. I usually recommend using a terminal server or rewriting
the app as a Web application or some other kind of 3-tier application.
On Sun, 20 Mar 2005 16:49:55 -0500, "Sylvain Lafontaine" <sylvain aei ca (fill
the blanks, no spam please)> wrote:
>Are you talking about the WAN or a LAN?
>In the case of the WAN, all tests that I have done in the past indicate that
>MDB files are much slower to access a SQL-Server backend. The use of linked
>views will greatly reduce the performance hit but even with them, I don't
>see the benefice of creating hundred of views instead of creating hundred of
>stored procedures. If you want to get some decent speed over the internet,
>there are a lot more of work to do using MDB than using ADP and even then,
>I'm not really sure if the use of linked views will achieve the same level
>of performance.
>You are entitled to your opinion but all the tests that I have done in the
>past clearly indicate that the use of MDB to access a SQL-Server over the
>internet is only, at its best, a kludge.
>For a LAN, the problem is of course much less severe but at the condition
>that the network is not already overcrowded.
>S. L.
>"Steve Jorgensen" <nospam@.nospam.nospam> wrote in message
>news:vvgr31l3buv11blqbv3utfk22uhgoa3vab@.4ax.com.. .
>
ADP vs. MDB: Speed
I have a situation with an ODBC linked view in an Access 2000 MDB with a SQL
7 back end. The view is scrolling very slowly. However, if I open the view
in an ADP file, it scrolls quickly.
I needed to use an ODBC link for the view because it needs to be editable.
Otherwise, I would have used a pass-through query.
In previous discussions about using an MDB file vs. an ADP file as a front
end for SQL Server, the impression I got was that both were about the same,
but that the MDB was a more mature technology and less problematic than the
ADP technology. However, the speed difference I'm noticing with the ADP file
in regards to this view is significant and is very disconcerting re. using
an MDB file.
Any thoughts/comments/suggestions would be appreciated. I've reproduced the
view's SQL below for reference.
Thanks,
Neil
SQL for view in question:
SELECT INVTRY.[Index], INVTRY.TITLE, INVTRY.AUTHILL1,
INVTRY.attFirstEdition, INVTRY.attSigned,
ISNULL(INVTRY.attSignedPD, ' ') SignedCond, INVTRY.YRPUB,
INVTRY.PRICE, INVTRY.Web, INVTRY.Status,
INVTRY.WebStatusPending, INVTRY.ActivateDate,
INVTRY.DeactivateDate, INVTRY.WebAddedBatchID,
INVTRY.AllowDuplicate, INVTRY.WebAction,
INVTRY.WebActionPending, INVTRY.DateModified,
INVTRY.DateWebActionApplied, INVTRY.JIT, INVTRY.MImage,
INVTRY.HImage, INVTRY.AdCode,
CASE WHEN INVTRY.WebAddedBatchID IS NOT NULL
THEN - 1 ELSE 0 END AS OnWeb
FROM vwInventory_Dupes INNER JOIN
(WebStatus INNER JOIN
(INVTRY INNER JOIN
tabStatus ON INVTRY.Status = tabStatus.Status) ON
WebStatus.WebStatus = INVTRY.Web) ON
(vwInventory_Dupes.YearPub = INVTRY.YRPUB) AND
(vwInventory_Dupes.SignedCond = ISNULL(INVTRY.attSignedPD,
' ')) AND (vwInventory_Dupes.Signed = INVTRY.attSigned) AND
(vwInventory_Dupes.FirstEd = INVTRY.attFirstEdition) AND
(vwInventory_Dupes.Author = INVTRY.AUTHILL1) AND
(vwInventory_Dupes.TITLE = INVTRY.TITLE)
WHERE (((tabStatus.ForWeb) = 1) AND ((WebStatus.IncludeDupe)
= 1))
SQL for vwInventory_Dupes, used as subquery:
SELECT INVTRY.TITLE, INVTRY.AUTHILL1 Author,
Cast(attFirstEdition AS tinyint) FirstEd,
Cast(attSigned AS tinyint) Signed,
ISNULL(INVTRY.attSignedPD, ' ') SignedCond,
INVTRY.YRPUB YearPub
FROM WebStatus INNER JOIN
(INVTRY INNER JOIN
tabStatus ON INVTRY.Status = tabStatus.Status) ON
WebStatus.WebStatus = INVTRY.Web
WHERE (((tabStatus.ForWeb) = 1) AND ((WebStatus.IncludeDupe)
= 1))
GROUP BY INVTRY.TITLE, INVTRY.AUTHILL1,
Cast(attFirstEdition AS tinyint), Cast(attSigned AS tinyint),
ISNULL(INVTRY.attSignedPD, ' '), INVTRY.YRPUB
HAVING (((COUNT(INVTRY.[INDEX])) > 1))The ways ADPs and MDBs use recordsets are very different, so sometimes one o
r
the other will be faster.
By default, the MDB opens a Dynaset which means it first opens a connection
to
grab the list of keys for the records, then reads the actual records as need
ed
for display by doing individual selects of 10 records aby explicit key. Thi
s
can help speed when each row has a lot of data because only the rows that ar
e
actually viewd will be read, not all of them. On the other hand, if the tab
le
has a very large number of rows, Access will spend a lot of time in the
background reading the whole list of keys, and if the key lookup for each ro
w
is slow for some reason, everything will be sluggish.
An ADP, a static recordset is normally used, and an absolute cap of 10,000
rows is applied, so if your recordset has more than that, you just won't get
all the rows. Static means in loads all the data at once, then you browse
through it in memeory. That's very fast once you load the data, but if each
row has a lot of data, especially memo fields and such, it can take a long
time to initially load.
On Sun, 20 Mar 2005 08:31:48 GMT, "Neil" <njones@.pxdy.com> wrote:
>I have a situation with an ODBC linked view in an Access 2000 MDB with a SQ
L
>7 back end. The view is scrolling very slowly. However, if I open the view
>in an ADP file, it scrolls quickly.
>I needed to use an ODBC link for the view because it needs to be editable.
>Otherwise, I would have used a pass-through query.
>In previous discussions about using an MDB file vs. an ADP file as a front
>end for SQL Server, the impression I got was that both were about the same,
>but that the MDB was a more mature technology and less problematic than the
>ADP technology. However, the speed difference I'm noticing with the ADP fil
e
>in regards to this view is significant and is very disconcerting re. using
>an MDB file.
>Any thoughts/comments/suggestions would be appreciated. I've reproduced the
>view's SQL below for reference.
>Thanks,
>Neil
>SQL for view in question:
>SELECT INVTRY.[Index], INVTRY.TITLE, INVTRY.AUTHILL1,
> INVTRY.attFirstEdition, INVTRY.attSigned,
> ISNULL(INVTRY.attSignedPD, ' ') SignedCond, INVTRY.YRPUB,
> INVTRY.PRICE, INVTRY.Web, INVTRY.Status,
> INVTRY.WebStatusPending, INVTRY.ActivateDate,
> INVTRY.DeactivateDate, INVTRY.WebAddedBatchID,
> INVTRY.AllowDuplicate, INVTRY.WebAction,
> INVTRY.WebActionPending, INVTRY.DateModified,
> INVTRY.DateWebActionApplied, INVTRY.JIT, INVTRY.MImage,
> INVTRY.HImage, INVTRY.AdCode,
> CASE WHEN INVTRY.WebAddedBatchID IS NOT NULL
> THEN - 1 ELSE 0 END AS OnWeb
>FROM vwInventory_Dupes INNER JOIN
> (WebStatus INNER JOIN
> (INVTRY INNER JOIN
> tabStatus ON INVTRY.Status = tabStatus.Status) ON
> WebStatus.WebStatus = INVTRY.Web) ON
> (vwInventory_Dupes.YearPub = INVTRY.YRPUB) AND
> (vwInventory_Dupes.SignedCond = ISNULL(INVTRY.attSignedPD,
> ' ')) AND (vwInventory_Dupes.Signed = INVTRY.attSigned) AND
> (vwInventory_Dupes.FirstEd = INVTRY.attFirstEdition) AND
> (vwInventory_Dupes.Author = INVTRY.AUTHILL1) AND
> (vwInventory_Dupes.TITLE = INVTRY.TITLE)
>WHERE (((tabStatus.ForWeb) = 1) AND ((WebStatus.IncludeDupe)
> = 1))
>SQL for vwInventory_Dupes, used as subquery:
>SELECT INVTRY.TITLE, INVTRY.AUTHILL1 Author,
> Cast(attFirstEdition AS tinyint) FirstEd,
> Cast(attSigned AS tinyint) Signed,
> ISNULL(INVTRY.attSignedPD, ' ') SignedCond,
> INVTRY.YRPUB YearPub
>FROM WebStatus INNER JOIN
> (INVTRY INNER JOIN
> tabStatus ON INVTRY.Status = tabStatus.Status) ON
> WebStatus.WebStatus = INVTRY.Web
>WHERE (((tabStatus.ForWeb) = 1) AND ((WebStatus.IncludeDupe)
> = 1))
>GROUP BY INVTRY.TITLE, INVTRY.AUTHILL1,
> Cast(attFirstEdition AS tinyint), Cast(attSigned AS tinyint),
> ISNULL(INVTRY.attSignedPD, ' '), INVTRY.YRPUB
>HAVING (((COUNT(INVTRY.[INDEX])) > 1))
>|||You may try to add a virtual index on your linked view:
http://support.microsoft.com/kb/q209123/
If this doesn't help, then you will have to use an unbound form coupled with
SQL pass-through queries (or ADO objects) to resolve your speed problem with
MDB. Another solution could be to go with TS to simulate a high-speed LAN.
I don't know where you got the impression that MDB were about the same as
ADP in term of speed. It has been repeated a number of times that the use
of MDB' linked tables and views is only a workable solution for small
databases on a fast Lan.
S. L.
"Neil" <njones@.pxdy.com> wrote in message
news:Uxa%d.15606$cN6.15135@.newsread1.news.pas.earthlink.net...
>I have a situation with an ODBC linked view in an Access 2000 MDB with a
>SQL 7 back end. The view is scrolling very slowly. However, if I open the
>view in an ADP file, it scrolls quickly.
> I needed to use an ODBC link for the view because it needs to be editable.
> Otherwise, I would have used a pass-through query.
> In previous discussions about using an MDB file vs. an ADP file as a front
> end for SQL Server, the impression I got was that both were about the
> same, but that the MDB was a more mature technology and less problematic
> than the ADP technology. However, the speed difference I'm noticing with
> the ADP file in regards to this view is significant and is very
> disconcerting re. using an MDB file.
> Any thoughts/comments/suggestions would be appreciated. I've reproduced
> the view's SQL below for reference.
> Thanks,
> Neil
> SQL for view in question:
> SELECT INVTRY.[Index], INVTRY.TITLE, INVTRY.AUTHILL1,
> INVTRY.attFirstEdition, INVTRY.attSigned,
> ISNULL(INVTRY.attSignedPD, ' ') SignedCond, INVTRY.YRPUB,
> INVTRY.PRICE, INVTRY.Web, INVTRY.Status,
> INVTRY.WebStatusPending, INVTRY.ActivateDate,
> INVTRY.DeactivateDate, INVTRY.WebAddedBatchID,
> INVTRY.AllowDuplicate, INVTRY.WebAction,
> INVTRY.WebActionPending, INVTRY.DateModified,
> INVTRY.DateWebActionApplied, INVTRY.JIT, INVTRY.MImage,
> INVTRY.HImage, INVTRY.AdCode,
> CASE WHEN INVTRY.WebAddedBatchID IS NOT NULL
> THEN - 1 ELSE 0 END AS OnWeb
> FROM vwInventory_Dupes INNER JOIN
> (WebStatus INNER JOIN
> (INVTRY INNER JOIN
> tabStatus ON INVTRY.Status = tabStatus.Status) ON
> WebStatus.WebStatus = INVTRY.Web) ON
> (vwInventory_Dupes.YearPub = INVTRY.YRPUB) AND
> (vwInventory_Dupes.SignedCond = ISNULL(INVTRY.attSignedPD,
> ' ')) AND (vwInventory_Dupes.Signed = INVTRY.attSigned) AND
> (vwInventory_Dupes.FirstEd = INVTRY.attFirstEdition) AND
> (vwInventory_Dupes.Author = INVTRY.AUTHILL1) AND
> (vwInventory_Dupes.TITLE = INVTRY.TITLE)
> WHERE (((tabStatus.ForWeb) = 1) AND ((WebStatus.IncludeDupe)
> = 1))
> SQL for vwInventory_Dupes, used as subquery:
> SELECT INVTRY.TITLE, INVTRY.AUTHILL1 Author,
> Cast(attFirstEdition AS tinyint) FirstEd,
> Cast(attSigned AS tinyint) Signed,
> ISNULL(INVTRY.attSignedPD, ' ') SignedCond,
> INVTRY.YRPUB YearPub
> FROM WebStatus INNER JOIN
> (INVTRY INNER JOIN
> tabStatus ON INVTRY.Status = tabStatus.Status) ON
> WebStatus.WebStatus = INVTRY.Web
> WHERE (((tabStatus.ForWeb) = 1) AND ((WebStatus.IncludeDupe)
> = 1))
> GROUP BY INVTRY.TITLE, INVTRY.AUTHILL1,
> Cast(attFirstEdition AS tinyint), Cast(attSigned AS tinyint),
> ISNULL(INVTRY.attSignedPD, ' '), INVTRY.YRPUB
> HAVING (((COUNT(INVTRY.[INDEX])) > 1))
>|||The view in question only returns 1154 rows. So we're not talking a large
number here. And only returning 24 fields.
There was a modification recently where three additional fields were added
to the view, and that seemed to slow things down significantly. I'm not sure
if there's some boundary at around 24 fields, or if something else is going
on. But the linked view is *very* slow, taking several seconds just to bring
up the first screen, and then, whenever the scroll bar goes down by one,
several more seconds just to refresh. The ADP view, on the other hand, is
fast, with no delay at all, either in bringing up the data or in scrolling.
Neil
"Steve Jorgensen" <nospam@.nospam.nospam> wrote in message
news:kq9r31tdjl3tv8v10kourkov1psurko5ug@.
4ax.com...
> The ways ADPs and MDBs use recordsets are very different, so sometimes one
> or
> the other will be faster.
> By default, the MDB opens a Dynaset which means it first opens a
> connection to
> grab the list of keys for the records, then reads the actual records as
> needed
> for display by doing individual selects of 10 records aby explicit key.
> This
> can help speed when each row has a lot of data because only the rows that
> are
> actually viewd will be read, not all of them. On the other hand, if the
> table
> has a very large number of rows, Access will spend a lot of time in the
> background reading the whole list of keys, and if the key lookup for each
> row
> is slow for some reason, everything will be sluggish.
> An ADP, a static recordset is normally used, and an absolute cap of 10,000
> rows is applied, so if your recordset has more than that, you just won't
> get
> all the rows. Static means in loads all the data at once, then you browse
> through it in memeory. That's very fast once you load the data, but if
> each
> row has a lot of data, especially memo fields and such, it can take a long
> time to initially load.
> On Sun, 20 Mar 2005 08:31:48 GMT, "Neil" <njones@.pxdy.com> wrote:
>
>|||> You may try to add a virtual index on your linked view:
> http://support.microsoft.com/kb/q209123/
Yes, these are added when you first attach the view or table. When the
object doesn't have a primary key (as with views) Access prompts you for
which field(s) to use as pk. It then stores that information. You can't
update the data otherwise. So it's already there.
> If this doesn't help, then you will have to use an unbound form coupled
> with SQL pass-through queries (or ADO objects) to resolve your speed
> problem with MDB.
As noted, the reason for not using pass-through is because it needs to be
updatable.
>Another solution could be to go with TS to simulate a high-speed LAN.
What is "TS"?
> I don't know where you got the impression that MDB were about the same as
> ADP in term of speed. It has been repeated a number of times that the use
> of MDB' linked tables and views is only a workable solution for small
> databases on a fast Lan.
From discussions in this newsgroup.
Neil
> S. L.
> "Neil" <njones@.pxdy.com> wrote in message
> news:Uxa%d.15606$cN6.15135@.newsread1.news.pas.earthlink.net...
>|||TS = Terminal Server; you can also use Citrix. This is a quick fix for
getting a multi-user access to an Access' MDB file over the Internet or to
solve the possibility of corruption when you LAN network is not rock solid.
However, this solution come with a price ($).
With the use of unbound forms as the solution, you are responsible for
sending the modifications to the data back to the database. This will give
you the possibility to use the result of a read-only SQL pass-through in a
form but, obviously, with the obligation of having more coding work to be
done. Of course, some of this work can be partially automated. You will
find more information on that subject on books dealing with Access and
SQL-Server. (Personally, I prefer to use ADP but it has many bugs.)
By using the profiler on the SQL-Server, it is also possible that you will
see something that will give you the possibility of resolving the speed
problem of you linked views by having a better understanding of what Access
is doing when it communicates with the server.
S. L.
"Neil" <njones@.pxdy.com> wrote in message
news:FCk%d.633$H06.566@.newsread3.news.pas.earthlink.net...
> Yes, these are added when you first attach the view or table. When the
> object doesn't have a primary key (as with views) Access prompts you for
> which field(s) to use as pk. It then stores that information. You can't
> update the data otherwise. So it's already there.
>
> As noted, the reason for not using pass-through is because it needs to be
> updatable.
>
> What is "TS"?
>
> From discussions in this newsgroup.
> Neil
>
>|||Are you talking about the WAN or a LAN?
In the case of the WAN, all tests that I have done in the past indicate that
MDB files are much slower to access a SQL-Server backend. The use of linked
views will greatly reduce the performance hit but even with them, I don't
see the benefice of creating hundred of views instead of creating hundred of
stored procedures. If you want to get some decent speed over the internet,
there are a lot more of work to do using MDB than using ADP and even then,
I'm not really sure if the use of linked views will achieve the same level
of performance.
You are entitled to your opinion but all the tests that I have done in the
past clearly indicate that the use of MDB to access a SQL-Server over the
internet is only, at its best, a kludge.
For a LAN, the problem is of course much less severe but at the condition
that the network is not already overcrowded.
S. L.
"Steve Jorgensen" <nospam@.nospam.nospam> wrote in message
news:vvgr31l3buv11blqbv3utfk22uhgoa3vab@.
4ax.com...
> On Sun, 20 Mar 2005 12:32:10 -0500, "Sylvain Lafontaine" <sylvain aei ca
> (fill
> the blanks, no spam please)> wrote:
>
> Frankly, I find that statement ludicrous. Many developers including
> myself
> have had excelent results using MDBs as front-ends to various kinds of SQL
> Server back-end for many years before there was such a thing as an ADP.
> When
> MDBs are slow, the workarounds to fix it are far less arduous than the
> workarounds required in ADPs to simply make them function in many cases.
>|||Did you specify a field or set of fields to be used as the primary key for t
he
view when you created the link? If not, it will be slower, and will not be
updateable.
On Sun, 20 Mar 2005 19:58:33 GMT, "Neil" <njones@.pxdy.com> wrote:
>The view in question only returns 1154 rows. So we're not talking a large
>number here. And only returning 24 fields.
>There was a modification recently where three additional fields were added
>to the view, and that seemed to slow things down significantly. I'm not sur
e
>if there's some boundary at around 24 fields, or if something else is going
>on. But the linked view is *very* slow, taking several seconds just to brin
g
>up the first screen, and then, whenever the scroll bar goes down by one,
>several more seconds just to refresh. The ADP view, on the other hand, is
>fast, with no delay at all, either in bringing up the data or in scrolling.
>Neil
>
>"Steve Jorgensen" <nospam@.nospam.nospam> wrote in message
> news:kq9r31tdjl3tv8v10kourkov1psurko5ug@.
4ax.com...
>|||Well, perhaps the reason for our difference of opinion is that I never thoug
ht
it was particularly appropriate to connect directly to a database server
across a WAN at all. I usually recommend using a terminal server or rewriti
ng
the app as a Web application or some other kind of 3-tier application.
On Sun, 20 Mar 2005 16:49:55 -0500, "Sylvain Lafontaine" <sylvain aei ca (fi
ll
the blanks, no spam please)> wrote:
>Are you talking about the WAN or a LAN?
>In the case of the WAN, all tests that I have done in the past indicate tha
t
>MDB files are much slower to access a SQL-Server backend. The use of linke
d
>views will greatly reduce the performance hit but even with them, I don't
>see the benefice of creating hundred of views instead of creating hundred o
f
>stored procedures. If you want to get some decent speed over the internet,
>there are a lot more of work to do using MDB than using ADP and even then,
>I'm not really sure if the use of linked views will achieve the same level
>of performance.
>You are entitled to your opinion but all the tests that I have done in the
>past clearly indicate that the use of MDB to access a SQL-Server over the
>internet is only, at its best, a kludge.
>For a LAN, the problem is of course much less severe but at the condition
>that the network is not already overcrowded.
>S. L.
>"Steve Jorgensen" <nospam@.nospam.nospam> wrote in message
> news:vvgr31l3buv11blqbv3utfk22uhgoa3vab@.
4ax.com...
>|||"Neil" <njones@.pxdy.com> wrote in message
news:Uxa%d.15606$cN6.15135@.newsread1.news.pas.earthlink.net...
> In previous discussions about using an MDB file vs. an ADP file as a front
> end for SQL Server, the impression I got was that both were about the
> same, but that the MDB was a more mature technology and less problematic
> than the ADP technology. However, the speed difference I'm noticing with
> the ADP file in regards to this view is significant and is very
> disconcerting re. using an MDB file.
> Any thoughts/comments/suggestions would be appreciated. I've reproduced
> the view's SQL below for reference.
Yes, a few things needs to cleared up here.
First, using a ADP with sql server is kind of like using a automatic truck
vs a truck with a standard shift. If you don't know trucks, and how to shift
gears, then obviously the automatic truck is going to be better. On the
other hand, an advanced driver will get the same performance out of the
standard truck...
If you look at the sql you posted, there is a number of tables involved.
If you just link a bunch of tables to sql server, and then tell ms-access to
"join" them together..you will often get poor performance. I mean, obviously
ms-access/JET has to pull data from multiple tables. With ODBC linked
tables, each table is *often* thought as a separate data source. With
a ADP, that sql is processed server side.
Remember, ms-access/JET has a difficult
time working with multiple ODBC DATA sources when you do a join on them. The
reason of course is that one table might be from Oracle, and the other might
be a local FoxPro table. When you join data through linked tables, you have
to remem ber what the Robot in Lost in space used to say:
"Danger ...Will Robison....Danger"...
When you use a ADP, that whole sql statement is sent to sql server to be
processed.
So, of course, if you used a pass-through query, then both the ADP, and the
ODBC will performance the same.
However, you mentioned that you can't use a pass through. However, you STILL
are using a sql statement that have SEVERAL LINKED tables. ms-access is
going
to have to figure out those links for you!. This should not be a surprise if
you think about what is going on here. However, the simple solution here is
thus to simply create a view on sql server, and then link to that!
Doing this, you will get the same
performance again as the ADP.
So, the lesson here is that you simply have to be MORE conscience of sql
that joins tables together. You can often get away with at last one extra
joined table via link tables..but as a general rule, you have to build a
query
anyway, so, just built a view on the sql side..and you eliminate the
performance
problem.
So, our question is using a ADP going to be faster then a MDB with linked
tables?
answers: Yes, ADP will be faster if you don't pay attention to avoiding
bottle necks, and thinking about what is going on...
So, yes, you do need a bit more caution when using a mdb file and odbc
tables against sql server. Without question, a ADP allows you to be much
more lazy, as ALL
SQL is 100% executed on the sql server side, and thus it is MUCH harder to
screw things up. With a mdb/odbc linked tables, you need extra caution, and
extra effort to avoid bottle necks. In effect, the increased flexibility of
a
mdb/odbc setup means you have more rope and more room to hang yourself!!
However, with a small effort, and some caution, you can EASLEY get the same
performance with a mdb/odbc as you get with a
ADP.
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@.msn.com
http://www.members.shaw.ca/AlbertKallal
7 back end. The view is scrolling very slowly. However, if I open the view
in an ADP file, it scrolls quickly.
I needed to use an ODBC link for the view because it needs to be editable.
Otherwise, I would have used a pass-through query.
In previous discussions about using an MDB file vs. an ADP file as a front
end for SQL Server, the impression I got was that both were about the same,
but that the MDB was a more mature technology and less problematic than the
ADP technology. However, the speed difference I'm noticing with the ADP file
in regards to this view is significant and is very disconcerting re. using
an MDB file.
Any thoughts/comments/suggestions would be appreciated. I've reproduced the
view's SQL below for reference.
Thanks,
Neil
SQL for view in question:
SELECT INVTRY.[Index], INVTRY.TITLE, INVTRY.AUTHILL1,
INVTRY.attFirstEdition, INVTRY.attSigned,
ISNULL(INVTRY.attSignedPD, ' ') SignedCond, INVTRY.YRPUB,
INVTRY.PRICE, INVTRY.Web, INVTRY.Status,
INVTRY.WebStatusPending, INVTRY.ActivateDate,
INVTRY.DeactivateDate, INVTRY.WebAddedBatchID,
INVTRY.AllowDuplicate, INVTRY.WebAction,
INVTRY.WebActionPending, INVTRY.DateModified,
INVTRY.DateWebActionApplied, INVTRY.JIT, INVTRY.MImage,
INVTRY.HImage, INVTRY.AdCode,
CASE WHEN INVTRY.WebAddedBatchID IS NOT NULL
THEN - 1 ELSE 0 END AS OnWeb
FROM vwInventory_Dupes INNER JOIN
(WebStatus INNER JOIN
(INVTRY INNER JOIN
tabStatus ON INVTRY.Status = tabStatus.Status) ON
WebStatus.WebStatus = INVTRY.Web) ON
(vwInventory_Dupes.YearPub = INVTRY.YRPUB) AND
(vwInventory_Dupes.SignedCond = ISNULL(INVTRY.attSignedPD,
' ')) AND (vwInventory_Dupes.Signed = INVTRY.attSigned) AND
(vwInventory_Dupes.FirstEd = INVTRY.attFirstEdition) AND
(vwInventory_Dupes.Author = INVTRY.AUTHILL1) AND
(vwInventory_Dupes.TITLE = INVTRY.TITLE)
WHERE (((tabStatus.ForWeb) = 1) AND ((WebStatus.IncludeDupe)
= 1))
SQL for vwInventory_Dupes, used as subquery:
SELECT INVTRY.TITLE, INVTRY.AUTHILL1 Author,
Cast(attFirstEdition AS tinyint) FirstEd,
Cast(attSigned AS tinyint) Signed,
ISNULL(INVTRY.attSignedPD, ' ') SignedCond,
INVTRY.YRPUB YearPub
FROM WebStatus INNER JOIN
(INVTRY INNER JOIN
tabStatus ON INVTRY.Status = tabStatus.Status) ON
WebStatus.WebStatus = INVTRY.Web
WHERE (((tabStatus.ForWeb) = 1) AND ((WebStatus.IncludeDupe)
= 1))
GROUP BY INVTRY.TITLE, INVTRY.AUTHILL1,
Cast(attFirstEdition AS tinyint), Cast(attSigned AS tinyint),
ISNULL(INVTRY.attSignedPD, ' '), INVTRY.YRPUB
HAVING (((COUNT(INVTRY.[INDEX])) > 1))The ways ADPs and MDBs use recordsets are very different, so sometimes one o
r
the other will be faster.
By default, the MDB opens a Dynaset which means it first opens a connection
to
grab the list of keys for the records, then reads the actual records as need
ed
for display by doing individual selects of 10 records aby explicit key. Thi
s
can help speed when each row has a lot of data because only the rows that ar
e
actually viewd will be read, not all of them. On the other hand, if the tab
le
has a very large number of rows, Access will spend a lot of time in the
background reading the whole list of keys, and if the key lookup for each ro
w
is slow for some reason, everything will be sluggish.
An ADP, a static recordset is normally used, and an absolute cap of 10,000
rows is applied, so if your recordset has more than that, you just won't get
all the rows. Static means in loads all the data at once, then you browse
through it in memeory. That's very fast once you load the data, but if each
row has a lot of data, especially memo fields and such, it can take a long
time to initially load.
On Sun, 20 Mar 2005 08:31:48 GMT, "Neil" <njones@.pxdy.com> wrote:
>I have a situation with an ODBC linked view in an Access 2000 MDB with a SQ
L
>7 back end. The view is scrolling very slowly. However, if I open the view
>in an ADP file, it scrolls quickly.
>I needed to use an ODBC link for the view because it needs to be editable.
>Otherwise, I would have used a pass-through query.
>In previous discussions about using an MDB file vs. an ADP file as a front
>end for SQL Server, the impression I got was that both were about the same,
>but that the MDB was a more mature technology and less problematic than the
>ADP technology. However, the speed difference I'm noticing with the ADP fil
e
>in regards to this view is significant and is very disconcerting re. using
>an MDB file.
>Any thoughts/comments/suggestions would be appreciated. I've reproduced the
>view's SQL below for reference.
>Thanks,
>Neil
>SQL for view in question:
>SELECT INVTRY.[Index], INVTRY.TITLE, INVTRY.AUTHILL1,
> INVTRY.attFirstEdition, INVTRY.attSigned,
> ISNULL(INVTRY.attSignedPD, ' ') SignedCond, INVTRY.YRPUB,
> INVTRY.PRICE, INVTRY.Web, INVTRY.Status,
> INVTRY.WebStatusPending, INVTRY.ActivateDate,
> INVTRY.DeactivateDate, INVTRY.WebAddedBatchID,
> INVTRY.AllowDuplicate, INVTRY.WebAction,
> INVTRY.WebActionPending, INVTRY.DateModified,
> INVTRY.DateWebActionApplied, INVTRY.JIT, INVTRY.MImage,
> INVTRY.HImage, INVTRY.AdCode,
> CASE WHEN INVTRY.WebAddedBatchID IS NOT NULL
> THEN - 1 ELSE 0 END AS OnWeb
>FROM vwInventory_Dupes INNER JOIN
> (WebStatus INNER JOIN
> (INVTRY INNER JOIN
> tabStatus ON INVTRY.Status = tabStatus.Status) ON
> WebStatus.WebStatus = INVTRY.Web) ON
> (vwInventory_Dupes.YearPub = INVTRY.YRPUB) AND
> (vwInventory_Dupes.SignedCond = ISNULL(INVTRY.attSignedPD,
> ' ')) AND (vwInventory_Dupes.Signed = INVTRY.attSigned) AND
> (vwInventory_Dupes.FirstEd = INVTRY.attFirstEdition) AND
> (vwInventory_Dupes.Author = INVTRY.AUTHILL1) AND
> (vwInventory_Dupes.TITLE = INVTRY.TITLE)
>WHERE (((tabStatus.ForWeb) = 1) AND ((WebStatus.IncludeDupe)
> = 1))
>SQL for vwInventory_Dupes, used as subquery:
>SELECT INVTRY.TITLE, INVTRY.AUTHILL1 Author,
> Cast(attFirstEdition AS tinyint) FirstEd,
> Cast(attSigned AS tinyint) Signed,
> ISNULL(INVTRY.attSignedPD, ' ') SignedCond,
> INVTRY.YRPUB YearPub
>FROM WebStatus INNER JOIN
> (INVTRY INNER JOIN
> tabStatus ON INVTRY.Status = tabStatus.Status) ON
> WebStatus.WebStatus = INVTRY.Web
>WHERE (((tabStatus.ForWeb) = 1) AND ((WebStatus.IncludeDupe)
> = 1))
>GROUP BY INVTRY.TITLE, INVTRY.AUTHILL1,
> Cast(attFirstEdition AS tinyint), Cast(attSigned AS tinyint),
> ISNULL(INVTRY.attSignedPD, ' '), INVTRY.YRPUB
>HAVING (((COUNT(INVTRY.[INDEX])) > 1))
>|||You may try to add a virtual index on your linked view:
http://support.microsoft.com/kb/q209123/
If this doesn't help, then you will have to use an unbound form coupled with
SQL pass-through queries (or ADO objects) to resolve your speed problem with
MDB. Another solution could be to go with TS to simulate a high-speed LAN.
I don't know where you got the impression that MDB were about the same as
ADP in term of speed. It has been repeated a number of times that the use
of MDB' linked tables and views is only a workable solution for small
databases on a fast Lan.
S. L.
"Neil" <njones@.pxdy.com> wrote in message
news:Uxa%d.15606$cN6.15135@.newsread1.news.pas.earthlink.net...
>I have a situation with an ODBC linked view in an Access 2000 MDB with a
>SQL 7 back end. The view is scrolling very slowly. However, if I open the
>view in an ADP file, it scrolls quickly.
> I needed to use an ODBC link for the view because it needs to be editable.
> Otherwise, I would have used a pass-through query.
> In previous discussions about using an MDB file vs. an ADP file as a front
> end for SQL Server, the impression I got was that both were about the
> same, but that the MDB was a more mature technology and less problematic
> than the ADP technology. However, the speed difference I'm noticing with
> the ADP file in regards to this view is significant and is very
> disconcerting re. using an MDB file.
> Any thoughts/comments/suggestions would be appreciated. I've reproduced
> the view's SQL below for reference.
> Thanks,
> Neil
> SQL for view in question:
> SELECT INVTRY.[Index], INVTRY.TITLE, INVTRY.AUTHILL1,
> INVTRY.attFirstEdition, INVTRY.attSigned,
> ISNULL(INVTRY.attSignedPD, ' ') SignedCond, INVTRY.YRPUB,
> INVTRY.PRICE, INVTRY.Web, INVTRY.Status,
> INVTRY.WebStatusPending, INVTRY.ActivateDate,
> INVTRY.DeactivateDate, INVTRY.WebAddedBatchID,
> INVTRY.AllowDuplicate, INVTRY.WebAction,
> INVTRY.WebActionPending, INVTRY.DateModified,
> INVTRY.DateWebActionApplied, INVTRY.JIT, INVTRY.MImage,
> INVTRY.HImage, INVTRY.AdCode,
> CASE WHEN INVTRY.WebAddedBatchID IS NOT NULL
> THEN - 1 ELSE 0 END AS OnWeb
> FROM vwInventory_Dupes INNER JOIN
> (WebStatus INNER JOIN
> (INVTRY INNER JOIN
> tabStatus ON INVTRY.Status = tabStatus.Status) ON
> WebStatus.WebStatus = INVTRY.Web) ON
> (vwInventory_Dupes.YearPub = INVTRY.YRPUB) AND
> (vwInventory_Dupes.SignedCond = ISNULL(INVTRY.attSignedPD,
> ' ')) AND (vwInventory_Dupes.Signed = INVTRY.attSigned) AND
> (vwInventory_Dupes.FirstEd = INVTRY.attFirstEdition) AND
> (vwInventory_Dupes.Author = INVTRY.AUTHILL1) AND
> (vwInventory_Dupes.TITLE = INVTRY.TITLE)
> WHERE (((tabStatus.ForWeb) = 1) AND ((WebStatus.IncludeDupe)
> = 1))
> SQL for vwInventory_Dupes, used as subquery:
> SELECT INVTRY.TITLE, INVTRY.AUTHILL1 Author,
> Cast(attFirstEdition AS tinyint) FirstEd,
> Cast(attSigned AS tinyint) Signed,
> ISNULL(INVTRY.attSignedPD, ' ') SignedCond,
> INVTRY.YRPUB YearPub
> FROM WebStatus INNER JOIN
> (INVTRY INNER JOIN
> tabStatus ON INVTRY.Status = tabStatus.Status) ON
> WebStatus.WebStatus = INVTRY.Web
> WHERE (((tabStatus.ForWeb) = 1) AND ((WebStatus.IncludeDupe)
> = 1))
> GROUP BY INVTRY.TITLE, INVTRY.AUTHILL1,
> Cast(attFirstEdition AS tinyint), Cast(attSigned AS tinyint),
> ISNULL(INVTRY.attSignedPD, ' '), INVTRY.YRPUB
> HAVING (((COUNT(INVTRY.[INDEX])) > 1))
>|||The view in question only returns 1154 rows. So we're not talking a large
number here. And only returning 24 fields.
There was a modification recently where three additional fields were added
to the view, and that seemed to slow things down significantly. I'm not sure
if there's some boundary at around 24 fields, or if something else is going
on. But the linked view is *very* slow, taking several seconds just to bring
up the first screen, and then, whenever the scroll bar goes down by one,
several more seconds just to refresh. The ADP view, on the other hand, is
fast, with no delay at all, either in bringing up the data or in scrolling.
Neil
"Steve Jorgensen" <nospam@.nospam.nospam> wrote in message
news:kq9r31tdjl3tv8v10kourkov1psurko5ug@.
4ax.com...
> The ways ADPs and MDBs use recordsets are very different, so sometimes one
> or
> the other will be faster.
> By default, the MDB opens a Dynaset which means it first opens a
> connection to
> grab the list of keys for the records, then reads the actual records as
> needed
> for display by doing individual selects of 10 records aby explicit key.
> This
> can help speed when each row has a lot of data because only the rows that
> are
> actually viewd will be read, not all of them. On the other hand, if the
> table
> has a very large number of rows, Access will spend a lot of time in the
> background reading the whole list of keys, and if the key lookup for each
> row
> is slow for some reason, everything will be sluggish.
> An ADP, a static recordset is normally used, and an absolute cap of 10,000
> rows is applied, so if your recordset has more than that, you just won't
> get
> all the rows. Static means in loads all the data at once, then you browse
> through it in memeory. That's very fast once you load the data, but if
> each
> row has a lot of data, especially memo fields and such, it can take a long
> time to initially load.
> On Sun, 20 Mar 2005 08:31:48 GMT, "Neil" <njones@.pxdy.com> wrote:
>
>|||> You may try to add a virtual index on your linked view:
> http://support.microsoft.com/kb/q209123/
Yes, these are added when you first attach the view or table. When the
object doesn't have a primary key (as with views) Access prompts you for
which field(s) to use as pk. It then stores that information. You can't
update the data otherwise. So it's already there.
> If this doesn't help, then you will have to use an unbound form coupled
> with SQL pass-through queries (or ADO objects) to resolve your speed
> problem with MDB.
As noted, the reason for not using pass-through is because it needs to be
updatable.
>Another solution could be to go with TS to simulate a high-speed LAN.
What is "TS"?
> I don't know where you got the impression that MDB were about the same as
> ADP in term of speed. It has been repeated a number of times that the use
> of MDB' linked tables and views is only a workable solution for small
> databases on a fast Lan.
From discussions in this newsgroup.
Neil
> S. L.
> "Neil" <njones@.pxdy.com> wrote in message
> news:Uxa%d.15606$cN6.15135@.newsread1.news.pas.earthlink.net...
>|||TS = Terminal Server; you can also use Citrix. This is a quick fix for
getting a multi-user access to an Access' MDB file over the Internet or to
solve the possibility of corruption when you LAN network is not rock solid.
However, this solution come with a price ($).
With the use of unbound forms as the solution, you are responsible for
sending the modifications to the data back to the database. This will give
you the possibility to use the result of a read-only SQL pass-through in a
form but, obviously, with the obligation of having more coding work to be
done. Of course, some of this work can be partially automated. You will
find more information on that subject on books dealing with Access and
SQL-Server. (Personally, I prefer to use ADP but it has many bugs.)
By using the profiler on the SQL-Server, it is also possible that you will
see something that will give you the possibility of resolving the speed
problem of you linked views by having a better understanding of what Access
is doing when it communicates with the server.
S. L.
"Neil" <njones@.pxdy.com> wrote in message
news:FCk%d.633$H06.566@.newsread3.news.pas.earthlink.net...
> Yes, these are added when you first attach the view or table. When the
> object doesn't have a primary key (as with views) Access prompts you for
> which field(s) to use as pk. It then stores that information. You can't
> update the data otherwise. So it's already there.
>
> As noted, the reason for not using pass-through is because it needs to be
> updatable.
>
> What is "TS"?
>
> From discussions in this newsgroup.
> Neil
>
>|||Are you talking about the WAN or a LAN?
In the case of the WAN, all tests that I have done in the past indicate that
MDB files are much slower to access a SQL-Server backend. The use of linked
views will greatly reduce the performance hit but even with them, I don't
see the benefice of creating hundred of views instead of creating hundred of
stored procedures. If you want to get some decent speed over the internet,
there are a lot more of work to do using MDB than using ADP and even then,
I'm not really sure if the use of linked views will achieve the same level
of performance.
You are entitled to your opinion but all the tests that I have done in the
past clearly indicate that the use of MDB to access a SQL-Server over the
internet is only, at its best, a kludge.
For a LAN, the problem is of course much less severe but at the condition
that the network is not already overcrowded.
S. L.
"Steve Jorgensen" <nospam@.nospam.nospam> wrote in message
news:vvgr31l3buv11blqbv3utfk22uhgoa3vab@.
4ax.com...
> On Sun, 20 Mar 2005 12:32:10 -0500, "Sylvain Lafontaine" <sylvain aei ca
> (fill
> the blanks, no spam please)> wrote:
>
> Frankly, I find that statement ludicrous. Many developers including
> myself
> have had excelent results using MDBs as front-ends to various kinds of SQL
> Server back-end for many years before there was such a thing as an ADP.
> When
> MDBs are slow, the workarounds to fix it are far less arduous than the
> workarounds required in ADPs to simply make them function in many cases.
>|||Did you specify a field or set of fields to be used as the primary key for t
he
view when you created the link? If not, it will be slower, and will not be
updateable.
On Sun, 20 Mar 2005 19:58:33 GMT, "Neil" <njones@.pxdy.com> wrote:
>The view in question only returns 1154 rows. So we're not talking a large
>number here. And only returning 24 fields.
>There was a modification recently where three additional fields were added
>to the view, and that seemed to slow things down significantly. I'm not sur
e
>if there's some boundary at around 24 fields, or if something else is going
>on. But the linked view is *very* slow, taking several seconds just to brin
g
>up the first screen, and then, whenever the scroll bar goes down by one,
>several more seconds just to refresh. The ADP view, on the other hand, is
>fast, with no delay at all, either in bringing up the data or in scrolling.
>Neil
>
>"Steve Jorgensen" <nospam@.nospam.nospam> wrote in message
> news:kq9r31tdjl3tv8v10kourkov1psurko5ug@.
4ax.com...
>|||Well, perhaps the reason for our difference of opinion is that I never thoug
ht
it was particularly appropriate to connect directly to a database server
across a WAN at all. I usually recommend using a terminal server or rewriti
ng
the app as a Web application or some other kind of 3-tier application.
On Sun, 20 Mar 2005 16:49:55 -0500, "Sylvain Lafontaine" <sylvain aei ca (fi
ll
the blanks, no spam please)> wrote:
>Are you talking about the WAN or a LAN?
>In the case of the WAN, all tests that I have done in the past indicate tha
t
>MDB files are much slower to access a SQL-Server backend. The use of linke
d
>views will greatly reduce the performance hit but even with them, I don't
>see the benefice of creating hundred of views instead of creating hundred o
f
>stored procedures. If you want to get some decent speed over the internet,
>there are a lot more of work to do using MDB than using ADP and even then,
>I'm not really sure if the use of linked views will achieve the same level
>of performance.
>You are entitled to your opinion but all the tests that I have done in the
>past clearly indicate that the use of MDB to access a SQL-Server over the
>internet is only, at its best, a kludge.
>For a LAN, the problem is of course much less severe but at the condition
>that the network is not already overcrowded.
>S. L.
>"Steve Jorgensen" <nospam@.nospam.nospam> wrote in message
> news:vvgr31l3buv11blqbv3utfk22uhgoa3vab@.
4ax.com...
>|||"Neil" <njones@.pxdy.com> wrote in message
news:Uxa%d.15606$cN6.15135@.newsread1.news.pas.earthlink.net...
> In previous discussions about using an MDB file vs. an ADP file as a front
> end for SQL Server, the impression I got was that both were about the
> same, but that the MDB was a more mature technology and less problematic
> than the ADP technology. However, the speed difference I'm noticing with
> the ADP file in regards to this view is significant and is very
> disconcerting re. using an MDB file.
> Any thoughts/comments/suggestions would be appreciated. I've reproduced
> the view's SQL below for reference.
Yes, a few things needs to cleared up here.
First, using a ADP with sql server is kind of like using a automatic truck
vs a truck with a standard shift. If you don't know trucks, and how to shift
gears, then obviously the automatic truck is going to be better. On the
other hand, an advanced driver will get the same performance out of the
standard truck...
If you look at the sql you posted, there is a number of tables involved.
If you just link a bunch of tables to sql server, and then tell ms-access to
"join" them together..you will often get poor performance. I mean, obviously
ms-access/JET has to pull data from multiple tables. With ODBC linked
tables, each table is *often* thought as a separate data source. With
a ADP, that sql is processed server side.
Remember, ms-access/JET has a difficult
time working with multiple ODBC DATA sources when you do a join on them. The
reason of course is that one table might be from Oracle, and the other might
be a local FoxPro table. When you join data through linked tables, you have
to remem ber what the Robot in Lost in space used to say:
"Danger ...Will Robison....Danger"...
When you use a ADP, that whole sql statement is sent to sql server to be
processed.
So, of course, if you used a pass-through query, then both the ADP, and the
ODBC will performance the same.
However, you mentioned that you can't use a pass through. However, you STILL
are using a sql statement that have SEVERAL LINKED tables. ms-access is
going
to have to figure out those links for you!. This should not be a surprise if
you think about what is going on here. However, the simple solution here is
thus to simply create a view on sql server, and then link to that!
Doing this, you will get the same
performance again as the ADP.
So, the lesson here is that you simply have to be MORE conscience of sql
that joins tables together. You can often get away with at last one extra
joined table via link tables..but as a general rule, you have to build a
query
anyway, so, just built a view on the sql side..and you eliminate the
performance
problem.
So, our question is using a ADP going to be faster then a MDB with linked
tables?
answers: Yes, ADP will be faster if you don't pay attention to avoiding
bottle necks, and thinking about what is going on...
So, yes, you do need a bit more caution when using a mdb file and odbc
tables against sql server. Without question, a ADP allows you to be much
more lazy, as ALL
SQL is 100% executed on the sql server side, and thus it is MUCH harder to
screw things up. With a mdb/odbc linked tables, you need extra caution, and
extra effort to avoid bottle necks. In effect, the increased flexibility of
a
mdb/odbc setup means you have more rope and more room to hang yourself!!
However, with a small effort, and some caution, you can EASLEY get the same
performance with a mdb/odbc as you get with a
ADP.
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@.msn.com
http://www.members.shaw.ca/AlbertKallal
Tuesday, March 6, 2012
adp connection to msde and exclusive rights
I am attempting to create a View from MS Access 2000 SR-1 to an MSDE 2000
(SQL Server 2000 Desktop Engine) database . I accessed with exclusive rights
but I get the following error when trying to create a view:
"You do not have exclusive access to the database at this time. Your design
changes will not be saved."
how can I get ability to create views ?
I'd recommend not using Access as a development tool for SQL Server
objects. Use the Query Analyzer or Enterprise Manager instead. The
error message you're getting is an Access error, not a SQL Server
error.
--Mary
On Thu, 24 Jun 2004 01:08:50 -0700, "TJS" <nospam@.here.com> wrote:
>I am attempting to create a View from MS Access 2000 SR-1 to an MSDE 2000
>(SQL Server 2000 Desktop Engine) database . I accessed with exclusive rights
>but I get the following error when trying to create a view:
>"You do not have exclusive access to the database at this time. Your design
>changes will not be saved."
>how can I get ability to create views ?
>
|||Hi Mary:
Thanks for replying (I have your book, Developer's guide to SQL Server).
Unfortunately, I don't have the Query Analyzer or Enterprise Manager , so I
was hoping to use access as a front end.
is there any way access can be a front end to msde ?
"Mary Chipman" <mchip@.online.microsoft.com> wrote in message
news:n74md01glum1t5l2c79ak6sjmj11sct03m@.4ax.com... [vbcol=seagreen]
> I'd recommend not using Access as a development tool for SQL Server
> objects. Use the Query Analyzer or Enterprise Manager instead. The
> error message you're getting is an Access error, not a SQL Server
> error.
> --Mary
> On Thu, 24 Jun 2004 01:08:50 -0700, "TJS" <nospam@.here.com> wrote:
rights[vbcol=seagreen]
design
>
|||MSDE is not a development platform, it's a deployment platform. I would
suggest that you spend the <$50 required to purchase SQL Server Developer
Edition. It contains everything you'll need to develop for the SQL Server
and MSDE platforms (really the same thing).
I'll even go out on a limb and predict that Mary will say the same thing.
:-)
Yes, you can use Access as a front end to MSDE, but you'll likely find the
tools she mentioned easier to use to create and manage your database.
Sincerely,
Stephen Dybing
This posting is provided "AS IS" with no warranties, and confers no rights.
"TJS" <nospam@.here.com> wrote in message
news:10dm523btum1rd5@.corp.supernews.com...
> Hi Mary:
> Thanks for replying (I have your book, Developer's guide to SQL Server).
> Unfortunately, I don't have the Query Analyzer or Enterprise Manager , so
I[vbcol=seagreen]
> was hoping to use access as a front end.
> is there any way access can be a front end to msde ?
>
>
> "Mary Chipman" <mchip@.online.microsoft.com> wrote in message
> news:n74md01glum1t5l2c79ak6sjmj11sct03m@.4ax.com...
2000
> rights
> design
>
|||I just want to create a view...
"Stephen Dybing [MSFT]" <stephd@.online.microsoft.com> wrote in message
news:OBa0OViWEHA.2844@.TK2MSFTNGP11.phx.gbl...
> MSDE is not a development platform, it's a deployment platform. I would
> suggest that you spend the <$50 required to purchase SQL Server Developer
> Edition. It contains everything you'll need to develop for the SQL Server
> and MSDE platforms (really the same thing).
> I'll even go out on a limb and predict that Mary will say the same thing.
> :-)
> Yes, you can use Access as a front end to MSDE, but you'll likely find the
> tools she mentioned easier to use to create and manage your database.
> --
> Sincerely,
> Stephen Dybing
> This posting is provided "AS IS" with no warranties, and confers no
rights.[vbcol=seagreen]
> "TJS" <nospam@.here.com> wrote in message
> news:10dm523btum1rd5@.corp.supernews.com...
so
> I
> 2000
>
(SQL Server 2000 Desktop Engine) database . I accessed with exclusive rights
but I get the following error when trying to create a view:
"You do not have exclusive access to the database at this time. Your design
changes will not be saved."
how can I get ability to create views ?
I'd recommend not using Access as a development tool for SQL Server
objects. Use the Query Analyzer or Enterprise Manager instead. The
error message you're getting is an Access error, not a SQL Server
error.
--Mary
On Thu, 24 Jun 2004 01:08:50 -0700, "TJS" <nospam@.here.com> wrote:
>I am attempting to create a View from MS Access 2000 SR-1 to an MSDE 2000
>(SQL Server 2000 Desktop Engine) database . I accessed with exclusive rights
>but I get the following error when trying to create a view:
>"You do not have exclusive access to the database at this time. Your design
>changes will not be saved."
>how can I get ability to create views ?
>
|||Hi Mary:
Thanks for replying (I have your book, Developer's guide to SQL Server).
Unfortunately, I don't have the Query Analyzer or Enterprise Manager , so I
was hoping to use access as a front end.
is there any way access can be a front end to msde ?
"Mary Chipman" <mchip@.online.microsoft.com> wrote in message
news:n74md01glum1t5l2c79ak6sjmj11sct03m@.4ax.com... [vbcol=seagreen]
> I'd recommend not using Access as a development tool for SQL Server
> objects. Use the Query Analyzer or Enterprise Manager instead. The
> error message you're getting is an Access error, not a SQL Server
> error.
> --Mary
> On Thu, 24 Jun 2004 01:08:50 -0700, "TJS" <nospam@.here.com> wrote:
rights[vbcol=seagreen]
design
>
|||MSDE is not a development platform, it's a deployment platform. I would
suggest that you spend the <$50 required to purchase SQL Server Developer
Edition. It contains everything you'll need to develop for the SQL Server
and MSDE platforms (really the same thing).
I'll even go out on a limb and predict that Mary will say the same thing.
:-)
Yes, you can use Access as a front end to MSDE, but you'll likely find the
tools she mentioned easier to use to create and manage your database.
Sincerely,
Stephen Dybing
This posting is provided "AS IS" with no warranties, and confers no rights.
"TJS" <nospam@.here.com> wrote in message
news:10dm523btum1rd5@.corp.supernews.com...
> Hi Mary:
> Thanks for replying (I have your book, Developer's guide to SQL Server).
> Unfortunately, I don't have the Query Analyzer or Enterprise Manager , so
I[vbcol=seagreen]
> was hoping to use access as a front end.
> is there any way access can be a front end to msde ?
>
>
> "Mary Chipman" <mchip@.online.microsoft.com> wrote in message
> news:n74md01glum1t5l2c79ak6sjmj11sct03m@.4ax.com...
2000
> rights
> design
>
|||I just want to create a view...
"Stephen Dybing [MSFT]" <stephd@.online.microsoft.com> wrote in message
news:OBa0OViWEHA.2844@.TK2MSFTNGP11.phx.gbl...
> MSDE is not a development platform, it's a deployment platform. I would
> suggest that you spend the <$50 required to purchase SQL Server Developer
> Edition. It contains everything you'll need to develop for the SQL Server
> and MSDE platforms (really the same thing).
> I'll even go out on a limb and predict that Mary will say the same thing.
> :-)
> Yes, you can use Access as a front end to MSDE, but you'll likely find the
> tools she mentioned easier to use to create and manage your database.
> --
> Sincerely,
> Stephen Dybing
> This posting is provided "AS IS" with no warranties, and confers no
rights.[vbcol=seagreen]
> "TJS" <nospam@.here.com> wrote in message
> news:10dm523btum1rd5@.corp.supernews.com...
so
> I
> 2000
>
Saturday, February 25, 2012
ADO.NET returns different colum value when compared to View results in SQL 2005 Management
I have a complex view in my sql 2005 database.
The view returns a column that could be null (as the result of a left outer join).
The coulmn that is returned is an integer.
Everything works fine if I run the view from SQL 2005 Management Studio.
My column value is always null if I use ADO.NET's SqlAdapter to return a DataTable.
Has anybody seen this behaviour before?
Any help appreciated.
Regards,
Paul.
Hi Paul,
From you description, I understand that when you query data from the database view, the destination column in the DataSet is always null.
This could be caused by a wrongly configured SqlDataAdapter or the mismatched target DataSet schema.
For example, if you're using a typed DataSet, the target column data type is in another type. In this case, data might be converted incorrectly. Also, please try to check the table mapping and column mapping for the SqlDataAdapter.
HTH.
|||I've solved the problem
I changed the DB Connection to system admin and everything worked ok.
Looks like I've got a permissions issue with one or more of my SQL Functions.
Thanks for the help.
Regards,
Paul.
Sunday, February 19, 2012
ADO puts EXEC in front of my SQL statements?
Hi there,
I'm tracking down a syntax error and have an odd problem.
I'm using the SQL Trace tool to view SQL arriving at the server.
Now, there's two problems...
SQL Server keeps on putting EXEC in front of my statements -
eg:
ENABLE TRIGGER ddlDatabaseTriggerLog ON DATABASE
is changed to
exec ENABLE TRIGGER ddlDatabaseTriggerLog ON DATABASE
And the second problem is that any cursor/dataset is created like:
declare @.p1 int
set @.p1=2
exec sp_prepexec @.p1 output,NULL,N'<my statement>'
select @.p1
Does anyone have an idea why?
--
With regards,
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com
Database development questions? Check the forum!
http://www.databasedevelopmentforum.comBecause you are embedding dynamic SQL. Best practice is to put your
code into stored procs and call the procs from ADO.
David Portas
SQL Server MVP
--|||Hello David,
> Because you are embedding dynamic SQL. Best practice is to put your
> code into stored procs and call the procs from ADO.
In this case, not an option :-)
Funny, ad-hoc queries in SQL Server Management Studio or in the
Query Analyzer don't "suffer" from this problem.
Why is that?
With regards,
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com|||Query Analyzer uses ODBC, not ADO, that's why :)
Jacco Schalkwijk
SQL Server MVP
"Martijn Tonies" <m.tonies@.upscene-removethis.nospam.com> wrote in message
news:%23C1BYjZyFHA.3320@.TK2MSFTNGP14.phx.gbl...
> Hello David,
>
> In this case, not an option :-)
> Funny, ad-hoc queries in SQL Server Management Studio or in the
> Query Analyzer don't "suffer" from this problem.
> Why is that?
>
> --
> With regards,
> Martijn Tonies
> Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
> Server
> Upscene Productions
> http://www.upscene.com
> Database development questions? Check the forum!
> http://www.databasedevelopmentforum.com
>|||
> Query Analyzer uses ODBC, not ADO, that's why :)
Hmm and I thought the ODBC driver wasn't updated to support SQL 2000.
Ah well...
Anyway, how can I get rid of this EXEC stuff then?
With regards,
Martijn Tonies
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Martijn Tonies" <m.tonies@.upscene-removethis.nospam.com> wrote in message
> news:%23C1BYjZyFHA.3320@.TK2MSFTNGP14.phx.gbl...
SQL
>|||Just to make sure that we are talking about the same thing: you are
using ADO 2.x, not ADO.NET, right ? And you are using SQL Server 2000
or something else ?
Razvan|||> Just to make sure that we are talking about the same thing: you are
> using ADO 2.x, not ADO.NET, right ? And you are using SQL Server 2000
> or something else ?
No ADO.NET, just plain old ADO.
SQL 2000 and 2005, for that matter.
Haven't tried SQL 7 on this particular thingy yet...
With regards,
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com|||I'm not an ADO expert, but IIRC there is a method you can call on the
connection (or command) that just sends the SQL to the server with adding
the EXEC stuff. Which one that is you'll have to look up in the ADO
documentation or ask on an ado newsgroup. It might be the CommandType. You
can try setting the CommandType to adCmdText.
Jacco Schalkwijk
SQL Server MVP
"Martijn Tonies" <m.tonies@.upscene-removethis.nospam.com> wrote in message
news:OnYpjrayFHA.460@.TK2MSFTNGP15.phx.gbl...
>
> Hmm and I thought the ODBC driver wasn't updated to support SQL 2000.
> Ah well...
> Anyway, how can I get rid of this EXEC stuff then?
>
> --
> With regards,
> Martijn Tonies
>
> SQL
>|||
> I'm not an ADO expert, but IIRC there is a method you can call on the
> connection (or command) that just sends the SQL to the server with adding
> the EXEC stuff. Which one that is you'll have to look up in the ADO
> documentation or ask on an ado newsgroup. It might be the CommandType. You
> can try setting the CommandType to adCmdText.
It's already adCmdText :-/
I'll have a look at the docs then ... couldn't find it before ...
With regards,
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com|||Hello, Martijn
In SQL Server 2000, "ENABLE TRIGGER ddlDatabaseTriggerLog ON DATABASE"
is not a valid statement. Try a "ALTER TABLE YourTable ENABLE TRIGGER
SomeNormalTrigger" and it should work. If ADO doesn't recognize your
CommandText as being a valid SQL statement, it tries to add an "exec "
or a "select * from " guessing that it could be a procedure name or a
table/view name.
In SQL Server 2005, that statement should work, but it seems that there
is a bug in the "Microsoft OLEDB Provider for SQL Server" (or it is not
updated for SQL Server 2005). Try using the "SQL Native Client" OLEDB
provider (i.e. "Provider=SQLNCLI.1", instead of "Provider=SQLOLEDB.1"
in the connection string). It worked on my system (using SQL Server
2005 September CTP).
Razvan
I'm tracking down a syntax error and have an odd problem.
I'm using the SQL Trace tool to view SQL arriving at the server.
Now, there's two problems...
SQL Server keeps on putting EXEC in front of my statements -
eg:
ENABLE TRIGGER ddlDatabaseTriggerLog ON DATABASE
is changed to
exec ENABLE TRIGGER ddlDatabaseTriggerLog ON DATABASE
And the second problem is that any cursor/dataset is created like:
declare @.p1 int
set @.p1=2
exec sp_prepexec @.p1 output,NULL,N'<my statement>'
select @.p1
Does anyone have an idea why?
--
With regards,
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com
Database development questions? Check the forum!
http://www.databasedevelopmentforum.comBecause you are embedding dynamic SQL. Best practice is to put your
code into stored procs and call the procs from ADO.
David Portas
SQL Server MVP
--|||Hello David,
> Because you are embedding dynamic SQL. Best practice is to put your
> code into stored procs and call the procs from ADO.
In this case, not an option :-)
Funny, ad-hoc queries in SQL Server Management Studio or in the
Query Analyzer don't "suffer" from this problem.
Why is that?
With regards,
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com|||Query Analyzer uses ODBC, not ADO, that's why :)
Jacco Schalkwijk
SQL Server MVP
"Martijn Tonies" <m.tonies@.upscene-removethis.nospam.com> wrote in message
news:%23C1BYjZyFHA.3320@.TK2MSFTNGP14.phx.gbl...
> Hello David,
>
> In this case, not an option :-)
> Funny, ad-hoc queries in SQL Server Management Studio or in the
> Query Analyzer don't "suffer" from this problem.
> Why is that?
>
> --
> With regards,
> Martijn Tonies
> Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
> Server
> Upscene Productions
> http://www.upscene.com
> Database development questions? Check the forum!
> http://www.databasedevelopmentforum.com
>|||
> Query Analyzer uses ODBC, not ADO, that's why :)
Hmm and I thought the ODBC driver wasn't updated to support SQL 2000.
Ah well...
Anyway, how can I get rid of this EXEC stuff then?
With regards,
Martijn Tonies
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Martijn Tonies" <m.tonies@.upscene-removethis.nospam.com> wrote in message
> news:%23C1BYjZyFHA.3320@.TK2MSFTNGP14.phx.gbl...
SQL
>|||Just to make sure that we are talking about the same thing: you are
using ADO 2.x, not ADO.NET, right ? And you are using SQL Server 2000
or something else ?
Razvan|||> Just to make sure that we are talking about the same thing: you are
> using ADO 2.x, not ADO.NET, right ? And you are using SQL Server 2000
> or something else ?
No ADO.NET, just plain old ADO.
SQL 2000 and 2005, for that matter.
Haven't tried SQL 7 on this particular thingy yet...
With regards,
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com|||I'm not an ADO expert, but IIRC there is a method you can call on the
connection (or command) that just sends the SQL to the server with adding
the EXEC stuff. Which one that is you'll have to look up in the ADO
documentation or ask on an ado newsgroup. It might be the CommandType. You
can try setting the CommandType to adCmdText.
Jacco Schalkwijk
SQL Server MVP
"Martijn Tonies" <m.tonies@.upscene-removethis.nospam.com> wrote in message
news:OnYpjrayFHA.460@.TK2MSFTNGP15.phx.gbl...
>
> Hmm and I thought the ODBC driver wasn't updated to support SQL 2000.
> Ah well...
> Anyway, how can I get rid of this EXEC stuff then?
>
> --
> With regards,
> Martijn Tonies
>
> SQL
>|||
> I'm not an ADO expert, but IIRC there is a method you can call on the
> connection (or command) that just sends the SQL to the server with adding
> the EXEC stuff. Which one that is you'll have to look up in the ADO
> documentation or ask on an ado newsgroup. It might be the CommandType. You
> can try setting the CommandType to adCmdText.
It's already adCmdText :-/
I'll have a look at the docs then ... couldn't find it before ...
With regards,
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com|||Hello, Martijn
In SQL Server 2000, "ENABLE TRIGGER ddlDatabaseTriggerLog ON DATABASE"
is not a valid statement. Try a "ALTER TABLE YourTable ENABLE TRIGGER
SomeNormalTrigger" and it should work. If ADO doesn't recognize your
CommandText as being a valid SQL statement, it tries to add an "exec "
or a "select * from " guessing that it could be a procedure name or a
table/view name.
In SQL Server 2005, that statement should work, but it seems that there
is a bug in the "Microsoft OLEDB Provider for SQL Server" (or it is not
updated for SQL Server 2005). Try using the "SQL Native Client" OLEDB
provider (i.e. "Provider=SQLNCLI.1", instead of "Provider=SQLOLEDB.1"
in the connection string). It worked on my system (using SQL Server
2005 September CTP).
Razvan
Subscribe to:
Posts (Atom)