>The joy of it all! After experiencing the inability to create or modify a Stored Procedure, I called our Admin. She told me that with SP4, you loose the ability to create or modify Stored Procedures when using a
linked server. This is for security reasons to prevent virus infection. Only the Admin can create or modify Stored Procedures.
>
>The error I receive is in this KB reference.
>
>
http://support.microsoft.com/kb/296769/en-us>
>I can assure you that I always comply with the suggested “fix”. Even Creating Stored Procedures outside of the Enterprise Manager (SQL Query Analyzer) results in the same problem.
>
>Now how do I do my job? This is a serious issue and I wonder if anyone has any insight into how it can be resolved - in my (the deverloper) favor!
>
>We have security - now what?
>
>We will be going to SQL Server 2005 in the future. That should be interesting. :)
Perhaps I have the solution. This worked. Now to find out what other things will go wrong! :)
SET ANSI_NULLS ON
GO
SET ANSI_WARNINGS ON
GO
CREATE PROCEDURE [dbo].[MySP]
I always used SET ANSI_WARNINGS OFF, which should allow your code to run faster. With SP4, it creates the above problem.
I still cannot modify any stored procedure I wrote before SP4 was installed. I can worry about that later.