Help on this

Hello:

I am currently running a query and it is taking like 6 hours to run.

what I do is:

I loop through user's table (20000 users) using a cursor, for each user I call a stored procedure "EXEC ...", then in that called stored procedure, I have an a set of 6 IF statements, and in each if statement I have a call for a simple stored procedure,.

Can anyone tell me, how can i optimize that query?

thanks a lot

Don't use a cursor? We'd need to know more specifics about what it's actually doing to determine if you can do this as a set operation instead of row-by-row (cursor).

Marcie

|||

Hello Marcie:

Thank you for your reply.

I sent you an email from your Blog, it has some codes.

thanks a lot,

|||

Hi SNT2,

I don't have access to that email account during the day, sorry. Also, if you'll post the code here (or at least an overview of what it does), other people will be able to help you with your problem too.

Take care,

Marcie

|||

Hello, I will list the Stored Procedures here: As I said, there is a main procedure, which loops using a cursor through all the records, and upon getting each account I call a method, the first method is the general one:

CREATE PROCEDURE ActiveUserAnalysis_AnalyzeUsers( @.AnalysisPeriod int = 30, -- the number of days over which the refills are checked @.CustGrpId int = 101, -- the group that we want to analyze @.AnalysisId int out)ASBEGIN SET NOCOUNT ON --create the analysis record INSERT INTO ActiveUserAnalysis ( AnalysisTime, AnalysisPeriod, CustGrpId) VALUES ( getdate(), @.AnalysisPeriod, 101) SET @.AnalysisId = @.@.identityDECLARE @.UserCount int SET @.UserCount = 0 --loop over all active users and analyze them DECLARE cActiveCustomers CURSORREAD_ONLYFOR select customerid, username from activecustomersview where custgrpid=@.CustGrpIdDECLARE @.customerid intDECLARE @.username varchar(30)OPEN cActiveCustomersFETCH NEXT FROM cActiveCustomers INTO @.customerid, @.usernameWHILE (@.@.fetch_status <> -1)BEGINIF (@.@.fetch_status <> -2)BEGIN SET @.UserCount = @.UserCount + 1 EXEC ActiveUserAnalysis_AnalyzeActiveUser @.AnalysisId, @.AnalysisPeriod, @.CustomerId, @.UserName   ENDFETCH NEXT FROM cActiveCustomers INTO @.customerid, @.usernameENDCLOSE cActiveCustomersDEALLOCATE cActiveCustomers UPDATE ActiveUserAnalysis SET ActiveUserCount = @.UserCount WHERE AnalysisId = @.AnalysisId SELECT Classification, count(*) FROM ActiveUserAnalysis_Results WHERE AnalysisId = @.AnalysisId GROUP BY ClassificationENDGO

Now the method we are calling above for each user is:

CREATE procedure ActiveUserAnalysis_AnalyzeActiveUser( @.AnalysisId int, @.AnalysisPeriod int, @.customerid int, @.username varchar(30))asbegin DECLARE @.TokenType varchar(4) DECLARE @.Expires datetime DECLARE @.Value int DECLARE @.LastActivity datetime DECLARE @.RefillType varchar(30) DECLARE @.LastRefillDate datetime DECLARE @.RefillCount int DECLARE @.TBSUserName varchar(30) --First collect info EXEC ActiveUserAnalysis_GetUsageToken @.customerid, @.TokenType out, @.Value out, @.Expires out SELECT TOP 1 @.LastActivity = LastActivity FROM PPPAccount WHERE CustomerId = @.CustomerId --Classify user -- 1- Refill EXEC ActiveUserAnalysis_CheckUserLastRefills @.CustomerId, @.AnalysisPeriod, @.RefillType out, @.LastRefillDate out if @.@.rowcount > 0 BEGIN INSERT INTO ActiveUserAnalysis_Results (AnalysisId, customerid, classification, lastactivity, refilltype, lastrefilldate, tokentype, value, expires) VALUES (@.AnalysisId, @.customerid, 'Refill', @.LastActivity, @.RefillType, @.LastRefillDate, @.TokenType, @.Value, @.Expires) RETURN END -- 2- NoLogin: He hasn't logged in at all during period If datediff (d, @.LastActivity, getdate()) > @.AnalysisPeriod BEGIN INSERT INTO ActiveUserAnalysis_Results (AnalysisId, customerid, classification, lastactivity, refilltype, lastrefilldate, tokentype, value, expires) VALUES (@.AnalysisId, @.customerid, 'NoLogin', @.LastActivity, @.RefillType, @.LastRefillDate, @.TokenType, @.Value, @.Expires) RETURN END --3 Other. If we reached here, then the user does not fit with any of the above categories INSERT INTO ActiveUserAnalysis_Results (AnalysisId, customerid, classification, lastactivity, refilltype, lastrefilldate, tokentype, value, expires) VALUES (@.AnalysisId, @.customerid, 'Other', @.LastActivity, @.RefillType, @.LastRefillDate, @.TokenType, @.Value, @.Expires)ENDGO

I didn't include all cases, but i listed only 3 of them.

Then in every case I am calling a procedure too.

Can that be optimzed?

Thanks Marcie

|||

Yes, but it will take some significant work. You haven't included the stored procedures/views that this depends on, so I can't really give you code that will work 100%.

Start off by creating a view for many of the stored procedures that this thing depends on... Can you post the code for say...

ActiveUserAnalysis_CheckUserLastRefills? Hopefully that doesn't call any more stored procedures/views.

|||

Hello, thank you for helping me.

The SP is as follows:

CREATE procedure ActiveUserAnalysis_CheckUserLastRefills(@.CustomerId int,@.AnalysisPeriod int, -- in days,@.Refilltype char(5) out,@.LastRefillDate datetime out)asselect top 1 @.RefillType=c.ISKCatCode, @.LastRefillDate = datepurchasedfrom isk i inner join iskcategory c on i.iskcatid = c.iskcatidwhere customerid = @.customerid and datediff(d, datepurchased, getdate()) <= @.AnalysisPeriod and i.CreatedNewCustomer = 0order by datepurchased descGO

