Access connection to SQL Server

I have had requests form users who would like to use MS Access to connect to
a SQL Server. Are there any security or other issues that would be relevant
to this approach?
RodRod,
This can be slow when linking to and working with large SQL tables (i.e.,
many rows).
HTH
Jerry
"Rod Snyder" <rod@.rcsnyder.com> wrote in message
news:efs3voFxFHA.2880@.TK2MSFTNGP12.phx.gbl...
>I have had requests form users who would like to use MS Access to connect
>to a SQL Server. Are there any security or other issues that would be
>relevant to this approach?
> Rod
>|||Hi Rod,
You can use an ODBC link which would require setting up user accounts and
respective permissions/roles to use and manipulate (update/delete/insert/run
)
sql server objects like Databases, tables, stored procedures.
For large tables, it would be more efficient to retrieve data into MS Access
by calling a Sql Server Stored Procedure with parameters using COM ADO and
populate tables in Access with the retrieved data. You would write the
routines in Access in a code module using VBA. But you would still have to
define a user role for connecting to the Sql Server. For example, you don't
want people connecting to Sql Server using an SA account (system
administrator). That would give everyone access to everything, and they
could do anything. Instead, you have to define a user role which would limi
t
access to respective databases like User1, who can read tbl1, tbl2, ... can
run SP1, SP2, ... in database1 but not database2
User2 can read/write to tables and run SPs in database1 but not database2,
and so on.
Rich
"Rod Snyder" wrote:

> I have had requests form users who would like to use MS Access to connect
to
> a SQL Server. Are there any security or other issues that would be relevan
t
> to this approach?
> Rod
>
>sql

Access cannot see MSDE when network cable disonnected

When my users are in the field and try to switch databases the
connection tool cannot see any servers even thought I know they are
there. The data link properties dialog does not see them. Also my data
entry program also cannot see them but is using sqldmo to list the
servers. However my SQL Service manager (inside my task bar) Can see
them. Any pointers on how to let the access project or the sqldmo
objects see the servers when they are disconnected. I even tried using
ODBC with no luck.
Thanks for any help.
Shane
Hello,
Personnaly, I use the following
Dim ListSvr As NameList
Set ListSvr = SQLDMO.ListAvailableSQLServers
' I add the servers to a collection after removing the ones I don't want
to show
' After that,
Set oServer = New SQLDMO.SQLServer2
Set ListSvr = oServer.ListInstalledInstances
' This function always work but only for the local instances (named or not)
' I add the needed servers to the other collection but I don't put the
servers already listed before
Marc Allard
Allcomp
Shane Lim wrote:
> When my users are in the field and try to switch databases the
> connection tool cannot see any servers even thought I know they are
> there. The data link properties dialog does not see them. Also my data
> entry program also cannot see them but is using sqldmo to list the
> servers. However my SQL Service manager (inside my task bar) Can see
> them. Any pointers on how to let the access project or the sqldmo
> objects see the servers when they are disconnected. I even tried using
> ODBC with no luck.
> Thanks for any help.
> Shane
>
|||That is PERFECT!!
On Fri, 25 Feb 2005 10:39:39 +0100, Allcomp <marc@.nospam.allcomp.be>
wrote:
[vbcol=seagreen]
>Hello,
>Personnaly, I use the following
>Dim ListSvr As NameList
>Set ListSvr = SQLDMO.ListAvailableSQLServers
>' I add the servers to a collection after removing the ones I don't want
>to show
>' After that,
>Set oServer = New SQLDMO.SQLServer2
>Set ListSvr = oServer.ListInstalledInstances
>' This function always work but only for the local instances (named or not)
>' I add the needed servers to the other collection but I don't put the
>servers already listed before
>Marc Allard
>Allcomp
>
>Shane Lim wrote:

Access 97 / VBA Users...

...should NEVER be allowed to touch EM, no matter how much T-SQL they know.
She sent 7800 records into an external "black-box" system -- records whose
status wee need to track.
And then she deleted them from SQL Server. Now she's talking to one of our
C# guys about using a hex editor to crack the LDF file format to extract the
records that were deleted and restore them.
PLEASE have more intelligent suggestions?
Anyone who's an MCDBA will recall the MOC 2072 course in database
administration. As a former MCT, I still have the CD for that course, if
anyone has a chapter or lesson for me to refer to?
Peace & happy computing,
Mike Labosh, MCSD
"When you kill a man, you're a murderer.
Kill many, and you're a conqueror.
Kill them all and you're a god." -- Dave MustaneSounds like the DBA needs to be a bit more stingy with the access
permissions and management needs to spend a bit more time peering over their
shoulders.
"Mike Labosh" <mlabosh@.hotmail.com> wrote in message
news:O9sXfhcxFHA.916@.TK2MSFTNGP10.phx.gbl...
> ...should NEVER be allowed to touch EM, no matter how much T-SQL they
> know.
> She sent 7800 records into an external "black-box" system -- records whose
> status wee need to track.
> And then she deleted them from SQL Server. Now she's talking to one of
> our C# guys about using a hex editor to crack the LDF file format to
> extract the records that were deleted and restore them.
> PLEASE have more intelligent suggestions?
> Anyone who's an MCDBA will recall the MOC 2072 course in database
> administration. As a former MCT, I still have the CD for that course, if
> anyone has a chapter or lesson for me to refer to?
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "When you kill a man, you're a murderer.
> Kill many, and you're a conqueror.
> Kill them all and you're a god." -- Dave Mustane
>|||> Sounds like the DBA needs to be a bit more stingy with the access
> permissions and management needs to spend a bit more time peering over
> their shoulders.
HAH! What DBA?!? Why do you think me, a developer is on this group so
much'! I'm just an MCSD code monkey, and I'm probably the one here that's
most qualified as a DBA.
Did that send a shiver up your spine?
--
Peace & happy computing,
Mike Labosh, MCSD
"When you kill a man, you're a murderer.
Kill many, and you're a conqueror.
Kill them all and you're a god." -- Dave Mustane|||Mike,
The MS-2072 curriculum is not going to help. If she could delete the
records in EM then she could do it in any app. So it is more of a
permissions issue here.
A fix...POINT IN TIME recoverability using the transaction log. Or possibly
one of the third-party log exploring tools (could help minimize data loss
after the DELETE occured).
HTH
Jerry
"Mike Labosh" <mlabosh@.hotmail.com> wrote in message
news:OHOXzDdxFHA.720@.TK2MSFTNGP15.phx.gbl...
> HAH! What DBA?!? Why do you think me, a developer is on this group so
> much'! I'm just an MCSD code monkey, and I'm probably the one here
> that's most qualified as a DBA.
> Did that send a shiver up your spine?
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "When you kill a man, you're a murderer.
> Kill many, and you're a conqueror.
> Kill them all and you're a god." -- Dave Mustane
>|||All good software engineers start out as common code monkeys but eventually
evolve into a DBA.
"Mike Labosh" <mlabosh@.hotmail.com> wrote in message
news:OHOXzDdxFHA.720@.TK2MSFTNGP15.phx.gbl...
> HAH! What DBA?!? Why do you think me, a developer is on this group so
> much'! I'm just an MCSD code monkey, and I'm probably the one here
> that's most qualified as a DBA.
> Did that send a shiver up your spine?
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "When you kill a man, you're a murderer.
> Kill many, and you're a conqueror.
> Kill them all and you're a god." -- Dave Mustane
>|||> A fix...POINT IN TIME recoverability using the transaction log. Or
> possibly one of the third-party log exploring tools (could help minimize
> data loss after the DELETE occured).
Yes, that's precisely what I'm looking for. I just found it at Module 7,
page 20.
--
Peace & happy computing,
Mike Labosh, MCSD
"When you kill a man, you're a murderer.
Kill many, and you're a conqueror.
Kill them all and you're a god." -- Dave Mustane|||Some thoughts here... http://www.karaszi.com/SQLServer/in...
mes.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Mike Labosh" <mlabosh@.hotmail.com> wrote in message news:O9sXfhcxFHA.916@.TK2MSFTNGP10.phx.
gbl...
> ...should NEVER be allowed to touch EM, no matter how much T-SQL they know
.
> She sent 7800 records into an external "black-box" system -- records whose
> status wee need to track.
> And then she deleted them from SQL Server. Now she's talking to one of ou
r
> C# guys about using a hex editor to crack the LDF file format to extract t
he
> records that were deleted and restore them.
> PLEASE have more intelligent suggestions?
> Anyone who's an MCDBA will recall the MOC 2072 course in database
> administration. As a former MCT, I still have the CD for that course, if
> anyone has a chapter or lesson for me to refer to?
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "When you kill a man, you're a murderer.
> Kill many, and you're a conqueror.
> Kill them all and you're a god." -- Dave Mustane
>

Access 2000 FAILS to relink ODBC tables with correct credentials

i have an access 2000 db in which i dynamically relink the tables from
sqlserver 2000 depending on the user - some users having read only
permissions, others read/write etc.
however access 2000 DOES NOT relink correctly and after looking in
msysobjects in access, have found that it seems to cache the last login
name. needless to say this ruins any permissions i have set on
sqlserver.
an edit of the code is attached below:
step 1. delete attached tables - this works and clears out msysobjects
step 2. get tablenames from sql server as recordset - this works as
well.
step 3. reattach
ODBC_STR = "ODBC;DSN=BMTL;DATABASE=BMTL;UID=" & Usr & ";PWD=;"
Set dbsODBC = OpenDatabase("", False, False, ODBC_STR)
'rs(0) = tablename
Do While Not rs.EOF
Set td = CurrentDb.CreateTableDef(rs(0), dbAttachSavePWD)
td.Connect = dbsODBC.Connect
td.SourceTableName = dbsODBC.TableDefs("dbo." & rs(0)).Name
CurrentDb.TableDefs.Append td
CurrentDb.TableDefs.Refresh 'refresh link here hopefully
rs.MoveNext
Loop
'refresh just to be sure
CurrentDb.TableDefs.Refresh
'open msysobjects in access and all are linked on either the current
login or on some previously cached login - which it does is random.
is there some attribute or method i can call that will absolutely
refresh the odbc link on the table?
regards
Edwinah63
Probably not the cause of your issue, but, you should not use
currentdb() like that. Instead, you should cache it in a variable & use
the variable:
dim db as database
set db -= currentdb()
db.this ... db.that ... db.t'other
set db = nothing
To understand why, google all groups on "currentdb cache kaplan"
(without the quotes).
HTH,
TC
|||Also, you might want to check out the refreshlink method.
tabledefs.refresh refreshes the tables collection, which is not the
same thing.
HTH,
TC
|||You are using DAO methods on Application.CurrentDB, i.e.
Application.dbengine.workspaces(0).databases(0)
dbEngine caches ODBC connections. Since you do not wish
to use cached connections, you can (1) wait ~ 15 minutes
until the cached connections time out, or (2) Change
the timeout interval, or (3) Flush the connections by
closing the application and re-opening, or
(4) use a separate dbEngine object.
set dbe = createobject("dao.dbengine.36")
with a separate database object:
set db = dbe.opendatabase()
or (5) use ADO to create a separate dbengine object
I wouldn't do it that way anyhow: I'd use Windows security
for the read only users, and use 0 instead of dbAttachSavePWD
for those users.
(david)
"Edwinah63" <edwinah@.customercare.com.au> wrote in message
news:1128573589.087302.168780@.g43g2000cwa.googlegr oups.com...
>i have an access 2000 db in which i dynamically relink the tables from
> sqlserver 2000 depending on the user - some users having read only
> permissions, others read/write etc.
> however access 2000 DOES NOT relink correctly and after looking in
> msysobjects in access, have found that it seems to cache the last login
> name. needless to say this ruins any permissions i have set on
> sqlserver.
> an edit of the code is attached below:
> step 1. delete attached tables - this works and clears out msysobjects
> step 2. get tablenames from sql server as recordset - this works as
> well.
> step 3. reattach
> ODBC_STR = "ODBC;DSN=BMTL;DATABASE=BMTL;UID=" & Usr & ";PWD=;"
> Set dbsODBC = OpenDatabase("", False, False, ODBC_STR)
> 'rs(0) = tablename
> Do While Not rs.EOF
> Set td = CurrentDb.CreateTableDef(rs(0), dbAttachSavePWD)
> td.Connect = dbsODBC.Connect
> td.SourceTableName = dbsODBC.TableDefs("dbo." & rs(0)).Name
> CurrentDb.TableDefs.Append td
> CurrentDb.TableDefs.Refresh 'refresh link here hopefully
> rs.MoveNext
> Loop
> 'refresh just to be sure
> CurrentDb.TableDefs.Refresh
> 'open msysobjects in access and all are linked on either the current
> login or on some previously cached login - which it does is random.
> is there some attribute or method i can call that will absolutely
> refresh the odbc link on the table?
> regards
> Edwinah63
>

Access 2000 FAILS to relink ODBC tables with correct credentials

i have an access 2000 db in which i dynamically relink the tables from
sqlserver 2000 depending on the user - some users having read only
permissions, others read/write etc.
however access 2000 DOES NOT relink correctly and after looking in
msysobjects in access, have found that it seems to cache the last login
name. needless to say this ruins any permissions i have set on
sqlserver.
an edit of the code is attached below:
step 1. delete attached tables - this works and clears out msysobjects
step 2. get tablenames from sql server as recordset - this works as
well.
step 3. reattach
ODBC_STR = "ODBC;DSN=BMTL;DATABASE=BMTL;UID=" & Usr & ";PWD=;"
Set dbsODBC = OpenDatabase("", False, False, ODBC_STR)
'rs(0) = tablename
Do While Not rs.EOF
Set td = CurrentDb.CreateTableDef(rs(0), dbAttachSavePWD)
td.Connect = dbsODBC.Connect
td.SourceTableName = dbsODBC.TableDefs("dbo." & rs(0)).Name
CurrentDb.TableDefs.Append td
CurrentDb.TableDefs.Refresh 'refresh link here hopefully
rs.MoveNext
Loop
'refresh just to be sure
CurrentDb.TableDefs.Refresh
'open msysobjects in access and all are linked on either the current
login or on some previously cached login - which it does is random.
is there some attribute or method i can call that will absolutely
refresh the odbc link on the table?
regards
Edwinah63Probably not the cause of your issue, but, you should not use
currentdb() like that. Instead, you should cache it in a variable & use
the variable:
dim db as database
set db -= currentdb()
db.this ... db.that ... db.t'other
set db = nothing
To understand why, google all groups on "currentdb cache kaplan"
(without the quotes).
HTH,
TC|||Also, you might want to check out the refreshlink method.
tabledefs.refresh refreshes the tables collection, which is not the
same thing.
HTH,
TC|||You are using DAO methods on Application.CurrentDB, i.e.
Application.dbengine.workspaces(0).databases(0)
dbEngine caches ODBC connections. Since you do not wish
to use cached connections, you can (1) wait ~ 15 minutes
until the cached connections time out, or (2) Change
the timeout interval, or (3) Flush the connections by
closing the application and re-opening, or
(4) use a separate dbEngine object.
set dbe = createobject("dao.dbengine.36")
with a separate database object:
set db = dbe.opendatabase()
or (5) use ADO to create a separate dbengine object
I wouldn't do it that way anyhow: I'd use Windows security
for the read only users, and use 0 instead of dbAttachSavePWD
for those users.
(david)
"Edwinah63" <edwinah@.customercare.com.au> wrote in message
news:1128573589.087302.168780@.g43g2000cwa.googlegroups.com...
>i have an access 2000 db in which i dynamically relink the tables from
> sqlserver 2000 depending on the user - some users having read only
> permissions, others read/write etc.
> however access 2000 DOES NOT relink correctly and after looking in
> msysobjects in access, have found that it seems to cache the last login
> name. needless to say this ruins any permissions i have set on
> sqlserver.
> an edit of the code is attached below:
> step 1. delete attached tables - this works and clears out msysobjects
> step 2. get tablenames from sql server as recordset - this works as
> well.
> step 3. reattach
> ODBC_STR = "ODBC;DSN=BMTL;DATABASE=BMTL;UID=" & Usr & ";PWD=;"
> Set dbsODBC = OpenDatabase("", False, False, ODBC_STR)
> 'rs(0) = tablename
> Do While Not rs.EOF
> Set td = CurrentDb.CreateTableDef(rs(0), dbAttachSavePWD)
> td.Connect = dbsODBC.Connect
> td.SourceTableName = dbsODBC.TableDefs("dbo." & rs(0)).Name
> CurrentDb.TableDefs.Append td
> CurrentDb.TableDefs.Refresh 'refresh link here hopefully
> rs.MoveNext
> Loop
> 'refresh just to be sure
> CurrentDb.TableDefs.Refresh
> 'open msysobjects in access and all are linked on either the current
> login or on some previously cached login - which it does is random.
> is there some attribute or method i can call that will absolutely
> refresh the odbc link on the table?
> regards
> Edwinah63
>

Access <- > SQL Server

Hello!

We have following situation;
network with 100 users, aplication developed in Access, user DB
deployed on SQL Server.

Is it better to create query inside aplication (with code) and then pass
it to SQL Server for execution or is it better to have all these queries
saved like stored procedures and then called from aplication?

Witch queries run faster?

Thank's!

P.S. I know that my english isn't perfect:(!!!!!On Sun, 15 Feb 2004 21:30:11 +0100 in comp.databases.ms-access,
"Jegger" <dzugaj@.net4u.hr> wrote:

>Hello!
> We have following situation;
> network with 100 users, aplication developed in Access, user DB
>deployed on SQL Server.
> Is it better to create query inside aplication (with code) and then pass
>it to SQL Server for execution or is it better to have all these queries
>saved like stored procedures and then called from aplication?
> Witch queries run faster?

Stored procedures are compiled with the execution plan so run faster
than dynamic SQL.

--
A)bort, R)etry, I)nfluence with large hammer.|||On Sun, 15 Feb 2004 21:30:11 +0100, "Jegger" <dzugaj@.net4u.hr> wrote:

>Hello!
> We have following situation;
> network with 100 users, aplication developed in Access, user DB
>deployed on SQL Server.
> Is it better to create query inside aplication (with code) and then pass
>it to SQL Server for execution or is it better to have all these queries
>saved like stored procedures and then called from aplication?
> Witch queries run faster?
> Thank's!
>P.S. I know that my english isn't perfect:(!!!!!

Since you say this is an application developed in Access, and there are,
apparently, no other applications sharing the back-end, my recommendation is
to implement all the queries in Access except those that you can't make run
optimally that way by simply modifying the query's approach (chaning
subqueries to outer joins, etc.). Most queries of linked tables on SQL Server
will run well as Access queries.

The reason for keeping the queries in Access as much as possible is that it
makes the particular choice of server easier to change. It would be
relatively (though not totally) trivial, for instance, to switch to a
PostgreSQL or Oracle back-end.

There is actually very little speed penalty, in most cases, for executing
Access queries against linked, ODBC tables because Access can generally
convert these into prepared statements that actually act quite a bit like
stored procedures in that they are compiled once by the server when they are
first used, then reused when the same query is executed again. SQL Server is
also clever enough to reuse the same query plan when additional statements are
prepared with identical definitions.|||I make it a rule to: (well, rules)

1) Create parameterized stored procedures whenever possible, which usually mean
every read-only situation. (If you're using ADPs, however, MS has done a heluva
job making param SPs updatable.)

2) Create Pass-through queries for any must-have dynamic sql (again,
read-only). Also, PT queries must be used to call those SQL Server SPs!

3) ONLY use JET (Access) queries for those siutations that REQUIRE it, like
updatable BOUND data sources, LinkChild/Master, consumers of VBA custom
functions, etc. (And strive to make your VBA functions into processes that use
SQL Server if you can)

It really boils down to this: Don't use JET unless you ABSOLUTELY MUST. I
haven't gone as far as some JET-haters that use UNBOUND forms w/
insert/update/delete classes, but I'm getting there...

I love Access as a front-end building tool, and a reporting tool. But I can
live without JET.

All of me recent Access/SQL development has been ADPs...no JET! YAYYYY!|||On 15 Feb 2004 23:41:03 GMT, dcmfan@.aol.comSPNOAM (DCM Fan) wrote:

>I make it a rule to: (well, rules)
>1) Create parameterized stored procedures whenever possible, which usually mean
>every read-only situation. (If you're using ADPs, however, MS has done a heluva
>job making param SPs updatable.)
>2) Create Pass-through queries for any must-have dynamic sql (again,
>read-only). Also, PT queries must be used to call those SQL Server SPs!
>3) ONLY use JET (Access) queries for those siutations that REQUIRE it, like
>updatable BOUND data sources, LinkChild/Master, consumers of VBA custom
>functions, etc. (And strive to make your VBA functions into processes that use
>SQL Server if you can)
>It really boils down to this: Don't use JET unless you ABSOLUTELY MUST. I
>haven't gone as far as some JET-haters that use UNBOUND forms w/
>insert/update/delete classes, but I'm getting there...
>I love Access as a front-end building tool, and a reporting tool. But I can
>live without JET.
>All of me recent Access/SQL development has been ADPs...no JET! YAYYYY!

I respect yor opinion, but I disagree. There are cases in which I do agree,
and these are when you will have multiple front-ends to the same data, but
when SQL Server is just the data store for an Access application, the
performance benefit to implenmenting everything server-side is more than
overshadowed by the increased complexity of the front-end, and the loss of
independence from a particular server back-end.

I do favor implementing server-side views and stored procedures as an
optimization when required, but only after a performance problem has been
identified, and it doesn't respond to more minor rearrangments of the queries.

Regarding the use of ADPs fopr client-server apps, I guess that worked for
you, but after I successfully implemented several client-server apps with DAO
and MDBs, I worked on one monster of a project with ADPs, and found it an
ecercise in terrible frustration. Error message handling doesn't work right,
Access is so touchy about the actual implementations of things on the back-end
that many techniques of great use from the server perspective are not usable,
the updatability rules change with every new release of MDAC, so that I've had
to adopt a practice of never trying to bind a form to a query on a join
because I can't trust it (I use hidden combo boxes for FK lookups - how's that
for efficiency!), etc. Frankly, I would never use ADPs again for anything
other than prototypig. I'm at least 5 times more productive using MDBs and
DAO. Of course, if I was using something other than Access as a front-end, I
would nearly always us ADO.

Clearly, your experience differs, but to me ADPs are simply far too unstable
for production systems, and I don't see that they provide any performance
boost, what with having to keep asking the server for schema information
instead of caching it locally in links, constantly digging around behind
procedures and views to update individual tables. You have to jumpt through
several hoops to force Access to respect your intended security and
abstractions with views and procedures, etc. I spend most of my time trying
to get the thing to do what I say instead of guessing what it'll do when it
tries to thnk for me and do what it "knows" I must want.|||Steve, we've been through all this before (pro/con of ADP) in the Access
forums.

Even if everything you say is true about ADPs, and the "common knowledge"
becomes "don't use ADPs, " I still stand by my mantra of "minimize use of JET
if at all possible."

I've never had corruption in 8 years of SQL Server development (It has happened
to others.) I have JET corruption at least once every 6 months. Granted, most
of the time it's caused by faulty hardware, but it scares me that JET is so
"nimble" that way.

Also, I don't know where you get your information on JET query performance, but
in NO WAY is it EVER equal to pass-through performance, except on the smallest
of SELECTS.

I can't begin to tell you how many times Access has either locked up, or I've
gotten impatient at the wait of a mutli-table query designed in the QBE, but
when run right from Query Analyzer returns records in less than 1 second.

Everyone of my combo boxes use Pass-through SELECTS. The performance increase
over JET selects REQUIRES IT, especially when connecting over the internet.
(one-to-one NAT)

Don't get me wrong...I take FULL ADVANTAGE of JET/DAO-based front ends (local
tables, specs, QBE), and I may even come up with an absolute must someday. I
even prefer DAO over ADO, but that's only because I KNOW that I'm more familiar
with it!

My bottom line is this: I'll listen to criticism of ADPs any day (although my
recent ADP projects have been successful, I have lots more exeperince with
MDB), but I can't take it when Access-lovers (I'm one) actually say with a
straight face that JET can perform in any way "as good as" SQL Server views,
SPs, or dynamic SQL.

Just the fact that an extra layer is involved ought to be enough proof...|||
On 16 Feb 2004 05:41:10 GMT, dcmfan@.aol.comSPNOAM (DCM Fan) wrote:

>Steve, we've been through all this before (pro/con of ADP) in the Access
>forums.
>Even if everything you say is true about ADPs, and the "common knowledge"
>becomes "don't use ADPs, " I still stand by my mantra of "minimize use of JET
>if at all possible."
>I've never had corruption in 8 years of SQL Server development (It has happened
>to others.) I have JET corruption at least once every 6 months. Granted, most
>of the time it's caused by faulty hardware, but it scares me that JET is so
>"nimble" that way.

I've had Access corruption, too, but the only times I've had data corruption
were when the back-end was an MDB. That has nothing to do with whether
queries are stored as Access objects or server-side objects when the data is
on the SQL Server.

>Also, I don't know where you get your information on JET query performance, but
>in NO WAY is it EVER equal to pass-through performance, except on the smallest
>of SELECTS.

That's simpy wrong. I've tested it, and I've used the moitoring tools to
watch the SQL. Unless you do something on the Access side that forces it to
do processing locally like joining or filtering on a function, or your query
is just too complex for Access to translate (rare) or if you are missing
indexes on joined fields (and why would you do that on purpose),
Access/DAO/Jet quite happily builds server-side SQL and executes it on the
server.

In the rare cases when I find a query is insisting on processing locally, I
turn part or all of it into a stored procedure or view.

>I can't begin to tell you how many times Access has either locked up, or I've
>gotten impatient at the wait of a mutli-table query designed in the QBE, but
>when run right from Query Analyzer returns records in less than 1 second.

Over time, I have gained an intuitive sense of what queries are dangerous that
way and which are not. It is tru that some time can be wasted with this now
and again.

>Everyone of my combo boxes use Pass-through SELECTS. The performance increase
>over JET selects REQUIRES IT, especially when connecting over the internet.
>(one-to-one NAT)

I have not had the performance issues you describe. It soed seem to help to
make the query DISTINCT, though, so Access will use a snapshot, and not a
Dynaset which would make more than the necessary number of round trips for a
read-only result.

>Don't get me wrong...I take FULL ADVANTAGE of JET/DAO-based front ends (local
>tables, specs, QBE), and I may even come up with an absolute must someday. I
>even prefer DAO over ADO, but that's only because I KNOW that I'm more familiar
>with it!
>My bottom line is this: I'll listen to criticism of ADPs any day (although my
>recent ADP projects have been successful, I have lots more exeperince with
>MDB), but I can't take it when Access-lovers (I'm one) actually say with a
>straight face that JET can perform in any way "as good as" SQL Server views,
>SPs, or dynamic SQL.

