I have 2 separate machines: one runs SQL 2000 and the other SQL 7.

At one time I was able to make changes in my stored procedures on the SQL 7 PC from my desktop, which is not the SQL 7 machine.

We installed the new SQL 2000 machine. At this point I needed to access both machines. So I installed the new enterprise manager for SQL 2000 and I can access both machines and the data on both machines.

My problem is that I am unable to add lines of code to stored procecures on the SQL 7 machine. I can add a new stored procedure but I am not allowed to change any lines of code in that procedure after it is saved the first time. I can delete lines and procedures just not add code.

Is this some sort of permission problem? If it is where do I go to correct it?

-- Anonymous, March 11, 2002

Answers

Linda,

Yes, a permissions problem is a good guess.

For a more detailed answer, please post a more detailed problem description including error numbers and the text of error messages as well as details of the account that SQL Server services are running under.

Eric

-- Anonymous, March 12, 2002

Eric, Sorry it took so long to answer, I had to find out some information before I replied

The situation is as follows: 1. I am logged in as sa on both servers and I should have update permission on the procedures 2. I can create a procedure on both machines, but I cannot update one on either machine. 3. There are no errors or error numbers. When I key in the corrections the syntax checks correctly and the change is accepted but when I open the procedure a 2nd time the corrections have not been added to the procedure. This is a problem on both SQL 7 Server and the SQL2000 Server 3. Recently I updated my desktop to Win 2000 and thought maybe that was a factor, so I reinstalled the SQL Client on my desktop. That did not fix my problem. Any suggestions???

-- Anonymous, March 22, 2002

Linda,

It could be that dbo is not the owner of the stored procedures. You also might try ISQL/W rather than Enterprise Manager to modify the stored procedures. Then use sp_helptext to verify changes.

Good Luck,

Eric

-- Anonymous, March 22, 2002

Eric, I created the stored procedure and the procedure is a user procedure owned by dbo.

I can go to another PC and use my system logons and am able to make changes in any procedure on the SQL 7 Server. But these installations have not had the Enterprise manager that came with SQL 2000 installed on the PC. So it seems to be my copy of the Enterprise Manager that is giving me the problem. Is that possible and have you run across any instances of this happening before?

Thanks

-- Anonymous, March 25, 2002

Linda,

I could not find mention anywhere of this particular problem. I can only suggest that you apply the latest SQL Server service packs to the client machines and servers.

Good Luck,

Eric

-- Anonymous, March 26, 2002

Eric, I fixed my problem. I am still not sure what was wrong. But I uninstalled both SQL 2000 and SQL 7 client from my desktop. When I did this I noticed that I still had a start icon for the enterprise manager on the programs menu and I clicked it. The SQL 2000 Enterprise Manager opened. I then tested it and it works correctly. So as I said before What happened?

Thanks Linda

-- Anonymous, March 26, 2002