December 11, 2007 at 10:18 am
I am upgrading some databases to sql 2005.
My goal is to drop every stored procedure and recreate them, to make sure that they will not fail when changing the compatibility level.
I created a table, and inserted the result of
sp_helptext [procedurename] onto it
then i copied the result into management studio.
The problem:
the text gets inserted into my temp table in rows, splitting up words, so I have to manually fix the procedure, and put words toguether before I can execute the statements.
Is there a way to make this task a little more simple? so that once i have the text of the procedure I can just run it?
Here is my query:
create table #temp (ProcText varchar (max))
DECLARE @ProcName varchar(128)
DECLARE @PROCTEXT VARCHAR(MAX)
SET NOCOUNT ON
DECLARE tnames_cursor CURSOR FOR
SELECT name FROM sys.objects
WHERE type = 'P'
ORDER BY name
OPEN tnames_cursor
FETCH NEXT FROM tnames_cursor INTO @ProcName
WHILE (@@fetch_status = 0)
BEGIN
insert into #temp
exec SP_HELPTEXT @ProcName
insert into #temp values ('go')
exec ('drop proc ' + @ProcName)
FETCH NEXT FROM tnames_cursor INTO @ProcName
END
CLOSE tnames_cursor
DEALLOCATE tnames_cursor
GO
select * from #TEMP
December 11, 2007 at 12:16 pm
You could just use management studio to generate the scripts for you...right click on the database in object explorer, and go to Tasks->Generate Scripts. The wizard will ask you what type(s) and which specific objects you want to script.
December 11, 2007 at 12:18 pm
Sorry, forgot something. In order to generate the DROP part of it, there is a "Script drop" option that you will want to set to true on the "Choose script options" page.
December 11, 2007 at 1:02 pm
How about triggers. Looks like I would have to script the tablein order todo them as well?
Is there a way to just do the triggers?
December 11, 2007 at 1:25 pm
Ohh triggers complicate things 🙂
Unfortunately, there is not a way to do just triggers using that wizard. I have no idea why they put that limitation into the tool.
If you are doing this after the database is on SQL Server 2005, you can query the sys.sql_modules catalog view, which stores the T-SQL in the definition column, which is nvarchar(max) instead of nvarchar(4000) like it was in the syscomments table in SQL 2000, so the whole definition should be in that one row. The problem you will have with that method is that Management Studio does not return new lines properly in a grid, and the default options for the results to text truncates it. I am trying to find out where/if you can change that. I'll let you know if I figure that out.
December 11, 2007 at 1:31 pm
Ahh, easy I was just looking in the wrong options window. Go to Tools->Options and then Query Results->SQL Server->Results to Text and set the "Maximum number of characters displayed in each column" option to something larger than the default. It seems to have a max of 8192 though...so hopefully you don't have really massive triggers so this actually helps....
December 11, 2007 at 2:24 pm
thank you so much!!
December 11, 2007 at 7:28 pm
Ack... don't drop them... then you have to worry about all sorts of things including grants, etc, etc.
Generate ALTER statements for them all.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 11, 2007 at 7:36 pm
True, but the generate script utility gives you an option to script object permissions as well so that is not a big deal. Regardless, you can use that script as the starting point and replace the CREATE statements with ALTER statements...just make sure you only replace CREATE PROCEDURE statements, and not any other CREATEs that may be in your procedure code.
December 11, 2007 at 7:38 pm
Dropping the procs will be a very big deal if there are any dependencies... the script generator isn't real good a recognizing those 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
December 11, 2007 at 9:58 pm
Honestly, the Upgrade Advisor should check some of this stuff. I agree with Jeff, I wouldn't drop them. Could cause lots of issues.
I'd develop a plan to execute them and see if they all work in 2005. Are we talking about so many procedures?
December 12, 2007 at 8:26 am
wouldn't it just be easier to attach a backup copy of the DB in a new 2005 instance and try to see if or how they work? Most of the challenges I saw were in HOW the stored procs worked after you upgraded the DB, not in whether it would get past the compiler. And again - most of it was in performance (speed, efficiency, blocking, etc..) differences, and not so much in actual changes in behavior outright.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
December 12, 2007 at 12:27 pm
Thank you so much for your advice! I have several databases, some of them with over 300 procedures, so runing them all will be difficult. I will look at the upgrade advisor.
December 13, 2007 at 4:40 am
I am attaching a zip file with a proc that will script out all your tables, including the triggers on the tables. If you just want triggers you can easily change it as required.
The other 2 procs will script out all your stored procs and all your functions.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
December 13, 2007 at 11:18 am
Situations like this are where unit testing is a great thing. I'd suggest creating a test suite for your database code going forward...probably not feasible to do it for your existing code, but as you make modifications you can add them piece by piece. If you can get your hands on some automation tools, even better.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply