How not to display all databases under sql2k using sql management studio?

When you use sql 2005 management studio to connect to a sql 2005 server, you can deny view on any database and one user will not be able to see any database that he/she does not own.

However, what about if this person uses sql 2005 management studio to connect to a sql 2000 server. Since sql 2005 management studio does not call the store procedure sp_MSdbuseraccess, it will display all the database in the server.

It does not seem like I can use "deny view on any database to username" on a sql 2000 server.

Does anyone have any idea what I need to do in order to not show all the database when I use sql 2005 management studio to connect to a sql 2000 server box?

Thank you.EM will show the database as existing, but you can still deny access to the database and prevent people from opening it up or viewing the objects and data inside it.

Only admins should be using Enterprise Manager anyway...|||What I want to do is to deny any login id (who owns certain database) to not be able to see other database at all.

Just like sql 2005, you can run "deny view any database to userid"

However, doesn't seem like sql 2000 has this command.|||The databases are like houses. You can prevent people from going inside, but you can't prevent people from seeing them.

How much of SQL Express is required at user locations?

SQL Express is quite sizable for what I want to do but it appears to have many features that could turn out to be highly desirable.

It makes sense that the entire SQL Express must be installed at the developer's location, but what portion of it is required at the application user's location? All, or some?

Thanks

You still need to install the complete system, by this I mean the standard system. You do not have to install the management tools, but the rest is required.

How much memory is allotted to a single user connection?

Hi, everybody!
I wonder how much memory space is allotted to a single user connection.
I know MS SQL Server 6.5 allots 37KB of memory space to a single connection.
And I heard that MS SQL 2000 allots 500KB of memory space to a single
connection.
Is it true?
Thanks in advance.Where did u get this stat from ... Will be interested to see the same ...
--
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
"Kim Keuk Tae" <zyuuzika@.korea.com> wrote in message
news:OevJwlqlDHA.2432@.TK2MSFTNGP10.phx.gbl...
> Hi, everybody!
> I wonder how much memory space is allotted to a single user connection.
> I know MS SQL Server 6.5 allots 37KB of memory space to a single
connection.
> And I heard that MS SQL 2000 allots 500KB of memory space to a single
> connection.
> Is it true?
> Thanks in advance.
>
>|||When in doubt, always first check the BOL. Under the section "Memory used by
SQL Server 2000 Objects Specifications", the BOL indicates that:
The memory used by a use connection uses 12 KB + (3 * Network Packet Size),
where Network Packet Size is controlled by the 'network packet size'
configuration option. The default is 4KB.
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"Kim Keuk Tae" <zyuuzika@.korea.com> wrote in message
news:OevJwlqlDHA.2432@.TK2MSFTNGP10.phx.gbl...
> Hi, everybody!
> I wonder how much memory space is allotted to a single user connection.
> I know MS SQL Server 6.5 allots 37KB of memory space to a single
connection.
> And I heard that MS SQL 2000 allots 500KB of memory space to a single
> connection.
> Is it true?
> Thanks in advance.
>
>

How much good practice is to user asp:SqlDataSource

I know that <asp:SqlDataSource> is fast, but is it a good practice to use this. The sqldatasource is right there in the aspx page and the SQL for sqldatasource is also right there.

Does that not break the 3 tier model, as normally we call a database layer and we have all the SQL's there.

In one of the code in saw the following. I don't know if it is a good practice to do something like this.

<asp:DropDownList ID="CountryDropDownList" runat="server" DataSourceID="SqlDataSource2" DataTextField="PickListLabel" DataValueField="PickListValue" OnDataBound="hidePanel">
</asp:DropDownList>

<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:webrepos %>"
SelectCommand="SELECT [PickListLabel], [PicklistValue] FROM [PickList_Values] WHERE (([ObjectName] = @.ObjectName) AND ([FieldName] = @.FieldName)) ORDER BY [AbbrCode]">
<SelectParameters>
<asp:Parameter DefaultValue="Account" Name="ObjectName" Type="String" />
<asp:Parameter DefaultValue="Country__c" Name="FieldName" Type="String" />
</SelectParameters>
</asp:SqlDataSource>

