Dislpay Report Parameters selectively?

I was wondering if there is a way I can selective display report parameters?
For example, I have one parameters that lets the user select a report type.
If the user selects 'Daily' I want to display the report parameter that
allows the user select a date. On the other hand if the user selects
'Monthly' I want to display a drop down list that has all months preloaded.
I do not want the dropdown list that contains months to show up if the user
wants to run a 'Daily' report.
Any help would be much appreciated!
TIA
VinayPresuming that you are also able to display a drop-down list of available
dates if the user selects 'Daily' then the way I would do this is to have a
dataset using a stored procedure which takes the Daily/Monthly parameter and
generates the drop-down list accordingly.
HTH,
magendo_man
"Vinay" wrote:
> I was wondering if there is a way I can selective display report parameters?
> For example, I have one parameters that lets the user select a report type.
> If the user selects 'Daily' I want to display the report parameter that
> allows the user select a date. On the other hand if the user selects
> 'Monthly' I want to display a drop down list that has all months preloaded.
> I do not want the dropdown list that contains months to show up if the user
> wants to run a 'Daily' report.
> Any help would be much appreciated!
> TIA
> Vinay
>
>

Disk space information in SQL Server 2005.

As a normal sql user we are not able to get the disk space information by

executing 'xp_fixeddrive' extended stored procedure.

We are able to get the result using sa user and windows authenticated user.

Any help will be appreciated.

You may get the information you seek by querying: sys.sysfiles,

or perhaps using the dynamic management view: sys.dm_db_file_space_usage

Refer to Books Online for more information about either of them.

|||the xp_fixeddrives can get the free space in the disk(drivers). it is different to sys.sysfiles.

|||do you have the permission to run this extend procedure? any error informaiton that you got when you used a normall sql user?

I think it caused by the permission. .when you login as sa or windows administrator account , it belongs to sysadmin group by default.

Disk Array Optimization

