Restrict Warning Message

I used a temporary table in a stored procedure.Whenever this stored procedure is executed with the desired output it is giving the warning message-
Warning: The table '#TEMPTBLPERSON' has been created but its maximum row size (10930) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.

Now my question is how can I avoid of displaying this warning message
in the stored procedure without altering the temporary table ?Originally posted by subhasishray
I used a temporary table in a stored procedure.Whenever this stored procedure is executed with the desired output it is giving the warning message-
Warning: The table '#TEMPTBLPERSON' has been created but its maximum row size (10930) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.

Now my question is how can I avoid of displaying this warning message
in the stored procedure without altering the temporary table ?

The best way is to reduce the data size of the table to less than 8060 bytes.

other way .. i believe might be to set the option

set ansi_warnings off|||I wrote clearly I don't want to reduce the length of the table.
set ansi_warnings_off|on is not giving desired output.
Subhasish|||Originally posted by subhasishray
I wrote clearly I don't want to reduce the length of the table.
set ansi_warnings_off|on is not giving desired output.
Subhasish

Clearly ... you are not paying heed to the warning message .. An insert into your table will fail in case the data is more than 8060 bytes . You should take that case into consideration.

Am searching for the other answer .. will get back as soon as i find it|||There is no "other" answer, you've said it all. the only other thing to do would be to split the table into 2 with 1-to-1 relationship.|||I was searching for a server property where i could control the severity level of warnings and errors as is availible in osql with the -m flag.|||Originally posted by subhasishray
I wrote clearly I don't want to reduce the length of the table.
set ansi_warnings_off|on is not giving desired output.
Subhasish

Whether you know it or not...that comes across with some attitude..

If you can't be flexible...then I don't know who will...

good luck...

Restrict users to modify tabble structure

Is it possible to restrict users to modify the table structure. Most of our
users are using "Windows authentication"
Thanks for any help.
J Justin
You need to limit what permissions each user has. Modify the security =
on each group or individual so that they have only the rights that they =
need. If you want to limit what they can do you will want to clear all =
checkboxes on the "server roles" tab and you will want to make sure that =
they have the appropriate database roles assigned. You access the forms =
that I am talking about via Enterprise Manager. Drill into the security =
folder, Logins node. double-click on a specific login to view its =
properties.
--=20
Keith
"Justin" <justinkumar@.hotmail.com> wrote in message =
news:%23nTbnRiTEHA.2324@.TK2MSFTNGP10.phx.gbl...
> Is it possible to restrict users to modify the table structure. Most =
of our
> users are using "Windows authentication"
> Thanks for any help.
>=20
> J Justin
>=20
>
|||Just make sure that they don't have such permissions/privileges in your database. I.e., should not have the
sysadmin server role. Should not be dbo in the database. Should not have db_ddladmin or db_owner database
role.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Justin" <justinkumar@.hotmail.com> wrote in message news:%23nTbnRiTEHA.2324@.TK2MSFTNGP10.phx.gbl...
> Is it possible to restrict users to modify the table structure. Most of our
> users are using "Windows authentication"
> Thanks for any help.
> J Justin
>

Restrict users to modify tabble structure

Is it possible to restrict users to modify the table structure. Most of our
users are using "Windows authentication"
Thanks for any help.
J JustinYou need to limit what permissions each user has. Modify the security =
on each group or individual so that they have only the rights that they =
need. If you want to limit what they can do you will want to clear all =
checkboxes on the "server roles" tab and you will want to make sure that =
they have the appropriate database roles assigned. You access the forms =
that I am talking about via Enterprise Manager. Drill into the security =
folder, Logins node. double-click on a specific login to view its =
properties.
--=20
Keith
"Justin" <justinkumar@.hotmail.com> wrote in message =
news:%23nTbnRiTEHA.2324@.TK2MSFTNGP10.phx.gbl...
> Is it possible to restrict users to modify the table structure. Most =
of our
> users are using "Windows authentication"
> Thanks for any help.
>=20
> J Justin
>=20
>|||Just make sure that they don't have such permissions/privileges in your data
base. I.e., should not have the
sysadmin server role. Should not be dbo in the database. Should not have db_
ddladmin or db_owner database
role.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Justin" <justinkumar@.hotmail.com> wrote in message news:%23nTbnRiTEHA.2324@.TK2MSFTNGP10.phx
.gbl...
> Is it possible to restrict users to modify the table structure. Most of ou
r
> users are using "Windows authentication"
> Thanks for any help.
> J Justin
>

Restrict users to modify tabble structure

