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

HelpColumnar Report with 3 Groupings?

Hi

I have sales data that needs to be grouped by Company, State and
Date. I would like to format the report as shown in image.

I am using Crystal Reports XI. I am new to Crystal Reports and
would like to find out whether this report is possible using either
columnar format or cross-tab?

Many ThanksPost a sample of your table(s). I can't help without knowing what you have to start with.

I have a feeling you'll need a crosstab|||Hi

I have data in two tables - Sales and Dealers.

Sales
-------
DealerCode
WeekEnding
NumberSold

Dealers
-------
DealerCode
CompanyName
State

I have a command in Crystal as follows:

SELECT d.CompanyName, d.State, s.WeekEnding, s.NumberSold
FROM sales s
LEFT JOIN dealers d
ON s.DealerCode = d.DealerCode
AND s.WeekEnding BETWEEN '2006-04-01' AND '2006-04-30'

I would like to group the data returned by CompanyName, State and Date to get a Total of NumberSold.

I would like to format the report in columns (as in image attachment). I would also like to have a two week total and a week-on-week figure.

If you need any further info, let me know. Thanks for your help!|||Look for cross tab reports in help file

Help--A error about memory!

I use linkedserver to get data from Oracle, but sometimes I get the error below:

Server: Msg 7399, Level 16, State 1, Procedure GetDataFromERP, Line 21
OLE DB provider 'OraOLEDB.Oracle' reported an error.
2006-02-09
[OLE/DB provider returned message: ]
[OLE/DB provider returned message: ROW-00001: Cannot allocate memory]
OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' ICommandText::Execute returned 0x80004005: ].

I think maybe it is because the data is very large.
How do I solve this problem?What is the statement you are executing against the linked server?|||The statement is "select ..... from openquery(......)"
The provider of linkedserver is "Oracle provider for OLE DB"|||That doesn't help much. That is what you are executing locally.

What is the statement you are executing against the linked server?|||This is the main statment of my store procedure:


