How to assign quota on a specific datafile?

Hi Faculties,
Is is possible to assign space quotas on filegroup(s)/files to
database users?

*** Sent via Developersdex http://www.developersdex.com ***"debian mojo" <debian_mojo@.yahoo.com> wrote in message
news:Rj2je.879$XT1.662@.news.uswest.net...
>
> Hi Faculties,
> Is is possible to assign space quotas on filegroup(s)/files to
> database users?
>
> *** Sent via Developersdex http://www.developersdex.com ***

No - you can limit/fix the size of database files (see ALTER DATABASE), but
there is no support for quotas within a database.

Simon

How to assign quota on a specific datafile?

Hi Faculties,
Is is possible to assign space quotas on filegroup(s)/files to
database users?

*** Sent via Developersdex http://www.developersdex.com ***"debian mojo" <debian_mojo@.yahoo.com> wrote in message
news:Rj2je.879$XT1.662@.news.uswest.net...
>
> Hi Faculties,
> Is is possible to assign space quotas on filegroup(s)/files to
> database users?
>
> *** Sent via Developersdex http://www.developersdex.com ***

No - you can limit/fix the size of database files (see ALTER DATABASE), but
there is no support for quotas within a database.

Simon

How to alert via email when Agent records errors/warning in log?

We are in on SQL2000 and our SQL Server Agent started to receive errors for
Mail Profile not available. It wasn't until we looked at the agent error log
we saw the problem.
Is there any way to report the SQL Server Agent Warnings/Errors via email or
alert as a pro-active stance?
Hi
Instead of using alerts you could have steps that send emails, to avoid mapi
you can use SMTP see http://www.sqldev.net/xp/xpsmtp.htm. The sending of the
email can be determined by the jobs workflow if necessary.
If you are looking for a product to monitor your server then you may want to
check out MOM.
John
"ssciarrino" wrote:

> We are in on SQL2000 and our SQL Server Agent started to receive errors for
> Mail Profile not available. It wasn't until we looked at the agent error log
> we saw the problem.
> Is there any way to report the SQL Server Agent Warnings/Errors via email or
> alert as a pro-active stance?

How to alert via email when Agent records errors/warning in log?

We are in on SQL2000 and our SQL Server Agent started to receive errors for
Mail Profile not available. It wasn't until we looked at the agent error lo
g
we saw the problem.
Is there any way to report the SQL Server Agent Warnings/Errors via email or
alert as a pro-active stance?Hi
Instead of using alerts you could have steps that send emails, to avoid mapi
you can use SMTP see http://www.sqldev.net/xp/xpsmtp.htm. The sending of the
email can be determined by the jobs workflow if necessary.
If you are looking for a product to monitor your server then you may want to
check out MOM.
John
"ssciarrino" wrote:

> We are in on SQL2000 and our SQL Server Agent started to receive errors fo
r
> Mail Profile not available. It wasn't until we looked at the agent error
log
> we saw the problem.
> Is there any way to report the SQL Server Agent Warnings/Errors via email
or
> alert as a pro-active stance?

How to alert via email when Agent records errors/warning in log?

We are in on SQL2000 and our SQL Server Agent started to receive errors for
Mail Profile not available. It wasn't until we looked at the agent error log
we saw the problem.
Is there any way to report the SQL Server Agent Warnings/Errors via email or
alert as a pro-active stance?Hi
Instead of using alerts you could have steps that send emails, to avoid mapi
you can use SMTP see http://www.sqldev.net/xp/xpsmtp.htm. The sending of the
email can be determined by the jobs workflow if necessary.
If you are looking for a product to monitor your server then you may want to
check out MOM.
John
"ssciarrino" wrote:
> We are in on SQL2000 and our SQL Server Agent started to receive errors for
> Mail Profile not available. It wasn't until we looked at the agent error log
> we saw the problem.
> Is there any way to report the SQL Server Agent Warnings/Errors via email or
> alert as a pro-active stance?sql

How to add tabls to published article without recreating snapshot?

Hi experts:

We have 4 SQL SERVER 2000 servers linked via replication, and due to business changes we have to add one table to the published databases. And the existed database has more than 20g, recreating the snapshot and re-init replication is not allowed as the business cannot be stopped more than 1 hour. So my question is how to add tables to the published article without recreating the snapshot?

By that is not possilbe, can we publish one new article on the same database?

Thanks in advance!

Ron

Hi Ron,

If you are using merge replication, you have to regenerate the snapshot for the entire publication after you added the new article. For snapshot\transactional replication, the snapshot agent will only generate the snapshot for the new article if the immediate_sync property of your publication is set to 0. In any case, you can always put the new article in a separate publication instead.

Hope that helps,

-Raymond

How to add RESTORE permission to Sql server 2000 & 2005 users

