How can I restore a field back to Null?

Hello,
I need to restore fields for records meeting a certain criteria back to
null. Is there a way to do this in a stored procedure?
dbuchanan
I figured it out.
\\
Update tbl040cmpt SET
cmSmallint08 = cmSmallint05,
cmSmallint04 = null
FROM tbl040cmpt
WHERE fkJob = 'd8779793-5f1a-4092-bad3-bf3ee5b50c3e'
//
dbuchanan

How can I restore a field back to Null?

Hello,
I need to restore fields for records meeting a certain criteria back to
null. Is there a way to do this in a stored procedure?
dbuchananI figured it out.
\\
Update tbl040cmpt SET
cmSmallint08 = cmSmallint05,
cmSmallint04 = null
FROM tbl040cmpt
WHERE fkJob = 'd8779793-5f1a-4092-bad3-bf3ee5b50c3e'
//
dbuchanansql

How can I restore a field back to Null?

Hello,
I need to restore fields for records meeting a certain criteria back to
null. Is there a way to do this in a stored procedure?
dbuchananI figured it out.
\\
Update tbl040cmpt SET
cmSmallint08 = cmSmallint05,
cmSmallint04 = null
FROM tbl040cmpt
WHERE fkJob = 'd8779793-5f1a-4092-bad3-bf3ee5b50c3e'
//
dbuchanan

how can i regenerate Database from my Backup file ? ?

hello All !!

i have taken mybackup(back file ) of database and i stored in folder , now i want to know

1)if i format my Hard Disk . and reinstall Sql server 2000 ?

2) if i delete that Database ?

how can i REGENERATE my database ?? again

thnk u all

In case you just have the backup somewhere safe / available after you have done / faced with 1) and/or 2), you can just restore the database from the .bak file.

SQl Server 2000 Backup And Restore
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlbackuprest.mspx

how can i read the <binary> in sql server

Can i read back the data'Please explain your question. Give an example of what you are trying to do.
David Portas
SQL Server MVP
--|||I have bought the application software which is linking with sql server 2000
from verndor
we can customize the software/table...
I saw one field which is set on<binary>
and we cannot see the data on that field...all data is <binary>
so i would like to ask can i read back the data?
thx
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> bl
news:7oydnU3XVb9xQp3cRVn-uQ@.giganews.com g...
> Please explain your question. Give an example of what you are trying to
do.
> --
> David Portas
> SQL Server MVP
> --
>|||inamori,
Just issue a SELECT statement on the binary column.
create table bin (
i int not null primary key identity (1,1),
b binary (8) -- binary column
)
go
insert bin (b) select cast(0xFF as int)
select i,cast(b as int) mybin from bin
i mybin
-- --
1 255
(1 row(s) affected)
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
inamori wrote:
> I have bought the application software which is linking with sql server 20
00
> from verndor
> we can customize the software/table...
> I saw one field which is set on<binary>
> and we cannot see the data on that field...all data is <binary>
> so i would like to ask can i read back the data?
> thx
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> bl
> news:7oydnU3XVb9xQp3cRVn-uQ@.giganews.com g...
>
> do.
>
>
>|||sorry the data type is image......
"inamori" <test@.test.com> bl news:cdr38s$lv22@.imsp212.netvigator.com
g...
> I have bought the application software which is linking with sql server
2000
> from verndor
> we can customize the software/table...
> I saw one field which is set on<binary>
> and we cannot see the data on that field...all data is <binary>
> so i would like to ask can i read back the data?
> thx
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> bl
> news:7oydnU3XVb9xQp3cRVn-uQ@.giganews.com g...
> do.
>|||inamori,
See this thread for some good information.
Subject: Re: How to Retrieve the Image stored in the DataBase
http://tinyurl.com/3k6pa
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
inamori wrote:
> sorry the data type is image......
> "inamori" <test@.test.com> bl news:cdr38s$lv22@.imsp212.netvigator.com
> g...
>
> 2000
>
>|||Thanks
i got something at least
"0x640200005E414C4C"
can i change back to the meaningful data.....
"Mark Allison" <marka@.no.tinned.meat.mvps.org> ?
news:OkE8BnLcEHA.1248@.TK2MSFTNGP11.phx.gbl ?...[vbcol=seagreen]
> inamori,
> Just issue a SELECT statement on the binary column.
> create table bin (
> i int not null primary key identity (1,1),
> b binary (8) -- binary column
> )
> go
> insert bin (b) select cast(0xFF as int)
> select i,cast(b as int) mybin from bin
> i mybin
> -- --
> 1 255
> (1 row(s) affected)
>
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> inamori wrote:
2000[vbcol=seagreen]|||Depends. Do you know what format the data is in? You'll probably need to do
this in a client application. For example if it contains a graphics file or
Word document then you'll need to open that in something that can interpret
the format.
David Portas
SQL Server MVP
--

how can i read the <binary> in sql server

Can i read back the data'Please explain your question. Give an example of what you are trying to do.
--
David Portas
SQL Server MVP
--|||I have bought the application software which is linking with sql server 2000
from verndor
we can customize the software/table...
I saw one field which is set on<binary>
and we cannot see the data on that field...all data is <binary>
so i would like to ask can i read back the data?
thx
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> ¦b¶l¥ó
news:7oydnU3XVb9xQp3cRVn-uQ@.giganews.com ¤¤¼¶¼g...
> Please explain your question. Give an example of what you are trying to
do.
> --
> David Portas
> SQL Server MVP
> --
>|||inamori,
Just issue a SELECT statement on the binary column.
create table bin (
i int not null primary key identity (1,1),
b binary (8) -- binary column
)
go
insert bin (b) select cast(0xFF as int)
select i,cast(b as int) mybin from bin
i mybin
-- --
1 255
(1 row(s) affected)
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
inamori wrote:
> I have bought the application software which is linking with sql server 2000
> from verndor
> we can customize the software/table...
> I saw one field which is set on<binary>
> and we cannot see the data on that field...all data is <binary>
> so i would like to ask can i read back the data?
> thx
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> ¦b¶l¥ó
> news:7oydnU3XVb9xQp3cRVn-uQ@.giganews.com ¤¤¼¶¼g...
>>Please explain your question. Give an example of what you are trying to
> do.
>>--
>>David Portas
>>SQL Server MVP
>>--
>>
>
>|||sorry the data type is image......
"inamori" <test@.test.com> ¦b¶l¥ó news:cdr38s$lv22@.imsp212.netvigator.com ¤¤
¼¶¼g...
> I have bought the application software which is linking with sql server
2000
> from verndor
> we can customize the software/table...
> I saw one field which is set on<binary>
> and we cannot see the data on that field...all data is <binary>
> so i would like to ask can i read back the data?
> thx
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> ¦b¶l¥ó
> news:7oydnU3XVb9xQp3cRVn-uQ@.giganews.com ¤¤¼¶¼g...
> > Please explain your question. Give an example of what you are trying to
> do.
> >
> > --
> > David Portas
> > SQL Server MVP
> > --
> >
> >
>|||inamori,
See this thread for some good information.
Subject: Re: How to Retrieve the Image stored in the DataBase
http://tinyurl.com/3k6pa
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
inamori wrote:
> sorry the data type is image......
> "inamori" <test@.test.com> ¦b¶l¥ó news:cdr38s$lv22@.imsp212.netvigator.com ¤¤
> ¼¶¼g...
>>I have bought the application software which is linking with sql server
> 2000
>>from verndor
>>we can customize the software/table...
>>I saw one field which is set on<binary>
>>and we cannot see the data on that field...all data is <binary>
>>so i would like to ask can i read back the data?
>>thx
>>"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> ¦b¶l¥ó
>>news:7oydnU3XVb9xQp3cRVn-uQ@.giganews.com ¤¤¼¶¼g...
>>Please explain your question. Give an example of what you are trying to
>>do.
>>--
>>David Portas
>>SQL Server MVP
>>--
>>
>>
>|||Thanks
i got something at least
"0x640200005E414C4C"
can i change back to the meaningful data.....
"Mark Allison" <marka@.no.tinned.meat.mvps.org> ?
news:OkE8BnLcEHA.1248@.TK2MSFTNGP11.phx.gbl ?...
> inamori,
> Just issue a SELECT statement on the binary column.
> create table bin (
> i int not null primary key identity (1,1),
> b binary (8) -- binary column
> )
> go
> insert bin (b) select cast(0xFF as int)
> select i,cast(b as int) mybin from bin
> i mybin
> -- --
> 1 255
> (1 row(s) affected)
>
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> inamori wrote:
> > I have bought the application software which is linking with sql server
2000
> > from verndor
> >
> > we can customize the software/table...
> >
> > I saw one field which is set on<binary>
> >
> > and we cannot see the data on that field...all data is <binary>
> >
> > so i would like to ask can i read back the data?
> >
> > thx
> > "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> ¦b¶l¥ó
> > news:7oydnU3XVb9xQp3cRVn-uQ@.giganews.com ¤¤¼¶¼g...
> >
> >>Please explain your question. Give an example of what you are trying to
> >
> > do.
> >
> >>--
> >>David Portas
> >>SQL Server MVP
> >>--
> >>
> >>
> >
> >
> >|||Depends. Do you know what format the data is in? You'll probably need to do
this in a client application. For example if it contains a graphics file or
Word document then you'll need to open that in something that can interpret
the format.
--
David Portas
SQL Server MVP
--