If your application doesn't require a business layer, then you could probably use a SqlDataSource safely with stored procedures. However, if you plan on moving to a tiered architecture, then I'd move to an ObjectDataSource which will allow you full flexibility on how you want to access your data.

|||

sahajMarg:

Does that not break the 3 tier model

My current personal site uses the SqlDataSource. I wanted to get something up quickly. I built the site in a few evenings (including the rudimentary backend). If I had decided not to "break the 3 tier model" and opted for custom business objects, it would have taken me a fair bit longer. The SqlDataSource was the perfect choice, given the circumstances. Like Ed suggests, I use stored procedures. But with only a dozen or so pages in the site, I didn't need to. The 3 Tier model is inappropriate in my case.

Having said that, I am toying with the idea of rebuilding it over Christmas with LINQ.

|||

I use both. I tend to use SqlDatasources for filling simple things like dropdowns, gridviews, etc.

But when I need to do complex logic, or non-trivial updates (updating multiple tables that requires transaction support) then I'll skip the SqlDatasource and call my DAL methods directly.

I'm not a big fan of the 3 Tier model to begin with, but it does have its place. It's just that place isn't where I sit usually, but I've been on projects where it was appropriate. That said, I'm also not a huge fan of stored procedures either. In most of my recent work, security is a niceity. There is no real privacy issues, or critically sensitive data involved, but changes come quick. Request, to implementation, to roll out is measured in days, and the simplicity of having inlined SQL where it can all be versioned as a singular unit far out weighs the benefits on my current projects.

If the project holds customer data, credit card information, medical records, or other such sensitive data then I'd probably recommend stored procedures, and possibly a 3 tier approach. If the project involved multiple programmers, developers, and designers then I might recommend a 3 tier approach especially if one or more of them can be focused on the backend data storage.

|||

I'm not keen on fiddling around in the back of a SqlDataSource. I don't know why - it's just a "thing" with me. Where I can't drag, drop, configure and forget, I'll switch to using my helper methods instead.

|||

mike thanks for your reply. now it perfectly makes sense.

How migrate msdb database from SQL 7.0 to SQL 2K

Since by detaching and attaching user databases from SQL 7.0 to SQL 2K, it can be migrated but how to migrate msdb database from 7.0 to 2K since above process will not work? Any suggestion are acceptable..
Thanks!Take a Full Backup Of MSDB database and restore it. That's the simplest way.

For more Info: See BOL .

Hope this Helps .
-Varad|||The model and msdb databases can only be restored from backups created on a Microsoft SQL Server 2000 server. Restore of backups of these databases made on SQL Server version 7.0 or earlier is not supported.|||Then how can one upgrade msdb database from 7.0 to 2000 if it has hundreds of DTS packages? Is there easier method of this migration..|||I think there is some difference between Upgrading the Server and Migrating the database from one server to another server.
Before migrating I think you have to apply the upgrade on Sql 7.0 and then use any migrating scheme.
May be this is of some help:

Upgrading Databases from SQL Server 7.0(bol)

How many users for a database application?

Hi,
lately i've been faced with a dilemma of whether i should choose to have one
database user to access the database from my app and have inbuilt user
management in the app itself or if i should choose to make a database user
for every user of the application, implementing minimal user management on
the app.
Which is best to choose? what is the best practice?
reagrds,Hi,
I recommend you to have seperate users in SQL server for each application
(provide prev. based on requirement), this will allow you to
have more security.
If you have the option of using Windows Authentication to connect to sql
server then you can set
the user and password policies for each user, this enables the highest
security level.
Note:
I will not recommend you to have application level users because of security
reasons.
Thanks
Hari
MCDBA
"Kazar Kunos" <kazar@.inbox.lv> wrote in message
news:e$92kfHiEHA.704@.TK2MSFTNGP09.phx.gbl...
> Hi,
> lately i've been faced with a dilemma of whether i should choose to have
one
> database user to access the database from my app and have inbuilt user
> management in the app itself or if i should choose to make a database user
> for every user of the application, implementing minimal user management on
> the app.
> Which is best to choose? what is the best practice?
> reagrds,
>
>

How many user current "connect" to a row in table.

Hi all...
Iam a newbie and i have one question want to ask experts ... .
I am current working on one App which have some user connect to database at the same time.
And i want to know: how many user connect or use (read , update) to one row in a table?
Could it be possible to know that? If you have one solution to solve this problem , please let me know :) ...
Could it be done by Software , T-SQL or anything ...., iam happy to know.
Thanks all.

Hello,

There's actually no straightforwarffd answer as this will depend upon the transaction isolation level which will in turn determine how many transactions may read a row as well as what state the data read will be in (dirty, comitted etc). Only one transaction can ever modify a row.

Have a look at http://www.microsoft.com/technet/prodtechnol/sql/2005/cncrrncy.mspx and http://msdn2.microsoft.com/en-US/library/ms173763.aspx as well as BOL for information.

Cheers,

Rob

|||Iam using SQL server 2000 and don't have intended to update to sql server 2005, so i can't use RLV.
I ask this question because i want to notify my clients when data was changed by users.
Is there any idea for me to solve this problem? if have , please show me more details
Thanks .:)|||Couldn't you try to use a trigger to make such a mecanism ?|||I think when a user changed data on the table and trigger on that table was fired then only that user was recieved alert from Server. Do you think so ?|||A trigger is fired on a statement level. When the trigger is fire you could write a message / row in a event table which is polled by the application. In another table you could save the information that the application of User A has received the notification but the application of user B did not. Althouh the trigger is fired once you could spread the *news* anout the changed data.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||Thanks for your advise.
But i think traffic network is expensive resource so if my App keep polled the table which store info about what table has changed , it'll impact my performence which i want to avoid.
do you have anothet idea?
Thanks again.|||

Can you state the underlying purpose to what you are doing? It is not a natural thing to be interested in what row a user might be "connected" to. Now if you are talking about locking it for modification, that makes it a bit more clear, and you could use some form of lock to make that happen.

The thing is, SQL works in a very set oriented architecture, so you might touch a million rows when you think you end up only looking at one (it might require a scan of all values to find just one). SQL Server does nice things with locking, and there are techniques like optimistic locking that you can use to enhance concurrency, so if you could give a bit more information we might could suggest a better way.

|||

Louis Davidson wrote:

Can you state the underlying purpose to what you are doing? It is not a natural thing to be interested in what row a user might be "connected" to. Now if you are talking about locking it for modification, that makes it a bit more clear, and you could use some form of lock to make that happen.

The thing is, SQL works in a very set oriented architecture, so you might touch a million rows when you think you end up only looking at one (it might require a scan of all values to find just one). SQL Server does nice things with locking, and there are techniques like optimistic locking that you can use to enhance concurrency, so if you could give a bit more information we might could suggest a better way.

Thanks for your post :).

My purpose at

begin is Sql server or My App will notify my client when data was

changed for any client who current connect to that row.
For example:
-user A connect and load row R1 into dataset(vb.net).
-user B also connect to that table and load row R2
- R1 and R2 has some column which has the same data
==>

when user change data on some column and finished it,a litle later,

because user B did not know data was changed and continue changed that

data...so if i sum on total column(for example)then may be the value

was double and that is what i don't expect.

Sorry about my poor English, but if you understand what i mean , please give me your advise
Thanks.

How many rows...?

I'm developing an app that will allow a user to track clicks and sales by date/time. However, I was wondering with the potential for a TON of clicks, is it feasable to track individual clicks in a SQL Server 2000 or 2005 database? I'm talking potentially 50,000 products receiving thousands of clicks per day, coud the db handle that kind of traffic? Or should I stick with a running total of clicks per product, and forget about the ability to see a breakdown of clicks per day, etc?That data is going to get out of control rather fast. However you can keep a running total by day.|||How many rows can a typical table handle? 1 million? 500 million? 1 billion? More? That will help me figure out if I can track clicks daily, or if I have to track weekly or monthly.|||The only limitation of rows per table in SQL is the storage available, see 'Maximum Capacity Specifications' topic in SQL Books Online

How many concurrent user of msde

i want to know how many user can access to msde in same
time?
Depends what you mean at the "same time". MSDE can handle numerous
connections. Your connection limit is limited by your particular licensing
and how much RAM you have on the workstation. Remember each connection to
MSDE take 40KB of RAM even though you are doing work with the connection or
not.
Up to 8 concurrent worker threads can access SQL Server at one time.
Please reply only to the newsgroup so that others can benefit. When posting,
please state the version of SQL Server being used and the error number/exact
error message text received, if any.
This posting is provided "AS IS" with no warranties, and confers no rights.
|||It's really more involved than simply "8 concurrent worker threads". MSDE is
not limited based on the number of connections--it's governed on how much
work it's asked to do at one time. Each operation (query/batch) consumes at
least one worker thread. These operations typically take a few milliseconds
to several minutes to run (depending on how you write the query). You can
execute 8 of these simultaneously without penalty--MSDE processes them as
quickly as the (single) processor allows. Once you ask for more than 8, the
governor kicks in and slows down all 8 by a fixed factor. When you ask for
more work from MSDE, it slows everything down even more--by a factor of the
number of worker threads over 8.
If you're clever, you can easily support many dozens of users and never see
the governor kick in.
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
"Thomas Carey, r." <tcarey@.Online.microsoft.com> wrote in message
news:76QIHt$kEHA.2792@.cpmsftngxa10.phx.gbl...
> Depends what you mean at the "same time". MSDE can handle numerous
> connections. Your connection limit is limited by your particular
> licensing
> and how much RAM you have on the workstation. Remember each connection to
> MSDE take 40KB of RAM even though you are doing work with the connection
> or
> not.
> Up to 8 concurrent worker threads can access SQL Server at one time.
> Please reply only to the newsgroup so that others can benefit. When
> posting,
> please state the version of SQL Server being used and the error
> number/exact
> error message text received, if any.
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>

How many concurrent user connect to msde

Which one can tell me how many user can access to msde
server in same time?
You can have as many connections as you want. There is a limit of 8
concurrent work processes available but 3 of these are taken up by the system
"michael" wrote:

> Which one can tell me how many user can access to msde
> server in same time?
>
|||hi,
"Andrew" <Andrew@.discussions.microsoft.com> ha scritto nel messaggio
news:325C3340-D75F-49A7-960B-0564CB85B781@.microsoft.com...
> You can have as many connections as you want. There is a limit of 8
> concurrent work processes available but 3 of these are taken up by the
system[vbcol=seagreen]
> "michael" wrote:
you can have more than 8 concurrent workloads... this is not limited...
there's only the tradeoff that, up to the 9th concurrent workload, a delay
will be applied to all executing workloads in order to penalize all of them
more info at
http://msdn.microsoft.com/library/de...r_sa2_0ciq.asp ,
http://msdn.microsoft.com/library/de...r_sa2_6dip.asp
and
http://msdn.microsoft.com/library/de...r_sa2_0neh.asp
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

How long register the Databases without access?

Hi Friends...
I want to know if exist a possibility to check who was the last user in
acceding some databases, and how time passed after it.
I need to know this, because i have more than 100 databases on a clustered
server and i supposed that more than 20 are unused.
Thanks all!
Hi
Not possible unless you switch on extensive auditing like C2 in SQL Server.
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/
"Tinchos" <Tinchos@.discussions.microsoft.com> wrote in message
news:15369103-F255-402F-A148-FEA183AEA2D3@.microsoft.com...
> Hi Friends...
>
> I want to know if exist a possibility to check who was the last user in
> acceding some databases, and how time passed after it.
> I need to know this, because i have more than 100 databases on a clustered
> server and i supposed that more than 20 are unused.
> Thanks all!
>