Is it possible to restrict users to modify the table structure. Most of our
users are using "Windows authentication"
Thanks for any help.
J JustinYou need to limit what permissions each user has. Modify the security =on each group or individual so that they have only the rights that they =need. If you want to limit what they can do you will want to clear all =checkboxes on the "server roles" tab and you will want to make sure that =they have the appropriate database roles assigned. You access the forms =that I am talking about via Enterprise Manager. Drill into the security =folder, Logins node. double-click on a specific login to view its =properties.
-- Keith
"Justin" <justinkumar@.hotmail.com> wrote in message =news:%23nTbnRiTEHA.2324@.TK2MSFTNGP10.phx.gbl...
> Is it possible to restrict users to modify the table structure. Most =of our
> users are using "Windows authentication"
> Thanks for any help.
> > J Justin
> >|||Just make sure that they don't have such permissions/privileges in your database. I.e., should not have the
sysadmin server role. Should not be dbo in the database. Should not have db_ddladmin or db_owner database
role.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Justin" <justinkumar@.hotmail.com> wrote in message news:%23nTbnRiTEHA.2324@.TK2MSFTNGP10.phx.gbl...
> Is it possible to restrict users to modify the table structure. Most of our
> users are using "Windows authentication"
> Thanks for any help.
> J Justin
>sql

restrict user access

how can i set the user access level for sql 2000 server?
i want to restrict the user only can query/add table/ drop table/ modify
data/import & export data
but don't allow them to view other services such as "Management,
Replication, Security...etc"
which standard role i can choose or need to create a custom role for this?
btw, Although user can view the content of other user, but they still can
see the name of DB? how can avoid this?
Thanks in advanced.
Hi
> i want to restrict the user only can query/add table/ drop table/ modify
> data/import & export data
Thats all what you want ? :-))))
Well first of all you need to create a login to SQL Server for this person.
Then grant an access to the database
As I see your requirements you are going to put them into db_owner database
role.
For more details please refer to the BOL.
"beachboy" <stanley@.javacatz.com> wrote in message
news:%23SgC77JIGHA.740@.TK2MSFTNGP12.phx.gbl...
> how can i set the user access level for sql 2000 server?
> i want to restrict the user only can query/add table/ drop table/ modify
> data/import & export data
> but don't allow them to view other services such as "Management,
> Replication, Security...etc"
> which standard role i can choose or need to create a custom role for this?
> btw, Although user can view the content of other user, but they still can
> see the name of DB? how can avoid this?
> Thanks in advanced.
>

restrict user access

how can i set the user access level for sql 2000 server?
i want to restrict the user only can query/add table/ drop table/ modify
data/import & export data
but don't allow them to view other services such as "Management,
Replication, Security...etc"
which standard role i can choose or need to create a custom role for this?
btw, Although user can view the content of other user, but they still can
see the name of DB? how can avoid this?
Thanks in advanced.Hi
> i want to restrict the user only can query/add table/ drop table/ modify
> data/import & export data
Thats all what you want ? :-))))
Well first of all you need to create a login to SQL Server for this person.
Then grant an access to the database
As I see your requirements you are going to put them into db_owner database
role.
For more details please refer to the BOL.
"beachboy" <stanley@.javacatz.com> wrote in message
news:%23SgC77JIGHA.740@.TK2MSFTNGP12.phx.gbl...
> how can i set the user access level for sql 2000 server?
> i want to restrict the user only can query/add table/ drop table/ modify
> data/import & export data
> but don't allow them to view other services such as "Management,
> Replication, Security...etc"
> which standard role i can choose or need to create a custom role for this?
> btw, Although user can view the content of other user, but they still can
> see the name of DB? how can avoid this?
> Thanks in advanced.
>

restrict user access

how can i set the user access level for sql 2000 server?
i want to restrict the user only can query/add table/ drop table/ modify
data/import & export data
but don't allow them to view other services such as "Management,
Replication, Security...etc"
which standard role i can choose or need to create a custom role for this?
btw, Although user can view the content of other user, but they still can
see the name of DB? how can avoid this?
Thanks in advanced.Hi
> i want to restrict the user only can query/add table/ drop table/ modify
> data/import & export data
Thats all what you want ? :-))))
Well first of all you need to create a login to SQL Server for this person.
Then grant an access to the database
As I see your requirements you are going to put them into db_owner database
role.
For more details please refer to the BOL.
"beachboy" <stanley@.javacatz.com> wrote in message
news:%23SgC77JIGHA.740@.TK2MSFTNGP12.phx.gbl...
> how can i set the user access level for sql 2000 server?
> i want to restrict the user only can query/add table/ drop table/ modify
> data/import & export data
> but don't allow them to view other services such as "Management,
> Replication, Security...etc"
> which standard role i can choose or need to create a custom role for this?
> btw, Although user can view the content of other user, but they still can
> see the name of DB? how can avoid this?
> Thanks in advanced.
>sql

Restrict The Number Of Records From Query

Hi,
ive a table of over 90,000 records . is ther any method for restricting the number of records returned by a SELECT query
i want to implement a query system which will give me the first 50 records, next 50 ,... and so on
can ne body help ?Originally posted by baburajv
Hi,

ive a table of over 90,000 records . is ther any method for restricting the number of records returned by a SELECT query