how can i read the <binary> in sql server

Can i read back the data?
Please explain your question. Give an example of what you are trying to do.
David Portas
SQL Server MVP
|||I have bought the application software which is linking with sql server 2000
from verndor
we can customize the software/table...
I saw one field which is set on<binary>
and we cannot see the data on that field...all data is <binary>
so i would like to ask can i read back the data?
thx
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> bl
news:7oydnU3XVb9xQp3cRVn-uQ@.giganews.com g...
> Please explain your question. Give an example of what you are trying to
do.
> --
> David Portas
> SQL Server MVP
> --
>
|||inamori,
Just issue a SELECT statement on the binary column.
create table bin (
i int not null primary key identity (1,1),
b binary (8) -- binary column
)
go
insert bin (b) select cast(0xFF as int)
select i,cast(b as int) mybin from bin
i mybin
-- --
1 255
(1 row(s) affected)
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
inamori wrote:
> I have bought the application software which is linking with sql server 2000
> from verndor
> we can customize the software/table...
> I saw one field which is set on<binary>
> and we cannot see the data on that field...all data is <binary>
> so i would like to ask can i read back the data?
> thx
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> bl
> news:7oydnU3XVb9xQp3cRVn-uQ@.giganews.com g...
>
> do.
>
>
|||sorry the data type is image......
"inamori" <test@.test.com> bl news:cdr38s$lv22@.imsp212.netvigator.com
g...
> I have bought the application software which is linking with sql server
2000
> from verndor
> we can customize the software/table...
> I saw one field which is set on<binary>
> and we cannot see the data on that field...all data is <binary>
> so i would like to ask can i read back the data?
> thx
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> bl
> news:7oydnU3XVb9xQp3cRVn-uQ@.giganews.com g...
> do.
>
|||inamori,
See this thread for some good information.
Subject: Re: How to Retrieve the Image stored in the DataBase
http://tinyurl.com/3k6pa
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
inamori wrote:
> sorry the data type is image......
> "inamori" <test@.test.com> bl news:cdr38s$lv22@.imsp212.netvigator.com
> g...
>
> 2000
>
>
|||Thanks
i got something at least
"0x640200005E414C4C"
can i change back to the meaningful data.....
"Mark Allison" <marka@.no.tinned.meat.mvps.org> ?
news:OkE8BnLcEHA.1248@.TK2MSFTNGP11.phx.gbl ?...[vbcol=seagreen]
> inamori,
> Just issue a SELECT statement on the binary column.
> create table bin (
> i int not null primary key identity (1,1),
> b binary (8) -- binary column
> )
> go
> insert bin (b) select cast(0xFF as int)
> select i,cast(b as int) mybin from bin
> i mybin
> -- --
> 1 255
> (1 row(s) affected)
>
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> inamori wrote:
2000[vbcol=seagreen]
|||Depends. Do you know what format the data is in? You'll probably need to do
this in a client application. For example if it contains a graphics file or
Word document then you'll need to open that in something that can interpret
the format.
David Portas
SQL Server MVP
sql

how can i query the date one week back from now?

for example today is May,2 2006: my query should return all data where date = April 24, 2006.

Try this RDL expression for calculating the previous week:
=Today.AddDays(-7)

If you want to do this directly in the query, you will need to look for date related functions for the particular database you are working with. For SQL Server queries you would use the DateAdd function: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_da-db_3vtw.asp

-- Robert

|||thanks a lot!!! sql

how can I push xml data back to table?