Many of our systems are experiencing performance issues with up to 100 user
databases per server. Our loads are typically 90% reads and our systems run
on a single RAID array.
If we could add only ONE additional RAID array to our production servers,
what would be the priority of items to be added to the new array:
1. Non-clustered indexes. On our read-optimized system, there are sometimes
10-14 indexes per table.
2. Transaction log. Would a system experiencing 10% writes gain a large
amount by moving logs to another disk?
3. TempDB. Many of our queries involve a fair amount of sorting and
grouping. Would moving TempDB gain a lot.
Any insights you might offer would be very helpful.
--
Larry Menzin
American Techsystems Corp.It's difficult to say, based on what you've told us. At the very least, go
with RAID10 for those disks. However, consider adding more memory. If you
have Windows 2003 EE, you can pump it up to 32GB. That will help to relieve
the load on the disks.
What is you current setup? Knowing that, we may be able to help further
with what should go where.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Larry Menzin" <LarryMenzin@.discussions.microsoft.com> wrote in message
news:8D67BED4-428F-4DEE-9182-43E516AA720D@.microsoft.com...
Many of our systems are experiencing performance issues with up to 100 user
databases per server. Our loads are typically 90% reads and our systems run
on a single RAID array.
If we could add only ONE additional RAID array to our production servers,
what would be the priority of items to be added to the new array:
1. Non-clustered indexes. On our read-optimized system, there are sometimes
10-14 indexes per table.
2. Transaction log. Would a system experiencing 10% writes gain a large
amount by moving logs to another disk?
3. TempDB. Many of our queries involve a fair amount of sorting and
grouping. Would moving TempDB gain a lot.
Any insights you might offer would be very helpful.
Larry Menzin
American Techsystems Corp.|||Our disks are RAID10. We have up to 8GB of RAM in our servers (Windows 2000),
but we only seem to be using about 3GB of that RAM. Our system admin is
working on enabling AWE extensions for more memory use.
--
Larry Menzin
American Techsystems Corp.
"Tom Moreau" wrote:
> It's difficult to say, based on what you've told us. At the very least, go
> with RAID10 for those disks. However, consider adding more memory. If you
> have Windows 2003 EE, you can pump it up to 32GB. That will help to relieve
> the load on the disks.
> What is you current setup? Knowing that, we may be able to help further
> with what should go where.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> ..
> "Larry Menzin" <LarryMenzin@.discussions.microsoft.com> wrote in message
> news:8D67BED4-428F-4DEE-9182-43E516AA720D@.microsoft.com...
> Many of our systems are experiencing performance issues with up to 100 user
> databases per server. Our loads are typically 90% reads and our systems run
> on a single RAID array.
> If we could add only ONE additional RAID array to our production servers,
> what would be the priority of items to be added to the new array:
> 1. Non-clustered indexes. On our read-optimized system, there are sometimes
> 10-14 indexes per table.
> 2. Transaction log. Would a system experiencing 10% writes gain a large
> amount by moving logs to another disk?
> 3. TempDB. Many of our queries involve a fair amount of sorting and
> grouping. Would moving TempDB gain a lot.
> Any insights you might offer would be very helpful.
>
> --
> Larry Menzin
> American Techsystems Corp.
>|||"Larry Menzin" <LarryMenzin@.discussions.microsoft.com> wrote in message
news:8D67BED4-428F-4DEE-9182-43E516AA720D@.microsoft.com...
> Many of our systems are experiencing performance issues with up to 100
> user
> databases per server. Our loads are typically 90% reads and our systems
> run
> on a single RAID array.
> If we could add only ONE additional RAID array to our production servers,
> what would be the priority of items to be added to the new array:
> 1. Non-clustered indexes. On our read-optimized system, there are
> sometimes
> 10-14 indexes per table.
> 2. Transaction log. Would a system experiencing 10% writes gain a large
> amount by moving logs to another disk?
> 3. TempDB. Many of our queries involve a fair amount of sorting and
> grouping. Would moving TempDB gain a lot.
> Any insights you might offer would be very helpful.
>
First. What are your performance issues? Why do you think more disks will
help? Even if they will help, wouldn't it be better to do X where X in
(optimize queries, add memory, upgrade the server, buy a second server,
etc)?
Second. With 100 user databases on the server, you can't really do much
that's useful by carefully placing things on disks. The best you can do is
to evenly spread the activity across all the disks. There are many ways to
do this, like putting half the databases on each disk, using multiple files
per filegroup, moving TempDb, etc. The goal is just to spread the load
across the disks.
David|||Yes, you'd want to add the /PAE switch to BOOT.IN, as well as using
sp_configure to enable AWE, as well as setting the amount of memory to use.
I'd go with 6.5GB for now. If you can upgrade to Windows 2003, that would
help, since you can take advantage of the 32GB.
As for your current disks, what do you have where? For example, where are
the logs, the data files, tempdb, ...?
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Larry Menzin" <LarryMenzin@.discussions.microsoft.com> wrote in message
news:380B1642-66C3-489F-9544-5B6846DF03AF@.microsoft.com...
Our disks are RAID10. We have up to 8GB of RAM in our servers (Windows
2000),
but we only seem to be using about 3GB of that RAM. Our system admin is
working on enabling AWE extensions for more memory use.
--
Larry Menzin
American Techsystems Corp.
"Tom Moreau" wrote:
> It's difficult to say, based on what you've told us. At the very least,
> go
> with RAID10 for those disks. However, consider adding more memory. If
> you
> have Windows 2003 EE, you can pump it up to 32GB. That will help to
> relieve
> the load on the disks.
> What is you current setup? Knowing that, we may be able to help further
> with what should go where.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> ..
> "Larry Menzin" <LarryMenzin@.discussions.microsoft.com> wrote in message
> news:8D67BED4-428F-4DEE-9182-43E516AA720D@.microsoft.com...
> Many of our systems are experiencing performance issues with up to 100
> user
> databases per server. Our loads are typically 90% reads and our systems
> run
> on a single RAID array.
> If we could add only ONE additional RAID array to our production servers,
> what would be the priority of items to be added to the new array:
> 1. Non-clustered indexes. On our read-optimized system, there are
> sometimes
> 10-14 indexes per table.
> 2. Transaction log. Would a system experiencing 10% writes gain a large
> amount by moving logs to another disk?
> 3. TempDB. Many of our queries involve a fair amount of sorting and
> grouping. Would moving TempDB gain a lot.
> Any insights you might offer would be very helpful.
>
> --
> Larry Menzin
> American Techsystems Corp.
>|||Our issues are primarily slow query performance (up to 20 seconds for some
queries).
The databases are not designed optimally and we are trying to buy time to
redesign parts of the database and application. However, the servers are 7x24
production servers and our maintenance window is small.
We do have one very large table in each database that is running up against
the 8k rowsize limitation. Our thoughts are that the same disk drive is
contending for indexes and tables at the same time and splitting off the
non-clustered indexes might be useful.
On the memory side, servers are equipped with 8GB of RAM and hardware seems
to be adequate. Since SQL Server is primarily a disk-bound application, we'd
like to try something with the disks that might be useful.
--
Larry Menzin
American Techsystems Corp.
"David Browne" wrote:
> "Larry Menzin" <LarryMenzin@.discussions.microsoft.com> wrote in message
> news:8D67BED4-428F-4DEE-9182-43E516AA720D@.microsoft.com...
> > Many of our systems are experiencing performance issues with up to 100
> > user
> > databases per server. Our loads are typically 90% reads and our systems
> > run
> > on a single RAID array.
> >
> > If we could add only ONE additional RAID array to our production servers,
> > what would be the priority of items to be added to the new array:
> >
> > 1. Non-clustered indexes. On our read-optimized system, there are
> > sometimes
> > 10-14 indexes per table.
> >
> > 2. Transaction log. Would a system experiencing 10% writes gain a large
> > amount by moving logs to another disk?
> >
> > 3. TempDB. Many of our queries involve a fair amount of sorting and
> > grouping. Would moving TempDB gain a lot.
> >
> > Any insights you might offer would be very helpful.
> >
> >
> First. What are your performance issues? Why do you think more disks will
> help? Even if they will help, wouldn't it be better to do X where X in
> (optimize queries, add memory, upgrade the server, buy a second server,
> etc)?
> Second. With 100 user databases on the server, you can't really do much
> that's useful by carefully placing things on disks. The best you can do is
> to evenly spread the activity across all the disks. There are many ways to
> do this, like putting half the databases on each disk, using multiple files
> per filegroup, moving TempDb, etc. The goal is just to spread the load
> across the disks.
> David
>
>|||Tom, we have everything on the same RAID 10 array.
--
Larry Menzin
American Techsystems Corp.
"Tom Moreau" wrote:
> Yes, you'd want to add the /PAE switch to BOOT.IN, as well as using
> sp_configure to enable AWE, as well as setting the amount of memory to use.
> I'd go with 6.5GB for now. If you can upgrade to Windows 2003, that would
> help, since you can take advantage of the 32GB.
> As for your current disks, what do you have where? For example, where are
> the logs, the data files, tempdb, ...?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> ..
> "Larry Menzin" <LarryMenzin@.discussions.microsoft.com> wrote in message
> news:380B1642-66C3-489F-9544-5B6846DF03AF@.microsoft.com...
> Our disks are RAID10. We have up to 8GB of RAM in our servers (Windows
> 2000),
> but we only seem to be using about 3GB of that RAM. Our system admin is
> working on enabling AWE extensions for more memory use.
> --
> Larry Menzin
> American Techsystems Corp.
>
> "Tom Moreau" wrote:
> > It's difficult to say, based on what you've told us. At the very least,
> > go
> > with RAID10 for those disks. However, consider adding more memory. If
> > you
> > have Windows 2003 EE, you can pump it up to 32GB. That will help to
> > relieve
> > the load on the disks.
> >
> > What is you current setup? Knowing that, we may be able to help further
> > with what should go where.
> >
> > --
> > Tom
> >
> > ----
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> > SQL Server MVP
> > Toronto, ON Canada
> > ..
> > "Larry Menzin" <LarryMenzin@.discussions.microsoft.com> wrote in message
> > news:8D67BED4-428F-4DEE-9182-43E516AA720D@.microsoft.com...
> > Many of our systems are experiencing performance issues with up to 100
> > user
> > databases per server. Our loads are typically 90% reads and our systems
> > run
> > on a single RAID array.
> >
> > If we could add only ONE additional RAID array to our production servers,
> > what would be the priority of items to be added to the new array:
> >
> > 1. Non-clustered indexes. On our read-optimized system, there are
> > sometimes
> > 10-14 indexes per table.
> >
> > 2. Transaction log. Would a system experiencing 10% writes gain a large
> > amount by moving logs to another disk?
> >
> > 3. TempDB. Many of our queries involve a fair amount of sorting and
> > grouping. Would moving TempDB gain a lot.
> >
> > Any insights you might offer would be very helpful.
> >
> >
> > --
> > Larry Menzin
> > American Techsystems Corp.
> >
> >
>|||"Larry Menzin" <LarryMenzin@.discussions.microsoft.com> wrote in message
news:97C04F97-3CF1-4AD3-B476-D06D62225135@.microsoft.com...
> Our issues are primarily slow query performance (up to 20 seconds for some
> queries).
> The databases are not designed optimally and we are trying to buy time to
> redesign parts of the database and application. However, the servers are
> 7x24
> production servers and our maintenance window is small.
> We do have one very large table in each database that is running up
> against
> the 8k rowsize limitation. Our thoughts are that the same disk drive is
> contending for indexes and tables at the same time and splitting off the
> non-clustered indexes might be useful.
> On the memory side, servers are equipped with 8GB of RAM and hardware
> seems
> to be adequate. Since SQL Server is primarily a disk-bound application,
> we'd
> like to try something with the disks that might be useful.
>
Ok. May be. But the statement "SQL Server is primarily a disk-bound
application" is just not true. Your servers, with their particuar
configuration and workload may be disk-bound, but if it's not adding disks
just won't help.
David|||Logs should be on a drive separate from the data. What you could do is move
half of your DB data files to the new array and leave their log files on the
existing array. Then, for the DB's that are still on the old array, move
their log files to the new array. That gives you the safety for the logs
but also potentially gives you the disk I/O performance, since the data are
split across two arrays.
I'd have your boss get out the check book and at least got to Windows 2003
so you can up the memory. Memory is life in SQL Server land.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Larry Menzin" <LarryMenzin@.discussions.microsoft.com> wrote in message
news:44C8C350-FF5A-43F8-B0CF-A8DF5ECF8D9E@.microsoft.com...
Tom, we have everything on the same RAID 10 array.
--
Larry Menzin
American Techsystems Corp.
"Tom Moreau" wrote:
> Yes, you'd want to add the /PAE switch to BOOT.IN, as well as using
> sp_configure to enable AWE, as well as setting the amount of memory to
> use.
> I'd go with 6.5GB for now. If you can upgrade to Windows 2003, that would
> help, since you can take advantage of the 32GB.
> As for your current disks, what do you have where? For example, where are
> the logs, the data files, tempdb, ...?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> ..
> "Larry Menzin" <LarryMenzin@.discussions.microsoft.com> wrote in message
> news:380B1642-66C3-489F-9544-5B6846DF03AF@.microsoft.com...
> Our disks are RAID10. We have up to 8GB of RAM in our servers (Windows
> 2000),
> but we only seem to be using about 3GB of that RAM. Our system admin is
> working on enabling AWE extensions for more memory use.
> --
> Larry Menzin
> American Techsystems Corp.
>
> "Tom Moreau" wrote:
> > It's difficult to say, based on what you've told us. At the very least,
> > go
> > with RAID10 for those disks. However, consider adding more memory. If
> > you
> > have Windows 2003 EE, you can pump it up to 32GB. That will help to
> > relieve
> > the load on the disks.
> >
> > What is you current setup? Knowing that, we may be able to help further
> > with what should go where.
> >
> > --
> > Tom
> >
> > ----
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> > SQL Server MVP
> > Toronto, ON Canada
> > ..
> > "Larry Menzin" <LarryMenzin@.discussions.microsoft.com> wrote in message
> > news:8D67BED4-428F-4DEE-9182-43E516AA720D@.microsoft.com...
> > Many of our systems are experiencing performance issues with up to 100
> > user
> > databases per server. Our loads are typically 90% reads and our systems
> > run
> > on a single RAID array.
> >
> > If we could add only ONE additional RAID array to our production
> > servers,
> > what would be the priority of items to be added to the new array:
> >
> > 1. Non-clustered indexes. On our read-optimized system, there are
> > sometimes
> > 10-14 indexes per table.
> >
> > 2. Transaction log. Would a system experiencing 10% writes gain a large
> > amount by moving logs to another disk?
> >
> > 3. TempDB. Many of our queries involve a fair amount of sorting and
> > grouping. Would moving TempDB gain a lot.
> >
> > Any insights you might offer would be very helpful.
> >
> >
> > --
> > Larry Menzin
> > American Techsystems Corp.
> >
> >
>|||SQL Server is data bound, with correct configuration this data should reside
in memory and therefore not disk bound.
I haven't got the original question but I would say any table that is having
issues with the 8k limit is bad. To alleviate this without a redesign you
need to seriously look at your indexes.
As a simple approach running profiler and then putting the output through
the index tuning adviser migth be your quickest solution.
A couple of questons,
How big are your databases? How many transactions are you processing?
Simon Sabin
SQL Server MVP
http://sqljunkies.com/weblog/simons
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:ug0sYELXGHA.3656@.TK2MSFTNGP05.phx.gbl...
> "Larry Menzin" <LarryMenzin@.discussions.microsoft.com> wrote in message
> news:97C04F97-3CF1-4AD3-B476-D06D62225135@.microsoft.com...
>> Our issues are primarily slow query performance (up to 20 seconds for
>> some
>> queries).
>> The databases are not designed optimally and we are trying to buy time to
>> redesign parts of the database and application. However, the servers are
>> 7x24
>> production servers and our maintenance window is small.
>> We do have one very large table in each database that is running up
>> against
>> the 8k rowsize limitation. Our thoughts are that the same disk drive is
>> contending for indexes and tables at the same time and splitting off the
>> non-clustered indexes might be useful.
>> On the memory side, servers are equipped with 8GB of RAM and hardware
>> seems
>> to be adequate. Since SQL Server is primarily a disk-bound application,
>> we'd
>> like to try something with the disks that might be useful.
> Ok. May be. But the statement "SQL Server is primarily a disk-bound
> application" is just not true. Your servers, with their particuar
> configuration and workload may be disk-bound, but if it's not adding disks
> just won't help.
> David
>