Well, I hate to be one of those people (actually, I'm and Access lover/hater,
and I'd take ADO.NET over Jet any day in a C/S project not done in Access),
but what you desccribe has not been my experience. It is true that some
queries need to be translated to run efficiently, but my experience is that
most don't, and in that case, they shouldn't.

I realize we just have to agree to disagree on much of this, but I think it's
important that both of our opinions are fully expressed for the record.

>Just the fact that an extra layer is involved ought to be enough proof...

From what I can tell, ADO/OLEDB is actually a much more complex stack of
layers than DAO/JET/ODBC, and some of what the DAO layer contains is very
beneficial such as the ability to use SQL to join tables from muliple sources,
and the caching of schema data in the front-end rather than looking it up
repeatly from the back-end.

Access -> ODBC -> SQL Server connection hangs

I have an MS Access 2003 database that uses a SQL Server 2000 database as
it's back-end. The database runs well most of the time but if the users dont
use the database for a period of time (approx 10 minutes) and return to it
then the connection hangs. When they try to perform any action that requires
a connection to the SQL Server database then the egg-timer mouse pointer
displays and the database stops responding. It's as if the connection times
out when it becomes inactive for a period of time. Due to a shortage of time
and the impatience of the operators I haven't seen the error message yet
(assuming one eventually appears). The problem is "fixed" by closing the
database and re-opening it again.
If the operators keep using the database constantly then the connection
seems to stay open and the database operates reliably.
I use an ODBC connection using SQL Server authentication. I don't use
Windows NT authentication because I was getting "Cannot generate SSPI
context" errors when I tried to use it (could this be related to the other
problem?).
Any ideas what is wrong and how I can fix it?
Access 2003/Jet 4.0 has an ODBC setting called ConnectionTimeout which is
"The number of seconds a cached connection can remain idle before timing
out. The default is 600 (values are of type REG_DWORD)." 600 seconds is 10
minutes.
This property is found at
\HKEY_LOCAL_MACHINE\Software\Microsoft\Jet\4.0\Eng ines\ODBC
On the client where Access is installed, set this to a higher value or 0
for indefinite.
-- james
***Disclaimer: This posting is provided "as is" with no warranties and
confers no rights.***
--
>From: "Davey" <davey@.hello.com>
>Newsgroups:
comp.databases.ms-access,microsoft.public.sqlserver.connect,microsof t.public
.sqlserver.odbc,microsoft.public.sqlserver.server
>Subject: Access -> ODBC -> SQL Server connection hangs
>Date: Tue, 22 Feb 2005 18:21:15 +0100
>Lines: 22
>Message-ID: <381bg7F5hs90lU1@.individual.net>
>X-Trace: individual.net hlPan2h158ALGlSJ0qH+wApbb7gzleZkv9TQYLUPSiELWze4U=
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 6.00.2900.2527
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2527
>X-RFC2646: Format=Flowed; Original
>Path:
TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSF TNGP08.phx.gbl!newsfeed00.
sul.t-online.de!newsfeed01.sul.t-online.de!t-online.de!fu-berlin.de!uni-berl
in.de!individual.net!not-for-mail
>Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.odbc:43324
microsoft.public.sqlserver.server:378990
microsoft.public.sqlserver.connect:44465
>X-Tomcat-NG: microsoft.public.sqlserver.connect
>I have an MS Access 2003 database that uses a SQL Server 2000 database as
>it's back-end. The database runs well most of the time but if the users
dont
>use the database for a period of time (approx 10 minutes) and return to it
>then the connection hangs. When they try to perform any action that
requires
>a connection to the SQL Server database then the egg-timer mouse pointer
>displays and the database stops responding. It's as if the connection
times
>out when it becomes inactive for a period of time. Due to a shortage of
time
>and the impatience of the operators I haven't seen the error message yet
>(assuming one eventually appears). The problem is "fixed" by closing the
>database and re-opening it again.
>If the operators keep using the database constantly then the connection
>seems to stay open and the database operates reliably.
>I use an ODBC connection using SQL Server authentication. I don't use
>Windows NT authentication because I was getting "Cannot generate SSPI
>context" errors when I tried to use it (could this be related to the other
>problem?).
>Any ideas what is wrong and how I can fix it?
>
>
|||How are you connecting, linked tables via DSN or by supplying
connection string in code? (the latter is recommended). You can also
try sticking a SQL Profiler trace to see what's going on at the
server, and try connecting from user's machines using some other
client besides Access.
--Mary
On Tue, 22 Feb 2005 18:21:15 +0100, "Davey" <davey@.hello.com> wrote:

>I have an MS Access 2003 database that uses a SQL Server 2000 database as
>it's back-end. The database runs well most of the time but if the users dont
>use the database for a period of time (approx 10 minutes) and return to it
>then the connection hangs. When they try to perform any action that requires
>a connection to the SQL Server database then the egg-timer mouse pointer
>displays and the database stops responding. It's as if the connection times
>out when it becomes inactive for a period of time. Due to a shortage of time
>and the impatience of the operators I haven't seen the error message yet
>(assuming one eventually appears). The problem is "fixed" by closing the
>database and re-opening it again.
>If the operators keep using the database constantly then the connection
>seems to stay open and the database operates reliably.
>I use an ODBC connection using SQL Server authentication. I don't use
>Windows NT authentication because I was getting "Cannot generate SSPI
>context" errors when I tried to use it (could this be related to the other
>problem?).
>Any ideas what is wrong and how I can fix it?
>

Access -> ODBC -> SQL Server connection hangs

I have an MS Access 2003 database that uses a SQL Server 2000 database as
it's back-end. The database runs well most of the time but if the users dont
use the database for a period of time (approx 10 minutes) and return to it
then the connection hangs. When they try to perform any action that requires
a connection to the SQL Server database then the egg-timer mouse pointer
displays and the database stops responding. It's as if the connection times
out when it becomes inactive for a period of time. Due to a shortage of time
and the impatience of the operators I haven't seen the error message yet
(assuming one eventually appears). The problem is "fixed" by closing the
database and re-opening it again.
If the operators keep using the database constantly then the connection
seems to stay open and the database operates reliably.
I use an ODBC connection using SQL Server authentication. I don't use
Windows NT authentication because I was getting "Cannot generate SSPI
context" errors when I tried to use it (could this be related to the other
problem?).
Any ideas what is wrong and how I can fix it?
How are you connecting, linked tables via DSN or by supplying
connection string in code? (the latter is recommended). You can also
try sticking a SQL Profiler trace to see what's going on at the
server, and try connecting from user's machines using some other
client besides Access.
--Mary
On Tue, 22 Feb 2005 18:21:15 +0100, "Davey" <davey@.hello.com> wrote:

>I have an MS Access 2003 database that uses a SQL Server 2000 database as
>it's back-end. The database runs well most of the time but if the users dont
>use the database for a period of time (approx 10 minutes) and return to it
>then the connection hangs. When they try to perform any action that requires
>a connection to the SQL Server database then the egg-timer mouse pointer
>displays and the database stops responding. It's as if the connection times
>out when it becomes inactive for a period of time. Due to a shortage of time
>and the impatience of the operators I haven't seen the error message yet
>(assuming one eventually appears). The problem is "fixed" by closing the
>database and re-opening it again.
>If the operators keep using the database constantly then the connection
>seems to stay open and the database operates reliably.
>I use an ODBC connection using SQL Server authentication. I don't use
>Windows NT authentication because I was getting "Cannot generate SSPI
>context" errors when I tried to use it (could this be related to the other
>problem?).
>Any ideas what is wrong and how I can fix it?
>

Access -> ODBC -> SQL Server connection hangs

