Jim Lawrence
accessd at shaw.ca
Tue Oct 3 15:32:40 CDT 2006
Hi Paul: Thanks for the comments. The problem was occurring in my SQL 2000 and SQL 2005 versions and I was unaware of why I was getting errors on one system and was being ignored on the other system. It turns out that after a recent patch on the old system (SQL 2000) and a subsequent import/upgrade attempt (SQL 2005) things started acting weird. First only a couple of the SPs imported and when trying to add the missed SPs they refused to save completely and/or would respond with the usual innocuous MS error. I had run the checker before attempting a save and no errors.... It turned out to be a legacy coding meets the new security. (Like a shampoo salesman meets airport security.) On older SPs between each process group, within the SP a 'GO' was added. This had always worked right before. The new syntax does not need a 'GO' statement and a 'GO' statement is only required or allowed as the last statement in a SP. In SQL 2000 the app would just chop the SP at the 'GO' statement and the SQL 2005 would simply ignore what it assumes as an invalid SP. The reason for this upgrade was to stop the threats from injection-attack text compromising an otherwise valid SP. The thing that most annoyed me was that all the SPs validated perfectly and the systems either ignored the SP and did not allow it to save or responded with some unintelligible error message before scrambling the SP. This is another MS half baked fix. Thanks again for your note at least it set me on the right path and some sleep last night. Jim -----Original Message----- From: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of paul.hartland at fsmail.net Sent: Tuesday, October 03, 2006 6:37 AM To: dba-sqlserver at databaseadvisors.com Subject: Re: [dba-SQLServer] Saving stored procedures Importance: High Jim I had the same problem when I first dabbled with this, you actually don't have to save it as far as I can remember. If you create the stored procedure, click the execute button, think this tests if all is ok...then if you refresh your stored procedure listing it should appear. Paul Message Received: Oct 03 2006, 01:37 PM From: "Jim Lawrence" To: dba-sqlserver at databaseadvisors.com Cc: Subject: [dba-SQLServer] Saving stored procedures Hi All: This undoubtedly will appear to be a silly problem but I do not seem to be able to get past it. It may be the late night or something. I am working in SQL 2005, in SQL Server Management Studio. I have just created a new Stored Procedure, tested it and now want to save it. It can be stored to any directory but does not save the results to the Stored Procedure list. There is something very simple that I am missing but at this late hour have no idea and the help files do not. All that is needed is for the new SP is for it to appear/stored in the list. TIA Regards Jim _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com Paul Hartland paul.hartland at fsmail.net 07730 523179 _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com