Thank you.|||

Hello Marcie:

Can you help in that please?

thanks

|||

SomeNewTricks2 wrote:

I am currently running a query and it is taking like 6 hours to run.

what I do is:

I loop through user's table (20000 users) using a cursor, foreach user I call a stored procedure "EXEC ...", then in that calledstored procedure, I have an a set of 6 IF statements, and in each ifstatement I have a call for a simple stored procedure,.

Can anyone tell me, how can i optimize that query?

That's an awful big haystack. Have you pinpointed thebottleneck? I'd suggest running the query from Query Analyzerwith Show Execution Plan turned on. This should give you an ideaof the slow parts.

Also, this will not likely make an appreciable difference in yourexecution time, but instead of a cursor I'd use a #temp table (see anexample of this approach in this post:http://forums.asp.net/511669/ShowPost.aspx).|||

SomeNewTricks2 wrote:

Hello Marcie:

Can you help in that please?

thanks

My gut feel is still that you could probably do all of this with one (large) Update statement, rather than looping through thousands of items and doing them one at a time. If that gets too complex (or is not possible), using a #temp table as Terri suggests will make this job easier, and much faster than the cursor approach.

Marcie

|||

Thank you all.

Do you think using a loop over a temp table, is better than cursor?

What about the "if" statements, is using them ok?

thanks

|||

SomeNewTricks2 wrote:

Do you think using a loop over a temp table, is better than cursor?

Yes I do, that's why I suggested it. Here's a little background information:SQL Server Temp Table Performance.

SomeNewTricks2 wrote:

What about the "if" statements, is using them ok?

I think using them is OK, but as far as I understand it, when SQLServer compiles the stored procedure it will use the execution planbased on whichever parameters were passed to it the first timethrough. So the execution plan may not be optimized for otherparameters. But I have also read that SQL Server often recompilesstored procedures, so I don't know how much of an issue that truly is.

Again, I urge you to identifiy your bottleneck(s). You havepresented a huge haystack to look through, and it could be that onesmall part needing optimization is causing all of the performanceissues. We don't really have enough information to be able tohelp you.

|||

Sorry, been away for a few days... I didn't see that you were stuffing some of values from ActiveUserAnalysis_GetUsageToken in your inserts, could you post that code? As long as it's pretty simple, I'll have some working code for ya that should be magnitudes faster than what you are currently doing by using set-based rather than cursor-if based logic.

Cursoring over a temp table won't be faster than cursoring over a query, however, you will have less transaction collisions (possibly). The article the above poster referenced, is saying temp tables are faster if by using one you can avoid a cursor and use set-based logic, which is what you'll probably have to do, but possibly not, but it doesn't eliminate the need to move away from the cursor-if logic.

|||

First of all, thank you Terri for your help.

Here is the code:

create procedure ActiveUserAnalysis_GetUsageToken
(
@.customerid int,
@.TokenType varchar(4) out,
@.Value int out,
@.Expires datetime out
)
as
SELECT TOP 1 /* get only first (highest priority) token */
@.TokenType=t.TokenTypeId, @.Value=u.Value, @.Expires=u.Expires
FROM UsageToken u
INNER JOIN UsageTokenTemplate t
ON u.UsageTokenTemplId = t.UsageTokenTemplId
INNER JOIN TokenType y
ON t.TokenTypeId = y.TokenTypeId
WHERE u.customerid = @.customerid
AND u.status<100 /* could be frozen */
AND (u.Expires is null or u.Expires > getdate()) /*did not expire*/
AND (u.Value is null or u.Value>0)
ORDER BY y.Priority, u.Created

Thanks a lot.

|||

Ok, let's start here. First step is to unroll this stored procedure into a view so that we can reuse it later. Heh, it would figure that you would have this buried somewhere, this is definately the hardest part of unravelling your cursor logic, so here goes. I am going to make the assumption that a single user can not have two (or more) tokens created with the same priority created at the same "Created". If this assumption is not correct, please let me know what combination of fields would be required to ALWAYS return no more than a single record.

Coding by hand here, so might be a syntax error somewhere (sorry).

CREATE VIEW vw_CustomerUsageToken

AS

SELECT t1.customerid,t1.TokenTypeId,t1.Value,t1.Expires
FROM (SELECT u.customerid,t.TokenTypeId,u.Value,u.Expires,y.priority,u.created FROM UsageToken u
INNER JOIN UsageTokenTemplate t
ON u.UsageTokenTemplId = t.UsageTokenTemplId
INNER JOIN TokenType y
ON t.TokenTypeId = y.TokenTypeId
WHERE u.status<100 /* could be frozen */
AND (u.Expires is null or u.Expires > getdate()) /*did not expire*/
AND (u.Value is null or u.Value>0)) t1

LEFT JOIN (SELECT u.customerid,t.TokenTypeId,u.Value,u.Expires,y.priority,u.created FROM UsageToken u
INNER JOIN UsageTokenTemplate t
ON u.UsageTokenTemplId = t.UsageTokenTemplId
INNER JOIN TokenType y
ON t.TokenTypeId = y.TokenTypeId
WHERE u.status<100 /* could be frozen */
AND (u.Expires is null or u.Expires > getdate()) /*did not expire*/
AND (u.Value is null or u.Value>0)) t2 on t1.customerid=t2.customerid AND (t2.priority<t1.priority OR (t2.priority=t1.priority AND t2.created<t1.created))

WHERE t2.customerid IS NULLsql

help on SubscriptionProperties.aspx page

I want to pop up a subscription page for user to subscribe a report from my customized web page. I did a little research on this page and I can generate a url including the report name/path, and some parameters else, then open the url in a browser window. sometimes it works fine. but sometimes it did not.