Hi, I have a xml string that reveived from select ... for xml auto in sql
server 2005, now how can I push these xml data back to another sql server
database table? Thx a lot.
Risen
Hello Risen,
R> Hi, I have a xml string that reveived from select ... for xml auto
R> in sql server 2005, now how can I push these xml data back to another
R> sql server database table? Thx a lot.
In SQL 2000 or 2005, look into OPENXML.
In 2005, you also have the option of using the XML datatype and the nodes
function or using SSIS or writing your own SQLCLR parser.
Lots of ways to skin that Cat. The Cat won't like any of them. :X
Thanks,
Kent Tegels
http://staff.develop.com/ktegels/
|||Thanks : )
"Kent Tegels" <ktegels@.develop.com>
?:b87ad74ba118c8c096c4c89b30@.news.microsoft.c om...
> Hello Risen,
> R> Hi, I have a xml string that reveived from select ... for xml auto
> R> in sql server 2005, now how can I push these xml data back to another
> R> sql server database table? Thx a lot.
> In SQL 2000 or 2005, look into OPENXML.
> In 2005, you also have the option of using the XML datatype and the nodes
> function or using SSIS or writing your own SQLCLR parser.
> Lots of ways to skin that Cat. The Cat won't like any of them. :X
> Thanks,
> Kent Tegels
> http://staff.develop.com/ktegels/
>
>
|||You can also use Xml Bulkload functionality.
See more on this :http://msdn2.microsoft.com/en-us/library/ms171993.aspx
Regards,
Monica Frintu
"Risen" wrote:

> Thanks : )
>
> "Kent Tegels" <ktegels@.develop.com>
> ?:b87ad74ba118c8c096c4c89b30@.news.microsoft.c om...
>
>

how can I push xml data back to table?

Hi, I have a xml string that reveived from select ... for xml auto in sql
server 2005, now how can I push these xml data back to another sql server
database table? Thx a lot.
RisenHello Risen,
R> Hi, I have a xml string that reveived from select ... for xml auto
R> in sql server 2005, now how can I push these xml data back to another
R> sql server database table? Thx a lot.
In SQL 2000 or 2005, look into OPENXML.
In 2005, you also have the option of using the XML datatype and the nodes
function or using SSIS or writing your own SQLCLR parser.
Lots of ways to skin that Cat. The Cat won't like any of them. :X
Thanks,
Kent Tegels
http://staff.develop.com/ktegels/|||Thanks : )
"Kent Tegels" <ktegels@.develop.com>
':b87ad74ba118c8c096c4c89b30@.news.microsoft.com...
> Hello Risen,
> R> Hi, I have a xml string that reveived from select ... for xml auto
> R> in sql server 2005, now how can I push these xml data back to another
> R> sql server database table? Thx a lot.
> In SQL 2000 or 2005, look into OPENXML.
> In 2005, you also have the option of using the XML datatype and the nodes
> function or using SSIS or writing your own SQLCLR parser.
> Lots of ways to skin that Cat. The Cat won't like any of them. :X
> Thanks,
> Kent Tegels
> http://staff.develop.com/ktegels/
>
>|||You can also use Xml Bulkload functionality.
See more on this :http://msdn2.microsoft.com/en-us/library/ms171993.aspx
Regards,
--
Monica Frintu
"Risen" wrote:

> Thanks : )
>
> "Kent Tegels" <ktegels@.develop.com>
> ':b87ad74ba118c8c096c4c89b30@.news.microsoft.com...
>
>

how can I post back a statement from a store procedure to the .aspx page

Hi all,
Anyone can show me how can I catch the 'Print' statement that I have defined in my store procedure using SQL server 2000 DB on the .aspx page? ( I am using ASP.NET 1.0)
My store procedure as follow:
CREATE PROC NewAcctType
(@.acctType VARCHAR(20))
AS
BEGIN
--checks if the new account type is already exist
IF EXISTS (SELECT * FROM AcctTypeCatalog WHERE acctType = @.acctType)
BEGIN
PRINT 'The account type is already exist'
RETURN
END

BEGIN TRANSACTION
INSERT INTO AcctTypeCatalog (acctType) VALUES (@.acctType)

--if there is an error on the insertion, rolls back the transaction; otherwise, commits the transaction
IF @.@.error <> 0 OR @.@.rowcount <> 1
BEGIN
ROLLBACK TRANSACTION
PRINT 'Insertion failure on AcctTypeCatalog table.'
RETURN
END
ELSE
BEGIN
COMMIT TRANSACTION
END
END
Thanks for all your replies

The best thing to do would be to either create another parameter and set its type to output or return the statement as a select.
@.message varchar(100) output
set @.message = 'The account type is already exists'
or
select 'The account type is already exist' as message

Nick

|||Hi Nick,
As you stated:
@.message varchar(100) output
set @.message = 'The account type is already exists'
Do I put a return statement like "Return @.message"?
How about if there is no errror in the procedure, do I still need to return any value to the .aspx page?
Thanks.|||Hi Nick,
when I executed my store procedure in SQL 2000 server, I got an error said"Cannot use the OUTPUT option in a DECLARE statement"
But without the Declare keyword, I got an incorrect syntax error, so how can I solve this problem? Is that necessary to put the "output" keyword at the end of the declare varaible statement?
Thanks|||syntax is:
create procedure whateverName
@.message varchar(100) output
as
set @.message = ''
if @.@.ERROR
set @.message = 'your text here.'
If you have no error, the top set statement will allow a blank to be passed back.
Nick|||

Nick,
Here is my syntax:
CREATE PROC DeleteCust
(@.SSN VARCHAR(12), @.message VARCHAR(40) output)
AS
BEGIN

--checks if the SSN is already exist
IF NOT EXISTS (SELECT * FROM Customer WHERE SSN = @.SSN)
BEGIN
SET @.message = 'The SSN is not exist!'
RETURN
END
BEGIN TRANSACTION
DELETE FROM Customer WHERE SSN = @.SSN

--if there is an error on the delete, rolls back the transaction; otherwise, commits the transaction
IF @.@.error <> 0 OR @.@.rowcount <> 1
BEGIN
ROLLBACK TRANSACTION
SET @.message = 'Delete failure on Customer table.'
RETURN
END
ELSE
BEGIN
COMMIT TRANSACTION
END
END

I executed this proc as:
declare @.message VARCHAR(40)
exec deleteCust '111-11-1111', @.message output
Given that SSN is invalid, I suppose got the message 'The SSN is not exist!", however, I didn't get that message from the execution instead the system message showed "The command(s) completed successfully.", so anywhere I was wrong with the above SP or the execute statement?
Appreciated your reply

|||

If your just looking for the value after a run in QA, add:

declare @.message VARCHAR(40)
exec deleteCust '111-11-1111', @.message output
select @.message
Nick

|||

Thanks nick. I got the message when I run the Store Procedure in SQL server. However, how can I get the error message when I called the Store Procedure on my .aspx page?
I have these codes on my page: ('DeleteCust' is my store procedure name, 'SSN.Text' is the value from the input box)
myConnection = new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings("ConnectionString"));

var myCommand : SqlDataAdapter = new SqlDataAdapter("DeleteCust", myConnection);
myCommand.SelectCommand.CommandType = CommandType.StoredProcedure;

myCommand.SelectCommand.Parameters.Add(new SqlParameter("@.SSN", SqlDbType.VarChar, 12)).Value = SSN.Text;
Then, what should I put it here to catch the @.message value? The @.message is VARCHAR, and I have a Return keyword within my procedure, and the return value type is INT?
ping

|||

Add (Code is in VB):

dim parm as new sqlParameter("@.message", sqlDBtype.varchar, 100)

parm.direction = ParameterDirection.Output
myCommand.SelectCommand.Parameters.Add(parm)
After you do your call to stored proc:
strMessage(Or whatever variable you are adding to) = myCommand.SelectCommand.Parameters(1).Value
Nick

|||Hi Nick,
Could u explain more detail for these statements coz I am new to doing asp, I want to know it more about the meaning of those codes.
parm.direction = ParameterDirection.Output
strMessage(what variable should I add it here? could u give me an example? are u talking about @.message?)
when do I use strMessage() ?
Many thanks.

|||

Here is a quick and dirty article on output parms.

http://www.eggheadcafe.com/PrintSearchContent.asp?LINKID=624

Nick

how can i manage database on lan base[network base]software

hi
my self avi
currently i am developing one application in vb 6 and back end as
sqlserver 7 which is used on lan.
i have some problem. like in my database i have one table salesvoucher
which has 'voucherno' field. when 2-3 user will work on salesform at a
time [since the softwrae will run on lan] then the same voucherno will
save for all users data which is wrong i need to save different voucherno
for each no. is there any way in sqlserver to apply some condition on
database or to set some its property[tables] so that at a time only one
users data will save depend on firstcome first serve base or is there any
way to make changes in programme.
plz help me regarding this i need it very badly.
since its my first lan based software plz give me some books name
regarding this software for vb6 and sqlserver7"avinash" <pawar_avinash@.rediffmail.com> wrote in message
news:00a8643e7e3050f1ff2f4ae6aa7209df@.localhost.ta lkaboutdatabases.com...
> hi
> my self avi
> currently i am developing one application in vb 6 and back end as
> sqlserver 7 which is used on lan.
> i have some problem. like in my database i have one table salesvoucher
> which has 'voucherno' field. when 2-3 user will work on salesform at a
> time [since the softwrae will run on lan] then the same voucherno will
> save for all users data which is wrong i need to save different voucherno
> for each no. is there any way in sqlserver to apply some condition on
> database or to set some its property[tables] so that at a time only one
> users data will save depend on firstcome first serve base or is there any
> way to make changes in programme.
> plz help me regarding this i need it very badly.
> since its my first lan based software plz give me some books name
> regarding this software for vb6 and sqlserver7

If the voucherno must be unique, then you should put a primary key or unique
constraint on that column, so that it's impossible to have duplicates. If
the voucherno is generated on the client, then you would have to handle the
error raised when a duplicate is inserted (such as error 2627 for primary
key violation), generate a new number, and submit it again.

Alternatively, you could generate the voucherno in the database. If you just
need an integer value, then you can use the IDENTITY property to generate
the number for you - see "IDENTITY (Property)" in Books Online. Every time
you insert a new row, MSSQL will generate a new number for you - this avoids
writing any code to generate new numbers.

Regarding books, you might find some useful information about SQL books
here:

http://vyaskn.tripod.com/sqlbooks.htm

Someone else may be able to suggest a good VB/SQL book, or you might want to
post in a VB group.

Simon|||Dear Avanish... sorry I cannot help you in dat... while I m just wanna
post a msg here... BYE

How can I get back the lost view?

Hi,

I am a newbie working on MS Sql Server 2000 for a while. I accidentally deleted a view through Query Analyzer and want to get it back. All data are backed-up every day but there are a lot of red tapes I have to go through in order to draw the lost view from the backup. Indeed, a different division is taking care of backups in our organization and they don't want to spend time on my issue.

I'm wondering if there is an automatic logging capability of sql server showing modified/ deleted/ updated data objects on daily basis with their contents that can be accessed later on. Or is there another recovery mechanism that can be used to get back the lost view?

Thanks for your attention to this matter,

Batuhan

Hi,

as this is your first post in here, welcome to the groups :-)

Actions are logged within the tranaction log of SQL Server, but if you did not change the view (with an alter or create command) there will be no information in the log to rely on, in addition you would need to have the last backup for applying the transaction (log) to this version. i guess you will have to go the hard way and let the backup division restore the database for you to an older version. Thats why I keep a script of my database as a "small" backup to restore the object that are just scriptable Perhaps you should add this as a best practise to your daily work.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

I'd like to second Jens's suggestion to keep track of the T-SQL that was used to generate any database object - think of it as the database source code. You can manage that as you manage your application code, using SourceSafe, for example.

Thanks
Laurentiu

|||

Thanks for replying my post.

I'm really curious about the content of transaction-log. Does it record every change we made in the database or just 'transactions'?
Does it cover logging of update, insert, delete operations that were executed in the database? Another question is how I can view the transaction log. Do you know any free software tool to read transaction log?

Batuhan

|||Statements are wrapped in transactions to ensure the ACID of databases. You can either use explicit transactions using BEGIN TRANSACTIONS or the appropiate functionality of the provider like the ADO.NET implementation or implicit while doing a regular DML operation which is not wrapped up in a explicit transaction. The transactionlog cannot be viewed easily, there are special (non-free tools) for viewing these like this from L**igent (you will propably find the name searching on the internet).

There is an undocumented way to read the log, but this is sort a cryptic to investigate:

DBCC Log('tempdb',1) --use the appopiate parameters to read the log

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

How Can i Get a list of Back devices

Hi All,,

I want to get a list of backup devices for a selected database
ex :
Northwind Database i need to get the list of back devices for this db
any one know how the query could be written

best regards

Wafi MohtasebDoessp_helpdevice give you what you are looking for?

Terri