I have an MS Access 2003 database that uses a SQL Server 2000 database as
it's back-end. The database runs well most of the time but if the users dont
use the database for a period of time (approx 10 minutes) and return to it
then the connection hangs. When they try to perform any action that requires
a connection to the SQL Server database then the egg-timer mouse pointer
displays and the database stops responding. It's as if the connection times
out when it becomes inactive for a period of time. Due to a shortage of time
and the impatience of the operators I haven't seen the error message yet
(assuming one eventually appears). The problem is "fixed" by closing the
database and re-opening it again.
If the operators keep using the database constantly then the connection
seems to stay open and the database operates reliably.
I use an ODBC connection using SQL Server authentication. I don't use
Windows NT authentication because I was getting "Cannot generate SSPI
context" errors when I tried to use it (could this be related to the other
problem?).
Any ideas what is wrong and how I can fix it?How are you connecting, linked tables via DSN or by supplying
connection string in code? (the latter is recommended). You can also
try sticking a SQL Profiler trace to see what's going on at the
server, and try connecting from user's machines using some other
client besides Access.
--Mary
On Tue, 22 Feb 2005 18:21:15 +0100, "Davey" <davey@.hello.com> wrote:

>I have an MS Access 2003 database that uses a SQL Server 2000 database as
>it's back-end. The database runs well most of the time but if the users don
t
>use the database for a period of time (approx 10 minutes) and return to it
>then the connection hangs. When they try to perform any action that require
s
>a connection to the SQL Server database then the egg-timer mouse pointer
>displays and the database stops responding. It's as if the connection times
>out when it becomes inactive for a period of time. Due to a shortage of tim
e
>and the impatience of the operators I haven't seen the error message yet
>(assuming one eventually appears). The problem is "fixed" by closing the
>database and re-opening it again.
>If the operators keep using the database constantly then the connection
>seems to stay open and the database operates reliably.
>I use an ODBC connection using SQL Server authentication. I don't use
>Windows NT authentication because I was getting "Cannot generate SSPI
>context" errors when I tried to use it (could this be related to the other
>problem?).
>Any ideas what is wrong and how I can fix it?
>

Access -> ODBC -> SQL Server connection hangs

I have an MS Access 2003 database that uses a SQL Server 2000 database as
it's back-end. The database runs well most of the time but if the users dont
use the database for a period of time (approx 10 minutes) and return to it
then the connection hangs. When they try to perform any action that requires
a connection to the SQL Server database then the egg-timer mouse pointer
displays and the database stops responding. It's as if the connection times
out when it becomes inactive for a period of time. Due to a shortage of time
and the impatience of the operators I haven't seen the error message yet
(assuming one eventually appears). The problem is "fixed" by closing the
database and re-opening it again.
If the operators keep using the database constantly then the connection
seems to stay open and the database operates reliably.
I use an ODBC connection using SQL Server authentication. I don't use
Windows NT authentication because I was getting "Cannot generate SSPI
context" errors when I tried to use it (could this be related to the other
problem?).
Any ideas what is wrong and how I can fix it?
How are you connecting, linked tables via DSN or by supplying
connection string in code? (the latter is recommended). You can also
try sticking a SQL Profiler trace to see what's going on at the
server, and try connecting from user's machines using some other
client besides Access.
--Mary
On Tue, 22 Feb 2005 18:21:15 +0100, "Davey" <davey@.hello.com> wrote:

>I have an MS Access 2003 database that uses a SQL Server 2000 database as
>it's back-end. The database runs well most of the time but if the users dont
>use the database for a period of time (approx 10 minutes) and return to it
>then the connection hangs. When they try to perform any action that requires
>a connection to the SQL Server database then the egg-timer mouse pointer
>displays and the database stops responding. It's as if the connection times
>out when it becomes inactive for a period of time. Due to a shortage of time
>and the impatience of the operators I haven't seen the error message yet
>(assuming one eventually appears). The problem is "fixed" by closing the
>database and re-opening it again.
>If the operators keep using the database constantly then the connection
>seems to stay open and the database operates reliably.
>I use an ODBC connection using SQL Server authentication. I don't use
>Windows NT authentication because I was getting "Cannot generate SSPI
>context" errors when I tried to use it (could this be related to the other
>problem?).
>Any ideas what is wrong and how I can fix it?
>

Access -> ODBC -> SQL Server connection hangs

I have an MS Access 2003 database that uses a SQL Server 2000 database as
it's back-end. The database runs well most of the time but if the users dont
use the database for a period of time (approx 10 minutes) and return to it
then the connection hangs. When they try to perform any action that requires
a connection to the SQL Server database then the egg-timer mouse pointer
displays and the database stops responding. It's as if the connection times
out when it becomes inactive for a period of time. Due to a shortage of time
and the impatience of the operators I haven't seen the error message yet
(assuming one eventually appears). The problem is "fixed" by closing the
database and re-opening it again.
If the operators keep using the database constantly then the connection
seems to stay open and the database operates reliably.
I use an ODBC connection using SQL Server authentication. I don't use
Windows NT authentication because I was getting "Cannot generate SSPI
context" errors when I tried to use it (could this be related to the other
problem?).
Any ideas what is wrong and how I can fix it?Access 2003/Jet 4.0 has an ODBC setting called ConnectionTimeout which is
"The number of seconds a cached connection can remain idle before timing
out. The default is 600 (values are of type REG_DWORD)." 600 seconds is 10
minutes.
This property is found at
\HKEY_LOCAL_MACHINE\Software\Microsoft\J
et\4.0\Engines\ODBC
On the client where Access is installed, set this to a higher value or 0
for indefinite.
-- james
***Disclaimer: This posting is provided "as is" with no warranties and
confers no rights.***
--
>From: "Davey" <davey@.hello.com>
>Newsgroups:
comp.databases.ms-access,microsoft.public.sqlserver.connect,microsoft.public
.sqlserver.odbc,microsoft.public.sqlserver.server
>Subject: Access -> ODBC -> SQL Server connection hangs
>Date: Tue, 22 Feb 2005 18:21:15 +0100
>Lines: 22
>Message-ID: <381bg7F5hs90lU1@.individual.net>
>X-Trace: individual.net hlPan2h158ALGlSJ0qH+wApbb7gzleZkv9TQYLUP
SiELWze4U=
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 6.00.2900.2527
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2527
>X-RFC2646: Format=Flowed; Original
>Path:
TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!newsfeed00.
sul.t-online.de!newsfeed01.sul.t-online.de!t-online.de!fu-berlin.de!uni-berl
in.de!individual.net!not-for-mail
>Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.odbc:43324
microsoft.public.sqlserver.server:378990
microsoft.public.sqlserver.connect:44465
>X-Tomcat-NG: microsoft.public.sqlserver.connect
>I have an MS Access 2003 database that uses a SQL Server 2000 database as
>it's back-end. The database runs well most of the time but if the users
dont
>use the database for a period of time (approx 10 minutes) and return to it
>then the connection hangs. When they try to perform any action that
requires
>a connection to the SQL Server database then the egg-timer mouse pointer
>displays and the database stops responding. It's as if the connection
times
>out when it becomes inactive for a period of time. Due to a shortage of
time
>and the impatience of the operators I haven't seen the error message yet
>(assuming one eventually appears). The problem is "fixed" by closing the
>database and re-opening it again.
>If the operators keep using the database constantly then the connection
>seems to stay open and the database operates reliably.
>I use an ODBC connection using SQL Server authentication. I don't use
>Windows NT authentication because I was getting "Cannot generate SSPI
>context" errors when I tried to use it (could this be related to the other
>problem?).
>Any ideas what is wrong and how I can fix it?
>
>|||How are you connecting, linked tables via DSN or by supplying
connection string in code? (the latter is recommended). You can also
try sticking a SQL Profiler trace to see what's going on at the
server, and try connecting from user's machines using some other
client besides Access.
--Mary
On Tue, 22 Feb 2005 18:21:15 +0100, "Davey" <davey@.hello.com> wrote:

>I have an MS Access 2003 database that uses a SQL Server 2000 database as
>it's back-end. The database runs well most of the time but if the users don
t
>use the database for a period of time (approx 10 minutes) and return to it
>then the connection hangs. When they try to perform any action that require
s
>a connection to the SQL Server database then the egg-timer mouse pointer
>displays and the database stops responding. It's as if the connection times
>out when it becomes inactive for a period of time. Due to a shortage of tim
e
>and the impatience of the operators I haven't seen the error message yet
>(assuming one eventually appears). The problem is "fixed" by closing the
>database and re-opening it again.
>If the operators keep using the database constantly then the connection
>seems to stay open and the database operates reliably.
>I use an ODBC connection using SQL Server authentication. I don't use
>Windows NT authentication because I was getting "Cannot generate SSPI
>context" errors when I tried to use it (could this be related to the other
>problem?).
>Any ideas what is wrong and how I can fix it?
>

Accesing data from many applications.

Hi, I have a server running sql server 2000 sp3. In this sql server we
use windows authentication to allow users to use the applications that
were developed in house.
At this moment we have some users that are using excel to get data from
databases but we dont want they do that, because we cant control the
data they are getting.
We want to restrict that all users can get data only using the
applications that are developed in house.
Do you know if in sql server I can restrict wich applications can be
connected to sql server and wich not'
I need to continue using windows authentication.
Thanks a lot for your help.
*** Sent via Developersdex http://www.codecomments.com ***On Feb 24, 11:28 am, MariaGuzman <mar...@.devdex.com> wrote:
> Hi, I have a server running sql server 2000 sp3. In this sql server we
> use windows authentication to allow users to use the applications that
> were developed in house.
> At this moment we have some users that are using excel to get data from
> databases but we don=B4t want they do that, because we can=B4t control the
> data they are getting.
> We want to restrict that all users can get data only using the
> applications that are developed in house.
> Do you know if in sql server I can restrict wich applications can be
> connected to sql server and wich not'
> I need to continue using windows authentication.
> Thanks a lot for your help.
> *** Sent via Developersdexhttp://www.codecomments.com***
If a user has permission to connect they can.
See SQL Server 2005 Books Online topic:
Application Roles|||MariaGuzman (marisa@.devdex.com) writes:
> Hi, I have a server running sql server 2000 sp3. In this sql server we
> use windows authentication to allow users to use the applications that
> were developed in house.
> At this moment we have some users that are using excel to get data from
> databases but we dont want they do that, because we cant control the
> data they are getting.
> We want to restrict that all users can get data only using the
> applications that are developed in house.
> Do you know if in sql server I can restrict wich applications can be
> connected to sql server and wich not'
I assume here that your applications are not using stored procedures,
but generate SQL statements in the client. Because if the applications
are using stored procedures (with no dynamic SQL in them), the answer
would be trivial. Just revoke all direct table access. It's sufficient
that users have EXEC permissions on the stored procedure.
If your applications are not using stored procedures, it's of course
a major task to rewrite them. Using application roles as Steve suggested
is a possible solution. It still requires the application to be rewritten,
because once the user has logged in, the application must issue
"sp_setapprole". Note also that if you schemes that requires you
to know the name of the current database user, this will break with
application role.
Note also that application role with two-tier solution is not real
security. The password for the application must be hidden somewhere,
but whereever you hide it, a skilled user will be able to find it.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Acceptable Performance Counter ranges

I am having some performance issues with end users and the SQL Server. I'm not a SQL Server admin, but I want to learn how to use performance counters to monitor the SQL Server. I've been scouring the net trying to find performance counter information.
I have found alot of places that define each performance counter, but I have run across relatively few sites that contain acceptable values for the various different performance counters. For example, I've been to extremeexperts.com and found their arti
cle "Understanding SQL Performance Counters." And for example it explains : "SQLSERVER:Access Methods - Full Scans/sec - Value greater than 1 or 2 indicates that we are having table/index page scans. We need to analyze how this can be avoided." I'm loo
king for information like the above where it said "Value greater than 1 or 2 ..." Does anyone know where I can get a full list of acceptable performance counter ranges?
Not sure if these have what your looking for but they are worth looking
over.
http://www.microsoft.com/sql/techinf...perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.co...ance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.co...mance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/de...rfmon_24u1.asp
Disk Monitoring
Andrew J. Kelly SQL MVP
"Jason" <Jason@.discussions.microsoft.com> wrote in message
news:7450EA3C-549E-4EAB-9E46-D9CA956DF35A@.microsoft.com...
> I am having some performance issues with end users and the SQL Server.
I'm not a SQL Server admin, but I want to learn how to use performance
counters to monitor the SQL Server. I've been scouring the net trying to
find performance counter information. I have found alot of places that
define each performance counter, but I have run across relatively few sites
that contain acceptable values for the various different performance
counters. For example, I've been to extremeexperts.com and found their
article "Understanding SQL Performance Counters." And for example it
explains : "SQLSERVER:Access Methods - Full Scans/sec - Value greater than 1
or 2 indicates that we are having table/index page scans. We need to
analyze how this can be avoided." I'm looking for information like the
above where it said "Value greater than 1 or 2 ..." Does anyone know where
I can get a full list of acceptable performance counter ranges?

Acceptable Performance Counter ranges

I am having some performance issues with end users and the SQL Server. I'm
not a SQL Server admin, but I want to learn how to use performance counters
to monitor the SQL Server. I've been scouring the net trying to find perfor
mance counter information.
I have found alot of places that define each performance counter, but I have
run across relatively few sites that contain acceptable values for the vari
ous different performance counters. For example, I've been to extremeexpert
s.com and found their arti
cle "Understanding SQL Performance Counters." And for example it explains :
"SQLSERVER:Access Methods - Full Scans/sec - Value greater than 1 or 2 indi
cates that we are having table/index page scans. We need to analyze how thi
s can be avoided." I'm loo
king for information like the above where it said "Value greater than 1 or 2
..." Does anyone know where I can get a full list of acceptable performanc
e counter ranges?Not sure if these have what your looking for but they are worth looking
over.
http://www.microsoft.com/sql/techin.../perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.c...mance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.c...rmance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
on_24u1.asp" target="_blank">http://msdn.microsoft.com/library/d.../>
on_24u1.asp
Disk Monitoring
Andrew J. Kelly SQL MVP
"Jason" <Jason@.discussions.microsoft.com> wrote in message
news:7450EA3C-549E-4EAB-9E46-D9CA956DF35A@.microsoft.com...
> I am having some performance issues with end users and the SQL Server.
I'm not a SQL Server admin, but I want to learn how to use performance
counters to monitor the SQL Server. I've been scouring the net trying to
find performance counter information. I have found alot of places that
define each performance counter, but I have run across relatively few sites
that contain acceptable values for the various different performance
counters. For example, I've been to extremeexperts.com and found their
article "Understanding SQL Performance Counters." And for example it
explains : "SQLSERVER:Access Methods - Full Scans/sec - Value greater than 1
or 2 indicates that we are having table/index page scans. We need to
analyze how this can be avoided." I'm looking for information like the
above where it said "Value greater than 1 or 2 ..." Does anyone know where
I can get a full list of acceptable performance counter ranges?

