Record Locks: following

Showing posts with label following. Show all posts

Friday, March 30, 2012

Recovery of a corrupted Database due to power failure

On the plus side, nothing makes you start thinking about backups like losing
(or almost losing) a bunch of critical data because you didn't bother
backing it up in the first place

Recovery of a corrupted Database due to power failure

On the plus side, nothing makes you start thinking about backups like losing
(or almost losing) a bunch of critical data because you didn't bother
backing it up in the first place

sql

Recovery of a corrupted Database due to power failure

Hi,
Iam looking for some tools or methods to recover a corrupted mdf file (SQL
-MSDE 2000)..please help me and I have tried the following :
1. used osql to open the master database and run the DBCC checkdb with
specified database name and given the following message :
Database 'RubyDB' cannot be opened. It has been marked SUSPECT by recovery.
See the SQL Server errorlog for more information.
... i didn't find any useful information in the error log and looked in the
msdn articles and tried to reset the status using sp_resetstatus and didn't
helped. also tried to used the update to change the suspect flag status.
didn't helped
2. also tried to detach the database using the sp_detach_db and tried to
re-attach the particular db by using :
sp_attach_db @.dbname = N'RubyDB', @.filename1 = N'C:\Program Files\Microsoft
SQL Server\MSSQL$RUBYMSDEINSTANCE\Data\RubyDB_Data.MDF', @.filename2 = N'C:\Program Files\Microsoft SQL
Server\MSSQL$RUBYMSDEINSTANCE\Data\RubyDB_Log.LDF' ;
An got a message :
Msg 9003, Level 20, State 6, Server ADDSV1WD67Z461\RUBYMSDEINSTANCE, Line 1
The LSN (742:208:1) passed to log scan in database 'RubyDB' is invalid.
Please help me if there are any tools to analyze the database problem (in
the .mdf file) and a way to get back the data (at least in partial)...
Thanks in advance
Hari"Got Backup?"
If you're against restoring from your last good backup for some reason, then
Google up on suspect databases. There are "unofficial" ways to reset
suspect databases. They're not 100% though, and you may end up having to
restore from backup anyway.
"Hari" <Hari@.discussions.microsoft.com> wrote in message
news:3AB6DE2D-FE0A-450C-A8CF-8E597C97ED5B@.microsoft.com...
> Hi,
> Iam looking for some tools or methods to recover a corrupted mdf file (SQL
> -MSDE 2000)..please help me and I have tried the following :
> 1. used osql to open the master database and run the DBCC checkdb with
> specified database name and given the following message :
> Database 'RubyDB' cannot be opened. It has been marked SUSPECT by
> recovery.
> See the SQL Server errorlog for more information.
> ... i didn't find any useful information in the error log and looked in
> the
> msdn articles and tried to reset the status using sp_resetstatus and
> didn't
> helped. also tried to used the update to change the suspect flag status.
> didn't helped
> 2. also tried to detach the database using the sp_detach_db and tried to
> re-attach the particular db by using :
> sp_attach_db @.dbname = N'RubyDB', @.filename1 = N'C:\Program
> Files\Microsoft
> SQL Server\MSSQL$RUBYMSDEINSTANCE\Data\RubyDB_Data.MDF', @.filename2 => N'C:\Program Files\Microsoft SQL
> Server\MSSQL$RUBYMSDEINSTANCE\Data\RubyDB_Log.LDF' ;
> An got a message :
>
> Msg 9003, Level 20, State 6, Server ADDSV1WD67Z461\RUBYMSDEINSTANCE, Line
> 1
> The LSN (742:208:1) passed to log scan in database 'RubyDB' is invalid.
>
> Please help me if there are any tools to analyze the database problem (in
> the .mdf file) and a way to get back the data (at least in partial)...
>
> Thanks in advance
> Hari
>|||Mike C# wrote:
> "Got Backup?"
>
What is this "backup" of which you speak? :-)
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Mike, could you give me more deatils on this unoffical way of resetting the
suspect database.
hari
"Mike C#" wrote:
> "Got Backup?"
> If you're against restoring from your last good backup for some reason, then
> Google up on suspect databases. There are "unofficial" ways to reset
> suspect databases. They're not 100% though, and you may end up having to
> restore from backup anyway.
> "Hari" <Hari@.discussions.microsoft.com> wrote in message
> news:3AB6DE2D-FE0A-450C-A8CF-8E597C97ED5B@.microsoft.com...
> > Hi,
> >
> > Iam looking for some tools or methods to recover a corrupted mdf file (SQL
> > -MSDE 2000)..please help me and I have tried the following :
> >
> > 1. used osql to open the master database and run the DBCC checkdb with
> > specified database name and given the following message :
> >
> > Database 'RubyDB' cannot be opened. It has been marked SUSPECT by
> > recovery.
> > See the SQL Server errorlog for more information.
> >
> > ... i didn't find any useful information in the error log and looked in
> > the
> > msdn articles and tried to reset the status using sp_resetstatus and
> > didn't
> > helped. also tried to used the update to change the suspect flag status.
> > didn't helped
> >
> > 2. also tried to detach the database using the sp_detach_db and tried to
> > re-attach the particular db by using :
> >
> > sp_attach_db @.dbname = N'RubyDB', @.filename1 = N'C:\Program
> > Files\Microsoft
> > SQL Server\MSSQL$RUBYMSDEINSTANCE\Data\RubyDB_Data.MDF', @.filename2 => > N'C:\Program Files\Microsoft SQL
> > Server\MSSQL$RUBYMSDEINSTANCE\Data\RubyDB_Log.LDF' ;
> >
> > An got a message :
> >
> >
> > Msg 9003, Level 20, State 6, Server ADDSV1WD67Z461\RUBYMSDEINSTANCE, Line
> > 1
> > The LSN (742:208:1) passed to log scan in database 'RubyDB' is invalid.
> >
> >
> > Please help me if there are any tools to analyze the database problem (in
> > the .mdf file) and a way to get back the data (at least in partial)...
> >
> >
> > Thanks in advance
> >
> > Hari
> >
> >
>
>|||I haven't had to do it in a long time, but basically you try to trick SQL
Server into thinking the database is no longer suspect. As I mentioned, my
good buddies Larry Page and Sergey Brin have a ton of information on it over
at their website (GOOGLE.COM).
If you do get it to work, then I would recommend immediately copying
everything to a NEW database and immediately starting to take daily BACKUPS
so you don't have to go through this again.
"Hari" <Hari@.discussions.microsoft.com> wrote in message
news:BB4CE1FD-CC3C-4DFA-A1D2-6BA056CBFA79@.microsoft.com...
> Mike, could you give me more deatils on this unoffical way of resetting
> the
> suspect database.
> hari
> "Mike C#" wrote:
>> "Got Backup?"
>> If you're against restoring from your last good backup for some reason,
>> then
>> Google up on suspect databases. There are "unofficial" ways to reset
>> suspect databases. They're not 100% though, and you may end up having to
>> restore from backup anyway.
>> "Hari" <Hari@.discussions.microsoft.com> wrote in message
>> news:3AB6DE2D-FE0A-450C-A8CF-8E597C97ED5B@.microsoft.com...
>> > Hi,
>> >
>> > Iam looking for some tools or methods to recover a corrupted mdf file
>> > (SQL
>> > -MSDE 2000)..please help me and I have tried the following :
>> >
>> > 1. used osql to open the master database and run the DBCC checkdb with
>> > specified database name and given the following message :
>> >
>> > Database 'RubyDB' cannot be opened. It has been marked SUSPECT by
>> > recovery.
>> > See the SQL Server errorlog for more information.
>> >
>> > ... i didn't find any useful information in the error log and looked in
>> > the
>> > msdn articles and tried to reset the status using sp_resetstatus and
>> > didn't
>> > helped. also tried to used the update to change the suspect flag
>> > status.
>> > didn't helped
>> >
>> > 2. also tried to detach the database using the sp_detach_db and tried
>> > to
>> > re-attach the particular db by using :
>> >
>> > sp_attach_db @.dbname = N'RubyDB', @.filename1 = N'C:\Program
>> > Files\Microsoft
>> > SQL Server\MSSQL$RUBYMSDEINSTANCE\Data\RubyDB_Data.MDF', @.filename2 =>> > N'C:\Program Files\Microsoft SQL
>> > Server\MSSQL$RUBYMSDEINSTANCE\Data\RubyDB_Log.LDF' ;
>> >
>> > An got a message :
>> >
>> >
>> > Msg 9003, Level 20, State 6, Server ADDSV1WD67Z461\RUBYMSDEINSTANCE,
>> > Line
>> > 1
>> > The LSN (742:208:1) passed to log scan in database 'RubyDB' is invalid.
>> >
>> >
>> > Please help me if there are any tools to analyze the database problem
>> > (in
>> > the .mdf file) and a way to get back the data (at least in partial)...
>> >
>> >
>> > Thanks in advance
>> >
>> > Hari
>> >
>> >
>>|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45AF77C7.8000807@.realsqlguy.com...
> Mike C# wrote:
>> "Got Backup?"
> What is this "backup" of which you speak? :-)
It's them high-falutin' words I done heard the DBA's slangin' around the
uther day :)
On the plus side, nothing makes you start thinking about backups like losing
(or almost losing) a bunch of critical data because you didn't bother
backing it up in the first place :)

Friday, March 23, 2012

Recovering from a lost log file

I'm trying to make sure that we're following the "best practices" with SQL Server and I have some questions about log files.

One general DBA rule is that data files must be on a different device than log files. This way, you can lose either disk and still recover all committed transactions. If you lose the data disk, you restore from backup and then rollforward all of the transactions that are in the log file. If you lose the log disk, you roll back uncommitted transactions and then create a new log file.

This scenerio doesn't seem to be supported by SQL Server. It looks like SQL Sever can deal with a failed data disk but, it can't handled a failed log disk. Is that correct? The documentation says that the log file holds both before image data and after image data so, if the log disk drops dead, SQL server can't roll back uncommitted transactions so you're left with a corrupt (or and least suspect) database. You can restore from backup but, you can't roll forward since you've lost the log file.

SQL Server seems to be a really good database with the exception of this gigantic flaw. I must be missing something, what is it?

Thanks,

John Vottero

John,

I think your explanation of why you separate log and data is where the problem lies. The transaction log in SQL Server is the first place tranaction data is written, and it's only written to the data files when a checkpoint occurs.

The reason you want to keep the log and data separate is because log file access is mostly writes, and you want to put the log on a mirrored drive, optimized for sequential write access, and put the data files on a RAID drive, because you'll mostly be reading from the data files. This provides the best performance of your database.

|||

Yes, there are lots of good reasons for putting logs and data on different disks but, whatever the configuration, I want to make sure that we don't lose a days worth of transactions if we lose a disk, any disk. Can that be done with SQL Server?

|||

Standard practice is to put your log file on a RAID 10 device - hardware mirroring.

Your guidelines seem to be based on loosing only 1 disk, so having a hardware mirror fits the requirement. In the event a physical disk is lost the mirror picks up and moves on.

If you loose the data drive and part of the mirror you're still fine, just restore the last full backup and the tail of the log.

If you need more assurance than that you can ship the log to another server.

Should you need still more security than log shipping you can use database mirroring in sql 2005 sp1.

In the end your business reasons are going to dictate what kind of fault tolerance you will need, and what kind of down time you can tolerate. That will drive your decision toward "best practices".

|||

John,

On our production servers I have a database maintenance plan which backs up the full database once a day in the early morning, and performs transaction log backups every hour. (One server has significant enough activity that I back up the transaction log every 15 minutes.)

These backups are done to disk files on a separate drive from either the data or the log files. Each night the backup files are then backed up to tape.

Since my data and log files are on our SAN, the backup files are sent to local drives on each server, so at no time will a single point of failure cause the loss of more than an hour's work, or in the case of the one server, more than 15 minutes work.

