Hello
Is it possible to force SQL Server to compile stored procedures after
creation? I mean, when I execute something like:
CREATE PROC SP1 AS
select * from X
where table X does not exist, I want to get error or warning. Now the proc
is created and the error is thrown only when it is executed. We have more
then 1500 stored procedures in the database now scripted out into sql
scripts. We want to ensure that scripts (and all objects like SPs, UDFs,..)
are valid after developers updates but by simply running the script to creat
e
DB and all its content does not produce any warning/error. How can we
accomplish that?
Thanks
eXavierHi
i can think about adding IF OBJECT_ID('Table') IS NULL which means if the
NULL then object does not exist and your error will be thrown
"eXavier" <eXavier@.community.nospam> wrote in message
news:7A7CC2D9-36E5-4895-98A1-6FB7FA0325EE@.microsoft.com...
> Hello
> Is it possible to force SQL Server to compile stored procedures after
> creation? I mean, when I execute something like:
> CREATE PROC SP1 AS
> select * from X
> where table X does not exist, I want to get error or warning. Now the proc
> is created and the error is thrown only when it is executed. We have more
> then 1500 stored procedures in the database now scripted out into sql
> scripts. We want to ensure that scripts (and all objects like SPs,
> UDFs,..)
> are valid after developers updates but by simply running the script to
> create
> DB and all its content does not produce any warning/error. How can we
> accomplish that?
> Thanks
> eXavier|||Hi,
I'm not sure if I understand what you mean. Do you mean to write some sql
script parser, extract all table names from all stored procedures and then
call your IF check? This is not acceptable for us. In fact we want the serve
r
to compile procedures to get eventual errors.. SP is compiled when executed
first but we cannot automatically execute them all as they have different
parameters..
Any other idea?
Thanks
eXavier
"Uri Dimant" wrote:
> Hi
> i can think about adding IF OBJECT_ID('Table') IS NULL which means if the
> NULL then object does not exist and your error will be thrown
>
>
> "eXavier" <eXavier@.community.nospam> wrote in message
> news:7A7CC2D9-36E5-4895-98A1-6FB7FA0325EE@.microsoft.com...
>
>|||Specify WITH RECOMPILE in your stored procedure. The procedure will not be
cached and will recompile at runtime.
--
MG
"eXavier" wrote:
> Hello
> Is it possible to force SQL Server to compile stored procedures after
> creation? I mean, when I execute something like:
> CREATE PROC SP1 AS
> select * from X
> where table X does not exist, I want to get error or warning. Now the proc
> is created and the error is thrown only when it is executed. We have more
> then 1500 stored procedures in the database now scripted out into sql
> scripts. We want to ensure that scripts (and all objects like SPs, UDFs,..
)
> are valid after developers updates but by simply running the script to cre
ate
> DB and all its content does not produce any warning/error. How can we
> accomplish that?
> Thanks
> eXavier|||Hi,
WITH RECOMPILE does exactly what you wrote. But it does not help in creation
time - you can still create procedure referencing non-existant tables.
The error is thrown only when the proc is executed, coming back to our
problem. (Further, if the SP compiles we want it to be cached.)
"MGeles" wrote:
[vbcol=seagreen]
> Specify WITH RECOMPILE in your stored procedure. The procedure will not b
e
> cached and will recompile at runtime.
> --
> MG
>
> "eXavier" wrote:
>|||I'm afraid you cannot do that
"eXavier" <eXavier@.community.nospam> wrote in message
news:4BBE6A41-D987-4A5C-A953-1BD71A01A972@.microsoft.com...[vbcol=seagreen]
> Hi,
> I'm not sure if I understand what you mean. Do you mean to write some sql
> script parser, extract all table names from all stored procedures and then
> call your IF check? This is not acceptable for us. In fact we want the
> server
> to compile procedures to get eventual errors.. SP is compiled when
> executed
> first but we cannot automatically execute them all as they have different
> parameters..
> Any other idea?
> Thanks
> eXavier
> "Uri Dimant" wrote:
>|||"eXavier" <eXavier@.community.nospam> wrote in message
news:7A7CC2D9-36E5-4895-98A1-6FB7FA0325EE@.microsoft.com...
> Hello
> Is it possible to force SQL Server to compile stored procedures after
> creation?
Wayyy back in the day (SQL 6.5 I believe) this was possible, since SQL
checked for the existence of objects before creating stored procedures.
Apparently this caused a bunch of headaches, especially with database object
scripts that weren't created in the proper order, so "deferred name
resolution" was introduced. Table names are not resolved until run-time
instead of at creation time. Your best bet might be to 1) Do what Uri
suggested and explicitly check for the existence of tables yourself before
running the CREATE SP statement, or 2) Do what MS suggests below and test
your code after creation. Uri's suggestion should not be all that
difficult. Maybe a single script that tests for the existence of *all*
tables that are supposed to be in the database. You could run this script
before you try to run any SP creation scripts, and abort the mission based
on the result.
Here's what MS has to say on deferred name resolution:
"Deferred Name Resolution. Deferred name resolution allows procedure
compilation without all table references being present. Deferred name
resolution works in much the same way as the object-oriented concept of late
binding. At compile time, the compiler attempts to resolve all table names
that the procedure references. But if a table does not yet exist, the
compiler defers this name resolution until execution time.
For developers who have used temporary tables within their stored procedures
or triggers, this subtle new feature is long overdue. Although this feature
is useful, it has a side effect that many developers might initially
miss-the compiler no longer reliably catches table-name typos. Yes, you now
must test your code. This statement might sound funny at first, but if you
are not aware of deferred name resolution, you might find yourself wondering
why the compiler missed this error."
(From
http://www.microsoft.com/technet/pr...loy/migrat.mspx)|||Hi eXavier,
xyz's suggestion is reasonable. Appreciate your understanding that this
requirement is individual and actually limited by the design of SQL Server.
It is impossible for us to change the native behavior. I noticed that you
just wanted to ensure that scripts are valid after developers updates but
by simply running the script to create DB and all its content does not
produce any warning/error. You may consider to find a way from management.
For example, setup a test database environment which is same as the
development database then script a file to execute all the SPs or UDFs
that you want to check. This may require a standard process that the
developer of a SP or a UDF should provide a SQL test statement which can be
directly copied into the script file for checking at runtime. If some
errors are thrown out, please check and correct both the test database and
the development database. It is important to keep the identical environment
between the test database and the development database.
If you have any other questions or concerns, please feel free to let me
know. It is my pleasure to be of assistance.
Charles Wang
Microsoft Online Community Support
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||You can catch most issues by executing the proc with SET FMTONLY ON like the
example below (passing any needed parameters as NULL values). However, some
errors can only be found by actually executing the proc.
SET FMTONLY ON
GO
EXEC dbo.SP1
GO
SET FMTONLY OFF
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"eXavier" <eXavier@.community.nospam> wrote in message
news:7A7CC2D9-36E5-4895-98A1-6FB7FA0325EE@.microsoft.com...
> Hello
> Is it possible to force SQL Server to compile stored procedures after
> creation? I mean, when I execute something like:
> CREATE PROC SP1 AS
> select * from X
> where table X does not exist, I want to get error or warning. Now the proc
> is created and the error is thrown only when it is executed. We have more
> then 1500 stored procedures in the database now scripted out into sql
> scripts. We want to ensure that scripts (and all objects like SPs,
> UDFs,..)
> are valid after developers updates but by simply running the script to
> create
> DB and all its content does not produce any warning/error. How can we
> accomplish that?
> Thanks
> eXavier
Showing posts with label force. Show all posts
Showing posts with label force. Show all posts
Sunday, February 19, 2012
compile SP after creation?
Hello
Is it possible to force SQL Server to compile stored procedures after
creation? I mean, when I execute something like:
CREATE PROC SP1 AS
select * from X
where table X does not exist, I want to get error or warning. Now the proc
is created and the error is thrown only when it is executed. We have more
then 1500 stored procedures in the database now scripted out into sql
scripts. We want to ensure that scripts (and all objects like SPs, UDFs,..)
are valid after developers updates but by simply running the script to create
DB and all its content does not produce any warning/error. How can we
accomplish that?
Thanks
eXavierHi
i can think about adding IF OBJECT_ID('Table') IS NULL which means if the
NULL then object does not exist and your error will be thrown
"eXavier" <eXavier@.community.nospam> wrote in message
news:7A7CC2D9-36E5-4895-98A1-6FB7FA0325EE@.microsoft.com...
> Hello
> Is it possible to force SQL Server to compile stored procedures after
> creation? I mean, when I execute something like:
> CREATE PROC SP1 AS
> select * from X
> where table X does not exist, I want to get error or warning. Now the proc
> is created and the error is thrown only when it is executed. We have more
> then 1500 stored procedures in the database now scripted out into sql
> scripts. We want to ensure that scripts (and all objects like SPs,
> UDFs,..)
> are valid after developers updates but by simply running the script to
> create
> DB and all its content does not produce any warning/error. How can we
> accomplish that?
> Thanks
> eXavier|||Hi,
I'm not sure if I understand what you mean. Do you mean to write some sql
script parser, extract all table names from all stored procedures and then
call your IF check? This is not acceptable for us. In fact we want the server
to compile procedures to get eventual errors.. SP is compiled when executed
first but we cannot automatically execute them all as they have different
parameters..
Any other idea?
Thanks
eXavier
"Uri Dimant" wrote:
> Hi
> i can think about adding IF OBJECT_ID('Table') IS NULL which means if the
> NULL then object does not exist and your error will be thrown
>
>
> "eXavier" <eXavier@.community.nospam> wrote in message
> news:7A7CC2D9-36E5-4895-98A1-6FB7FA0325EE@.microsoft.com...
> > Hello
> > Is it possible to force SQL Server to compile stored procedures after
> > creation? I mean, when I execute something like:
> >
> > CREATE PROC SP1 AS
> > select * from X
> >
> > where table X does not exist, I want to get error or warning. Now the proc
> > is created and the error is thrown only when it is executed. We have more
> > then 1500 stored procedures in the database now scripted out into sql
> > scripts. We want to ensure that scripts (and all objects like SPs,
> > UDFs,..)
> > are valid after developers updates but by simply running the script to
> > create
> > DB and all its content does not produce any warning/error. How can we
> > accomplish that?
> >
> > Thanks
> > eXavier
>
>|||Specify WITH RECOMPILE in your stored procedure. The procedure will not be
cached and will recompile at runtime.
--
MG
"eXavier" wrote:
> Hello
> Is it possible to force SQL Server to compile stored procedures after
> creation? I mean, when I execute something like:
> CREATE PROC SP1 AS
> select * from X
> where table X does not exist, I want to get error or warning. Now the proc
> is created and the error is thrown only when it is executed. We have more
> then 1500 stored procedures in the database now scripted out into sql
> scripts. We want to ensure that scripts (and all objects like SPs, UDFs,..)
> are valid after developers updates but by simply running the script to create
> DB and all its content does not produce any warning/error. How can we
> accomplish that?
> Thanks
> eXavier|||Hi,
WITH RECOMPILE does exactly what you wrote. But it does not help in creation
time - you can still create procedure referencing non-existant tables.
The error is thrown only when the proc is executed, coming back to our
problem. (Further, if the SP compiles we want it to be cached.)
"MGeles" wrote:
> Specify WITH RECOMPILE in your stored procedure. The procedure will not be
> cached and will recompile at runtime.
> --
> MG
>
> "eXavier" wrote:
> > Hello
> > Is it possible to force SQL Server to compile stored procedures after
> > creation? I mean, when I execute something like:
> >
> > CREATE PROC SP1 AS
> > select * from X
> >
> > where table X does not exist, I want to get error or warning. Now the proc
> > is created and the error is thrown only when it is executed. We have more
> > then 1500 stored procedures in the database now scripted out into sql
> > scripts. We want to ensure that scripts (and all objects like SPs, UDFs,..)
> > are valid after developers updates but by simply running the script to create
> > DB and all its content does not produce any warning/error. How can we
> > accomplish that?
> >
> > Thanks
> > eXavier|||I'm afraid you cannot do that
"eXavier" <eXavier@.community.nospam> wrote in message
news:4BBE6A41-D987-4A5C-A953-1BD71A01A972@.microsoft.com...
> Hi,
> I'm not sure if I understand what you mean. Do you mean to write some sql
> script parser, extract all table names from all stored procedures and then
> call your IF check? This is not acceptable for us. In fact we want the
> server
> to compile procedures to get eventual errors.. SP is compiled when
> executed
> first but we cannot automatically execute them all as they have different
> parameters..
> Any other idea?
> Thanks
> eXavier
> "Uri Dimant" wrote:
>> Hi
>> i can think about adding IF OBJECT_ID('Table') IS NULL which means if
>> the
>> NULL then object does not exist and your error will be thrown
>>
>>
>> "eXavier" <eXavier@.community.nospam> wrote in message
>> news:7A7CC2D9-36E5-4895-98A1-6FB7FA0325EE@.microsoft.com...
>> > Hello
>> > Is it possible to force SQL Server to compile stored procedures after
>> > creation? I mean, when I execute something like:
>> >
>> > CREATE PROC SP1 AS
>> > select * from X
>> >
>> > where table X does not exist, I want to get error or warning. Now the
>> > proc
>> > is created and the error is thrown only when it is executed. We have
>> > more
>> > then 1500 stored procedures in the database now scripted out into sql
>> > scripts. We want to ensure that scripts (and all objects like SPs,
>> > UDFs,..)
>> > are valid after developers updates but by simply running the script to
>> > create
>> > DB and all its content does not produce any warning/error. How can we
>> > accomplish that?
>> >
>> > Thanks
>> > eXavier
>>|||"eXavier" <eXavier@.community.nospam> wrote in message
news:7A7CC2D9-36E5-4895-98A1-6FB7FA0325EE@.microsoft.com...
> Hello
> Is it possible to force SQL Server to compile stored procedures after
> creation?
Wayyy back in the day (SQL 6.5 I believe) this was possible, since SQL
checked for the existence of objects before creating stored procedures.
Apparently this caused a bunch of headaches, especially with database object
scripts that weren't created in the proper order, so "deferred name
resolution" was introduced. Table names are not resolved until run-time
instead of at creation time. Your best bet might be to 1) Do what Uri
suggested and explicitly check for the existence of tables yourself before
running the CREATE SP statement, or 2) Do what MS suggests below and test
your code after creation. Uri's suggestion should not be all that
difficult. Maybe a single script that tests for the existence of *all*
tables that are supposed to be in the database. You could run this script
before you try to run any SP creation scripts, and abort the mission based
on the result.
Here's what MS has to say on deferred name resolution:
"Deferred Name Resolution. Deferred name resolution allows procedure
compilation without all table references being present. Deferred name
resolution works in much the same way as the object-oriented concept of late
binding. At compile time, the compiler attempts to resolve all table names
that the procedure references. But if a table does not yet exist, the
compiler defers this name resolution until execution time.
For developers who have used temporary tables within their stored procedures
or triggers, this subtle new feature is long overdue. Although this feature
is useful, it has a side effect that many developers might initially
miss-the compiler no longer reliably catches table-name typos. Yes, you now
must test your code. This statement might sound funny at first, but if you
are not aware of deferred name resolution, you might find yourself wondering
why the compiler missed this error."
(From
http://www.microsoft.com/technet/prodtechnol/sql/70/deploy/migrat.mspx)|||Hi eXavier,
xyz's suggestion is reasonable. Appreciate your understanding that this
requirement is individual and actually limited by the design of SQL Server.
It is impossible for us to change the native behavior. I noticed that you
just wanted to ensure that scripts are valid after developers updates but
by simply running the script to create DB and all its content does not
produce any warning/error. You may consider to find a way from management.
For example, setup a test database environment which is same as the
development database then script a file to execute all the SPs or UDFs
that you want to check. This may require a standard process that the
developer of a SP or a UDF should provide a SQL test statement which can be
directly copied into the script file for checking at runtime. If some
errors are thrown out, please check and correct both the test database and
the development database. It is important to keep the identical environment
between the test database and the development database.
If you have any other questions or concerns, please feel free to let me
know. It is my pleasure to be of assistance.
Charles Wang
Microsoft Online Community Support
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||You can catch most issues by executing the proc with SET FMTONLY ON like the
example below (passing any needed parameters as NULL values). However, some
errors can only be found by actually executing the proc.
SET FMTONLY ON
GO
EXEC dbo.SP1
GO
SET FMTONLY OFF
GO
--
Hope this helps.
Dan Guzman
SQL Server MVP
"eXavier" <eXavier@.community.nospam> wrote in message
news:7A7CC2D9-36E5-4895-98A1-6FB7FA0325EE@.microsoft.com...
> Hello
> Is it possible to force SQL Server to compile stored procedures after
> creation? I mean, when I execute something like:
> CREATE PROC SP1 AS
> select * from X
> where table X does not exist, I want to get error or warning. Now the proc
> is created and the error is thrown only when it is executed. We have more
> then 1500 stored procedures in the database now scripted out into sql
> scripts. We want to ensure that scripts (and all objects like SPs,
> UDFs,..)
> are valid after developers updates but by simply running the script to
> create
> DB and all its content does not produce any warning/error. How can we
> accomplish that?
> Thanks
> eXavier
Is it possible to force SQL Server to compile stored procedures after
creation? I mean, when I execute something like:
CREATE PROC SP1 AS
select * from X
where table X does not exist, I want to get error or warning. Now the proc
is created and the error is thrown only when it is executed. We have more
then 1500 stored procedures in the database now scripted out into sql
scripts. We want to ensure that scripts (and all objects like SPs, UDFs,..)
are valid after developers updates but by simply running the script to create
DB and all its content does not produce any warning/error. How can we
accomplish that?
Thanks
eXavierHi
i can think about adding IF OBJECT_ID('Table') IS NULL which means if the
NULL then object does not exist and your error will be thrown
"eXavier" <eXavier@.community.nospam> wrote in message
news:7A7CC2D9-36E5-4895-98A1-6FB7FA0325EE@.microsoft.com...
> Hello
> Is it possible to force SQL Server to compile stored procedures after
> creation? I mean, when I execute something like:
> CREATE PROC SP1 AS
> select * from X
> where table X does not exist, I want to get error or warning. Now the proc
> is created and the error is thrown only when it is executed. We have more
> then 1500 stored procedures in the database now scripted out into sql
> scripts. We want to ensure that scripts (and all objects like SPs,
> UDFs,..)
> are valid after developers updates but by simply running the script to
> create
> DB and all its content does not produce any warning/error. How can we
> accomplish that?
> Thanks
> eXavier|||Hi,
I'm not sure if I understand what you mean. Do you mean to write some sql
script parser, extract all table names from all stored procedures and then
call your IF check? This is not acceptable for us. In fact we want the server
to compile procedures to get eventual errors.. SP is compiled when executed
first but we cannot automatically execute them all as they have different
parameters..
Any other idea?
Thanks
eXavier
"Uri Dimant" wrote:
> Hi
> i can think about adding IF OBJECT_ID('Table') IS NULL which means if the
> NULL then object does not exist and your error will be thrown
>
>
> "eXavier" <eXavier@.community.nospam> wrote in message
> news:7A7CC2D9-36E5-4895-98A1-6FB7FA0325EE@.microsoft.com...
> > Hello
> > Is it possible to force SQL Server to compile stored procedures after
> > creation? I mean, when I execute something like:
> >
> > CREATE PROC SP1 AS
> > select * from X
> >
> > where table X does not exist, I want to get error or warning. Now the proc
> > is created and the error is thrown only when it is executed. We have more
> > then 1500 stored procedures in the database now scripted out into sql
> > scripts. We want to ensure that scripts (and all objects like SPs,
> > UDFs,..)
> > are valid after developers updates but by simply running the script to
> > create
> > DB and all its content does not produce any warning/error. How can we
> > accomplish that?
> >
> > Thanks
> > eXavier
>
>|||Specify WITH RECOMPILE in your stored procedure. The procedure will not be
cached and will recompile at runtime.
--
MG
"eXavier" wrote:
> Hello
> Is it possible to force SQL Server to compile stored procedures after
> creation? I mean, when I execute something like:
> CREATE PROC SP1 AS
> select * from X
> where table X does not exist, I want to get error or warning. Now the proc
> is created and the error is thrown only when it is executed. We have more
> then 1500 stored procedures in the database now scripted out into sql
> scripts. We want to ensure that scripts (and all objects like SPs, UDFs,..)
> are valid after developers updates but by simply running the script to create
> DB and all its content does not produce any warning/error. How can we
> accomplish that?
> Thanks
> eXavier|||Hi,
WITH RECOMPILE does exactly what you wrote. But it does not help in creation
time - you can still create procedure referencing non-existant tables.
The error is thrown only when the proc is executed, coming back to our
problem. (Further, if the SP compiles we want it to be cached.)
"MGeles" wrote:
> Specify WITH RECOMPILE in your stored procedure. The procedure will not be
> cached and will recompile at runtime.
> --
> MG
>
> "eXavier" wrote:
> > Hello
> > Is it possible to force SQL Server to compile stored procedures after
> > creation? I mean, when I execute something like:
> >
> > CREATE PROC SP1 AS
> > select * from X
> >
> > where table X does not exist, I want to get error or warning. Now the proc
> > is created and the error is thrown only when it is executed. We have more
> > then 1500 stored procedures in the database now scripted out into sql
> > scripts. We want to ensure that scripts (and all objects like SPs, UDFs,..)
> > are valid after developers updates but by simply running the script to create
> > DB and all its content does not produce any warning/error. How can we
> > accomplish that?
> >
> > Thanks
> > eXavier|||I'm afraid you cannot do that
"eXavier" <eXavier@.community.nospam> wrote in message
news:4BBE6A41-D987-4A5C-A953-1BD71A01A972@.microsoft.com...
> Hi,
> I'm not sure if I understand what you mean. Do you mean to write some sql
> script parser, extract all table names from all stored procedures and then
> call your IF check? This is not acceptable for us. In fact we want the
> server
> to compile procedures to get eventual errors.. SP is compiled when
> executed
> first but we cannot automatically execute them all as they have different
> parameters..
> Any other idea?
> Thanks
> eXavier
> "Uri Dimant" wrote:
>> Hi
>> i can think about adding IF OBJECT_ID('Table') IS NULL which means if
>> the
>> NULL then object does not exist and your error will be thrown
>>
>>
>> "eXavier" <eXavier@.community.nospam> wrote in message
>> news:7A7CC2D9-36E5-4895-98A1-6FB7FA0325EE@.microsoft.com...
>> > Hello
>> > Is it possible to force SQL Server to compile stored procedures after
>> > creation? I mean, when I execute something like:
>> >
>> > CREATE PROC SP1 AS
>> > select * from X
>> >
>> > where table X does not exist, I want to get error or warning. Now the
>> > proc
>> > is created and the error is thrown only when it is executed. We have
>> > more
>> > then 1500 stored procedures in the database now scripted out into sql
>> > scripts. We want to ensure that scripts (and all objects like SPs,
>> > UDFs,..)
>> > are valid after developers updates but by simply running the script to
>> > create
>> > DB and all its content does not produce any warning/error. How can we
>> > accomplish that?
>> >
>> > Thanks
>> > eXavier
>>|||"eXavier" <eXavier@.community.nospam> wrote in message
news:7A7CC2D9-36E5-4895-98A1-6FB7FA0325EE@.microsoft.com...
> Hello
> Is it possible to force SQL Server to compile stored procedures after
> creation?
Wayyy back in the day (SQL 6.5 I believe) this was possible, since SQL
checked for the existence of objects before creating stored procedures.
Apparently this caused a bunch of headaches, especially with database object
scripts that weren't created in the proper order, so "deferred name
resolution" was introduced. Table names are not resolved until run-time
instead of at creation time. Your best bet might be to 1) Do what Uri
suggested and explicitly check for the existence of tables yourself before
running the CREATE SP statement, or 2) Do what MS suggests below and test
your code after creation. Uri's suggestion should not be all that
difficult. Maybe a single script that tests for the existence of *all*
tables that are supposed to be in the database. You could run this script
before you try to run any SP creation scripts, and abort the mission based
on the result.
Here's what MS has to say on deferred name resolution:
"Deferred Name Resolution. Deferred name resolution allows procedure
compilation without all table references being present. Deferred name
resolution works in much the same way as the object-oriented concept of late
binding. At compile time, the compiler attempts to resolve all table names
that the procedure references. But if a table does not yet exist, the
compiler defers this name resolution until execution time.
For developers who have used temporary tables within their stored procedures
or triggers, this subtle new feature is long overdue. Although this feature
is useful, it has a side effect that many developers might initially
miss-the compiler no longer reliably catches table-name typos. Yes, you now
must test your code. This statement might sound funny at first, but if you
are not aware of deferred name resolution, you might find yourself wondering
why the compiler missed this error."
(From
http://www.microsoft.com/technet/prodtechnol/sql/70/deploy/migrat.mspx)|||Hi eXavier,
xyz's suggestion is reasonable. Appreciate your understanding that this
requirement is individual and actually limited by the design of SQL Server.
It is impossible for us to change the native behavior. I noticed that you
just wanted to ensure that scripts are valid after developers updates but
by simply running the script to create DB and all its content does not
produce any warning/error. You may consider to find a way from management.
For example, setup a test database environment which is same as the
development database then script a file to execute all the SPs or UDFs
that you want to check. This may require a standard process that the
developer of a SP or a UDF should provide a SQL test statement which can be
directly copied into the script file for checking at runtime. If some
errors are thrown out, please check and correct both the test database and
the development database. It is important to keep the identical environment
between the test database and the development database.
If you have any other questions or concerns, please feel free to let me
know. It is my pleasure to be of assistance.
Charles Wang
Microsoft Online Community Support
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||You can catch most issues by executing the proc with SET FMTONLY ON like the
example below (passing any needed parameters as NULL values). However, some
errors can only be found by actually executing the proc.
SET FMTONLY ON
GO
EXEC dbo.SP1
GO
SET FMTONLY OFF
GO
--
Hope this helps.
Dan Guzman
SQL Server MVP
"eXavier" <eXavier@.community.nospam> wrote in message
news:7A7CC2D9-36E5-4895-98A1-6FB7FA0325EE@.microsoft.com...
> Hello
> Is it possible to force SQL Server to compile stored procedures after
> creation? I mean, when I execute something like:
> CREATE PROC SP1 AS
> select * from X
> where table X does not exist, I want to get error or warning. Now the proc
> is created and the error is thrown only when it is executed. We have more
> then 1500 stored procedures in the database now scripted out into sql
> scripts. We want to ensure that scripts (and all objects like SPs,
> UDFs,..)
> are valid after developers updates but by simply running the script to
> create
> DB and all its content does not produce any warning/error. How can we
> accomplish that?
> Thanks
> eXavier
Friday, February 17, 2012
compile a store proc again
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
compile a store proc again
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
Subscribe to:
Posts (Atom)