How to add RESTORE permission to Sql server 2000 & 2005 users. I need to
add only Restore permission to the existing user.
*** Sent via Developersdex http://www.codecomments.com ***
Mohammed,
Both SQL Server 2000 and 2005 have the same basic comments on RESTORE
permissions. Look down toward the bottom of the Books Online article on
RESTORE. It says:
RESTORE permissions default to members of the sysadmin and dbcreator fixed
server roles and the owner (dbo) of the database ... members of the db_owner
fixed database role do not have RESTORE permissions.
If the dbo, not db_owner, seems confusing it is like this. One login owns
the database, it may be 'sa' or 'MyDomain\MyLogin'. That login maps to the
dbo user and should have rights to restore the database. Inside the
database, many users may be in the db_owner role, but since those are inside
the database to be restored, they do not get the RESTORE permission.
So, you can make logins members of the dbcreator fixed server role if you
are satisfied with the other rights that they will also get.
Or (apparently) you can make one user the owner of a database and he should
then be able to do a restore of that database. (I have not tested the owner
of the database approach today.)
RLF
"Mohamed Kaleel" <compguyy@.gmail.com> wrote in message
news:e5j1rZoUIHA.1212@.TK2MSFTNGP05.phx.gbl...
>
> How to add RESTORE permission to Sql server 2000 & 2005 users. I need to
> add only Restore permission to the existing user.
> *** Sent via Developersdex http://www.codecomments.com ***
|||Hi Russell,
Thanks for reply, i tried this scenario,i am not able to restore the DB.
If i grant sysadmin privilages to the user then i can able to RESTORE
the DB.
Please suggest me.
Thanks,
Kaleel
*** Sent via Developersdex http://www.codecomments.com ***
|||Hi Russell,
Thanks for reply, i tried this scenario,i am not able to restore the DB.
If i grant sysadmin privilages to the user then i can able to RESTORE
the DB.
Please suggest me.
Thanks,
Kaleel
*** Sent via Developersdex http://www.codecomments.com ***
|||Mohammed,
Of course, sysadmin will work, but I created a login RLFTest that is not a
sysadmin for these tests.
Test 1: Granted RLFTest the "dbcreator" server role and the "public" role in
MyDatabase.
RESTORE DATABASE MyDatabase ... was successful.
Test 2: Revoked RLFTest from the "dbcreator" server role and the "public"
role in MyDatabase. Made RLFTest the owner of the database.
USE MyDatabase
exec sp_changedbowner 'RLFTest'
RESTORE DATABASE MyDatabase ... was successful.
So, for me both of the approved non-sysadmin routes worked just fine. You
might retest using the details of what I did. If you still are having
problems, please let me know the details of error messages, etc.
RLF
"Mohamed Kaleel" <Mohamed.Kaleel@.kaleel.com> wrote in message
news:%231HKhE1UIHA.5164@.TK2MSFTNGP03.phx.gbl...
> Hi Russell,
> Thanks for reply, i tried this scenario,i am not able to restore the DB.
> If i grant sysadmin privilages to the user then i can able to RESTORE
> the DB.
> Please suggest me.
> Thanks,
> Kaleel
>
>
> *** Sent via Developersdex http://www.codecomments.com ***
|||For the first Scenario, i get the following error
===================================
Restore failed for Server 'MyMachine\HTMS_PROD'.
(Microsoft.SqlServer.Express.Smo)
For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.0
0.3042.00&EvtSrc=Microsoft.SqlServer.Management.Sm o.ExceptionTemplates.F
ailedOperationExceptionText&EvtID=Restore+Server&L inkId=20476
Program Location:
at Microsoft.SqlServer.Management.Smo.Restore.SqlRest ore(Server srv)
at
Microsoft.SqlServer.Management.SqlManagerUI.SqlRes toreDatabaseOptions.Ru
nRestore()
===================================
System.Data.SqlClient.SqlError: Server user 'TestHtms' is not a valid
user in database 'HTMS_Temp1'. (Microsoft.SqlServer.Express.Smo)
For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.0
0.3042.00&LinkId=20476
Program Location:
at
Microsoft.SqlServer.Management.Smo.ExecutionManage r.ExecuteNonQueryWithM
essage(StringCollection queries, ServerMessageEventHandler
dbccMessageHandler, Boolean errorsAsMessages)
at
Microsoft.SqlServer.Management.Smo.BackupRestoreBa se.ExecuteSql(Server
server, StringCollection queries)
at Microsoft.SqlServer.Management.Smo.Restore.SqlRest ore(Server srv)
*** Sent via Developersdex http://www.codecomments.com ***
|||Mohamed,
Did you grant 'TestHtms' access to the 'HTMS_Temp1' database?
You will notice in my first scenario that it was necessary to grant some
rights to the database, though in my case simply making 'RLFTest' a member
of the public role was enough.
A note: If the restored database did not already have RLFTest as a user,
RLFTest lost access to the database once the restore was complete.
I notice that you are using SQL Server 2005 Express. That is was I also
used to test.
RLF
"Mohamed Kaleel" <Mohamed.Kaleel@.kaleel.com> wrote in message
news:e50kH3DVIHA.4360@.TK2MSFTNGP06.phx.gbl...
> For the first Scenario, i get the following error
> ===================================
> Restore failed for Server 'MyMachine\HTMS_PROD'.
> (Microsoft.SqlServer.Express.Smo)
> --
> For help, click:
> http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.0
> 0.3042.00&EvtSrc=Microsoft.SqlServer.Management.Sm o.ExceptionTemplates.F
> ailedOperationExceptionText&EvtID=Restore+Server&L inkId=20476
> --
> Program Location:
> at Microsoft.SqlServer.Management.Smo.Restore.SqlRest ore(Server srv)
> at
> Microsoft.SqlServer.Management.SqlManagerUI.SqlRes toreDatabaseOptions.Ru
> nRestore()
> ===================================
> System.Data.SqlClient.SqlError: Server user 'TestHtms' is not a valid
> user in database 'HTMS_Temp1'. (Microsoft.SqlServer.Express.Smo)
> --
> For help, click:
> http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.0
> 0.3042.00&LinkId=20476
> --
> Program Location:
> at
> Microsoft.SqlServer.Management.Smo.ExecutionManage r.ExecuteNonQueryWithM
> essage(StringCollection queries, ServerMessageEventHandler
> dbccMessageHandler, Boolean errorsAsMessages)
> at
> Microsoft.SqlServer.Management.Smo.BackupRestoreBa se.ExecuteSql(Server
> server, StringCollection queries)
> at Microsoft.SqlServer.Management.Smo.Restore.SqlRest ore(Server srv)
>
>
> *** Sent via Developersdex http://www.codecomments.com ***

how to add data to my tables (was "SQL Server Newbie")

Hi. I just set up my first sql server database and I've managed to connect to it via ASP as a test.
I'm not sure how to add data to my tables. In MS Access, you can edit the table and add records. How do I do that in SQL Server?
I'm using the Enterprise manager tool to create tables... does it have something i can use?

ThanksThe sky's the limit on how you can add and edit data in SQL tables, but comparable to what you said about editing tables in access... In EM, you can right click a table then select to open table and return all rows, top or specifiy a query. (I usually use top 1 if I plan on adding rows)

I would advise using query analyzer to do any editing though. Much easier and more efficient IMHO.|||When I try to return rows by right clicking on the table, i get an error that says:
An unexpected error happened during this operation.
Query Designer encountered a Query Error: Unspecified error.

??|||If all else fails, and you have primary keys on your tables, you can always just link the tables in MS Access. As Dr_Clong advised, though, you probably want to pick up a book on Transact-SQL. I liked Sams Publishing's "Teach Yourself in 21 Days" book.|||ok... so it sounds like i have to write sql INSERT statements to add data...??
Sorry if that's a dumb question.
I'll look up some books on transact sql.|||I don't know your setup or for that matter what could be causing your error, but I agree the best way to start out is by educating yourself on SQL Server tools and TSQL.|||1. Stop using Enterprise Manager

2. Start Using Query Analyzer

3. Open Books Online and Leave it Open

4. INSERT INTO myTable99(Col1, Col2, Col3) SELECT 'x','y','z'

5. Or bcp data in from flat files

6. Have a Margarita!

7. Post here often

8. Have Another Margarita

9. Go to 8.

How to add a group header and footer for a report

I have a report that is being called via stored proc, and i want to group by contract. when report gets generated i get multiple contracts info. but will be grouped/sorted by contract.

please how can i have a group header and also a group footer to show a summary of each contract information with some calculated fields in it.

i may get 100 records related to 10 contracts , 10 rows for each contract.

as soon as the first contract info is shown on the report it has to show a summary related to the first contract in the group footer, and then continue populating the second contract info and so on.

Please i am totally new to reporting and help would be appreciated. thank you all.

Here's what you can do:

Click anywhere on your table while in Layout.
Right click on the box next to your details row (it has 3 horizontal bold bars on it), and select 'Insert Group'.
On the 'General' tab, make sure 'Include group header' and 'Include group footer' are both checked.
In the 'Group on:' section, drop down the box and select your contract field. (Or you can type: =Fields!contract.Value)
On the 'Sorting' tab, again, pick or type your contract field as above.
On the 'Visibility' tab, make sure that the 'Visible' radio button is selected.
Hit 'OK' and now you can add your fields to the group header and footer.

Hope this helps.

Jarret

|||

Did this work for you Reddymade?

Jarret