Data coversion or Derived column?

I have a numeric column with the following sample values in a source flat file:

240

6

48

310

55

I would like to dump them in a table (destination) as string with the length only 3 and in the following format "xxx" .

Data in the destination column will look like this after the transformation:

240

006

048

310

055

Thanks for your help!

milton06 wrote:

I have a numeric column with the following sample values in a source flat file:

240

6

48

310

55

I would like to dump them in a table (destination) as string with the length only 3 and in the following format "xxx" .

Data in the destination column will look like this after the transformation:

240

006

048

310

055

Thanks for your help!

You'll need a derived column component for that. Here's the expression

RIGHT("000" + (DT_STR, 3, 1252)[columnname], 3)

-Jamie

|||Excellent Jamie.|||

milton06 wrote:

Excellent Jamie.

Please mark his post as the answer if that solved your problem.

Thanks,
Phil

Data Conversion Problem

I have tried the following in all kinds of combinations but cannot get it to
work. At this point I am not seeing the problem straight and need new eyes
to guide me.
CREATE TABLE EmpEvals
(
last_name varchar(25),
first_name varchar(25),
begin_dt datetime,
adj_beg_dt datetime,
termination_date datetime,
eval_months int
)
INSERT INTO EmpEvals (last_name, first_name, begin_dt, adj_beg_dt,
termination_date, eval_months )
SELECT eval_months = CAST(DATEDIFF(m, begin_dt, GETDATE() ) as int ),
last_name, first_name, begin_dt, adj_beg_dt, termination_date
FROM employee
I am getting an error going from datetime to int with the CONVERT/CAST in
the SELECT statement. I've tried both CONVERT and CAST and just cannot get
the CONVERT/CAST the way SQL Server wants it. I even tried changing
eval_months to datetime and it complained. Someone PLEASE help before I have
no hair left to pull out!
TIA
MikeOn Wed, 28 Jun 2006 08:08:52 -0400, "Mike" <mavila@.shoremortgage.com>
wrote:

>INSERT INTO EmpEvals (last_name, first_name, begin_dt, adj_beg_dt,
>termination_date, eval_months )
>SELECT eval_months = CAST(DATEDIFF(m, begin_dt, GETDATE() ) as int ),
>last_name, first_name, begin_dt, adj_beg_dt, termination_date
>FROM employee
The order of the column list of the INSERT must match the order of the
column list of the SELECT. In the code above, last_name is getting
the data for eval_months, first_name is getting last_name, etc.
Roy Harvey
Beacon Falls, CT|||Hi Mike
DATEDIFF returns an INT so there should be no problem with conversion. In
fact, you shouldn't even need the CAST at all.
I think the problem is that you are returning the value for eval_ months
first in your select, but it is the last column in the table. So all your
select values are trying to go into the wrong columns, and there are
conversion errors.
Using eval_months in your SELECT only gives a column header to the result,
it does not map it to a column of the table you are inserting into. You cold
give it any column name you want and it would be ignored, since you are
inserting into a table, and not returning the SELECT result to the client.
Try this:
INSERT INTO EmpEvals (last_name, first_name, begin_dt, adj_beg_dt,
termination_date, eval_months )
SELECT last_name, first_name, begin_dt, adj_beg_dt, termination_date,
DATEDIFF(m, begin_dt, GETDATE() )
FROM employee
HTH
Kalen Delaney, SQL Server MVP
"Mike" <mavila@.shoremortgage.com> wrote in message
news:Oc9REwqmGHA.5100@.TK2MSFTNGP04.phx.gbl...
>I have tried the following in all kinds of combinations but cannot get it
>to work. At this point I am not seeing the problem straight and need new
>eyes to guide me.
>
> CREATE TABLE EmpEvals
> (
> last_name varchar(25),
> first_name varchar(25),
> begin_dt datetime,
> adj_beg_dt datetime,
> termination_date datetime,
> eval_months int
> )
> INSERT INTO EmpEvals (last_name, first_name, begin_dt, adj_beg_dt,
> termination_date, eval_months )
> SELECT eval_months = CAST(DATEDIFF(m, begin_dt, GETDATE() ) as int ),
> last_name, first_name, begin_dt, adj_beg_dt, termination_date
> FROM employee
>
> I am getting an error going from datetime to int with the CONVERT/CAST in
> the SELECT statement. I've tried both CONVERT and CAST and just cannot get
> the CONVERT/CAST the way SQL Server wants it. I even tried changing
> eval_months to datetime and it complained. Someone PLEASE help before I
> have no hair left to pull out!
> TIA
> Mike
>|||Mike
How about to move an eval_months column at the beginning of the columns
list?
INSERT INTO EmpEvals (eval_months ,last_name, first_name, begin_dt,
adj_beg_dt,
termination_date, eval_months )
SELECT eval_months = CAST(DATEDIFF(m, begin_dt, GETDATE() ) as int ),
last_name, first_name, begin_dt, adj_beg_dt, termination_date
FROM employee
"Mike" <mavila@.shoremortgage.com> wrote in message
news:Oc9REwqmGHA.5100@.TK2MSFTNGP04.phx.gbl...
>I have tried the following in all kinds of combinations but cannot get it
>to work. At this point I am not seeing the problem straight and need new
>eyes to guide me.
>
> CREATE TABLE EmpEvals
> (
> last_name varchar(25),
> first_name varchar(25),
> begin_dt datetime,
> adj_beg_dt datetime,
> termination_date datetime,
> eval_months int
> )
> INSERT INTO EmpEvals (last_name, first_name, begin_dt, adj_beg_dt,
> termination_date, eval_months )
> SELECT eval_months = CAST(DATEDIFF(m, begin_dt, GETDATE() ) as int ),
> last_name, first_name, begin_dt, adj_beg_dt, termination_date
> FROM employee
>
> I am getting an error going from datetime to int with the CONVERT/CAST in
> the SELECT statement. I've tried both CONVERT and CAST and just cannot get
> the CONVERT/CAST the way SQL Server wants it. I even tried changing
> eval_months to datetime and it complained. Someone PLEASE help before I
> have no hair left to pull out!
> TIA
> Mike
>|||BLESS YOU!! That worked nicely. I need a vacation. I'm getting by
facts.
Thanks.
Mike
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:urs4a21tmcnc7kj680mk4vjbp4ip1d7bfb@.
4ax.com...
> On Wed, 28 Jun 2006 08:08:52 -0400, "Mike" <mavila@.shoremortgage.com>
> wrote:
>
> The order of the column list of the INSERT must match the order of the
> column list of the SELECT. In the code above, last_name is getting
> the data for eval_months, first_name is getting last_name, etc.
> Roy Harvey
> Beacon Falls, CT

Data Conversion Issue

Hi,

I have a simple query that does the following :-

select desc1,desc2,desc3,desc4,desc5 from testdata

So I select the data for the above

What I would like to achieve without having to go to great lengths the
following:-

So taking the column desc1 I want to insert this into a table

e.g.

Desc1 is record 1 in the table
Desc2 is record 2 in the table
Desc3 is record 3 in the table
Desc4 is record 4 in the table
and so on

Is there a tool or a special type declaration that can be used ?

Regards
Andrew[posted and mailed, please reply in news]

Info (info@.schnof.co.uk) writes:
> I have a simple query that does the following :-
> select desc1,desc2,desc3,desc4,desc5 from testdata
>
> So I select the data for the above
> What I would like to achieve without having to go to great lengths the
> following:-
> So taking the column desc1 I want to insert this into a table
> e.g.
> Desc1 is record 1 in the table
> Desc2 is record 2 in the table
> Desc3 is record 3 in the table
> Desc4 is record 4 in the table
> and so on

I'm afraid that I don't really understand what you are asking for. Could
you provide the following:

o CREATE TABLE statement for your table(s).
o INSERT statement with sample data.
o The desired result from this sample data.

The reason I ask for this is that it clarifies your question, and with
the script it's possible to post a tested solution.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

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

Data Conversion (String to DateTime)

I am trying to insert data from a web form to a SQL Database. I am receiving the following error: {"String was not recognized as a valid Boolean."} I am also receiving a similar error for text boxes that have dates.

Below is the code that I am using:

<asp:SqlDataSource

id="SqlDataSource1"

runat="server"

connectionstring="<%$ ConnectionStrings:ConnMktProjReq %>"

selectcommand="SELECT LoanRepName,Branch,CurrentDate,ReqDueDate,ProofByEmail,ProofByEmail,FaxNumber,ProjectExplanation,PrintQuantity,PDFDisc,PDFEmail,LoanRepEmail FROM MktProjReq"

insertcommand="INSERT INTO MktProjReq(LoanRepName, Branch, CurrentDate, ReqDueDate, ProofByEmail, ProofByEmail, FaxNumber, ProjectExplanation, PrintQuantity, PDFDisc, PDFEmail, LoanRepEmail) VALUES (@.RepName, @.BranchName, @.Date, @.DueDate, @.ByEmail, @.ByFax, @.Fax, @.ProjExp, @.PrintQty, @.Disc, @.Email, @.RepEmail)">

<InsertParameters>

<asp:FormParameterName="RepName"FormField="LoanRepNameBox"/>

<asp:FormParameterName="BranchName"FormField="BranchList"/>

<asp:FormParameterName="Date"FormField="CurrentDateBox"Type="DateTime"/>

<asp:FormParameterName="DueDate"FormField="ReqDueDateBox"Type="DateTime"/>

<asp:FormParameterName="ByEmail"FormField="ProofByEmailCheckbox"Type="boolean"/>

<asp:FormParameterName="ByFax"FormField="ProofByFaxCheckbox"Type="boolean"/>

<asp:FormParameterName="Fax"FormField="FaxNumberBox"/>

<asp:FormParameterName="ProjExp"FormField="ProjectExplanationBox"/>

<asp:FormParameterName="PrintQty"FormField="PrintQuantityBox"/>

<asp:FormParameterName="Disc"FormField="PDFByDiscCheckbox"Type="boolean"/>

<asp:FormParameterName="Email"FormField="PDFByFaxCheckbox"Type="boolean"/>

<asp:FormParameterName="RepEmail"FormField="LoanRepEmailBox"/>

</InsertParameters>

</asp:SqlDataSource>

protectedvoid Button1_Click(object sender,EventArgs e)

{

SqlDataSource1.Insert();

}

I have been searching forums for parsing data, but I haven't found anything that works. Can anyone provide guidance.

Thank you,

Paul

What about remove (cross out in the following) the Type="boolean" for these formfields:

<asp:FormParameterName="ByEmail"FormField="ProofByEmailCheckbox"Type="boolean" />

<asp:FormParameterName="ByFax"FormField="ProofByFaxCheckbox" Type="boolean" />

<asp:FormParameterName="Disc"FormField="PDFByDiscCheckbox" Type="boolean" />

<asp:FormParameterName="Email"FormField="PDFByFaxCheckbox" Type="boolean" />

|||

Thank you, that ended the exception errors.

Paul

data conversion -- varchar to nvarchar

