reasons for allocation/consistency errors

What are the reasons for why me may see allocation or consistency errors
when we run DBCC CHECKDB ? Using SQL 2000.
Basically looking for all possible causes
That is kind of a big question, and is probably why no one answers... None
of us have a list of all of the reasons...
In my experience, if you run your DBCC without going to read only or single
user mode, and others are using the system for insert, update, delete during
the dbcc, you will probably get some errors. These are probably due to
changes made by the other users... If you get such an error, rerun dbcc on
the table in question... if the error disappears, or moves to another page,
consider it a transient error and ignore it... If the same error occurs on
the same page, it is a hard error and work to fix it..
Otherwise the most common reasons for DBCC errors is hardware failures.
These can include memory chip failures, disk controllers, and disk drives...
Often re-occuring dbcc errors are the first warning you will get regarding
an impending hardware failure...
Software bugs in the OS and SQL can also cause corruptions, but this occurs
less frequently.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:unQs66aIEHA.2688@.tk2msftngp13.phx.gbl...
> What are the reasons for why me may see allocation or consistency errors
> when we run DBCC CHECKDB ? Using SQL 2000.
> Basically looking for all possible causes
>
>

reasons for allocation/consistency errors

What are the reasons for why me may see allocation or consistency errors
when we run DBCC CHECKDB ? Using SQL 2000.
Basically looking for all possible causesThat is kind of a big question, and is probably why no one answers... None
of us have a list of all of the reasons...
In my experience, if you run your DBCC without going to read only or single
user mode, and others are using the system for insert, update, delete during
the dbcc, you will probably get some errors. These are probably due to
changes made by the other users... If you get such an error, rerun dbcc on
the table in question... if the error disappears, or moves to another page,
consider it a transient error and ignore it... If the same error occurs on
the same page, it is a hard error and work to fix it..
Otherwise the most common reasons for DBCC errors is hardware failures.
These can include memory chip failures, disk controllers, and disk drives...
Often re-occuring dbcc errors are the first warning you will get regarding
an impending hardware failure...
Software bugs in the OS and SQL can also cause corruptions, but this occurs
less frequently.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:unQs66aIEHA.2688@.tk2msftngp13.phx.gbl...
> What are the reasons for why me may see allocation or consistency errors
> when we run DBCC CHECKDB ? Using SQL 2000.
> Basically looking for all possible causes
>
>

reasons for allocation/consistency errors

What are the reasons for why me may see allocation or consistency errors
when we run DBCC CHECKDB ? Using SQL 2000.
Basically looking for all possible causesThat is kind of a big question, and is probably why no one answers... None
of us have a list of all of the reasons...
In my experience, if you run your DBCC without going to read only or single
user mode, and others are using the system for insert, update, delete during
the dbcc, you will probably get some errors. These are probably due to
changes made by the other users... If you get such an error, rerun dbcc on
the table in question... if the error disappears, or moves to another page,
consider it a transient error and ignore it... If the same error occurs on
the same page, it is a hard error and work to fix it..
Otherwise the most common reasons for DBCC errors is hardware failures.
These can include memory chip failures, disk controllers, and disk drives...
Often re-occuring dbcc errors are the first warning you will get regarding
an impending hardware failure...
Software bugs in the OS and SQL can also cause corruptions, but this occurs
less frequently.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:unQs66aIEHA.2688@.tk2msftngp13.phx.gbl...
> What are the reasons for why me may see allocation or consistency errors
> when we run DBCC CHECKDB ? Using SQL 2000.
> Basically looking for all possible causes
>
>

Really urgent - Data field retrieve and store

Basically i make use of FormView in Web Developer Express Edition to create and store information to the database ...

what i want to do further is to capture the value within the primary key of the newly created recordset that I set it become auto increament ....

this value would be stored within a session string in global condition that allowed any page able to access it ....

If you simply want to return the ID to use within the application, and you are using a stored procedure, do this.

Insert the record as normal in the table, then add a statement such as SELECT SCOPE_IDENETITY() as TheNewId

If your insert fails, this line never executes. If insert is OK, you get teh new ID passed back

In your code, perform an execute scaler method call on the connection, and cast the return (decimal as object) into an integer, and store in session. If the returned value is null, the insert failed

What I often create for projects is a global "Execute Scaler" method that accepts the name of a stored procedure, and a string dictionary of name/value pairs.

IN the method, create the SQL Command object, call the SQLCommandBuilder static method to get the parameters, then iterate through the dictionary to add these by name.

Call the command ExecuteScaler as mentioned and proceed as above

|||Really appreciate ur feedback men... but it looks so technical for me men... i am totally a new beginner but have to complete this project within another week time...

can u demonstrate the code (sorry for the hassle)... i kind of not sure how to achieve what u had mentioned...|||

Example SP

CREATE PROCEDURE

dbo.MyTable_Insert

(

@.Parameter1varchar(100)

)

AS

SET NOCOUNT ON

INSERT INTOMyTable (MyData)VALUES(@.Parameter1)

