How to change Recovery mode

We can use the codes below to get the recovery mode of a database:
select DatabasePropertyEx('test', 'Recovery')
Now I want to change a database's Recovery to SIMPLE.
How can I do that?Hi ad,
Try: Alter Database <DB Name> Set Recovery Simple
"ad" wrote:

> We can use the codes below to get the recovery mode of a database:
> select DatabasePropertyEx('test', 'Recovery')
> Now I want to change a database's Recovery to SIMPLE.
> How can I do that?
>
>
>

How to Change AutoNumber Not Start From 1

I have a table with structure below :
Table_A
{
PGradeID int IDENTITY(1,1),
PName varchar(20)
}

If I delete all records on Table_A, and then fill some records,
PGradeID will no longer start from 1 anymore, everybody knows that. How
to change it so PGradeID will start from 1 after all records was
deleted?

Thanks b4

ResantLookup the DBCC CHECKIDENT command. Also, if you TRUNCATE rather than DELETE
then the IDENTITY will be reset to seed.

Why do you care what the IDENTITY value is? It's usually fatal to attach any
significance to the value assigned by IDENTITY. If you care about the value
then IDENTITY isn't the right solution.

--
David Portas
SQL Server MVP
--|||Reset identity value will not cause problem in my case, but I
appreciate your warning.
Thanks a lot, it's works!

how to capture custom made error message into table