I am wondering if there are some regulations or information about the url variables being passed over to the page of SubscriptionProperties.aspx. thanks a lot.

Did you have luck with this? I'm about to head down a similar route and thought I'd check before getting too far along.

Thanks,

Derek

|||

I changed the original design. instead of using subscriptionproperties.aspx page, I made my own subscription page with reportingservice web service. which is not that complicated.

before doing this, I hacked the dll of that page and try to find something helpful, no luck. that's the reason I have to make my own subscription page.

help on SubscriptionProperties.aspx page

I want to pop up a subscription page for user to subscribe a report from my customized web page. I did a little research on this page and I can generate a url including the report name/path, and some parameters else, then open the url in a browser window. sometimes it works fine. but sometimes it did not.

I am wondering if there are some regulations or information about the url variables being passed over to the page of SubscriptionProperties.aspx. thanks a lot.

Did you have luck with this? I'm about to head down a similar route and thought I'd check before getting too far along.

Thanks,

Derek

|||

I changed the original design. instead of using subscriptionproperties.aspx page, I made my own subscription page with reportingservice web service. which is not that complicated.

before doing this, I hacked the dll of that page and try to find something helpful, no luck. that's the reason I have to make my own subscription page.

Help on Security of reports acccess

Dear all,
If I need to give acces to a particular folder and report type based on
groups of user's where this can be configured ?
For instance typically a user belonging to group MAINTENANCE will be able to
use reports only from maintenance folder and groups of QUALITY reports from
quality folder.
The yshould only use the report and not have access to datasource even
seeing them
Do I use the adminstrativ part of report server or should I create
authorisation and credential rights inside the Web.Config file of my report
server '
thnaks for help
regards
sergeIf you are using Report Manager to deliver the reports the security can be
configured right from there. Browse to the Report Manager web page
(http://myserver/reports). If your account has admin privileges then you
can edit the security, found under Properties for each folder. I am using
this to deliver reports for Accounting, Warehouse, and Transportation from
the same server, and each group sees only the reports I want them to see.
They also don't have access to the data source.
Just make sure that all groups have access to the home folder of Report
Manager, otherwise they won't get too far.
-Greg

help on query

I need to select all messages for user X from all users, except those who user X has blocked in tblBlockList.
I have the following query, but it returns a row for each time UserCodeBlocked is not equal to UserCodeSender...

What I need is just to select all messages for a user EXCEPT when they are from a user in the blocklist...
I have tried the keyworddistinct but that ofcourse doesnt work since Row_number makes every row unique...
tblMessages contains the actual message
tblUsersandmessages relates users and messages

select ROW_NUMBER() OVER (ORDER BY a.SentDateTime DESC) as RowNum,a.MessageID,a.UserCodeSender,a.MessageTitle,a.SentDateTime
FROM tblMessages a
INNER JOIN tblUsersAndMessages b ON (a.MessageID=b.MessageID)
INNER JOIN tblUserData c ON (a.UserCodeSender=c.UserCode)
RIGHT JOIN tblBlockList bl ON bl.UserCodeBlocked<>a.UserCodeSender
WHERE b.UserCode=5

did you try a left join instead of a right join? You want the data for the message, not for the block list.

select ROW_NUMBER()OVER (ORDER BY a.SentDateTimeDESC)as RowNum,a.MessageID,a.UserCodeSender,a.MessageTitle,a.SentDateTimeFROM tblMessages aINNERJOIN tblUsersAndMessages bON (a.MessageID=b.MessageID)INNERJOIN tblUserData cON (a.UserCodeSender=c.UserCode)LEFTOUTER JOIN tblBlockList blON bl.UserCodeBlocked<>a.UserCodeSenderWHERE b.UserCode=5

If this doesn't help, please post the table structures and/or relationship diagram

|||tblBlockList
UserCodeBlocker int 'person who has blocked someone
UserCodeBlocked int 'person being blocked

tblMessages
UserCodeSender int
MessageID int
MessageTitle
...

tblUsersAndMessages
UserCode int
MessageID int
...sql

Help on creating a user function.

When I declare a cursor,I use a variable to replace the sql statement:
DECLARE rs CURSOR LOCAL FAST_FORWARD FOR
@.sqlPlan
But it is not true.Who can correct for me.

Another question is :
How to execute a sql statement state by a variable "@.sqlPlan" and
insert the result to a table "@.FeatRequestStatus"?

I am a new hand of sql programming.Thank you very much for your helpWhen I use:
insert @.FeatRequestStatus
exec @.sqlPlan
It says "execute can be used as a source when insert into a table viarable"
"Kevin" <hua@.lucent.com> wrote in message
news:dc2mgs$16f@.netnews.proxy.lucent.com...
> When I declare a cursor,I use a variable to replace the sql statement:
> DECLARE rs CURSOR LOCAL FAST_FORWARD FOR
> @.sqlPlan
> But it is not true.Who can correct for me.
> Another question is :
> How to execute a sql statement state by a variable "@.sqlPlan" and
> insert the result to a table "@.FeatRequestStatus"?
> I am a new hand of sql programming.Thank you very much for your help|||Kevin (hua@.lucent.com) writes:
> When I declare a cursor,I use a variable to replace the sql statement:
> DECLARE rs CURSOR LOCAL FAST_FORWARD FOR
> @.sqlPlan
> But it is not true.Who can correct for me.

You need to say:

EXEC ('DECLARE rs CURSOR GLOBAL FAST_FORWARD ' + @.sqlPlan)

Note that I changed LOCAL to GLOBAL here. This is necessary, since the
cursor is accessed from a different scope than it is created.

> Another question is :
> How to execute a sql statement state by a variable "@.sqlPlan" and
> insert the result to a table "@.FeatRequestStatus"?

INSERT EXEC does not work with table variables, as you have experienced.
Use a temp table instead.

And if @.sqlPlan is an SQL statement, the syntax is

EXEC(@.sqlPlan)

The syntax you had on your other post:

EXEC @.sqlPlan

means "execute the stored procedure of which the name is in @.sqlPlan".

> I am a new hand of sql programming.Thank you very much for your help

In such case, I should maybe point out, that cursors is something
to be used sparingly. There are situations where cursors can be
motivated, but they often come with a price of severly reduced
performance. Work set-based if you can.

Dynamic SQL is not really anything for beginners - it's definitely an
advanced feature. Dynamic SQL makes things a lot more complex, and
avoid if you can. I have a longer article on dynamic SQL on my web
site that you could find useful:
http://www.sommarskog.se/dynamic_sql.html

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

Help needed! DBCC CHECKDB - consistency errors

Hello All,
After restoring the SQL Server 2000 user database backup into one of the SQL
Server 2000 instances I get database consistency errors. I restored this
database a couple of times and every time I get the errors; however they are
not consistent - different types of errors, different tables and indexes get
affected. I restored the same database backup into a different server and
ran dbcc checkdb over it - no errors occurred. Something causes corruption
in the database on one particular server.
Here are the errors:
DBCC results for 'tTREE_COMPONENTS'.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 1467308437, index ID 0: Page (1:251481) could not be processed.
See other errors for details.
Server: Msg 8944, Level 16, State 1, Line 1
Table error: Object ID 1467308437, index ID 0, page (1:251481), row 44. Test
(ColumnOffsets <= (nextRec - pRec)) failed. Values are 87 and 57.
There are 663 rows in 16 pages for object 'tTREE_COMPONENTS'.
DBCC results for 'tACCUMULATORS'.
There are 10234724 rows in 97149 pages for object 'tACCUMULATORS'.
CHECKDB found 0 allocation errors and 2 consistency errors in table
'tACCUMULATORS' (object ID 1467308437).
DBCC results for 'WATSTARTDATETP'.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 1905493917, index ID 4: Page (3:224866) could not be processed.
See other errors for details.
Server: Msg 8941, Level 16, State 1, Line 1
Table error: Object ID 1905493917, index ID 4, page (3:224866). Test (sorted
[i].offset >= PAGEHEADSIZE) failed. Slot 159, offset 0x1 is invalid.
Server: Msg 8942, Level 16, State 1, Line 1
Table error: Object ID 1905493917, index ID 4, page (3:224866). Test
(sorted[i].offset >= max) failed. Slot 0, offset 0x9f overlaps with the prior
row.
There are 4 rows in 1 pages for object 'WATSTARTDATETP'.
DBCC results for 'ACCRUALTRAN'.
There are 5919148 rows in 150538 pages for object 'ACCRUALTRAN'.
CHECKDB found 0 allocation errors and 3 consistency errors in table
'ACCRUALTRAN' (object ID 1905493917).
Could it be that our IO subsystem is causing corruption? I am thinking
about running SQLIOSim and SQLIOStress..
If you could please take a look at the errors and give me your thoughts it
would be greatly appreciated.
Thank you in advance,
JuliaJulia,
My only suspicion, after reading your description, is that you are probably
running into a bad disk or array. And, if so, you need to determine if that
is true and fix it right now.
RLF
"DBCC CHECKDB - consistency errors"
<DBCCCHECKDBconsistencyerrors@.discussions.microsoft.com> wrote in message
news:94E38ACE-9F29-4B76-8B3A-2CF1467AC9B2@.microsoft.com...
> Hello All,
> After restoring the SQL Server 2000 user database backup into one of the
> SQL
> Server 2000 instances I get database consistency errors. I restored this
> database a couple of times and every time I get the errors; however they
> are
> not consistent - different types of errors, different tables and indexes
> get
> affected. I restored the same database backup into a different server and
> ran dbcc checkdb over it - no errors occurred. Something causes
> corruption
> in the database on one particular server.
> Here are the errors:
> DBCC results for 'tTREE_COMPONENTS'.
> Server: Msg 8928, Level 16, State 1, Line 1
> Object ID 1467308437, index ID 0: Page (1:251481) could not be processed.
> See other errors for details.
> Server: Msg 8944, Level 16, State 1, Line 1
> Table error: Object ID 1467308437, index ID 0, page (1:251481), row 44.
> Test
> (ColumnOffsets <= (nextRec - pRec)) failed. Values are 87 and 57.
> There are 663 rows in 16 pages for object 'tTREE_COMPONENTS'.
> DBCC results for 'tACCUMULATORS'.
> There are 10234724 rows in 97149 pages for object 'tACCUMULATORS'.
> CHECKDB found 0 allocation errors and 2 consistency errors in table
> 'tACCUMULATORS' (object ID 1467308437).
> DBCC results for 'WATSTARTDATETP'.
> Server: Msg 8928, Level 16, State 1, Line 1
> Object ID 1905493917, index ID 4: Page (3:224866) could not be processed.
> See other errors for details.
> Server: Msg 8941, Level 16, State 1, Line 1
> Table error: Object ID 1905493917, index ID 4, page (3:224866). Test
> (sorted
> [i].offset >= PAGEHEADSIZE) failed. Slot 159, offset 0x1 is invalid.
> Server: Msg 8942, Level 16, State 1, Line 1
> Table error: Object ID 1905493917, index ID 4, page (3:224866). Test
> (sorted[i].offset >= max) failed. Slot 0, offset 0x9f overlaps with the
> prior
> row.
> There are 4 rows in 1 pages for object 'WATSTARTDATETP'.
> DBCC results for 'ACCRUALTRAN'.
> There are 5919148 rows in 150538 pages for object 'ACCRUALTRAN'.
> CHECKDB found 0 allocation errors and 3 consistency errors in table
> 'ACCRUALTRAN' (object ID 1905493917).
> Could it be that our IO subsystem is causing corruption? I am thinking
> about running SQLIOSim and SQLIOStress..
> If you could please take a look at the errors and give me your thoughts it
> would be greatly appreciated.
> Thank you in advance,
> Julia
>
>|||Russell, thank you so much for such a quick response. In my company in order
to make the server administrators to run Dell hardware diagnostics I
basically need to prove that itâ's a bad disk issue, but there are no errors
in the Event Viewer log and I donâ't think we have the smoking gun in SQL
Server 2000 that would point to the cause of the consistency error?
"Russell Fields" wrote:
> Julia,
> My only suspicion, after reading your description, is that you are probably
> running into a bad disk or array. And, if so, you need to determine if that
> is true and fix it right now.
> RLF
> "DBCC CHECKDB - consistency errors"
> <DBCCCHECKDBconsistencyerrors@.discussions.microsoft.com> wrote in message
> news:94E38ACE-9F29-4B76-8B3A-2CF1467AC9B2@.microsoft.com...
> > Hello All,
> >
> > After restoring the SQL Server 2000 user database backup into one of the
> > SQL
> > Server 2000 instances I get database consistency errors. I restored this
> > database a couple of times and every time I get the errors; however they
> > are
> > not consistent - different types of errors, different tables and indexes
> > get
> > affected. I restored the same database backup into a different server and
> > ran dbcc checkdb over it - no errors occurred. Something causes
> > corruption
> > in the database on one particular server.
> >
> > Here are the errors:
> >
> > DBCC results for 'tTREE_COMPONENTS'.
> > Server: Msg 8928, Level 16, State 1, Line 1
> > Object ID 1467308437, index ID 0: Page (1:251481) could not be processed.
> > See other errors for details.
> > Server: Msg 8944, Level 16, State 1, Line 1
> > Table error: Object ID 1467308437, index ID 0, page (1:251481), row 44.
> > Test
> > (ColumnOffsets <= (nextRec - pRec)) failed. Values are 87 and 57.
> > There are 663 rows in 16 pages for object 'tTREE_COMPONENTS'.
> >
> > DBCC results for 'tACCUMULATORS'.
> > There are 10234724 rows in 97149 pages for object 'tACCUMULATORS'.
> > CHECKDB found 0 allocation errors and 2 consistency errors in table
> > 'tACCUMULATORS' (object ID 1467308437).
> >
> > DBCC results for 'WATSTARTDATETP'.
> > Server: Msg 8928, Level 16, State 1, Line 1
> > Object ID 1905493917, index ID 4: Page (3:224866) could not be processed.
> > See other errors for details.
> >
> > Server: Msg 8941, Level 16, State 1, Line 1
> > Table error: Object ID 1905493917, index ID 4, page (3:224866). Test
> > (sorted
> > [i].offset >= PAGEHEADSIZE) failed. Slot 159, offset 0x1 is invalid.
> >
> > Server: Msg 8942, Level 16, State 1, Line 1
> > Table error: Object ID 1905493917, index ID 4, page (3:224866). Test
> > (sorted[i].offset >= max) failed. Slot 0, offset 0x9f overlaps with the
> > prior
> > row.
> > There are 4 rows in 1 pages for object 'WATSTARTDATETP'.
> >
> > DBCC results for 'ACCRUALTRAN'.
> > There are 5919148 rows in 150538 pages for object 'ACCRUALTRAN'.
> > CHECKDB found 0 allocation errors and 3 consistency errors in table
> > 'ACCRUALTRAN' (object ID 1905493917).
> >
> > Could it be that our IO subsystem is causing corruption? I am thinking
> > about running SQLIOSim and SQLIOStress..
> >
> > If you could please take a look at the errors and give me your thoughts it
> > would be greatly appreciated.
> >
> > Thank you in advance,
> >
> > Julia
> >
> >
> >
>
>|||Julia,
Last time I had a similar problem, I finally proved it by running:
CHKDSK D:
It spat out errors that none of the monitoring tools had found. In my case,
it was not the disk, but the RAID array that had gone bad. There were no
Event log errors, no (for us) HP / Compaq warning messages, etc. But the
array was still bad.
Of course, I could be wrong, but this is my best guess based on the
symptoms.
RLF
(Going home now, so I cannot follow up any more tonight.)
"DBCC CHECKDB - consistency errors"
<DBCCCHECKDBconsistencyerrors@.discussions.microsoft.com> wrote in message
news:064B60C9-080E-4B5D-9657-23FDBD6B0C1A@.microsoft.com...
> Russell, thank you so much for such a quick response. In my company in
> order
> to make the server administrators to run Dell hardware diagnostics I
> basically need to prove that it's a bad disk issue, but there are no
> errors
> in the Event Viewer log and I don't think we have the smoking gun in SQL
> Server 2000 that would point to the cause of the consistency error?
> "Russell Fields" wrote:
>> Julia,
>> My only suspicion, after reading your description, is that you are
>> probably
>> running into a bad disk or array. And, if so, you need to determine if
>> that
>> is true and fix it right now.
>> RLF
>> "DBCC CHECKDB - consistency errors"
>> <DBCCCHECKDBconsistencyerrors@.discussions.microsoft.com> wrote in message
>> news:94E38ACE-9F29-4B76-8B3A-2CF1467AC9B2@.microsoft.com...
>> > Hello All,
>> >
>> > After restoring the SQL Server 2000 user database backup into one of
>> > the
>> > SQL
>> > Server 2000 instances I get database consistency errors. I restored
>> > this
>> > database a couple of times and every time I get the errors; however
>> > they
>> > are
>> > not consistent - different types of errors, different tables and
>> > indexes
>> > get
>> > affected. I restored the same database backup into a different server
>> > and
>> > ran dbcc checkdb over it - no errors occurred. Something causes
>> > corruption
>> > in the database on one particular server.
>> >
>> > Here are the errors:
>> >
>> > DBCC results for 'tTREE_COMPONENTS'.
>> > Server: Msg 8928, Level 16, State 1, Line 1
>> > Object ID 1467308437, index ID 0: Page (1:251481) could not be
>> > processed.
>> > See other errors for details.
>> > Server: Msg 8944, Level 16, State 1, Line 1
>> > Table error: Object ID 1467308437, index ID 0, page (1:251481), row 44.
>> > Test
>> > (ColumnOffsets <= (nextRec - pRec)) failed. Values are 87 and 57.
>> > There are 663 rows in 16 pages for object 'tTREE_COMPONENTS'.
>> >
>> > DBCC results for 'tACCUMULATORS'.
>> > There are 10234724 rows in 97149 pages for object 'tACCUMULATORS'.
>> > CHECKDB found 0 allocation errors and 2 consistency errors in table
>> > 'tACCUMULATORS' (object ID 1467308437).
>> >
>> > DBCC results for 'WATSTARTDATETP'.
>> > Server: Msg 8928, Level 16, State 1, Line 1
>> > Object ID 1905493917, index ID 4: Page (3:224866) could not be
>> > processed.
>> > See other errors for details.
>> >
>> > Server: Msg 8941, Level 16, State 1, Line 1
>> > Table error: Object ID 1905493917, index ID 4, page (3:224866). Test
>> > (sorted
>> > [i].offset >= PAGEHEADSIZE) failed. Slot 159, offset 0x1 is invalid.
>> >
>> > Server: Msg 8942, Level 16, State 1, Line 1
>> > Table error: Object ID 1905493917, index ID 4, page (3:224866). Test
>> > (sorted[i].offset >= max) failed. Slot 0, offset 0x9f overlaps with the
>> > prior
>> > row.
>> > There are 4 rows in 1 pages for object 'WATSTARTDATETP'.
>> >
>> > DBCC results for 'ACCRUALTRAN'.
>> > There are 5919148 rows in 150538 pages for object 'ACCRUALTRAN'.
>> > CHECKDB found 0 allocation errors and 3 consistency errors in table
>> > 'ACCRUALTRAN' (object ID 1905493917).
>> >
>> > Could it be that our IO subsystem is causing corruption? I am thinking
>> > about running SQLIOSim and SQLIOStress..
>> >
>> > If you could please take a look at the errors and give me your thoughts
>> > it
>> > would be greatly appreciated.
>> >
>> > Thank you in advance,
>> >
>> > Julia
>> >
>> >
>> >
>>|||> I restored the same database backup into a different server and
> ran dbcc checkdb over it - no errors occurred. Something causes
> corruption
> in the database on one particular server.
I agree with Russell's analysis. I think the facts that the corruption only
occurs on one server and manifests itself in different ways ought be enough
proof for the server admins to run hardware diagnostics.
--
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"DBCC CHECKDB - consistency errors"
<DBCCCHECKDBconsistencyerrors@.discussions.microsoft.com> wrote in message
news:064B60C9-080E-4B5D-9657-23FDBD6B0C1A@.microsoft.com...
> Russell, thank you so much for such a quick response. In my company in
> order
> to make the server administrators to run Dell hardware diagnostics I
> basically need to prove that itâ's a bad disk issue, but there are no
> errors
> in the Event Viewer log and I donâ't think we have the smoking gun in SQL
> Server 2000 that would point to the cause of the consistency error?
> "Russell Fields" wrote:
>> Julia,
>> My only suspicion, after reading your description, is that you are
>> probably
>> running into a bad disk or array. And, if so, you need to determine if
>> that
>> is true and fix it right now.
>> RLF
>> "DBCC CHECKDB - consistency errors"
>> <DBCCCHECKDBconsistencyerrors@.discussions.microsoft.com> wrote in message
>> news:94E38ACE-9F29-4B76-8B3A-2CF1467AC9B2@.microsoft.com...
>> > Hello All,
>> >
>> > After restoring the SQL Server 2000 user database backup into one of
>> > the
>> > SQL
>> > Server 2000 instances I get database consistency errors. I restored
>> > this
>> > database a couple of times and every time I get the errors; however
>> > they
>> > are
>> > not consistent - different types of errors, different tables and
>> > indexes
>> > get
>> > affected. I restored the same database backup into a different server
>> > and
>> > ran dbcc checkdb over it - no errors occurred. Something causes
>> > corruption
>> > in the database on one particular server.
>> >
>> > Here are the errors:
>> >
>> > DBCC results for 'tTREE_COMPONENTS'.
>> > Server: Msg 8928, Level 16, State 1, Line 1
>> > Object ID 1467308437, index ID 0: Page (1:251481) could not be
>> > processed.
>> > See other errors for details.
>> > Server: Msg 8944, Level 16, State 1, Line 1
>> > Table error: Object ID 1467308437, index ID 0, page (1:251481), row 44.
>> > Test
>> > (ColumnOffsets <= (nextRec - pRec)) failed. Values are 87 and 57.
>> > There are 663 rows in 16 pages for object 'tTREE_COMPONENTS'.
>> >
>> > DBCC results for 'tACCUMULATORS'.
>> > There are 10234724 rows in 97149 pages for object 'tACCUMULATORS'.
>> > CHECKDB found 0 allocation errors and 2 consistency errors in table
>> > 'tACCUMULATORS' (object ID 1467308437).
>> >
>> > DBCC results for 'WATSTARTDATETP'.
>> > Server: Msg 8928, Level 16, State 1, Line 1
>> > Object ID 1905493917, index ID 4: Page (3:224866) could not be
>> > processed.
>> > See other errors for details.
>> >
>> > Server: Msg 8941, Level 16, State 1, Line 1
>> > Table error: Object ID 1905493917, index ID 4, page (3:224866). Test
>> > (sorted
>> > [i].offset >= PAGEHEADSIZE) failed. Slot 159, offset 0x1 is invalid.
>> >
>> > Server: Msg 8942, Level 16, State 1, Line 1
>> > Table error: Object ID 1905493917, index ID 4, page (3:224866). Test
>> > (sorted[i].offset >= max) failed. Slot 0, offset 0x9f overlaps with the
>> > prior
>> > row.
>> > There are 4 rows in 1 pages for object 'WATSTARTDATETP'.
>> >
>> > DBCC results for 'ACCRUALTRAN'.
>> > There are 5919148 rows in 150538 pages for object 'ACCRUALTRAN'.
>> > CHECKDB found 0 allocation errors and 3 consistency errors in table
>> > 'ACCRUALTRAN' (object ID 1905493917).
>> >
>> > Could it be that our IO subsystem is causing corruption? I am thinking
>> > about running SQLIOSim and SQLIOStress..
>> >
>> > If you could please take a look at the errors and give me your thoughts
>> > it
>> > would be greatly appreciated.
>> >
>> > Thank you in advance,
>> >
>> > Julia
>> >
>> >
>> >
>>

help needed on a silly query :P

hi ...
i m a new member
i want a help on this query(new user of sql :D )
yaa and the query is

create a rule and attach it to the service_code(column) of airline_service(Table) alow only the values 'cc','n' and 'wc' to be entered into the column

plz tell mee... :confused: ..i wlll be gr8 fulllllllllllllllllllllllllllllllll ;)I suggest you to create a CHECK CONSTRAINT instead of a rule:

