rename relational DB

I would like to rename a database progrmatically. if any users are connected
,
this causes an error. is there a way by which i can forcefully close all
connection and restore the database.
Thanks
GSUse this code before restoring/renaming
ALTER DATABASE MYBASE SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE
This will restrict the user to the database. NOTE: This will rollback any
uncommitted transactions!
--
Adam J Warne, MCDBA
"GS" wrote:

> I would like to rename a database progrmatically. if any users are connect
ed,
> this causes an error. is there a way by which i can forcefully close all
> connection and restore the database.
> Thanks
> GS|||I have a problem again. I dont want to hardcode my database name ; I would
rather pass this as a parameter to 'ALTER DATABASE' function.
Is this possible
"Adam Warne" wrote:
> Use this code before restoring/renaming
> ALTER DATABASE MYBASE SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE
> This will restrict the user to the database. NOTE: This will rollback any
> uncommitted transactions!
> --
> Adam J Warne, MCDBA
>
> "GS" wrote:
>

rename a database

Hi,
How do I rename a database without changing the users permissions?
I have tried
ALTER DATABASE old_db_name
MODIFY NAME = new_db_name
and
EXEC sp_renamedb 'old_db_name','new_db_name'
and both give me:
Server: Msg 5030, Level 16, State 2, Line 1
The database could not be exclusively locked to perform the operation.
Thanks
// MalinMake sure that Old_db is not used by other resources at the time of
rename
Madhivanan

Removing operators from System administrator Role

Hi List!
I have 2 users with SA admin rol granted on a SQL2000 SP3 Server. I want to
take them off, but the people said me that they need to monitor a lot of job
s
running on this server, so what priviledges does i need to grant them to all
o
to see the results of this jobs?496371c0860" target="_blank">http://groups.google.de/group/micro.../>
496371c0860
http://groups.google.de/group/micro...853a5d26aa1b4e1
HTH, Jens Suessmeyer.
"Tinchos" wrote:

> Hi List!
> I have 2 users with SA admin rol granted on a SQL2000 SP3 Server. I want t
o
> take them off, but the people said me that they need to monitor a lot of j
obs
> running on this server, so what priviledges does i need to grant them to a
llo
> to see the results of this jobs?
>

Removing operators from System administrator Role

Hi List!
I have 2 users with SA admin rol granted on a SQL2000 SP3 Server. I want to
take them off, but the people said me that they need to monitor a lot of jobs
running on this server, so what priviledges does i need to grant them to allo
to see the results of this jobs?
http://groups.google.de/group/micros...9c4496371c0860
http://groups.google.de/group/micros...53a5d26aa1b4e1
HTH, Jens Suessmeyer.
"Tinchos" wrote:

> Hi List!
> I have 2 users with SA admin rol granted on a SQL2000 SP3 Server. I want to
> take them off, but the people said me that they need to monitor a lot of jobs
> running on this server, so what priviledges does i need to grant them to allo
> to see the results of this jobs?
>

Removing operators from System administrator Role

Hi List!
I have 2 users with SA admin rol granted on a SQL2000 SP3 Server. I want to
take them off, but the people said me that they need to monitor a lot of jobs
running on this server, so what priviledges does i need to grant them to allo
to see the results of this jobs?http://groups.google.de/group/microsoft.public.sqlserver.server/browse_frm/thread/795ec20703b7b535/919c4496371c0860?tvc=1&q=jobs+Jens+S%C3%BC%C3%9Fmeyer&hl=de#919c4496371c0860
http://groups.google.de/group/microsoft.public.sqlserver.security/browse_frm/thread/1d0ffa0f2b2fbdcd/0853a5d26aa1b4e1?lnk=st&q=jobs+Jens+S%C3%BC%C3%9Fmeyer&rnum=6&hl=de#0853a5d26aa1b4e1
HTH, Jens Suessmeyer.
"Tinchos" wrote:
> Hi List!
> I have 2 users with SA admin rol granted on a SQL2000 SP3 Server. I want to
> take them off, but the people said me that they need to monitor a lot of jobs
> running on this server, so what priviledges does i need to grant them to allo
> to see the results of this jobs?
>

Removing individual results from a paged set of results.

Hi,

I have a web form that lets users search for people in my database they wish to contact. The database returns a paged set of results using a CTE, Top X, and Row_number().

I would like to give my users to option of removing individual people from this list but cannot find a way to do this.

I have tried creating a session variable with a comma delimited list of ID's that I pass to my sproc and use in a NOT IN() statement. But I keep getting a "Input string was not in a correct format." Error Message.

Is there any way to do this? I am still new to stored procedures so any advice would be helpful.

Thanks

The only way I know to do it is to use dynamic sql.

|||

Can you post your SQL stored procedure? Its a lot easier to give suggestions if we can see what we're working with. There are several ways to do this, but I'd like to see what I'm working with before I make a suggestion.

Stu

|||

This is the SPROC I am currently working with without any code to remove individual rows.

CREATE PROCEDURE [dbo].[zk_update_request_england]
(
@.property_type tinyint,
@.market_status tinyint,
@.price int,
@.bedrooms tinyint,
@.search_location varchar(30),
@.PageSize int,
@.PageIndex int,
@.TRV int,
@.topx int,
@.TotalRequests int OUTPUT
)

AS

SET NOCOUNT ON

BEGIN

-- Set Paging limits
Declare @.firstRow Int;
Declare @.lastRow Int;
Set @.firstRow = (@.PageIndex * @.PageSize) + 1;
Set @.lastRow = @.firstRow + @.PageSize - 1;

-- Load count into @.TotalRequests
Set @.TotalRequests = (SELECT Count(*)
FROM [dbo].[zk_request_england]
WHERE property_type = @.property_type
AND market_status = @.market_status
AND bedrooms <= @.bedrooms
AND search_location = @.search_location
AND min_price <= @.price
AND max_price >= @.price)

-- If @.TotalRequests is less than @.topx change @.topx to @.TotalRequests
IF @.TotalRequests <= @.topx
SET @.topx = @.TotalRequests
ELSE
SET @.TotalRequests = @.topx
;

WITH SearchResults AS
(
SELECT TOP(@.topx) ROW_NUMBER() OVER (ORDER BY max_price DESC) AS RowNumber,
id,
user_name,
bedrooms,
min_price,
max_price,
property_description,
searched
FROM [dbo].[zk_request_england]
WHERE property_type = @.property_type
AND market_status = @.market_status
AND bedrooms <= @.bedrooms
AND search_location = @.search_location
AND min_price <= @.price
AND max_price >= @.price
)

SELECT id,
user_name,
bedrooms,
min_price,
max_price,
property_description
FROM SearchResults
WHERE RowNumber BETWEEN @.firstRow and @.lastRow

END

SET NOCOUNT OFF

removing all data from all users table

Hi All,
I have over 200 tables in a database and I like to know
if there is a Transact-SQL Reference that would remove
all rows form ALL tables in the database. I am not sure
if this is possible because some tables are referenced by
foreign key constraints.
TRUNCATE TABLE name only does one table at a time and
it's too time consuming to use it to remove all rows from
all tables.
Thank you,
MitraYou'll still have issues with foreign keys but you can do this:
EXEC sp_msForEachTable 'TRUNCATE TABLE ?'
Or you can generate a script this way:
SELECT 'TRUNCATE TABLE '+TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
When you run this, it will generate a series of statements in the lower
pane. You can copy this to the top pane (or new query window) and execute
it. You might consider adding a GO to the SELECT so that any errors on
individual truncate commands don't abort the whole batch; the list of errors
at the bottom will be your guide to tables you need to disable constraints
or otherwise handle individually.
You could also consider wiping out the database and re-creating it.
Hopefully you have scripts for all the tables/procedures etc. in SourceSafe,
and automating this should be trivial (especially if you're going to do this
periodically).
http://www.aspfaq.com/
(Reverse address to reply.)
"mitra fatholahi" <mitra928@.hotmail.com> wrote in message
news:17b6601c449e2$b99060e0$a601280a@.phx
.gbl...
> Hi All,
> I have over 200 tables in a database and I like to know
> if there is a Transact-SQL Reference that would remove
> all rows form ALL tables in the database. I am not sure
> if this is possible because some tables are referenced by
> foreign key constraints.
> TRUNCATE TABLE name only does one table at a time and
> it's too time consuming to use it to remove all rows from
> all tables.
> Thank you,
> Mitra|||Mitra,
If possible, cant you script out the entire database including the
indexes,triggers,relationships etc ,drop the database and then recreate the
database ?Since, as you mention the foreign key relationships exist, you
cannot do TRUNCATE TABLE unless you drop all those FKs and recreate them
later.Other option would be to prepare a DELETE FROM script on all tables,
making sure that the child tables appear first.I would adopt this only if I
cant drop the database.If you are going with DELETE, then you can do
something like:
SELECT 'DELETE FROM '+TABLE_SCHEMA+'.'+QUOTENAME(TABLE_NAME)+CHAR(13)+'GO'
FROM information_schema.tables
The above query would generate a script.Make necessary modifications and
execute the resultant script.If you are going with TRUNCATE you can adopt
the above script generation method.
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"mitra fatholahi" <mitra928@.hotmail.com> wrote in message
news:17b6601c449e2$b99060e0$a601280a@.phx
.gbl...
> Hi All,
> I have over 200 tables in a database and I like to know
> if there is a Transact-SQL Reference that would remove
> all rows form ALL tables in the database. I am not sure
> if this is possible because some tables are referenced by
> foreign key constraints.
> TRUNCATE TABLE name only does one table at a time and
> it's too time consuming to use it to remove all rows from
> all tables.
> Thank you,
> Mitra

removing all data from all users table

Hi All,
I have over 200 tables in a database and I like to know
if there is a Transact-SQL Reference that would remove
all rows form ALL tables in the database. I am not sure
if this is possible because some tables are referenced by
foreign key constraints.
TRUNCATE TABLE name only does one table at a time and
it's too time consuming to use it to remove all rows from
all tables.
Thank you,
MitraYou'll still have issues with foreign keys but you can do this:
EXEC sp_msForEachTable 'TRUNCATE TABLE ?'
Or you can generate a script this way:
SELECT 'TRUNCATE TABLE '+TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
When you run this, it will generate a series of statements in the lower
pane. You can copy this to the top pane (or new query window) and execute
it. You might consider adding a GO to the SELECT so that any errors on
individual truncate commands don't abort the whole batch; the list of errors
at the bottom will be your guide to tables you need to disable constraints
or otherwise handle individually.
You could also consider wiping out the database and re-creating it.
Hopefully you have scripts for all the tables/procedures etc. in SourceSafe,
and automating this should be trivial (especially if you're going to do this
periodically).
--
http://www.aspfaq.com/
(Reverse address to reply.)
"mitra fatholahi" <mitra928@.hotmail.com> wrote in message
news:17b6601c449e2$b99060e0$a601280a@.phx.gbl...
> Hi All,
> I have over 200 tables in a database and I like to know
> if there is a Transact-SQL Reference that would remove
> all rows form ALL tables in the database. I am not sure
> if this is possible because some tables are referenced by
> foreign key constraints.
> TRUNCATE TABLE name only does one table at a time and
> it's too time consuming to use it to remove all rows from
> all tables.
> Thank you,
> Mitra|||Mitra,
If possible, cant you script out the entire database including the
indexes,triggers,relationships etc ,drop the database and then recreate the
database ?Since, as you mention the foreign key relationships exist, you
cannot do TRUNCATE TABLE unless you drop all those FKs and recreate them
later.Other option would be to prepare a DELETE FROM script on all tables,
making sure that the child tables appear first.I would adopt this only if I
cant drop the database.If you are going with DELETE, then you can do
something like:
SELECT 'DELETE FROM '+TABLE_SCHEMA+'.'+QUOTENAME(TABLE_NAME)+CHAR(13)+'GO'
FROM information_schema.tables
The above query would generate a script.Make necessary modifications and
execute the resultant script.If you are going with TRUNCATE you can adopt
the above script generation method.
--
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"mitra fatholahi" <mitra928@.hotmail.com> wrote in message
news:17b6601c449e2$b99060e0$a601280a@.phx.gbl...
> Hi All,
> I have over 200 tables in a database and I like to know
> if there is a Transact-SQL Reference that would remove
> all rows form ALL tables in the database. I am not sure
> if this is possible because some tables are referenced by
> foreign key constraints.
> TRUNCATE TABLE name only does one table at a time and
> it's too time consuming to use it to remove all rows from
> all tables.
> Thank you,
> Mitra

removing all data from all users table

Hi All,
I have over 200 tables in a database and I like to know
if there is a Transact-SQL Reference that would remove
all rows form ALL tables in the database. I am not sure
if this is possible because some tables are referenced by
foreign key constraints.
TRUNCATE TABLE name only does one table at a time and
it's too time consuming to use it to remove all rows from
all tables.
Thank you,
Mitra
You'll still have issues with foreign keys but you can do this:
EXEC sp_msForEachTable 'TRUNCATE TABLE ?'
Or you can generate a script this way:
SELECT 'TRUNCATE TABLE '+TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
When you run this, it will generate a series of statements in the lower
pane. You can copy this to the top pane (or new query window) and execute
it. You might consider adding a GO to the SELECT so that any errors on
individual truncate commands don't abort the whole batch; the list of errors
at the bottom will be your guide to tables you need to disable constraints
or otherwise handle individually.
You could also consider wiping out the database and re-creating it.
Hopefully you have scripts for all the tables/procedures etc. in SourceSafe,
and automating this should be trivial (especially if you're going to do this
periodically).
http://www.aspfaq.com/
(Reverse address to reply.)
"mitra fatholahi" <mitra928@.hotmail.com> wrote in message
news:17b6601c449e2$b99060e0$a601280a@.phx.gbl...
> Hi All,
> I have over 200 tables in a database and I like to know
> if there is a Transact-SQL Reference that would remove
> all rows form ALL tables in the database. I am not sure
> if this is possible because some tables are referenced by
> foreign key constraints.
> TRUNCATE TABLE name only does one table at a time and
> it's too time consuming to use it to remove all rows from
> all tables.
> Thank you,
> Mitra
|||Mitra,
If possible, cant you script out the entire database including the
indexes,triggers,relationships etc ,drop the database and then recreate the
database ?Since, as you mention the foreign key relationships exist, you
cannot do TRUNCATE TABLE unless you drop all those FKs and recreate them
later.Other option would be to prepare a DELETE FROM script on all tables,
making sure that the child tables appear first.I would adopt this only if I
cant drop the database.If you are going with DELETE, then you can do
something like:
SELECT 'DELETE FROM '+TABLE_SCHEMA+'.'+QUOTENAME(TABLE_NAME)+CHAR(13)+ 'GO'
FROM information_schema.tables
The above query would generate a script.Make necessary modifications and
execute the resultant script.If you are going with TRUNCATE you can adopt
the above script generation method.
Dinesh
SQL Server MVP
--
SQL Server FAQ at
http://www.tkdinesh.com
"mitra fatholahi" <mitra928@.hotmail.com> wrote in message
news:17b6601c449e2$b99060e0$a601280a@.phx.gbl...
> Hi All,
> I have over 200 tables in a database and I like to know
> if there is a Transact-SQL Reference that would remove
> all rows form ALL tables in the database. I am not sure
> if this is possible because some tables are referenced by
> foreign key constraints.
> TRUNCATE TABLE name only does one table at a time and
> it's too time consuming to use it to remove all rows from
> all tables.
> Thank you,
> Mitra