You might consider a similar backup strategy.

|||

All of our logical disks will be RAID10 so it's unlikely that we will lose anything but, I want to make sure that all the bases are covered. It looks like we'll be doing log shipping or frequent log backups.

Thanks for all the advice!

Wednesday, March 21, 2012

Recovering database

Sql Server 2000 on Win 2k:
I'm recieving the following message when trying to reattach a database file: "Error 823: I/O error 38(Reached the end of the file.) detected during last read at offset 0000000000000000 of file 'D:\MSSQL\Data\DbName_log.ldf'." The database came offline after a disk problem and it looks like the log file became corrupted. Any ideas on a way to restore this database from only the mdf files? Can you reattach a DB and recreate the log file?

My other idea was possible inserting into sysdatabases, putting the Db in 32768=emergency mode, and running DBCC CHECKDB? If that is even possible?

Any feedback will be greatly appreciated.

Thanks.The log file is corrupted. You need to create new log file for the database. The thing is that you will lose all the uncommitted transaction. use dbcc rebuild_log to create a new log file and reattach the db.

recovering a database w/no log

I am trying to attach a database with no log. Receiving
the following error:
Error 1813: Could not open new database 'optima3'.
Create database is aborted. device activation error. The
physical file name 'g:\logs\optima_log.ldf may be
incorrect.Try sp_attach_single_file_db
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"ababl" <anonymous@.discussions.microsoft.com> wrote in message
news:011d01c3d3aa$227af390$a101280a@.phx.gbl...
> I am trying to attach a database with no log. Receiving
> the following error:
> Error 1813: Could not open new database 'optima3'.
> Create database is aborted. device activation error. The
> physical file name 'g:\logs\optima_log.ldf may be
> incorrect.

Wednesday, March 7, 2012

RecordSets

