Hide Columns

Hi,
I have created a report that contains a table. The table returns data w/
multiple statuses. I have created a group based on the status and have a
page break at the end of the group (so each page will display only one status
type). I would like to dynamically show / hide columns based on the status.
Is this possible? I tried setting the visibility of the individual cells,
however, this leaves a gap where the cells were (which is not desirable)â?¦
Any thoughts?
Thanks!Try setting the visibility property on the column instead of the
individual cells.
On Sep 5, 4:18 pm, David <Da...@.discussions.microsoft.com> wrote:
> Hi,
> I have created a report that contains a table. The table returns data w/
> multiple statuses. I have created a group based on the status and have a
> page break at the end of the group (so each page will display only one status
> type). I would like to dynamically show / hide columns based on the status.
> Is this possible? I tried setting the visibility of the individual cells,
> however, this leaves a gap where the cells were (which is not desirable)...
> Any thoughts?
> Thanks!|||Hi Jen,
Can this be done with a Cross-Tab Report? I dont see the Visibility Property
of the column group :( .
--
With Thanks and Regards,
Raja Krishnamurthy
"Jen" wrote:
> Try setting the visibility property on the column instead of the
> individual cells.
> On Sep 5, 4:18 pm, David <Da...@.discussions.microsoft.com> wrote:
> > Hi,
> >
> > I have created a report that contains a table. The table returns data w/
> > multiple statuses. I have created a group based on the status and have a
> > page break at the end of the group (so each page will display only one status
> > type). I would like to dynamically show / hide columns based on the status.
> > Is this possible? I tried setting the visibility of the individual cells,
> > however, this leaves a gap where the cells were (which is not desirable)...
> >
> > Any thoughts?
> >
> > Thanks!
>
>

Hide a column (value) in a subtotal of a matrix?

Hello,
I am trying to hide (or possible show a calculated value in a subtotal)
a value in a matrix. My dataset returns something in this format.
RowHeader1, RowHeader2, ColumnName, ColumnType, Amount
1, 1, Total, Amount, 100
1, 1, Total2, Amount, 0
1, 1, Variance, Percent, 1.00
1, 2, Total, Amount, 50
1, 2, Total2, Amount, 55
1, 2, Variance, Percent, .10
I have row groups on RowHeader1 and RowHeader2. Also, I have a Column
Group on ColumnName and I have SUM(Fields!Amount.Value) in the Data
cell. The Matrix looks something like this:
Total Total2 Variance
1 1 100 0 100%
2 50 55 10%
TOTAL 150 55 110%
What I would like to do is either have the correct value in the
SubTotal field for the Variance (which I don't think is possible) or
just hide it. I tried to use InScope() as a start but I have been
getting nowhere.
Any help would be greatly appreciated.
Thanks,
AbeIs there a way to know if you are in the Subtotal Row or not?
Thanks|||Apparently you looked already at the InScope function. If you have multiple
row/column groupings you need to make sure that your conditional expression
considers all cases. E.g.
=iif(InScope("ColumnGroup1"), iif(InScope("RowGroup1"), "In Cell", "In
Subtotal of RowGroup1"), iif(InScope("RowGroup1"), "In Subtotal of
ColumnGroup1", "In Subtotal of entire matrix"))
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Abe" <abe@.flonet.com> wrote in message
news:1106156667.460735.143790@.c13g2000cwb.googlegroups.com...
> Is there a way to know if you are in the Subtotal Row or not?
> Thanks
>|||Thanks a lot for your help, I really appreciate it.

Hidden parameter using userid

I have created a hidden parameter with a default value of user!userid so I can pass this to a UDF which returns a table with data dependent on the logged on windows user. This works fine until I view the report using Report Manager when any alteration to any of the other parameters requires me to click twice on the View Report button before the report is displayed. The problem does not occur if I have a hidden parameter which either has a default value obtained from a query or is a constant. Any ideas on how I can overcome this? Thanks.I have since discovered the problem is not as stated but is due to the fact that I have hidden parameters dependent on the input of an earlier parameter. If the input parameter is a drop down then all is OK but if you have to interactively enter a value then you have to click the View Report button twice. Removing the userid parameter meant that the dependent parameter was last in the list and again all was then OK; however, I still have some reports where I have more than one hidden parameter dependent on an earlier value so the problem still occurs.
"JohnH" wrote:
> I have created a hidden parameter with a default value of user!userid so I can pass this to a UDF which returns a table with data dependent on the logged on windows user. This works fine until I view the report using Report Manager when any alteration to any of the other parameters requires me to click twice on the View Report button before the report is displayed. The problem does not occur if I have a hidden parameter which either has a default value obtained from a query or is a constant. Any ideas on how I can overcome this? Thanks.

heterogeneous Function

Hello Everyone,
I have a procedure which returns profits made by sales persons.
for this I need to access 2 data bases.
to calculate the profit I have written a function on one of the databases
but I get an error asking me to set ANSI_NULLS and ANSI_WARNINGS. when I set
these I get syntax error.
Am I missing to do something here.
Any help is greatly appreciated.
Rose.Without a repro, here is a guess: Make sure you have the settings while
creating the function.
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
GO
CREATE FUNCTION ufn_test(...
...
GO
Anith

Help-Corrupt table

I have a table that got corrupted last night somehow.
dbcc checktable on the table returns:
Server: Msg 7995, Level 16, State 3, Line 1
Database 'MPF' consistency errors in sysobjects,
sysindexes, syscolumns, or systypes prevent further CHECK
processing.
DBCC execution completed. If DBCC printed error messages,
contact your system administrator.
I've tried all 3 options including REPAIR_ALLOW_DATA_LOSS
and they all get the same results (above). I can't select
from the table either. I backed up the db and restored it
to another server...same thing. What all are my options
at this point? Is there anyway to fix the table without
going to backup?
Thanks,
VanHave you tried a DBCC CHECKDB ?
Peter
"The length of this document defends it well against the
risk of its being read."
Winston Churchill
>--Original Message--
>I have a table that got corrupted last night somehow.
>dbcc checktable on the table returns:
>Server: Msg 7995, Level 16, State 3, Line 1
>Database 'MPF' consistency errors in sysobjects,
>sysindexes, syscolumns, or systypes prevent further CHECK
>processing.
>DBCC execution completed. If DBCC printed error messages,
>contact your system administrator.
>I've tried all 3 options including REPAIR_ALLOW_DATA_LOSS
>and they all get the same results (above). I can't
select
>from the table either. I backed up the db and restored
it
>to another server...same thing. What all are my options
>at this point? Is there anyway to fix the table without
>going to backup?
>Thanks,
>Van
>.
>|||Yes. DBCC CheckTable is basically the same thing except
it only does one table. DBCC CheckDB generated the same
error.
>--Original Message--
>Have you tried a DBCC CHECKDB ?
>Peter
>"The length of this document defends it well against the
>risk of its being read."
>Winston Churchill
>
>>--Original Message--
>>I have a table that got corrupted last night somehow.
>>dbcc checktable on the table returns:
>>Server: Msg 7995, Level 16, State 3, Line 1
>>Database 'MPF' consistency errors in sysobjects,
>>sysindexes, syscolumns, or systypes prevent further
CHECK
>>processing.
>>DBCC execution completed. If DBCC printed error
messages,
>>contact your system administrator.
>>I've tried all 3 options including
REPAIR_ALLOW_DATA_LOSS
>>and they all get the same results (above). I can't
>select
>>from the table either. I backed up the db and restored
>it
>>to another server...same thing. What all are my options
>>at this point? Is there anyway to fix the table without
>>going to backup?
>>Thanks,
>>Van
>>.
>.
>|||In that case no - sorry, you will need to restore from
backup.
If you still have access to your Databases Data then you
could re-create it using the generate SQL (but that might
not work as it looks at the tables that caused the error),
DTS it, delete the old one, then rename the new one.
BTW CHECKTABLE and CHECKDB are different if you check BOL
under DBCC CHECKDB you will see 'DBCC CHECKDB performs the
same checking as if both a DBCC CHECKALLOC statement and a
DBCC CHECKTABLE statement were executed for each table in
the database.'
Anyway sorry I can't be of more help.
Peter
"Age is an issue of mind over matter. If you don't mind,
it doesn't matter."
Mark Twain
>--Original Message--
>Yes. DBCC CheckTable is basically the same thing except
>it only does one table. DBCC CheckDB generated the same
>error.
>>--Original Message--
>>Have you tried a DBCC CHECKDB ?
>>Peter
>>"The length of this document defends it well against the
>>risk of its being read."
>>Winston Churchill
>>
>>--Original Message--
>>I have a table that got corrupted last night somehow.
>>dbcc checktable on the table returns:
>>Server: Msg 7995, Level 16, State 3, Line 1
>>Database 'MPF' consistency errors in sysobjects,
>>sysindexes, syscolumns, or systypes prevent further
>CHECK
>>processing.
>>DBCC execution completed. If DBCC printed error
>messages,
>>contact your system administrator.
>>I've tried all 3 options including
>REPAIR_ALLOW_DATA_LOSS
>>and they all get the same results (above). I can't
>>select
>>from the table either. I backed up the db and restored
>>it
>>to another server...same thing. What all are my
options
>>at this point? Is there anyway to fix the table
without
>>going to backup?
>>Thanks,
>>Van
>>.
>>.
>.
>|||http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"VanColeJones" <anonymous@.discussions.microsoft.com> wrote in message
news:064801c518fd$15f1d550$a601280a@.phx.gbl...
> Yes. DBCC CheckTable is basically the same thing except
> it only does one table. DBCC CheckDB generated the same
> error.
>>--Original Message--
>>Have you tried a DBCC CHECKDB ?
>>Peter
>>"The length of this document defends it well against the
>>risk of its being read."
>>Winston Churchill
>>
>>--Original Message--
>>I have a table that got corrupted last night somehow.
>>dbcc checktable on the table returns:
>>Server: Msg 7995, Level 16, State 3, Line 1
>>Database 'MPF' consistency errors in sysobjects,
>>sysindexes, syscolumns, or systypes prevent further
> CHECK
>>processing.
>>DBCC execution completed. If DBCC printed error
> messages,
>>contact your system administrator.
>>I've tried all 3 options including
> REPAIR_ALLOW_DATA_LOSS
>>and they all get the same results (above). I can't
>>select
>>from the table either. I backed up the db and restored
>>it
>>to another server...same thing. What all are my options
>>at this point? Is there anyway to fix the table without
>>going to backup?
>>Thanks,
>>Van
>>.
>>.|||I see a hypothetical index on the corrupt table named "ĸ8=04". It's not really a lower case "k". It's something else and the square is somekind a carrage return or something because there is a blank line between this index name and the next. This index is listed as a clustered, hypothetical index. I already have a clustered index on this table (different fields). Not sure if a clustered hypothetical index is valid. And the name? I tried to drop it. Said it didn't exist but left the square out of the name (like it didn't see the square when it tried to drop it. So I u used brackets to refference it, and it says it's still not there (with the square this time).
>--Original Message--
>In that case no - sorry, you will need to restore from >backup.
>If you still have access to your Databases Data then you >could re-create it using the generate SQL (but that might >not work as it looks at the tables that caused the error), >DTS it, delete the old one, then rename the new one.
>BTW CHECKTABLE and CHECKDB are different if you check BOL >under DBCC CHECKDB you will see 'DBCC CHECKDB performs the >same checking as if both a DBCC CHECKALLOC statement and a >DBCC CHECKTABLE statement were executed for each table in >the database.'
>Anyway sorry I can't be of more help.
>Peter
>"Age is an issue of mind over matter. If you don't mind, >it doesn't matter." >Mark Twain >
>>--Original Message--
>>Yes. DBCC CheckTable is basically the same thing except >>it only does one table. DBCC CheckDB generated the same >>error.
>>--Original Message--
>>Have you tried a DBCC CHECKDB ?
>>Peter
>>"The length of this document defends it well against the >>risk of its being read." >>Winston Churchill >>
>>--Original Message--
>>I have a table that got corrupted last night somehow. >>dbcc checktable on the table returns:
>>Server: Msg 7995, Level 16, State 3, Line 1
>>Database 'MPF' consistency errors in sysobjects, >>sysindexes, syscolumns, or systypes prevent further >>CHECK >>processing.
>>DBCC execution completed. If DBCC printed error >>messages, >>contact your system administrator.
>>I've tried all 3 options including >>REPAIR_ALLOW_DATA_LOSS >>and they all get the same results (above). I can't >>select >>from the table either. I backed up the db and restored >>it >>to another server...same thing. What all are my >options >>at this point? Is there anyway to fix the table >without >>going to backup?
>>Thanks,
>>Van
>>.
>>.
>>.
>.
>|||Hmm Interesting, this maybe of help.
http://support.microsoft.com/default.aspx?scid=3Dkb;en-
us;818097
Peter
"All generalizations are false, including this one."
Mark Twain
>--Original Message--
>I see a hypothetical index on the corrupt table >named "ĸ8=04". It's not really a lower case "k". It's >something else and the square is somekind a carrage return >or something because there is a blank line between this >index name and the next. This index is listed as a >clustered, hypothetical index. I already have a clustered >index on this table (different fields). Not sure if a >clustered hypothetical index is valid. And the name? I >tried to drop it. Said it didn't exist but left the >square out of the name (like it didn't see the square when >it tried to drop it. So I u used brackets to refference >it, and it says it's still not there (with the square this >time).
>>--Original Message--
>>In that case no - sorry, you will need to restore from >>backup.
>>If you still have access to your Databases Data then you >>could re-create it using the generate SQL (but that might >>not work as it looks at the tables that caused the >error), >>DTS it, delete the old one, then rename the new one.
>>BTW CHECKTABLE and CHECKDB are different if you check BOL >>under DBCC CHECKDB you will see 'DBCC CHECKDB performs >the >>same checking as if both a DBCC CHECKALLOC statement and >a >>DBCC CHECKTABLE statement were executed for each table in >>the database.'
>>Anyway sorry I can't be of more help.
>>Peter
>>"Age is an issue of mind over matter. If you don't mind, >>it doesn't matter." >>Mark Twain >>
>>--Original Message--
>>Yes. DBCC CheckTable is basically the same thing except >>it only does one table. DBCC CheckDB generated the same >>error.
>>--Original Message--
>>Have you tried a DBCC CHECKDB ?
>>Peter
>>"The length of this document defends it well against >the >>risk of its being read." >>Winston Churchill >>
>>--Original Message--
>>I have a table that got corrupted last night somehow. >>dbcc checktable on the table returns:
>>Server: Msg 7995, Level 16, State 3, Line 1
>>Database 'MPF' consistency errors in sysobjects, >>sysindexes, syscolumns, or systypes prevent further >>CHECK >>processing.
>>DBCC execution completed. If DBCC printed error >>messages, >>contact your system administrator.
>>I've tried all 3 options including >>REPAIR_ALLOW_DATA_LOSS >>and they all get the same results (above). I can't >>select >>from the table either. I backed up the db and >restored >>it >>to another server...same thing. What all are my >>options >>at this point? Is there anyway to fix the table >>without >>going to backup?
>>Thanks,
>>Van
>>.
>>.
>>.
>>.
>.
>|||Thanks, that was it. Had to get the hotfix and test it and all, but that was the problem.
Thanks,
Van
>--Original Message--
>I see a hypothetical index on the corrupt table >named "ĸ8=04". It's not really a lower case "k". It's >something else and the square is somekind a carrage return >or something because there is a blank line between this >index name and the next. This index is listed as a >clustered, hypothetical index. I already have a clustered >index on this table (different fields). Not sure if a >clustered hypothetical index is valid. And the name? I >tried to drop it. Said it didn't exist but left the >square out of the name (like it didn't see the square when >it tried to drop it. So I u used brackets to refference >it, and it says it's still not there (with the square this >time).
>>--Original Message--
>>In that case no - sorry, you will need to restore from >>backup.
>>If you still have access to your Databases Data then you >>could re-create it using the generate SQL (but that might >>not work as it looks at the tables that caused the >error), >>DTS it, delete the old one, then rename the new one.
>>BTW CHECKTABLE and CHECKDB are different if you check BOL >>under DBCC CHECKDB you will see 'DBCC CHECKDB performs >the >>same checking as if both a DBCC CHECKALLOC statement and >a >>DBCC CHECKTABLE statement were executed for each table in >>the database.'
>>Anyway sorry I can't be of more help.
>>Peter
>>"Age is an issue of mind over matter. If you don't mind, >>it doesn't matter." >>Mark Twain >>
>>--Original Message--
>>Yes. DBCC CheckTable is basically the same thing except >>it only does one table. DBCC CheckDB generated the same >>error.
>>--Original Message--
>>Have you tried a DBCC CHECKDB ?
>>Peter
>>"The length of this document defends it well against >the >>risk of its being read." >>Winston Churchill >>
>>--Original Message--
>>I have a table that got corrupted last night somehow. >>dbcc checktable on the table returns:
>>Server: Msg 7995, Level 16, State 3, Line 1
>>Database 'MPF' consistency errors in sysobjects, >>sysindexes, syscolumns, or systypes prevent further >>CHECK >>processing.
>>DBCC execution completed. If DBCC printed error >>messages, >>contact your system administrator.
>>I've tried all 3 options including >>REPAIR_ALLOW_DATA_LOSS >>and they all get the same results (above). I can't >>select >>from the table either. I backed up the db and >restored >>it >>to another server...same thing. What all are my >>options >>at this point? Is there anyway to fix the table >>without >>going to backup?
>>Thanks,
>>Van
>>.
>>.
>>.
>>.
>.
>|||That KB has nothing whatsoever do to with the problem described. CHECKDB
won't do anything different from CHECKTABLE in this case. You've got system
table corruption that DBCC repairs will not fix so you must restore from
your last known good backup. Tibor's link is a good set of steps to follow.
Regards
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Peter The Spate" <anonymous@.discussions.microsoft.com> wrote in message
news:068c01c51903$51607780$a601280a@.phx.gbl...
Hmm Interesting, this maybe of help.
http://support.microsoft.com/default.aspx?scid=kb;en-
us;818097
Peter
"All generalizations are false, including this one."
Mark Twain
>--Original Message--
>I see a hypothetical index on the corrupt table
>named "ĸ8 ". It's not really a lower case "k". It's
>something else and the square is somekind a carrage
return
>or something because there is a blank line between this
>index name and the next. This index is listed as a
>clustered, hypothetical index. I already have a
clustered
>index on this table (different fields). Not sure if a
>clustered hypothetical index is valid. And the name? I
>tried to drop it. Said it didn't exist but left the
>square out of the name (like it didn't see the square
when
>it tried to drop it. So I u used brackets to refference
>it, and it says it's still not there (with the square
this
>time).
>>--Original Message--
>>In that case no - sorry, you will need to restore from
>>backup.
>>If you still have access to your Databases Data then you
>>could re-create it using the generate SQL (but that
might
>>not work as it looks at the tables that caused the
>error),
>>DTS it, delete the old one, then rename the new one.
>>BTW CHECKTABLE and CHECKDB are different if you check
BOL
>>under DBCC CHECKDB you will see 'DBCC CHECKDB performs
>the
>>same checking as if both a DBCC CHECKALLOC statement and
>a
>>DBCC CHECKTABLE statement were executed for each table
in
>>the database.'
>>Anyway sorry I can't be of more help.
>>Peter
>>"Age is an issue of mind over matter. If you don't mind,
>>it doesn't matter."
>>Mark Twain
>>
>>--Original Message--
>>Yes. DBCC CheckTable is basically the same thing
except
>>it only does one table. DBCC CheckDB generated the
same
>>error.
>>--Original Message--
>>Have you tried a DBCC CHECKDB ?
>>Peter
>>"The length of this document defends it well against
>the
>>risk of its being read."
>>Winston Churchill
>>
>>--Original Message--
>>I have a table that got corrupted last night
somehow.
>>dbcc checktable on the table returns:
>>Server: Msg 7995, Level 16, State 3, Line 1
>>Database 'MPF' consistency errors in sysobjects,
>>sysindexes, syscolumns, or systypes prevent further
>>CHECK
>>processing.
>>DBCC execution completed. If DBCC printed error
>>messages,
>>contact your system administrator.
>>I've tried all 3 options including
>>REPAIR_ALLOW_DATA_LOSS
>>and they all get the same results (above). I can't
>>select
>>from the table either. I backed up the db and
>restored
>>it
>>to another server...same thing. What all are my
>>options
>>at this point? Is there anyway to fix the table
>>without
>>going to backup?
>>Thanks,
>>Van
>>.
>>.
>>.
>>.
>.
>|||Oops - mistyped the URL and looked at the wrong KB article. Well done Peter.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:e4R1dHRGFHA.1292@.TK2MSFTNGP10.phx.gbl...
> That KB has nothing whatsoever do to with the problem described. CHECKDB
> won't do anything different from CHECKTABLE in this case. You've got
system
> table corruption that DBCC repairs will not fix so you must restore from
> your last known good backup. Tibor's link is a good set of steps to
follow.
> Regards
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Peter The Spate" <anonymous@.discussions.microsoft.com> wrote in message
> news:068c01c51903$51607780$a601280a@.phx.gbl...
> Hmm Interesting, this maybe of help.
> http://support.microsoft.com/default.aspx?scid=kb;en-
> us;818097
> Peter
> "All generalizations are false, including this one."
> Mark Twain
>
> >--Original Message--
> >I see a hypothetical index on the corrupt table
> >named "ĸ8 ". It's not really a lower case "k". It's
> >something else and the square is somekind a carrage
> return
> >or something because there is a blank line between this
> >index name and the next. This index is listed as a
> >clustered, hypothetical index. I already have a
> clustered
> >index on this table (different fields). Not sure if a
> >clustered hypothetical index is valid. And the name? I
> >tried to drop it. Said it didn't exist but left the
> >square out of the name (like it didn't see the square
> when
> >it tried to drop it. So I u used brackets to refference
> >it, and it says it's still not there (with the square
> this
> >time).
> >
> >>--Original Message--
> >>In that case no - sorry, you will need to restore from
> >>backup.
> >>
> >>If you still have access to your Databases Data then you
> >>could re-create it using the generate SQL (but that
> might
> >>not work as it looks at the tables that caused the
> >error),
> >>DTS it, delete the old one, then rename the new one.
> >>
> >>BTW CHECKTABLE and CHECKDB are different if you check
> BOL
> >>under DBCC CHECKDB you will see 'DBCC CHECKDB performs
> >the
> >>same checking as if both a DBCC CHECKALLOC statement and
> >a
> >>DBCC CHECKTABLE statement were executed for each table
> in
> >>the database.'
> >>
> >>Anyway sorry I can't be of more help.
> >>Peter
> >>
> >>"Age is an issue of mind over matter. If you don't mind,
> >>it doesn't matter."
> >>Mark Twain
> >>
> >>
> >>--Original Message--
> >>Yes. DBCC CheckTable is basically the same thing
> except
> >>it only does one table. DBCC CheckDB generated the
> same
> >>error.
> >>
> >>--Original Message--
> >>Have you tried a DBCC CHECKDB ?
> >>
> >>Peter
> >>
> >>"The length of this document defends it well against
> >the
> >>risk of its being read."
> >>Winston Churchill
> >>
> >>
> >>--Original Message--
> >>I have a table that got corrupted last night
> somehow.
> >>dbcc checktable on the table returns:
> >>
> >>Server: Msg 7995, Level 16, State 3, Line 1
> >>Database 'MPF' consistency errors in sysobjects,
> >>sysindexes, syscolumns, or systypes prevent further
> >>CHECK
> >>processing.
> >>DBCC execution completed. If DBCC printed error
> >>messages,
> >>contact your system administrator.
> >>
> >>I've tried all 3 options including
> >>REPAIR_ALLOW_DATA_LOSS
> >>and they all get the same results (above). I can't
> >>select
> >>from the table either. I backed up the db and
> >restored
> >>it
> >>to another server...same thing. What all are my
> >>options
> >>at this point? Is there anyway to fix the table
> >>without
> >>going to backup?
> >>
> >>Thanks,
> >>
> >>Van
> >>.
> >>
> >>.
> >>
> >>.
> >>
> >>.
> >>
> >.
> >
>|||This is just a followup if anybody's curious. This hotfix didn't totally fix the error. DBCC CheckTable and CheckDB were still giving errors. Exception_Access_Violations were still occuring and the SQL server was actually stopping itself. The hotfix did at least make it so that I could at least select from the table and recreate it without having to go to backup. So I just recreated the table and dropped the old one. All is good now...
>--Original Message--
>Thanks, that was it. Had to get the hotfix and test it >and all, but that was the problem.
>Thanks,
>Van
>>--Original Message--
>>I see a hypothetical index on the corrupt table >>named "ĸ8=04". It's not really a lower case "k". It's >>something else and the square is somekind a carrage >return >>or something because there is a blank line between this >>index name and the next. This index is listed as a >>clustered, hypothetical index. I already have a >clustered >>index on this table (different fields). Not sure if a >>clustered hypothetical index is valid. And the name? I >>tried to drop it. Said it didn't exist but left the >>square out of the name (like it didn't see the square >when >>it tried to drop it. So I u used brackets to refference >>it, and it says it's still not there (with the square >this >>time).
>>--Original Message--
>>In that case no - sorry, you will need to restore from >>backup.
>>If you still have access to your Databases Data then you >>could re-create it using the generate SQL (but that >might >>not work as it looks at the tables that caused the >>error), >>DTS it, delete the old one, then rename the new one.
>>BTW CHECKTABLE and CHECKDB are different if you check >BOL >>under DBCC CHECKDB you will see 'DBCC CHECKDB performs >>the >>same checking as if both a DBCC CHECKALLOC statement and >>a >>DBCC CHECKTABLE statement were executed for each table >in >>the database.'
>>Anyway sorry I can't be of more help.
>>Peter
>>"Age is an issue of mind over matter. If you don't mind, >>it doesn't matter." >>Mark Twain >>
>>--Original Message--
>>Yes. DBCC CheckTable is basically the same thing >except >>it only does one table. DBCC CheckDB generated the >same >>error.
>>--Original Message--
>>Have you tried a DBCC CHECKDB ?
>>Peter
>>"The length of this document defends it well against >>the >>risk of its being read." >>Winston Churchill >>
>>--Original Message--
>>I have a table that got corrupted last night >somehow. >>dbcc checktable on the table returns:
>>Server: Msg 7995, Level 16, State 3, Line 1
>>Database 'MPF' consistency errors in sysobjects, >>sysindexes, syscolumns, or systypes prevent further >>CHECK >>processing.
>>DBCC execution completed. If DBCC printed error >>messages, >>contact your system administrator.
>>I've tried all 3 options including >>REPAIR_ALLOW_DATA_LOSS >>and they all get the same results (above). I can't >>select >>from the table either. I backed up the db and >>restored >>it >>to another server...same thing. What all are my >>options >>at this point? Is there anyway to fix the table >>without >>going to backup?
>>Thanks,
>>Van
>>.
>>.
>>.
>>.
>>.
>.
>

Help: No mapping between account names and security IDs was done

Hi,
My report returns this error: "No mapping between account names and security
IDs was done. (Exception from HRESULT: 0x80070534)", I have spent a lot of
time to solve (set up Role/Datasource etc) but fail. I do know what the real
issue is, can you help me or direction to solve this problem.
My reporting server in DMZ zone and talk to internal lan SQL server via
opened 1433 port.
Many Many Thanks.
FRankiePlease help
> Hi,
> My report returns this error: "No mapping between account names and
> security IDs was done. (Exception from HRESULT: 0x80070534)", I have spent
> a lot of time to solve (set up Role/Datasource etc) but fail. I do know
> what the real issue is, can you help me or direction to solve this
> problem.
> My reporting server in DMZ zone and talk to internal lan SQL server via
> opened 1433 port.
> Many Many Thanks.
> FRankie
>
>

HELP: How do I pass variables from Control Flow to Data Flow

I have an Execute SQL Task that returns a Full Rowset from a SQL Server table and assigns it to a variable objRecs. I connect that to a foreach container with an ADO enumerator using objRecs variable and Rows in first table mode. I defined variables and mapped them to the columns.

I tested this by placing a Script task inside the foreach container and displaying the variables in a messagebox.

Now, for each row, I want to write a record to an MS Access table and then update a column back in the original SQL Server table where I retreived data in the Execute SQL task (i have the primary key). If I drop a Data Flow Task inside my foreach container, how do I pass the variables as input to an OLE DB Destination on the Data Flow?

Also, how would I update the original source table where source.id = objRects.id?

Thank you for your assistance. I have spent the day trying to figure this out (and thought it would be simple), but I am just not getting SSIS. Sorry if this has been covered.

Thanks,

Steve

smcneese wrote:

I have an Execute SQL Task that returns a Full Rowset from a SQL Server table and assigns it to a variable objRecs. I connect that to a foreach container with an ADO enumerator using objRecs variable and Rows in first table mode. I defined variables and mapped them to the columns.

I tested this by placing a Script task inside the foreach container and displaying the variables in a messagebox.

Now, for each row, I want to write a record to an MS Access table and then update a column back in the original SQL Server table where I retreived data in the Execute SQL task (i have the primary key). If I drop a Data Flow Task inside my foreach container, how do I pass the variables as input to an OLE DB Destination on the Data Flow?

Also, how would I update the original source table where source.id = objRects.id?

Thank you for your assistance. I have spent the day trying to figure this out (and thought it would be simple), but I am just not getting SSIS. Sorry if this has been covered.

Thanks,

Steve

You can use the Derived Column Component to put variable values into the pipeline.

-Jamie

|||Use a multicast and an OLE DB Command to perform the update on the original table.|||

I looked at this, but the Derived Column Component requires an input also. How do I configure that?

Thanks,

Steve

|||

smcneese wrote:

I looked at this, but the Derived Column Component requires an input also. How do I configure that?

Thanks,

Steve

You can't use Derived Column Component as a source adapter so in this case you'll have to use a script component.

I would question as to whether this is really what you want to do though. Executing a data-flow just to process one row of data with one column in it seems like the proverbial sledgehammer to crack a nut. What are you trying to achieve exactly?

-Jamie

|||

I question it myself. ;) I am trying to perform what I thought was a simple task, but I don't know DTS/SSIS well enough to know the correct approach. I spent all day trying to read articles to understand, but I am just not getting it.

I have a table is SQL Server 2005 that gets status records written to it for events that occur on wireless phones. When a record is written into this table, I need to write a record into a table of a legacy application running in Access. My original thought was to write a managed stored procedure in C# but someone suggested SSIS so I thought I would give it a try. I would think this to be a simple problem to solve and exactly what SSIS is for, but I cannot figure it out.

Can you or someone point me in the right direction? My current approach used an Execute SQL task to read all of the records that have not been processed. Then I used a Foreach container thinking I could take each row, write it to Access and then update that original source row to indicate it was processed. I would prefer not to do it row by row, but the source table is constantly being written to so that needs to be accounted for. Any help for your experts would be greatly appreciated.

Thanks,

Steve

|||

smcneese wrote:

I question it myself. ;) I am trying to perform what I thought was a simple task, but I don't know DTS/SSIS well enough to know the correct approach. I spent all day trying to read articles to understand, but I am just not getting it.

I have a table is SQL Server 2005 that gets status records written to it for events that occur on wireless phones. When a record is written into this table, I need to write a record into a table of a legacy application running in Access. My original thought was to write a managed stored procedure in C# but someone suggested SSIS so I thought I would give it a try. I would think this to be a simple problem to solve and exactly what SSIS is for, but I cannot figure it out.

Can you or someone point me in the right direction? My current approach used an Execute SQL task to read all of the records that have not been processed. Then I used a Foreach container thinking I could take each row, write it to Access and then update that original source row to indicate it was processed. I would prefer not to do it row by row, but the source table is constantly being written to so that needs to be accounted for. Any help for your experts would be greatly appreciated.

Thanks,

Steve

I don't really see a problem updating the table while it is being written to. Modern RDBMSs are supposed to be handle multiple concurrent connections to the same resource - that's kinda the point.

However, you say that when a record is written to the table in SQL Server 2005 you need to write it to the Access application. That is inherently a real-time operation and SSIS is a tool for batch operations. Given that 10000foot view of the problem I don't think SSIS is what you want. If you do NOT need to write to the access application at the same time as the record arrives then this becomes more of a batch operation and SSIS will be more suitable.

-Jamie

|||

I should have stated "near real time". Doing this batch every x minutes is fine. My point was about concurrent operation on the RDBMS. If I use and ADO .NET Input on the Data Flow with the appropriate SQL Statement to select my records I can then pass the results of that to the OLE ADO Ouput for Access. Now how can I use that same result set to got back and update my source table "processed" column so they don't get picked up again the next time the batch process runs? Can I split the output of the ADO.NET Input and use it as input to two other processes? Is that the way to do it?

Thanks for your help Jamie!

|||

smcneese wrote:

I should have stated "near real time". Doing this batch every x minutes is fine. My point was about concurrent operation on the RDBMS. If I use and ADO .NET Input on the Data Flow with the appropriate SQL Statement to select my records I can then pass the results of that to the OLE ADO Ouput for Access. Now how can I use that same result set to got back and update my source table "processed" column so they don't get picked up again the next time the batch process runs? Can I split the output of the ADO.NET Input and use it as input to two other processes? Is that the way to do it?

Thanks for your help Jamie!

Yes, that is the way to do it. And jwelch replied on this thread telling you exactly how.

-Jamie

|||

All,

Thank you for all of your help. Everything finally clicked on how this all works. Solution working fine, tested and in production.

Thanks,

Steve

|||

smcneese wrote:

All,

Thank you for all of your help. Everything finally clicked on how this all works. Solution working fine, tested and in production.

Thanks,

Steve

Good news. A happy customer for a change

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--