ALTER TABLE dbo.airline_service
ADD CONSTRAINT chk_servicecode CHECK (service_code IN ('cc', 'n', 'wc'))

Davide Mauri
http://www.davidemauri.it|||You could create a trigger or a check. I think a check is more likely what you'd like, fe:

use monkey
go

create table table1 (mycolumn varchar(10))
go

alter table table1 with check
add constraint df__myrule check
( mycolumn not in ('yes', 'allowed'))
go

insert into table1 (mycolumn) values ('yes')
insert into table1 (mycolumn) values ('no')
go

select *
from table1

drop table table1
go

See BOL on CHECK

EDIT: sniped!|||thank youuuuuuuuuuuuuuuuuuuuuuu :o|||I hope your professor gives you full credit for that, but next time try to do your own homework first.|||I doubt if the Professor will give a credit!

He asked for a rule so it should be:

CREATE RULE list_rule
AS
@.list IN ('cc', 'n', 'wc')

nomis|||That's for sure, but rules are to be considered deprecated, only for backward compatibility. So the CHECK solution is really better. :-)

Anyway, after adding the rule it must be binded with sp_bindrule

Help needed in SQL Query Search!

Hi everyone,

I'm trying to implement SQL Server database search. The details are:-
1. I have table called EMPLOYEE has FNAME,LNAME etc cols.
2. User might look for any employee using either FNAME or LNAME
3. I have search box in asp.net where user could enter search string
The sample data:
FNAME LNAME
abc george
def george
rkis lita
rose lita

The query i wrote:
SELECT * FROM EMPLOYEE WHERE lname like '%' + searchArg + '%'
My problem is:-
1. let's say user is looking for employee "george"; In search string instead of typing actual word "george", user could type "jeorge"; because the name pronounce or sounds like similar.
Same thing with user could type "leta" instead of "lita". Again these are all similar sounds.

When you look for "jeorge" in GOOGLE; it says "did you mean george"; i would like implement something like that. somewhere i saw SOUNDEX would do what i am looking for; but i no luck for me.

Is this possible anyway in T-SQL or Fulltext search.

Your help is greatly appreciated.

Thanks
Bob

Bob,
You were on the right track with Soundex, here's how you can use it. Also look into difference.
Sample data: (pubs)

au_id au_lname au_fname

---- ------------ -------

409-56-7008 Bennet Abraham

648-92-1872 Blotchet-Halls Reginald

238-95-7766 Carson Cheryl

722-51-5454 DeFrance Michel

712-45-1867 del Castillo Innes

427-17-2319 Dull Ann

213-46-8915 Green Marjorie

527-72-3246 Greene Morningstar

472-27-2349 Gringlesby Burt

My Queries:

select*from authorswheresoundex(au_lname)=soundex('Benet')

Returns:
au_id au_lname au_fname

