Foreach loop with XML Source failure

I can't import from XML files using a foreach loop. I load an XML file with a generated XSD. When I map the file to the table it has no errors. If I now go back and change to a different XML file, I get an error:

"Error 1 Validation error. Data Flow Task: DTS.Pipeline: input column "COLUMNNAME" (129) has lineage ID 2115 that was not previously used in the Data Flow task. Package.dtsx 0 0"

This is for testing purposes. When I run the foreach loop it does not work. Ironically, I do the exact same thing in another foreach loop with a completely different XML and it works fine.

Here is the broken XSD:

<?xml version="1.0"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="ComputerStatus">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" maxOccurs="unbounded" name="computer">
<xs:complexType>
<xs:attribute name="GUID" type="xs:string" use="optional" />
<xs:attribute name="WSUSServer" type="xs:string" use="optional" />
<xs:attribute name="WSUSGroup" type="xs:string" use="optional" />
<xs:attribute name="computerName" type="xs:string" use="optional" />
<xs:attribute name="OSBuild" type="xs:unsignedShort" use="optional" />
<xs:attribute name="OSSP" type="xs:unsignedByte" use="optional" />
<xs:attribute name="Model" type="xs:string" use="optional" />
<xs:attribute name="Make" type="xs:string" use="optional" />
<xs:attribute name="BIOS" type="xs:string" use="optional" />
<xs:attribute name="Processor" type="xs:string" use="optional" />
<xs:attribute name="LastReportedStatus" type="xs:string" use="optional" />
<xs:attribute name="LastSyncTime" type="xs:string" use="optional" />
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>

Help. Please. What have I done wrong. I imagine there is a flaw in my XML, but I can't pinpoint it.

Here is a sample of the XML file:

<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<ComputerStatus>
<computerCount QTY="1" />
<computer GUID="edc2b6a5-5d86-467c-8c89-43fa18ae5921" WSUSServer="WSUS" WSUSGroup="THIS" computerName="COMPUTER" OSBuild="3790" OSSP="1" Model="COMPUTERTYPE" Make="HP" BIOS="1" Processor="x86" LastReportedStatus="10/25/2006 12:00:49 PM" LastSyncTime="10/25/2006 11:57:09 AM" />
</ComputerStatus>

That error says INPUT column, so I doubt its coming from the XML source adapter. An XML source adapter has output columns (and external metadata columns). Are you certain the error is with the source adapter and not some other pipeline component?

As an aside, that XSD and xml will work just fine in without regard the surrounding container. The XSD is not broken so far as use in the SSIS source adapter is concerned, although it does not contain the <computerCount> element.|||

Thank you for the feedback, but I think that I failed to mention that yes, the next thing that I send the XML Source to, whether it be a sort, derived column, an ole db destination, etc... is where the failure shows up.

Take for instance the case where I put the XML Source to an OLE DB Destination. I use a file and set the columns via regular mapping. Then I go back and set the XML Source to another file to be sure it continues to work and I get the error:

Error 1 Validation error. Data Flow Task: DTS.Pipeline: input column "WSUSServer" (5136) has lineage ID 4776 that was not previously used in the Data Flow task. Package.dtsx 0 0

Then I go back into the Ole DB Destination and have it map using Column Names. And everything is okay again. Then go back and switch to the next file and get this error:

Error 1 Validation error. Data Flow Task: DTS.Pipeline: input column "WSUSServer" (5136) has lineage ID 5265 that was not previously used in the Data Flow task. Package.dtsx 0 0

It's a vicious cycle.

An aside, to your aside, I was messing with the XSD and took out the ComputerCount during debug.

Thank you for your help.

sql

ForEach Config validation errors

I have a Config file for a Data Flow that is utilizing a Configuration file. I have specified in the Configuration file to capture the Directory and FileSpec for the ForEach Enumeration.

When I open the package, I get the following error:

Warning 1 Warning loading ISOTypeLoss.dtsx: The package path referenced an object that cannot be found: "\Package\Foreach TC03 File.ForEachEnumerator.Properties[Directory]". This occurs when an attempt is made to resolve a package path to an object that cannot be found. i:\visual studio 2005\projects\claimtran\isotypeloss\ISOTypeLoss.dtsx 1 1

Warning 2 Warning loading ISOTypeLoss.dtsx: The package path referenced an object that cannot be found: "\Package\Foreach TC03 File.ForEachEnumerator.Properties[FileSpec]". This occurs when an attempt is made to resolve a package path to an object that cannot be found. i:\visual studio 2005\projects\claimtran\isotypeloss\ISOTypeLoss.dtsx 1 1

I have verified that these two entries are indeed in the Config file and they do point to a valid location for the Directory and File Specification. The package runs fine.

Thanks.

More information:

When I am selecting these two fields during the Configuration file wizard, the final screen that shows the summary of activity displays the following info under the Properties section:

Properties:
<Unknown>.Properties[FileSpec]
<Unknown>.Properties[Directory]
\Package.Connections[LoggingOutput].Properties[ConnectionString]
\Package.Connections[DSSStaging].Properties[ServerName]
\Package.Connections[DSS].Properties[ServerName]
\Package.Connections[Corp1Staging].Properties[ServerName]

The FileSpec and Directory properties are under the ForEach.EnumeratorProperties section.

Is there a known issue pertaining to this?

|||What build are you running?|||

KirkHaselden wrote:

What build are you running?

June CTP|||UPDATE:

We deployed the dtsx to the file system on the server, made changes to the FileSpec parameter in the config file and deployed it, then executed the package pointing to the Config file.

The package still looked for the file as specified in the dtsx XML (package), regardless of what was specified in the config.

To confirm, we renamed the input file to match the FileSpec parameter in the package and it ran successfully, finding and processing the file.

The ForEach parsing for the Enumeration objects appears to have a bug. Can someone let me know if this is a known bug and if it is going to be fixed for the next CTP release?

Thanks.|||

We attempted to recreate the Config file uinder the September CTP and are having the same problems. The Config wizard

Here is what is displayed at the bottom of the dtsConfig file:

File name:
\\MSSQLYukon\etlconfig\ISOTypeLoss.dtsConfig

Properties:
\Package.Connections[ISOTypeLossLog].Properties[ConnectionString]
\Package.Connections[DSSStaging].Properties[ServerName]
\Package.Connections[DSSStaging].Properties[InitialCatalog]
\Package.Connections[DSSStaging].Properties[ConnectionString]
<Unknown>.Properties[FileSpec]
<Unknown>.Properties[Directory]

We still get the error in the Error Window:

Warning 1 Warning loading ISOTypeLoss.dtsx: The package path referenced an object that cannot be found: "\Package\Foreach TC03 File.ForEachEnumerator.Properties[Directory]". This occurs when an attempt is made to resolve a package path to an object that cannot be found. i:\visual studio 2005\projects\claimtran\isotypeloss\ISOTypeLoss.dtsx 1 1

|||You will get this error if your config file contains references to names that don't exist elsewhere in the package. (In this case, it sounds like your config file contains references to "Foreach TC03 File", which doesn't exist in your package.) When you recreated the config file, did you actually delete the previous config file from the file system?|||

Cim Ryan wrote:

You will get this error if your config file contains references to names that don't exist elsewhere in the package. (In this case, it sounds like your config file contains references to "Foreach TC03 File", which doesn't exist in your package.) When you recreated the config file, did you actually delete the previous config file from the file system?

Absolutely. The errors I have highlighted above were copied and pasted from the confirmation screen of completing the Package Configuration Wizard and BEFORE the file is written.

The wizard itself does not recognize the setting before the file is written.

This is a HUGE issue if we are to deploy Yukon! |||

I've only been able to reproduce the error you're encountering by manually editing the config file and corrupting the name of the ForEach Loop. If you'll remove from your package all but the tasks necessary to reproduce the error (make a backup copy first, obviously), I'll take a look at the pared down package if you post it here.

|||

Cim Ryan wrote:

I've only been able to reproduce the error you're encountering by manually editing the config file and corrupting the name of the ForEach Loop. If you'll remove from your package all but the tasks necessary to reproduce the error (make a backup copy first, obviously), I'll take a look at the pared down package if you post it here.

Where in the Wizard should I be selecting the Directory and FileSpec properties? I select them under the <PackageName>/Executables/<ForEachContainerName>/Executables/EnumeratorProperties entry. In there I select "Directory" and "FileSpec".

