Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322

    Unanswered: INFORMATION SCHEMA And LInked Server

    I know I've done this before and I can't find, and it's kinda of foggy here today...

    anyone see anything with this

    Code:
    EXEC sp_addlinkedserver 
        'PAERSCBVD0045',
        N'SQL Server'
    
    Connect as X002548 since logins are on both serves
    
    */
    
    -- Tables in 14 not in 45
    
        SELECT S14.TABLE_NAME 
          FROM INFORMATION_SCHEMA.Tables S14
     LEFT JOIN INFORMATION_SCHEMA.PAERSCBVD0045.dbo.MEP.Tables S45
    	ON S14.TABLE_SCHEMA = S45.TABLE_SCHEMA
           AND S14.TABLE_NAME   = S45.TABLE_NAME
         WHERE S45.TABLE_SCHEMA = IS NULL
           AND S45.TABLE_NAME   = IS NULL
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    OK, syntax is

    Code:
        SELECT *
          FROM PAERSCBVD0045.MEP.INFORMATION_SCHEMA.Tables S45
    But now I get

    Server: Msg 18452, Level 14, State 1, Line 1
    Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
    And I know the put me in as windows login as sa

    I tried

    Code:
    EXEC sp_addlinkedsrvlogin 'X002548'
    But got

    Server: Msg 823, Level 24, State 2, Procedure sp_addlinkedsrvlogin, Line 1
    I/O error (torn page) detected during read at offset 0x00000000786000 in file 'C:\Program Files\Microsoft SQL Server\MSSQL\data\master.mdf'.

    Connection Broken
    Which doesn't look good, so I did a checkdb on the linked server and got no errors

    Maybe masters got a problem on mine

    Wonder if it's a collation thing
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Ah, OK, Not sure if this is the problem, but master on my box has consistencey errors

    Server: Msg 8928, Level 16, State 1, Line 1
    Object ID 6, index ID 0: Page (1:963) could not be processed. See other errors for details.
    Server: Msg 8939, Level 16, State 1, Line 1
    Table error: Object ID 6, index ID 0, page (1:963). Test (IS_ON (BUF_IOERR, bp->bstat) && bp->berrcode) failed. Values are 2057 and -1.
    DBCC results for 'master'.
    DBCC results for 'sysobjects'.
    There are 1269 rows in 23 pages for object 'sysobjects'.
    DBCC results for 'sysindexes'.
    There are 104 rows in 4 pages for object 'sysindexes'.
    DBCC results for 'syscolumns'.
    There are 4851 rows in 84 pages for object 'syscolumns'.
    DBCC results for 'systypes'.
    There are 26 rows in 1 pages for object 'systypes'.
    DBCC results for 'syscomments'.
    There are 2011 rows in 945 pages for object 'syscomments'.
    CHECKDB found 0 allocation errors and 2 consistency errors in table 'syscomments' (object ID 6).
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    OK, more succienct messages

    Server: Msg 8928, Level 16, State 1, Line 1
    Object ID 6, index ID 0: Page (1:963) could not be processed. See other errors for details.
    Server: Msg 8939, Level 16, State 1, Line 1
    Table error: Object ID 6, index ID 0, page (1:963). Test (IS_ON (BUF_IOERR, bp->bstat) && bp->berrcode) failed. Values are 2057 and -1.
    CHECKDB found 0 allocation errors and 2 consistency errors in table 'syscomments' (object ID 6).
    CHECKDB found 0 allocation errors and 2 consistency errors in database 'master'.
    repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (master ).
    It appears to be in syscomments. Great lo these many years and finally a system problem. Can I fix a system table?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Sure you can. With a master rebuild, of course. And you do have a backup around, don't you? ;-)

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Of course. I found the earliest and made a copy and saved it locally

    But I don't think that's really causing my linked server problem, but this is the first time I've ever seen this

    And these are more like warning no?

    Maybe I should bounce the box and see what happens
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •