hidden ReportParameter where is it?

I'm coding up a WinForm using SSRS WebServices, and I have found I can't see a Hidden property for the ReportParameter

Anyone know how to read if a Parameter is hidden?

You can know that whether the report parameter is hidden or not by using the below code snippets.

Dim pAs Microsoft.Reporting.WebForms.ReportParameterInfoFor Each pIn ReportViewer1.ServerReport.GetParametersIf p.Visible =False Then'Parameter is hiddenEnd IfNext

|||

Thanks for the reply, but I'm having a little difficulity understanding why this is not available from Reporting Services Web Service.

I am looking up the details from reportservice.asmx WebService, and returning the information back to a List:

CatalogItem[] items =this.objRS.ListChildren(currentFolder,false);

(Sorry it's in C#)

Where objRS is the RS WebServices.

Then I use

reportParametersArray =this.objRS.GetReportParameters(report, historyID, forRendering,null,null);

foreach (ReportParameter pin reportParametersArray)

{

p.Visible; // This odes not exist :-(

}

However Visible is not available using it in this way :-(

Why is it different, and what am I missing?

|||

Hi,

You can get the visibility by adding a condition to the code you have written. Please find the code below. I hope this would help.

--

reportParametersArray =this.objRS.GetReportParameters(report, historyID, forRendering,null,null);
foreach (ReportParameter pin reportParametersArray)
{
if (p.Prompt =="") {
//Parameter is hidden }}

--

|||

Perfect, thanks.

Why oh why the name change from Visible to Prompt?

|||

It's something like a flag used for an internal manipulation.

Heterogeneous Data *Target*

I want to create a custom read-only pull subscriber to populate a non-RDBMS
database, for which I can't use any of the existing subscriber types (e.g.
SQL, ODBC/OLE).
I understand enough about how the replication process and tables work (SQL2K
at the moment) to do this. So, for example, I can do the following:
1. Set up a local (i.e. same SQL Server as publisher) SQL subscriber to
replicate the data set I want into a database on the same SQL Server. E.g.
replicate from myserver.sourcedb to myserver.targetdb. This works well; i.e.
I can make updates in sourcedb and see them propagate through to targetdb,
and when sp_MSdistribution_cleanup runs it cleans up
distribution..msrepl_commands correctly.
2. I can then disable / remove the Replication Distribution job, and
commands will begin to queue up in distribution..msrepl_commands.
3. I can then service these commands myself using my custom distributor,
parsing the commands to update my target database. The rest of the process
remains the same; i.e. the log reader and the standard cleanups, so I'm only
replacing the Replication Distribution process / job.
My questions are:
1. As this specific activity is (apparently) undocumented, can I rely on MS
continuing to use a similar model for replication in future SQL Server
versions? I suppose we can tell this for 2005 already although I don't know
the answer to that yet. I wouldn't find a small amount of change a problem
as long as the basic model remained the same.
2. I am parsing the SQL commands in msrepl_commands.command. All the
commands I've seen so far are 'simple'; i.e. they are of only a handful of
forms such as:
'update <table> set <column> = <value>'
, or
'delete <table> where <primary key col 1> = <value 1> and <primary key col
2> = <value 2>'
, etc.
Then there are a few snapshot-related commands which I can recognize.
Again, is this something I can rely on? This may not be worth the effort if
it transpires a sophisticated parsing function is actually required. Note: I
turn off SP replication and ignore schema changes for now.
3. Can I be reasonably sure that some routine or ad-hoc maintenance process
will not - as a goodwill gesture - 'fix' the replication for me and reinstate
the standard Replication Distribution job?
Thanks.
this is not a good path to go down. You are making yourself vulnerable to
the next sp, patch or version of SQL Server MS releases. Also if things go
south you cannot rely on MS for support. Also you are locking your company
into a solution which only you understand.
This being said, you should be safe for the immediate future. SQL 2005 is
similar to SQL 2000 tranny repl.
I take it you are using sp_browsereplcmds to correctly format everything for
you. You can also use sp_dumpparam (I think that's the name of the command)
for more info.
Hilary
"sellotape" <sellotape@.discussions.microsoft.com> wrote in message
news:3116B0C9-967D-48A4-9B65-60394E33E181@.microsoft.com...
>I want to create a custom read-only pull subscriber to populate a non-RDBMS
> database, for which I can't use any of the existing subscriber types (e.g.
> SQL, ODBC/OLE).
> I understand enough about how the replication process and tables work
> (SQL2K
> at the moment) to do this. So, for example, I can do the following:
> 1. Set up a local (i.e. same SQL Server as publisher) SQL subscriber to
> replicate the data set I want into a database on the same SQL Server.
> E.g.
> replicate from myserver.sourcedb to myserver.targetdb. This works well;
> i.e.
> I can make updates in sourcedb and see them propagate through to targetdb,
> and when sp_MSdistribution_cleanup runs it cleans up
> distribution..msrepl_commands correctly.
> 2. I can then disable / remove the Replication Distribution job, and
> commands will begin to queue up in distribution..msrepl_commands.
> 3. I can then service these commands myself using my custom distributor,
> parsing the commands to update my target database. The rest of the
> process
> remains the same; i.e. the log reader and the standard cleanups, so I'm
> only
> replacing the Replication Distribution process / job.
> My questions are:
> 1. As this specific activity is (apparently) undocumented, can I rely on
> MS
> continuing to use a similar model for replication in future SQL Server
> versions? I suppose we can tell this for 2005 already although I don't
> know
> the answer to that yet. I wouldn't find a small amount of change a
> problem
> as long as the basic model remained the same.
> 2. I am parsing the SQL commands in msrepl_commands.command. All the
> commands I've seen so far are 'simple'; i.e. they are of only a handful of
> forms such as:
> 'update <table> set <column> = <value>'
> , or
> 'delete <table> where <primary key col 1> = <value 1> and <primary key
> col
> 2> = <value 2>'
> , etc.
> Then there are a few snapshot-related commands which I can recognize.
> Again, is this something I can rely on? This may not be worth the effort
> if
> it transpires a sophisticated parsing function is actually required.
> Note: I
> turn off SP replication and ignore schema changes for now.
> 3. Can I be reasonably sure that some routine or ad-hoc maintenance
> process
> will not - as a goodwill gesture - 'fix' the replication for me and
> reinstate
> the standard Replication Distribution job?
> Thanks.
|||"Hilary Cotter" wrote:

> this is not a good path to go down. You are making yourself vulnerable to
> the next sp, patch or version of SQL Server MS releases. Also if things go
> south you cannot rely on MS for support. Also you are locking your company
> into a solution which only you understand.
Thanks; that's the answer I was expecting.
I was wondering something that I didn't ask explicitly in the original
question. This is a common requirement (in general); namely to replicate a
part of a data source to another repository. It's not always the case that
the target is a compatible RDBMS. Some companies replicate parts of their
personnel data (via LDAP) to ADS (or other DS) trees, others replicate to
authentication or access systems, address lists and so on. What we're all
doing in the absence of a fairly generic solution like I'm suggesting is
probably a combination of *additional* triggers, *additional* extended SPs,
polling (more highly latent and possibly less efficient) for changes, or
taking full snapshots (again more highly latent and possibly less efficient).
Mostly, we are either taking a latency and / or efficiency hit, or we are
just re-inventing the existing replication metaphor. Usually we are also
unnecessarily modifying the data source (additional triggers, extended procs,
timestamps) to achieve this.
As MS already provides a documented solution for replication into MSSQL from
heterogeneous sources; is it not a little limiting that it does not provide
one for replication out to heterogeneous targets? Then again perhaps it
does, but I've not spotted it yet.
Then just as a sidebar, in this context it's perhaps interesting to note
that even when sticking only to fully vendor-supported solutions this is no
guarantee that additional work will not be required when upgrading. E.g.
several VC++ / VB upgrades have not been backwardly compatible and required a
fair amount of manual rework. I'm not saying it's not understandable; just
that it happens.

Here is a question I cant seem to find the answer to

Hi all. I'm kind of stuck. I may be having a general conceptual error here.

Suppose I have a table with two columns in the primary key, ID and VERSION.

Suppose I have a child table that I want a relationship on ID only and not VERSION.

Is there a way to do this besides creating a check constraint? (ie When I print and ERD it gives the lines, so the "non" db folks can "see" the relationships)

not sure if this is possible or not but any clues you all can give would be most helpful.

Thanks,

bill
s e g fa h l t @. l o n g b o y s . n e tThe conceptual error you have made is mixing up the parent and child. The table with id and version should be the child, and then it can have a foreign key relationship with the parent.

CREATE TABLE parent (
id int NOT NULL ,
string char (10) NULL
)
GO

ALTER TABLE parent ADD CONSTRAINT
PK_parent PRIMARY KEY CLUSTERED
(id)
GO

CREATE TABLE child (
id int NOT NULL ,
version int NOT NULL ,
string char (10) NULL
)
GO

ALTER TABLE child ADD CONSTRAINT
PK_child PRIMARY KEY CLUSTERED
(id, version)
GO

ALTER TABLE child ADD CONSTRAINT
FK_child_parent FOREIGN KEY
(id) REFERENCES parent (id)
GO

Hope this helps.|||Thanks for the feedback. My apologies for not being more clear on the subject. I opted for brevity instead of details.

Here is my situation.
I have a very large table which I want to split up into smaller tables. The tables [should] ideally have a 1-1 relationship between them all.

I also need to implement a transaction tracking solution such that I can determine if any column of any of the tables was changed, when it was changed, and who changed it, and be able to rollback if necessary to any particular point in time.

Finally, I also need to implement a modification approval system, such that any proposed changes must be captured, run through the red-tape corporate pipeline, then when approved, put into the table as actual data. While the changes are in the approval process, I need to be able to show the entire record with the proposed changes to one set of individual, and the entire record with only the approved changes/data to another set of people.

There is non-trivial amount of tables I have to do this with.

I came up with 2 feasible solutions.

solution 1 is : Each table which I need to track will have a copy table(same structure). It will store the proposed changes and any change history.

solution 2 is a bit more abstract. For each of my tables which I need to have transaction auditing and modification approval on, I will add two columns. a version column and a stage column. The version column will be used mainly for reporting and an easy way to see changelog history for any particular item ID. The stage column will be of "proposed", "review", "approved", "rejected", "obsolete". All the data will be contained in one table. so for each unique record(identified by ID) I will actually have multiple records with the same ID. But each successive record will have a higher version number. As new Version Numbers are added, the stage will be moved to "obsolete" when the changes are approved. then that record's stage will be set to "approved" and it will become the official record.

I just came across this yesterday, so i'm still fleshing out my "solution". I like the idea of solution 2 as its compact and fairly easy to code for. However, the more I think about it, the more I feel I will break anykind of RI I can put and still use the DB to enforce it. I don't think I even need a primary key of (ID, Version), but you need a primary key, to have a foreign key. If I could get the relationship, without having a primary key(just have a clustered, non-unique index on ID) then that would be sufficient. However, I'm pretty new to Sql Server, so I don't know if there is a way to do that. Any Ideas?

here is a visual of what my solution 2 would be like.

I have a table
create table table1 (
id int not null,
version int not null,
stageId int not null,
more columns,
primary key (id, version)
)

I have a child table, which I need a RI check on ID only of the parent table. It too, will have a version column, but does not relate to the parent table. It will however, be part of the primary key of the child table.

create table child1ToTable1 (
id int not null,
version int not null,
stageId int not null,
more columns,
primary key(id, version)
)

create table child2ToTable1 (
id int not null,
version int not null,
stageId int not null,
more columns,
primary key(id, version)
)

I need the relationship from table1 to child[12]ToTable1 to be a many to many on ID, but I don't care about the version.

Clear as mud? I know. it kind of gives me a headache too.

:)

Thanks for you help.

bill

HELPPP SQL select statement pissing me off, error, but why.....

grr.
I have two different select statements that are giving me the same error.. and its getting irritating cause i cant figure out why...

the error IS --tada--

-------------
ADODB.Recordset error '800a0bb9'

Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

/vendorlist.asp, line 68
-------------
Here's the statement.. and ill point out line 68 below with a few other details...
-------------
Dim vendorRS, SQL
set vendorRS = Server.CreateObject("ADODB.Recordset")
SQL = "SELECT ttVendors.company, ttVendors.website, ttVendors.phone, ttVendors.phone800,ttVendors.vendorID," &_
SQL = SQL & "ttInventory.newprice, ttInventory.refurbprice, ttInventory.oosprice, ttInventory.venddescrip" &_
SQL = SQL & "FROM ttVendors INNER JOIN ttInventory on ttVendors.vendorID = ttInventory.vendorID" &_
SQL = SQL & "WHERE ttInventory.prodID = '" &prodID& "'"

vendorRS.Open SQL ,objCn, 1, 3

DIM company
company = vendorRs("company")
vendorRs.close
set vendorRs = nothing
--------------
Ok so... line 68 IS
vendorRS.Open SQL ,objCn, 1, 3

ive tried it like
vendorRS.Open = SQL ,objCn, 1, 3
vendorRS.Open, SQL ,objCn, 1, 3
vendorRS.Open SQL ,objCn, 3, 3

no luck..
Also, prodID is passed from a querystring from previous page.. just used the typical request object to gather.. in the DB, all prodID fields are of INT type.. and im wondering if that makes a difference..

any ideas.. or blanks i can fill it?PS,, in Query analyzer, the statement works perfect...!!

HELP: SQL Server Service can't start up!

I installed SQL Server 2000 on a PC which is on a domain "DOMAIN" and it
uses Windows XP Professional. I used Windows Authentication Mode (instead of
Mixed-Mode). SQL Server 2000 starts up successfully.
Then I installed on a standalone PC which is part of a workgroup "WORKGROUP"
(instead of a domain) and it uses Windows XP Professional. I selected
Windows Authentication Mode. There was a dialog for me to enter my login
details and it already had fields entered, eg:
Username: Owner
Password: <BLANK>
Domain: WORKGROUP
This standalone PC only has one user account "Owner" which is bounded to the
Administrator (the 1st account Windows XP creates for you). The password for
this account is blank. The installation process was successful. But when SQL
Server Service Manager tried to start my SQL Server, it says login failure.
Just wondering, does SQL Server 2000 doesn't like blank passwords and
auto-fails immediately? Or does it have to be on a domain? I cannot use
Mixed-Mode as my development application doesn't work with Mixed-Mode.
Andrew
ERROR.LOG and/or Event Viewer have some info?
Does the MSSQLSERVER Service start automaticaly and under Local System
account?
"Andrew Wan" <andrew_wan1980@.hotmail.com> wrote in message
news:Ox$roroIHHA.816@.TK2MSFTNGP06.phx.gbl...
>I installed SQL Server 2000 on a PC which is on a domain "DOMAIN" and it
>uses Windows XP Professional. I used Windows Authentication Mode (instead
>of Mixed-Mode). SQL Server 2000 starts up successfully.
> Then I installed on a standalone PC which is part of a workgroup
> "WORKGROUP" (instead of a domain) and it uses Windows XP Professional. I
> selected Windows Authentication Mode. There was a dialog for me to enter
> my login details and it already had fields entered, eg:
> Username: Owner
> Password: <BLANK>
> Domain: WORKGROUP
> This standalone PC only has one user account "Owner" which is bounded to
> the Administrator (the 1st account Windows XP creates for you). The
> password for this account is blank. The installation process was
> successful. But when SQL Server Service Manager tried to start my SQL
> Server, it says login failure.
> Just wondering, does SQL Server 2000 doesn't like blank passwords and
> auto-fails immediately? Or does it have to be on a domain? I cannot use
> Mixed-Mode as my development application doesn't work with Mixed-Mode.
>
|||It doesn't have 2 be on a domain.
Test it by giving the local user account a password an restart the sql
service.
Greetz,
SQLWIZ
"Uri Dimant" wrote:

> Andrew
> ERROR.LOG and/or Event Viewer have some info?
>
> Does the MSSQLSERVER Service start automaticaly and under Local System
> account?
> "Andrew Wan" <andrew_wan1980@.hotmail.com> wrote in message
> news:Ox$roroIHHA.816@.TK2MSFTNGP06.phx.gbl...
>
>
|||Hi
I'm only a novice in this field - but I had what appears to be the same
problem a few weeks back, where my SQL server service was logging in with
Windows authentication that wasn't the primary admin account (although it had
admin access). Anyway - due to a network enforced user login password
rotation - I had to change the login password and forgot that the SQL server
service was still associated with the older password. So the SQL server
service wasn't starting...login failure. As I was doing other work at the
time on the server it took a little while (longer than it should have ;-P) to
consider that it was the password change that stuffed the service startup. I
changed the authentication of the service to use the real admin
authentication (which remains outside of the network password rotation
scheme/policy) and the server services have started fine everytime since.
Cheers
Tim
"Hate_orphaned_users" wrote:
[vbcol=seagreen]
> It doesn't have 2 be on a domain.
> Test it by giving the local user account a password an restart the sql
> service.
> Greetz,
> SQLWIZ
> "Uri Dimant" wrote:

HELP: SQL Server Service can't start up!

I installed SQL Server 2000 on a PC which is on a domain "DOMAIN" and it
uses Windows XP Professional. I used Windows Authentication Mode (instead of
Mixed-Mode). SQL Server 2000 starts up successfully.
Then I installed on a standalone PC which is part of a workgroup "WORKGROUP"
(instead of a domain) and it uses Windows XP Professional. I selected
Windows Authentication Mode. There was a dialog for me to enter my login
details and it already had fields entered, eg:
Username: Owner
Password: <BLANK>
Domain: WORKGROUP
This standalone PC only has one user account "Owner" which is bounded to the
Administrator (the 1st account Windows XP creates for you). The password for
this account is blank. The installation process was successful. But when SQL
Server Service Manager tried to start my SQL Server, it says login failure.
Just wondering, does SQL Server 2000 doesn't like blank passwords and
auto-fails immediately? Or does it have to be on a domain? I cannot use
Mixed-Mode as my development application doesn't work with Mixed-Mode.Andrew
ERROR.LOG and/or Event Viewer have some info?
Does the MSSQLSERVER Service start automaticaly and under Local System
account?
"Andrew Wan" <andrew_wan1980@.hotmail.com> wrote in message
news:Ox$roroIHHA.816@.TK2MSFTNGP06.phx.gbl...
>I installed SQL Server 2000 on a PC which is on a domain "DOMAIN" and it
>uses Windows XP Professional. I used Windows Authentication Mode (instead
>of Mixed-Mode). SQL Server 2000 starts up successfully.
> Then I installed on a standalone PC which is part of a workgroup
> "WORKGROUP" (instead of a domain) and it uses Windows XP Professional. I
> selected Windows Authentication Mode. There was a dialog for me to enter
> my login details and it already had fields entered, eg:
> Username: Owner
> Password: <BLANK>
> Domain: WORKGROUP
> This standalone PC only has one user account "Owner" which is bounded to
> the Administrator (the 1st account Windows XP creates for you). The
> password for this account is blank. The installation process was
> successful. But when SQL Server Service Manager tried to start my SQL
> Server, it says login failure.
> Just wondering, does SQL Server 2000 doesn't like blank passwords and
> auto-fails immediately? Or does it have to be on a domain? I cannot use
> Mixed-Mode as my development application doesn't work with Mixed-Mode.
>

Help: I can open my database in Management Studio, but can't connect it in Integration Services

Received error tip:

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

I'm using the Evaluation Edition of SQL Server 2005, Windows XP SP2 Home Edition.

I have disabled the firewall.

You need to enable remote connections:

http://www.sqlserver2005.de/Sharedfiles/remoteconnection.jpg

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Yes, I have enable remote connections, but still wrong.

Can the Home Edition of Windows XP support SQL Server?

|||

Hi, only the developer edition and the express edition are supposed to work on Windows XP Home (as of the offical system requirements, didn′t tested that out yet). Let me hear some feedback if that worked for you.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||I have installed SQL Server Evaluation Edition on Windows XP Home Edition. It can work. In the Management Studio, the database can be opened and executed. The only one problem is that in the reporting services, the database can't be connetcted.

Help: CHECKDB gives errors I can't fix

Hi,
I've got what appears to be a corrupt database, (SQL 7 SP4) running CHECKDB on it returns lots of errors, the first being:
Msg 8966, Level 16, State 5, Server 01AW01, Procedure , Line 3
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not read and latch page
(1:177936) with latch type SH. PFS failed.
I switched the database to single user, and tried various repair flags on CHECKDB, and I always seem to get the same errors, and it returns lots of:
'The repair level on the DBCC statement caused this repair to be bypassed.'
Any ideas on where to start on this.. Its a customers database, I don't have information on what backups they have etc.. and its probably problems in the only large table in the database (some 2.5 million records, and 900Mb on disk, with 400Mb index).. Could this be a resource related problem somehow 'Hopefully you get some specific suggestions about your message. Below you
find my general recommendations. Note, however, that this *might* not
actually be a corrupt database (I can't say for sure) so opening a case with
MS might be a better option than start with the restore route (also search
Kb etc first):
Here are the general recommendations for handling a suspect or corrupt
database:
0. Ensure you have a backup strategy that you can use to recover from
hardware failures (including corruption). I recommend performing both
database and log backup in most situations.
1. If you can run DBCC CHECKDB against the database: Search Books Online and
KB for the error numbers that CHECKDB gives you. There might be specific
info for that type of error.
2. Find out why this happened. Check eventlog, do HW diagnostics etc.;
search Books Online and KB for those errors. You don't want this to happen
again! If the database is suspect, the file might have been in use by for
instance an anti-virus program and restarting SQL Server might be all that
is needed - but you still want to read logs etc to find out what happened.
3. If there is a hardware problem, ensure the faulty hardware is replaced.
4. Backup the log. This assumes that log backup schedule is in place, of
course. If the database is suspect, then the NO_TRUNCATE option for the
RESTORE command must be used. Also, you might want to do a file backup of
the mdf and ldf files, for extra safety.
5. Restore is the best thing to do now. If you managed to backup log as per
step 4, then you will most probably have zero dataloss. You should restore
the latest clean database backup and the subsequent log backups including
the one taken in above step.
If the database isn't suspect, then DBCC with a REPAIR option might be a
secondary option but this will often result in loss of data. Additional
solutions, depending on the errors, may be to manually rebuild non-clustered
indexes, manually drop and reload a table if the data is static, and so on.
If the database is suspect, a secondary option can be to try to "un-suspect"
the database using sp_resetstatus. Read about it (books online, KB, google
etc). It might help but if the database is too damaged, it might just pop
back to suspect again. There's also something called "emergency mode" which
is a "panic" status you can set in order to try to get data out of a damaged
database. I think the name of that option speaks for itself. Again search
the net for info.
If you feel uncertain with above steps, I recommend letting MS hand-hold you
through the steps appropriate for your particular situation.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Kevin" <anonymous@.discussions.microsoft.com> wrote in message
news:BC4EEEE9-BA17-416F-8C9A-8DED0C6AED3F@.microsoft.com...
> Hi,
> I've got what appears to be a corrupt database, (SQL 7 SP4) running
CHECKDB on it returns lots of errors, the first being:
> Msg 8966, Level 16, State 5, Server 01AW01, Procedure , Line 3
> [Microsoft][ODBC SQL Server Driver][SQL Server]Could not read and latch
page
> (1:177936) with latch type SH. PFS failed.
> I switched the database to single user, and tried various repair flags on
CHECKDB, and I always seem to get the same errors, and it returns lots of:
> 'The repair level on the DBCC statement caused this repair to be
bypassed.'
> Any ideas on where to start on this.. Its a customers database, I don't
have information on what backups they have etc.. and its probably problems
in the only large table in the database (some 2.5 million records, and 900Mb
on disk, with 400Mb index).. Could this be a resource related problem
somehow '
>
>|||This is a multi-part message in MIME format.
--=_NextPart_000_0058_01C3BD8A.EDA4E890
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
Hi Kevin,
I think that this is probably a corruption in the database. This error =message indicates that DBCC was not able to read (or latch) page 177936 =in your customer's database. The page type in question (PFS) is a =system allocation page that tracks database free space and some other =state information. This page is actually used to drive the DBCC scan, =so an error here is pretty severe. This is made worse by the fact that =DBCC can't repair this type of page if there is a problem.
Without more error context it's hard for me to say why we can't read =this page: it could be hardware causing a bad read from disk, a bad =page header that resulted from a page corruption, etc.
I completely agree with Tibor that you should restore the database, =perhaps pairing that with a case with PSS to investigate root cause.
Thanks,
Ryan Stonecipher
SQL Server Storage Engine (DBCC)
"Tibor Karaszi" =<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se> wrote in =message news:einCq2YvDHA.2712@.tk2msftngp13.phx.gbl...
Hopefully you get some specific suggestions about your message. Below =you
find my general recommendations. Note, however, that this *might* not
actually be a corrupt database (I can't say for sure) so opening a =case with
MS might be a better option than start with the restore route (also =search
Kb etc first):
Here are the general recommendations for handling a suspect or corrupt
database:
0. Ensure you have a backup strategy that you can use to recover from
hardware failures (including corruption). I recommend performing both
database and log backup in most situations.
1. If you can run DBCC CHECKDB against the database: Search Books =Online and
KB for the error numbers that CHECKDB gives you. There might be =specific
info for that type of error.
2. Find out why this happened. Check eventlog, do HW diagnostics etc.;
search Books Online and KB for those errors. You don't want this to =happen
again! If the database is suspect, the file might have been in use by =for
instance an anti-virus program and restarting SQL Server might be all =that
is needed - but you still want to read logs etc to find out what =happened.
3. If there is a hardware problem, ensure the faulty hardware is =replaced.
4. Backup the log. This assumes that log backup schedule is in place, =of
course. If the database is suspect, then the NO_TRUNCATE option for =the
RESTORE command must be used. Also, you might want to do a file backup =of
the mdf and ldf files, for extra safety.
5. Restore is the best thing to do now. If you managed to backup log =as per
step 4, then you will most probably have zero dataloss. You should =restore
the latest clean database backup and the subsequent log backups =including
the one taken in above step.
If the database isn't suspect, then DBCC with a REPAIR option might be =a
secondary option but this will often result in loss of data. =Additional
solutions, depending on the errors, may be to manually rebuild =non-clustered
indexes, manually drop and reload a table if the data is static, and =so on.
If the database is suspect, a secondary option can be to try to ="un-suspect"
the database using sp_resetstatus. Read about it (books online, KB, =google
etc). It might help but if the database is too damaged, it might just =pop
back to suspect again. There's also something called "emergency mode" =which
is a "panic" status you can set in order to try to get data out of a =damaged
database. I think the name of that option speaks for itself. Again =search
the net for info.
If you feel uncertain with above steps, I recommend letting MS =hand-hold you
through the steps appropriate for your particular situation.
-- Tibor Karaszi, SQL Server MVP
Archive at:
=http://groups.google.com/groups?oi=3Ddjq&as_ugroup=3Dmicrosoft.public.sql=
server
"Kevin" <anonymous@.discussions.microsoft.com> wrote in message
news:BC4EEEE9-BA17-416F-8C9A-8DED0C6AED3F@.microsoft.com...
> Hi,
>
> I've got what appears to be a corrupt database, (SQL 7 SP4) running
CHECKDB on it returns lots of errors, the first being:
>
> Msg 8966, Level 16, State 5, Server 01AW01, Procedure , Line 3
> [Microsoft][ODBC SQL Server Driver][SQL Server]Could not read and =latch
page
> (1:177936) with latch type SH. PFS failed.
>
> I switched the database to single user, and tried various repair =flags on
CHECKDB, and I always seem to get the same errors, and it returns lots =of:
>
> 'The repair level on the DBCC statement caused this repair to be
bypassed.'
>
> Any ideas on where to start on this.. Its a customers database, I =don't
have information on what backups they have etc.. and its probably =problems
in the only large table in the database (some 2.5 million records, and =900Mb
on disk, with 400Mb index).. Could this be a resource related problem
somehow '
>
>
>
>
--=_NextPart_000_0058_01C3BD8A.EDA4E890
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Hi Kevin,
I think that this is probably a =corruption in the database. This error message indicates that DBCC was not =able to read (or latch) page 177936 in your customer's database. The page =type in question (PFS) is a system allocation page that tracks database free =space and some other state information. This page is actually used to drive =the DBCC scan, so an error here is pretty severe. This is made worse by the =fact that DBCC can't repair this type of page if there is a =problem.
Without more error context it's =hard for me to say why we can't read this page: it could be hardware causing a =bad read from disk, a bad page header that resulted from a page corruption, etc.
I completely agree with Tibor =that you should restore the database, perhaps pairing that with a case with PSS =to investigate root cause.
Thanks,
Ryan Stonecipher
SQL Server Storage Engine (DBCC)
"Tibor Karaszi" wrote in message news:einCq2YvDHA.2712=@.tk2msftngp13.phx.gbl...Hopefully you get some specific suggestions about your message. Below =youfind my general recommendations. Note, however, that this *might* =notactually be a corrupt database (I can't say for sure) so opening a case withMS =might be a better option than start with the restore route (also searchKb =etc first):Here are the general recommendations for handling a =suspect or corruptdatabase:0. Ensure you have a backup strategy that =you can use to recover fromhardware failures (including corruption). I =recommend performing bothdatabase and log backup in most =situations.1. If you can run DBCC CHECKDB against the database: Search Books Online =andKB for the error numbers that CHECKDB gives you. There might be =specificinfo for that type of error.2. Find out why this happened. Check =eventlog, do HW diagnostics etc.;search Books Online and KB for those =errors. You don't want this to happenagain! If the database is suspect, the =file might have been in use by forinstance an anti-virus program and =restarting SQL Server might be all thatis needed - but you still want to read =logs etc to find out what happened.3. If there is a hardware problem, =ensure the faulty hardware is replaced.4. Backup the log. This assumes =that log backup schedule is in place, ofcourse. If the database is suspect, =then the NO_TRUNCATE option for theRESTORE command must be used. Also, =you might want to do a file backup ofthe mdf and ldf files, for extra safety.5. Restore is the best thing to do now. If you managed =to backup log as perstep 4, then you will most probably have zero =dataloss. You should restorethe latest clean database backup and the =subsequent log backups includingthe one taken in above step.If the database =isn't suspect, then DBCC with a REPAIR option might be asecondary option =but this will often result in loss of data. Additionalsolutions, =depending on the errors, may be to manually rebuild non-clusteredindexes, =manually drop and reload a table if the data is static, and so on.If the =database is suspect, a secondary option can be to try to "un-suspect"the =database using sp_resetstatus. Read about it (books online, KB, googleetc). =It might help but if the database is too damaged, it might just =popback to suspect again. There's also something called "emergency mode" =whichis a "panic" status you can set in order to try to get data out of a damageddatabase. I think the name of that option speaks for =itself. Again searchthe net for info.If you feel uncertain with =above steps, I recommend letting MS hand-hold youthrough the steps appropriate =for your particular situation.-- Tibor Karaszi, SQL Server =MVPArchive at:http://groups.google.com/groups?oi=3Ddjq&as_ugrou=p=3Dmicrosoft.public.sqlserver"Kevin" wrote in messagenews:BC4=EEEE9-BA17-416F-8C9A-8DED0C6AED3F@.microsoft.com...> Hi,>> I've got what appears to be a corrupt database, =(SQL 7 SP4) runningCHECKDB on it returns lots of errors, the first being:>> Msg 8966, Level 16, State 5, Server 01AW01, =Procedure , Line 3> [Microsoft][ODBC SQL Server Driver][SQL Server]Could =not read and latchpage> (1:177936) with latch type SH. PFS failed.>> I switched the database to single user, and =tried various repair flags onCHECKDB, and I always seem to get the same =errors, and it returns lots of:>> 'The repair level on the DBCC statement caused this repair to bebypassed.'>> Any =ideas on where to start on this.. Its a customers database, I =don'thave information on what backups they have etc.. and its probably problemsin the only large table in the database (some 2.5 million =records, and 900Mbon disk, with 400Mb index).. Could this be a =resource related problemsomehow '>>>>

---

--=_NextPart_000_0058_01C3BD8A.EDA4E890--