SELECT SCOPE_IDENTITY()asTheNewId

Example Code

int InsertRecord(string insertValue)

{

// assumes you have set cnn as a suitable connection

SqlCommand cmd =newSqlCommand("MyTable_Insert", cnn);

cmd.CommandType = System.Data.CommandType.StoredProcedure;

cmd.Parameters["@.Parameter1"].Value = insertValue;

object retObject = cmd.ExecuteScalar();

if (retObject !=null)

return (int)retObject;

else

return 0;// no new record

}

really strange performance problem

hey
I have a sql2000 server SP3 and I mgrate a databse from SQL7. I run teo basically equal select: he first one 2 seconds the second one 58 minutes.....

select count(*) from uu_resume_ses_dummy_dummy
where substring(dominio,1,20)
not in (select substring(col018_dominio,1,20)
from iis_uu_diario_resume where substring(col018_dominio,1,20)
= substring(uu_resume_ses_dummy_dummy.dominio,1,20))
option (maxdop 1)

select count(*) from uu_resume_ses_dummy_dummy
where substring(dominio,1,30)
not in (select substring(col018_dominio,1,30)
from iis_uu_diario_resume where substring(col018_dominio,1,30)
= substring(uu_resume_ses_dummy_dummy.dominio,1,30))
option (maxdop 1)

the only differencei s that the substring range: 20 to 30. Notice that the limit is not fixed. SOmetimes the jump in execution time happende when I change from 90 top 91......
really I dont' know. (Fields ara varchar(90) but it was the same with varchar(255). the PLAN are exactly the same. in the second case the CPU was 50% fror 58 minutes fixed.
thanks for all the help (really needed)essentialy, for every record in the "uu_resume_ses_dummy_dummy" table you are looking at every record in the "iis_uu_diario_resume" table Using only one processor.

Since you will be looking at every record you have the potential of being delayed by locks, index leaf splits and other traffic. What happens if you run these selects on a quiet system. I suspect the time diffrence is small.|||I was runnng these queries both in a "busy" server (4 cpu, 4Gb RAM) and on a really quiet server (2 CPU, 4GB RAM) with same timing. Quiet server means that basically % of CPU without that select was netween 0 and 5%|||forgot things.

1) same times without the option of processing in one CPU only
2) both table are index on the specific fields.

what you say is ok. problem is:why almost identical queries have such a big big big difference in execution time?

Really need help w/ what should be simple question!

Hello,

Basically, all I am trying to do is create an SSIS pkg that will read a list of stored procedures from a table. I set up an EXECUTE SQL TASK to do this. I am saving the result set to a variable (type object).

Next, I call a FOREACHLOOP container, which references the variable from the EXECUTE SQL TASK.

But I'm not sure what to do next? I just want the FOR EACH LOOP to simply execute the stored procedure name as given by the first EXECUTE SQL TASK.

I was thinking to put another EXECUTE SQL TASK inside the FOR EACH LOOP, but I don't know how to reference the stored procedure name correctly, or if this is even allowable.

I am not a scripter :-)

Can you please offer me some advice on what to do next? I am desperately seeking a solution.

Thank you!!!

In the Variable Mappings tab of your For Each container, make sure that you are mapping the necessary columns (like the stored proc name) of your resultset to variables.

Create a new variable to hold the call to the stored procedure. In the properties for the variable, make sure EvaluateAsExpression is true. In the Expression property, construct the string for a call to the stored proc, using the variables you mapped in the For Each container. Add an Execute SQL task in your For Each container, and set the SQLSourceType to variable. Then pick the variable you created as the SourceVariable.

If you wait a bit, I'll post an example on my blog.

|||

Example:

http://agilebi.com/cs/blogs/jwelch/archive/2007/03/21/using-for-each-to-iterate-a-resultset.aspx

The sample package is attached to the post.

Hope this helps.

|||

Hi John,

I looked *everywhere*, and I honestly don't see where to set "EvaluateAsExpression" is true. That is, where in the world do I set the properties for the variable in the FOREACH containter editor? I really don't see this anywhere.

I also looked at your example from your post, and I don't see this anywhere, either?

Thanks

|||

Hi John,

I examined your example very carefully. However, I don't know what you mean by the statement:

There is a variable named "select". This variable uses an expression to build the following select statement: "SELECT ID, ParentID, ChildName FROM ChildTable WHERE ParentID = " + the "parentID" variable. The "select" variable is then used in another Execute SQL task.

Where are you building the expression? I don't see it.

Thanks

|||Select the variable in the variables window (not in the container editor) and hit F4. This should bring up the properties for the variable. EvaluateAsExpression is an option in that window. This is also where the expression is defined.|||

Are you talking about the DataObjectVariable under EnumeratorProperties in the Properties of the ForEachLoop container?

I see my the name of my ADO Object Source Variable (User::Reports) , but not the mapped column (User::report_name) > which contains the name of the stored procedure.

But hitting F4 does not bring up anything.

?

Thanks

|||

I think I may be getting "closer", in spite of things probably not being set correctly at this point.