INSERT INTO
[ARA].[dbo].[AgingData]
([RO], [NS], [CustomerID], [CustomerCode], [CustomerName],
[TransactionalCurrency], [ROCurrency], [CurrentByTrx], [Over1_15ByTrx],
[Over16_30ByTrx], [Over31_60ByTrx], [Over61_90ByTrx], [Over90ByTrx],
[CurrentByRO], [Over1_15ByRO], [Over16_30ByRO], [Over31_60ByRO],
[Over61_90ByRO], [Over90ByRO], [CurrentByUSD], [Over1_15ByUSD],
[Over16_30ByUSD], [Over31_60ByUSD], [Over61_90ByUSD], [Over90ByUSD],
[TotalAmountByTraCur], [TotalAmountByROCur], [TotalAmountByUSD],
[ProductLine], [AgedDate], [ProcessDate], [Tenor_Flag])
SELECT [RO_NAME], [NS_NAME], [CUST_ID], [CUST_NO], [CUST_NAME], [TRX_CURR], [RO_CURR],
[TRX_AMOUNT_BUCKET_1], [TRX_AMOUNT_BUCKET_2], [TRX_AMOUNT_BUCKET_3],
[TRX_AMOUNT_BUCKET_4], [TRX_AMOUNT_BUCKET_5], [TRX_AMOUNT_BUCKET_6],
[RO_CURR_AMOUNT_BUCKET_1], [RO_CURR_AMOUNT_BUCKET_2], [RO_CURR_AMOUNT_BUCKET_3],
[RO_CURR_AMOUNT_BUCKET_4], [RO_CURR_AMOUNT_BUCKET_5], [RO_CURR_AMOUNT_BUCKET_6],
[USD_CURR_AMOUNT_BUCKET_1], [USD_CURR_AMOUNT_BUCKET_2], [USD_CURR_AMOUNT_BUCKET_3],
[USD_CURR_AMOUNT_BUCKET_4], [USD_CURR_AMOUNT_BUCKET_5], [USD_CURR_AMOUNT_BUCKET_6],
[TOTAL_TRX_AMOUNT], [TOTAL_AMOUNT_WITH_RO_CURR], [TOTAL_AMOUNT_WITH_USD_CURR],
[PRODLINE], [AGED_DATE], [PROCESS_DATE], 1
FROM OPENQUERY(ORACLE_BQE,
'SELECT RO_NAME, NS_NAME, CUST_ID, CUST_NO, CUST_NAME, TRX_CURR, RO_CURR,
TRX_AMOUNT_BUCKET_1, TRX_AMOUNT_BUCKET_2, TRX_AMOUNT_BUCKET_3,
TRX_AMOUNT_BUCKET_4, TRX_AMOUNT_BUCKET_5, TRX_AMOUNT_BUCKET_6,
RO_CURR_AMOUNT_BUCKET_1, RO_CURR_AMOUNT_BUCKET_2, RO_CURR_AMOUNT_BUCKET_3,
RO_CURR_AMOUNT_BUCKET_4, RO_CURR_AMOUNT_BUCKET_5, RO_CURR_AMOUNT_BUCKET_6,
USD_CURR_AMOUNT_BUCKET_1, USD_CURR_AMOUNT_BUCKET_2, USD_CURR_AMOUNT_BUCKET_3,
USD_CURR_AMOUNT_BUCKET_4, USD_CURR_AMOUNT_BUCKET_5, USD_CURR_AMOUNT_BUCKET_6,
TOTAL_TRX_AMOUNT, TOTAL_AMOUNT_WITH_RO_CURR, TOTAL_AMOUNT_WITH_USD_CURR,
PRODLINE, TO_CHAR(AGED_DATE) AS AGED_DATE, TO_CHAR(PROCESS_DATE) AS PROCESS_DATE
FROM APPS.XXAR_ARA_AGE_DETAIL')
WHERE CONVERT(NVARCHAR(10), AGED_DATE) = @.BEGINDATE

I just get data from Oracle and insert them to the local table, it is not complicated. But there are another three similar statement in this store procedure.|||Any way you can filter the data on the Oracle side? Try executing your statement as dynamic SQL, which will allow you to pass the @.BEGINDATE value within your OPENQUERY statement, and should reduce the volume of data returned.|||I have solved this problem by filtering data on the Oracle side!
Thank you very much!

Help: Table name displaying twice in SQL Statement inside SQL Server Enterprise Manager

For some reason whenever I look at the SQL statement of a particular table, the table name displays twice.
For example,
SELECT * FROM State State
Even when I execute this statement, it still returns the correct results. It does this for all tables in this particular database. I also check another database and thoses display the table names in the SQL statements correctly.
Does anyone know why the table name would display twice in a table inside of a particular database?

How (where) are these statements being generated? All this is doing is creating an alias for the table with its own name, so this won't hurt anything. It's much like doing:
SELECT A.Field1 FROM State A
Marcie|||

datagridgirl wrote:

How (where) are these statements being generated? All this is doing is creating an alias for the table with its own name, so this won't hurt anything. It's much like doing:
SELECT A.Field1 FROM State A
Marcie

It just came up whenever I look at the SQL statement (SQL Pane) after "Returning All Rows" of a particular table. I guess it is not a big deal. I just don't know why it would do that.|||

voodoo9055 wrote:

It just came up whenever I look at the SQL statement (SQL Pane) after "Returning All Rows" of a particular table. I guess it is not a big deal. I just don't know why it would do that.

I'm curious now...in what tool? I'm not seeing this behavior in either VS.NET (via Server Explorer) or Enterprise Manager...
Marcie|||

datagridgirl wrote:

voodoo9055 wrote:

It just came up whenever I look at the SQL statement (SQL Pane) after "Returning All Rows" of a particular table. I guess it is not a big deal. I just don't know why it would do that.

I'm curious now...in what tool? I'm not seeing this behavior in either VS.NET (via Server Explorer) or Enterprise Manager...
Marcie

Enterprise Manager.
It displays that way for one of my databases. The rest display normally.

Help: KILL doesn't kill

I'm trying to kill a process (BACKUP DATABASE). It goes into KILLED/ROLLBACK
state. Kill WITH STATUSONLY says it's 100% rolled back with 0 time left to
finish. But the task stays around, and keeps both S and X locks on the
database.
This is apparently a repeatable problem. Is this an SQL Server 2000 bug?
What can I do? The server is a live application, I don't want to have to
cycle the server.
Thanks.I've heard of this happening and that it's an issue with SQL 2000 SP4,
related to a concurrency fix in Tempdb.
"Scott" wrote:
> I'm trying to kill a process (BACKUP DATABASE). It goes into KILLED/ROLLBACK
> state. Kill WITH STATUSONLY says it's 100% rolled back with 0 time left to
> finish. But the task stays around, and keeps both S and X locks on the
> database.
> This is apparently a repeatable problem. Is this an SQL Server 2000 bug?
> What can I do? The server is a live application, I don't want to have to
> cycle the server.
> Thanks.
>

Help: Error Log

Dear all,
After I checked the SQL Server Log i found error like this:
1. Error: 17883, Severity: 1, State: 0
What does it mean?
2. Login failed for user 'sa'.
Almost every minute this error occured.
Does someone know what's happening to my SQL Server?
Thanks
Robert Lie
Robert,
1. This message means your scheduler appears to be hung. See the following
for more info:
New concurrency and scheduling diagnostics added to SQL Server
http://support.microsoft.com/kb/319892/
2. This means something is trying to login to SQL Server (and failing). Run
SQL Profiler to see what this is. Whatever it is, it shouldn't be using the
sa account.
Mark.
"Robert Lie" wrote:

> Dear all,
> After I checked the SQL Server Log i found error like this:
> 1. Error: 17883, Severity: 1, State: 0
> What does it mean?
> 2. Login failed for user 'sa'.
> Almost every minute this error occured.
> Does someone know what's happening to my SQL Server?
> Thanks
> Robert Lie
>

Help: Error Log

Dear all,
After I checked the SQL Server Log i found error like this:
1. Error: 17883, Severity: 1, State: 0
What does it mean?
2. Login failed for user 'sa'.
Almost every minute this error occured.
Does someone know what's happening to my SQL Server?
Thanks
Robert LieRobert,
1. This message means your scheduler appears to be hung. See the following
for more info:
New concurrency and scheduling diagnostics added to SQL Server
http://support.microsoft.com/kb/319892/
2. This means something is trying to login to SQL Server (and failing). Run
SQL Profiler to see what this is. Whatever it is, it shouldn't be using the
sa account.
Mark.
"Robert Lie" wrote:

> Dear all,
> After I checked the SQL Server Log i found error like this:
> 1. Error: 17883, Severity: 1, State: 0
> What does it mean?
> 2. Login failed for user 'sa'.
> Almost every minute this error occured.
> Does someone know what's happening to my SQL Server?
> Thanks
> Robert Lie
>

Help: Error Log

Dear all,
After I checked the SQL Server Log i found error like this:
1. Error: 17883, Severity: 1, State: 0
What does it mean?
2. Login failed for user 'sa'.
Almost every minute this error occured.
Does someone know what's happening to my SQL Server?
Thanks
Robert LieRobert,
1. This message means your scheduler appears to be hung. See the following
for more info:
New concurrency and scheduling diagnostics added to SQL Server
http://support.microsoft.com/kb/319892/
2. This means something is trying to login to SQL Server (and failing). Run
SQL Profiler to see what this is. Whatever it is, it shouldn't be using the
sa account.
Mark.
"Robert Lie" wrote:
> Dear all,
> After I checked the SQL Server Log i found error like this:
> 1. Error: 17883, Severity: 1, State: 0
> What does it mean?
> 2. Login failed for user 'sa'.
> Almost every minute this error occured.
> Does someone know what's happening to my SQL Server?
> Thanks
> Robert Lie
>