How long register the Databases without access?

Hi Friends...
I want to know if exist a possibility to check who was the last user in
acceding some databases, and how time passed after it.
I need to know this, because i have more than 100 databases on a clustered
server and i supposed that more than 20 are unused.
Thanks all!Hi
Not possible unless you switch on extensive auditing like C2 in SQL Server.
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/
"Tinchos" <Tinchos@.discussions.microsoft.com> wrote in message
news:15369103-F255-402F-A148-FEA183AEA2D3@.microsoft.com...
> Hi Friends...
>
> I want to know if exist a possibility to check who was the last user in
> acceding some databases, and how time passed after it.
> I need to know this, because i have more than 100 databases on a clustered
> server and i supposed that more than 20 are unused.
> Thanks all!
>

How long register the Databases without access?

Hi Friends...
I want to know if exist a possibility to check who was the last user in
acceding some databases, and how time passed after it.
I need to know this, because i have more than 100 databases on a clustered
server and i supposed that more than 20 are unused.
Thanks all!Hi
Not possible unless you switch on extensive auditing like C2 in SQL Server.
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/
"Tinchos" <Tinchos@.discussions.microsoft.com> wrote in message
news:15369103-F255-402F-A148-FEA183AEA2D3@.microsoft.com...
> Hi Friends...
>
> I want to know if exist a possibility to check who was the last user in
> acceding some databases, and how time passed after it.
> I need to know this, because i have more than 100 databases on a clustered
> server and i supposed that more than 20 are unused.
> Thanks all!
>

how long before sleeping connections disappear

Using SS2000 SP4. How long does it take after a user logs out before their
"sleeping connection" in EM/Current Activity/Process Info disappears? Is
there a way short of rebooting or stopping/starting services to get it to
disappear sooner?
Thanks,
--
Dan D.Hello,
The inactive connections will be removed from SQL Server based on the
application time out setup in appllication layer. Sleeping status doesnt
mean that status is orphan
connection, because most of the time if the process wait for a user Input
or waiting for a lock will be in sleeping mode. It can go for Running
status any time.
Take a look into the below URL:-
http://support.microsoft.com/kb/Q137983
Thanks
Hari
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:04CB0892-63B1-4B6F-8E7F-5E5581A73563@.microsoft.com...
> Using SS2000 SP4. How long does it take after a user logs out before their
> "sleeping connection" in EM/Current Activity/Process Info disappears? Is
> there a way short of rebooting or stopping/starting services to get it to
> disappear sooner?
> Thanks,
> --
> Dan D.|||The connections are held until the application closes them. Many
applications use connection pooling so that closed connections are pooled
for reuse. These will disappear after a short period, IIRC 30-60 seconds.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:04CB0892-63B1-4B6F-8E7F-5E5581A73563@.microsoft.com...
> Using SS2000 SP4. How long does it take after a user logs out before their
> "sleeping connection" in EM/Current Activity/Process Info disappears? Is
> there a way short of rebooting or stopping/starting services to get it to
> disappear sooner?
> Thanks,
> --
> Dan D.|||These connections are persisting longer than that. Maybe the application has
changed the timeout. Thanks.
--
Dan D.
"Dan Guzman" wrote:
> The connections are held until the application closes them. Many
> applications use connection pooling so that closed connections are pooled
> for reuse. These will disappear after a short period, IIRC 30-60 seconds.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:04CB0892-63B1-4B6F-8E7F-5E5581A73563@.microsoft.com...
> > Using SS2000 SP4. How long does it take after a user logs out before their
> > "sleeping connection" in EM/Current Activity/Process Info disappears? Is
> > there a way short of rebooting or stopping/starting services to get it to
> > disappear sooner?
> >
> > Thanks,
> > --
> > Dan D.
>|||Interesting article. Thanks.
--
Dan D.
"Hari Prasad" wrote:
> Hello,
> The inactive connections will be removed from SQL Server based on the
> application time out setup in appllication layer. Sleeping status doesnt
> mean that status is orphan
> connection, because most of the time if the process wait for a user Input
> or waiting for a lock will be in sleeping mode. It can go for Running
> status any time.
> Take a look into the below URL:-
> http://support.microsoft.com/kb/Q137983
> Thanks
> Hari
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:04CB0892-63B1-4B6F-8E7F-5E5581A73563@.microsoft.com...
> > Using SS2000 SP4. How long does it take after a user logs out before their
> > "sleeping connection" in EM/Current Activity/Process Info disappears? Is
> > there a way short of rebooting or stopping/starting services to get it to
> > disappear sooner?
> >
> > Thanks,
> > --
> > Dan D.
>
>

how long before sleeping connections disappear

Using SS2000 SP4. How long does it take after a user logs out before their
"sleeping connection" in EM/Current Activity/Process Info disappears? Is
there a way short of rebooting or stopping/starting services to get it to
disappear sooner?
Thanks,
Dan D.
Hello,
The inactive connections will be removed from SQL Server based on the
application time out setup in appllication layer. Sleeping status doesnt
mean that status is orphan
connection, because most of the time if the process wait for a user Input
or waiting for a lock will be in sleeping mode. It can go for Running
status any time.
Take a look into the below URL:-
http://support.microsoft.com/kb/Q137983
Thanks
Hari
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:04CB0892-63B1-4B6F-8E7F-5E5581A73563@.microsoft.com...
> Using SS2000 SP4. How long does it take after a user logs out before their
> "sleeping connection" in EM/Current Activity/Process Info disappears? Is
> there a way short of rebooting or stopping/starting services to get it to
> disappear sooner?
> Thanks,
> --
> Dan D.
|||The connections are held until the application closes them. Many
applications use connection pooling so that closed connections are pooled
for reuse. These will disappear after a short period, IIRC 30-60 seconds.
Hope this helps.
Dan Guzman
SQL Server MVP
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:04CB0892-63B1-4B6F-8E7F-5E5581A73563@.microsoft.com...
> Using SS2000 SP4. How long does it take after a user logs out before their
> "sleeping connection" in EM/Current Activity/Process Info disappears? Is
> there a way short of rebooting or stopping/starting services to get it to
> disappear sooner?
> Thanks,
> --
> Dan D.
|||These connections are persisting longer than that. Maybe the application has
changed the timeout. Thanks.
Dan D.
"Dan Guzman" wrote:

> The connections are held until the application closes them. Many
> applications use connection pooling so that closed connections are pooled
> for reuse. These will disappear after a short period, IIRC 30-60 seconds.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:04CB0892-63B1-4B6F-8E7F-5E5581A73563@.microsoft.com...
>
|||Interesting article. Thanks.
Dan D.
"Hari Prasad" wrote:

> Hello,
> The inactive connections will be removed from SQL Server based on the
> application time out setup in appllication layer. Sleeping status doesnt
> mean that status is orphan
> connection, because most of the time if the process wait for a user Input
> or waiting for a lock will be in sleeping mode. It can go for Running
> status any time.
> Take a look into the below URL:-
> http://support.microsoft.com/kb/Q137983
> Thanks
> Hari
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:04CB0892-63B1-4B6F-8E7F-5E5581A73563@.microsoft.com...
>
>

how long before sleeping connections disappear