Hello,
The following question applies to SQL Server 2000 SP3 and SQL Server 2005:
We are in the process of investigating international support for our
application and part of that will likely require changing all of the
char/varchar columns in our database to nchar/nvarchar.
From what I gather by reading these forums, altering the columns in place
via ALTER TABLE statements is an acceptable method for doing this.
The question that I have been unable to completely confirm, however, is what
happens to existing data in the tables? Does the data get converted to
Unicode as part of the ALTER TABLE statement? And, if so, is there any risk
that this conversion will produce unexpected or unpredictable results?
Preliminary testing seems to indicate that the data does get converted
successfully during this process but I am just trying to confirm that
suspicion.
Thanks in advance for any assistance,
JohnLx> From what I gather by reading these forums, altering the columns in place
> via ALTER TABLE statements is an acceptable method for doing this.
> The question that I have been unable to completely confirm, however, is
> what
> happens to existing data in the tables? Does the data get converted to
> Unicode as part of the ALTER TABLE statement?
Yes, but of course there can't already be any data in there that requires
Unicode. Other than disk space requirements for those columns doubling (and
don't forget indexes), you shouldn't see any noticeable difference, except
(see next comment).

> And, if so, is there any risk
> that this conversion will produce unexpected or unpredictable results?
Absolutely. For char/varchar in the smaller size ranges (up to 4000) you
shouldn't see any problems. However, if you have a varchar(8000) with at
least one tuple with 4001 or more characters, you will get the following
when you try to convert VARCHAR(8000) to NVARCHAR(4000) (the largest size
for varying double-wide):
Msg 8152, Level 16, State 4, Line 1
String or binary data would be truncated.
The statement has been terminated.
In addition, you'll want to reindex and update statistics for any tables
that have indexes/statistics on the column(s) you're changing. You'll also
want to verify that you re-compile (and alter params and converts, where
necessary) any stored procedures or functions that reference the columns.
You might also want to issue sp_refreshview for any views that reference
those tables.
A|||John,
the data get's converted in place as long as they fit into the new
datatype. Inyour case there shouldn't be an problem. The only thing
you have to keep in mind is while you can store up 8000 char in a
varchar column, nvarchar uses twice the space and thus the limit is
4000 characters.
Markus|||Whenever you alter the structure of a table, it can cause index
fragmentation. You will want to use DBCC INDEXDEFRAG for each altered table.
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
"johnlx" <nomail@.discussions.microsoft.com> wrote in message
news:2288C9DC-40B8-48E0-A63B-143A622A7030@.microsoft.com...
> Hello,
> The following question applies to SQL Server 2000 SP3 and SQL Server 2005:
> We are in the process of investigating international support for our
> application and part of that will likely require changing all of the
> char/varchar columns in our database to nchar/nvarchar.
> From what I gather by reading these forums, altering the columns in place
> via ALTER TABLE statements is an acceptable method for doing this.
> The question that I have been unable to completely confirm, however, is
> what
> happens to existing data in the tables? Does the data get converted to
> Unicode as part of the ALTER TABLE statement? And, if so, is there any
> risk
> that this conversion will produce unexpected or unpredictable results?
> Preliminary testing seems to indicate that the data does get converted
> successfully during this process but I am just trying to confirm that
> suspicion.
> Thanks in advance for any assistance,
> JohnLx
>|||Thanks to everyone who responded. You confirmed what I thought with regards
to the data conversion.
Aaron, good point about the views. I hadn't thought to rebuild them but
will include that in the process.
Thanks,
--John Lennox|||On Mon, 19 Dec 2005 14:23:08 -0500, Aaron Bertrand [SQL Server MVP]
wrote:
(snip)
>In addition, you'll want to reindex and update statistics for any tables
>that have indexes/statistics on the column(s) you're changing. You'll also
>want to verify that you re-compile (and alter params and converts, where
>necessary) any stored procedures or functions that reference the columns.
>You might also want to issue sp_refreshview for any views that reference
>those tables.
Hi Aaron (and JohnLx),
And to avoid implicit conversions that might damage performance, the
next step would be to check all variable declarations, temp table
definitions and table variable definitions - they too should be changed
from varchar to nvarchar and from char to nchar.
And finally, put an N in front of all string constants in your code.
(I.e., use SET @.var = N'Text', not SET @.var = 'Text')
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Data Conversion

I need help!!!! I am about to go nuts! I am getting the following error in SSIS:

Error at Violations Load [SQL Server Destination [3800]]: The column ""Site No "" can't be inserted because the conversion between types DT_STR and DT_NUMERIC is not supported.

I have tried using the data conversion task, modifying all properties to DT_NUMERIC and so on. I just can't figure it out! I am attempting to load a numeric field from a flat file into a SQL Server database. I cannot find any information on this and have tried about everything. I need any help or suggestions anyone can offer! Thank you in advance for your help!!

SD

If the column in sql is a string (char, varchar, etc) you should use a (DT_WSTR,<<length>>)intcolum from file.

For example.. (DT_WSTR,2)12 would cast the number 12 into a string of 2 character length as "12"

Hope that helps.

|||

The destination component tells you the type of the target table. Double click on any data path (the green lines between the components) to see teh tye of the field in the pipeline. At some point the 2 will be different. Thats where you need to do a data conversion.

-Jamie

Data checking?

Hey all,
prolly a simple solution, but why isn't the following string working in
my execute sql step within DTS? It produces results, just not the ones
I want... What am I doing wrong?

select x from new_files where x like '%[^0-9]%' and x like '%[^a-z]%'

It's displaying all the records? It should only be displaying those
records that do *not* contain letters or numbers.
Thanks in advance!
-RoyOn 6 Jan 2005 06:13:08 -0800, roy.anderson@.gmail.com wrote:

> Hey all,
> prolly a simple solution, but why isn't the following string working in
> my execute sql step within DTS? It produces results, just not the ones
> I want... What am I doing wrong?
>
> select x from new_files where x like '%[^0-9]%' and x like '%[^a-z]%'
> It's displaying all the records? It should only be displaying those
> records that do *not* contain letters or numbers.
> Thanks in advance!
> -Roy

Your clause is selecting rows where the x column contains at least one
character that is not a digit and also contain at least one character that
is not a letter. If you had a row where x was all letters, all digits, or
maybe all letters plus punctuation but no digits, etc., then it would not
be included.

The clause you want is probably

WHERE NOT (x LIKE '%[0-9a-z]%')

(parenthesis optional)|||Thanks much Ross, after some toying around, the end product that works
is:

WHERE (x LIKE '%[^0-9a-z]%')

I'm unsure why having the "NOT" specified beforehand produces no
results, but it doesn't. I'm assuming it's because sqlserver perceives
the NOT as referring to the wildcards too, ergo, it's only looking for
blank fields.

Thanks much for the help!!!|||On 6 Jan 2005 09:02:41 -0800, Roy wrote:

> Thanks much Ross, after some toying around, the end product that works
> is:
> WHERE (x LIKE '%[^0-9a-z]%')
> I'm unsure why having the "NOT" specified beforehand produces no
> results, but it doesn't. I'm assuming it's because sqlserver perceives
> the NOT as referring to the wildcards too, ergo, it's only looking for
> blank fields.
> Thanks much for the help!!!

It looks to me like your query is requesting those rows that contain at
least one non-letter, non-digit character. I thought you wanted rows that
contained no letters and contained no digits... maybe I'm still confused
... but if you've got what you want, great.

Data Change

Can anybody suggest the best way I can achieve the following.
To select anybody whose surname has change in the last week, and to automatically flag a code field with "C". :cool:Hi,

Does your table have date field which is updated everytime a user updates the table?|||Yes I have found that it has a Modifcation Date field.|||Is there anyway to tell whether it is the surname that has been updated? Or could it be another field?|||Are you looking for a general solution, or is this a one-time issue?

For a general solution, you should use a trigger to stamp a datefield anytime a surname is modified.

For a one-time solution, restore a backup of your table from a week ago under a different name, and then join them on their primary keys to compare surnames.

....you do have weekly backups, right?|||if you have no record of this information,(the prior updating of the surname) you will need to create a audit column or and audit table then you can create a trigger that will indicate as such from here on out.

here are some {books online} articles you might want to look at.

Create Trigger
Alter Trigger
Programming Triggers

data base access

Hi,
I have problem accessing my sqlDatabase via asp.net
When running a test application I get the following error message:

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: Login failed for user 'P900\ASPNET'.

I know the connection to the db is ok since I can gain access from a simple console application.

Anybody knows what's wrong and how to fix it??
Many thanks,

Cat33Is your ASPNET user on machine P900 a user in the SQL Server you are trying to connect to?

If you are using integrated security, a console mode applicaiton might work because integrated security while you are running a console mode application uses YOUR security context. Running an ASP.NET application, you are using the ASP.NET users' security context (ASPNET by default).

You need to either add the ASPNET user to SQL Server or use a username and password.

Look at the different possible connection strings here:

www.connectionstrings.com|||Ok, thanks for quick response. I have one simple question however;
How do I add the ASPNET user to the SQL Server?
Thanks in advance,
Cat33|||Do you have Enterprise Manager? If so, Expand out the Security folder, and right click on Logins and add a new login.

If not, there is a command line tool called OSQL you can use. Find it and add the folder to the path, or from the folder where OSQL is, run the following:

osql -S servername\instancename -E -q
 --Line numbers will appear
EXEC sp_grantlogin 'COMPUTERNAME\ASPNET'
go
use <databasename>
go
EXEC sp_grantdbaccess 'COMPUTERNAME\ASPNET'
go
EXEC sp_addrolemember 'db_owner', 'COMPUTERNAME\ASPNET'
go

|||Hi,
Sorry to have to plague you with this access question, but after having done what you said, using the osql-tool, nothing improved. I still get the same Error Message, wheré the line 299 is hightlighted. I'm running Framework 1.1 with IIS 5.1 on a XP Pro machine. I have earlier been able to access the database so I simply don't know what to do. I have seen to it that the ASPNET account has been added to the directories in question. What do I do now? Do you have any idea as to where the source to the error is?
Thanks again,
Catharina

Error Message:

Login failed for user 'P900\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: Login failed for user 'P900\ASPNET'.

Source Error:

Line 294:
Line 295:cmd = new SqlCommand(sql, con);
Line 296:con.Open();
Line 297:
Line 298:bool doredirect = true;

Source File: c:\inetpub\wwwroot\webapperikspage\start\newuser.aspx.cs Line: 296

Stack Trace:

[SqlException: Login failed for user 'P900\ASPNET'.]
System.Data.SqlClient.ConnectionPool.GetConnection(Boolean& isInTransaction)
System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction)
System.Data.SqlClient.SqlConnection.Open()
WebAppEriksPage.start.NewUser.InsertUser() in c:\inetpub\wwwroot\webapperikspage\start\newuser.aspx.cs:296
WebAppEriksPage.start.NewUser.btnAccept_Click(Object sender, EventArgs e) in c:\inetpub\wwwroot\webapperikspage\start\newuser.aspx.cs:147
System.Web.UI.WebControls.Button.OnClick(EventArgs e)
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument)
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
System.Web.UI.Page.ProcessRequestMain()|||When you ran the script in the previous message, you did replace COMPUTERNAME with your computer's name (P900), correct? Were there any error messages when you ran those lines through OSQL?|||Hi,
this is what I wrote:
osql - S P900\NetSDK -E -q
1> EXEC sp_grantlogin 'P900\ASPNET'
2> go

1> use Northwind
2> go

1> EXEC sp_grantdbaccess 'P900\ASPNET'
2> go

1> EXEC sp_addrolemember 'db_owner', 'P900\ASPNET'
2>go

1>exit
and I got no error messages

/Catharina|||OK. Also, replace Northwind with the name of the database you want to use. Northwind is a sample database used for that script.|||Well, at this stage the Northwind database is the database I'm using since this is a test trial to see that things work, which they don't, and I can access that database via a console application and also a Windows application.
Any more leads?|||If you added the user to the database, and there were no errors, then I cannot think of anything else.

Can you show your exact connection string (it should not have any password in it, so you should be able to just cut and paste it). If you did have access to Enterprise Manager, I would suggest looking there to verify that the user is really there.|||

con = new SqlConnection("server= p900\\NetSDK; Trusted_Connection=yes; database= Northwind");

I don't have access to Enterprise Manager, can I verify via osql?
Cheers,
Catharina|||When you start OSQL, rather than

osql -S servername\instancename -E -q

Should be, in your case:

osql -S p900\NetSDK -E -q

I have always used Trusted_Connection=true rather than =yes, so I would try that.

Data Available in a Trigger

I am creating a transaction log trigger for a table.

I would like to log the following data

The user login id: SYSTEM_USER

The User's Computer name: ?

The servers time and date: GetDate()

The trigger Action: Update, Delete, or Insert

The unique record ID for the affected table:

One column for the deleted row in xml raw:

One column for the inserted row in xml raw:

Is there a way to get the users computer name?

Consider this:

"select * from inserts for xml raw"

This is nice because i want to store the inserted and/or deleted table information as xml columns. But I would like to handle the transaction log in a way that created one transaction row add per row in the inserted or deleted table.

My end goal is to insert into the transaction log table as follows:

Lets say that my update trigger contains an inserted table with two rows and the deleted table would have the same, two rows.

I would like to insert two rows into my transaction log. with the username, date time, action and one column for the inserted row as xml raw, and one column for the deleted row as xml raw.

Anyone know how to do this?

It would be nice if i could impliment something like this:

'select * as xml raw from inserted' And it ould return as many rows as is in the inserted table, each row having one column wich represents that row in xml format.

'select * from inserted for xml raw' This is not good because it returns one row with one column whose value is an xml representation of the entire inserted table.

Thanx

Jerry Cicierega

The host_name() function will return this information, but it is not always set. Try looking this up in books online.|||

Thank you for responding. Yes this works on my system. The computer name part of my issue is solved.

The xml part of my question still stands.

Thank you !

Data Archiving...sounds easy but how to approach.

hell all,
What is the best way to achieve the following mechanism.

My product has a database, which will be installed at my client site.
as we all know database is something which will grow tremendously.
Now i am planning to come out with a Data archiving facility in such a way that
Yearly data will be backed up and will be maintained in a separate machine.
The data that was backed up will be removed from the current machine's database.Ofcourse
there will be a mchanism provided through which i can transfer data from offline database to online database.
Now, please throw u r thoughts on how to approach this problem...
My database is in SQL Server.

Thanks and regards
saiHi Sai -

I don't know if this is the best way to accomplish this task -

but i set up a job that runs each day at 4:30am (after backup is completed)

that simply deletes all records that are 365 days old in the tables
that i want to keep trimmed -

I do have a datetime stamp on each table that is populated when the record is added to the table -

hope this helps -

take care
tony

Data Access layer Advice

I've been following Soctt Mitchell's tutorials on Data Access and in Tutorial 1 (Step 5) he suggests using SQL Subqueries in TableAdapters in order to pick up extra information for display using a datasource.

I have two tables for a gallery system I'm building. One called Photographs and one called MS_Photographs which has extra information about certain images. When reading the MS_Photograph data I also want to include a couple of fields from the related Photographs table. Rather than creating a table adapter just to pull this data I wanted to use the existing MS_Photographs adapter with a query such as...

1SELECT CAR_MAKE, CAR_MODEL,2 (SELECT DATE_TAKEN3FROM PHOTOGRAPHS4WHERE (PHOTOGRAPH_ID = MS_PHOTOGRAPHS.PHOTOGRAPH_ID))AS DATE_TAKEN,5 (SELECT FORMAT6FROM PHOTOGRAPHS7WHERE (PHOTOGRAPH_ID = MS_PHOTOGRAPHS.PHOTOGRAPH_ID))AS FORMAT,8 (SELECT REFERENCE9FROM PHOTOGRAPHS10WHERE (PHOTOGRAPH_ID = MS_PHOTOGRAPHS.PHOTOGRAPH_ID))AS REFERENCE,11 DRIVER1, TEAM, GALLERY_ID, PHOTOGRAPH_ID12FROM MS_PHOTOGRAPHS13WHERE (GALLERY_ID = @.GalleryID)

This works but I wanted to know if there's a way to get all of the fields using one subquery instead of three? I did try it but it gave me errors for everything I could think of.

Is using a subquery like above the best way when you want this many fields from a secondary table or should I be using another approach. I'm using classes for the BLL as well and wondered if there's a way to do it at this stage instead?

Can't you simply right a query containing a join like so:

SELECTA.CAR_MAKE,A.CAR_MODEL,B.DATE_TAKEN, B.FORMAT,B.REFERENCE,A.DRIVER1,A.TEAM,A.GALLERY_ID,A.PHOTOGRAPH_IDFROMMS_PHOTOGRAPHSAS AINNERJOIN PHOTOGRAPHSAS BON B.PHOTOGRAPH_ID = A.PHOTOGRAPH_IDWHEREA.GALLERY_ID = @.GalleryID

|||

You can use a join but the tutorial explains that this affects the auto-generated methods for inserting, updating and deleting data using the table adapter.

|||

If I understand your problem correctly it sounds like what you want is a join (perhaps an outer join):

select a.car_make, a.car_model, b.date_taken, b.format, b.reference., etc
from ms_photographs a,
photographs b
where a.photographs_id = b.photographs_id
and a.gallery_id = @.GalleryID

The only issue is that the above query is an "inner" join which means that it will only return data that have matching rows in each table. If you might have data from ms_photographs that is not in photograps, then you need an "outer" join, which just means a change to the where clause as follows:

where a.photographs_id *= b.photographs_id
and a.gallery_id = @.GalleryID

There is an alternate syntax involving the use of the words LEFT OUTER JOIN (which is what the above example is) or RIGHT OUTER JOIN, but I prefer the *= syntax (only because I'm old and that's how I learned it This alternate syntax is the new standard, but the old way is still quite common. Here is the above rewritten to the new standard:

select a.car_make, a.car_model, b.date_taken, b.format, b.reference., etc
from ms_photographs a left outer join photographs b
on a.photographs_id = b.photographs_id
where a.gallery_id = @.GalleryID

BTW, the 2 syntaxes do not actually produce identical output in all cases, but the differences are very subtle and have to do with what happens if you include a where condition on the outer table -- it's not worth going into at this point

|||

Thanks for your suggestion. I can see how that would work but I'm trying to avoid it as the tutorial states that it will affect the other autogenerated statements in the tableadapter if i use a join.

Is there any sensible way to do this without a join or in the BLL?

Cheers

|||

nevets2001uk2:

Is there any sensible way to do this without a join or in the BLL?

I don't see how. Either you use a join or your do all the lookups yourself.

|||

nevets2001uk2:

I've been following Soctt Mitchell's tutorials on Data Access and in Tutorial 1 (Step 5) he suggests using SQL Subqueries in TableAdapters in order to pick up extra information for display using a datasource.

I have two tables for a gallery system I'm building. One called Photographs and one called MS_Photographs which has extra information about certain images. When reading the MS_Photograph data I also want to include a couple of fields from the related Photographs table. Rather than creating a table adapter just to pull this data I wanted to use the existing MS_Photographs adapter with a query such as......

You can have multiple select methods - for example, the GetAll and GetByID are common. So if you are not dealing with ginormous volumes and millions of hits, just stick the 3 subqueries in and see how it goes. I dont think SQL Server will physically read the second table three times per row. You can another select method without the subqueries for perfomance if you like. You don'tneed another adapter, though youcould have one if you wanted.

|||

Thanks for the advice. I'll stick with what I have for now and see how I go.

DATA ACCESS

The network admins did some OS security patches (and who
knows what else) and today we're getting the following
error on a few established processes:
Microsoft OLE DB Provider for SQL Server -2147217900
Server 'SERVER123' is not configured for DATA ACCESS.
We believe this is a cross server issue. Anyway, this
error msg looks familiar, but I can't quite place it.
Any help would be much appreciated.Joe,
Try..
exec sp_serveroption SERVER123, 'data access', 'true'
--
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"JoeMan" <anonymous@.discussions.microsoft.com> wrote in message
news:1d0e801c42308$ddd0b690$a401280a@.phx.gbl...
> The network admins did some OS security patches (and who
> knows what else) and today we're getting the following
> error on a few established processes:
> Microsoft OLE DB Provider for SQL Server -2147217900
> Server 'SERVER123' is not configured for DATA ACCESS.
> We believe this is a cross server issue. Anyway, this
> error msg looks familiar, but I can't quite place it.
> Any help would be much appreciated.
>|||Hi,
Execute the below procedure with correct linked server name.
sp_serveroption 'Linked_server_name','data access',true
Thanks
Hari
MCDBA
"JoeMan" <anonymous@.discussions.microsoft.com> wrote in message
news:1d0e801c42308$ddd0b690$a401280a@.phx.gbl...
> The network admins did some OS security patches (and who
> knows what else) and today we're getting the following
> error on a few established processes:
> Microsoft OLE DB Provider for SQL Server -2147217900
> Server 'SERVER123' is not configured for DATA ACCESS.
> We believe this is a cross server issue. Anyway, this
> error msg looks familiar, but I can't quite place it.
> Any help would be much appreciated.
>|||Before I run this, I've got a question:
1. I've already got a link server set up. Does this
just "enable" that linked server?
2. If I am running this on serverA, do I put in 'serverB'
for the first argument, i.e. the linked server that I am
trying to connect to?
3. How could this get set to false? I certainly didn't
do it! Have you ever seen MS installs hose this up?
>--Original Message--
>Joe,
>Try..
>exec sp_serveroption SERVER123, 'data access', 'true'
>--
>Dinesh
>SQL Server MVP
>--
>--
>SQL Server FAQ at
>http://www.tkdinesh.com
>"JoeMan" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1d0e801c42308$ddd0b690$a401280a@.phx.gbl...
>> The network admins did some OS security patches (and who
>> knows what else) and today we're getting the following
>> error on a few established processes:
>> Microsoft OLE DB Provider for SQL Server -2147217900
>> Server 'SERVER123' is not configured for DATA ACCESS.
>> We believe this is a cross server issue. Anyway, this
>> error msg looks familiar, but I can't quite place it.
>> Any help would be much appreciated.
>
>.
>|||Before I run this, I've got a few questions:
1. I've already got a linked server set up and it seems
to be working for reads at least (but the developers are
claiming it's not working for updates). Does this
just "enable" that linked server?
2. If I am running this on serverA, do I put in 'serverB'
for the first argument, i.e. the linked server that I am
trying to connect to?
3. How could this get set to false? I certainly didn't
do it! Have you ever seen MS installs hose this up?
>--Original Message--
>Hi,
>Execute the below procedure with correct linked server
name.
>sp_serveroption 'Linked_server_name','data access',true
>
>Thanks
>Hari
>MCDBA
>"JoeMan" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1d0e801c42308$ddd0b690$a401280a@.phx.gbl...
>> The network admins did some OS security patches (and who
>> knows what else) and today we're getting the following
>> error on a few established processes:
>> Microsoft OLE DB Provider for SQL Server -2147217900
>> Server 'SERVER123' is not configured for DATA ACCESS.
>> We believe this is a cross server issue. Anyway, this
>> error msg looks familiar, but I can't quite place it.
>> Any help would be much appreciated.
>
>.
>|||Hi,
1. I've already got a link server set up. Does this just "enable" that
linked server?
Enables and disables a linked server for distributed query access.
2. If I am running this on serverA, do I put in 'serverB' for the first
argument, i.e. the linked server that I am trying to connect to?
You have to put SERVERB (Which is the remote server)
3. How could this get set to false? I certainly didn't do it! Have you
ever seen MS installs hose this up?
No Idea
Thanks
Hari
MCDBA
"JoeMan" <anonymous@.discussions.microsoft.com> wrote in message
news:188d201c4230b$6b192940$a001280a@.phx.gbl...
> Before I run this, I've got a question:
> 1. I've already got a link server set up. Does this
> just "enable" that linked server?
> 2. If I am running this on serverA, do I put in 'serverB'
> for the first argument, i.e. the linked server that I am
> trying to connect to?
> 3. How could this get set to false? I certainly didn't
> do it! Have you ever seen MS installs hose this up?
> >--Original Message--
> >Joe,
> >
> >Try..
> >
> >exec sp_serveroption SERVER123, 'data access', 'true'
> >
> >--
> >Dinesh
> >SQL Server MVP
> >--
> >--
> >SQL Server FAQ at
> >http://www.tkdinesh.com
> >
> >"JoeMan" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:1d0e801c42308$ddd0b690$a401280a@.phx.gbl...
> >> The network admins did some OS security patches (and who
> >> knows what else) and today we're getting the following
> >> error on a few established processes:
> >>
> >> Microsoft OLE DB Provider for SQL Server -2147217900
> >> Server 'SERVER123' is not configured for DATA ACCESS.
> >>
> >> We believe this is a cross server issue. Anyway, this
> >> error msg looks familiar, but I can't quite place it.
> >>
> >> Any help would be much appreciated.
> >>
> >
> >
> >.
> >|||Joe,
#1.Correct. 'True' enables the linked server for data access.
#2.Yes.
#3.How it was set to false, I dont know.May be, one of those patches did
that.
--
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"JoeMan" <anonymous@.discussions.microsoft.com> wrote in message
news:188d201c4230b$6b192940$a001280a@.phx.gbl...
> Before I run this, I've got a question:
> 1. I've already got a link server set up. Does this
> just "enable" that linked server?
> 2. If I am running this on serverA, do I put in 'serverB'
> for the first argument, i.e. the linked server that I am
> trying to connect to?
> 3. How could this get set to false? I certainly didn't
> do it! Have you ever seen MS installs hose this up?
> >--Original Message--
> >Joe,
> >
> >Try..
> >
> >exec sp_serveroption SERVER123, 'data access', 'true'
> >
> >--
> >Dinesh
> >SQL Server MVP
> >--
> >--
> >SQL Server FAQ at
> >http://www.tkdinesh.com
> >
> >"JoeMan" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:1d0e801c42308$ddd0b690$a401280a@.phx.gbl...
> >> The network admins did some OS security patches (and who
> >> knows what else) and today we're getting the following
> >> error on a few established processes:
> >>
> >> Microsoft OLE DB Provider for SQL Server -2147217900
> >> Server 'SERVER123' is not configured for DATA ACCESS.
> >>
> >> We believe this is a cross server issue. Anyway, this
> >> error msg looks familiar, but I can't quite place it.
> >>
> >> Any help would be much appreciated.
> >>
> >
> >
> >.
> >

Daily Maintenanec Plan

Good Day,
I am having a problem with an SQL server 2005 maintenance plan that updates
Statistics.
I keep receiving the following error:-
Executing the query "UPDATE STATISTICS [dbo].[STATSDATA8_27_2007]
WITH FULLSCAN
" failed with the following error: "Table 'STATSDATA8_27_2007' does not
exist.". Possible failure reasons: Problems with the query, "ResultSet"
property not set correctly, parameters not set correctly, or connection not
established correctly.
I am not sure how to resolve this and it seems to stop statistical updating.
Could anyone offer any help?
Kind Regards,
Paul.It seems that the table you want to automatically update its statistics is
not exist. You or someone else may be deleted it?
Be sure it still exists first.
--
Ekrem Önsoy
"Paul Johnson" <paul.johnson@.gaapweb.com> wrote in message
news:ufUwvys7HHA.1188@.TK2MSFTNGP04.phx.gbl...
> Good Day,
> I am having a problem with an SQL server 2005 maintenance plan that
> updates Statistics.
> I keep receiving the following error:-
> Executing the query "UPDATE STATISTICS [dbo].[STATSDATA8_27_2007]
> WITH FULLSCAN
> " failed with the following error: "Table 'STATSDATA8_27_2007' does not
> exist.". Possible failure reasons: Problems with the query, "ResultSet"
> property not set correctly, parameters not set correctly, or connection
> not established correctly.
> I am not sure how to resolve this and it seems to stop statistical
> updating. Could anyone offer any help?
> Kind Regards,
> Paul.
>|||Thank you for your reply.
I should have mentioned that the plan is updating statistics for all user
databases and so there is no database or table specifically specified. If
this table is not present, why would it attempt to update the statistics?
Thanks for any advice.
Regards,
Paul.
"Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
news:9BE37C2E-55DB-4EA3-ACAC-702118FF60B8@.microsoft.com...
> It seems that the table you want to automatically update its statistics is
> not exist. You or someone else may be deleted it?
> Be sure it still exists first.
> --
> Ekrem Önsoy
>
> "Paul Johnson" <paul.johnson@.gaapweb.com> wrote in message
> news:ufUwvys7HHA.1188@.TK2MSFTNGP04.phx.gbl...
>> Good Day,
>> I am having a problem with an SQL server 2005 maintenance plan that
>> updates Statistics.
>> I keep receiving the following error:-
>> Executing the query "UPDATE STATISTICS [dbo].[STATSDATA8_27_2007]
>> WITH FULLSCAN
>> " failed with the following error: "Table 'STATSDATA8_27_2007' does not
>> exist.". Possible failure reasons: Problems with the query, "ResultSet"
>> property not set correctly, parameters not set correctly, or connection
>> not established correctly.
>> I am not sure how to resolve this and it seems to stop statistical
>> updating. Could anyone offer any help?
>> Kind Regards,
>> Paul.
>>
>|||Well, I did not understand exactly what you meant by saying "plan updates
statistics for all user databases and there is no specipic tables nor
databases".
However, when you create a maintenance plan to update statistics
automatically, you select databases and tables to be updated. So, there you
have already selected those databases\tables... And then, it appears that
you drop one and the job arises an error because it can not find the object
which you chose to be updated.
--
Ekrem Önsoy
"Paul Johnson" <paul.johnson@.gaapweb.com> wrote in message
news:O1D49cv7HHA.980@.TK2MSFTNGP06.phx.gbl...
> Thank you for your reply.
> I should have mentioned that the plan is updating statistics for all user
> databases and so there is no database or table specifically specified. If
> this table is not present, why would it attempt to update the statistics?
> Thanks for any advice.
> Regards,
> Paul.
>
> "Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
> news:9BE37C2E-55DB-4EA3-ACAC-702118FF60B8@.microsoft.com...
>> It seems that the table you want to automatically update its statistics
>> is not exist. You or someone else may be deleted it?
>> Be sure it still exists first.
>> --
>> Ekrem Önsoy
>>
>> "Paul Johnson" <paul.johnson@.gaapweb.com> wrote in message
>> news:ufUwvys7HHA.1188@.TK2MSFTNGP04.phx.gbl...
>> Good Day,
>> I am having a problem with an SQL server 2005 maintenance plan that
>> updates Statistics.
>> I keep receiving the following error:-
>> Executing the query "UPDATE STATISTICS [dbo].[STATSDATA8_27_2007]
>> WITH FULLSCAN
>> " failed with the following error: "Table 'STATSDATA8_27_2007' does not
>> exist.". Possible failure reasons: Problems with the query, "ResultSet"
>> property not set correctly, parameters not set correctly, or connection
>> not established correctly.
>> I am not sure how to resolve this and it seems to stop statistical
>> updating. Could anyone offer any help?
>> Kind Regards,
>> Paul.
>>
>|||Thanks for your reply.
I am assuming that if I do not actually specify the database by selecting
"All databases" it would update the stats for all that are available. So if
I add a new database, I do not need to edit the plan, it would just update
stats for all databases.
Regards,
Paul.
"Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
news:4BFEB99B-50C0-4247-9E10-52BD5A6A4458@.microsoft.com...
> Well, I did not understand exactly what you meant by saying "plan updates
> statistics for all user databases and there is no specipic tables nor
> databases".
> However, when you create a maintenance plan to update statistics
> automatically, you select databases and tables to be updated. So, there
> you have already selected those databases\tables... And then, it appears
> that you drop one and the job arises an error because it can not find the
> object which you chose to be updated.
> --
> Ekrem Önsoy
>
> "Paul Johnson" <paul.johnson@.gaapweb.com> wrote in message
> news:O1D49cv7HHA.980@.TK2MSFTNGP06.phx.gbl...
>> Thank you for your reply.
>> I should have mentioned that the plan is updating statistics for all user
>> databases and so there is no database or table specifically specified. If
>> this table is not present, why would it attempt to update the statistics?
>> Thanks for any advice.
>> Regards,
>> Paul.
>>
>> "Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
>> news:9BE37C2E-55DB-4EA3-ACAC-702118FF60B8@.microsoft.com...
>> It seems that the table you want to automatically update its statistics
>> is not exist. You or someone else may be deleted it?
>> Be sure it still exists first.
>> --
>> Ekrem Önsoy
>>
>> "Paul Johnson" <paul.johnson@.gaapweb.com> wrote in message
>> news:ufUwvys7HHA.1188@.TK2MSFTNGP04.phx.gbl...
>> Good Day,
>> I am having a problem with an SQL server 2005 maintenance plan that
>> updates Statistics.
>> I keep receiving the following error:-
>> Executing the query "UPDATE STATISTICS [dbo].[STATSDATA8_27_2007]
>> WITH FULLSCAN
>> " failed with the following error: "Table 'STATSDATA8_27_2007' does not
>> exist.". Possible failure reasons: Problems with the query, "ResultSet"
>> property not set correctly, parameters not set correctly, or connection
>> not established correctly.
>> I am not sure how to resolve this and it seems to stop statistical
>> updating. Could anyone offer any help?
>> Kind Regards,
>> Paul.
>>
>>
>|||Can you try deleting the old one and creating a new one?
--
Ekrem Önsoy
"Paul Johnson" <paul.johnson@.gaapweb.com> wrote in message
news:OBh8V0w7HHA.1188@.TK2MSFTNGP04.phx.gbl...
> Thanks for your reply.
> I am assuming that if I do not actually specify the database by selecting
> "All databases" it would update the stats for all that are available. So
> if I add a new database, I do not need to edit the plan, it would just
> update stats for all databases.
> Regards,
> Paul.
> "Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
> news:4BFEB99B-50C0-4247-9E10-52BD5A6A4458@.microsoft.com...
>> Well, I did not understand exactly what you meant by saying "plan updates
>> statistics for all user databases and there is no specipic tables nor
>> databases".
>> However, when you create a maintenance plan to update statistics
>> automatically, you select databases and tables to be updated. So, there
>> you have already selected those databases\tables... And then, it appears
>> that you drop one and the job arises an error because it can not find the
>> object which you chose to be updated.
>> --
>> Ekrem Önsoy
>>
>> "Paul Johnson" <paul.johnson@.gaapweb.com> wrote in message
>> news:O1D49cv7HHA.980@.TK2MSFTNGP06.phx.gbl...
>> Thank you for your reply.
>> I should have mentioned that the plan is updating statistics for all
>> user databases and so there is no database or table specifically
>> specified. If this table is not present, why would it attempt to update
>> the statistics?
>> Thanks for any advice.
>> Regards,
>> Paul.
>>
>> "Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
>> news:9BE37C2E-55DB-4EA3-ACAC-702118FF60B8@.microsoft.com...
>> It seems that the table you want to automatically update its statistics
>> is not exist. You or someone else may be deleted it?
>> Be sure it still exists first.
>> --
>> Ekrem Önsoy
>>
>> "Paul Johnson" <paul.johnson@.gaapweb.com> wrote in message
>> news:ufUwvys7HHA.1188@.TK2MSFTNGP04.phx.gbl...
>> Good Day,
>> I am having a problem with an SQL server 2005 maintenance plan that
>> updates Statistics.
>> I keep receiving the following error:-
>> Executing the query "UPDATE STATISTICS [dbo].[STATSDATA8_27_2007]
>> WITH FULLSCAN
>> " failed with the following error: "Table 'STATSDATA8_27_2007' does
>> not exist.". Possible failure reasons: Problems with the query,
>> "ResultSet" property not set correctly, parameters not set correctly,
>> or connection not established correctly.
>> I am not sure how to resolve this and it seems to stop statistical
>> updating. Could anyone offer any help?
>> Kind Regards,
>> Paul.
>>
>>
>>
>|||Good Day,
I have recreated the maintenance plan but I still get a similar error:-
Failed:(-1073548784) Executing the query "UPDATE STATISTICS
[dbo].[STATSDATA8_30_2007] WITH FULLSCAN " failed with the following error:
"Table 'STATSDATA8_30_2007' does not exist.". Possible failure reasons:
Problems with the query, "ResultSet" property not set correctly, parameters
not set correctly, or connection not established correctly.
I am really not sure what is going on. I have not picked databases
individually, I just chose the all setting.
Paul
"Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
news:19F9B743-6704-4A02-91CA-B5C2EE1ABBAF@.microsoft.com...
> Can you try deleting the old one and creating a new one?
> --
> Ekrem Önsoy
>
> "Paul Johnson" <paul.johnson@.gaapweb.com> wrote in message
> news:OBh8V0w7HHA.1188@.TK2MSFTNGP04.phx.gbl...
>> Thanks for your reply.
>> I am assuming that if I do not actually specify the database by selecting
>> "All databases" it would update the stats for all that are available. So
>> if I add a new database, I do not need to edit the plan, it would just
>> update stats for all databases.
>> Regards,
>> Paul.
>> "Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
>> news:4BFEB99B-50C0-4247-9E10-52BD5A6A4458@.microsoft.com...
>> Well, I did not understand exactly what you meant by saying "plan
>> updates statistics for all user databases and there is no specipic
>> tables nor databases".
>> However, when you create a maintenance plan to update statistics
>> automatically, you select databases and tables to be updated. So, there
>> you have already selected those databases\tables... And then, it appears
>> that you drop one and the job arises an error because it can not find
>> the object which you chose to be updated.
>> --
>> Ekrem Önsoy
>>
>> "Paul Johnson" <paul.johnson@.gaapweb.com> wrote in message
>> news:O1D49cv7HHA.980@.TK2MSFTNGP06.phx.gbl...
>> Thank you for your reply.
>> I should have mentioned that the plan is updating statistics for all
>> user databases and so there is no database or table specifically
>> specified. If this table is not present, why would it attempt to update
>> the statistics?
>> Thanks for any advice.
>> Regards,
>> Paul.
>>
>> "Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
>> news:9BE37C2E-55DB-4EA3-ACAC-702118FF60B8@.microsoft.com...
>> It seems that the table you want to automatically update its
>> statistics is not exist. You or someone else may be deleted it?
>> Be sure it still exists first.
>> --
>> Ekrem Önsoy
>>
>> "Paul Johnson" <paul.johnson@.gaapweb.com> wrote in message
>> news:ufUwvys7HHA.1188@.TK2MSFTNGP04.phx.gbl...
>> Good Day,
>> I am having a problem with an SQL server 2005 maintenance plan that
>> updates Statistics.
>> I keep receiving the following error:-
>> Executing the query "UPDATE STATISTICS [dbo].[STATSDATA8_27_2007]
>> WITH FULLSCAN
>> " failed with the following error: "Table 'STATSDATA8_27_2007' does
>> not exist.". Possible failure reasons: Problems with the query,
>> "ResultSet" property not set correctly, parameters not set correctly,
>> or connection not established correctly.
>> I am not sure how to resolve this and it seems to stop statistical
>> updating. Could anyone offer any help?
>> Kind Regards,
>> Paul.
>>
>>
>>
>>
>

cyclic redundancy check error

I have revived a database by replacing the log file, detaching and
reattaching.
Now I get the following error when running a query against the
suspected culprit table:
Server: Msg 823, Level 24, State 2, Procedure LocationCountbyCountry,
Line 3
I/O error 23(Data error (cyclic redundancy check).) detected during
read at offset 0x00000181ab0000 in file 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\myDatabaseName.mdf'.
Connection Broken
Is there anything I can do to fix this?
Try doing a DBCC CHECKTABLE on that table and see what it reports. Post the
results here.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"laurenq uantrell" <laurenquantrell@.hotmail.com> wrote in message
news:1135611827.501973.78400@.g14g2000cwa.googlegro ups.com...
I have revived a database by replacing the log file, detaching and
reattaching.
Now I get the following error when running a query against the
suspected culprit table:
Server: Msg 823, Level 24, State 2, Procedure LocationCountbyCountry,
Line 3
I/O error 23(Data error (cyclic redundancy check).) detected during
read at offset 0x00000181ab0000 in file 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\myDatabaseName.mdf'.
Connection Broken
Is there anything I can do to fix this?
|||Whew! 45 minutes running DBCC CHECKTABLE produced these results:
Server: Msg 8966, Level 16, State 2, Procedure sprocTableCheck, Line 2
Could not read and latch page (1:789856) with latch type UP.
1(Incorrect function.) failed.
Server: Msg 8966, Level 16, State 1, Procedure sprocTableCheck, Line 2
Could not read and latch page (1:789857) with latch type UP.
1(Incorrect function.) failed.
Server: Msg 8966, Level 16, State 1, Procedure sprocTableCheck, Line 2
Could not read and latch page (1:789858) with latch type UP.
1(Incorrect function.) failed.
Server: Msg 8966, Level 16, State 1, Procedure sprocTableCheck, Line 2
Could not read and latch page (1:789859) with latch type UP.
1(Incorrect function.) failed.
Server: Msg 8966, Level 16, State 1, Procedure sprocTableCheck, Line 2
Could not read and latch page (1:789860) with latch type UP.
1(Incorrect function.) failed.
Server: Msg 8966, Level 16, State 1, Procedure sprocTableCheck, Line 2
Could not read and latch page (1:789861) with latch type UP.
1(Incorrect function.) failed.
Server: Msg 8966, Level 16, State 1, Procedure sprocTableCheck, Line 2
Could not read and latch page (1:789862) with latch type UP.
1(Incorrect function.) failed.
Server: Msg 8966, Level 16, State 1, Procedure sprocTableCheck, Line 2
Could not read and latch page (1:789863) with latch type UP.
1(Incorrect function.) failed.
Server: Msg 2533, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Page (1:789856) allocated to object ID 1027899129, index
ID 0 was not seen. Page may be invalid or have incorrect object ID
information in its header.
Server: Msg 2533, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Page (1:789857) allocated to object ID 1027899129, index
ID 0 was not seen. Page may be invalid or have incorrect object ID
information in its header.
Server: Msg 2533, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Page (1:789858) allocated to object ID 1027899129, index
ID 0 was not seen. Page may be invalid or have incorrect object ID
information in its header.
CHECKTABLE found 0 allocation errors and 8 consistency errors not
associated with any single object.
DBCC results for 'myTableName'.
Server: Msg 2533, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Page (1:789859) allocated to object ID 1027899129, index
ID 0 was not seen. Page may be invalid or have incorrect object ID
information in its header.
Server: Msg 2533, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Page (1:789860) allocated to object ID 1027899129, index
ID 0 was not seen. Page may be invalid or have incorrect object ID
information in its header.
Server: Msg 2533, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Page (1:789861) allocated to object ID 1027899129, index
ID 0 was not seen. Page may be invalid or have incorrect object ID
information in its header.
Server: Msg 2533, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Page (1:789862) allocated to object ID 1027899129, index
ID 0 was not seen. Page may be invalid or have incorrect object ID
information in its header.
Server: Msg 2533, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Page (1:789863) allocated to object ID 1027899129, index
ID 0 was not seen. Page may be invalid or have incorrect object ID
information in its header.
Server: Msg 8976, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Object ID 1027899129, index ID 1. Page (1:789856) was not
seen in the scan although its parent (1:789623) and previous (1:789855)
refer to it. Check any previous errors.
Server: Msg 8980, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Object ID 1027899129, index ID 1. Index node page
(1:789623), slot 234 refers to child page (1:789857) and previous child
(1:789856), but they were not encountered.
Server: Msg 8980, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Object ID 1027899129, index ID 1. Index node page
(1:789623), slot 235 refers to child page (1:789858) and previous child
(1:789857), but they were not encountered.
Server: Msg 8980, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Object ID 1027899129, index ID 1. Index node page
(1:789623), slot 236 refers to child page (1:789859) and previous child
(1:789858), but they were not encountered.
Server: Msg 8980, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Object ID 1027899129, index ID 1. Index node page
(1:789623), slot 237 refers to child page (1:789860) and previous child
(1:789859), but they were not encountered.
Server: Msg 8980, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Object ID 1027899129, index ID 1. Index node page
(1:789623), slot 238 refers to child page (1:789861) and previous child
(1:789860), but they were not encountered.
Server: Msg 8980, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Object ID 1027899129, index ID 1. Index node page
(1:789623), slot 239 refers to child page (1:789862) and previous child
(1:789861), but they were not encountered.
Server: Msg 8980, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Object ID 1027899129, index ID 1. Index node page
(1:789623), slot 240 refers to child page (1:789863) and previous child
(1:789862), but they were not encountered.
Server: Msg 8978, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Object ID 1027899129, index ID 1. Page (1:789864) is
missing a reference from previous page (1:789863). Possible chain
linkage problem.
There are 5507914 rows in 186584 pages for object 'myTableName'.
CHECKTABLE found 0 allocation errors and 17 consistency errors in table
'myTableName' (object ID 1027899129).
repair_allow_data_loss is the minimum repair level for the errors found
by DBCC CHECKTABLE (MyDatabaseName.dbo.myTableName ).
Stored Procedure: MyDatabaseName.dbo.sprocTableCheck
Return Code = -6
|||The key bit is:
"repair_allow_data_loss is the minimum repair level for the errors found"
If you run it with repair_allow_data_loss, it MAY fix your problem but it
will LIKELY cause you to lose data.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"laurenq uantrell" <laurenquantrell@.hotmail.com> wrote in message
news:1135693208.073001.300030@.z14g2000cwz.googlegr oups.com...
Whew! 45 minutes running DBCC CHECKTABLE produced these results:
Server: Msg 8966, Level 16, State 2, Procedure sprocTableCheck, Line 2
Could not read and latch page (1:789856) with latch type UP.
1(Incorrect function.) failed.
Server: Msg 8966, Level 16, State 1, Procedure sprocTableCheck, Line 2
Could not read and latch page (1:789857) with latch type UP.
1(Incorrect function.) failed.
Server: Msg 8966, Level 16, State 1, Procedure sprocTableCheck, Line 2
Could not read and latch page (1:789858) with latch type UP.
1(Incorrect function.) failed.
Server: Msg 8966, Level 16, State 1, Procedure sprocTableCheck, Line 2
Could not read and latch page (1:789859) with latch type UP.
1(Incorrect function.) failed.
Server: Msg 8966, Level 16, State 1, Procedure sprocTableCheck, Line 2
Could not read and latch page (1:789860) with latch type UP.
1(Incorrect function.) failed.
Server: Msg 8966, Level 16, State 1, Procedure sprocTableCheck, Line 2
Could not read and latch page (1:789861) with latch type UP.
1(Incorrect function.) failed.
Server: Msg 8966, Level 16, State 1, Procedure sprocTableCheck, Line 2
Could not read and latch page (1:789862) with latch type UP.
1(Incorrect function.) failed.
Server: Msg 8966, Level 16, State 1, Procedure sprocTableCheck, Line 2
Could not read and latch page (1:789863) with latch type UP.
1(Incorrect function.) failed.
Server: Msg 2533, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Page (1:789856) allocated to object ID 1027899129, index
ID 0 was not seen. Page may be invalid or have incorrect object ID
information in its header.
Server: Msg 2533, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Page (1:789857) allocated to object ID 1027899129, index
ID 0 was not seen. Page may be invalid or have incorrect object ID
information in its header.
Server: Msg 2533, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Page (1:789858) allocated to object ID 1027899129, index
ID 0 was not seen. Page may be invalid or have incorrect object ID
information in its header.
CHECKTABLE found 0 allocation errors and 8 consistency errors not
associated with any single object.
DBCC results for 'myTableName'.
Server: Msg 2533, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Page (1:789859) allocated to object ID 1027899129, index
ID 0 was not seen. Page may be invalid or have incorrect object ID
information in its header.
Server: Msg 2533, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Page (1:789860) allocated to object ID 1027899129, index
ID 0 was not seen. Page may be invalid or have incorrect object ID
information in its header.
Server: Msg 2533, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Page (1:789861) allocated to object ID 1027899129, index
ID 0 was not seen. Page may be invalid or have incorrect object ID
information in its header.
Server: Msg 2533, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Page (1:789862) allocated to object ID 1027899129, index
ID 0 was not seen. Page may be invalid or have incorrect object ID
information in its header.
Server: Msg 2533, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Page (1:789863) allocated to object ID 1027899129, index
ID 0 was not seen. Page may be invalid or have incorrect object ID
information in its header.
Server: Msg 8976, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Object ID 1027899129, index ID 1. Page (1:789856) was not
seen in the scan although its parent (1:789623) and previous (1:789855)
refer to it. Check any previous errors.
Server: Msg 8980, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Object ID 1027899129, index ID 1. Index node page
(1:789623), slot 234 refers to child page (1:789857) and previous child
(1:789856), but they were not encountered.
Server: Msg 8980, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Object ID 1027899129, index ID 1. Index node page
(1:789623), slot 235 refers to child page (1:789858) and previous child
(1:789857), but they were not encountered.
Server: Msg 8980, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Object ID 1027899129, index ID 1. Index node page
(1:789623), slot 236 refers to child page (1:789859) and previous child
(1:789858), but they were not encountered.
Server: Msg 8980, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Object ID 1027899129, index ID 1. Index node page
(1:789623), slot 237 refers to child page (1:789860) and previous child
(1:789859), but they were not encountered.
Server: Msg 8980, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Object ID 1027899129, index ID 1. Index node page
(1:789623), slot 238 refers to child page (1:789861) and previous child
(1:789860), but they were not encountered.
Server: Msg 8980, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Object ID 1027899129, index ID 1. Index node page
(1:789623), slot 239 refers to child page (1:789862) and previous child
(1:789861), but they were not encountered.
Server: Msg 8980, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Object ID 1027899129, index ID 1. Index node page
(1:789623), slot 240 refers to child page (1:789863) and previous child
(1:789862), but they were not encountered.
Server: Msg 8978, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Object ID 1027899129, index ID 1. Page (1:789864) is
missing a reference from previous page (1:789863). Possible chain
linkage problem.
There are 5507914 rows in 186584 pages for object 'myTableName'.
CHECKTABLE found 0 allocation errors and 17 consistency errors in table
'myTableName' (object ID 1027899129).
repair_allow_data_loss is the minimum repair level for the errors found
by DBCC CHECKTABLE (MyDatabaseName.dbo.myTableName ).
Stored Procedure: MyDatabaseName.dbo.sprocTableCheck
Return Code = -6
|||After running DBCC CHECKTABLE I get these results:
Server: Msg 8966, Level 16, State 2, Procedure sprocTableCheck, Line 2
Could not read and latch page (1:789856) with latch type UP.
1(Incorrect function.) failed.
Server: Msg 8966, Level 16, State 1, Procedure sprocTableCheck, Line 2
Could not read and latch page (1:789857) with latch type UP.
1(Incorrect function.) failed.
Server: Msg 8966, Level 16, State 1, Procedure sprocTableCheck, Line 2
Could not read and latch page (1:789858) with latch type UP.
1(Incorrect function.) failed.
Server: Msg 8966, Level 16, State 1, Procedure sprocTableCheck, Line 2
Could not read and latch page (1:789859) with latch type UP.
1(Incorrect function.) failed.
Server: Msg 8966, Level 16, State 1, Procedure sprocTableCheck, Line 2
Could not read and latch page (1:789860) with latch type UP.
1(Incorrect function.) failed.
Server: Msg 8966, Level 16, State 1, Procedure sprocTableCheck, Line 2
Could not read and latch page (1:789861) with latch type UP.
1(Incorrect function.) failed.
Server: Msg 8966, Level 16, State 1, Procedure sprocTableCheck, Line 2
Could not read and latch page (1:789862) with latch type UP.
1(Incorrect function.) failed.
Server: Msg 8966, Level 16, State 1, Procedure sprocTableCheck, Line 2
Could not read and latch page (1:789863) with latch type UP.
1(Incorrect function.) failed.
Server: Msg 2533, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Page (1:789856) allocated to object ID 1027899129, index
ID 0 was not seen. Page may be invalid or have incorrect object ID
information in its header.
Server: Msg 2533, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Page (1:789857) allocated to object ID 1027899129, index
ID 0 was not seen. Page may be invalid or have incorrect object ID
information in its header.
Server: Msg 2533, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Page (1:789858) allocated to object ID 1027899129, index
ID 0 was not seen. Page may be invalid or have incorrect object ID
information in its header.
CHECKTABLE found 0 allocation errors and 8 consistency errors not
associated with any single object.
DBCC results for 'myTableName'.
Server: Msg 2533, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Page (1:789859) allocated to object ID 1027899129, index
ID 0 was not seen. Page may be invalid or have incorrect object ID
information in its header.
Server: Msg 2533, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Page (1:789860) allocated to object ID 1027899129, index
ID 0 was not seen. Page may be invalid or have incorrect object ID
information in its header.
Server: Msg 2533, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Page (1:789861) allocated to object ID 1027899129, index
ID 0 was not seen. Page may be invalid or have incorrect object ID
information in its header.
Server: Msg 2533, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Page (1:789862) allocated to object ID 1027899129, index
ID 0 was not seen. Page may be invalid or have incorrect object ID
information in its header.
Server: Msg 2533, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Page (1:789863) allocated to object ID 1027899129, index
ID 0 was not seen. Page may be invalid or have incorrect object ID
information in its header.
Server: Msg 8976, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Object ID 1027899129, index ID 1. Page (1:789856) was not
seen in the scan although its parent (1:789623) and previous (1:789855)
refer to it. Check any previous errors.
Server: Msg 8980, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Object ID 1027899129, index ID 1. Index node page
(1:789623), slot 234 refers to child page (1:789857) and previous child
(1:789856), but they were not encountered.
Server: Msg 8980, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Object ID 1027899129, index ID 1. Index node page
(1:789623), slot 235 refers to child page (1:789858) and previous child
(1:789857), but they were not encountered.
Server: Msg 8980, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Object ID 1027899129, index ID 1. Index node page
(1:789623), slot 236 refers to child page (1:789859) and previous child
(1:789858), but they were not encountered.
Server: Msg 8980, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Object ID 1027899129, index ID 1. Index node page
(1:789623), slot 237 refers to child page (1:789860) and previous child
(1:789859), but they were not encountered.
Server: Msg 8980, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Object ID 1027899129, index ID 1. Index node page
(1:789623), slot 238 refers to child page (1:789861) and previous child
(1:789860), but they were not encountered.
Server: Msg 8980, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Object ID 1027899129, index ID 1. Index node page
(1:789623), slot 239 refers to child page (1:789862) and previous child
(1:789861), but they were not encountered.
Server: Msg 8980, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Object ID 1027899129, index ID 1. Index node page
(1:789623), slot 240 refers to child page (1:789863) and previous child
(1:789862), but they were not encountered.
Server: Msg 8978, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Object ID 1027899129, index ID 1. Page (1:789864) is
missing a reference from previous page (1:789863). Possible chain
linkage problem.
There are 5507914 rows in 186584 pages for object 'myTableName'.
CHECKTABLE found 0 allocation errors and 17 consistency errors in table
'myTableName' (object ID 1027899129).
repair_allow_data_loss is the minimum repair level for the errors found
by DBCC CHECKTABLE (MyDatabaseName.dbo.myTableName ).
Stored Procedure: MyDatabaseName.dbo.sprocTableCheck
Return Code = -6
|||Is that with repair_allow_data_loss?
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"laurenq uantrell" <laurenquantrell@.hotmail.com> wrote in message
news:1135704934.219295.29650@.g49g2000cwa.googlegro ups.com...
After running DBCC CHECKTABLE I get these results:
Server: Msg 8966, Level 16, State 2, Procedure sprocTableCheck, Line 2
Could not read and latch page (1:789856) with latch type UP.
1(Incorrect function.) failed.
Server: Msg 8966, Level 16, State 1, Procedure sprocTableCheck, Line 2
Could not read and latch page (1:789857) with latch type UP.
1(Incorrect function.) failed.
Server: Msg 8966, Level 16, State 1, Procedure sprocTableCheck, Line 2
Could not read and latch page (1:789858) with latch type UP.
1(Incorrect function.) failed.
Server: Msg 8966, Level 16, State 1, Procedure sprocTableCheck, Line 2
Could not read and latch page (1:789859) with latch type UP.
1(Incorrect function.) failed.
Server: Msg 8966, Level 16, State 1, Procedure sprocTableCheck, Line 2
Could not read and latch page (1:789860) with latch type UP.
1(Incorrect function.) failed.
Server: Msg 8966, Level 16, State 1, Procedure sprocTableCheck, Line 2
Could not read and latch page (1:789861) with latch type UP.
1(Incorrect function.) failed.
Server: Msg 8966, Level 16, State 1, Procedure sprocTableCheck, Line 2
Could not read and latch page (1:789862) with latch type UP.
1(Incorrect function.) failed.
Server: Msg 8966, Level 16, State 1, Procedure sprocTableCheck, Line 2
Could not read and latch page (1:789863) with latch type UP.
1(Incorrect function.) failed.
Server: Msg 2533, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Page (1:789856) allocated to object ID 1027899129, index
ID 0 was not seen. Page may be invalid or have incorrect object ID
information in its header.
Server: Msg 2533, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Page (1:789857) allocated to object ID 1027899129, index
ID 0 was not seen. Page may be invalid or have incorrect object ID
information in its header.
Server: Msg 2533, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Page (1:789858) allocated to object ID 1027899129, index
ID 0 was not seen. Page may be invalid or have incorrect object ID
information in its header.
CHECKTABLE found 0 allocation errors and 8 consistency errors not
associated with any single object.
DBCC results for 'myTableName'.
Server: Msg 2533, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Page (1:789859) allocated to object ID 1027899129, index
ID 0 was not seen. Page may be invalid or have incorrect object ID
information in its header.
Server: Msg 2533, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Page (1:789860) allocated to object ID 1027899129, index
ID 0 was not seen. Page may be invalid or have incorrect object ID
information in its header.
Server: Msg 2533, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Page (1:789861) allocated to object ID 1027899129, index
ID 0 was not seen. Page may be invalid or have incorrect object ID
information in its header.
Server: Msg 2533, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Page (1:789862) allocated to object ID 1027899129, index
ID 0 was not seen. Page may be invalid or have incorrect object ID
information in its header.
Server: Msg 2533, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Page (1:789863) allocated to object ID 1027899129, index
ID 0 was not seen. Page may be invalid or have incorrect object ID
information in its header.
Server: Msg 8976, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Object ID 1027899129, index ID 1. Page (1:789856) was not
seen in the scan although its parent (1:789623) and previous (1:789855)
refer to it. Check any previous errors.
Server: Msg 8980, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Object ID 1027899129, index ID 1. Index node page
(1:789623), slot 234 refers to child page (1:789857) and previous child
(1:789856), but they were not encountered.
Server: Msg 8980, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Object ID 1027899129, index ID 1. Index node page
(1:789623), slot 235 refers to child page (1:789858) and previous child
(1:789857), but they were not encountered.
Server: Msg 8980, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Object ID 1027899129, index ID 1. Index node page
(1:789623), slot 236 refers to child page (1:789859) and previous child
(1:789858), but they were not encountered.
Server: Msg 8980, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Object ID 1027899129, index ID 1. Index node page
(1:789623), slot 237 refers to child page (1:789860) and previous child
(1:789859), but they were not encountered.
Server: Msg 8980, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Object ID 1027899129, index ID 1. Index node page
(1:789623), slot 238 refers to child page (1:789861) and previous child
(1:789860), but they were not encountered.
Server: Msg 8980, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Object ID 1027899129, index ID 1. Index node page
(1:789623), slot 239 refers to child page (1:789862) and previous child
(1:789861), but they were not encountered.
Server: Msg 8980, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Object ID 1027899129, index ID 1. Index node page
(1:789623), slot 240 refers to child page (1:789863) and previous child
(1:789862), but they were not encountered.
Server: Msg 8978, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Object ID 1027899129, index ID 1. Page (1:789864) is
missing a reference from previous page (1:789863). Possible chain
linkage problem.
There are 5507914 rows in 186584 pages for object 'myTableName'.
CHECKTABLE found 0 allocation errors and 17 consistency errors in table
'myTableName' (object ID 1027899129).
repair_allow_data_loss is the minimum repair level for the errors found
by DBCC CHECKTABLE (MyDatabaseName.dbo.myTableName ).
Stored Procedure: MyDatabaseName.dbo.sprocTableCheck
Return Code = -6
|||Tom,
I have run DBCC CHECKTABLE with repair_allow_data_loss (it failed to
repair on the non-data-loss options) and it seems to have reconstructed
this very large table. Running row counts on various Group Bys it
appears that the number of rows are correct though I have no way of
knowing if column data has been currupted since this is a many millions
of rows table.
|||If you have an older copy of the DB, perhaps you can do a column-by-column
comparison. At least that would verify the older stuff.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"laurenq uantrell" <laurenquantrell@.hotmail.com> wrote in message
news:1135881183.968306.281210@.g44g2000cwa.googlegr oups.com...
Tom,
I have run DBCC CHECKTABLE with repair_allow_data_loss (it failed to
repair on the non-data-loss options) and it seems to have reconstructed
this very large table. Running row counts on various Group Bys it
appears that the number of rows are correct though I have no way of
knowing if column data has been currupted since this is a many millions
of rows table.

cyclic redundancy check error

I have revived a database by replacing the log file, detaching and
reattaching.
Now I get the following error when running a query against the
suspected culprit table:
Server: Msg 823, Level 24, State 2, Procedure LocationCountbyCountry,
Line 3
I/O error 23(Data error (cyclic redundancy check).) detected during
read at offset 0x00000181ab0000 in file 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\myDatabaseName.mdf'.
Connection Broken
Is there anything I can do to fix this?Try doing a DBCC CHECKTABLE on that table and see what it reports. Post the
results here.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"laurenq uantrell" <laurenquantrell@.hotmail.com> wrote in message
news:1135611827.501973.78400@.g14g2000cwa.googlegroups.com...
I have revived a database by replacing the log file, detaching and
reattaching.
Now I get the following error when running a query against the
suspected culprit table:
Server: Msg 823, Level 24, State 2, Procedure LocationCountbyCountry,
Line 3
I/O error 23(Data error (cyclic redundancy check).) detected during
read at offset 0x00000181ab0000 in file 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\myDatabaseName.mdf'.
Connection Broken
Is there anything I can do to fix this?|||Whew! 45 minutes running DBCC CHECKTABLE produced these results:
Server: Msg 8966, Level 16, State 2, Procedure sprocTableCheck, Line 2
Could not read and latch page (1:789856) with latch type UP.
1(Incorrect function.) failed.
Server: Msg 8966, Level 16, State 1, Procedure sprocTableCheck, Line 2
Could not read and latch page (1:789857) with latch type UP.
1(Incorrect function.) failed.
Server: Msg 8966, Level 16, State 1, Procedure sprocTableCheck, Line 2
Could not read and latch page (1:789858) with latch type UP.
1(Incorrect function.) failed.
Server: Msg 8966, Level 16, State 1, Procedure sprocTableCheck, Line 2
Could not read and latch page (1:789859) with latch type UP.
1(Incorrect function.) failed.
Server: Msg 8966, Level 16, State 1, Procedure sprocTableCheck, Line 2
Could not read and latch page (1:789860) with latch type UP.
1(Incorrect function.) failed.
Server: Msg 8966, Level 16, State 1, Procedure sprocTableCheck, Line 2
Could not read and latch page (1:789861) with latch type UP.
1(Incorrect function.) failed.
Server: Msg 8966, Level 16, State 1, Procedure sprocTableCheck, Line 2
Could not read and latch page (1:789862) with latch type UP.
1(Incorrect function.) failed.
Server: Msg 8966, Level 16, State 1, Procedure sprocTableCheck, Line 2
Could not read and latch page (1:789863) with latch type UP.
1(Incorrect function.) failed.
Server: Msg 2533, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Page (1:789856) allocated to object ID 1027899129, index
ID 0 was not seen. Page may be invalid or have incorrect object ID
information in its header.
Server: Msg 2533, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Page (1:789857) allocated to object ID 1027899129, index
ID 0 was not seen. Page may be invalid or have incorrect object ID
information in its header.
Server: Msg 2533, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Page (1:789858) allocated to object ID 1027899129, index
ID 0 was not seen. Page may be invalid or have incorrect object ID
information in its header.
CHECKTABLE found 0 allocation errors and 8 consistency errors not
associated with any single object.
DBCC results for 'myTableName'.
Server: Msg 2533, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Page (1:789859) allocated to object ID 1027899129, index
ID 0 was not seen. Page may be invalid or have incorrect object ID
information in its header.
Server: Msg 2533, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Page (1:789860) allocated to object ID 1027899129, index
ID 0 was not seen. Page may be invalid or have incorrect object ID
information in its header.
Server: Msg 2533, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Page (1:789861) allocated to object ID 1027899129, index
ID 0 was not seen. Page may be invalid or have incorrect object ID
information in its header.
Server: Msg 2533, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Page (1:789862) allocated to object ID 1027899129, index
ID 0 was not seen. Page may be invalid or have incorrect object ID
information in its header.
Server: Msg 2533, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Page (1:789863) allocated to object ID 1027899129, index
ID 0 was not seen. Page may be invalid or have incorrect object ID
information in its header.
Server: Msg 8976, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Object ID 1027899129, index ID 1. Page (1:789856) was not
seen in the scan although its parent (1:789623) and previous (1:789855)
refer to it. Check any previous errors.
Server: Msg 8980, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Object ID 1027899129, index ID 1. Index node page
(1:789623), slot 234 refers to child page (1:789857) and previous child
(1:789856), but they were not encountered.
Server: Msg 8980, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Object ID 1027899129, index ID 1. Index node page
(1:789623), slot 235 refers to child page (1:789858) and previous child
(1:789857), but they were not encountered.
Server: Msg 8980, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Object ID 1027899129, index ID 1. Index node page
(1:789623), slot 236 refers to child page (1:789859) and previous child
(1:789858), but they were not encountered.
Server: Msg 8980, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Object ID 1027899129, index ID 1. Index node page
(1:789623), slot 237 refers to child page (1:789860) and previous child
(1:789859), but they were not encountered.
Server: Msg 8980, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Object ID 1027899129, index ID 1. Index node page
(1:789623), slot 238 refers to child page (1:789861) and previous child
(1:789860), but they were not encountered.
Server: Msg 8980, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Object ID 1027899129, index ID 1. Index node page
(1:789623), slot 239 refers to child page (1:789862) and previous child
(1:789861), but they were not encountered.
Server: Msg 8980, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Object ID 1027899129, index ID 1. Index node page
(1:789623), slot 240 refers to child page (1:789863) and previous child
(1:789862), but they were not encountered.
Server: Msg 8978, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Object ID 1027899129, index ID 1. Page (1:789864) is
missing a reference from previous page (1:789863). Possible chain
linkage problem.
There are 5507914 rows in 186584 pages for object 'myTableName'.
CHECKTABLE found 0 allocation errors and 17 consistency errors in table
'myTableName' (object ID 1027899129).
repair_allow_data_loss is the minimum repair level for the errors found
by DBCC CHECKTABLE (MyDatabaseName.dbo.myTableName ).
Stored Procedure: MyDatabaseName.dbo.sprocTableCheck
Return Code = -6|||The key bit is:
"repair_allow_data_loss is the minimum repair level for the errors found"
If you run it with repair_allow_data_loss, it MAY fix your problem but it
will LIKELY cause you to lose data.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"laurenq uantrell" <laurenquantrell@.hotmail.com> wrote in message
news:1135693208.073001.300030@.z14g2000cwz.googlegroups.com...
Whew! 45 minutes running DBCC CHECKTABLE produced these results:
Server: Msg 8966, Level 16, State 2, Procedure sprocTableCheck, Line 2
Could not read and latch page (1:789856) with latch type UP.
1(Incorrect function.) failed.
Server: Msg 8966, Level 16, State 1, Procedure sprocTableCheck, Line 2
Could not read and latch page (1:789857) with latch type UP.
1(Incorrect function.) failed.
Server: Msg 8966, Level 16, State 1, Procedure sprocTableCheck, Line 2
Could not read and latch page (1:789858) with latch type UP.
1(Incorrect function.) failed.
Server: Msg 8966, Level 16, State 1, Procedure sprocTableCheck, Line 2
Could not read and latch page (1:789859) with latch type UP.
1(Incorrect function.) failed.
Server: Msg 8966, Level 16, State 1, Procedure sprocTableCheck, Line 2
Could not read and latch page (1:789860) with latch type UP.
1(Incorrect function.) failed.
Server: Msg 8966, Level 16, State 1, Procedure sprocTableCheck, Line 2
Could not read and latch page (1:789861) with latch type UP.
1(Incorrect function.) failed.
Server: Msg 8966, Level 16, State 1, Procedure sprocTableCheck, Line 2
Could not read and latch page (1:789862) with latch type UP.
1(Incorrect function.) failed.
Server: Msg 8966, Level 16, State 1, Procedure sprocTableCheck, Line 2
Could not read and latch page (1:789863) with latch type UP.
1(Incorrect function.) failed.
Server: Msg 2533, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Page (1:789856) allocated to object ID 1027899129, index
ID 0 was not seen. Page may be invalid or have incorrect object ID
information in its header.
Server: Msg 2533, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Page (1:789857) allocated to object ID 1027899129, index
ID 0 was not seen. Page may be invalid or have incorrect object ID
information in its header.
Server: Msg 2533, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Page (1:789858) allocated to object ID 1027899129, index
ID 0 was not seen. Page may be invalid or have incorrect object ID
information in its header.
CHECKTABLE found 0 allocation errors and 8 consistency errors not
associated with any single object.
DBCC results for 'myTableName'.
Server: Msg 2533, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Page (1:789859) allocated to object ID 1027899129, index
ID 0 was not seen. Page may be invalid or have incorrect object ID
information in its header.
Server: Msg 2533, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Page (1:789860) allocated to object ID 1027899129, index
ID 0 was not seen. Page may be invalid or have incorrect object ID
information in its header.
Server: Msg 2533, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Page (1:789861) allocated to object ID 1027899129, index
ID 0 was not seen. Page may be invalid or have incorrect object ID
information in its header.
Server: Msg 2533, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Page (1:789862) allocated to object ID 1027899129, index
ID 0 was not seen. Page may be invalid or have incorrect object ID
information in its header.
Server: Msg 2533, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Page (1:789863) allocated to object ID 1027899129, index
ID 0 was not seen. Page may be invalid or have incorrect object ID
information in its header.
Server: Msg 8976, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Object ID 1027899129, index ID 1. Page (1:789856) was not
seen in the scan although its parent (1:789623) and previous (1:789855)
refer to it. Check any previous errors.
Server: Msg 8980, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Object ID 1027899129, index ID 1. Index node page
(1:789623), slot 234 refers to child page (1:789857) and previous child
(1:789856), but they were not encountered.
Server: Msg 8980, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Object ID 1027899129, index ID 1. Index node page
(1:789623), slot 235 refers to child page (1:789858) and previous child
(1:789857), but they were not encountered.
Server: Msg 8980, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Object ID 1027899129, index ID 1. Index node page
(1:789623), slot 236 refers to child page (1:789859) and previous child
(1:789858), but they were not encountered.
Server: Msg 8980, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Object ID 1027899129, index ID 1. Index node page
(1:789623), slot 237 refers to child page (1:789860) and previous child
(1:789859), but they were not encountered.
Server: Msg 8980, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Object ID 1027899129, index ID 1. Index node page
(1:789623), slot 238 refers to child page (1:789861) and previous child
(1:789860), but they were not encountered.
Server: Msg 8980, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Object ID 1027899129, index ID 1. Index node page
(1:789623), slot 239 refers to child page (1:789862) and previous child
(1:789861), but they were not encountered.
Server: Msg 8980, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Object ID 1027899129, index ID 1. Index node page
(1:789623), slot 240 refers to child page (1:789863) and previous child
(1:789862), but they were not encountered.
Server: Msg 8978, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Object ID 1027899129, index ID 1. Page (1:789864) is
missing a reference from previous page (1:789863). Possible chain
linkage problem.
There are 5507914 rows in 186584 pages for object 'myTableName'.
CHECKTABLE found 0 allocation errors and 17 consistency errors in table
'myTableName' (object ID 1027899129).
repair_allow_data_loss is the minimum repair level for the errors found
by DBCC CHECKTABLE (MyDatabaseName.dbo.myTableName ).
Stored Procedure: MyDatabaseName.dbo.sprocTableCheck
Return Code = -6|||After running DBCC CHECKTABLE I get these results:
Server: Msg 8966, Level 16, State 2, Procedure sprocTableCheck, Line 2
Could not read and latch page (1:789856) with latch type UP.
1(Incorrect function.) failed.
Server: Msg 8966, Level 16, State 1, Procedure sprocTableCheck, Line 2
Could not read and latch page (1:789857) with latch type UP.
1(Incorrect function.) failed.
Server: Msg 8966, Level 16, State 1, Procedure sprocTableCheck, Line 2
Could not read and latch page (1:789858) with latch type UP.
1(Incorrect function.) failed.
Server: Msg 8966, Level 16, State 1, Procedure sprocTableCheck, Line 2
Could not read and latch page (1:789859) with latch type UP.
1(Incorrect function.) failed.
Server: Msg 8966, Level 16, State 1, Procedure sprocTableCheck, Line 2
Could not read and latch page (1:789860) with latch type UP.
1(Incorrect function.) failed.
Server: Msg 8966, Level 16, State 1, Procedure sprocTableCheck, Line 2
Could not read and latch page (1:789861) with latch type UP.
1(Incorrect function.) failed.
Server: Msg 8966, Level 16, State 1, Procedure sprocTableCheck, Line 2
Could not read and latch page (1:789862) with latch type UP.
1(Incorrect function.) failed.
Server: Msg 8966, Level 16, State 1, Procedure sprocTableCheck, Line 2
Could not read and latch page (1:789863) with latch type UP.
1(Incorrect function.) failed.
Server: Msg 2533, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Page (1:789856) allocated to object ID 1027899129, index
ID 0 was not seen. Page may be invalid or have incorrect object ID
information in its header.
Server: Msg 2533, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Page (1:789857) allocated to object ID 1027899129, index
ID 0 was not seen. Page may be invalid or have incorrect object ID
information in its header.
Server: Msg 2533, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Page (1:789858) allocated to object ID 1027899129, index
ID 0 was not seen. Page may be invalid or have incorrect object ID
information in its header.
CHECKTABLE found 0 allocation errors and 8 consistency errors not
associated with any single object.
DBCC results for 'myTableName'.
Server: Msg 2533, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Page (1:789859) allocated to object ID 1027899129, index
ID 0 was not seen. Page may be invalid or have incorrect object ID
information in its header.
Server: Msg 2533, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Page (1:789860) allocated to object ID 1027899129, index
ID 0 was not seen. Page may be invalid or have incorrect object ID
information in its header.
Server: Msg 2533, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Page (1:789861) allocated to object ID 1027899129, index
ID 0 was not seen. Page may be invalid or have incorrect object ID
information in its header.
Server: Msg 2533, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Page (1:789862) allocated to object ID 1027899129, index
ID 0 was not seen. Page may be invalid or have incorrect object ID
information in its header.
Server: Msg 2533, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Page (1:789863) allocated to object ID 1027899129, index
ID 0 was not seen. Page may be invalid or have incorrect object ID
information in its header.
Server: Msg 8976, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Object ID 1027899129, index ID 1. Page (1:789856) was not
seen in the scan although its parent (1:789623) and previous (1:789855)
refer to it. Check any previous errors.
Server: Msg 8980, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Object ID 1027899129, index ID 1. Index node page
(1:789623), slot 234 refers to child page (1:789857) and previous child
(1:789856), but they were not encountered.
Server: Msg 8980, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Object ID 1027899129, index ID 1. Index node page
(1:789623), slot 235 refers to child page (1:789858) and previous child
(1:789857), but they were not encountered.
Server: Msg 8980, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Object ID 1027899129, index ID 1. Index node page
(1:789623), slot 236 refers to child page (1:789859) and previous child
(1:789858), but they were not encountered.
Server: Msg 8980, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Object ID 1027899129, index ID 1. Index node page
(1:789623), slot 237 refers to child page (1:789860) and previous child
(1:789859), but they were not encountered.
Server: Msg 8980, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Object ID 1027899129, index ID 1. Index node page
(1:789623), slot 238 refers to child page (1:789861) and previous child
(1:789860), but they were not encountered.
Server: Msg 8980, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Object ID 1027899129, index ID 1. Index node page
(1:789623), slot 239 refers to child page (1:789862) and previous child
(1:789861), but they were not encountered.
Server: Msg 8980, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Object ID 1027899129, index ID 1. Index node page
(1:789623), slot 240 refers to child page (1:789863) and previous child
(1:789862), but they were not encountered.
Server: Msg 8978, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Object ID 1027899129, index ID 1. Page (1:789864) is
missing a reference from previous page (1:789863). Possible chain
linkage problem.
There are 5507914 rows in 186584 pages for object 'myTableName'.
CHECKTABLE found 0 allocation errors and 17 consistency errors in table
'myTableName' (object ID 1027899129).
repair_allow_data_loss is the minimum repair level for the errors found
by DBCC CHECKTABLE (MyDatabaseName.dbo.myTableName ).
Stored Procedure: MyDatabaseName.dbo.sprocTableCheck
Return Code = -6|||Is that with repair_allow_data_loss?
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"laurenq uantrell" <laurenquantrell@.hotmail.com> wrote in message
news:1135704934.219295.29650@.g49g2000cwa.googlegroups.com...
After running DBCC CHECKTABLE I get these results:
Server: Msg 8966, Level 16, State 2, Procedure sprocTableCheck, Line 2
Could not read and latch page (1:789856) with latch type UP.
1(Incorrect function.) failed.
Server: Msg 8966, Level 16, State 1, Procedure sprocTableCheck, Line 2
Could not read and latch page (1:789857) with latch type UP.
1(Incorrect function.) failed.
Server: Msg 8966, Level 16, State 1, Procedure sprocTableCheck, Line 2
Could not read and latch page (1:789858) with latch type UP.
1(Incorrect function.) failed.
Server: Msg 8966, Level 16, State 1, Procedure sprocTableCheck, Line 2
Could not read and latch page (1:789859) with latch type UP.
1(Incorrect function.) failed.
Server: Msg 8966, Level 16, State 1, Procedure sprocTableCheck, Line 2
Could not read and latch page (1:789860) with latch type UP.
1(Incorrect function.) failed.
Server: Msg 8966, Level 16, State 1, Procedure sprocTableCheck, Line 2
Could not read and latch page (1:789861) with latch type UP.
1(Incorrect function.) failed.
Server: Msg 8966, Level 16, State 1, Procedure sprocTableCheck, Line 2
Could not read and latch page (1:789862) with latch type UP.
1(Incorrect function.) failed.
Server: Msg 8966, Level 16, State 1, Procedure sprocTableCheck, Line 2
Could not read and latch page (1:789863) with latch type UP.
1(Incorrect function.) failed.
Server: Msg 2533, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Page (1:789856) allocated to object ID 1027899129, index
ID 0 was not seen. Page may be invalid or have incorrect object ID
information in its header.
Server: Msg 2533, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Page (1:789857) allocated to object ID 1027899129, index
ID 0 was not seen. Page may be invalid or have incorrect object ID
information in its header.
Server: Msg 2533, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Page (1:789858) allocated to object ID 1027899129, index
ID 0 was not seen. Page may be invalid or have incorrect object ID
information in its header.
CHECKTABLE found 0 allocation errors and 8 consistency errors not
associated with any single object.
DBCC results for 'myTableName'.
Server: Msg 2533, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Page (1:789859) allocated to object ID 1027899129, index
ID 0 was not seen. Page may be invalid or have incorrect object ID
information in its header.
Server: Msg 2533, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Page (1:789860) allocated to object ID 1027899129, index
ID 0 was not seen. Page may be invalid or have incorrect object ID
information in its header.
Server: Msg 2533, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Page (1:789861) allocated to object ID 1027899129, index
ID 0 was not seen. Page may be invalid or have incorrect object ID
information in its header.
Server: Msg 2533, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Page (1:789862) allocated to object ID 1027899129, index
ID 0 was not seen. Page may be invalid or have incorrect object ID
information in its header.
Server: Msg 2533, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Page (1:789863) allocated to object ID 1027899129, index
ID 0 was not seen. Page may be invalid or have incorrect object ID
information in its header.
Server: Msg 8976, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Object ID 1027899129, index ID 1. Page (1:789856) was not
seen in the scan although its parent (1:789623) and previous (1:789855)
refer to it. Check any previous errors.
Server: Msg 8980, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Object ID 1027899129, index ID 1. Index node page
(1:789623), slot 234 refers to child page (1:789857) and previous child
(1:789856), but they were not encountered.
Server: Msg 8980, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Object ID 1027899129, index ID 1. Index node page
(1:789623), slot 235 refers to child page (1:789858) and previous child
(1:789857), but they were not encountered.
Server: Msg 8980, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Object ID 1027899129, index ID 1. Index node page
(1:789623), slot 236 refers to child page (1:789859) and previous child
(1:789858), but they were not encountered.
Server: Msg 8980, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Object ID 1027899129, index ID 1. Index node page
(1:789623), slot 237 refers to child page (1:789860) and previous child
(1:789859), but they were not encountered.
Server: Msg 8980, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Object ID 1027899129, index ID 1. Index node page
(1:789623), slot 238 refers to child page (1:789861) and previous child
(1:789860), but they were not encountered.
Server: Msg 8980, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Object ID 1027899129, index ID 1. Index node page
(1:789623), slot 239 refers to child page (1:789862) and previous child
(1:789861), but they were not encountered.
Server: Msg 8980, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Object ID 1027899129, index ID 1. Index node page
(1:789623), slot 240 refers to child page (1:789863) and previous child
(1:789862), but they were not encountered.
Server: Msg 8978, Level 16, State 1, Procedure sprocTableCheck, Line 2
Table error: Object ID 1027899129, index ID 1. Page (1:789864) is
missing a reference from previous page (1:789863). Possible chain
linkage problem.
There are 5507914 rows in 186584 pages for object 'myTableName'.
CHECKTABLE found 0 allocation errors and 17 consistency errors in table
'myTableName' (object ID 1027899129).
repair_allow_data_loss is the minimum repair level for the errors found
by DBCC CHECKTABLE (MyDatabaseName.dbo.myTableName ).
Stored Procedure: MyDatabaseName.dbo.sprocTableCheck
Return Code = -6|||Tom,
I have run DBCC CHECKTABLE with repair_allow_data_loss (it failed to
repair on the non-data-loss options) and it seems to have reconstructed
this very large table. Running row counts on various Group Bys it
appears that the number of rows are correct though I have no way of
knowing if column data has been currupted since this is a many millions
of rows table.|||If you have an older copy of the DB, perhaps you can do a column-by-column
comparison. At least that would verify the older stuff.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"laurenq uantrell" <laurenquantrell@.hotmail.com> wrote in message
news:1135881183.968306.281210@.g44g2000cwa.googlegroups.com...
Tom,
I have run DBCC CHECKTABLE with repair_allow_data_loss (it failed to
repair on the non-data-loss options) and it seems to have reconstructed
this very large table. Running row counts on various Group Bys it
appears that the number of rows are correct though I have no way of
knowing if column data has been currupted since this is a many millions
of rows table.