---- ------------ -------

409-56-7008 Bennet Abraham

select au_id,au_lname,au_fnamefrom authorswheredifference(au_lname,'whit')> 3

Returns:
au_id au_lname au_fname

---- ------------ -------

172-32-1176 White Johnson

Hope this helps, as you can see here it is definitely possible to get the "similar" functionality. Check books online for more info on difference, iirc it returns one of a number of different values that state how close or different the words are.
Scott

Help needed for SSRS 2005(Regarding html code)

Hi Experts,
I am working on SSRS 2005, and I am facing a problem in this.
We are using an ASP.net application in which user fills a form. Some
fields contain text fields and we are entering all the fields in our
database through application.
But when we are entering values from application then our database
contains some html code in some fields with data.
Now I want to show those fields in my report, Values coming from
database in my report but they are in html format. So it is not
readable in report.
Now you people tell me how to solve this issue in my reports. I want
that data in my report as user entered in the application form.
Any help will be appreciated.
Regards
DineshDinesh,
The only thing I can think of is writing a function which strips out the
HTML tags and then call that function for each field in your report. You
could make this function a Report Code Block or even better, put it in an
assembly referenced by the Report.
--
Andy Potter
blog : http://sqlreportingservices.spaces.live.com
info@.(NOSPAM)lakeclaireenterprises.com
"Dinesh" <dinesht15@.gmail.com> wrote in message
news:1176467678.190725.177080@.y80g2000hsf.googlegroups.com...
> Hi Experts,
> I am working on SSRS 2005, and I am facing a problem in this.
> We are using an ASP.net application in which user fills a form. Some
> fields contain text fields and we are entering all the fields in our
> database through application.
> But when we are entering values from application then our database
> contains some html code in some fields with data.
> Now I want to show those fields in my report, Values coming from
> database in my report but they are in html format. So it is not
> readable in report.
> Now you people tell me how to solve this issue in my reports. I want
> that data in my report as user entered in the application form.
> Any help will be appreciated.
> Regards
> Dinesh
>

