Sql-server – SQL Server 2000 Replication, Performance, and Query Priorities

The fastest, simplest solution is to restore your last good backup. If that isn't an option you can try a workaround to make MSSQL think the database already exists:

http://sqlskills.com/BLOGS/PAUL/post/Disaster-recovery-101-hack-attach-a-damaged-database.aspx

Then you can run DBCC and see if it can repair the database, but even in that case you may still have data loss. You could also call Microsoft (maybe you already have a support contract), but the bottom line is that if you have a damaged database and no backup then there's not much that anyone can do.

What version of SQL Server is the PI2 server in this situation? When was the last time the underlying statistics were updated on these tables on the Linked Server? While you updated statistics, it sounds like that was only done on the local database server that you migrated to. Updating statistics on these underlying remote tables may help the optimizer identify a more optimal plan and allow for the entire query to execute remotely.

Another possibility is permissions (though you say that's no different between the old and new servers), four-part (aka distributed) queries require proper permissions to remote tables in order to get an optimal plan. From the Guidelines to Using Distributed Queries page:

To create the best query plans when you are using a table on a linked server, the query processor must have data distribution statistics from the linked server. Users that have limited permissions on any columns of the table might not have sufficient permissions to obtain all the useful statistics, and might receive a less efficient query plan and experience poor performance. If the linked server is an instance of SQL Server, to obtain all available statistics, the user must own the table or be a member of the sysadmin fixed server role, the db_owner fixed database role, or the db_ddladmin fixed database role on the linked server.

Really since all of these tables are on the linked server, I suggest you force the query to execute remotely in it's entirety instead of hoping it will do so with four-part/distributed query syntax. You do this by reworking the query into an EXECUTE AT statement, such as follows:

EXECUTE('select  
        MKPF.MANDT,
        MKPF.MBLNR,
        MKPF.MJAHR,
        MSEG.BWART,
        MSEG.MATNR,
        MSEG.LGORT,
        MKPF.BUDAT,
        MKPF.CPUDT,
        MKPF.CPUTM,
        MKPF.AEDAT,
        MSEG.SHKZG
    from PKT.pkt.MSEG AS MSEG 
    join PKT.pkt.MKPF AS MKPF 
        on MKPF.mandt=MSEG.mandt and MKPF.MJAHR = MSEG.MJAHR and MKPF.MBLNR = MSEG.MBLNR
    join PKT.pkt.MARA MARA
        on MARA.MANDT=MSEG.MANDT and MARA.MATNR = MSEG.MATNR
        and MARA.spart =''01''
    WHERE 
        MSEG.Mandt=''500'' and MSEG.MATNR like ''00000000%'' and 
        MKPF.CPUDT between ? and ?
        AND MSEG.BWART in (''101'',''561'')'
, @datefrom, @dateto) AT PI2

I'm suggesting the EXECUTE AT approach instead of using other LinkedServer syntax such as OPENQUERY or OPENROWSET as this will allow you to still generate the date parameters from data hosted on your local server (as your estimated query plan indicates) and pass them as parameters to the query you wish to run remotely in what will likely be a more optimal fashion.