Invisible rows after failed syncronization

Publisher: SQL Server 2000 SP4
Subscriber: SQL Server 2005 Mobile Edition

Sometimes, after a failed merge replication(due to communication error) some rows on the subscriber became invisible for the publisher.

I've reproduced a case:

[START Short Version]
All rows inserted between a comm failed sync and a fine sync became invisible.
This seems to be caused because the subscriber keeps generating rows with the same generation number and the publisher doesn't look for this generation anymore.
[END Short Version]

[START Detailed case]
STEP 0: INITIAL STATE
Publisher
Orders Table
EMPTY

Subscriber
NO DATABASE

STEP 1: After FIRST SYNC and INSERTING 2 Orders

Publisher

Orders Table

EMPTY

Subscriber

Orders Table
OrderId __sysIG __sysCG __sysMC
000001 4 4 81
000002 4 4 81

STEP 2: After SECOND SYNC

Publisher

Orders Table

OrderId

000001

000002

Subscriber

Orders Table

OrderId __sysIG __sysCG __sysMC

000001 4 4 80

000002 4 4 80

STEP 3: INSERT ANOTHER ORDER

Subscriber

Orders Table

OrderId __sysIG __sysCG __sysMC

000001 4 4 80

000002 4 4 80

000003 6 6 81

STEP 4: After THIRD SYNC (with comm error)
Error: [NativeError:28037][HRESULT:-2147012889]->[A request to send data to the computer running IIS has failed. For more information, see HRESULT.]
When: Error happens provoked after sending from Subscriber to Publisher but before ending syn process.
Publisher
Orders Table

OrderId

000001

000002

000003
Subscriber
Orders Table

OrderId __sysIG __sysCG __sysMC

000001 4 4 80

000002 4 4 80
000003 6 6 81
Note: Publisher has the row but subscriber keeps the state in 81.

STEP 5: INSERT ANOTHER ORDER (BEFORE A GOOD SYNC)
Subscriber
Orders Table

OrderId __sysIG __sysCG __sysMC

000001 4 4 80

000002 4 4 80
000003 6 6 81

000004 6 6 81
Note: Orders 000003 and 000004 have the same system info

STEP 6: After FOURTH SYNC
Publisher
Orders Table

OrderId
000001

000002

000003
Subscriber
Orders Table

OrderId __sysIG __sysCG __sysMC

000001 4 4 80

000002 4 4 80
000003 6 6 81

000004 6 6 81

Note: Even with a fine merge process Publisher didn't get the 000004 row and the Subscriber didn't update the __sysMC

STEP 7: INSERT ANOTHER ORDER

Subscriber

Orders Table
OrderId __sysIG __sysCG __sysMC
000001 4 4 80
000002 4 4 80

000003 6 6 81

000004 6 6 81

000005 8 8 81

STEP 8: After FIFTH SYNC

Publisher
Orders Table
OrderId
000001
000002
000003
000005
Subscriber
Orders Table
OrderId __sysIG __sysCG __sysMC
000001 4 4 80
000002 4 4 80
000003 6 6 81

000004 6 6 81

000002 8 8 80

Note: Data from generation 8 merge correctly, but data from generation 6 keeps invisible to publisher.
STEP 9: DUMMY UPDATE ON GEN 6 ROWS
Action: I made a non relevant update to force the Subscriber update the generation.
Subscriber
Orders Table

OrderId __sysIG __sysCG __sysMC

000001 4 4 80

000002 4 4 80
000003 6 10 81
000004 6 10 81
000005 8 8 80

Note: sysCG get its value updated correctly.

STEP 10: After SIXTH SYNC

Publisher
Orders Table
OrderId
000001
000002
000003
000004

000005
Subscriber
Orders Table
OrderId __sysIG __sysCG __sysMC
000001 4 4 80
000002 4 4 80

000003 6 6 80

000004 6 6 80

000002 8 8 80

Note: Data merges correctly.
[END Detailed case]

I could code a system that checks all the publication tables and updates all the last generation rows in case of communication error but i would really like to avoid doing it.

I don't know if I'm missing something or if this is a bug or a known issue.

This is a big problem for me because communication errors on cellular phone based connection are quite common and users keep working even if there's an error.

Any comment will be appreciated.Finally, I've modified my replication process solve this manually.

On a normal case (no previous error):
1. I get the current generation (__sysMergeSubscriptions.LastUploadedGen+1)
2. Launch a normal sync.
3. If it fails I save the failed generation for using it on next sync.

On the previous error case:
1. Do a normal sync (to get a valid __sysMergeSubscriptions.LastUploadedGen)
2. Do a dummy update on all rows (from all published tables (from __sysMergeArticles)) that __sysIG or __sysCG equals the wrong generation number.
3. Do a normal sync to send all the invisible rows to the publisher.

I don't like my own solution, but that's the only way I have to keep the system working as I haven't find any reference to this problem anywhere.|||

Hey MeZKaL,

I just have few questions to narrow downt he problem point in the whole path of sync components.

In the same above scenario, can you please also let me know the contents of __sysMergeSubscription and
__sysMergeSubscriptionProperties table for that subscription in each of the above STEPs (esp. Steps 4 to 6)?

Basically, I just want to know which of the following is true (after a failed sync)

