How to force space allocation to a SS2000 DB?

I have to force a space allocation to a DB file in order to stop a Diagnostic Manager alert which states that the database is over 80% full. Now, I don't think this alert is rational, nor do I think it is useful in any way but that's the way it goes. My only option is to allocate more space to the DB so that the space used will fall below 80% so the alert will stop being issued.

So, can you tell me how to force a space allocation on an existing db file?

Thanks,

Michael

Hi Michael,

You have to define the initial size for your file as big as you need. You can do it with the MS SQL Server Manager Studio, Databases --> mydatabase --> right clic --> properties --> files

You can do it like this code sample

Code Snippet

USE [master]

GO

ALTER DATABASE [mydatabase] MODIFY FILE ( NAME = N'mydatabase_file_name', SIZE = 10240KB )

GO

Hope it helps.

Laurent

How to find the second largest value in a field !

Hi,

i am taking the values from four tables ,

I am showing the Salesman in the descending order. according to their Sale Amount. by displaying in Descending, user can able to view the
salesman who sold for the highest amount.

Now I want to find the second highest Amount in the field.

for the highest and lowest we can use the Max and Min funtion.

for the second highest value, How can I write the query.

I already check the previous forums. But i couldn't get the idea.

Kindly reply me

Thank you very much,
Chock.Originally posted by chock
Hi,

i am taking the values from four tables ,

I am showing the Salesman in the descending order. according to their Sale Amount. by displaying in Descending, user can able to view the
salesman who sold for the highest amount.

Now I want to find the second highest Amount in the field.

for the highest and lowest we can use the Max and Min funtion.

for the second highest value, How can I write the query.

I already check the previous forums. But i couldn't get the idea.

Kindly reply me

Thank you very much,
Chock.
Well one way would be to say: what is the highest value after the highest value has been excluded (if you follow me):

SELECT MAX(amount)
FROM mytab
WHERE amount != (SELECT MAX(amount) FROM mytab);

Of course, you wouldn't want to use this recursive approach to get the 5th highest amount! For that, you could do:

SELECT amount FROM mytab m1
WHERE 4 =
(SELECT COUNT(DISTINCT amount) FROM mytab m2
WHERE m2.amount > m1.amount
);

i.e. get the amount for which there are exactly 4 higher amounts in the table.|||Hi,

You send me two queries, the first query I understand it. But in the second query

SELECT amount FROM mytab m1
WHERE 4 =
(SELECT COUNT(DISTINCT amount) FROM mytab m2
WHERE m2.amount > m1.amount
);
what's m1 and what's m2. In the previous query you didn't use the m1.

Actually Amount is the Field name we are going to compare and select.
and mytab is the Table Name.
I am new to this so I think i need some more o understand. can you please tell about the m1 and m2.

Thank you very much,
Chock.|||Originally posted by chock
Hi,

You send me two queries, the first query I understand it. But in the second query

SELECT amount FROM mytab m1
WHERE 4 =
(SELECT COUNT(DISTINCT amount) FROM mytab m2
WHERE m2.amount > m1.amount
);
what's m1 and what's m2. In the previous query you didn't use the m1.

Actually Amount is the Field name we are going to compare and select.
and mytab is the Table Name.
I am new to this so I think i need some more o understand. can you please tell about the m1 and m2.

Thank you very much,
Chock.
m1 and m2 are "aliases". I made them up, because I wanted to use the same table "mytab" twice in the same query and compare values. Without aliases the query would be:

SELECT amount FROM mytab
WHERE 4 =
(SELECT COUNT(DISTINCT amount) FROM mytab
WHERE mytab.amount > mytab.amount
);

... which will return no data, because the condition "WHERE mytab.amount > mytab.amount" is nonsense. What I want to say is "WHERE mytab.amount (in this subquery) > mytab.amount (in the main query)". Aliases allow you to do that.|||tony, you may have confused the issue by jumping from the second highest to the fifth

here's another way to get the row with the second highest value:select Salesman, SaleAmount
from SalesTable
where SaleAmount =
( select max(SaleAmount)
from SalesTable
where SaleAmount <
( select max(SaleAmount)
from SalesTable
)
)in english, "get the row where the SaleAmount is the highest SaleAmount that is less than the highest overall SaleAmount"

wouldn't want to nest that too deeply, eh

i believe a good optimiser will evaluate the innermost first (it is not correlated), then the next inner, then do a straight retrieval -- i could be wrong, though (it has happened, and optimizer performance is not my long suit)

rudy
http://r937.com

how to find if index key is ASC or DESC from system tables?

There is a index: CustomerInfo_1
with keys: customerId, EnteryDate DESC
I could not find where the order of index key (i.e. whether the key is ascending or descending) is stored?
I tried system tables such as sysindexes and sysindexkeys tables. But could not find it.
Any help in this regard will be truly appreciated.

Thank you.
Regards,
Anuj GoyalWhen defining indexes, you can specify whether the data for each column is stored in ascending or descending order. If neither direction is specified, ascending is the default.

You can use INDEXKEY_PROPERTY to know the order, refer to books online for more information.

How to find group user login Name