help needed ...to update the datatable

hi,

I have my database stored in the sqlserver 2005. Using the table name i am retrieving the table and it is displayed to the user in the form of datagridview.I am allowing the user to modify the contents of the table, including the headers. Is it possible for me to update the table straightway rather than giving a sql update command for each and every row of the table .

Pls reply asap....

-Sweety

Sure is.

Though this really isnt a SQL Issue you have. You also forgot to mention what programming language you are using (VB.NET, C# etc) If you are using the datagrid and a dataset, then its easily possible however, depending on what .net platform you are using (1.1, 2.0) if you tell us, we will be able to help further.|||

hi,

sorry for the duplicate posting.I am using VC# and .net 2.0..

bye

Sweety

help needed ! sql query

my app contains one form (aspx) and it has different controls to be filled by user (textbox,radiobutton ..etc)
it has one button which i want to use to pass values entered in these controls to other page and do some queries to sql server there (2nd page)
Now the thing is ...my controls can have NULL values ...like user could enter just one parameter and hit button
or user can fill 2 parameter and hit enter
so on the other hand (2nd page) how should i query the database accordingly ...

Example:
if (myTextBox.Text == String.Empty)
{
myParameter.Value = DBNull.Value;
}
else
{
myParameter.Value = myTextBox.Text;
}
|||

You can always use an If Else loop to write the query.
If IsDbNull(parameter) Then
parameter = (Whatever default value or just null)
Else
parameter = Request.Form("name of parameter")
End If
I hope this is clear enough.

Help me to fix this error

hi all

Server Error in '/test' Application.

---

Cannot open database requested in login 'mehr'. Login fails. Login failed for user 'COMPUTER2\ASPNET'.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details:System.Data.SqlClient.SqlException: Cannot open database requested in login 'mehr'. Login fails. Login failed for user 'COMPUTER2\ASPNET'.
Source Error:

Line 67: Line 68: Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.ClickLine 69: cmd.Connection.Open()Line 70: cmd.ExecuteNonQuery()Line 71: cmd.Connection.Close()

Source File:c:\inetpub\wwwroot\test\WebForm1.aspx.vb Line:69
Stack Trace:

[SqlException: Cannot open database requested in login 'mehr'. Login fails.Login failed for user 'COMPUTER2\ASPNET'.] System.Data.SqlClient.ConnectionPool.GetConnection(Boolean& isInTransaction) +474 System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction) +372 System.Data.SqlClient.SqlConnection.Open() +384 test.WebForm1.Button1_Click(Object sender, EventArgs e) in c:\inetpub\wwwroot\test\WebForm1.aspx.vb:69 System.Web.UI.WebControls.Button.OnClick(EventArgs e) +108 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +57 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +18 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain() +1277