1) Client did not enumerate the changes and hence did not send the changes

2) Client has enumerated the changes, sent the changes but metadata does not say its new change

3) Client has enumerated, sent the changes, metadata also says its a new change, but publisher ignores it for some internal
reasons like publisher metadata says its a false change ...etc

Thanks,

Laxmi

|||I repeated the case saving a copy of the mobile db for each step.

There are no changes on __sysMergeSubscriptionProperties so I will put it just one time.

__sysMergeSubscriptionProperties
SubscriptionId: 87a0e646-d908-b0b9-e2b1-d6eb58165c8f
Distributor
DistributorAddress
DistributorNetwork: 0
DistributorSecurityMode: 1
DistributorLogin
DistributorPassword
ExchangeType: 3
InternetLogin: domain\user
InternetURL: URL
InternetPassword: *******
LoginTimeout: 15
ProfileName: DEFAULT
Publisher: SERVER\INSTANCE
PublisherAddress
PublisherNetwork: 0
PublisherDatabase: DBName
PublisherSecurityMode: 1
PublisherLogin: domain\user
PublisherPassword: *******
Publication: PubName
QueryTimeout: 300
Validate: 0
HostName
Subscriber: domain\user
InternetProxyServer
InternetProxyLogin
InternetProxyPassword
ConnectionRetryTimeout: 120
CompressionLevel: 1
ConnectionManager: 0
SnapshotTransferType: 99

Many of the field value from __sysMergeSubscriptions didn't change during the process.

__sysMergeSubscriptions
Publisher: server\instance
PublisherDatabase: DBName
Publication: PubName
PublicationId: 03e497f6-83b6-4de3-87e3-e97835880c94
SubscriptionId: 87a0e646-d908-b0b9-e2b1-d6eb58165c8f
SyncPartnerId: 751a4dfe-2eba-7a4a-b11a-35beec9a8bf3
SyncAnchorId: 00000000-0000-0000-0000-000000000000
SchemaVersion: 481
SchemaVersionId: 00000000-0000-0000-0000-000000000000
ReplNick (casted to int): -312916250
Retention: 0
MachineId: 374677b5-b002-d9f5-d4e4-4628538f2ccd
PathName: Path\DB.sdf
ReinitRequested: 0
ReplProp: NULL
HostName:
ValidationHostName: 0
ReplicaState: 00000000-0000-0000-0000-000000000000
LastCleanupDate: 15/11/2006 16:30
ReconcilerVersion: 8
Subscriber: domain\user

[START Detailed case]

STEP 1: After FIRST SYNC
Publisher
Orders Table
EMPTY

Subscriber
Orders Table
EMPTY

__sysMergeSubscriptions
SyncAnchor LastUploadedGen LastSuccessfulSync SentGen80
4977 0 15/11/2006 16:30 70
SentGen80Id: 61327c39-f34b-15d1-3924-8e2577f94d6f

STEP 2: After INSERTING 2 Orders
Subscriber
Orders Table
OrderId __sysIG __sysCG __sysMC
57000 4 4 81
57001 4 4 81

__sysMergeSubscriptions
SyncAnchor LastUploadedGen LastSuccessfulSync SentGen80
4977 0 15/11/2006 16:30 70
SentGen80Id: 61327c39-f34b-15d1-3924-8e2577f94d6f

STEP 3: After SECOND SYNC (with comm error)
Error: [NativeError:28037][HRESULT:-2147012889]->[A request to send data to the computer running IIS has failed. For more information, see HRESULT.]
When: Error happens provoked after sending from Subscriber to Publisher but before ending syn process.
Publisher
Orders Table
OrderId
57000
57001

Subscriber
Orders Table
OrderId __sysIG __sysCG __sysMC
57000 4 4 81
57001 4 4 81

__sysMergeSubscriptions
SyncAnchor LastUploadedGen LastSuccessfulSync SentGen80
4977 0 15/11/2006 16:30 70
SentGen80Id: 61327c39-f34b-15d1-3924-8e2577f94d6f

STEP 4: INSERT ANOTHER ORDER
Subscriber
Orders Table
OrderId __sysIG __sysCG __sysMC
57000 4 4 81
57001 4 4 81
57002 4 4 81

__sysMergeSubscriptions
SyncAnchor LastUploadedGen LastSuccessfulSync SentGen80
4977 0 15/11/2006 16:30 70
SentGen80Id: 61327c39-f34b-15d1-3924-8e2577f94d6f

STEP 5: After THIRD SYNC
Publisher
Orders Table
OrderId
57000
57001

Subscriber
Orders Table
OrderId __sysIG __sysCG __sysMC
57000 4 4 81
57001 4 4 81
57002 4 4 81

__sysMergeSubscriptions
SyncAnchor LastUploadedGen LastSuccessfulSync SentGen80
5095 5 15/11/2006 17:22 71
SentGen80Id: e5ff3df6-195c-3145-273e-86784ace7e16

STEP 6: DUMMY UPDATE
Subscriber
Orders Table
OrderId __sysIG __sysCG __sysMC
57000 4 6 81
57001 4 6 81
57002 4 6 81

