Forcing name resolution on SP compile 
Author Message
 Forcing name resolution on SP compile
Is this possible?  I can't find mention of a way to do this in the SQL
Server2000 docs.


Sat, 14 Aug 2004 04:56:20 GMT
 Forcing name resolution on SP compile

sean,

if you want to force a sp to recompile then in your sp you should have...

create proc usp_sean
with recompile
as
....

--
-oj
http://rac4sql.home.attbi.com


Quote:
> Is this possible?  I can't find mention of a way to do this in the SQL
> Server2000 docs.



Sat, 14 Aug 2004 05:05:31 GMT
 Forcing name resolution on SP compile

Quote:
> sean,

> if you want to force a sp to recompile then in your sp you should have...

> create proc usp_sean
> with recompile
> as
> ....

Hrm... I don't want the SP to recompile on every execution though.  All I
want is a check when I initially create the SP that tells me if I am
referencing objects that do not already exist in the DB.  Basically, a quick
way to verify the correctness of code I'm developing without actually
executing the SP's.  I mention this because I ran into a bug recently that
turned out to be a misspelling of a table name in a SP.

Sean



Sat, 14 Aug 2004 05:13:03 GMT
 Forcing name resolution on SP compile
Tony wrote has an article on this topic, see if it helps:
http://www.sqlserver.co.nz/content/content.asp?article=92
--
HTH,
Vyas, MVP (SQL Server)

http://vyaskn.tripod.com/



Quote:
> sean,

> if you want to force a sp to recompile then in your sp you should have...

> create proc usp_sean
> with recompile
> as
> ....

Hrm... I don't want the SP to recompile on every execution though.  All I
want is a check when I initially create the SP that tells me if I am
referencing objects that do not already exist in the DB.  Basically, a quick
way to verify the correctness of code I'm developing without actually
executing the SP's.  I mention this because I ran into a bug recently that
turned out to be a misspelling of a table name in a SP.

Sean



Sat, 14 Aug 2004 05:21:23 GMT
 Forcing name resolution on SP compile
i see...defered resolution is a problem for you...this is by design as of
sql7, i believe. perhaps, you want to set show_plan to see if it throws any
error. see book online for more info regarding show_plan.

--
-oj
http://rac4sql.home.attbi.com


Quote:


> > sean,

> > if you want to force a sp to recompile then in your sp you should
have...

> > create proc usp_sean
> > with recompile
> > as
> > ....

> Hrm... I don't want the SP to recompile on every execution though.  All I
> want is a check when I initially create the SP that tells me if I am
> referencing objects that do not already exist in the DB.  Basically, a
quick
> way to verify the correctness of code I'm developing without actually
> executing the SP's.  I mention this because I ran into a bug recently that
> turned out to be a misspelling of a table name in a SP.

> Sean



Sat, 14 Aug 2004 05:31:08 GMT
 Forcing name resolution on SP compile
You could code to test for the table's existence using something like:

IF EXISTS (SELECT * FROM sysobjects
  WHERE id = object_id(N'[dbo].[<tablename>]')
   AND OBJECTPROPERTY(id, N'IsTable') = 1)

You could use this inside the SP itself to prevent runtime errors or you
could add this to your script that creates the SP so that it is only created
if the table(s) exist.

Michael MacGregor
Database Architect
SalesDriver



Sat, 14 Aug 2004 05:37:13 GMT
 Forcing name resolution on SP compile
set fmtonly on

set fmtonly off

    This will work for all SPs except those that create objects in the code.
Another way is to run the sp like:

set xact_abort on
go
begin tran

rollback
go

--
Umachandar Jayachandran
SQL Resources at http://www.umachandar.com/resources.htm
( Please reply only to newsgroup. )



Sat, 14 Aug 2004 05:45:01 GMT
 Forcing name resolution on SP compile


Quote:
> Tony wrote has an article on this topic, see if it helps:
> http://www.sqlserver.co.nz/content/content.asp?article=92

Perfect.  This is exactly what I was looking for.  Now I can run a single
script to test all of my SPs without impacting the final code.  Thanks for
the help.

Sean



Sat, 14 Aug 2004 06:39:08 GMT
 Forcing name resolution on SP compile
You are welcome Sean :)
--
HTH,
Vyas, MVP (SQL Server)

http://vyaskn.tripod.com/




Quote:
> Tony wrote has an article on this topic, see if it helps:
> http://www.sqlserver.co.nz/content/content.asp?article=92

Perfect.  This is exactly what I was looking for.  Now I can run a single
script to test all of my SPs without impacting the final code.  Thanks for
the help.

Sean



Sat, 14 Aug 2004 06:50:11 GMT
 
 [ 9 post ] 

 Relevant Pages 

1. SQL Server forcing the owner name to be entered all of a sudden in table name

2. How can I call a SP inside a SP without the owner name

3. How to Exec a SP where SP name and params are alll variables

4. Using Exec in an SP / Columns named by @variable in an SP

5. Get sp's own name within the SP

6. 6.5 query resolution vs. 4.21a query resolution

7. Name resolution (on local domain)

8. system tables - link SP name and Table Name?

9. Views with dynamic table name resolution

10. Deferred name resolution in stored procs and triggers but not views

11. Name Resolution in SPs

12. name resolution


 
Powered by phpBB® Forum Software