discover all permissions for a user

Hi folks,

Is there any easy way of finding out, using a query, all permissions that a user has on any securable? Also, what sort of permission (e.g. ALTER, SELECT, INSERT etc..)

I'm going to hunt around on Google but thought I'd post here in case anyone can tell me before I find it.

thanks in advance.

-Jamie

ignore this.

select *

from sys.database_permissions

does me right!

Doh!

-Jamie

disconnecting yourself after setting db to single user

Is there a way to drop your connection to a database, specifically after you
have set the database to single user mode?
What I'm trying to do is run a test restore of a SQL Database. What I'm
doing is using the command :
ALTER DATABASE <database name> SET SINGLE_USER WITH ROLLBACK IMMEDIATE.
to put the db into single user mode, however the problem is that in the
process of doing this it establishes a connection to the db, therefore
taking the one and only available connection to the db. Currently the only
way I can disconnect the connection is to re-start SQL, so I can then run
the restore, and the backup server can successfully connect to the db
(otherwise it complains that it cannot obtain exclusive access to the
database as it is in use).
Needless to say I'd prefer to not need to kill all SQL connections to the
entire server just so I can test the backups are being successful.
KeithWhy not grab the SINGLE_USER for yourself and do the restore.
ALTER DATABASE <dbname> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
go
USE <dbname>
go
RESTORE ...
"Keith Langmead" <klangmead@.nospam.kms.co.uk> wrote in message
news:%23zT%23sPetEHA.2192@.TK2MSFTNGP14.phx.gbl...
> Is there a way to drop your connection to a database, specifically after
> you
> have set the database to single user mode?
> What I'm trying to do is run a test restore of a SQL Database. What I'm
> doing is using the command :
> ALTER DATABASE <database name> SET SINGLE_USER WITH ROLLBACK IMMEDIATE.
> to put the db into single user mode, however the problem is that in the
> process of doing this it establishes a connection to the db, therefore
> taking the one and only available connection to the db. Currently the only
> way I can disconnect the connection is to re-start SQL, so I can then run
> the restore, and the backup server can successfully connect to the db
> (otherwise it complains that it cannot obtain exclusive access to the
> database as it is in use).
> Needless to say I'd prefer to not need to kill all SQL connections to the
> entire server just so I can test the backups are being successful.
> Keith
>|||Because I'm using Backup Exec to do the backup and restore, which is a
different connection to the one that I set the db to single user with. The
error below even happens if I set the db to single user from the backup
server itself, rather than on the SQL Server box.
Keith
"Stringfellow Hawke" <Stringfellow.Hawke@.gmail.com> wrote in message
news:%23XDGbaetEHA.3788@.TK2MSFTNGP09.phx.gbl...
> Why not grab the SINGLE_USER for yourself and do the restore.
> ALTER DATABASE <dbname> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
> go
> USE <dbname>
> go
> RESTORE ...
>
> "Keith Langmead" <klangmead@.nospam.kms.co.uk> wrote in message
> news:%23zT%23sPetEHA.2192@.TK2MSFTNGP14.phx.gbl...
> > Is there a way to drop your connection to a database, specifically after
> > you
> > have set the database to single user mode?
> >
> > What I'm trying to do is run a test restore of a SQL Database. What I'm
> > doing is using the command :
> >
> > ALTER DATABASE <database name> SET SINGLE_USER WITH ROLLBACK IMMEDIATE.
> >
> > to put the db into single user mode, however the problem is that in the
> > process of doing this it establishes a connection to the db, therefore
> > taking the one and only available connection to the db. Currently the
only
> > way I can disconnect the connection is to re-start SQL, so I can then
run
> > the restore, and the backup server can successfully connect to the db
> > (otherwise it complains that it cannot obtain exclusive access to the
> > database as it is in use).
> >
> > Needless to say I'd prefer to not need to kill all SQL connections to
the
> > entire server just so I can test the backups are being successful.
> >
> > Keith
> >
> >
>|||I don't have much experience with Backup Exec but 1 thing springs to mind.
Restore the database with a different name, once restored drop the original
database then rename the restored one.
"Keith Langmead" <klangmead@.nospam.kms.co.uk> wrote in message
news:OgCTK6etEHA.908@.TK2MSFTNGP10.phx.gbl...
> Because I'm using Backup Exec to do the backup and restore, which is a
> different connection to the one that I set the db to single user with. The
> error below even happens if I set the db to single user from the backup
> server itself, rather than on the SQL Server box.
> Keith
> "Stringfellow Hawke" <Stringfellow.Hawke@.gmail.com> wrote in message
> news:%23XDGbaetEHA.3788@.TK2MSFTNGP09.phx.gbl...
>> Why not grab the SINGLE_USER for yourself and do the restore.
>> ALTER DATABASE <dbname> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
>> go
>> USE <dbname>
>> go
>> RESTORE ...
>>
>> "Keith Langmead" <klangmead@.nospam.kms.co.uk> wrote in message
>> news:%23zT%23sPetEHA.2192@.TK2MSFTNGP14.phx.gbl...
>> > Is there a way to drop your connection to a database, specifically
>> > after
>> > you
>> > have set the database to single user mode?
>> >
>> > What I'm trying to do is run a test restore of a SQL Database. What I'm
>> > doing is using the command :
>> >
>> > ALTER DATABASE <database name> SET SINGLE_USER WITH ROLLBACK IMMEDIATE.
>> >
>> > to put the db into single user mode, however the problem is that in the
>> > process of doing this it establishes a connection to the db, therefore
>> > taking the one and only available connection to the db. Currently the
> only
>> > way I can disconnect the connection is to re-start SQL, so I can then
> run
>> > the restore, and the backup server can successfully connect to the db
>> > (otherwise it complains that it cannot obtain exclusive access to the
>> > database as it is in use).
>> >
>> > Needless to say I'd prefer to not need to kill all SQL connections to
> the
>> > entire server just so I can test the backups are being successful.
>> >
>> > Keith
>> >
>> >
>>
>|||That's basically what I'm doing currently.
With Backup Exec, if you want to restore the db to a database other than the
original, (and since it's just a test, I don't want to mess around with the
live db) you need to have an existing db in place which you can point the
restore job to. Backup Exec then needs to be able to gain exclusive access
to the target db before it can restore the information to it.
Keith
"Stringfellow Hawke" <Stringfellow.Hawke@.gmail.com> wrote in message
news:uOdJJKftEHA.2688@.TK2MSFTNGP14.phx.gbl...
> I don't have much experience with Backup Exec but 1 thing springs to mind.
> Restore the database with a different name, once restored drop the
original
> database then rename the restored one.
> "Keith Langmead" <klangmead@.nospam.kms.co.uk> wrote in message
> news:OgCTK6etEHA.908@.TK2MSFTNGP10.phx.gbl...
> > Because I'm using Backup Exec to do the backup and restore, which is a
> > different connection to the one that I set the db to single user with.
The
> > error below even happens if I set the db to single user from the backup
> > server itself, rather than on the SQL Server box.
> >
> > Keith
> >
> > "Stringfellow Hawke" <Stringfellow.Hawke@.gmail.com> wrote in message
> > news:%23XDGbaetEHA.3788@.TK2MSFTNGP09.phx.gbl...
> >> Why not grab the SINGLE_USER for yourself and do the restore.
> >>
> >> ALTER DATABASE <dbname> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
> >> go
> >> USE <dbname>
> >> go
> >> RESTORE ...
> >>
> >>
> >> "Keith Langmead" <klangmead@.nospam.kms.co.uk> wrote in message
> >> news:%23zT%23sPetEHA.2192@.TK2MSFTNGP14.phx.gbl...
> >> > Is there a way to drop your connection to a database, specifically
> >> > after
> >> > you
> >> > have set the database to single user mode?
> >> >
> >> > What I'm trying to do is run a test restore of a SQL Database. What
I'm
> >> > doing is using the command :
> >> >
> >> > ALTER DATABASE <database name> SET SINGLE_USER WITH ROLLBACK
IMMEDIATE.
> >> >
> >> > to put the db into single user mode, however the problem is that in
the
> >> > process of doing this it establishes a connection to the db,
therefore
> >> > taking the one and only available connection to the db. Currently the
> > only
> >> > way I can disconnect the connection is to re-start SQL, so I can then
> > run
> >> > the restore, and the backup server can successfully connect to the db
> >> > (otherwise it complains that it cannot obtain exclusive access to the
> >> > database as it is in use).
> >> >
> >> > Needless to say I'd prefer to not need to kill all SQL connections to
> > the
> >> > entire server just so I can test the backups are being successful.
> >> >
> >> > Keith
> >> >
> >> >
> >>
> >>
> >
> >
>

disconnecting yourself after setting db to single user

Is there a way to drop your connection to a database, specifically after you
have set the database to single user mode?
What I'm trying to do is run a test restore of a SQL Database. What I'm
doing is using the command :
ALTER DATABASE <database name> SET SINGLE_USER WITH ROLLBACK IMMEDIATE.
to put the db into single user mode, however the problem is that in the
process of doing this it establishes a connection to the db, therefore
taking the one and only available connection to the db. Currently the only
way I can disconnect the connection is to re-start SQL, so I can then run
the restore, and the backup server can successfully connect to the db
(otherwise it complains that it cannot obtain exclusive access to the
database as it is in use).
Needless to say I'd prefer to not need to kill all SQL connections to the
entire server just so I can test the backups are being successful.
Keith
Why not grab the SINGLE_USER for yourself and do the restore.
ALTER DATABASE <dbname> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
go
USE <dbname>
go
RESTORE ...
"Keith Langmead" <klangmead@.nospam.kms.co.uk> wrote in message
news:%23zT%23sPetEHA.2192@.TK2MSFTNGP14.phx.gbl...
> Is there a way to drop your connection to a database, specifically after
> you
> have set the database to single user mode?
> What I'm trying to do is run a test restore of a SQL Database. What I'm
> doing is using the command :
> ALTER DATABASE <database name> SET SINGLE_USER WITH ROLLBACK IMMEDIATE.
> to put the db into single user mode, however the problem is that in the
> process of doing this it establishes a connection to the db, therefore
> taking the one and only available connection to the db. Currently the only
> way I can disconnect the connection is to re-start SQL, so I can then run
> the restore, and the backup server can successfully connect to the db
> (otherwise it complains that it cannot obtain exclusive access to the
> database as it is in use).
> Needless to say I'd prefer to not need to kill all SQL connections to the
> entire server just so I can test the backups are being successful.
> Keith
>
|||Because I'm using Backup Exec to do the backup and restore, which is a
different connection to the one that I set the db to single user with. The
error below even happens if I set the db to single user from the backup
server itself, rather than on the SQL Server box.
Keith
"Stringfellow Hawke" <Stringfellow.Hawke@.gmail.com> wrote in message
news:%23XDGbaetEHA.3788@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Why not grab the SINGLE_USER for yourself and do the restore.
> ALTER DATABASE <dbname> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
> go
> USE <dbname>
> go
> RESTORE ...
>
> "Keith Langmead" <klangmead@.nospam.kms.co.uk> wrote in message
> news:%23zT%23sPetEHA.2192@.TK2MSFTNGP14.phx.gbl...
only[vbcol=seagreen]
run[vbcol=seagreen]
the
>
|||I don't have much experience with Backup Exec but 1 thing springs to mind.
Restore the database with a different name, once restored drop the original
database then rename the restored one.
"Keith Langmead" <klangmead@.nospam.kms.co.uk> wrote in message
news:OgCTK6etEHA.908@.TK2MSFTNGP10.phx.gbl...
> Because I'm using Backup Exec to do the backup and restore, which is a
> different connection to the one that I set the db to single user with. The
> error below even happens if I set the db to single user from the backup
> server itself, rather than on the SQL Server box.
> Keith
> "Stringfellow Hawke" <Stringfellow.Hawke@.gmail.com> wrote in message
> news:%23XDGbaetEHA.3788@.TK2MSFTNGP09.phx.gbl...
> only
> run
> the
>
|||That's basically what I'm doing currently.
With Backup Exec, if you want to restore the db to a database other than the
original, (and since it's just a test, I don't want to mess around with the
live db) you need to have an existing db in place which you can point the
restore job to. Backup Exec then needs to be able to gain exclusive access
to the target db before it can restore the information to it.
Keith
"Stringfellow Hawke" <Stringfellow.Hawke@.gmail.com> wrote in message
news:uOdJJKftEHA.2688@.TK2MSFTNGP14.phx.gbl...
> I don't have much experience with Backup Exec but 1 thing springs to mind.
> Restore the database with a different name, once restored drop the
original[vbcol=seagreen]
> database then rename the restored one.
> "Keith Langmead" <klangmead@.nospam.kms.co.uk> wrote in message
> news:OgCTK6etEHA.908@.TK2MSFTNGP10.phx.gbl...
The[vbcol=seagreen]
I'm[vbcol=seagreen]
IMMEDIATE.[vbcol=seagreen]
the[vbcol=seagreen]
therefore
>

disconnecting yourself after setting db to single user

Is there a way to drop your connection to a database, specifically after you
have set the database to single user mode?
What I'm trying to do is run a test restore of a SQL Database. What I'm
doing is using the command :
ALTER DATABASE <database name> SET SINGLE_USER WITH ROLLBACK IMMEDIATE.
to put the db into single user mode, however the problem is that in the
process of doing this it establishes a connection to the db, therefore
taking the one and only available connection to the db. Currently the only
way I can disconnect the connection is to re-start SQL, so I can then run
the restore, and the backup server can successfully connect to the db
(otherwise it complains that it cannot obtain exclusive access to the
database as it is in use).
Needless to say I'd prefer to not need to kill all SQL connections to the
entire server just so I can test the backups are being successful.
KeithWhy not grab the SINGLE_USER for yourself and do the restore.
ALTER DATABASE <dbname> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
go
USE <dbname>
go
RESTORE ...
"Keith Langmead" <klangmead@.nospam.kms.co.uk> wrote in message
news:%23zT%23sPetEHA.2192@.TK2MSFTNGP14.phx.gbl...
> Is there a way to drop your connection to a database, specifically after
> you
> have set the database to single user mode?
> What I'm trying to do is run a test restore of a SQL Database. What I'm
> doing is using the command :
> ALTER DATABASE <database name> SET SINGLE_USER WITH ROLLBACK IMMEDIATE.
> to put the db into single user mode, however the problem is that in the
> process of doing this it establishes a connection to the db, therefore
> taking the one and only available connection to the db. Currently the only
> way I can disconnect the connection is to re-start SQL, so I can then run
> the restore, and the backup server can successfully connect to the db
> (otherwise it complains that it cannot obtain exclusive access to the
> database as it is in use).
> Needless to say I'd prefer to not need to kill all SQL connections to the
> entire server just so I can test the backups are being successful.
> Keith
>|||Because I'm using Backup Exec to do the backup and restore, which is a
different connection to the one that I set the db to single user with. The
error below even happens if I set the db to single user from the backup
server itself, rather than on the SQL Server box.
Keith
"Stringfellow Hawke" <Stringfellow.Hawke@.gmail.com> wrote in message
news:%23XDGbaetEHA.3788@.TK2MSFTNGP09.phx.gbl...
> Why not grab the SINGLE_USER for yourself and do the restore.
> ALTER DATABASE <dbname> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
> go
> USE <dbname>
> go
> RESTORE ...
>
> "Keith Langmead" <klangmead@.nospam.kms.co.uk> wrote in message
> news:%23zT%23sPetEHA.2192@.TK2MSFTNGP14.phx.gbl...
only[vbcol=seagreen]
run[vbcol=seagreen]
the[vbcol=seagreen]
>|||I don't have much experience with Backup Exec but 1 thing springs to mind.
Restore the database with a different name, once restored drop the original
database then rename the restored one.
"Keith Langmead" <klangmead@.nospam.kms.co.uk> wrote in message
news:OgCTK6etEHA.908@.TK2MSFTNGP10.phx.gbl...
> Because I'm using Backup Exec to do the backup and restore, which is a
> different connection to the one that I set the db to single user with. The
> error below even happens if I set the db to single user from the backup
> server itself, rather than on the SQL Server box.
> Keith
> "Stringfellow Hawke" <Stringfellow.Hawke@.gmail.com> wrote in message
> news:%23XDGbaetEHA.3788@.TK2MSFTNGP09.phx.gbl...
> only
> run
> the
>|||That's basically what I'm doing currently.
With Backup Exec, if you want to restore the db to a database other than the
original, (and since it's just a test, I don't want to mess around with the
live db) you need to have an existing db in place which you can point the
restore job to. Backup Exec then needs to be able to gain exclusive access
to the target db before it can restore the information to it.
Keith
"Stringfellow Hawke" <Stringfellow.Hawke@.gmail.com> wrote in message
news:uOdJJKftEHA.2688@.TK2MSFTNGP14.phx.gbl...
> I don't have much experience with Backup Exec but 1 thing springs to mind.
> Restore the database with a different name, once restored drop the
original
> database then rename the restored one.
> "Keith Langmead" <klangmead@.nospam.kms.co.uk> wrote in message
> news:OgCTK6etEHA.908@.TK2MSFTNGP10.phx.gbl...
The[vbcol=seagreen]
I'm[vbcol=seagreen]
IMMEDIATE.[vbcol=seagreen]
the[vbcol=seagreen]
therefore[vbcol=seagreen]
>sql

disconnect users from a database

I need to be able to drop all user from one database in script...can someon
e point me in the right direction or if its really simple, tell me how?you have to select the spid 's from sysprocesses table that related to db yo
u want and then run kill (spid) command for that spid's.
I think there are some sample scripts to do that on the web like www.databasejorna
l.com etc..
"Jamie Elliott" wrote:

> I need to be able to drop all user from one database in script...can someone poin
t me in the right direction or if its really simple, tell me how?|||Hi,
Did you meant to drop the users or disconnect all users from database.
Disconnenct all users in a database:-
alter database <dbname> set single_user with rollback immediate
Drop the users:-
sp_dropuser <user_name>
You could write a script based on sysusers table.
select 'sp_dropuser ' +name +char(10)+'go' from sysusers where uid>4
Execute the below script with text result and copy the result and paste in a
new window to drop all users apart from system created users.
The drop user will fail if the user owns any object. In that case you have
to change the object owner using sp_changeobjectowner system procedure
(See books online)
Thanks
Hari
MCDBA
"Jamie Elliott" <JamieElliott@.discussions.microsoft.com> wrote in message
news:126F63B8-31B4-4F21-9903-6489C4F862F3@.microsoft.com...
> I need to be able to drop all user from one database in script...can
someone point me in the right direction or if its really simple, tell me
how?

disconnect users from a database

I need to be able to drop all user from one database in script...can someone point me in the right direction or if its really simple, tell me how?
you have to select the spid 's from sysprocesses table that related to db you want and then run kill (spid) command for that spid's.
I think there are some sample scripts to do that on the web like www.databasejornal.com etc..
"Jamie Elliott" wrote:

> I need to be able to drop all user from one database in script...can someone point me in the right direction or if its really simple, tell me how?
|||Hi,
Did you meant to drop the users or disconnect all users from database.
Disconnenct all users in a database:-
alter database <dbname> set single_user with rollback immediate
Drop the users:-
sp_dropuser <user_name>
You could write a script based on sysusers table.
select 'sp_dropuser ' +name +char(10)+'go' from sysusers where uid>4
Execute the below script with text result and copy the result and paste in a
new window to drop all users apart from system created users.
The drop user will fail if the user owns any object. In that case you have
to change the object owner using sp_changeobjectowner system procedure
(See books online)
Thanks
Hari
MCDBA
"Jamie Elliott" <JamieElliott@.discussions.microsoft.com> wrote in message
news:126F63B8-31B4-4F21-9903-6489C4F862F3@.microsoft.com...
> I need to be able to drop all user from one database in script...can
someone point me in the right direction or if its really simple, tell me
how?

disconnect the user

I'm trying to detach my database.But i couldn't do that coz there are some users connected to it. So how can i disconnect them?is there any way to disconnect them from the DB?
Thanks.In SQL server 2000

When you detach a database it should give you an option to kill all user processes, if you click that it will log off all users.

If not I have some t-sql code that does it.|||It worked fine.Thank you very much.

disconnect a user whoz locking the DB with diff SPIDs

I have issued a simple insert statement on table and the statement could not insert a single row into the table.When i check the locks on the tables i found that almost 5000(five thousand) locks on the table for a single user with different SPID(s).
The user is and sql server user(not a windows user) and used to connect to the application.I wanted to disconnect the user from the DB so that all the locks will be freed.But i dunno how to disconnect a user from the DB. I know that i can issue KILL command to Terminate a user process based on the system process ID,but here the same user has nearly 1000 SPIDs.I thought that it would be very big job to kill each and every process and restared the sql server.but i guess its not the correct process to do.
how can i dosconnect a specific user from the DB.

Thanks.Well if you think it's a big job to kill 1,000 spids, how big of a job is it to create them?

I would want to know what process is soing that.

Also, watch the ROLLBACK...it'll be twice as long as letting the work COMMIT

But you've got bigger problems...

ALTER DATABASE <database> SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Will blow everyone out, rollback all uncommitted work, and give you control

Disconection of Database users in mirror configuration

On the machine DBSP01 we have the TEST database running. In the SQL environment we created the user Test. This user was set up to be the database owner of the TEST database. To make a connection to the Test database, the user Test was used and everything works perfect.

On the machine DBSP02 we also created a database called TEST and a user called Test, who was database owner of the TEST database. Again we where able to connect to the database by using the user Test.

Now we set up a so called mirror configuration. This means that we have one active database, called the principal database, and a standby database, called the mirror. The active database is used to communicate to the outside, while the standby database is copying every change from the active database. This way the principal database and the mirror database contain always the same data. When one of the databases is acting as a mirror database, this database cannot be reached from the outside.

After a while it can happen that the principal database fails for some reason. The database is not capable to run properly, so it stops. The mirror database will become the principal database and the outside can connect to the new principal database. Here we have a problem: the user Test is no longer the database owner of the TEST database. For some reason the user Test is disconnected as database owner of the TEST database when the database is acting as mirror. I can’t find the reason for this problem and I can also not find a solution for this problem.

Hi,

I quoted the topic "Managing server metadata and jobs after role switching" in BOL.

"For users to be able to access the database after a role switch, a login on the principal server that has permission to access the principal database, must also be defined on the mirror server. However, the master database cannot be mirrored. Therefore, if on the current principal server, you create a new login to this login for the principal database, you must do the same on the mirror.

The login of every user of the database must be manually defined on the mirror server and on the principal server. Otherwise, when the principal role switches and the former mirror server offers its database as the principal database, users whose logins are not defined on the former mirror cannot access the new principal. The users are orphaned.

If a user is orphaned on the new principal, create the login on the new principal and run sp_change_users_login (Transact-SQL). For more information, see Troubleshooting Orphaned Users. "

Peng

Disconection of Database users in mirror configuration

On the machine DBSP01 we have the TEST database running. In the SQL environment we created the user Test. This user was set up to be the database owner of the TEST database. To make a connection to the Test database, the user Test was used and everything works perfect.

On the machine DBSP02 we also created a database called TEST and a user called Test, who was database owner of the TEST database. Again we where able to connect to the database by using the user Test.

Now we set up a so called mirror configuration. This means that we have one active database, called the principal database, and a standby database, called the mirror. The active database is used to communicate to the outside, while the standby database is copying every change from the active database. This way the principal database and the mirror database contain always the same data. When one of the databases is acting as a mirror database, this database cannot be reached from the outside.

After a while it can happen that the principal database fails for some reason. The database is not capable to run properly, so it stops. The mirror database will become the principal database and the outside can connect to the new principal database. Here we have a problem: the user Test is no longer the database owner of the TEST database. For some reason the user Test is disconnected as database owner of the TEST database when the database is acting as mirror. I can’t find the reason for this problem and I can also not find a solution for this problem.

Hi,

I quoted the topic "Managing server metadata and jobs after role switching" in BOL.

"For users to be able to access the database after a role switch, a login on the principal server that has permission to access the principal database, must also be defined on the mirror server. However, the master database cannot be mirrored. Therefore, if on the current principal server, you create a new login to this login for the principal database, you must do the same on the mirror.

The login of every user of the database must be manually defined on the mirror server and on the principal server. Otherwise, when the principal role switches and the former mirror server offers its database as the principal database, users whose logins are not defined on the former mirror cannot access the new principal. The users are orphaned.

If a user is orphaned on the new principal, create the login on the new principal and run sp_change_users_login (Transact-SQL). For more information, see Troubleshooting Orphaned Users. "

Peng

Disaster Recovery question

What's the best method for preparing for a SQL Server2K disaster? Is backing
up master, msdb and model as well as the logins and user databases enough?
Is it better to restore master and msdb or re-create the logins and jobs
(assuming they were scripted out).
Looking for other perspectives.
Thanks
Ron
Ron hi,
Check this article and you will find some ideas
http://support.microsoft.com/default...b;en-us;307775
HTH
Andreas
"Ron" wrote:

> What's the best method for preparing for a SQL Server2K disaster? Is backing
> up master, msdb and model as well as the logins and user databases enough?
> Is it better to restore master and msdb or re-create the logins and jobs
> (assuming they were scripted out).
> Looking for other perspectives.
> Thanks
> Ron
>
|||Thanks - that shows good "How to" for the details. However I was looking for
more of an overview, bigger picture for DR.
Thanks.
Ron
"Andreas Mavrogenis" wrote:
[vbcol=seagreen]
> Ron hi,
> Check this article and you will find some ideas
> http://support.microsoft.com/default...b;en-us;307775
> HTH
> Andreas
> "Ron" wrote:
|||MASTER, MSDB and user database backup if fine.. this include most of the
things.. other then this, keep on taking registry backup of server nad
doucment your configuration setting of SQL Server.
You should do some dummy test for restoration of databases and validate what
backup policy you have for point in time recovery of user databases.
"Ron" wrote:
[vbcol=seagreen]
> Thanks - that shows good "How to" for the details. However I was looking for
> more of an overview, bigger picture for DR.
> Thanks.
> Ron
> "Andreas Mavrogenis" wrote:
|||If a brand new box is to replace the fail server, prepare the disk drive
mappings, processor, RAM, etc.
Other things include SQL server configuration, startup parameters, memory
configuration, scripted replications, and more.
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:584D6A0B-A59B-4B0E-90FC-F601950641B8@.microsoft.com...
> What's the best method for preparing for a SQL Server2K disaster? Is
> backing
> up master, msdb and model as well as the logins and user databases enough?
> Is it better to restore master and msdb or re-create the logins and jobs
> (assuming they were scripted out).
> Looking for other perspectives.
> Thanks
> Ron
>
|||Hi,
As writen, you must have a documented disaster recovery plan, in order to
perform these actions step-by-step, avoiding to forget something.
Also, if you have another machine, the ideal is to have it on another site
if applicable with all nesecary network routes.
A disaster plan is a subject to discuss because you have many-many option
according to your needs.
Following, for a well designed and documented DRP, in SQL 2000, consider
having a new machine, with SQL 2000 and try to enable log shipping
http://support.microsoft.com/default...b;en-us;323135
Finally, you can check this article for common FAQs on log shipping
http://support.microsoft.com/default...b;en-us;314515
HTH
Andreas
"Richard Ding" wrote:

> If a brand new box is to replace the fail server, prepare the disk drive
> mappings, processor, RAM, etc.
> Other things include SQL server configuration, startup parameters, memory
> configuration, scripted replications, and more.
> "Ron" <Ron@.discussions.microsoft.com> wrote in message
> news:584D6A0B-A59B-4B0E-90FC-F601950641B8@.microsoft.com...
>
>

Disaster Recovery question

What's the best method for preparing for a SQL Server2K disaster? Is backing
up master, msdb and model as well as the logins and user databases enough?
Is it better to restore master and msdb or re-create the logins and jobs
(assuming they were scripted out).
Looking for other perspectives.
Thanks
RonRon hi,
Check this article and you will find some ideas
http://support.microsoft.com/default.aspx?scid=kb;en-us;307775
HTH
Andreas
"Ron" wrote:
> What's the best method for preparing for a SQL Server2K disaster? Is backing
> up master, msdb and model as well as the logins and user databases enough?
> Is it better to restore master and msdb or re-create the logins and jobs
> (assuming they were scripted out).
> Looking for other perspectives.
> Thanks
> Ron
>|||Thanks - that shows good "How to" for the details. However I was looking for
more of an overview, bigger picture for DR.
Thanks.
Ron
"Andreas Mavrogenis" wrote:
> Ron hi,
> Check this article and you will find some ideas
> http://support.microsoft.com/default.aspx?scid=kb;en-us;307775
> HTH
> Andreas
> "Ron" wrote:
> > What's the best method for preparing for a SQL Server2K disaster? Is backing
> > up master, msdb and model as well as the logins and user databases enough?
> >
> > Is it better to restore master and msdb or re-create the logins and jobs
> > (assuming they were scripted out).
> >
> > Looking for other perspectives.
> >
> > Thanks
> >
> > Ron
> >
> >|||MASTER, MSDB and user database backup if fine.. this include most of the
things.. other then this, keep on taking registry backup of server nad
doucment your configuration setting of SQL Server.
You should do some dummy test for restoration of databases and validate what
backup policy you have for point in time recovery of user databases.
"Ron" wrote:
> Thanks - that shows good "How to" for the details. However I was looking for
> more of an overview, bigger picture for DR.
> Thanks.
> Ron
> "Andreas Mavrogenis" wrote:
> > Ron hi,
> > Check this article and you will find some ideas
> > http://support.microsoft.com/default.aspx?scid=kb;en-us;307775
> >
> > HTH
> > Andreas
> >
> > "Ron" wrote:
> >
> > > What's the best method for preparing for a SQL Server2K disaster? Is backing
> > > up master, msdb and model as well as the logins and user databases enough?
> > >
> > > Is it better to restore master and msdb or re-create the logins and jobs
> > > (assuming they were scripted out).
> > >
> > > Looking for other perspectives.
> > >
> > > Thanks
> > >
> > > Ron
> > >
> > >|||If a brand new box is to replace the fail server, prepare the disk drive
mappings, processor, RAM, etc.
Other things include SQL server configuration, startup parameters, memory
configuration, scripted replications, and more.
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:584D6A0B-A59B-4B0E-90FC-F601950641B8@.microsoft.com...
> What's the best method for preparing for a SQL Server2K disaster? Is
> backing
> up master, msdb and model as well as the logins and user databases enough?
> Is it better to restore master and msdb or re-create the logins and jobs
> (assuming they were scripted out).
> Looking for other perspectives.
> Thanks
> Ron
>|||Hi,
As writen, you must have a documented disaster recovery plan, in order to
perform these actions step-by-step, avoiding to forget something.
Also, if you have another machine, the ideal is to have it on another site
if applicable with all nesecary network routes.
A disaster plan is a subject to discuss because you have many-many option
according to your needs.
Following, for a well designed and documented DRP, in SQL 2000, consider
having a new machine, with SQL 2000 and try to enable log shipping
http://support.microsoft.com/default.aspx?scid=kb;en-us;323135
Finally, you can check this article for common FAQs on log shipping
http://support.microsoft.com/default.aspx?scid=kb;en-us;314515
HTH
Andreas
"Richard Ding" wrote:
> If a brand new box is to replace the fail server, prepare the disk drive
> mappings, processor, RAM, etc.
> Other things include SQL server configuration, startup parameters, memory
> configuration, scripted replications, and more.
> "Ron" <Ron@.discussions.microsoft.com> wrote in message
> news:584D6A0B-A59B-4B0E-90FC-F601950641B8@.microsoft.com...
> > What's the best method for preparing for a SQL Server2K disaster? Is
> > backing
> > up master, msdb and model as well as the logins and user databases enough?
> >
> > Is it better to restore master and msdb or re-create the logins and jobs
> > (assuming they were scripted out).
> >
> > Looking for other perspectives.
> >
> > Thanks
> >
> > Ron
> >
> >
>
>

Disaster Recovery question

What's the best method for preparing for a SQL Server2K disaster? Is backin
g
up master, msdb and model as well as the logins and user databases enough?
Is it better to restore master and msdb or re-create the logins and jobs
(assuming they were scripted out).
Looking for other perspectives.
Thanks
RonRon hi,
Check this article and you will find some ideas
http://support.microsoft.com/defaul...kb;en-us;307775
HTH
Andreas
"Ron" wrote:

> What's the best method for preparing for a SQL Server2K disaster? Is back
ing
> up master, msdb and model as well as the logins and user databases enough?
> Is it better to restore master and msdb or re-create the logins and jobs
> (assuming they were scripted out).
> Looking for other perspectives.
> Thanks
> Ron
>|||Thanks - that shows good "How to" for the details. However I was looking fo
r
more of an overview, bigger picture for DR.
Thanks.
Ron
"Andreas Mavrogenis" wrote:
[vbcol=seagreen]
> Ron hi,
> Check this article and you will find some ideas
> http://support.microsoft.com/defaul...kb;en-us;307775
> HTH
> Andreas
> "Ron" wrote:
>|||MASTER, MSDB and user database backup if fine.. this include most of the
things.. other then this, keep on taking registry backup of server nad
doucment your configuration setting of SQL Server.
You should do some dummy test for restoration of databases and validate what
backup policy you have for point in time recovery of user databases.
"Ron" wrote:
[vbcol=seagreen]
> Thanks - that shows good "How to" for the details. However I was looking
for
> more of an overview, bigger picture for DR.
> Thanks.
> Ron
> "Andreas Mavrogenis" wrote:
>|||If a brand new box is to replace the fail server, prepare the disk drive
mappings, processor, RAM, etc.
Other things include SQL server configuration, startup parameters, memory
configuration, scripted replications, and more.
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:584D6A0B-A59B-4B0E-90FC-F601950641B8@.microsoft.com...
> What's the best method for preparing for a SQL Server2K disaster? Is
> backing
> up master, msdb and model as well as the logins and user databases enough?
> Is it better to restore master and msdb or re-create the logins and jobs
> (assuming they were scripted out).
> Looking for other perspectives.
> Thanks
> Ron
>|||Hi,
As writen, you must have a documented disaster recovery plan, in order to
perform these actions step-by-step, avoiding to forget something.
Also, if you have another machine, the ideal is to have it on another site
if applicable with all nesecary network routes.
A disaster plan is a subject to discuss because you have many-many option
according to your needs.
Following, for a well designed and documented DRP, in SQL 2000, consider
having a new machine, with SQL 2000 and try to enable log shipping
http://support.microsoft.com/defaul...kb;en-us;323135
Finally, you can check this article for common FAQs on log shipping
http://support.microsoft.com/defaul...kb;en-us;314515
HTH
Andreas
"Richard Ding" wrote:

> If a brand new box is to replace the fail server, prepare the disk drive
> mappings, processor, RAM, etc.
> Other things include SQL server configuration, startup parameters, memory
> configuration, scripted replications, and more.
> "Ron" <Ron@.discussions.microsoft.com> wrote in message
> news:584D6A0B-A59B-4B0E-90FC-F601950641B8@.microsoft.com...
>
>

Disaplay in report

Hi,

I have a report in which the report parameter 'cases' is a mutli valued.When the user selects 23,12 from that Case parameter now I am getting this result from my Stored procedure like this:

Case Date Route Reason Expen

23 1/1/07 a f d

12 .. .

Like this now in my report I want to display like the following:

Case :23

Date:1/1/07

Route:a

Reason :b

..

this is one page

and in the second page : display the next case information.

Like that Other than Using Table what is the best way to display data like this. Each row from the Select need to be display in one page based on the Case parameter selection.

Thanks in advance

Hi,

I am displaying the each field in the one detail row of table and applied the grouping to the Table based on CaseID to display in each result in one page.

Thanks.

Disable user configurable Subscription

Hi,

Does anyone know if you can disable a users ability to set their own schedules and only choose from shared schedules for their reports. The reason I ask is because we have a window from about 3-7 in the morning where we do DTS and maintenance etc and dont want the databases queried as the results could be wrong. Therefore we want to setup a range of shared schedules for users to pick which wont coincide with that time.

Thanks in advance

Dan.

Sorry this feature does not exist in the current subscription framework. You could write your own delivery extension that did this, but it would not stop someone from using another extension and using a non shared schedule.

Disable the use of Enterprise Manager

I have created a new DB user, ABC, for using ASP.NET application to
connecting to SQL Server. Does it means this ABC user can logon in the
Enterprise Manager to view the data without using the web application' if
so, is there any way to block this ABC user from viewing data in Enterprise
Manager?> I have created a new DB user, ABC, for using ASP.NET application to
> connecting to SQL Server. Does it means this ABC user can logon in the
> Enterprise Manager to view the data without using the web application'
Yes.
> if
> so, is there any way to block this ABC user from viewing data in Enterprise
> Manager?
No. I suggest you use stored procedures and only allow your users to execute the stored procedures.
You might also want to read about application roles...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Grey" <erickwyum@.i-cable.com> wrote in message news:eMZrdqsnEHA.1160@.tk2msftngp13.phx.gbl...
> I have created a new DB user, ABC, for using ASP.NET application to
> connecting to SQL Server. Does it means this ABC user can logon in the
> Enterprise Manager to view the data without using the web application' if
> so, is there any way to block this ABC user from viewing data in Enterprise
> Manager?
>|||TO add to Tibor's comments, if you really, really want that capability, use
Application roles. They allow you to give permission when access is via the
application, but the end user does NOT have a permissions, so he can not go
through SEM or query analyzer and see anything.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Grey" <erickwyum@.i-cable.com> wrote in message
news:eMZrdqsnEHA.1160@.tk2msftngp13.phx.gbl...
> I have created a new DB user, ABC, for using ASP.NET application to
> connecting to SQL Server. Does it means this ABC user can logon in the
> Enterprise Manager to view the data without using the web application' if
> so, is there any way to block this ABC user from viewing data in
Enterprise
> Manager?
>

Disable the use of Enterprise Manager

I have created a new DB user, ABC, for using ASP.NET application to
connecting to SQL Server. Does it means this ABC user can logon in the
Enterprise Manager to view the data without using the web application? if
so, is there any way to block this ABC user from viewing data in Enterprise
Manager?
> I have created a new DB user, ABC, for using ASP.NET application to
> connecting to SQL Server. Does it means this ABC user can logon in the
> Enterprise Manager to view the data without using the web application?
Yes.

> if
> so, is there any way to block this ABC user from viewing data in Enterprise
> Manager?
No. I suggest you use stored procedures and only allow your users to execute the stored procedures.
You might also want to read about application roles...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Grey" <erickwyum@.i-cable.com> wrote in message news:eMZrdqsnEHA.1160@.tk2msftngp13.phx.gbl...
> I have created a new DB user, ABC, for using ASP.NET application to
> connecting to SQL Server. Does it means this ABC user can logon in the
> Enterprise Manager to view the data without using the web application? if
> so, is there any way to block this ABC user from viewing data in Enterprise
> Manager?
>
|||TO add to Tibor's comments, if you really, really want that capability, use
Application roles. They allow you to give permission when access is via the
application, but the end user does NOT have a permissions, so he can not go
through SEM or query analyzer and see anything.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Grey" <erickwyum@.i-cable.com> wrote in message
news:eMZrdqsnEHA.1160@.tk2msftngp13.phx.gbl...
> I have created a new DB user, ABC, for using ASP.NET application to
> connecting to SQL Server. Does it means this ABC user can logon in the
> Enterprise Manager to view the data without using the web application? if
> so, is there any way to block this ABC user from viewing data in
Enterprise
> Manager?
>