__sysMergeSubscriptions
SyncAnchor LastUploadedGen LastSuccessfulSync SentGen80
5095 5 15/11/2006 17:22 71
SentGen80Id: e5ff3df6-195c-3145-273e-86784ace7e16

STEP 7: After FOURTH SYNC
Publisher
Orders Table
OrderId
57000
57001
57002

Subscriber
Orders Table
OrderId __sysIG __sysCG __sysMC
57000 4 6 80
57001 4 6 80
57002 4 6 80

__sysMergeSubscriptions
SyncAnchor LastUploadedGen LastSuccessfulSync SentGen80
5213 7 15/11/2006 17:35 72
SentGen80Id
SentGen80Id: 4171a971-b963-a541-300a-0b1602902e39

[__sysMergeSubscriptions DETAIL]
SyncAnchor LastUploadedGen LastSuccessfulSync SentGen80
step1 4977 0 15/11/2006 16:30 70
step2 4977 0 15/11/2006 16:30 70
step3 4977 0 15/11/2006 16:30 70
step4 4977 0 15/11/2006 16:30 70
step5 5095 5 15/11/2006 17:22 71
step6 5095 5 15/11/2006 17:22 71
step7 5213 7 15/11/2006 17:35 72

SentGen80Id
61327c39-f34b-15d1-3924-8e2577f94d6f
61327c39-f34b-15d1-3924-8e2577f94d6f
61327c39-f34b-15d1-3924-8e2577f94d6f
61327c39-f34b-15d1-3924-8e2577f94d6f
e5ff3df6-195c-3145-273e-86784ace7e16
e5ff3df6-195c-3145-273e-86784ace7e16
4171a971-b963-a541-300a-0b1602902e39|||Can you open a case with CSS please for this? That way we can get a better handle on this in a timely manner.|||My workaround runs pretty well and I don't have time now for spending hours and hours crossing mails and test results with a support service.

If anyone can help with the previous data, it'll be welcomed but I won't spent more time testing and logging for this issue. I've lots of work and not many time.

Invisible Replications