Using SS2000 SP4. How long does it take after a user logs out before their
"sleeping connection" in EM/Current Activity/Process Info disappears? Is
there a way short of rebooting or stopping/starting services to get it to
disappear sooner?
Thanks,
--
Dan D.Hello,
The inactive connections will be removed from SQL Server based on the
application time out setup in appllication layer. Sleeping status doesnt
mean that status is orphan
connection, because most of the time if the process wait for a user Input
or waiting for a lock will be in sleeping mode. It can go for Running
status any time.
Take a look into the below URL:-
http://support.microsoft.com/kb/Q137983
Thanks
Hari
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:04CB0892-63B1-4B6F-8E7F-5E5581A73563@.microsoft.com...
> Using SS2000 SP4. How long does it take after a user logs out before their
> "sleeping connection" in EM/Current Activity/Process Info disappears? Is
> there a way short of rebooting or stopping/starting services to get it to
> disappear sooner?
> Thanks,
> --
> Dan D.|||The connections are held until the application closes them. Many
applications use connection pooling so that closed connections are pooled
for reuse. These will disappear after a short period, IIRC 30-60 seconds.
Hope this helps.
Dan Guzman
SQL Server MVP
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:04CB0892-63B1-4B6F-8E7F-5E5581A73563@.microsoft.com...
> Using SS2000 SP4. How long does it take after a user logs out before their
> "sleeping connection" in EM/Current Activity/Process Info disappears? Is
> there a way short of rebooting or stopping/starting services to get it to
> disappear sooner?
> Thanks,
> --
> Dan D.|||These connections are persisting longer than that. Maybe the application has
changed the timeout. Thanks.
--
Dan D.
"Dan Guzman" wrote:

> The connections are held until the application closes them. Many
> applications use connection pooling so that closed connections are pooled
> for reuse. These will disappear after a short period, IIRC 30-60 seconds.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:04CB0892-63B1-4B6F-8E7F-5E5581A73563@.microsoft.com...
>|||Interesting article. Thanks.
--
Dan D.
"Hari Prasad" wrote:

> Hello,
> The inactive connections will be removed from SQL Server based on the
> application time out setup in appllication layer. Sleeping status doesnt
> mean that status is orphan
> connection, because most of the time if the process wait for a user Input
> or waiting for a lock will be in sleeping mode. It can go for Running
> status any time.
> Take a look into the below URL:-
> http://support.microsoft.com/kb/Q137983
> Thanks
> Hari
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:04CB0892-63B1-4B6F-8E7F-5E5581A73563@.microsoft.com...
>
>

How i can see how many user using database?

Hello,

I want do some store procedure for see how many users using database in this exactly moment.
I want do this because i need to stop DB for execute backup or restore.

And how i can kill all conection for do this.

thanks. Sorry i am learning !I see existe sp_who and sp_who2 , how i can filter user de one db ? example only msdb

Thanks|||Try this select statement to view connected users.

select spid, loginame,b.name db_name, hostname, program_name,a.status,
login_time, last_batch, lastwaittype
from master..sysprocesses a
join master..sysdatabases b on a.dbid = b.dbid

To kill all connections at once I would set server in single user mode and kill that last connection and login by myself.
The thing is as soon as you kill all connections they still can connect back.|||Well i used one store procedure for filters users, databases etc...

But now i dont know how i can see how many users ( only number ) is connected in one database and put one button in one windows aplication for kill all users.

thanks|||I already resolve problem for kill users. Now i just know how i can put in windows aplication some messsage ( Have x users conected in db )

How grant all objects

How
grant user to tables(insert,update,delete,select),Sp,Func,Views !
in one line...
if can be...You'll need to do updates to the system tables, (I believe) and so you'll need to allow ad-hoc updates on those tables - normally not allowed.

However, and I know this is not what you're asking, but I like not giving any permissions on tables, and only giving exec permissions on Stored Procs. This way you protect your tables and you limit what can be done / seen in your database by defining allowed requests and actions in the procedures.

How get table size?

I am trying to create a report showing the allocated size of each table. How can I obtain the size of each user table and place in my report?

Thanks!

I would start with stored proc "sp_spaceused".