Hi there!!
Can we force stored procs in a database to recompile and see if there is any compile time error in the stored procs then and there.
Something like sp_recompile which marks Stored Proc for recompilation and compilation occurs later on.
I am looking for something which will force the existing stored procs to compile and let me see the error if any.
Best Regards
Rahul Kumar, Software Engineer, India
SP_RECOMPILE is helpful to recompile the next time they are run.
It will remove all the chache Plan for the current object when you run the SP it will compile it.
You can do the following step to get the compile errors...
Exec Sp_recompile 'MySp'
Exec @.R = MySp
If @.R = 0
Print 'Success'
Else
Print 'Failed on Compile'
But the drawback here is you can't find the error is occured by compiler or runtime execution.
|||
This can be done for one stored proc, but we cant have this approach if we want to recompile all the strored procs in the database- as they may be having different parameters.
|||You can try calling DBCC FREEPROCCACHE
This will flush the procedure cache and the cached plans. Next time you execute the procedures, they will compile and you should be able to hit the compilation error.
|||It all depends on what you mean by "recompile". I know that I was really keen that the meant what it sounded like, which would be to take the source code, build an executable module, then a plan, and get things ready.
When you run sp_recompile, all it does is bump an internal value that is used to tell objects to recompile because something has changed. It will not take existing source code and rebuild the executable.
What is the purpose you are trying to achieve? Find where structures have changed? This is something you need to do with source control. Either by having documentation you can search (using a tool, or just manually searching) or just rebuild your project from scratch. Even recompiling is not foolproof unfortunately because of delayed name resolution, meaning that, in a procedure, if it comes to a table name that does not exist, it assumes that you know what you are doing and that the object will be created later. Man, I hate delayed name resolution, except when I need it (https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=124490)
|||Okay let me be bit specific what I am trying to achieve.--will also explain what i mean by "recompile"
I have a database in Sql Server 2000 and I am trying to copy it on Sql server 2005.Now we have many changes like sort of =* join which we have to do in stored procs.So I am trying to get list of stored procs which wont compile successfully on sql server 2005.
One way to get this list is with SQL Server 2005 Ugrade Advisor, I am surprise but it didnt give me an exhaustive list.
So I thought I would be better if I could recompile all the stored procs, and that what i am trying to achieve.
Best Regards
Rahul
|||For that you would need to script out the stored procedures, and try to compile them. As long as object names are still the same (mitigating the whole question of delayed name resolution) then that will work.
If you know the pattern you are looking for, like =* or *=, you could use a search to find these cases:
DECLARE @.value nvarchar(128)
SET @.value = '=*'
SELECT cast(schema_name(schema_id) + '.' + name AS varchar(60)) AS name,
cast(type_desc AS varchar(20)) AS type , create_date,
modify_date,
char(13) + char(10)
+ '--select object_definition(' + cast(object_id as varchar(10)) + ') as [' + name + ']'
FROM sys.objects
WHERE charindex(@.value,replace(replace(object_definition(object_id),'[',''),']','')) > 0
--http://drsql.spaces.live.com/blog/cns!80677FB08B3162E4!1139.entry
Object_definition is a new function in 2005 that gives you the text of the object in a nice package.
But, no, there is no way to have this kind of syntax check done automatically for you if the Upgrade Advisor misses it.
|||
Thanks Louis
this '=*' was just one issue I cited for example, there are and can (which i dont know) many more, which give error on sql server 2005 and even SQL Server 2005 Ugrade Advisor didnt catch.
Can we rewrite your given code as
select object_name(id) from syscomments where text like '%=*%'
@. Louis -- But, no, there is no way to have this kind of syntax check done automatically for you if the Upgrade Advisor misses it.
Does this mean that we can not identify affected stored proc if Upgrade Advisor misses them, expect by executing them.
Regards
Rahul
|||Install the SQL Server Best Practices Analyzer and run it against your SQL Server 2000 database. This has a rule to check for the older-style outer joins.|||
Yeah, anything that the program Umachandar mentions or the upgrade advisor miss will not be found. I was just giving an alternative for searching through your code if you find things you need to.
And no, syscomments cannot be relied upon for a precise search, since it is chunked into 4000 character chunks. Object_definition returns the full text as a varchar(max) that you can use the like on.
My experience iwth the Upgrade Advisor was pretty pleasant. We had very little trouble taking our 2000 databases from 2005, but then again, we try to keep up and usually make those kind of changes ahead of time (not that that helps you :)
|||Just to site one example which made me wondering--
Here is a line of code from my stored proc
tsequal(TmStp, convert(varbinary(8), convert(bigint, @.xTmStp)))
Now the irony is my stored proc is got compiled in sql server 2000 and when i moved it to sql server 2005 and compiled it ther it gave me following error:-
Msg 102, Level 15, State 1, Procedure rsp_UpdPolSchdTmStp, Line 43
Incorrect syntax near 'TSEQUAL'.
Regards
Rahul Kumar
|||Yep, TSEQUAL is gone for good in 2005 (http://sqljunkies.com/Forums/ShowPost.aspx?PostID=2534) but it wasn't documented for quite a while.
It isn't necessary, so you can change:
tsequal(TmStp, convert(varbinary(8), convert(bigint, @.xTmStp)))
to
TmStp = convert(varbinary(8), convert(bigint, @.xTmStp)))
though I am not sure why you are doing all of the converting. if @.xTmStp is of varbinary(8) type (or rowversion/timestamp type) you can just do:
TmStp = @.xTmStp
No comments:
Post a Comment