I tried to post the XML but the Forum logged an error. Can I send the XML to you via email or post an attachment somewhere?|||Yeah, that sounds like the correct place to be specifying the properties. Go ahead and email me the package and config files.|||By the way, these are not errors, they are warnings which are non-critical. There is a property on packages called "SuppressConfigurationWarnings" that will hide these messages. They are there to simply inform you that the configuration is attempting to configure an object that is not there. So you can either remedy the situation or ignore it.
Configuration warnings should not fail the package.
K|||

KirkHaselden wrote:

By the way, these are not errors, they are warnings which are non-critical. There is a property on packages called "SuppressConfigurationWarnings" that will hide these messages. They are there to simply inform you that the configuration is attempting to configure an object that is not there. So you can either remedy the situation or ignore it.
Configuration warnings should not fail the package.
K

Kirk:

The problem is that we need to change settings in the config file to migrate the packages through Model, QA and to Production. As I posted above, I can change the settings to point to a different Directory but because the package cannot configure the setting it always uses the setting that was set when the package was designed, which is the Test directory. The only way to change this is to open the package, change the attributes during design and save them. But then it points to the Production directory, etc.

Changing the SuppressconfiurationWarnings setting will indeed hide the warning but it will not change the setting at execution time - which is the reason for having these properties in the config file in the first place.|||

Cim Ryan wrote:

Yeah, that sounds like the correct place to be specifying the properties. Go ahead and email me the package and config files.

Done. As stated, I am running September CTP.|||

I wasn't able to reproduce this failure, and Raul can no longer do so, either.

ForEach Config validation errors

I have a Config file for a Data Flow that is utilizing a Configuration file. I have specified in the Configuration file to capture the Directory and FileSpec for the ForEach Enumeration.

When I open the package, I get the following error:

Warning 1 Warning loading ISOTypeLoss.dtsx: The package path referenced an object that cannot be found: "\Package\Foreach TC03 File.ForEachEnumerator.Properties[Directory]". This occurs when an attempt is made to resolve a package path to an object that cannot be found. i:\visual studio 2005\projects\claimtran\isotypeloss\ISOTypeLoss.dtsx 1 1

Warning 2 Warning loading ISOTypeLoss.dtsx: The package path referenced an object that cannot be found: "\Package\Foreach TC03 File.ForEachEnumerator.Properties[FileSpec]". This occurs when an attempt is made to resolve a package path to an object that cannot be found. i:\visual studio 2005\projects\claimtran\isotypeloss\ISOTypeLoss.dtsx 1 1

I have verified that these two entries are indeed in the Config file and they do point to a valid location for the Directory and File Specification. The package runs fine.

Thanks.

More information:

When I am selecting these two fields during the Configuration file wizard, the final screen that shows the summary of activity displays the following info under the Properties section:

Properties:
<Unknown>.Properties[FileSpec]
<Unknown>.Properties[Directory]
\Package.Connections[LoggingOutput].Properties[ConnectionString]
\Package.Connections[DSSStaging].Properties[ServerName]
\Package.Connections[DSS].Properties[ServerName]
\Package.Connections[Corp1Staging].Properties[ServerName]

The FileSpec and Directory properties are under the ForEach.EnumeratorProperties section.

Is there a known issue pertaining to this?

|||What build are you running?|||

KirkHaselden wrote:

What build are you running?

June CTP|||UPDATE:

We deployed the dtsx to the file system on the server, made changes to the FileSpec parameter in the config file and deployed it, then executed the package pointing to the Config file.

The package still looked for the file as specified in the dtsx XML (package), regardless of what was specified in the config.

To confirm, we renamed the input file to match the FileSpec parameter in the package and it ran successfully, finding and processing the file.

The ForEach parsing for the Enumeration objects appears to have a bug. Can someone let me know if this is a known bug and if it is going to be fixed for the next CTP release?

Thanks.|||

We attempted to recreate the Config file uinder the September CTP and are having the same problems. The Config wizard

Here is what is displayed at the bottom of the dtsConfig file:

File name:
\\MSSQLYukon\etlconfig\ISOTypeLoss.dtsConfig

