Conect via IP not server name - why?

Hi
My colleague has set up MSDE on his machine. I am only able to connect to his machine via IP and not servername
Why is this? I want to use ACCESS as a fornt end and can't useing IP address
Jerry

If you are on the same network just use the register server option in Enterprise manager and it becomes local to you. Hope this helps.

Concurrent User limitation?

Hi folks -
I have an application that runs a SQL database. I am using the MSDE to
access it. Client machine has the proprietary software and MDAC v 2.8
installed on a Win2KPro workstation that is fully patched. The support team
for my proprietary application is telling me that using MSDE instead of SQL
Server will give me a concurrent user limit of around 10 or 11.
I've been running this application for almost a year and have not run into
this until I recently instituted Group Policy settings (although I don't see
where the changes to Group Policy would have a bearing).
Sometimes the workstations can access the application, other times within
the same day, they receive an error stating that "the server does not exist
or access is denied", yet they can restart and get right in.
Does anyone know if there IS a concurrent user limitation using MSDE to
access a SQL application?
Thanks.
Deb Landrum, AAM, AIT
Insurance Alliance, Houston TX
HP Workstations P4, W2K-Pro, 512 RAM, Gigabit NICs, dual monitors
On Tue, 6 Sep 2005 14:29:13 -0500, Deb Landrum wrote:
(snip)
>Does anyone know if there IS a concurrent user limitation using MSDE to
>access a SQL application?
Hi Deb,
Well - sort of.
There is no real limit to the concurrent user connections, but there is
a limit to the number of concurrent workloads. If a connected user
spends most time entering data on the form, and only a fraction of the
total time actually executing queries, you can serve quite a few users
without ever running into problems. On the other hand, one single user
can spawn multiple concurrent workloads at once (though it's not
trivial).
The limit on concurrent workloads is set to 8 workloads, but since SQL
Server needs 3 workloads for it's internal processes, there are 5 left
for the users. Once you exceed the workload limit, you won;t get any
errors, but the overall performance of all workloads will be reduced.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||hi,
Deb Landrum wrote:
> Hi folks -
> I have an application that runs a SQL database. I am using the MSDE
> to access it. Client machine has the proprietary software and MDAC v
> 2.8 installed on a Win2KPro workstation that is fully patched. The
> support team for my proprietary application is telling me that using
> MSDE instead of SQL Server will give me a concurrent user limit of
> around 10 or 11.
> I've been running this application for almost a year and have not run
> into this until I recently instituted Group Policy settings (although
> I don't see where the changes to Group Policy would have a bearing).
> Sometimes the workstations can access the application, other times
> within the same day, they receive an error stating that "the server
> does not exist or access is denied", yet they can restart and get
> right in.
> Does anyone know if there IS a concurrent user limitation using MSDE
> to access a SQL application?
there's no upper limit but 32767 connections to an MSDE instance but, a
built-in Workload Governor will kick in when 8 concurrent workloads of a
certain type are running... this will result in a linear delay applied to
all concurrent batches in the I/O activities.. when the number decreases
under the 8 limit, the governor will go sleeping again, waiting for next
"overflow" to kick in again... that does not mean 8 concurrent users is the
maximum allowed without the governor to kick in, as usually lot of
connections will be "sleeping", just waiting for user intervention to do
something... Microsoft released a paper "estimating" a magic number of 25
concurrent users before the overhead will be noticeable to end users, but
this just is a "magic number"... it really depend on the application type,
access mode, activities against the data, data load and so on... as you may
understand, there's no actual documentation about this governor but the
papers as
http://msdn.microsoft.com/library/?u...asp?frame=true
...
but I think this is another story, as the exception you get is not related
to all this matter...
this error is a generic MDAC related exception, as you can see reading
http://support.microsoft.com/default...06&Product=sql ,
and probably depending on problem in your LAN...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Hi
To add what Hugo and Andrea have commented on, there is a limitation at OS
level. You are running w2k Professional which is a workstation product.
Currently the workstation products have a limitation of 10 simultaneous
connections from other machines. In effect, on your platform, you are
limited to 10 connections to MSDE. This limitation is in place to ensure
that workstation products, which are not optimized to be application or
file share servers, are not used as servers.
If you install MSDE on a server product, this limitation goes away.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Deb Landrum" <dlandrum@.ins-alliance.com> wrote in message
news:edolElxsFHA.912@.TK2MSFTNGP11.phx.gbl...
> Hi folks -
> I have an application that runs a SQL database. I am using the MSDE to
> access it. Client machine has the proprietary software and MDAC v 2.8
> installed on a Win2KPro workstation that is fully patched. The support
> team
> for my proprietary application is telling me that using MSDE instead of
> SQL
> Server will give me a concurrent user limit of around 10 or 11.
> I've been running this application for almost a year and have not run into
> this until I recently instituted Group Policy settings (although I don't
> see
> where the changes to Group Policy would have a bearing).
> Sometimes the workstations can access the application, other times within
> the same day, they receive an error stating that "the server does not
> exist
> or access is denied", yet they can restart and get right in.
> Does anyone know if there IS a concurrent user limitation using MSDE to
> access a SQL application?
> Thanks.
>
> --
> Deb Landrum, AAM, AIT
> Insurance Alliance, Houston TX
> HP Workstations P4, W2K-Pro, 512 RAM, Gigabit NICs, dual monitors
>
>
|||Thanks to all for your input - the MSDE is on a server product, and, the
resolution was actually an issue within the SQL application that we are
running. For whatever reason, although the server name was properly and
correctly shown for the workstations to access it, the problematic ones were
receiving the error. However, by substituting the IP address of the server
for the actual server name, those same workstations were once again able to
access the application.
I don't know if this would be a particular weakness in the proprietary
application that we're using or not, but, again, Thank You to all for
replying.
Deb
"Deb Landrum" <dlandrum@.ins-alliance.com> wrote in message
news:edolElxsFHA.912@.TK2MSFTNGP11.phx.gbl...
> Hi folks -
> I have an application that runs a SQL database. I am using the MSDE to
> access it. Client machine has the proprietary software and MDAC v 2.8
> installed on a Win2KPro workstation that is fully patched. The support
team
> for my proprietary application is telling me that using MSDE instead of
SQL
> Server will give me a concurrent user limit of around 10 or 11.
> I've been running this application for almost a year and have not run into
> this until I recently instituted Group Policy settings (although I don't
see
> where the changes to Group Policy would have a bearing).
> Sometimes the workstations can access the application, other times within
> the same day, they receive an error stating that "the server does not
exist
> or access is denied", yet they can restart and get right in.
> Does anyone know if there IS a concurrent user limitation using MSDE to
> access a SQL application?
> Thanks.
>
> --
> Deb Landrum, AAM, AIT
> Insurance Alliance, Houston TX
> HP Workstations P4, W2K-Pro, 512 RAM, Gigabit NICs, dual monitors
>
>
|||hi Deb,
Deb Landrum wrote:
> Thanks to all for your input - the MSDE is on a server product, and,
> the resolution was actually an issue within the SQL application that
> we are running. For whatever reason, although the server name was
> properly and correctly shown for the workstations to access it, the
> problematic ones were receiving the error. However, by substituting
> the IP address of the server for the actual server name, those same
> workstations were once again able to access the application.
> I don't know if this would be a particular weakness in the proprietary
> application that we're using or not, but, again, Thank You to all for
> replying.
you probably have a Name resolution problem in your LAN... let your Net
admin gain his money :D:D
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Concurrent MSDE SQL and SQL 2000/2005

We have two servers that need to be upgraded. One has an app that uses the
MSDE version of SQL. The second uses SQL 2000. Neither has very many users
.
Could we purchase a single new server, and run both versions of SQL on it
concurrently? Would it make a difference if the SQL 2000 is eventually
upgraded to SQL 2005, while the MSDE version stays at 2000 instead of going
to SQL Express?
Thanks!
EmilyYou could run both versions on one server, but it would require having
a named instance, which adds a little bit more hassle. One would be
the default instance and you would connect to it as per normal but the
for the named instance you would have to connect to it as
ComputerName\InstanceName. My recommendation would be to just install
SQL 2000/2005 on your new server and host all databases on the one
instance. Is their any particular reason you want them on seperate
instances?|||Thank you for your response, Paul. My hesitation in using two instances is
simply that I am new to SQL, and new to these applications, and there isn't
anyone here who knows much about them. Having never had SQL training, I am
a
little reluctant to change any of the default settings. However, the
financial people don't want to buy two servers. The vendors are not very
helpful.
"Paul T." wrote:

>
> You could run both versions on one server, but it would require having
> a named instance, which adds a little bit more hassle. One would be
> the default instance and you would connect to it as per normal but the
> for the named instance you would have to connect to it as
> ComputerName\InstanceName. My recommendation would be to just install
> SQL 2000/2005 on your new server and host all databases on the one
> instance. Is their any particular reason you want them on seperate
> instances?
>|||If you are reluctant to change default settings, and you have not had
much SQL training your best bet is to continue using the two servers
and upgrade either as needed. If you wanted them on the same server
you would have to change the default settings of at least one instance
as it would need an instance name. If you moved the msde databases
onto the SQL 2000 server, you would need to at least know how to attach
and detach the databases and ensure that all the logins still have the
proper access. If there is no business reason that these applications
are running on seperate servers then I would look into learning enough
about SQL Server to put the databases on the SQL Server 2000 box
together.|||You could run both versions on one server, but it would require having
a named instance, which adds a little bit more hassle. One would be
the default instance and you would connect to it as per normal but the
for the named instance you would have to connect to it as
ComputerName\InstanceName. My recommendation would be to just install
SQL 2000/2005 on your new server and host all databases on the one
instance. Is their any particular reason you want them on seperate
instances?|||Thank you for your response, Paul. My hesitation in using two instances is
simply that I am new to SQL, and new to these applications, and there isn't
anyone here who knows much about them. Having never had SQL training, I am
a
little reluctant to change any of the default settings. However, the
financial people don't want to buy two servers. The vendors are not very
helpful.
"Paul T." wrote:

>
> You could run both versions on one server, but it would require having
> a named instance, which adds a little bit more hassle. One would be
> the default instance and you would connect to it as per normal but the
> for the named instance you would have to connect to it as
> ComputerName\InstanceName. My recommendation would be to just install
> SQL 2000/2005 on your new server and host all databases on the one
> instance. Is their any particular reason you want them on seperate
> instances?
>|||If you are reluctant to change default settings, and you have not had
much SQL training your best bet is to continue using the two servers
and upgrade either as needed. If you wanted them on the same server
you would have to change the default settings of at least one instance
as it would need an instance name. If you moved the msde databases
onto the SQL 2000 server, you would need to at least know how to attach
and detach the databases and ensure that all the logins still have the
proper access. If there is no business reason that these applications
are running on seperate servers then I would look into learning enough
about SQL Server to put the databases on the SQL Server 2000 box
together.

Concurrent MSDE SQL and SQL 2000/2005

We have two servers that need to be upgraded. One has an app that uses the
MSDE version of SQL. The second uses SQL 2000. Neither has very many users.
Could we purchase a single new server, and run both versions of SQL on it
concurrently? Would it make a difference if the SQL 2000 is eventually
upgraded to SQL 2005, while the MSDE version stays at 2000 instead of going
to SQL Express?
Thanks!
EmilyYou could run both versions on one server, but it would require having
a named instance, which adds a little bit more hassle. One would be
the default instance and you would connect to it as per normal but the
for the named instance you would have to connect to it as
ComputerName\InstanceName. My recommendation would be to just install
SQL 2000/2005 on your new server and host all databases on the one
instance. Is their any particular reason you want them on seperate
instances?|||Thank you for your response, Paul. My hesitation in using two instances is
simply that I am new to SQL, and new to these applications, and there isn't
anyone here who knows much about them. Having never had SQL training, I am a
little reluctant to change any of the default settings. However, the
financial people don't want to buy two servers. The vendors are not very
helpful.
"Paul T." wrote:
>
> You could run both versions on one server, but it would require having
> a named instance, which adds a little bit more hassle. One would be
> the default instance and you would connect to it as per normal but the
> for the named instance you would have to connect to it as
> ComputerName\InstanceName. My recommendation would be to just install
> SQL 2000/2005 on your new server and host all databases on the one
> instance. Is their any particular reason you want them on seperate
> instances?
>|||If you are reluctant to change default settings, and you have not had
much SQL training your best bet is to continue using the two servers
and upgrade either as needed. If you wanted them on the same server
you would have to change the default settings of at least one instance
as it would need an instance name. If you moved the msde databases
onto the SQL 2000 server, you would need to at least know how to attach
and detach the databases and ensure that all the logins still have the
proper access. If there is no business reason that these applications
are running on seperate servers then I would look into learning enough
about SQL Server to put the databases on the SQL Server 2000 box
together.

Concurrency puzzle (ODBC API related)

I hope this is not outside the scope of this newsgroup.

have been useing BulkAdd with ms-access with good success.
Until now that I;m switching to MSDE which is on a shared server.

The problem is that MSDE gives an error:
Invalid attribute/option identifier, sql state=HY092
which corresponds to:
SQL_ATTR_CONCURRENCY statement attribute was set to SQL_CONCUR_READ_ONLY.

but my BulkAdd routine explicitly has a call to:
SQLSetStmtAttr(stHndl, SQL_ATTR_CONCURRENCY, SQL_CONCUR_LOCK , iRet)

I cannot imagine how the concurrency is readonly.
Could it be that I'm looking at the wrong place or the wrong attribute?

Thanks in adv.Ernesto (tsh@.mathematicuslabs.com) writes:
> have been useing BulkAdd with ms-access with good success.
> Until now that I;m switching to MSDE which is on a shared server.
> The problem is that MSDE gives an error:
> Invalid attribute/option identifier, sql state=HY092
> which corresponds to:
> SQL_ATTR_CONCURRENCY statement attribute was set to SQL_CONCUR_READ_ONLY.
> but my BulkAdd routine explicitly has a call to:
> SQLSetStmtAttr(stHndl, SQL_ATTR_CONCURRENCY, SQL_CONCUR_LOCK , iRet)
> I cannot imagine how the concurrency is readonly.
> Could it be that I'm looking at the wrong place or the wrong attribute?

Without seeing your code, it is not possible to give a definitive answer.
(And it doesn't help that I have little experience of ODBC API programming.)

But checking the docs, my conclusion is that the SELECT statement for your
cursor is such that the cursor is not updatable. This could be due to
several causes: lack of primary key, use of expressions etc. Would need to
see the SQL code together with some information on the table to say more.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland ,
You are good!
I changed the query which created the table to create a primary key and it
SEEMS to solve the problem.
(It's a good thing having a primary key is ok in this case, else I would not
know how to "solve" this.)

I'm not completely out of the woods yet, but I think now there is another
unrelated problem.
Hopefully you'll keep reading if I am fooled and the problem is still there.

Now if you can only tell me how or where in the world can I learn these
things about the odbc api.

Not having found any good resource which explains these things, the way I
operate is by prayer only and geenrous people like yourself.

E|||Ernesto (tsh@.mathematicuslabs.com) writes:
> Now if you can only tell me how or where in the world can I learn these
> things about the odbc api.

The place to read about the ODBC API in general is the MDAC Books Online,
which comes with the MDAC SDK. Then there is a section in the SQL Server
Books Online which covers specifics for the SQL Server ODBC Driver.

For this particular issue, though, it's more of a general understanding
of databases in general, and in this particular case about cursors. I
know that Kalen Deleany has a chapter on cursors in her "Inside SQL
Server 2000". I doubt that she says much about ODBC, but the same issues
apply when you use cursors in T-SQL.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thank you for the sources. I will look them up.
One more dumb question in case you can respond:

How would I do this if I did not want to add a primary key to the table?|||On Wed, 7 Jul 2004 14:20:02 -0400, Ernesto wrote:

> Thank you for the sources. I will look them up.
> One more dumb question in case you can respond:
> How would I do this if I did not want to add a primary key to the table?

You could add it to a temporary table that did have a primary key, then
execute an INSERT INTO command to transfer it to the permanent table.

However, a primary key is almost always a good idea. Some will argue that
without a primary key it isn't a table at all.|||>> How would I do this if I did not want to add a primary key to the
table? <<

Without a key, this is not a table; it is a punch card file with a
little class. You would return to what we used to call "EDP"
(Electronic Data Processing) in the late 1950's and throw away all 30
years of progress.

Well, you asked and that is the answer.

--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!