Im unsing VB6 SP6 and SQl 2000
Im creating a recordset using the following codsample
Dim ContraRS as ADODB.Recordset
Dim SQLString as String
Set ContraRS = New ADODB.Recordset
SQL String = "Select * from Table1 where Id = 2
ContraRS.CursorLocation = adUseClient
ContraRS.Open SQLString, UserDBConnect, adOpenForwardOnly, adLockReadOnly
This returns a recordset. Is there a way i can repeat theis process and
append to the data. The reason for this is im trying to obtain a list of
Banking transactions and there may be multiple id's to search for, non are
fixed as we have no way of knowing which clients (id's) will be submitting
data in on any days. I could build a dynamic SQl query with lots of 'AND Id
=
'' but im looking to see if there is a simplier wayWhere do these ID numbers come from? Maybe you can use a subquery:
SELECT *
FROM Table1
WHERE id IN
(SELECT id
FROM SomeOtherTable
WHERE some_date = @.date
/* @.date = the date of the data you are searching for */)
Best not to use SELECT * in production code - list the required column
names instead.
Make use of stored procedures where you can because that approach has
many advantages over executing SELECT statements directly from VB. If
you use a stored proc you can pass a list of IDs as parameters and use
them in an IN clause:
...WHERE id IN (@.id1, @.id2, @.id3, ...)
Finally, why are you still developing VB6, which was officially retired
as of yesterday ;-).
David Portas
SQL Server MVP
--|||Peter Newman wrote:
> Im unsing VB6 SP6 and SQl 2000
> Im creating a recordset using the following codsample
> Dim ContraRS as ADODB.Recordset
> Dim SQLString as String
> Set ContraRS = New ADODB.Recordset
> SQL String = "Select * from Table1 where Id = 2
> ContraRS.CursorLocation = adUseClient
> ContraRS.Open SQLString, UserDBConnect, adOpenForwardOnly,
> adLockReadOnly
> This returns a recordset. Is there a way i can repeat theis process
> and append to the data. The reason for this is im trying to obtain a
> list of Banking transactions and there may be multiple id's to search
> for, non are fixed as we have no way of knowing which clients (id's)
> will be submitting data in on any days. I could build a dynamic SQl
> query with lots of 'AND Id = '' but im looking to see if there is a
> simplier way
No. I would consider moving this processing into a stored procedure which
returns only the final recordset needed by the client.
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||Peter Newman wrote:
> Im unsing VB6 SP6 and SQl 2000
> Im creating a recordset using the following codsample
> Dim ContraRS as ADODB.Recordset
> Dim SQLString as String
> Set ContraRS = New ADODB.Recordset
> SQL String = "Select * from Table1 where Id = 2
> ContraRS.CursorLocation = adUseClient
> ContraRS.Open SQLString, UserDBConnect, adOpenForwardOnly,
> adLockReadOnly
> This returns a recordset. Is there a way i can repeat theis process
> and append to the data. The reason for this is im trying to obtain a
> list of Banking transactions and there may be multiple id's to search
> for, non are fixed as we have no way of knowing which clients (id's)
> will be submitting data in on any days. I could build a dynamic SQl
> query with lots of 'AND Id = '' but im looking to see if there is a
> simplier way
How are you deciding what id's to search for?
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Recordset solution is sought. Please help.

-- First of all, thank you for your effort and your time.
--
-- I am looking for a record set solution to the following problem.
--
-- We have feature 1 2 and 3 and users define certain combinations of them.
These can be 2-way combinations where feature3 is null or 3 way combinations
-- For simplicity, let us assume we have values A B and C for these three
features.
-- Users can fill them in redundant ways as
-- ACB, CBA, BCA, CAB, ABC, BAC.
-- I would like to select a single occurrence, ABC, or one out of six,
always one distinct combination.
--
-- I have made an attempt however I remove all duplicate occurances. I just
don't see how I can resolve this. Please help.
--
-- Thanks again for your help.
--
set nocount on
declare @.tmp table (Feature varchar(10) not null, Feature1 varchar(10) not
null, Feature2 varchar(10) null)
insert @.tmp values ('A','C','B') -- one of the folowing 6 to return
insert @.tmp values ('C','B','A')
insert @.tmp values ('B','C','A')
insert @.tmp values ('C','A','B')
insert @.tmp values ('A','B','C')
insert @.tmp values ('B','A','C')
insert @.tmp values ('D','A','C') -- return either DAC or ACD
insert @.tmp values ('P','A','C')
insert @.tmp values ('N','B','D')
insert @.tmp values ('A','C','D')
SELECT *
FROM @.tmp t
WHERE NOT EXISTS
(
SELECT *
FROM @.tmp t2
WHERE t2.feature1 = t.feature AND t2.feature = t.feature1
AND t2.feature2 IS NULL AND t.feature2 IS NULL -- two way
)
AND NOT EXISTS
(
SELECT *
FROM @.tmp t2
WHERE -- 3 way
t2.feature = t.feature AND t2.feature1 = t.feature2 AND t2.feature2 =
t.feature1
OR
t2.feature1 = t.feature1 AND t2.feature = t.feature2 AND t2.feature2 =
t.feature
OR
t2.feature2 = t.feature2 AND t2.feature = t.feature1 AND t2.feature1 =
t.feature
)
-- Expected outcome:
A,B,C
D,A,C
P,A,C
N,B,DYou can add an Identity column to the temp table and check for Identity
column not equal in your sub query.
Perayu
"Farmer" wrote:

> -- First of all, thank you for your effort and your time.
> --
> -- I am looking for a record set solution to the following problem.
> --
> -- We have feature 1 2 and 3 and users define certain combinations of them
.
> These can be 2-way combinations where feature3 is null or 3 way combinatio
ns
> -- For simplicity, let us assume we have values A B and C for these three
> features.
> -- Users can fill them in redundant ways as
> -- ACB, CBA, BCA, CAB, ABC, BAC.
> -- I would like to select a single occurrence, ABC, or one out of six,
> always one distinct combination.
> --
> -- I have made an attempt however I remove all duplicate occurances. I jus
t
> don't see how I can resolve this. Please help.
> --
> -- Thanks again for your help.
> --
> set nocount on
> declare @.tmp table (Feature varchar(10) not null, Feature1 varchar(10) not
> null, Feature2 varchar(10) null)
> insert @.tmp values ('A','C','B') -- one of the folowing 6 to return
> insert @.tmp values ('C','B','A')
> insert @.tmp values ('B','C','A')
> insert @.tmp values ('C','A','B')
> insert @.tmp values ('A','B','C')
> insert @.tmp values ('B','A','C')
> insert @.tmp values ('D','A','C') -- return either DAC or ACD
> insert @.tmp values ('P','A','C')
> insert @.tmp values ('N','B','D')
> insert @.tmp values ('A','C','D')
>
> SELECT *
> FROM @.tmp t
> WHERE NOT EXISTS
> (
> SELECT *
> FROM @.tmp t2
> WHERE t2.feature1 = t.feature AND t2.feature = t.feature1
> AND t2.feature2 IS NULL AND t.feature2 IS NULL -- two way
> )
> AND NOT EXISTS
> (
> SELECT *
> FROM @.tmp t2
> WHERE -- 3 way
> t2.feature = t.feature AND t2.feature1 = t.feature2 AND t2.feature2 =
> t.feature1
> OR
> t2.feature1 = t.feature1 AND t2.feature = t.feature2 AND t2.feature2 =
> t.feature
> OR
> t2.feature2 = t.feature2 AND t2.feature = t.feature1 AND t2.feature1 =
> t.feature
> )
> -- Expected outcome:
> A,B,C
> D,A,C
> P,A,C
> N,B,D
>
>|||Thank you for replying.
How do you suggest I do it? Please post what you think I can use.
"Perayu" <Perayu@.discussions.microsoft.com> wrote in message
news:B06C8780-1282-4F0E-AC17-EFBF72703991@.microsoft.com...
> You can add an Identity column to the temp table and check for Identity
> column not equal in your sub query.
> Perayu
> "Farmer" wrote:
>|||Farmer
Why not perfom such reports on the client side?
"Farmer" <someone@.somewhere.com> wrote in message
news:ukeQ0UzpFHA.3180@.TK2MSFTNGP15.phx.gbl...
> Thank you for replying.
> How do you suggest I do it? Please post what you think I can use.
>
> "Perayu" <Perayu@.discussions.microsoft.com> wrote in message
> news:B06C8780-1282-4F0E-AC17-EFBF72703991@.microsoft.com...
>

Recordset is read-only

Hey,

I'm using CRecordSet to add new rows to some table.

The Sequence of operations is as following:

m_recSet->Open();

m_recSet->AddNew();

m_recSet->Update();

In the AddNew() function i'm getting an Exception Saying "Recordset is read-only".

I saw in previous posts that this problem is caused when there is no Primary Key, but, my table

does not have a Primary Key and i don't want to set one.

How can i add rows to a table that doesn't have Primary Key ?

Thank

Shahar

On http://www.thescripts.com/forum/thread82452.html I see the same problem being discussed. Is there a reason why you don't want a primary key on the table?

Thanks

Waseem

|||

The reason is as simple as there is no single primary key, i don't want to disable duplicate rows, and even if i'll compromise on that

i'll need to create Primary Key that will include 5 Fields, which as i see it won't be very efficient.

Waseem Basheer - MSFT wrote:

On http://www.thescripts.com/forum/thread82452.html I see the same problem being discussed. Is there a reason why you don't want a primary key on the table?

Thanks

Waseem

|||Perhaps you 'could' add an IDENTITY column to the table.

Saturday, February 25, 2012

records for the last 6 months?

I need to retrieve records for the last 6 months so I have the following in my query:
created < (getdate() - 180)

Of course not all months are 30 days so this isn't 100% accurate. I need to be 100% accurate but haven't found a better solution.

Can someone suggest a better solution?

Thanks.
Maybe CREATED < dateadd (mm, - 6, getdate()) ?|||

Depends on exactly what you mean by 6 months..

You could say

select dateadd(month,-6,getdate())

So for today, it would be 2006-11-16 15:01:04.490 since my current getdate is 2007-05-16 15:01:04.490. I think this actually makes the most sense, as it does not really concern itself with the number of days.

However, if you need the fixed number of days to give you a consistent comparison, 180 days is a good thing too.)

Monday, February 20, 2012

Record Selection Formula Help

I have the following data for example

GROUP SECTION
Invoice Number [Aug 8, 2007]

DETAILS SECTION
ItemNo | Description | Latest PurchaseDate
SAMPLEA | DESCRIPTIONA | Aug 7, 2007
SAMPLEA | DESCRIPTIONA | Jul 1, 2007
SAMPLEA | DESCRIPTIONA | Jun 5, 2007
SAMPLEB | DESCRIPTIONB | Jun 6, 2007
SAMPLEB | DESCRIPTIONB | May 5, 2007

Is there a way i can only select in the detail section the maximum date of the latest purchase where the latest purchase date should be <= Invoice Number Date

Thanks for the help.I am not sure what you want to do.

One way to show only the latest detail is: 1
1. sort the details section on the appropriate field so that the latest detail appears last.
2. Move all the fields in the detail section down to the group footer, maintaining their location across the page.
3. If you have some sort of totaling in the group footer, create a group footer B and move the summary fields there.
4. Hide or suppress the details section.

record retrieving problem

hi all,

I have a table productprice which has the following feilds

id price datecreated productname

1 12.00 13/05/2007 a1

2 23.00 14/05/2007 a1

3 24.00 15/05/2007 a1

4 56.00 13/05/2007 b1

5 34.00 18/05/2007 b1

6 23.00 21/05/2007 b1

7 11.00 12/02/2007 c1

8 78.00 12/03/2007 c2

Ineed to select the rows that are highlighted here.. ie the row that hasthe max(datecreated) for all the productname in the table..

plz help

thanks in advance..

I'll assume in row #8, the productname was supposed to be c1 and was a typo. With that assumption:

SELECT *

FROM (

SELECT *,row_number() OVER(Partition by productname ORDER BY datecreated DESC,id DESC) TheRank

FROM productprice

) t1

WHERE TheRank=1

|||

hi Motley,

First thanks to Motley for his reply..

row-number() function is a new funtion in sql swerver 2005..But Iam using sql server 2000.And I am Afraid that is funtion will work in sql server 2000.Sorry for not mentioning the databse that I am using..

I am not much aware of this function..I searched in the internet and I could find this result..

how can i do this in sql server 2000 ?

thanks in advance,

|||

SELECT p1.*

FROM ProductPrice p1

LEFT JOIN ProductPrice p2 ON ((p1.datecreated<p2.datecreated OR (p1.datecreated=p2.datecreated and p1.id<p2.id)) AND p1.productname=p2.productname)

WHERE p2.id IS NULL

Please test this, as I believe it's performance gets exponentially worse as the number of records grows.

Friday, March 30, 2012

Recovery of a corrupted Database due to power failure

Recovery of a corrupted Database due to power failure

Recovery of a corrupted Database due to power failure

Friday, March 23, 2012

Recovering from a lost log file

Wednesday, March 21, 2012

Recovering database

recovering a database w/no log

Wednesday, March 7, 2012

RecordSets

Recordset solution is sought. Please help.

Recordset is read-only

Saturday, February 25, 2012

records for the last 6 months?

Monday, February 20, 2012

Record Selection Formula Help

record retrieving problem

Record Locks

Blog Archive

About Me