Our system administrator set up an NT server group in order to allow
our users to login to our application via https to our sql server.
The group appears as a User in SQL Server when you look at it in
Enterprise Manager. That said, I can not see the users associated
with the group from Enterprise Manager, but know they can login to the
database.
The problem is this. When we login via the web we get access to the
database without problem, but when you look at the current_user what
you see is the login Name the user entered and NOT the name of the
group/User. That is to say, I can see a UserID which is not listed as
a User in SQL Server and can't see the name of the group, which is
listed as a user in SQL Server.
I need to know who's logging in order to direct them to the
appropriate web page via their role. Before the admin set up the
group, I was using sp_helpuser to get the role, but then again I had
the userID to do this.
The question I have now, is there any way to see what the
group/user is who logged in i.e. the goup listed as the User in
Enterprise Manager? Otherwise I have to build a table of userIDs and
their group/User name, which seems to defeat the purpose of having he
server authenticate users.

Thanks,
Tom"Tom Loach" <tcl4p@.virginia.edu> wrote in message
news:o7vttvktkbdbib39pqklga3f8s9lgdsksc@.4ax.com...
> Our system administrator set up an NT server group in order to allow
> our users to login to our application via https to our sql server.
> The group appears as a User in SQL Server when you look at it in
> Enterprise Manager. That said, I can not see the users associated
> with the group from Enterprise Manager, but know they can login to the
> database.
> The problem is this. When we login via the web we get access to the
> database without problem, but when you look at the current_user what
> you see is the login Name the user entered and NOT the name of the
> group/User. That is to say, I can see a UserID which is not listed as
> a User in SQL Server and can't see the name of the group, which is
> listed as a user in SQL Server.
> I need to know who's logging in order to direct them to the
> appropriate web page via their role. Before the admin set up the
> group, I was using sp_helpuser to get the role, but then again I had
> the userID to do this.
> The question I have now, is there any way to see what the
> group/user is who logged in i.e. the goup listed as the User in
> Enterprise Manager? Otherwise I have to build a table of userIDs and
> their group/User name, which seems to defeat the purpose of having he
> server authenticate users.
> Thanks,
> Tom

I'm not sure I understand your setup completely, but if you use CURRENT_USER
you will see the user name in the current database, not the login name. If
you use SYSTEM_USER instead, you should see the user's login - does this
give the result you expect?

If not, then perhaps you could clarify the sequence of events used to set up
the group, espeically the difference between a server login and a database
user - this is what's not entirely clear from your post above. If you can
provide the actual SQL commands used to set up server access and permissions
for the Windows group, that would be the least ambiguous description,
although of course you may well have done it through Enterprise Manager
instead.

Simon|||Simon,
thanks for your suggestion, but alas it did not work. Iam sorrry
I was not able to make the situation more clear, but perhaps another
try.
We have a group account set up, which if you look at in Enterprise
manager appears as a User i.e. a_Group. Obviously, since this is a
group account it is comprised of multiple users who need access to SQL
Server. The problem we're having is, if you login to the system and
if I run a stored procedure that returns the Current_User, what I get
is not the name of the User i.e. a_Group that appears in Enterprise
Manager, but the UserID i.e. h3tcl that was used to login to the
Windows server.
When I tried to exec sp_helpuser using Current_User I get an error
since SQL Server does not know who the user is (it sees h3tcl). If I
exec sp_helpuser and enter the user name (a_Group) found in
Enterprise manager sp_helpuser works fine and returns the appropriate
data from SQL Server.
So I'm back to the problem is how and where do I look for the group
name? If you have any other ideas I'm more than open. Thanks again
for your input.

Regards,
Tom

On Tue, 16 Dec 2003 16:44:21 +0100, "Simon Hayes" <sql@.hayes.ch>
wrote:

>"Tom Loach" <tcl4p@.virginia.edu> wrote in message
>news:o7vttvktkbdbib39pqklga3f8s9lgdsksc@.4ax.com...
>> Our system administrator set up an NT server group in order to allow
>> our users to login to our application via https to our sql server.
>> The group appears as a User in SQL Server when you look at it in
>> Enterprise Manager. That said, I can not see the users associated
>> with the group from Enterprise Manager, but know they can login to the
>> database.
>> The problem is this. When we login via the web we get access to the
>> database without problem, but when you look at the current_user what
>> you see is the login Name the user entered and NOT the name of the
>> group/User. That is to say, I can see a UserID which is not listed as
>> a User in SQL Server and can't see the name of the group, which is
>> listed as a user in SQL Server.
>> I need to know who's logging in order to direct them to the
>> appropriate web page via their role. Before the admin set up the
>> group, I was using sp_helpuser to get the role, but then again I had
>> the userID to do this.
>> The question I have now, is there any way to see what the
>> group/user is who logged in i.e. the goup listed as the User in
>> Enterprise Manager? Otherwise I have to build a table of userIDs and
>> their group/User name, which seems to defeat the purpose of having he
>> server authenticate users.
>>
>> Thanks,
>> Tom
>I'm not sure I understand your setup completely, but if you use CURRENT_USER
>you will see the user name in the current database, not the login name. If
>you use SYSTEM_USER instead, you should see the user's login - does this
>give the result you expect?
>If not, then perhaps you could clarify the sequence of events used to set up
>the group, espeically the difference between a server login and a database
>user - this is what's not entirely clear from your post above. If you can
>provide the actual SQL commands used to set up server access and permissions
>for the Windows group, that would be the least ambiguous description,
>although of course you may well have done it through Enterprise Manager
>instead.
>Simon