Properties:
\Package.Connections[ISOTypeLossLog].Properties[ConnectionString]
\Package.Connections[DSSStaging].Properties[ServerName]
\Package.Connections[DSSStaging].Properties[InitialCatalog]
\Package.Connections[DSSStaging].Properties[ConnectionString]
<Unknown>.Properties[FileSpec]
<Unknown>.Properties[Directory]

We still get the error in the Error Window:

Warning 1 Warning loading ISOTypeLoss.dtsx: The package path referenced an object that cannot be found: "\Package\Foreach TC03 File.ForEachEnumerator.Properties[Directory]". This occurs when an attempt is made to resolve a package path to an object that cannot be found. i:\visual studio 2005\projects\claimtran\isotypeloss\ISOTypeLoss.dtsx 1 1

|||You will get this error if your config file contains references to names that don't exist elsewhere in the package. (In this case, it sounds like your config file contains references to "Foreach TC03 File", which doesn't exist in your package.) When you recreated the config file, did you actually delete the previous config file from the file system?|||

Cim Ryan wrote:

You will get this error if your config file contains references to names that don't exist elsewhere in the package. (In this case, it sounds like your config file contains references to "Foreach TC03 File", which doesn't exist in your package.) When you recreated the config file, did you actually delete the previous config file from the file system?

Absolutely. The errors I have highlighted above were copied and pasted from the confirmation screen of completing the Package Configuration Wizard and BEFORE the file is written.

The wizard itself does not recognize the setting before the file is written.

This is a HUGE issue if we are to deploy Yukon! |||

I've only been able to reproduce the error you're encountering by manually editing the config file and corrupting the name of the ForEach Loop. If you'll remove from your package all but the tasks necessary to reproduce the error (make a backup copy first, obviously), I'll take a look at the pared down package if you post it here.

|||

Cim Ryan wrote:

I've only been able to reproduce the error you're encountering by manually editing the config file and corrupting the name of the ForEach Loop. If you'll remove from your package all but the tasks necessary to reproduce the error (make a backup copy first, obviously), I'll take a look at the pared down package if you post it here.

Where in the Wizard should I be selecting the Directory and FileSpec properties? I select them under the <PackageName>/Executables/<ForEachContainerName>/Executables/EnumeratorProperties entry. In there I select "Directory" and "FileSpec".

I tried to post the XML but the Forum logged an error. Can I send the XML to you via email or post an attachment somewhere?|||Yeah, that sounds like the correct place to be specifying the properties. Go ahead and email me the package and config files.|||By the way, these are not errors, they are warnings which are non-critical. There is a property on packages called "SuppressConfigurationWarnings" that will hide these messages. They are there to simply inform you that the configuration is attempting to configure an object that is not there. So you can either remedy the situation or ignore it.
Configuration warnings should not fail the package.
K|||

KirkHaselden wrote:

By the way, these are not errors, they are warnings which are non-critical. There is a property on packages called "SuppressConfigurationWarnings" that will hide these messages. They are there to simply inform you that the configuration is attempting to configure an object that is not there. So you can either remedy the situation or ignore it.
Configuration warnings should not fail the package.
K

Kirk:

The problem is that we need to change settings in the config file to migrate the packages through Model, QA and to Production. As I posted above, I can change the settings to point to a different Directory but because the package cannot configure the setting it always uses the setting that was set when the package was designed, which is the Test directory. The only way to change this is to open the package, change the attributes during design and save them. But then it points to the Production directory, etc.

Changing the SuppressconfiurationWarnings setting will indeed hide the warning but it will not change the setting at execution time - which is the reason for having these properties in the config file in the first place.|||

Cim Ryan wrote:

Yeah, that sounds like the correct place to be specifying the properties. Go ahead and email me the package and config files.

Done. As stated, I am running September CTP.|||

I wasn't able to reproduce this failure, and Raul can no longer do so, either.

Force to complete query, ignore errors

Hi,

I have a big table and want to make a plausibility check of it′s data.

Problem is, that my query stops, if there is an unexpected datatype in one of the rows. But that is it, what i want to filter out of my table with that query and save the result as new correct table.

How can i write a parameter to my query SQL Code, that if a error occurs, the querry resumes and the error line will not displayed in my final querry overview?

In my books and on the net, i don′t found something to this theme ;-(.

Thx in advance.

You should provide additional info
- how is the table outline ?
- which is the unexpected data that breaks your code ?
- which sql server version are you working with ?
- it's a pure t-sql approach or is a mixed ado.net / sql approach ?
- can you post a short version of the t-sql code here ?|||- how is the table outline ?
The table has 30 columns with differnt data's in there. The datatyps are all nvarchar(50) after the flatfile txt Import. But there are some date, text, int and float datats, in there. I will bringt the correct datatyp to the columns later, because i have 80 of such tables, and before i must correct them form wrong lines and merge them after that.

- which is the unexpected data that breaks your code ?

Very very much possible things. A date where a int is expected. A float where a int is expected and and. It′s because the txt-Files i got have many data errors and wrong moved lines in there and i must correct them now.

- which sql server version are you working with ?

SQL Server 2005

- it's a pure t-sql approach or is a mixed ado.net / sql approach ?

I don′t know, but i don′t think ado.net.

- can you post a short version of the t-sql code here ?

[Code]
SELECT TOP (100) PERCENT _Ti, _Pl, _Ve, [_Datum_Time], _Ere, _Sg, _Sm,
FROM dbo.d6020900_ges
WHERE (_Ti < 1) OR
(_Ti > 1000) AND (_Pl < 0) OR
(_Pl > 15) AND (_Ve < 0) OR
(_Ve > 10000) AND (_Ere < - 1) OR
(_Ere > 10) AND (_Sg < N'0') OR
(_Sg > N'356') AND (_Sm < N'0') OR
(_Sm > N'255')
ORDER BY _Ti, _Pl, _Ve, [_Datum_Time]
[\Code]

|||There are different approaches depending on which is the operation you have to do...

First of all don't expect that is a simple task... I don't think you will be able to build a query like the one you posted... there are a lot of implicit conversions that simply cannot work in your case... there's no way to tell SQL to "skip" some row when a conversion fails... you have to build a little "engine" that anayze data column by column.

If you have to correct data in a table, this happens only once, so you can launch a long running query, maybe using a cursor and evaluating on a per row basis...
if this is the case you may also want to use a CLR stored proc... in that case you may fetch your data into a dataset and use standard manipulation/conversion using your favorite language (C#, VB.NET). In that case I think that the use of a CLR SP is fully justified from the improved flexibility in analyzing data, after all string manipulation with the framework is a lot more powerful that T-SQL.

If you have to repeadetly query the data to build a report or something like that... well this is a nightmare... I strongly suggest you to convert your data into a table that has the required data types (integers, datetime and so on), else you may build a bunch of conversion functions and/or a view made up by functions (or computed fields) that may be used to access the data... but, as you probably have already thought, this not a good performance solution.. it would be better to apply those functions only once to migrate your data into a brand new ad-hoc table.

Anyway let me know if you need further info.|||Thx so far.

My biggest problem are the much wrong lines in my Table, so converting in SQL fails and importing the basis txt-flatfile to a table with the correct datatypes fails also.

Is there a way to import only the rows, that are correct and dont stop at the wrong?|||You need to process you data row by row. This can be done
- using a cursor if you want to operate with a pure T-SQL solution
- using an external application (or a CLR stored proc) if you want more power/flexibility
I don't know any other way

You may also add a signature bit to your row that marks the row as dirty so you can filter it out... but again this requires that you process the table line by line.

in pseudo-code this may look like this

OPEN CURSOR
FETCH NEXT DATA INTO FIELD1, FIELD2, ...
WHILE (@.@.FETCH_STATUS = 0) BEGIN
IF DIRTY_DATE(FIELD1) OR DIRTY_INT(FIELD2) OR ... BEGIN
... DO SOMETHING HERE ...
END
FETCH NEXT DATA INTO FIELD1, FIELD2, ...
END

Just a question: why don't you avoid inserting bad rows when you import the txt file ?|||Hm, what u mean with avoid inserting bad rows? The bad rows are allready in my base txt-Files. But they are too large, to check and delete there every single line with the hand.

I don′t know how i can say on the import assistent, that bad rows will not be importet. At my try's the import stops, if a bad row is detected, an i musst correct the line in the txt-file with my own hands, delete the table in the db and then start again the import.

Is there a better way?|||> Hm, what u mean with avoid inserting bad rows?

You have an application that imports the text file into the system... correct ? well in that case the validation must be performed from that application. the data are imported only after validation ha occurred, so you won't have any problem in the db.

> Is there a better way?

I will be explicit, hope you won't offend:
1. who is the mad person that thought such a procedure ? how is possible that your "file-producer" is not able to write a decent file with no errors ? What I would do ? completely reject any file that contains errors.
2. who have designed the table with the nvarchar data only ? hope that you agree that is really a stupid thing... it's like you were trying to program an application with no data types... only pointers and bytes... that's prehistory.
|||O.K. thx, but the produced basis txt Files are corrupt and i have filtered many things before with VBA in excel, but to filter everything, vba and excel are to slow for my masses on files. The basis txt files are so, like I have got them, i can't get new ones or better ones, i must live with them.|||
> i must live with them.

ognuno ha le sue sfighe ...

FORCE DB Corruption

Hi,
I need to test a script to ensure that it behaves appropriately after
running DBCC CHECKDB and finding 'errors'. I have an if statement that runs
if @.@.error = 0 but I want to make sure that it works as intended should DBCC
CHECKDB FIND errors. How can I force a database to become corrupt to a point
that DBCC CHECKDB will report errors?
Thanks,
MichelleThis script from Sharon Dooley should do the trick - be careful with it
though!! I haven't tried it on SQL 2000, but it should work.
/* this script works on a database that is a copy of pubs. I created
this database easily with DTS This only works on SQL Server 7 databases
*/
/* NOTE: This script will truly corrupt a database. It should not be run
on ANY real database. It is only for simulating corruption for training
purposes.
*/
sp_configure allow, 1
go
reconfigure with override
go
update sysindexes set FirstIAM = 1234
where id = OBJECT_ID('roysched')
go
sp_configure allow, 0
go
reconfigure with override
go
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
michelle wrote:
> Hi,
> I need to test a script to ensure that it behaves appropriately after
> running DBCC CHECKDB and finding 'errors'. I have an if statement that runs
> if @.@.error = 0 but I want to make sure that it works as intended should DBCC
> CHECKDB FIND errors. How can I force a database to become corrupt to a point
> that DBCC CHECKDB will report errors?
> Thanks,
> Michelle
>|||Thanks! I thought that I remembered something from PASS one year but was
having trouble wading through the numerous results regarding db corruption
to find what I was looking for.
This DOES work on SQL2000 and DID corrupt a copy of the pubs database.
Thanks!
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:OZC$NUlzEHA.3488@.TK2MSFTNGP10.phx.gbl...
> This script from Sharon Dooley should do the trick - be careful with it
> though!! I haven't tried it on SQL 2000, but it should work.
> /* this script works on a database that is a copy of pubs. I created
> this database easily with DTS This only works on SQL Server 7 databases
> */
> /* NOTE: This script will truly corrupt a database. It should not be run
> on ANY real database. It is only for simulating corruption for training
> purposes.
> */
> sp_configure allow, 1
> go
> reconfigure with override
> go
> update sysindexes set FirstIAM = 1234
> where id = OBJECT_ID('roysched')
> go
> sp_configure allow, 0
> go
> reconfigure with override
> go
>
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
> michelle wrote:
> > Hi,
> >
> > I need to test a script to ensure that it behaves appropriately after
> > running DBCC CHECKDB and finding 'errors'. I have an if statement that
runs
> > if @.@.error = 0 but I want to make sure that it works as intended should
DBCC
> > CHECKDB FIND errors. How can I force a database to become corrupt to a
point
> > that DBCC CHECKDB will report errors?
> >
> > Thanks,
> >
> > Michelle
> >
> >

FORCE DB Corruption

Hi,
I need to test a script to ensure that it behaves appropriately after
running DBCC CHECKDB and finding 'errors'. I have an if statement that runs
if @.@.error = 0 but I want to make sure that it works as intended should DBCC
CHECKDB FIND errors. How can I force a database to become corrupt to a point
that DBCC CHECKDB will report errors?
Thanks,
Michelle
This script from Sharon Dooley should do the trick - be careful with it
though!! I haven't tried it on SQL 2000, but it should work.
/* this script works on a database that is a copy of pubs. I created
this database easily with DTS This only works on SQL Server 7 databases
*/
/* NOTE: This script will truly corrupt a database. It should not be run
on ANY real database. It is only for simulating corruption for training
purposes.
*/
sp_configure allow, 1
go
reconfigure with override
go
update sysindexes set FirstIAM = 1234
where id = OBJECT_ID('roysched')
go
sp_configure allow, 0
go
reconfigure with override
go
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
michelle wrote:
> Hi,
> I need to test a script to ensure that it behaves appropriately after
> running DBCC CHECKDB and finding 'errors'. I have an if statement that runs
> if @.@.error = 0 but I want to make sure that it works as intended should DBCC
> CHECKDB FIND errors. How can I force a database to become corrupt to a point
> that DBCC CHECKDB will report errors?
> Thanks,
> Michelle
>
|||Thanks! I thought that I remembered something from PASS one year but was
having trouble wading through the numerous results regarding db corruption
to find what I was looking for.
This DOES work on SQL2000 and DID corrupt a copy of the pubs database.
Thanks!
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:OZC$NUlzEHA.3488@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> This script from Sharon Dooley should do the trick - be careful with it
> though!! I haven't tried it on SQL 2000, but it should work.
> /* this script works on a database that is a copy of pubs. I created
> this database easily with DTS This only works on SQL Server 7 databases
> */
> /* NOTE: This script will truly corrupt a database. It should not be run
> on ANY real database. It is only for simulating corruption for training
> purposes.
> */
> sp_configure allow, 1
> go
> reconfigure with override
> go
> update sysindexes set FirstIAM = 1234
> where id = OBJECT_ID('roysched')
> go
> sp_configure allow, 0
> go
> reconfigure with override
> go
>
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
> michelle wrote:
runs[vbcol=seagreen]
DBCC[vbcol=seagreen]
point[vbcol=seagreen]

FORCE DB Corruption

Hi,
I need to test a script to ensure that it behaves appropriately after
running DBCC CHECKDB and finding 'errors'. I have an if statement that runs
if @.@.error = 0 but I want to make sure that it works as intended should DBCC
CHECKDB FIND errors. How can I force a database to become corrupt to a point
that DBCC CHECKDB will report errors?
Thanks,
MichelleThis script from Sharon Dooley should do the trick - be careful with it
though!! I haven't tried it on SQL 2000, but it should work.
/* this script works on a database that is a copy of pubs. I created
this database easily with DTS This only works on SQL Server 7 databases
*/
/* NOTE: This script will truly corrupt a database. It should not be run
on ANY real database. It is only for simulating corruption for training
purposes.
*/
sp_configure allow, 1
go
reconfigure with override
go
update sysindexes set FirstIAM = 1234
where id = OBJECT_ID('roysched')
go
sp_configure allow, 0
go
reconfigure with override
go
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
michelle wrote:
> Hi,
> I need to test a script to ensure that it behaves appropriately after
> running DBCC CHECKDB and finding 'errors'. I have an if statement that run
s
> if @.@.error = 0 but I want to make sure that it works as intended should DB
CC
> CHECKDB FIND errors. How can I force a database to become corrupt to a poi
nt
> that DBCC CHECKDB will report errors?
> Thanks,
> Michelle
>|||Thanks! I thought that I remembered something from PASS one year but was
having trouble wading through the numerous results regarding db corruption
to find what I was looking for.
This DOES work on SQL2000 and DID corrupt a copy of the pubs database.
Thanks!
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:OZC$NUlzEHA.3488@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> This script from Sharon Dooley should do the trick - be careful with it
> though!! I haven't tried it on SQL 2000, but it should work.
> /* this script works on a database that is a copy of pubs. I created
> this database easily with DTS This only works on SQL Server 7 databases
> */
> /* NOTE: This script will truly corrupt a database. It should not be run
> on ANY real database. It is only for simulating corruption for training
> purposes.
> */
> sp_configure allow, 1
> go
> reconfigure with override
> go
> update sysindexes set FirstIAM = 1234
> where id = OBJECT_ID('roysched')
> go
> sp_configure allow, 0
> go
> reconfigure with override
> go
>
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
> michelle wrote:
runs[vbcol=seagreen]
DBCC[vbcol=seagreen]
point[vbcol=seagreen]