Acceptable Performance Counter ranges

I am having some performance issues with end users and the SQL Server. I'm not a SQL Server admin, but I want to learn how to use performance counters to monitor the SQL Server. I've been scouring the net trying to find performance counter information. I have found alot of places that define each performance counter, but I have run across relatively few sites that contain acceptable values for the various different performance counters. For example, I've been to extremeexperts.com and found their article "Understanding SQL Performance Counters." And for example it explains : "SQLSERVER:Access Methods - Full Scans/sec - Value greater than 1 or 2 indicates that we are having table/index page scans. We need to analyze how this can be avoided." I'm looking for information like the above where it said "Value greater than 1 or 2 ..." Does anyone know where I can get a full list of acceptable performance counter ranges?Not sure if these have what your looking for but they are worth looking
over.
http://www.microsoft.com/sql/techinfo/administration/2000/perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.com/sql_server_performance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.com/best_sql_server_performance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_perfmon_24u1.asp
Disk Monitoring
Andrew J. Kelly SQL MVP
"Jason" <Jason@.discussions.microsoft.com> wrote in message
news:7450EA3C-549E-4EAB-9E46-D9CA956DF35A@.microsoft.com...
> I am having some performance issues with end users and the SQL Server.
I'm not a SQL Server admin, but I want to learn how to use performance
counters to monitor the SQL Server. I've been scouring the net trying to
find performance counter information. I have found alot of places that
define each performance counter, but I have run across relatively few sites
that contain acceptable values for the various different performance
counters. For example, I've been to extremeexperts.com and found their
article "Understanding SQL Performance Counters." And for example it
explains : "SQLSERVER:Access Methods - Full Scans/sec - Value greater than 1
or 2 indicates that we are having table/index page scans. We need to
analyze how this can be avoided." I'm looking for information like the
above where it said "Value greater than 1 or 2 ..." Does anyone know where
I can get a full list of acceptable performance counter ranges?

about Transaction's problem

There is a table named users in my database.
It has two colume:
userName varchar(20)
age int
now I execute the follow transaction:
begin tran
insert users (userName,age) values ('mark',25)
insert users (userName,age) values ('jack',2577777777777767777)
commit tran
obviously,the second setence has an overflow error,
the question is:has the first record been inserted into the users table?
why?
"κ" <weisl@.netstars.com.cn> wrote in message
news:%23T$6pCrPGHA.140@.TK2MSFTNGP12.phx.gbl...
> There is a table named users in my database.
> It has two colume:
> userName varchar(20)
> age int
> now I execute the follow transaction:
> begin tran
> insert users (userName,age) values ('mark',25)
> insert users (userName,age) values ('jack',2577777777777767777)
> commit tran
> obviously,the second setence has an overflow error,
> the question is:has the first record been inserted into the users table?
> why?
>
Because you told it to. You need error checking (or try/catch in SQL 2005)
to prevent execution from moving from the line that generates the error to
the line that commits the transaction.
David

about Transaction's problem

There is a table named users in my database.
It has two colume:
userName varchar(20)
age int
now I execute the follow transaction:
begin tran
insert users (userName,age) values ('mark',25)
insert users (userName,age) values ('jack',2577777777777767777)
commit tran
obviously,the second setence has an overflow error,
the question is:has the first record been inserted into the users table?
why?"κÊöÀÚ" <weisl@.netstars.com.cn> wrote in message
news:%23T$6pCrPGHA.140@.TK2MSFTNGP12.phx.gbl...
> There is a table named users in my database.
> It has two colume:
> userName varchar(20)
> age int
> now I execute the follow transaction:
> begin tran
> insert users (userName,age) values ('mark',25)
> insert users (userName,age) values ('jack',2577777777777767777)
> commit tran
> obviously,the second setence has an overflow error,
> the question is:has the first record been inserted into the users table?
> why?
>
Because you told it to. You need error checking (or try/catch in SQL 2005)
to prevent execution from moving from the line that generates the error to
the line that commits the transaction.
David

about Transaction's problem

There is a table named users in my database.
It has two colume:
userName varchar(20)
age int
now I execute the follow transaction:
begin tran
insert users (userName,age) values ('mark',25)
insert users (userName,age) values ('jack',2577777777777767777)
commit tran
obviously,the second setence has an overflow error,
the question is:has the first record been inserted into the users table?
why?"κ" <weisl@.netstars.com.cn> wrote in message
news:%23T$6pCrPGHA.140@.TK2MSFTNGP12.phx.gbl...
> There is a table named users in my database.
> It has two colume:
> userName varchar(20)
> age int
> now I execute the follow transaction:
> begin tran
> insert users (userName,age) values ('mark',25)
> insert users (userName,age) values ('jack',2577777777777767777)
> commit tran
> obviously,the second setence has an overflow error,
> the question is:has the first record been inserted into the users table?
> why?
>
Because you told it to. You need error checking (or try/catch in SQL 2005)
to prevent execution from moving from the line that generates the error to
the line that commits the transaction.
David

About SQL Server 2005 Management Studio

i would like to ask if there is a way to FILTER THE IPADDRESSES of users who wants to login the SQL Server Management Studio. right now, we are using Enterprise Edition of SQL Server 2005.

i just want to limit the users who can log in at Management Studio, the users know the password of SA, and i can't change the password coz we have deployed the programs with odbc connection using SA. so i thought of filtering the ipaddress of computers i want to grant access to SQL Server Management Studio, if its possible.

need a help.

thanks

I'm not sure you are able to do this unless you can write some sort of database/server scoped trigger that checks which application/IP the request is coming from (does anyone know if thats possible?).

However, remember that SSMS is just a GUI intended to make administration easier and restricted access to this tool doesn't really plug the serious security risk you have in using the sa password. Malicious users would still be able to use other methods of accessing the database eg osql.

Obviously, you should seriously consider stopping your application using the sa account for database access. At the very least, its good practice to create another user with sysadmin priveleges (eg saUser) and use this instead though operating a policy of least privelege is most best practice.

HTH|||

thank you richbrownesq.

About SQL Server 2005 Express

I developed a small application to manage inventory, accounting, payroll and so, for up to 5 users, running on SQL DB. Now I have some people interested for the software, they want to use it in their small companies. If I sell them the solution, do they have to license SQL DB ? or can they use express edition for free ?

You can distribute (or they can download and install) SQL 2005 Express. It is FREE.

You may wish to check out this resource:

SQL Server 2005 Express Redistribution
http://www.microsoft.com/sql/editions/express/redistregister.mspx