So far I have an EXEC SQL task >> FOREACHLOOP >> EXEC SQL task.

And it appears it is actually trying to RUN the stored procedure names. But I am getting this error. Do you think this has to do with my not setting the EvaluateAsExpression? Or something else? Thanks!

SSIS package "Migration - Run Report Comparison.dtsx" starting.

Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query "MY_STORED_PROC" failed with the following error: "Parameter name is unrecognized.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Task failed: Execute SQL Task

Warning: 0x80019002 at Foreach Loop Container: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

Warning: 0x80019002 at Migration - Run Report Comparison: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

|||No, I'm talking about the window where you originally define the variable. On the menu bar for Visual Studio, choose SSIS..Variables, and you should see a list of variables defined in your package. You may need to click the "Show All Variables" button, as some variables may be scoped to specific items in the package.|||

Yes, I see it now, thanks so much for explaining this. I am very new to SSIS and packages in general.

So I set the variable which contains the stored procedure name EvaluateAsExpression as True.

However, I am still getting aforementioned error.

|||

Ha!

Figured it out. The devil is in the details as they say!

What was missing was that in the Parameter Mapping tab of the 2nd EXEC SQL TASK was that I needed to set the Parameter Name to 0.

I still don't fully understand the meaning of all the 0's, although I think it has something do with how OLEDB does things.

Anyhow, it's working... now onto step 2 - write outputs of sp's to name files.

Thanks again

Your example was very useful!!

|||

Could you mark the question as answered? Makes it easier for others to find answers for similar questions.

Thanks.

Really dumb Stored Proc Question. But I need help and Im lost.

Right now, I'm working on the database of a new project. The project is basically a keyword tracking and referral tracking website directory. My plan is to give the website some java code that will allow me to grab the http_referer and request_uri per page load. (everytime they get traffic)

Right now, I've got some tables I think will work fine. One is the storage table, where I'll store what the javascript grabs.

table.http_ref (columns will be)
uid (Unique ID)
http_referer
request_uri

Then I got the results page I want to parse the http_ref table into. For example.

Here we have a google results page which is a search for baby shoes. This would be the http_referer

http://www.google.com/search?sourceid=navclient-ff&ie=UTF-8&rlz=1B2GGGL_enUS177US177&q=baby+shoes
And if someone clicks on a link get this page, which let's say I'm tracking in my project.
http://www.thebabymarketplace.com/securestore/c54581.2.html
in my http_ref table I will have
uid
(whatever)
http_referer
http://www.google.com/search?sourceid=navclient-ff&ie=UTF-8&rlz=1B2GGGL_enUS177US177&q=baby+shoes
request_uri
http://www.thebabymarketplace.com/securestore/c54581.2.html
so I want a stored proc to grab the http_referer column and parse it for the refering search engine
http://www.google.com
and the key words
search?sourceid=navclient-ff&ie=UTF-8&rlz=1B2GGGL_enUS177US177&q=baby+shoes
Then grab the uri_request, which should be the web page visited,
and INSERT into another table where I will hold
keywords
referer
refered page
Any suggestions? I've been going nuts over this.

Then the key point is to parse the http_referer. You should gurantee that all http_referer strings are in a same format, for example the search string begins with '/search?' and the keywords start with '&q=' and locate the end of the string. Then you can use such procedure:

CREATE PROC sp_ParseHttpRef
AS
INSERT INTO Tbl_Parsed
SELECT SUBSTRING(HttpReferer,1,CHARINDEX('/search?',HttpReferer)-1),
RIGHT(HttpReferer,LEN(HttpReferer)-(CHARINDEX('&q=',HttpReferer,CHARINDEX('/search?',HttpReferer))+LEN('&q=')-1)),
Request_URI
FROM Tbl_SourceHttp
go

|||

Thank you very much.

So, can I take this proc and use it as a template to track other search engine referer strings?

For instance:

MSN

http://search.msn.com/results.aspx?q=baby+shoes&FORM=SSRE

or Yahoo

http://search.yahoo.com/search?p=baby+shoes&sm=Yahoo%21+Search&fr=FP-tab-web-t&toggle=1&cop=&ei=UTF-8

and create a Proc for each search engine I'm tracking?

Seems as if I would then have to Schedule the proc's to run, our just call them when I grab the http_referer and request_uri?

|||

Sure you can use the sample as a template, just keep in mind to parse the http_referer correctly. You do not need to schedule the proc to run, you can call it when you grab the http_refer and request_uri in your application, or you can make the proc as an insert trigger on the table which stores the http_refer and request_uri. For example:

create trigger trg_ParseHttp on Tbl_SourceHttp for insert
as
insert into Tbl_Parsed
SELECT SUBSTRING(HttpReferer,1,CHARINDEX('/search?',HttpReferer)-1),
RIGHT(HttpReferer,LEN(HttpReferer)-(CHARINDEX('&q=',HttpReferer,CHARINDEX('/search?',HttpReferer))+LEN('&q=')-1)),
Request_URI
FROM inserted
go