I got an error when I restored database

Hi Guys,
I got an error when I restored database. I tryed to restore a database that
is 8G. I got an error that is "An internal consistency error occured. Contact
technical support for assistance. RESTORE DATABASE is terminating
abnormally", then the database was marked Loading and in suspect mode. I try
to use command restore database policy with recovery, then got
errorimmediately:
File 'changept' was only partially restored by a database or file restore.
The entire file must be successfully restored before applying the log.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
I did restore this database two weeks ago in other database server and was
successful, but it is failed in this new server now. I don't know what reason
is. I need the help. Thank in advance.
Hi
"Iter" wrote:

> Hi Guys,
> I got an error when I restored database. I tryed to restore a database that
> is 8G. I got an error that is "An internal consistency error occured. Contact
> technical support for assistance. RESTORE DATABASE is terminating
> abnormally", then the database was marked Loading and in suspect mode. I try
> to use command restore database policy with recovery, then got
> errorimmediately:
> File 'changept' was only partially restored by a database or file restore.
> The entire file must be successfully restored before applying the log.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> I did restore this database two weeks ago in other database server and was
> successful, but it is failed in this new server now. I don't know what reason
> is. I need the help. Thank in advance.
Have you copied the backup onto this server? The backup itself may be
corrupted try doing a RESTORE HEADERONLY command from query analyser. It is
not clear how you are restoring this file, try using query analyser to
restore the database if you are using EM then you can post the command you
are using. As this is a new server, the database does not need to exist
before you restore it. Are you putting the files in a different location?
Check that you have permissions and enough space to do this.
If the database exists on a different server you may want to either detach
the database and copy the MDF/LDF files and re-attach them (to both servers!)
or backup from one server to the other using a UNC path for the backup file,
so that you don't have to copy the backup file.
HTH
John
|||>I try to use command restore database policy with recovery, then got
> errorimmediately:
> File 'changept' was only partially restored by a database or file restore.
> The entire file must be successfully restored before applying the log.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> I did restore this database two weeks ago in other database server and was
> successful, but it is failed in this new server now. I don't know what reason
> is. I need the help. Thank in advance.
Can we assume that this is the actual same backup file you are talking about
having restored before? This looks like there may be multiple backups in this
file and it is trying to restore a log file backup before restoring the full
backup.

I got an error when I restored database

Hi Guys,
I got an error when I restored database. I tryed to restore a database that
is 8G. I got an error that is "An internal consistency error occured. Contac
t
technical support for assistance. RESTORE DATABASE is terminating
abnormally", then the database was marked Loading and in suspect mode. I tr
y
to use command restore database policy with recovery, then got
errorimmediately:
File 'changept' was only partially restored by a database or file restore.
The entire file must be successfully restored before applying the log.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
I did restore this database two weeks ago in other database server and was
successful, but it is failed in this new server now. I don't know what reaso
n
is. I need the help. Thank in advance.Hi
"Iter" wrote:

> Hi Guys,
> I got an error when I restored database. I tryed to restore a database tha
t
> is 8G. I got an error that is "An internal consistency error occured. Cont
act
> technical support for assistance. RESTORE DATABASE is terminating
> abnormally", then the database was marked Loading and in suspect mode. I
try
> to use command restore database policy with recovery, then got
> errorimmediately:
> File 'changept' was only partially restored by a database or file restore.
> The entire file must be successfully restored before applying the log.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> I did restore this database two weeks ago in other database server and was
> successful, but it is failed in this new server now. I don't know what rea
son
> is. I need the help. Thank in advance.
Have you copied the backup onto this server? The backup itself may be
corrupted try doing a RESTORE HEADERONLY command from query analyser. It is
not clear how you are restoring this file, try using query analyser to
restore the database if you are using EM then you can post the command you
are using. As this is a new server, the database does not need to exist
before you restore it. Are you putting the files in a different location?
Check that you have permissions and enough space to do this.
If the database exists on a different server you may want to either detach
the database and copy the MDF/LDF files and re-attach them (to both servers!
)
or backup from one server to the other using a UNC path for the backup file,
so that you don't have to copy the backup file.
HTH
John|||>I try to use command restore database policy with recovery, then got
> errorimmediately:
> File 'changept' was only partially restored by a database or file restore.
> The entire file must be successfully restored before applying the log.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> I did restore this database two weeks ago in other database server and was
> successful, but it is failed in this new server now. I don't know what rea
son
> is. I need the help. Thank in advance.
Can we assume that this is the actual same backup file you are talking about
having restored before? This looks like there may be multiple backups in thi
s
file and it is trying to restore a log file backup before restoring the full
backup.

I get "Timeout expired"

Hi, everyone! As many of guys around this newsgroup, I've been getting
"Timeout expired" using SQL Server 2000.
It raises that error message out of nowhere... first, I tought it was
because of too many users connected, but made some test with just ONE SINGLE
user connected to DataBase, and I'm still getting this message.
It acts randomly, as I can be working perfectly now and the next second, I
get the anoying message again.
I've even tried to remake the table's index, but it doesn't seem to work...
I'm not even able to erase the primary index in the first place...
I have 52 tables in my database, and the other 51 work just fine, it's just
one which is getting me into trouble...
I'm desperate, can you help me, please?!? (picture a face of agony while
reading the latter LOL ;-) )
best regards,
Alex
AlexTirado wrote:
> Hi, everyone! As many of guys around this newsgroup, I've been getting
> "Timeout expired" using SQL Server 2000.
> It raises that error message out of nowhere... first, I tought it was
> because of too many users connected, but made some test with just ONE
> SINGLE user connected to DataBase, and I'm still getting this message.
> It acts randomly, as I can be working perfectly now and the next
> second, I get the anoying message again.
> I've even tried to remake the table's index, but it doesn't seem to
> work... I'm not even able to erase the primary index in the first
> place...
> I have 52 tables in my database, and the other 51 work just fine,
> it's just one which is getting me into trouble...
> I'm desperate, can you help me, please?!? (picture a face of agony
> while reading the latter LOL ;-) )
> best regards,
> Alex
What do you have your timeout value set to in your code? This is either
a lock:timeout or a query timeout. Both can be captured in Profiler
using the Lock:Timeout and Attention events. What statement is running
just prior to the problem. Use the SQL:StmtStarting and RPC:Starting
events as well as SP:StmtStarting if this is occurring inside a stored
procedure.
David Gugick
Imceda Software
www.imceda.com
sql

I get "Timeout expired"

Hi, everyone! As many of guys around this newsgroup, I've been getting
"Timeout expired" using SQL Server 2000.
It raises that error message out of nowhere... first, I tought it was
because of too many users connected, but made some test with just ONE SINGLE
user connected to DataBase, and I'm still getting this message.
It acts randomly, as I can be working perfectly now and the next second, I
get the anoying message again.
I've even tried to remake the table's index, but it doesn't seem to work...
I'm not even able to erase the primary index in the first place...
I have 52 tables in my database, and the other 51 work just fine, it's just
one which is getting me into trouble...
I'm desperate, can you help me, please?!? (picture a face of agony while
reading the latter LOL ;-) )
best regards,
AlexAlexTirado wrote:
> Hi, everyone! As many of guys around this newsgroup, I've been getting
> "Timeout expired" using SQL Server 2000.
> It raises that error message out of nowhere... first, I tought it was
> because of too many users connected, but made some test with just ONE
> SINGLE user connected to DataBase, and I'm still getting this message.
> It acts randomly, as I can be working perfectly now and the next
> second, I get the anoying message again.
> I've even tried to remake the table's index, but it doesn't seem to
> work... I'm not even able to erase the primary index in the first
> place...
> I have 52 tables in my database, and the other 51 work just fine,
> it's just one which is getting me into trouble...
> I'm desperate, can you help me, please?!? (picture a face of agony
> while reading the latter LOL ;-) )
> best regards,
> Alex
What do you have your timeout value set to in your code? This is either
a lock:timeout or a query timeout. Both can be captured in Profiler
using the Lock:Timeout and Attention events. What statement is running
just prior to the problem. Use the SQL:StmtStarting and RPC:Starting
events as well as SP:StmtStarting if this is occurring inside a stored
procedure.
David Gugick
Imceda Software
www.imceda.com

I get "Timeout expired"

Hi, everyone! As many of guys around this newsgroup, I've been getting
"Timeout expired" using SQL Server 2000.
It raises that error message out of nowhere... first, I tought it was
because of too many users connected, but made some test with just ONE SINGLE
user connected to DataBase, and I'm still getting this message.
It acts randomly, as I can be working perfectly now and the next second, I
get the anoying message again.
I've even tried to remake the table's index, but it doesn't seem to work...
I'm not even able to erase the primary index in the first place...
I have 52 tables in my database, and the other 51 work just fine, it's just
one which is getting me into trouble...
I'm desperate, can you help me, please?!? (picture a face of agony while
reading the latter LOL ;-) )
best regards,
AlexAlexTirado wrote:
> Hi, everyone! As many of guys around this newsgroup, I've been getting
> "Timeout expired" using SQL Server 2000.
> It raises that error message out of nowhere... first, I tought it was
> because of too many users connected, but made some test with just ONE
> SINGLE user connected to DataBase, and I'm still getting this message.
> It acts randomly, as I can be working perfectly now and the next
> second, I get the anoying message again.
> I've even tried to remake the table's index, but it doesn't seem to
> work... I'm not even able to erase the primary index in the first
> place...
> I have 52 tables in my database, and the other 51 work just fine,
> it's just one which is getting me into trouble...
> I'm desperate, can you help me, please?!? (picture a face of agony
> while reading the latter LOL ;-) )
> best regards,
> Alex
What do you have your timeout value set to in your code? This is either
a lock:timeout or a query timeout. Both can be captured in Profiler
using the Lock:Timeout and Attention events. What statement is running
just prior to the problem. Use the SQL:StmtStarting and RPC:Starting
events as well as SP:StmtStarting if this is occurring inside a stored
procedure.
--
David Gugick
Imceda Software
www.imceda.com

I Cant "show sql query" - Crystal Report 8.5

Hey Guys, all fine ?

I created a report and using 3 tables( or more) with joins, group and etc. And this report calls a subreport. Well, the report is perfect, no problems! But, i can't see your sql definition(In crystal report, menu database,Show sql query) and, in Visual Basic i need to set a similar sql of report, only changing the where clause, by filter controls in VB.

In old times, i have used crystal report 8, and can show sql query.

In VB, how i can input a sql string in report, after execute it ?

How i can show sql query in CR?

Please, i need this help with urgency! The rope is in my neck! =(]

Thanks a lot!"Show SQL Query" option is very much present in crystal reports 8.5, if you don't have the option, then you try re-installing crystal reports.

I can't get this view faster

Hey guys I can't get this view to be faster. It is causing me serious problems. I am trying to get all this data gathered to return to the client but I can't make it any faster. My Low return time is 24 seconds and my High return time is 77 Seconds. But the it will take 24 Seconds even if no data is returned.

What can i do?

I have indexed every foreign key.

i have pictures of my execution path that you can look at at the following address

http://salessimplicity.dnsalias.com/ssnet/needhelp/needhelp.htm

If you need anything to help me let me know I am more than willing to help to get this thing going faster..

The Main view is below

Code Snippet

create VIEW dbo.vuFollowUpTriggerAll AS
SELECT
Client.ID,
SubdivFU.ID AS FollowUpSubdivID,
FollowUpDone.ID AS FollowUpID,
Case
When SubdivFU.DeptID is Not Null then Agent1.ID
Else Agent2.ID
END As SalesAgentID,
-- Agent2.ID AS SalesAgentID,
Subdiv.ID AS SubdivisionID,
Subdiv.MarketingName AS SubdivisionName,
Buyer.FirstName,
Buyer.LastName,
Case
When SubdivFU.DeptID is Not Null then Agent1.FirstName
Else Agent2.FirstName
END As SalesAgentFirst,
Case
When SubdivFU.DeptID is Not Null then Agent1.LastName
Else Agent2.LastName
END As SalesAgentLast,
-- Agent2.FirstName AS SalesAgentFirst,
-- Agent2.LastName AS SalesAgentLast,
FollowUpDone.DateItemEntered,
FollowUpDone.DateItemComplete AS DateComplete,
FollowUpDone.Custom,
--Removed ClientStatus.Status - didn't see it used in code.
SubdivFU.ContactDesc AS TaskDesc,
FollowUpDone.Removed,
--For Contingencies and Deposit Reminders, we subtract the days rather then add them.
DATEADD(Day, TE.Multiplier * ISNULL(SubdivFU.DaysFromTrigger, 0), ClientDate.EventDate) AS DueDate,
ClientDate.DepositID,
SubdivFU.ContactType AS TaskType,
SubdivFU.MasterReportID,
SubdivFU.EmailLetterID,
Buyer.Email,
Buyer.DoNotSendEmail,
Buyer.DoNotSendLetter,
SubdivFU.OverrideOptOut,
FollowUpDone.UserID,
Case
When SubdivFU.DeptID is Not Null then Agent1.FirstName
Else Agent2.FirstName
END As AssignedToFirstName,
Case
When SubdivFU.DeptID is Not Null then Agent1.LastName
Else Agent2.LastName
END As AssignedToLastName
FROM
dbo.tbSubdivision Subdiv
INNER JOIN dbo.tbSubdivisionFollowUp SubdivFU ON SubdivFU.SubdivisionID = Subdiv.ID
-- tbTrigger Event is the table that helps us put this whole spaghetti together
--it joins the Event type to the Date Type to the Multiplier.
INNER JOIN dbo.tbTriggerEvent TE ON TE.ID = SubdivFU.TriggerEvent
--tbDateType is what connects vuClient Dates the appropriate date field.
-- INNER JOIN dbo.tbDateType DT ON DT.ID = TE.DateTypeID
INNER JOIN dbo.tbClient Client ON Subdiv.ID = Client.SubdivisionID
AND ((SubdivFU.ClientRankID = Client.ClientRankID AND TE.UseRank = 1)
OR (TE.UseRank = 0))
--vuCLientDates replaces what we were going to use as tbClientDates but it is almost as fast to generate this on the fly
--as it is to generate it on the fly.
INNER JOIN dbo.vuClientDates ClientDate ON ClientDate.DateTypeID = TE.DateTypeID and Client.ID = ClientDate.ClientID
INNER JOIN dbo.tbMember Member ON Member.ClientID = Client.ID
AND Member.MemberTypeID = 1 --Buyer Type
INNER JOIN dbo.tbIndividual Buyer ON Member.IndividualID = Buyer.ID
--vuFollowUpTriggerGetAgent is a view we use to connect Clients to their Agents in each Department.
Left Outer Join vuFollowUpTriggerGetAgent GetAgent on GetAgent.DeptID = SubdivFU.DeptID and Client.ID = GetAgent.ClientID and GetAgent.SubdivisionID = Subdiv.ID
Left Outer JOIN dbo.tbUser Agent1 ON Agent1.ID = GetAgent.UserID
Inner JOIN dbo.tbUser Agent2 ON Agent2.ID = Client.UserID
LEFT OUTER JOIN dbo.tbFollowUp FollowUpDone ON FollowUpDone.SubdivFollowUpID = SubdivFU.ID
AND FollowUpDone.ClientID = Client.ID
AND isNull(FollowUpDone.DepositID, 0) = isNull(ClientDate.DepositID, 0)
WHERE (FollowUpDone.DateItemComplete Is Null)
AND (isnull(FollowUpDone.Removed,0) = 0)
AND (SubdivFU.Active = 1)
AND ((SubdivFU.TriggerEvent = 'R' AND DATEDIFF(DAY, ClientDate.EventDate, GETDATE()) < 0 )
OR (SubdivFU.TriggerEvent = 'O' AND (FLOOR(CAST(GETDATE() AS float)) >= DATEADD(Day, ISNULL(SubdivFU.DaysFromTrigger, 0), ClientDate.EventDate)) )
OR (SubdivFU.TriggerEvent <> 'R' and SubdivFU.TriggerEvent <> 'O')
)
AND (SubdivFU.OverrideOptOut = 1
OR ((SubdivFU.ContactType = 'O')
OR (SubdivFU.ContactType = 'L' AND Buyer.DoNotSendLetter = 0)
OR (SubdivFU.ContactType = 'E' AND Buyer.DoNotSendEmail = 0)
OR (SubdivFU.ContactType = 'C' AND Buyer.DoNotCall = 0)))
AND (((SubdivFU.DeptID IS Not Null) and (Agent1.ID is Not Null)) or (SubdivFU.DeptID is Null) )

It joins to a few other views defined below

Code Snippet

ALTER View dbo.vuFollowUpTriggerGetAgent AS
-- 5/14/07 DebP Added link to tbDept in order to exclude inactive departments.

SELECT SFU.DeptID, tbClient.ID AS ClientID,
ISNULL(tbClientDept.UserID, ISNULL(tbSubdivisionDept.UserID, Null)) AS UserID,
tbSubdivision.ID as SubdivisionID
FROM tbSubdivision
INNER JOIN dbo.tbClient ON tbClient.SubdivisionID = tbSubdivision.ID
INNER Join
(SELECT SubdivisionID, DeptID
FROM dbo.tbSubdivisionFollowUp
INNER JOIN dbo.tbDept ON dbo.tbSubdivisionFollowUp.DeptID = dbo.tbDept.ID
AND dbo.tbDept.Active <> 0
GROUP BY SubdivisionID, DeptID)
SFU ON SFU.SubdivisionID = tbSubdivision.ID
-- The following is no longer needed since we're doing an inner join to tbDept.
--AND SFU.DeptID is Not Null
LEFT OUTER JOIN dbo.tbSubdivisionDept ON tbSubdivisionDept.DeptID = SFU.DeptID
AND tbSubdivisionDept.SubdivisionID = tbSubdivision.ID
AND tbSubdivisionDept.UserID is not Null
LEFT OUTER JOIN dbo.tbClientDept ON tbClientDept.DeptID = SFU.DeptID
AND tbClient.ID = tbClientDept.ClientID
AND tbClientDept.UserID is not Null

and

Code Snippet

ALTER view dbo.vuClientDates as

SELECT tbClient.ID as ClientID,
Case
When tbClient.ClientStatusID = 4 then 1
When tbClient.ClientStatusID = 1 then 2
When tbClient.ClientStatusID = 2 then 4
when tbClient.ClientStatusID = 3 AND tbClient.CancellationDate is null Then 11
When tbClient.CancellationDate IS NOT NULL and tbClient.ClientStatusID = 3 Then 5
End as DateTypeID,

Case
When tbClient.ClientStatusID = 4 then VisitDate
When tbClient.ClientStatusID = 1 then ContractDate
When tbClient.ClientStatusID = 2 then ActualCloseDate
when tbClient.ClientStatusID = 3 AND tbClient.CancellationDate is null Then VisitDate
When tbClient.CancellationDate IS NOT NULL and tbClient.ClientStatusID = 3 Then CancellationDate
End
as EventDate,
Null as DepositID, Null as StageID, Null as EscrowStageID From tbClient

UNION ALL

SELECT tbClient.ID as ClientID, 3 as DateTypeID, ExpirationDate as EventDate, Null as DepositID, Null as StageID, Null as EscrowStageID
From tbClient
where (tbClient.Contingency = 1)
AND (tbClient.CancellationDate is null
OR tbClient.ClientStatusID in ( 1, 2, 4)
)

UNION ALL

SELECT tbClient.ID as ClientID, 6 as DateTypeID, DateDue as EventDate, Deposits.ID as DepositID, Null as StageID, Null as EscrowStageID
From tbClient
INNER JOIN dbo.tbDeposits Deposits ON Deposits.ClientID = tbClient.ID
AND Deposits.DateDue IS NOT NULL
AND Deposits.DatePaid IS NULL
Where (tbClient.CancellationDate is null
OR tbClient.ClientStatusID in( 1,2,4)
)

These are the indexes i have added for this

Code Snippet

CREATE INDEX [IX_tbIndividual] on [dbo].[tbIndividual] ([BuilderID]) WITH FILLFACTOR = 95
CREATE INDEX [IX_tbClient] on [dbo].[tbClient] ([ClientStatusID]) WITH FILLFACTOR = 95
CREATE INDEX [IX_tbClient_1] on [dbo].[tbClient] ([SubdivisionID]) WITH FILLFACTOR = 95
CREATE INDEX [IX_tbClient_2] on [dbo].[tbClient] ([UserID]) WITH FILLFACTOR = 95
CREATE INDEX [IX_tbMember] on [dbo].[tbMember] ([ClientID]) WITH FILLFACTOR = 95
CREATE INDEX [IX_tbMember_1] on [dbo].[tbMember] ([IndividualID]) WITH FILLFACTOR = 95
CREATE INDEX [IX_tbMember_2] on [dbo].[tbMember] ([MemberTypeID]) WITH FILLFACTOR = 95
CREATE INDEX [IX_tbDeposits] on [dbo].[tbDeposits] ([ClientID]) WITH FILLFACTOR = 95
CREATE INDEX [IX_tbSubdivisionFollowUp_2] on [dbo].[tbSubdivisionFollowUp] ([TriggerEvent]) WITH FILLFACTOR = 95
CREATE INDEX [IX_tbSubdivisionFollowUp] on [dbo].[tbSubdivisionFollowUp] ([SubdivisionID]) WITH FILLFACTOR = 95
CREATE INDEX [IX_tbSubdivisionFollowUp_1] on [dbo].[tbSubdivisionFollowUp] ([DeptID]) WITH FILLFACTOR = 95
CREATE INDEX [IX_tbSubdivisionFollowUp_3] on [dbo].[tbSubdivisionFollowUp] ([ContactType]) WITH FILLFACTOR = 95
CREATE INDEX [IX_tbFollowUp_2] on [dbo].[tbFollowUp] ([ClientID]) WITH FILLFACTOR = 95
CREATE INDEX [IX_tbFollowUp] on [dbo].[tbFollowUp] ([SubdivFollowUpID]) WITH FILLFACTOR = 95
CREATE INDEX [IX_tbFollowUp_1] on [dbo].[tbFollowUp] ([MasterReportID]) WITH FILLFACTOR = 95
CREATE INDEX [IX_tbFollowUp_3] on [dbo].[tbFollowUp] ([DepositID]) WITH FILLFACTOR = 95
CREATE INDEX [IX_tbSubdivisionDept] on [dbo].[tbSubdivisionDept] ([SubdivisionID]) WITH FILLFACTOR = 95
CREATE INDEX [IX_tbUserSubdivision] on [dbo].[tbUserSubdivision] ([UserID]) WITH FILLFACTOR = 95
CREATE INDEX [IX_tbUserSubdivision_1] on [dbo].[tbUserSubdivision] ([SubdivisionID]) WITH FILLFACTOR = 95
CREATE INDEX [IX_tbClientDept_1] on [dbo].[tbClientDept] ([DeptID]) WITH FILLFACTOR = 95
CREATE INDEX [IX_tbTriggerEvent] on [dbo].[tbTriggerEvent] ([DateTypeID]) WITH FILLFACTOR = 95
CREATE INDEX [IX_tbTriggerEvent_1] on [dbo].[tbTriggerEvent] ([TriggerEventDesc]) WITH FILLFACTOR = 95
CREATE INDEX [IX_tbRoomOptions] on [dbo].[tbRoomOptions] ([AvailablePlanOptionID]) WITH FILLFACTOR = 95
CREATE INDEX [IX_tbRoomOptions] on [dbo].[tbRoomOptions] ([RoomPlanID]) WITH FILLFACTOR = 95
CREATE INDEX [IX_Username] on [dbo].[tbUser] ([Username]) WITH FILLFACTOR = 95
CREATE INDEX [IX_Username] on [dbo].[tbUser] ([BuilderID]) WITH FILLFACTOR = 95
CREATE INDEX [IX_LoginID] on [dbo].[tbUser] ([LoginID]) WITH FILLFACTOR = 95

Hmm... Three table scans (at 25% of the execution plan each) seem to indicate you're missing appropriate indexes somewhere.|||Ok but where?
|||

You have a lot of joins in your query. Have you tried using temporary tables instead to store intermediate results and then join them ?

|||

Well, two of them are on the tbClient with CancelationDate and ClientStatusID, and one of those also looks at Contingency.

(Those are tables 2 and 3 with the 25% costs on the far right of the plan).

You're only indexing on ClientStatusID

You're also using a lot of functions in the filtering which will pop the plan back to table scans.

You may want to develop and "grow" this incrementally. Just do the first two tables and get them honed in, then add in the next table.

Get a good clean manageable baseline to grow from.

|||Also, to go along with Dale, you'll want to create indexes based on the composite key of the join. Don't just create an index for each column individually. Do as he says and "grow" the view. Each time you add something new, review the execution plan again to ensure nothing has snuck up on you.|||

These are all great ideas...

Thanks

|||How do i get rid of a hash match left?

I am Really confusing Please help me about a single query

Hi Guys,
This is my Problem.
A table contain following desing for handling different level of categories, bu it is dynamic

int_categoryid,
int_parent_categoryid,
int_categorylevel,
str_categoryname,
bit_active
thatall.
I want to list data from table as following order
categorry_parent11
category_child12
category_child13
category_child23
category_child22
categorry_parent21
..................
................................
.....................
like this..
ie we can insert parent category and sub category to n level dynamically without adding a new table
please mail me for another clarification...

please help me
regards
Abdul
For the example data you provided, can you tell us what data appears in the int_categorylevel column for each row?
categorry_parent11
category_child12
category_child13
category_child23
category_child22
categorry_parent21
|||

Thanks, tmorton
yes I am also interesting in int_categorylevel, and it gives the information about category level, 0,1,2 etc...
for Example Suppose we can take hotel menu categories...
Lunch Catgeroy level is 0
Veg Catgeroy level is 1
Average Catgeroy level is 2
Expensive Catgeroy level is 2
Non Veg Catgeroy level is 1
Chicken Catgeroy level is 2
Mutton Catgeroy level is 2
thanks ur response...