---

Version Information: Microsoft .NET Framework Version:1.1.4322.573; ASP.NET Version:1.1.4322.573


thanks for your replyMeans user computer2/aspnet isnt a valid user in your database mehr. You either need to add the user, or add a different user to your connection string.

Nick

Help me PLZ

Hi...

I have a problem ..I am creating an auction site the problem is when the user enter the min bid (the type of it is money) the compiler display error

cmd.Parameters.Add("@.minbid", Data.SqlDbType.money)

when I but the type of the min bid integer its work

cmd.Parameters.Add("@.minbid", Data.SqlDbType.Int)

use Decimal type and set the precision appropriately.

Also few things:

(1) Please do not multi-post.

(2) Post all therelevant code.

(3) Post the EXACT error message as it appears rather than simply saying compilor displays error or there is a problem. In order for some one to visualize the problem and help you we need as much information as we can. You have to help us first in order for us to help you.

Help me please about username and password

How to set user and password connect to Database server from other program
when Sql server 2000 authentication by Windows ?
And How Change Windows authentication to Sql server authentication.
To connect via a different user and password you have to modify the
connection string that you are connecting with, but thats only valid
for SQL Server Authentication.You can=B4t specify the Windows user and
password as though this is Integrated Authentication. As you didn=B4t
state how (with which program etc) you are connecting its hard to give
you a suggestion for that, but if you are using the .NET Framework you
could start a new thread and impersonate the thread as a special
Windows user, then you could use impersonation even you are logged on
with a different user.
HTH, jens Suessmeyer.

Help me please about username and password

How to set user and password connect to Database server from other program
when Sql server 2000 authentication by Windows ?
And How Change Windows authentication to Sql server authentication.To connect via a different user and password you have to modify the
connection string that you are connecting with, but thats only valid
for SQL Server Authentication.You can=B4t specify the Windows user and
password as though this is Integrated Authentication. As you didn=B4t
state how (with which program etc) you are connecting its hard to give
you a suggestion for that, but if you are using the .NET Framework you
could start a new thread and impersonate the thread as a special
Windows user, then you could use impersonation even you are logged on
with a different user.
HTH, jens Suessmeyer.

Help me in writing sql query

When a user logs in the system I mark a Table in the database(that is a new id is generated)
Now I need to write a query which actually gets the following things--

1)Total no of people logged in the system for a given date
2)Minimum no of people logged in the system for a given date
3)Maximum no of people logged in the system for a given date
4)Average no of people logged in the system for a given date.

Hope You guys would help me in writing this query

ThanksWell, if all you're logging is when someone logged in, the only thing I can see you being able to get is the count of how many people logged in on a given date.
How long can people keep their connections? Overnight?
Unless you've also kept track of when they logged out, I don't see any way to know how many people were logged in at a given time. So I don't see a way to get the minimum, maximum or average.