i want to implement a query system which will give me the first 50 records, next 50 ,... and so on

can ne body help ?

select top 50 from table_name|||SELECT TOP 50 FROM TABLE_NAME

WILL RETURN THE TOP 50 ROWS FROM THE TABLE

HOW CAN I RETRIEVE THE NEXT 50 ROWS|||Originally posted by baburajv
SELECT TOP 50 FROM TABLE_NAME

WILL RETURN THE TOP 50 ROWS FROM THE TABLE

HOW CAN I RETRIEVE THE NEXT 50 ROWS

I think then u need to use temperory tables for it.|||select top NUMBER_OF_ROWS *
from table
where column NOT IN
(select top BEGIN_AT column
from table)

Restrict ing Sum(...) in Report Table Footer?

Hello,
My Report Table contains a Date Field and a numeric Value field. In the
Footer row of the table I use the Sum Aggregate function to sum up all the
values for all the rows that get displayed
=sum(Fields!CountFld.Value)
I also have an conditional Expression for the background color of each cell
in the detail row
=iif(Parameters!EndDate.Value > Fields!
CurExpireDate.Value, "Gainsboro", "White")
which changes the background color of each cell in the row (I add this IIF
expression to each field in the row).
So, if a value is in a row that is the gainsboro color, I do not want to sum
that value. Is there a way to restrict sum(Fields!CountFld.Value) ?
PseudoCode
=Sum(iif(Parameters!EndDate.Value > Fields!
CurExpireDate.Value, Fields!CountFld.Value, 0))
something like this?
Thanks,
RichWell, I tried my own pseudocode and it worked!
=Sum(iif(Parameters!EndDate.Value > Fields!
> CurExpireDate.Value, 0, Fields!CountFld.Value))
I just had to change the values around
"Rich" wrote:
> Hello,
> My Report Table contains a Date Field and a numeric Value field. In the
> Footer row of the table I use the Sum Aggregate function to sum up all the
> values for all the rows that get displayed
> =sum(Fields!CountFld.Value)
> I also have an conditional Expression for the background color of each cell
> in the detail row
> =iif(Parameters!EndDate.Value > Fields!
> CurExpireDate.Value, "Gainsboro", "White")
> which changes the background color of each cell in the row (I add this IIF
> expression to each field in the row).
> So, if a value is in a row that is the gainsboro color, I do not want to sum
> that value. Is there a way to restrict sum(Fields!CountFld.Value) ?
> PseudoCode
> =Sum(iif(Parameters!EndDate.Value > Fields!
> CurExpireDate.Value, Fields!CountFld.Value, 0))
> something like this?
> Thanks,
> Rich
>|||I used the same method, but have run into 1 problem. If the table or group
spans multiple pages, the sum fails (I get #Error). Not sure if you'll
encounter this, but I thought I would make you aware of my issue using that
method.
Michael
"Rich" wrote:
> Well, I tried my own pseudocode and it worked!
> =Sum(iif(Parameters!EndDate.Value > Fields!
> > CurExpireDate.Value, 0, Fields!CountFld.Value))
> I just had to change the values around
>
> "Rich" wrote:
> > Hello,
> >
> > My Report Table contains a Date Field and a numeric Value field. In the
> > Footer row of the table I use the Sum Aggregate function to sum up all the
> > values for all the rows that get displayed
> >
> > =sum(Fields!CountFld.Value)
> >
> > I also have an conditional Expression for the background color of each cell
> > in the detail row
> >
> > =iif(Parameters!EndDate.Value > Fields!
> > CurExpireDate.Value, "Gainsboro", "White")
> >
> > which changes the background color of each cell in the row (I add this IIF
> > expression to each field in the row).
> >
> > So, if a value is in a row that is the gainsboro color, I do not want to sum
> > that value. Is there a way to restrict sum(Fields!CountFld.Value) ?
> >
> > PseudoCode
> > =Sum(iif(Parameters!EndDate.Value > Fields!
> > CurExpireDate.Value, Fields!CountFld.Value, 0))
> >
> > something like this?
> >
> > Thanks,
> > Rich
> >|||Thanks. Not the kind of surprises I am looking forward to. Hopefully, I
won't run into that problem, but if I do, I will now know that I am not
alone. This is actually quite encouraging because I have had some seroius
heartaches with RS (2000).
"Michael C" wrote:
> I used the same method, but have run into 1 problem. If the table or group
> spans multiple pages, the sum fails (I get #Error). Not sure if you'll
> encounter this, but I thought I would make you aware of my issue using that
> method.
> Michael
> "Rich" wrote:
> > Well, I tried my own pseudocode and it worked!
> >
> > =Sum(iif(Parameters!EndDate.Value > Fields!
> > > CurExpireDate.Value, 0, Fields!CountFld.Value))
> >
> > I just had to change the values around
> >
> >
> >
> > "Rich" wrote:
> >
> > > Hello,
> > >
> > > My Report Table contains a Date Field and a numeric Value field. In the
> > > Footer row of the table I use the Sum Aggregate function to sum up all the
> > > values for all the rows that get displayed
> > >
> > > =sum(Fields!CountFld.Value)
> > >
> > > I also have an conditional Expression for the background color of each cell
> > > in the detail row
> > >
> > > =iif(Parameters!EndDate.Value > Fields!
> > > CurExpireDate.Value, "Gainsboro", "White")
> > >
> > > which changes the background color of each cell in the row (I add this IIF
> > > expression to each field in the row).
> > >
> > > So, if a value is in a row that is the gainsboro color, I do not want to sum
> > > that value. Is there a way to restrict sum(Fields!CountFld.Value) ?
> > >
> > > PseudoCode
> > > =Sum(iif(Parameters!EndDate.Value > Fields!
> > > CurExpireDate.Value, Fields!CountFld.Value, 0))
> > >
> > > something like this?
> > >
> > > Thanks,
> > > Rich
> > >sql

Restrict delete from a table

Hi

I need to restrict delete from one table by any user of SQL. How can we do this? This is our master table and we dont want any one to delete data from this table.

Thanks
BalaTry Cascade ON DELETE NO ACTION, you can use either code or set it on the table properties. Run a search for Cascade Delete in the BOL(books online) for more examples. NO ACTION is an ANSI SQL DRI(Declarative Referential Integrity) rule that prevents Deletes from tables. Hope this helps

CREATE TABLE order_part
(order_nmbr int,
part_nmbr int
FOREIGN KEY REFERENCES part_sample(part_nmbr)
ON DELETE NO ACTION,
qty_ordered int)
GO

Kind regards,
Gift Peddie

restoring with StopBeforeMark

hi
i did :
create table pp ( id int )
backup full
insert into pp values ( 1 )
backup diff
insert into pp values ( 2 )
backup log 1
insert into pp values ( 3 )
backup log 2
insert into pp values ( 4 )
backup log 3 -- tail
i want to restore
the rows 1, 2, and 3
the way i'm trying is this :
restore database [xx] from TheBackUp with
NOrecovery,
StopBeforeMark= 'LSN of backup log 4'
but,
i get a table with no rows
what is wrong ?
TIA
atte,
Hernn
atte,
Hernn
ohhhh...
i understand now...
i have to restore
the whole the backup chain
from the first to the last
but using the StopBeforeClause...
is there a way to restore
the whole backup chain in one line ?
should i to write a "loop"
for restore each row
that RESTORE HEADERONLY bring me ?
atte,
Hernn
"bajopalabra" <bajopalabra@.hotmail.com> escribi en el mensaje
news:%23kiu9On3GHA.696@.TK2MSFTNGP03.phx.gbl...
| hi
| i did :
|
| create table pp ( id int )
| backup full
| insert into pp values ( 1 )
| backup diff
| insert into pp values ( 2 )
| backup log 1
| insert into pp values ( 3 )
| backup log 2
| insert into pp values ( 4 )
| backup log 3 -- tail
|
| i want to restore
| the rows 1, 2, and 3
|
| the way i'm trying is this :
|
| restore database [xx] from TheBackUp with
| NOrecovery,
| StopBeforeMark= 'LSN of backup log 4'
|
| but,
| i get a table with no rows
|
| what is wrong ?
|
| TIA
|
| --
| atte,
| Hernn
|
| --
| atte,
| Hernn
|
|
|||Already replied in .programming. Please don't multi-post.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"bajopalabra" <bajopalabra@.hotmail.com> wrote in message
news:uVMa5gn3GHA.4976@.TK2MSFTNGP02.phx.gbl...
> ohhhh...
> i understand now...
> i have to restore
> the whole the backup chain
> from the first to the last
> but using the StopBeforeClause...
> is there a way to restore
> the whole backup chain in one line ?
> should i to write a "loop"
> for restore each row
> that RESTORE HEADERONLY bring me ?
> --
> atte,
> Hernn
> "bajopalabra" <bajopalabra@.hotmail.com> escribi en el mensaje
> news:%23kiu9On3GHA.696@.TK2MSFTNGP03.phx.gbl...
> | hi
> | i did :
> |
> | create table pp ( id int )
> | backup full
> | insert into pp values ( 1 )
> | backup diff
> | insert into pp values ( 2 )
> | backup log 1
> | insert into pp values ( 3 )
> | backup log 2
> | insert into pp values ( 4 )
> | backup log 3 -- tail
> |
> | i want to restore
> | the rows 1, 2, and 3
> |
> | the way i'm trying is this :
> |
> | restore database [xx] from TheBackUp with
> | NOrecovery,
> | StopBeforeMark= 'LSN of backup log 4'
> |
> | but,
> | i get a table with no rows
> |
> | what is wrong ?
> |
> | TIA
> |
> | --
> | atte,
> | Hernn
> |
> | --
> | atte,
> | Hernn
> |
> |
>

restoring with StopBeforeMark

hi
i did :
create table pp ( id int )
backup full
insert into pp values ( 1 )
backup diff
insert into pp values ( 2 )
backup log 1
insert into pp values ( 3 )
backup log 2
insert into pp values ( 4 )
backup log 3 -- tail
i want to restore
the rows 1, 2, and 3
the way i'm trying is this :
restore database [xx] from TheBackUp with
NOrecovery,
StopBeforeMark= 'LSN of backup log 4'
but,
i get a table with no rows
what is wrong '
TIA
atte,
Hernn
atte,
Hernnohhhh...
i understand now...
i have to restore
the whole the backup chain
from the first to the last
but using the StopBeforeClause...
is there a way to restore
the whole backup chain in one line ?
should i to write a "loop"
for restore each row
that RESTORE HEADERONLY bring me '
atte,
Hernn
"bajopalabra" <bajopalabra@.hotmail.com> escribi en el mensaje
news:%23kiu9On3GHA.696@.TK2MSFTNGP03.phx.gbl...
| hi
| i did :
|
| create table pp ( id int )
| backup full
| insert into pp values ( 1 )
| backup diff
| insert into pp values ( 2 )
| backup log 1
| insert into pp values ( 3 )
| backup log 2
| insert into pp values ( 4 )
| backup log 3 -- tail
|
| i want to restore
| the rows 1, 2, and 3
|
| the way i'm trying is this :
|
| restore database [xx] from TheBackUp with
| NOrecovery,
| StopBeforeMark= 'LSN of backup log 4'
|
| but,
| i get a table with no rows
|
| what is wrong '
|
| TIA
|
| --
| atte,
| Hernn
|
| --
| atte,
| Hernn
|
||||Already replied in .programming. Please don't multi-post.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"bajopalabra" <bajopalabra@.hotmail.com> wrote in message
news:uVMa5gn3GHA.4976@.TK2MSFTNGP02.phx.gbl...
> ohhhh...
> i understand now...
> i have to restore
> the whole the backup chain
> from the first to the last
> but using the StopBeforeClause...
> is there a way to restore
> the whole backup chain in one line ?
> should i to write a "loop"
> for restore each row
> that RESTORE HEADERONLY bring me '
> --
> atte,
> Hernn
> "bajopalabra" <bajopalabra@.hotmail.com> escribi en el mensaje
> news:%23kiu9On3GHA.696@.TK2MSFTNGP03.phx.gbl...
> | hi
> | i did :
> |
> | create table pp ( id int )
> | backup full
> | insert into pp values ( 1 )
> | backup diff
> | insert into pp values ( 2 )
> | backup log 1
> | insert into pp values ( 3 )
> | backup log 2
> | insert into pp values ( 4 )
> | backup log 3 -- tail
> |
> | i want to restore
> | the rows 1, 2, and 3
> |
> | the way i'm trying is this :
> |
> | restore database [xx] from TheBackUp with
> | NOrecovery,
> | StopBeforeMark= 'LSN of backup log 4'
> |
> | but,
> | i get a table with no rows
> |
> | what is wrong '
> |
> | TIA
> |
> | --
> | atte,
> | Hernn
> |
> | --
> | atte,
> | Hernn
> |
> |
>

restoring with StopBeforeMark

hi
i did :
create table pp ( id int )
backup full
insert into pp values ( 1 )
backup diff
insert into pp values ( 2 )
backup log 1
insert into pp values ( 3 )
backup log 2
insert into pp values ( 4 )
backup log 3 -- tail
i want to restore
the rows 1, 2, and 3
the way i'm trying is this :
restore database [xx] from TheBackUp with
NOrecovery,
StopBeforeMark= 'LSN of backup log 4'
but,
i get a table with no rows
what is wrong '
TIA
--
atte,
Hernán
--
atte,
Hernánohhhh...
i understand now...
i have to restore
the whole the backup chain
from the first to the last
but using the StopBeforeClause...
is there a way to restore
the whole backup chain in one line ?
should i to write a "loop"
for restore each row
that RESTORE HEADERONLY bring me '
--
atte,
Hernán
"bajopalabra" <bajopalabra@.hotmail.com> escribió en el mensaje
news:%23kiu9On3GHA.696@.TK2MSFTNGP03.phx.gbl...
| hi
| i did :
|
| create table pp ( id int )
| backup full
| insert into pp values ( 1 )
| backup diff
| insert into pp values ( 2 )
| backup log 1
| insert into pp values ( 3 )
| backup log 2
| insert into pp values ( 4 )
| backup log 3 -- tail
|
| i want to restore
| the rows 1, 2, and 3
|
| the way i'm trying is this :
|
| restore database [xx] from TheBackUp with
| NOrecovery,
| StopBeforeMark= 'LSN of backup log 4'
|
| but,
| i get a table with no rows
|
| what is wrong '
|
| TIA
|
| --
| atte,
| Hernán
|
| --
| atte,
| Hernán
|
||||Already replied in .programming. Please don't multi-post.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"bajopalabra" <bajopalabra@.hotmail.com> wrote in message
news:uVMa5gn3GHA.4976@.TK2MSFTNGP02.phx.gbl...
> ohhhh...
> i understand now...
> i have to restore
> the whole the backup chain
> from the first to the last
> but using the StopBeforeClause...
> is there a way to restore
> the whole backup chain in one line ?
> should i to write a "loop"
> for restore each row
> that RESTORE HEADERONLY bring me '
> --
> atte,
> Hernán
> "bajopalabra" <bajopalabra@.hotmail.com> escribió en el mensaje
> news:%23kiu9On3GHA.696@.TK2MSFTNGP03.phx.gbl...
> | hi
> | i did :
> |
> | create table pp ( id int )
> | backup full
> | insert into pp values ( 1 )
> | backup diff
> | insert into pp values ( 2 )
> | backup log 1
> | insert into pp values ( 3 )
> | backup log 2
> | insert into pp values ( 4 )
> | backup log 3 -- tail
> |
> | i want to restore
> | the rows 1, 2, and 3
> |
> | the way i'm trying is this :
> |
> | restore database [xx] from TheBackUp with
> | NOrecovery,
> | StopBeforeMark= 'LSN of backup log 4'
> |
> | but,
> | i get a table with no rows
> |
> | what is wrong '
> |
> | TIA
> |
> | --
> | atte,
> | Hernán
> |
> | --
> | atte,
> | Hernán
> |
> |
>

Restoring to get a single table

A user has managed to ruin a table on our production system, and I'd like to
offer them an older copy online so they can compare them. I think the best
way would be to restore the last backup (only a day old, phew) as a new
database, and then copy the data over.
Can anyone guide me on how to do the restore to a NEW database, leaving the
original intact and online?
MaurySee the Restore Database command, and simply use MyDatabase_New instead of
MyDatabase. Same thing in Enterprise Manager if you prefer to do it
there...just give it a new name
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:27178864-F2FE-44B9-8D10-7B3CB35B1303@.microsoft.com...
>A user has managed to ruin a table on our production system, and I'd like
>to
> offer them an older copy online so they can compare them. I think the best
> way would be to restore the last backup (only a day old, phew) as a new
> database, and then copy the data over.
> Can anyone guide me on how to do the restore to a NEW database, leaving
> the
> original intact and online?
> Maury|||"Kevin3NF" wrote:
> See the Restore Database command, and simply use MyDatabase_New instead of
> MyDatabase. Same thing in Enterprise Manager if you prefer to do it
> there...just give it a new name
Ok, thanks. Here goes nothing...
Maury|||www.red-gate.com makes a data Compare utility that may be useful in helping
you identify the ruined data, as well as writing scripts to resolve it.
"Maury Markowitz" wrote:
> "Kevin3NF" wrote:
> > See the Restore Database command, and simply use MyDatabase_New instead of
> > MyDatabase. Same thing in Enterprise Manager if you prefer to do it
> > there...just give it a new name
> Ok, thanks. Here goes nothing...
> Maurysql

Restoring to get a single table

A user has managed to ruin a table on our production system, and I'd like to
offer them an older copy online so they can compare them. I think the best
way would be to restore the last backup (only a day old, phew) as a new
database, and then copy the data over.
Can anyone guide me on how to do the restore to a NEW database, leaving the
original intact and online?
Maury
See the Restore Database command, and simply use MyDatabase_New instead of
MyDatabase. Same thing in Enterprise Manager if you prefer to do it
there...just give it a new name
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:27178864-F2FE-44B9-8D10-7B3CB35B1303@.microsoft.com...
>A user has managed to ruin a table on our production system, and I'd like
>to
> offer them an older copy online so they can compare them. I think the best
> way would be to restore the last backup (only a day old, phew) as a new
> database, and then copy the data over.
> Can anyone guide me on how to do the restore to a NEW database, leaving
> the
> original intact and online?
> Maury
|||"Kevin3NF" wrote:

> See the Restore Database command, and simply use MyDatabase_New instead of
> MyDatabase. Same thing in Enterprise Manager if you prefer to do it
> there...just give it a new name
Ok, thanks. Here goes nothing...
Maury
|||www.red-gate.com makes a data Compare utility that may be useful in helping
you identify the ruined data, as well as writing scripts to resolve it.
"Maury Markowitz" wrote:

> "Kevin3NF" wrote:
>
> Ok, thanks. Here goes nothing...
> Maury

Restoring to get a single table

A user has managed to ruin a table on our production system, and I'd like to
offer them an older copy online so they can compare them. I think the best
way would be to restore the last backup (only a day old, phew) as a new
database, and then copy the data over.
Can anyone guide me on how to do the restore to a NEW database, leaving the
original intact and online?
MaurySee the Restore Database command, and simply use MyDatabase_New instead of
MyDatabase. Same thing in Enterprise Manager if you prefer to do it
there...just give it a new name
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:27178864-F2FE-44B9-8D10-7B3CB35B1303@.microsoft.com...
>A user has managed to ruin a table on our production system, and I'd like
>to
> offer them an older copy online so they can compare them. I think the best
> way would be to restore the last backup (only a day old, phew) as a new
> database, and then copy the data over.
> Can anyone guide me on how to do the restore to a NEW database, leaving
> the
> original intact and online?
> Maury|||"Kevin3NF" wrote:

> See the Restore Database command, and simply use MyDatabase_New instead of
> MyDatabase. Same thing in Enterprise Manager if you prefer to do it
> there...just give it a new name
Ok, thanks. Here goes nothing...
Maury|||www.red-gate.com makes a data Compare utility that may be useful in helping
you identify the ruined data, as well as writing scripts to resolve it.
"Maury Markowitz" wrote:

> "Kevin3NF" wrote:
>
> Ok, thanks. Here goes nothing...
> Maury

restoring table or view or sp

Actually I have very big size of databse. Also its back up is obvious that also biger size.

In case of only data loss of one table or view or sp or change then restore whole database then get back previous backup database & restore table i.e. it consuming too much time.

I want to restore only one table or view in faster way. how i can do it. plz give any suggestions

If you database was made of seperate files you could restore individual files.

To restore only a table you need a third party product such as litespeed.

I am not sure if any product can restore only schema components i.e views and sps. Standard practice is to keep the SQL scripts for Views and SPs. Do you not have these?

|||

what is litespeed & how i can get it. give the idea plz

|||

in sql server 2005 you can use Database snapshot as

a part of your backup and restore strategy.

A database snapshot is a readonly replica of your database

so in case a table is accidentally deleted, you can copy the deleted

data from the snapshot.

other use of the snapshot is for reporting purposes so this startegy will also

boost the database performance as well

restoring single table

Hi All,
I am taking transaction log backup every six hours. Now i want to restore just one particular table before 2 hrs of now.
How can i go about pls help me as its very urgent.
TIA
RegardsRESTORE

bcp out|||hi thanks for the reply

Can u be more specific as i am very new to this database

TIA
Regards|||You can restore the database to a second instance of the database and then DTS or use BCP to send the table over to your live database. If you are new I would reccomend using the DTS import/export wizard (remember to delete destination rows).

The other way is you could buy a third party tool called lumigent log explorer that would allow you to do this without restoring.

HTH

Restoring records in a particular table

Hi,
I have unknowingly deleted 100 records instead of 1 record.
I haven't set autocommit off also...
Could you please let me know if there is any way to get back those records...
Thanks in advance...
Did you take a backup prior to doing the delete?
Last night or something?
You could restore it with a different DB name and import the records...
thanks,
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/
"Srik" <Srik@.discussions.microsoft.com> wrote in message
news:A500CA6C-3B51-4929-BBCA-BBA62368148E@.microsoft.com...
> Hi,
> I have unknowingly deleted 100 records instead of 1 record.
> I haven't set autocommit off also...
> Could you please let me know if there is any way to get back those
> records...
> Thanks in advance...
|||If you are using the full recovery model and have been doing database and
transaction log dumps you should be able to restore the database to a point
in time before you deleted these rows.
You can also look at tools like Log Explorer which may help.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Srik" <Srik@.discussions.microsoft.com> wrote in message
news:A500CA6C-3B51-4929-BBCA-BBA62368148E@.microsoft.com...
> Hi,
> I have unknowingly deleted 100 records instead of 1 record.
> I haven't set autocommit off also...
> Could you please let me know if there is any way to get back those
> records...
> Thanks in advance...
|||There is back up, but i dont know the duration of the backup they take
by 10:30 i have deleted the records from particular table.
See the query
delete tp_no='3' from tablename ,without adding the condition so that
multiple records are deleted with the tp_no='3'
"Warren Brunk" wrote:

> Did you take a backup prior to doing the delete?
> Last night or something?
> You could restore it with a different DB name and import the records...
> thanks,
> --
> /*
> Warren Brunk - MCITP - SQL 2005, MCDBA
> www.techintsolutions.com
> */
>
> "Srik" <Srik@.discussions.microsoft.com> wrote in message
> news:A500CA6C-3B51-4929-BBCA-BBA62368148E@.microsoft.com...
>
>
|||Back up your transaction log, and do a point in time recovery.
Or you can take your latest full back up and restore it to a new DB and copy
the data in.
thanks,
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/
"Srik" <Srik@.discussions.microsoft.com> wrote in message
news:0E12A098-1D1B-4E6C-9BA1-853FC2F7ACA2@.microsoft.com...[vbcol=seagreen]
> There is back up, but i dont know the duration of the backup they take
> by 10:30 i have deleted the records from particular table.
> See the query
> delete tp_no='3' from tablename ,without adding the condition so that
> multiple records are deleted with the tp_no='3'
> "Warren Brunk" wrote:
|||If you dont have backup or database snapshot where you can read the data you
deleted, perhaps you can use some third party tools for transaction log
managment. I believe red gate has one tool as well as lumigent (log
explorer).
There are probably others, if you search on the net you should find a
couple.
MC
"Srik" <Srik@.discussions.microsoft.com> wrote in message
news:A500CA6C-3B51-4929-BBCA-BBA62368148E@.microsoft.com...
> Hi,
> I have unknowingly deleted 100 records instead of 1 record.
> I haven't set autocommit off also...
> Could you please let me know if there is any way to get back those
records...
> Thanks in advance...

Restoring records in a particular table

Hi,
I have unknowingly deleted 100 records instead of 1 record.
I haven't set autocommit off also...
Could you please let me know if there is any way to get back those records...
Thanks in advance...Did you take a backup prior to doing the delete?
Last night or something?
You could restore it with a different DB name and import the records...
thanks,
--
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/
"Srik" <Srik@.discussions.microsoft.com> wrote in message
news:A500CA6C-3B51-4929-BBCA-BBA62368148E@.microsoft.com...
> Hi,
> I have unknowingly deleted 100 records instead of 1 record.
> I haven't set autocommit off also...
> Could you please let me know if there is any way to get back those
> records...
> Thanks in advance...|||If you are using the full recovery model and have been doing database and
transaction log dumps you should be able to restore the database to a point
in time before you deleted these rows.
You can also look at tools like Log Explorer which may help.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Srik" <Srik@.discussions.microsoft.com> wrote in message
news:A500CA6C-3B51-4929-BBCA-BBA62368148E@.microsoft.com...
> Hi,
> I have unknowingly deleted 100 records instead of 1 record.
> I haven't set autocommit off also...
> Could you please let me know if there is any way to get back those
> records...
> Thanks in advance...|||There is back up, but i dont know the duration of the backup they take
by 10:30 i have deleted the records from particular table.
See the query
delete tp_no='3' from tablename ,without adding the condition so that
multiple records are deleted with the tp_no='3'
"Warren Brunk" wrote:
> Did you take a backup prior to doing the delete?
> Last night or something?
> You could restore it with a different DB name and import the records...
> thanks,
> --
> /*
> Warren Brunk - MCITP - SQL 2005, MCDBA
> www.techintsolutions.com
> */
>
> "Srik" <Srik@.discussions.microsoft.com> wrote in message
> news:A500CA6C-3B51-4929-BBCA-BBA62368148E@.microsoft.com...
> > Hi,
> >
> > I have unknowingly deleted 100 records instead of 1 record.
> > I haven't set autocommit off also...
> > Could you please let me know if there is any way to get back those
> > records...
> >
> > Thanks in advance...
>
>|||Back up your transaction log, and do a point in time recovery.
Or you can take your latest full back up and restore it to a new DB and copy
the data in.
thanks,
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/
"Srik" <Srik@.discussions.microsoft.com> wrote in message
news:0E12A098-1D1B-4E6C-9BA1-853FC2F7ACA2@.microsoft.com...
> There is back up, but i dont know the duration of the backup they take
> by 10:30 i have deleted the records from particular table.
> See the query
> delete tp_no='3' from tablename ,without adding the condition so that
> multiple records are deleted with the tp_no='3'
> "Warren Brunk" wrote:
>> Did you take a backup prior to doing the delete?
>> Last night or something?
>> You could restore it with a different DB name and import the records...
>> thanks,
>> --
>> /*
>> Warren Brunk - MCITP - SQL 2005, MCDBA
>> www.techintsolutions.com
>> */
>>
>> "Srik" <Srik@.discussions.microsoft.com> wrote in message
>> news:A500CA6C-3B51-4929-BBCA-BBA62368148E@.microsoft.com...
>> > Hi,
>> >
>> > I have unknowingly deleted 100 records instead of 1 record.
>> > I haven't set autocommit off also...
>> > Could you please let me know if there is any way to get back those
>> > records...
>> >
>> > Thanks in advance...
>>|||If you dont have backup or database snapshot where you can read the data you
deleted, perhaps you can use some third party tools for transaction log
managment. I believe red gate has one tool as well as lumigent (log
explorer).
There are probably others, if you search on the net you should find a
couple.
MC
"Srik" <Srik@.discussions.microsoft.com> wrote in message
news:A500CA6C-3B51-4929-BBCA-BBA62368148E@.microsoft.com...
> Hi,
> I have unknowingly deleted 100 records instead of 1 record.
> I haven't set autocommit off also...
> Could you please let me know if there is any way to get back those
records...
> Thanks in advance...