SQL 2005 linked server to SQL 2000, need to run instcat.sql

[Mike]

I need to update the catalog on my SQL 2000 box so run distributed queries
from a 64-bit SQL Server 2005 server to a linked 32-bit SQL Server 2000
server and I know I need to run the instcat.sql per KB # 906954. My question
is how will this affect my SQL 2000 production database when I do this? I
have replication setup on this box as well as it being in an active/passive
cluster. I know how touchy SQL 2000 is about replication and can't afford to
break it.

[Denny Cherry]

It shouldn't give you any problems. I've run instcat.sql on serveral
servers SQL 2000 machines without issue.

Denny

[Mike]

Thanks for the reply Denny. Have you ran the script on a cluster with ot
wothout replication setup?

Mike

[Denny Cherry]

I had run it without replication setup. If I remember correctly
instcat doesn't do anything to replication. Read through the script
to check the objects that it modifies / creates.

Denny

[Geoff N. Hiten]

Unless you have a very strange naming convention, instcat.sql should not
cause any problems. I have run it on clustered systems running replication
without any problems.

--
Geoff N. Hiten
Principal SQL Infrastructure Consultant
Microsoft SQL Server MVP

"Mike" <ms...@nospam.nospam> wrote in message
news:005140F5-7BFF-4ED8...@microsoft.com...

[Charles Wang [MSFT]]

Hi,
Regarding this issue, I have replied you under your other thread at public.sqlserver.odbc community.
Please post back under that thread for further communications.

For your reading convenience, I would like to include my response below:
===========================================================
(It seems that you could not see my email response, so I directly post the response here.)

From your description, I understand that you need to update the catalog on your 32-bit SQL Server 2000
box and you need to run distributed queries from your 64-bit SQL Server 2005 to the SQL Server 2000
instance. According to the KB 906954, you need to run instcat.sql to avoid the known error. Your question
is:
How will the method affect your SQL Server 2000 production database?

By now, there is no known issue for carrying out this method. However since the instcat.sql will update
some system stored procedures, it may impact some of your running functions that are using these SPs
(SQL Server replication will use some of the system procedures). Generally every time when you do such
operations, we recommend that you perform a test before you put it into production environment.
However since this issue was because that the system stored procedures were not updated
automatically during your SQL Server 2000 SP3 or SP4 installation in your production environment, it may
be hard to reproduce this issue in your test environment, so my suggestion here is that for not causing
other problems, it is better to stop your replication and other database operations when you take this
action. This process should finish within a short time and then your replication and other applications
that may use the system stored procedures can be resumed. If you really cannot arrange a break time for
doing this, please arrange a not busy time for your database server to run the script.
================================================================

If you have any other questions or concerns, please feel free to let me know.

Best regards,
Charles Wang
Microsoft Online Community Support
===========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msd...@microsoft.com.
===========================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notifications.

Note: The MSDN Managed Newsgroup support offering is for
non-urgent issues where an initial response from the community
or a Microsoft Support Engineer within 1 business day is acceptable.
Please note that each follow up response may take approximately
2 business days as the support professional working with you may
need further investigation to reach the most efficient resolution.
The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by
contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
============================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================