Dear all,
I want to know how to custom made error message into table, as I illustrated
T-SQL below :
if objectproperty(object_id('DateTable'),'I
sUserTable')=1
drop table DateTable
if objectproperty(object_id('InputFromFlatF
ile1'),'IsUserTable')=1
drop table InputFromFlatFile1
if objectproperty(object_id('errorLog'),'Is
UserTable')=1
drop table errorLog
if objectproperty(object_id('usp_testData')
,'IsProcedure')=1
drop proc usp_testData
create table DateTable
(datetimestamp datetime)
go
create table errorLog
(
data varchar(200),
errmsg varchar(2000)
)
go
create proc usp_testData
(@.dateinfo datetime)
as
begin
if convert(varchar(8),@.dateinfo,112)<'20060328'
begin
raiserror('Date input invalid because it''s entered before 28 Mar
2006',16,1)
return
end
else
insert into DateTable (datetimestamp) values (@.dateinfo)
end
go
create table InputFromFlatFile1
(
id int identity (1,1) not null,
linestring varchar(100)
)
go
insert into InputFromFlatFile1 (linestring) values ('20060328');
insert into InputFromFlatFile1 (linestring) values ('20060212');
insert into InputFromFlatFile1 (linestring) values ('20060115');
declare @.linestring varchar(200), @.errmsg varchar(2000)
declare @.cnt int, @.recnum int, @.error int
set @.cnt = 1
select @.recnum = count(*) from InputFromFlatFile1
while @.cnt <= @.recnum
begin
select @.linestring = linestring from InputFromFlatFile1 where id = @.cnt;
print @.linestring
exec @.error = usp_testData @.linestring;
if @.error<>0 or @.@.error <> 0
begin
if @.@.error <> 0
begin
set @.error = @.@.error
select @.errmsg = description from master.dbo.sysmessages where error =
@.error
end
/*
How can I capture user-made error in stored procedure usp_testData to put
into ErrorLog table
'
*/
insert into errorLog values (@.linestring, @.errmsg)
end
set @.cnt = @.cnt + 1;
end
select * from DateTable
select * from errorLog
from query analyzer I get error message below :
Date input invalid because it is entered before 28 Mar 2006
but I cannot get the error message as above in errorLog table
How can I capture error message like in query analyzer in errorLog table?
Regards,
Koronx
SQL HobbistHi
Check out http://www.sommarskog.se/error-handling-II.html and
http://www.sommarskog.se/error-handling-I.html You will need to add the
insert statement into your code or possibly do it through the client
application by trapping the error message returned.
John
"Kornx Koronx" wrote:

> Dear all,
> I want to know how to custom made error message into table, as I illustrat
ed
> T-SQL below :
> if objectproperty(object_id('DateTable'),'I
sUserTable')=1
> drop table DateTable
> if objectproperty(object_id('InputFromFlatF
ile1'),'IsUserTable')=1
> drop table InputFromFlatFile1
> if objectproperty(object_id('errorLog'),'Is
UserTable')=1
> drop table errorLog
> if objectproperty(object_id('usp_testData')
,'IsProcedure')=1
> drop proc usp_testData
> create table DateTable
> (datetimestamp datetime)
> go
> create table errorLog
> (
> data varchar(200),
> errmsg varchar(2000)
> )
> go
> create proc usp_testData
> (@.dateinfo datetime)
> as
> begin
> if convert(varchar(8),@.dateinfo,112)<'20060328'
> begin
> raiserror('Date input invalid because it''s entered before 28 Mar
> 2006',16,1)
> return
> end
> else
> insert into DateTable (datetimestamp) values (@.dateinfo)
> end
> go
> create table InputFromFlatFile1
> (
> id int identity (1,1) not null,
> linestring varchar(100)
> )
> go
> insert into InputFromFlatFile1 (linestring) values ('20060328');
> insert into InputFromFlatFile1 (linestring) values ('20060212');
> insert into InputFromFlatFile1 (linestring) values ('20060115');
> declare @.linestring varchar(200), @.errmsg varchar(2000)
> declare @.cnt int, @.recnum int, @.error int
> set @.cnt = 1
> select @.recnum = count(*) from InputFromFlatFile1
> while @.cnt <= @.recnum
> begin
> select @.linestring = linestring from InputFromFlatFile1 where id = @.cnt;
> print @.linestring
> exec @.error = usp_testData @.linestring;
> if @.error<>0 or @.@.error <> 0
> begin
> if @.@.error <> 0
> begin
> set @.error = @.@.error
> select @.errmsg = description from master.dbo.sysmessages where error =
> @.error
> end
> /*
> How can I capture user-made error in stored procedure usp_testData to put
> into ErrorLog table
> '
> */
> insert into errorLog values (@.linestring, @.errmsg)
> end
> set @.cnt = @.cnt + 1;
> end
> select * from DateTable
> select * from errorLog
> from query analyzer I get error message below :
> Date input invalid because it is entered before 28 Mar 2006
>
> but I cannot get the error message as above in errorLog table
> How can I capture error message like in query analyzer in errorLog table?
> Regards,
> Koronx
> SQL Hobbist

How to call talbe/SP from Linked server with out database and user

Dear Friends,
Usually we call tabal and SP like given below.
select * from [linkedserver].[database].[dbo].[TableName]
EXEC [linkedserver].[database].[dbo].usp_storedprocedure
but I like to all with out database and username.
I tryed like below
select * from [linkedserver]...[TableName]
OR
select * from [linkedserver]..[user].[TableName]
It's gives error
Server: Msg 7313, Level 16, State 1, Line 1
Invalid schema or catalog specified for provider 'MSDASQL'.
OLE DB error trace [Non-interface error: Invalid schema or catalog
specified for the provider.].
Please help me to achive this.
Thasks and regards,
Rajesh
On Wed, 14 Sep 2005 07:57:08 -0700, Rajesha wrote:

>Dear Friends,
>Usually we call tabal and SP like given below.
> select * from [linkedserver].[database].[dbo].[TableName]
> EXEC [linkedserver].[database].[dbo].usp_storedprocedure
>but I like to all with out database and username.
>I tryed like below
>select * from [linkedserver]...[TableName]
>OR
>select * from [linkedserver]..[user].[TableName]
>It's gives error
>Server: Msg 7313, Level 16, State 1, Line 1
>Invalid schema or catalog specified for provider 'MSDASQL'.
>OLE DB error trace [Non-interface error: Invalid schema or catalog
>specified for the provider.].
>Please help me to achive this.
>Thasks and regards,
>Rajesh
Hi Rajesh,
You can't leave out the databasename. A linked server might hold more
than one database, so you have to specify that part.
I believe that you can leave out the owner, but I'm not sure, and I
can't test that right now. However, it is recommended that you always
include the owner. This helps SQL Server find the object more quickly,
and it helps reduce the number of recompiles.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)