Derek,
can you run sp_removedbreplication in the previously
published databases.
If this doesn't remove the rogue red x's in replication
monitor, try restarting the sql server service - when I
have investigated this before, there is a reference to a
temp table in tempdb, so restarting removed both the
table and the red icon.
Apparently sp_MSload_replication_status may clear this
error as well.
HTH,
Paul Ibison
(The ONLY sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
removedbreplication didn't work. It appears the "new" "Database A" has no
knowledge of the replications. I'm not sure where replication monitor is
getting it's information from, but that is what needs clearing out.
sp_MSload_replication_status also didn't seem to help.
I can get the database rebooted over the weekend, but is there anything else
I can try or any reading I can do to help investigate.
Thanks
Derek
"Paul Ibison" wrote:

> Derek,
> can you run sp_removedbreplication in the previously
> published databases.
> If this doesn't remove the rogue red x's in replication
> monitor, try restarting the sql server service - when I
> have investigated this before, there is a reference to a
> temp table in tempdb, so restarting removed both the
> table and the red icon.
> Apparently sp_MSload_replication_status may clear this
> error as well.
> HTH,
> Paul Ibison
> (The ONLY sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>

Invisible Replications

We have
Database A.
Database A Training
Database B
"Database A Training" was a copy of "Database A" 6 months ago.
Since then "Database A Training" has had replications set up to "Database B".
Last night a collegue decided that "Database A Training" needed updating and
so backed up "Database A" and then restored that backup over the top of
"Database A Training".
When I open the new "Database A Training" all the replications are gone.
This is not a problem as we were thinking of dispensing with most of them
anyway and the one I wanted is easy to recreate.
However when I now go to Replication Monitor all the old replications are
still showing and one of them is showing the big red X (OK, white X in red
circle) that shows something has failed.
When I right click - Delete I get the following message.
"SQL Server Enterprise Manager could not retrieve information about
publication 'XYZ'.
Error 21776: [SQL-DMO]The name 'XYZ' was not found in the TransPublications
collection. If the name is a qualified anem, use [] to separate various
parts of the name, and try again.
All I want to do is delete them and learn why this happened. I don't care
about recovering any information about them.
Your help is, as always, appreciated.
Derek
is this merge or transactional?
The best thing to do here is to restore the database again, this time using
the keep_replication switch. Then drop the publication using the GUI.
I'd also check the metadata tables to see if there is anything lingering
there.
You'll probably find some rows in syssubscriptions and sysmergesubscriptions
which you can delete.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Derek" <Derek@.discussions.microsoft.com> wrote in message
news:4FBE6B8B-8083-45DB-BD3F-6D9ACC1B8FE1@.microsoft.com...
> We have
> Database A.
> Database A Training
> Database B
> "Database A Training" was a copy of "Database A" 6 months ago.
> Since then "Database A Training" has had replications set up to "Database
B".
> Last night a collegue decided that "Database A Training" needed updating
and
> so backed up "Database A" and then restored that backup over the top of
> "Database A Training".
> When I open the new "Database A Training" all the replications are gone.
> This is not a problem as we were thinking of dispensing with most of them
> anyway and the one I wanted is easy to recreate.
> However when I now go to Replication Monitor all the old replications are
> still showing and one of them is showing the big red X (OK, white X in red
> circle) that shows something has failed.
> When I right click - Delete I get the following message.
> "SQL Server Enterprise Manager could not retrieve information about
> publication 'XYZ'.
> Error 21776: [SQL-DMO]The name 'XYZ' was not found in the
TransPublications
> collection. If the name is a qualified anem, use [] to separate various
> parts of the name, and try again.
> All I want to do is delete them and learn why this happened. I don't care
> about recovering any information about them.
> Your help is, as always, appreciated.
> Derek
|||Transactional. I'll let you know how this goes.
"Hilary Cotter" wrote:

> is this merge or transactional?
> The best thing to do here is to restore the database again, this time using
> the keep_replication switch. Then drop the publication using the GUI.
> I'd also check the metadata tables to see if there is anything lingering
> there.
> You'll probably find some rows in syssubscriptions and sysmergesubscriptions
> which you can delete.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "Derek" <Derek@.discussions.microsoft.com> wrote in message
> news:4FBE6B8B-8083-45DB-BD3F-6D9ACC1B8FE1@.microsoft.com...
> B".
> and
> TransPublications
>
>
|||syssubscriptions and sysmergesubscriptions didn't show anything.
However when I restored an old copy of the training database (that had the
replications), I could then delete them all.
Thanks for all your help.
Derek
"Derek" wrote:
[vbcol=seagreen]
> Transactional. I'll let you know how this goes.
> "Hilary Cotter" wrote:

Invisible Parameters

Hi,
How do i make parameters invisible.
Scenario:
I am doing the dynamic sorting. I put 2 textboxes next to the table header
asc/desc
and am doing osrting sending values by parameters and reloading the report
with sorted options.
But how do i make them invisible parameters ?i.e, the parameters shouldnt be
shown on the report.
When i try making the prompt blank - it gives me an error that the parameter
is only read only.
Please help
Thanks
RamDid you put a Default value before making clearing the prompt text?
--
Thanks
Ravi
"Ramani" wrote:
> Hi,
> How do i make parameters invisible.
> Scenario:
> I am doing the dynamic sorting. I put 2 textboxes next to the table header
> asc/desc
> and am doing osrting sending values by parameters and reloading the report
> with sorted options.
> But how do i make them invisible parameters ?i.e, the parameters shouldnt be
> shown on the report.
> When i try making the prompt blank - it gives me an error that the parameter
> is only read only.
> Please help
> Thanks
> Ram|||Yes, I did. I set the default values.
"Ravi" wrote:
> Did you put a Default value before making clearing the prompt text?
> --
> Thanks
> Ravi
>
> "Ramani" wrote:
> > Hi,
> > How do i make parameters invisible.
> > Scenario:
> > I am doing the dynamic sorting. I put 2 textboxes next to the table header
> > asc/desc
> > and am doing osrting sending values by parameters and reloading the report
> > with sorted options.
> > But how do i make them invisible parameters ?i.e, the parameters shouldnt be
> > shown on the report.
> > When i try making the prompt blank - it gives me an error that the parameter
> > is only read only.
> > Please help
> > Thanks
> > Ram|||I solved this problem by upgrading the server to at least the service pack 1
of reporting services. Then the "readonly" error no longer appears. It is to
my knowledge the only way to get rid of that ?&%$&* message.
To make sure you have the right version of Reporting Services, go to the
base URL for the report server (typically http://servername/reportserver/. At
the bottom of the browser page will be the version number:
Microsoft SQL Server Reporting Services Version 8.00.743.00 <- this is
old
Microsoft SQL Server Reporting Services Version 8.00.878.00 <- this is
SP1
Hope this helps.
"Ramani" wrote:
> Yes, I did. I set the default values.
> "Ravi" wrote:
> > Did you put a Default value before making clearing the prompt text?
> >
> > --
> > Thanks
> > Ravi
> >
> >
> > "Ramani" wrote:
> >
> > > Hi,
> > > How do i make parameters invisible.
> > > Scenario:
> > > I am doing the dynamic sorting. I put 2 textboxes next to the table header
> > > asc/desc
> > > and am doing osrting sending values by parameters and reloading the report
> > > with sorted options.
> > > But how do i make them invisible parameters ?i.e, the parameters shouldnt be
> > > shown on the report.
> > > When i try making the prompt blank - it gives me an error that the parameter
> > > is only read only.
> > > Please help
> > > Thanks
> > > Ram|||I have sp2 installed .. will that be fine ?
"Eric" wrote:
> I solved this problem by upgrading the server to at least the service pack 1
> of reporting services. Then the "readonly" error no longer appears. It is to
> my knowledge the only way to get rid of that ?&%$&* message.
> To make sure you have the right version of Reporting Services, go to the
> base URL for the report server (typically http://servername/reportserver/. At
> the bottom of the browser page will be the version number:
> Microsoft SQL Server Reporting Services Version 8.00.743.00 <- this is
> old
> Microsoft SQL Server Reporting Services Version 8.00.878.00 <- this is
> SP1
> Hope this helps.
> "Ramani" wrote:
> > Yes, I did. I set the default values.
> >
> > "Ravi" wrote:
> >
> > > Did you put a Default value before making clearing the prompt text?
> > >
> > > --
> > > Thanks
> > > Ravi
> > >
> > >
> > > "Ramani" wrote:
> > >
> > > > Hi,
> > > > How do i make parameters invisible.
> > > > Scenario:
> > > > I am doing the dynamic sorting. I put 2 textboxes next to the table header
> > > > asc/desc
> > > > and am doing osrting sending values by parameters and reloading the report
> > > > with sorted options.
> > > > But how do i make them invisible parameters ?i.e, the parameters shouldnt be
> > > > shown on the report.
> > > > When i try making the prompt blank - it gives me an error that the parameter
> > > > is only read only.
> > > > Please help
> > > > Thanks
> > > > Ramsql

Invisible controls durig runtime

Hi,
Do you know why when I run my ssis packages in the dev machine, the diagrams are not visible during run time.
I can design the package but not sure why when I start the package, the diagrams in the control flow can not be viewed.
Please note that if I do this on the server, I can see the diagrams during run time.

Thanks

The controls or the diagram indicating the control/data flows? You change nouns between the subject and the body of this post. ("controls" versus "diagrams")

If the latter, are you sure you don't just need to scroll to the left, up, down, or to the right to see them?|||

Hi,

I mean the controls such as flat file source, ...

Thanks

invisible components

I am not sure why when the packages are run, the components dissapear. The only thing I see is the result in the output.

So there is no visual on the tabs.

Thanks

Could you provide more information on this. How do they disappear? Are you sure they are not just scrolled away due to your window layout in the debug mode?

Thanks,

Bob

|||

Simply, the components (flat file source, ole db source, etc...) disaapear as the package is run. I can only see what is happening in the output window.

Thanks

|||

arkiboys wrote:

Simply, the components (flat file source, ole db source, etc...) disaapear as the package is run. I can only see what is happening in the output window.

Thanks

Are you sure they just aren't to the side and that you need to scroll to see them? Look at the scroll bars in the design window and see if they are way off to the side.|||

Yes, I am sure.
There are no scroll bars during the run time.

Thanks

|||

arkiboys wrote:

Yes, I am sure.
There are no scroll bars during the run time.

Thanks

Any way you can capture a screen shot and send it to me via my e-mail listed in my profile?|||

Just sent email,

Thanks

|||

The email address phil_dot_brammer@.gmail.com bounced back.

Are you sure it works please?

|||You'll have to remove "_dot_" and replace it with "."|||That looks like the debug "Call Stack" window... Heck, I guess it even says so in the window title.

What happens when you go to "View -> Designer"?|||

You are right.
I did not notice the call stack window title.

Many thanks

Invalid Object Name - Weird Error - Help!

I have a really strange problem. It seems a table has gone invisible. It's
listed in SysObjects, but does not show up in the tables list.
When I execte "Select * From Object_Access_Levels", it raises the error "Invalid
Object Name".
If I try to [Drop Table Object_Access_Levels], it returns the error:
"Cannot drop the table 'Object_Access_Levels' because it does not exist in the
system catalog."
If I follow the ID for the table to Syscolumns and SysIndexes, all the records
are there.
This is a complete show-stopper. I cannot continue development until this table
is restored.
Here's a copy of the record from sysobjects:
name,id,xtype,uid,info,status,base_schema_ver,replinfo,parent_obj,crdate,ftcatid,
schema_ver,stats_schema_ver,type,userstat,sysstat,indexdel,refdate,version,deltri
g,instrig,updtrig,seltrig,category,cache
"Object_Access_Levels",1858821684,"U ",1,17,8451,272,0,0,"07/12/2002
02:09pm",0,272,0,"U ",1,115,0,"07/12/2002 02:09pm",0,,,,0,2560,0
Does anyone have a clue how I can fix this problem?
I am using SQL2K with SP3a.
TIA,
-Steve-Who is the owner of the object? I always make sure to create my tables this
way:
CREATE TABLE dbo.Object_Access_Levels
And always reference them with the prefix also:
SELECT * FROM dbo.Object_Access_Levels
DROP TABLE dbo.Object_Access_Levels
You can see the owner name by:
SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'Object_Access_Levels'
If TABLE_SCHEMA is not dbo, you should drop and re-create the table as dbo,
and always refer to it with the dbo. prefix.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Steve Zimmelman" <sk_z@.psi_med.com> wrote in message
news:e50xOxeYEHA.808@.tk2msftngp13.phx.gbl...
>I have a really strange problem. It seems a table has gone invisible.
>It's
> listed in SysObjects, but does not show up in the tables list.
> When I execte "Select * From Object_Access_Levels", it raises the error
> "Invalid
> Object Name".
> If I try to [Drop Table Object_Access_Levels], it returns the error:
> "Cannot drop the table 'Object_Access_Levels' because it does not exist in
> the
> system catalog."
> If I follow the ID for the table to Syscolumns and SysIndexes, all the
> records
> are there.
> This is a complete show-stopper. I cannot continue development until this
> table
> is restored.
> Here's a copy of the record from sysobjects:
> name,id,xtype,uid,info,status,base_schema_ver,replinfo,parent_obj,crdate,ftcatid,
> schema_ver,stats_schema_ver,type,userstat,sysstat,indexdel,refdate,version,deltri
> g,instrig,updtrig,seltrig,category,cache
> "Object_Access_Levels",1858821684,"U ",1,17,8451,272,0,0,"07/12/2002
> 02:09pm",0,272,0,"U ",1,115,0,"07/12/2002 02:09pm",0,,,,0,2560,0
> Does anyone have a clue how I can fix this problem?
> I am using SQL2K with SP3a.
> TIA,
> -Steve-
>|||You may want to run DBCC CHECKCATALOG to check the system
tables.
Mark Baekdal
www.dbghost.com - the only true Database Change Manager
for SQL Server.
>--Original Message--
>I have a really strange problem. It seems a table has
gone invisible. It's
>listed in SysObjects, but does not show up in the tables
list.
>When I execte "Select * From Object_Access_Levels", it
raises the error "Invalid
>Object Name".
>If I try to [Drop Table Object_Access_Levels], it
returns the error:
> "Cannot drop the table 'Object_Access_Levels' because
it does not exist in the
>system catalog."
>If I follow the ID for the table to Syscolumns and
SysIndexes, all the records
>are there.
>This is a complete show-stopper. I cannot continue
development until this table
>is restored.
>Here's a copy of the record from sysobjects:
>name,id,xtype,uid,info,status,base_schema_ver,replinfo,pa
rent_obj,crdate,ftcatid,
>schema_ver,stats_schema_ver,type,userstat,sysstat,indexde
l,refdate,version,deltri
>g,instrig,updtrig,seltrig,category,cache
>"Object_Access_Levels",1858821684,"U ",1,17,8451,272,0,0,
"07/12/2002
>02:09pm",0,272,0,"U ",1,115,0,"07/12/2002
02:09pm",0,,,,0,2560,0
>Does anyone have a clue how I can fix this problem?
>I am using SQL2K with SP3a.
>TIA,
>-Steve-
>
>.
>|||Thanks Aaron,
The table has been in use for over a year. Everything was fine last week during
development. Then yesterday, I tried to run the app and got the error during the
load.
This SQL command returns an empty result.
[SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'Object_Access_Levels']
But as I stated, the table is listed in the SyObjects, SysIndexes, and
SysColumns.
Running: DBCC CHECKCATALOG ('MedMas') WITH NO_INFOMSGS
Returns this error:
[Table Corrupt: Object ID 1840725610 (object '1840725610') does not match between
'SYSCOLUMNS' and 'SYSOBJECTS']
It looks like SQL's internal table got hosed. Is there any way to fix this
without completely rebuilding the database?
-Steve-
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%239t8NTfYEHA.4004@.TK2MSFTNGP10.phx.gbl...
Who is the owner of the object? I always make sure to create my tables this
way:
CREATE TABLE dbo.Object_Access_Levels
And always reference them with the prefix also:
SELECT * FROM dbo.Object_Access_Levels
DROP TABLE dbo.Object_Access_Levels
You can see the owner name by:
SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'Object_Access_Levels'
If TABLE_SCHEMA is not dbo, you should drop and re-create the table as dbo,
and always refer to it with the dbo. prefix.
--
http://www.aspfaq.com/
(Reverse address to reply.)|||Hi Mark,
Running: DBCC CHECKCATALOG ('MedMas') WITH NO_INFOMSGS
Returns this error:
[Table Corrupt: Object ID 1840725610 (object '1840725610') does not match between
'SYSCOLUMNS' and 'SYSOBJECTS']
Is this fixable without rebuilding the database?
Thanks,
-Steve-
"mark baekdal" <anonymous@.discussions.microsoft.com> wrote in message
news:26ebb01c46255$b2dbd4b0$a301280a@.phx.gbl...
You may want to run DBCC CHECKCATALOG to check the system
tables.
Mark Baekdal
www.dbghost.com - the only true Database Change Manager
for SQL Server.|||BTW, I just checked the ID 1840725610, it doesn't belong the Object_Access_Levels
table. It belongs to a couple of params. I think this DB is really hosed!
-Steve-
"Steve Zimmelman" <sk_z@.psi_med.com> wrote in message
news:%23JR2N$oYEHA.1764@.TK2MSFTNGP10.phx.gbl...
Hi Mark,
Running: DBCC CHECKCATALOG ('MedMas') WITH NO_INFOMSGS
Returns this error:
[Table Corrupt: Object ID 1840725610 (object '1840725610') does not match between
'SYSCOLUMNS' and 'SYSOBJECTS']
Is this fixable without rebuilding the database?
Thanks,
-Steve-
"mark baekdal" <anonymous@.discussions.microsoft.com> wrote in message
news:26ebb01c46255$b2dbd4b0$a301280a@.phx.gbl...
You may want to run DBCC CHECKCATALOG to check the system
tables.
Mark Baekdal
www.dbghost.com - the only true Database Change Manager
for SQL Server.|||Steve,
Any idea what error message(s) you were getting? If
error 2513, check SQL Books Online for steps to possibly
resolve the inconsistency. Please ensure you have a
backup or copy of the .mdf & .ldf before you do anything.
HTH
Darren Fuller
>--Original Message--
>Hi Mark,
>Running: DBCC CHECKCATALOG ('MedMas') WITH NO_INFOMSGS
>Returns this error:
>[Table Corrupt: Object ID 1840725610
(object '1840725610') does not match between
>'SYSCOLUMNS' and 'SYSOBJECTS']
>Is this fixable without rebuilding the database?
>Thanks,
>-Steve-
>"mark baekdal" <anonymous@.discussions.microsoft.com>
wrote in message
>news:26ebb01c46255$b2dbd4b0$a301280a@.phx.gbl...
>You may want to run DBCC CHECKCATALOG to check the system
>tables.
>Mark Baekdal
>www.dbghost.com - the only true Database Change Manager
>for SQL Server.
>
>.
>|||After much wasted time, I restored an old copy of the db. I was under the
impression (apparently a false one) that SQL server didn't have these types
corruption problems.
Thanks everyone for your suggestions.
-Steve-|||Your impression is not false, we don't have these types of corruption
problems.
However, hardware can and does introduce all manner of corruptions that
manifest themselves in various ways. I would check your NT event logs and
SQL errorlog for IO susbsystem errors. Before restoring from your backup, I
would have recommended running DBCC CHECKDB to check for other corruptions.
In future, to avoid wasting time, you should call Product Support who will
be able to help you pinpoint the problem very quickly.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Steve Zimmelman" <sk_z@.psi_med.com> wrote in message
news:ermVPB6YEHA.1048@.tk2msftngp13.phx.gbl...
> After much wasted time, I restored an old copy of the db. I was under the
> impression (apparently a false one) that SQL server didn't have these
types
> corruption problems.
> Thanks everyone for your suggestions.
> -Steve-
>

Invalid Object Name - Weird Error - Help!

I have a really strange problem. It seems a table has gone invisible. It's
listed in SysObjects, but does not show up in the tables list.
When I execte "Select * From Object_Access_Levels", it raises the error "Inv
alid
Object Name".
If I try to [Drop Table Object_Access_Levels], it returns the error:
"Cannot drop the table 'Object_Access_Levels' because it does not exist in t
he
system catalog."
If I follow the ID for the table to Syscolumns and SysIndexes, all the recor
ds
are there.
This is a complete show-stopper. I cannot continue development until this t
able
is restored.
Here's a copy of the record from sysobjects:
name,id,xtype,uid,info,status,base_schem
a_ver,replinfo,parent_obj,crdate,ftc
atid,
schema_ver,stats_schema_ver,type,usersta
t,sysstat,indexdel,refdate,version,d
eltri
g,instrig,updtrig,seltrig,category,cache
"Object_Access_Levels",1858821684,"U ",1,17,8451,272,0,0,"07/12/2002
02:09pm",0,272,0,"U ",1,115,0,"07/12/2002 02:09pm",0,,,,0,2560,0
Does anyone have a clue how I can fix this problem?
I am using SQL2K with SP3a.
TIA,
-Steve-Who is the owner of the object? I always make sure to create my tables this
way:
CREATE TABLE dbo.Object_Access_Levels
And always reference them with the prefix also:
SELECT * FROM dbo.Object_Access_Levels
DROP TABLE dbo.Object_Access_Levels
You can see the owner name by:
SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'Object_Access_Levels'
If TABLE_SCHEMA is not dbo, you should drop and re-create the table as dbo,
and always refer to it with the dbo. prefix.
http://www.aspfaq.com/
(Reverse address to reply.)
"Steve Zimmelman" <sk_z@.psi_med.com> wrote in message
news:e50xOxeYEHA.808@.tk2msftngp13.phx.gbl...
>I have a really strange problem. It seems a table has gone invisible.
>It's
> listed in SysObjects, but does not show up in the tables list.
> When I execte "Select * From Object_Access_Levels", it raises the error
> "Invalid
> Object Name".
> If I try to [Drop Table Object_Access_Levels], it returns the error:
> "Cannot drop the table 'Object_Access_Levels' because it does not exist in
> the
> system catalog."
> If I follow the ID for the table to Syscolumns and SysIndexes, all the
> records
> are there.
> This is a complete show-stopper. I cannot continue development until this
> table
> is restored.
> Here's a copy of the record from sysobjects:
> name,id,xtype,uid,info,status,base_schem
a_ver,replinfo,parent_obj,crdate,f
tcatid,
> schema_ver,stats_schema_ver,type,usersta
t,sysstat,indexdel,refdate,version
,deltri
> g,instrig,updtrig,seltrig,category,cache
> "Object_Access_Levels",1858821684,"U ",1,17,8451,272,0,0,"07/12/2002
> 02:09pm",0,272,0,"U ",1,115,0,"07/12/2002 02:09pm",0,,,,0,2560,0
> Does anyone have a clue how I can fix this problem?
> I am using SQL2K with SP3a.
> TIA,
> -Steve-
>|||Thanks Aaron,
The table has been in use for over a year. Everything was fine last week du
ring
development. Then yesterday, I tried to run the app and got the error durin
g the
load.
This SQL command returns an empty result.
[SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'Object_Access_Levels']
But as I stated, the table is listed in the SyObjects, SysIndexes, and
SysColumns.
Running: DBCC CHECKCATALOG ('MedMas') WITH NO_INFOMSGS
Returns this error:
[Table Corrupt: Object ID 1840725610 (object '1840725610') does not matc
h between
'SYSCOLUMNS' and 'SYSOBJECTS']
It looks like SQL's internal table got hosed. Is there any way to fix this
without completely rebuilding the database?
-Steve-
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%239t8NTfYEHA.4004@.TK2MSFTNGP10.phx.gbl...
Who is the owner of the object? I always make sure to create my tables this
way:
CREATE TABLE dbo.Object_Access_Levels
And always reference them with the prefix also:
SELECT * FROM dbo.Object_Access_Levels
DROP TABLE dbo.Object_Access_Levels
You can see the owner name by:
SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'Object_Access_Levels'
If TABLE_SCHEMA is not dbo, you should drop and re-create the table as dbo,
and always refer to it with the dbo. prefix.
http://www.aspfaq.com/
(Reverse address to reply.)sql

Invalid Object Name - Weird Error - Help!

I have a really strange problem. It seems a table has gone invisible. It's
listed in SysObjects, but does not show up in the tables list.
When I execte "Select * From Object_Access_Levels", it raises the error "Invalid
Object Name".
If I try to [Drop Table Object_Access_Levels], it returns the error:
"Cannot drop the table 'Object_Access_Levels' because it does not exist in the
system catalog."
If I follow the ID for the table to Syscolumns and SysIndexes, all the records
are there.
This is a complete show-stopper. I cannot continue development until this table
is restored.
Here's a copy of the record from sysobjects:
name,id,xtype,uid,info,status,base_schema_ver,repl info,parent_obj,crdate,ftcatid,
schema_ver,stats_schema_ver,type,userstat,sysstat, indexdel,refdate,version,deltri
g,instrig,updtrig,seltrig,category,cache
"Object_Access_Levels",1858821684,"U ",1,17,8451,272,0,0,"07/12/2002
02:09pm",0,272,0,"U ",1,115,0,"07/12/2002 02:09pm",0,,,,0,2560,0
Does anyone have a clue how I can fix this problem?
I am using SQL2K with SP3a.
TIA,
-Steve-
Who is the owner of the object? I always make sure to create my tables this
way:
CREATE TABLE dbo.Object_Access_Levels
And always reference them with the prefix also:
SELECT * FROM dbo.Object_Access_Levels
DROP TABLE dbo.Object_Access_Levels
You can see the owner name by:
SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'Object_Access_Levels'
If TABLE_SCHEMA is not dbo, you should drop and re-create the table as dbo,
and always refer to it with the dbo. prefix.
http://www.aspfaq.com/
(Reverse address to reply.)
"Steve Zimmelman" <sk_z@.psi_med.com> wrote in message
news:e50xOxeYEHA.808@.tk2msftngp13.phx.gbl...
>I have a really strange problem. It seems a table has gone invisible.
>It's
> listed in SysObjects, but does not show up in the tables list.
> When I execte "Select * From Object_Access_Levels", it raises the error
> "Invalid
> Object Name".
> If I try to [Drop Table Object_Access_Levels], it returns the error:
> "Cannot drop the table 'Object_Access_Levels' because it does not exist in
> the
> system catalog."
> If I follow the ID for the table to Syscolumns and SysIndexes, all the
> records
> are there.
> This is a complete show-stopper. I cannot continue development until this
> table
> is restored.
> Here's a copy of the record from sysobjects:
> name,id,xtype,uid,info,status,base_schema_ver,repl info,parent_obj,crdate,ftcatid,
> schema_ver,stats_schema_ver,type,userstat,sysstat, indexdel,refdate,version,deltri
> g,instrig,updtrig,seltrig,category,cache
> "Object_Access_Levels",1858821684,"U ",1,17,8451,272,0,0,"07/12/2002
> 02:09pm",0,272,0,"U ",1,115,0,"07/12/2002 02:09pm",0,,,,0,2560,0
> Does anyone have a clue how I can fix this problem?
> I am using SQL2K with SP3a.
> TIA,
> -Steve-
>
|||Thanks Aaron,
The table has been in use for over a year. Everything was fine last week during
development. Then yesterday, I tried to run the app and got the error during the
load.
This SQL command returns an empty result.
[SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'Object_Access_Levels']
But as I stated, the table is listed in the SyObjects, SysIndexes, and
SysColumns.
Running: DBCC CHECKCATALOG ('MedMas') WITH NO_INFOMSGS
Returns this error:
[Table Corrupt: Object ID 1840725610 (object '1840725610') does not match between
'SYSCOLUMNS' and 'SYSOBJECTS']
It looks like SQL's internal table got hosed. Is there any way to fix this
without completely rebuilding the database?
-Steve-
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%239t8NTfYEHA.4004@.TK2MSFTNGP10.phx.gbl...
Who is the owner of the object? I always make sure to create my tables this
way:
CREATE TABLE dbo.Object_Access_Levels
And always reference them with the prefix also:
SELECT * FROM dbo.Object_Access_Levels
DROP TABLE dbo.Object_Access_Levels
You can see the owner name by:
SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'Object_Access_Levels'
If TABLE_SCHEMA is not dbo, you should drop and re-create the table as dbo,
and always refer to it with the dbo. prefix.
http://www.aspfaq.com/
(Reverse address to reply.)