Sql Comparing two tables: compile

Showing posts with label compile. Show all posts

Sunday, February 19, 2012

Compile store procedures

I'm having a excessive amount of lock timeout and I discover that every time
when users execute store procedures, these procedures are compile before
execution. Anyone has an idea about this behaviour ?
thank you for your help...Check this link:
http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q243/5/86.ASP&NoWebContent=1
--
HTH,
SriSamp
Please reply to the whole group only!
"Tony" <news@.hotmail.com> wrote in message
news:%23l0Kxh8PDHA.2424@.tk2msftngp13.phx.gbl...
> I'm having a excessive amount of lock timeout and I discover that every
time
> when users execute store procedures, these procedures are compile before
> execution. Anyone has an idea about this behaviour ?
> thank you for your help...
>

Compile SQL Please Help

Welcome
i am want to teach sql . how to
install compile<comedy>How do I reading manual?</comedy>

<serious>I don't think it's physically possible for anyone to understand what you've just asked.</serious>|||i think he/she wants to know how to download and install the free sql server express software|||if that's the case, it's here:

http://msdn.microsoft.com/vstudio/express/sql/download/|||I hope you want to teach .. yourself ;)

I am afraid you cannot move your experience directly from Oracle to SQL Server.
You need to install SQL Server and tools – Query Analyzer for SQL Server 2000 or Management Studio for SQL Server 2005.|||thank you very much

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 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

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

Friday, February 17, 2012

Compile Error: Redefinition of IRowsetBookmark (VS 2005)

Hello,

actually i try to create a native c++ desktop-application that will have to access a SQL CE 3.1 db-file over OLE DB. For that purpose i want to use the same self written wrapper class(for the OLE DB stuff) that i use for a native WM2003 application that creates the db files to be accessed on the desktop application.
The WM2003 application including my wrapper class compiles fine with eVC++ 4.0.
But now with VS 2005 including the same class i got this compile error: "Redefinition of class IRowsetBookmark". I have to add that i used the application wizard to generate a mfc sdi application with database support(ODBC is used in this app, too).
I figured out that in the following 2 inlcuded files IRowsetBookmark is defined:
1)The manually included "ssceoledb30.h" in my wrapper class.
2)The probably from the application wizard included "oledb.h" (in "\Microsoft Visual Studio 8\VC\PlatformSDK\Include")

My workaround for the moment is to comment the class definition in the file "ssceoledb30.h" out because i don't use this OLE DB interface in my wrapper class. But i don't think that this is the way that microsoft supposed to go.

Does anybody had this problem and solved it other way than simply comment one of the class definition out?

I would appreciate it when someone out there knows the answer and could tell me.

Kind regards,
Andre

The ssceoledb30.h file contains both the standard oledb.h and the SQL Compact Edition-specific definitions. Theoretically, you should be able to use just this file. In my products, I'm using a sqlmobile.h file that was published by Microsoft but does not seem to be available now. This file contains only the SQL Compact Edition stuff and was designed to be used with the oledb.h file.

|||Thanks for your answer.
The problem will be to find the place where the oledb.h is included. I've done a search over the complete project and found no include line for that header. So it is inlcluded within some of the other header files.
It's not my intention to modify any system header files. So i would also prefer a header you mention that only contains the SQL Compact Edition-specific definitions.

Maybe someone knows where this header file from microsoft is still available or knows another way to get this working without the need of modifying standard header files.

Kind regards,
Andre
|||

I am sorry but the sqlmobile.h file was never released by Microsoft. Would you believe that I built this file myself from ssceoledb30.h, wrote an article about this and completely forgot about it?

I'm getting old...

|||

Jo?o Paulo Figueira wrote:

I am sorry but the sqlmobile.h file was never released by Microsoft. Would you believe that I built this file myself from ssceoledb30.h, wrote an article about this and completely forgot about it?

I'm getting old...

Never mind!

With your help i got a solution without the need to modify the standard headers. That's the main thing. Now with dependent include of ssceoledb30.h or sqlmobile my wrapper class can be used both for native mobile and native desktop developement without the need of adaption.
In difference to your article i have additionally had to comment the complete class IRowsetBookmark out as stated in the early post, not just the GUID definition of IID_IRowsetBookmark.

Kind regards,
Andre

Compile Error: Redefinition of IRowsetBookmark (VS 2005)

Hello,

Does anybody had this problem and solved it other way than simply comment one of the class definition out?

I would appreciate it when someone out there knows the answer and could tell me.

Kind regards,
Andre

Maybe someone knows where this header file from microsoft is still available or knows another way to get this working without the need of modifying standard header files.

Kind regards,
Andre
|||

I'm getting old...

|||

Jo?o Paulo Figueira wrote:

I am sorry but the sqlmobile.h file was never released by Microsoft. Would you believe that I built this file myself from ssceoledb30.h, wrote an article about this and completely forgot about it?

I'm getting old...

Never mind!

Kind regards,
Andre

Compile Blocking Issues

We've recently been experiencing problems locking issues seemingly caused by
compiles.. ..cpu max's out at 100%, query duration get longer, and we see a
large number of LCK_M_X in sysprocesses with coupled with something like TAB:
5:736291420:0 [COMPILE].. ..what could be causing this issue?
K1) What is the table referenced in the lock?
2) It could be caused by lots of compiles' :-)) Seriously, do you do a
lot of sproc calls? Even worse, lots of badly-written ADO calls? Lots of
temptable useage (it is SCARY how many recompiles can be caused by this!!)?
SQL 2000 or 2005'
TheSQLGuru
President
Indicium Resources, Inc.
"Ben UK" <BenUK@.discussions.microsoft.com> wrote in message
news:6E5794CF-C439-4FC7-A0EE-C71478CF552A@.microsoft.com...
> We've recently been experiencing problems locking issues seemingly caused
> by
> compiles.. ..cpu max's out at 100%, query duration get longer, and we see
> a
> large number of LCK_M_X in sysprocesses with coupled with something like
> TAB:
> 5:736291420:0 [COMPILE].. ..what could be causing this issue?
> K|||Have you read this:
"Description of SQL Server blocking caused by compile locks"
http://support.microsoft.com/kb/263889
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Ben UK" <BenUK@.discussions.microsoft.com> wrote in message
news:6E5794CF-C439-4FC7-A0EE-C71478CF552A@.microsoft.com...
> We've recently been experiencing problems locking issues seemingly caused
> by
> compiles.. ..cpu max's out at 100%, query duration get longer, and we see
> a
> large number of LCK_M_X in sysprocesses with coupled with something like
> TAB:
> 5:736291420:0 [COMPILE].. ..what could be causing this issue?
> K|||On Jun 13, 11:49 am, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
> 1) What is the table referenced in the lock?
> 2) It could be caused by lots of compiles' :-)) Seriously, do you do a
> lot of sproc calls? Even worse, lots of badly-written ADO calls? Lots of
> temptable useage (it is SCARY how many recompiles can be caused by this!!)?
> SQL 2000 or 2005'
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Ben UK" <B...@.discussions.microsoft.com> wrote in message
> news:6E5794CF-C439-4FC7-A0EE-C71478CF552A@.microsoft.com...
>
>
> > We've recently been experiencing problems locking issues seemingly caused
> > by
> > compiles.. ..cpu max's out at 100%, query duration get longer, and we see
> > a
> > large number of LCK_M_X in sysprocesses with coupled with something like
> > TAB:
> > 5:736291420:0 [COMPILE].. ..what could be causing this issue?
> > K- Hide quoted text -
> - Show quoted text -
I've observed this behavior with sprocs that are called very often and
make use of temp tables. As data is inserted into the temp table one
or more sp-recompile events will happen. If many concurrent spids are
calling this sproc at the same time, SQL appears to allow only one
spid at a time to perform the recompile - this will reduce the level
of concurrency in the system and you will see blocking spids that are
marked with [COMPILE]. Try to use profiler to trace stored procedure
recompiles, and RPC:Completed events to identify what is being
recomplied, then ideally try to tune the code to reduce recompiles.|||Thanks for the responses, we're SQL 2005, SP1, the objects referenced in the
lock are primarily 2 sp's and 1 udf.. ..when querying sysprocesses we can see
around 30 occurances of this lock from around 600 connections.
The problems *seem*to have started since we changed the schema and removed a
table containing denormalized data and replaced it with a view. Both the
sp's that have compile issues reference the new view (as do around 50-60
more), the udf doesn't reference any new tables... ...the udf uses a table
variable, but neither sp uses temp tables of any kind.
http://support.microsoft.com/kb/263889
^ I did read the article earlier today.. ..it was kinda useful, but I didn't
see anything in there that would indicate the cause of our issue. The only
thing it made me question was some of the table with the sp's weren't fully
qualified.. ..but this has always been the case so it would be strange for
this to only just start causing a problem..
Again thanks for the responses.. ..any help is greatly appreciated
K
Unfortunately I can't analyse new traces, as currently our frontend is being
redirected..|||Is 736291420 an object id for a stored proc? It sounds like what MS calls
"rolling block". Does the blocking head spid(s) constantly changing?
Did you run profiler trace to see where the SP:Recompile event occurs and
what Event Subclass it falls into?
"Ben UK" <BenUK@.discussions.microsoft.com> wrote in message
news:6E5794CF-C439-4FC7-A0EE-C71478CF552A@.microsoft.com...
> We've recently been experiencing problems locking issues seemingly caused
> by
> compiles.. ..cpu max's out at 100%, query duration get longer, and we see
> a
> large number of LCK_M_X in sysprocesses with coupled with something like
> TAB:
> 5:736291420:0 [COMPILE].. ..what could be causing this issue?
> K|||Yep it's a sproc and the blocking head does constantly change.. ..what causes
this behaviour?
Thanks in advance
K
"YPD" wrote:
> Is 736291420 an object id for a stored proc? It sounds like what MS calls
> "rolling block". Does the blocking head spid(s) constantly changing?
> Did you run profiler trace to see where the SP:Recompile event occurs and
> what Event Subclass it falls into?
>
> "Ben UK" <BenUK@.discussions.microsoft.com> wrote in message
> news:6E5794CF-C439-4FC7-A0EE-C71478CF552A@.microsoft.com...
> >
> > We've recently been experiencing problems locking issues seemingly caused
> > by
> > compiles.. ..cpu max's out at 100%, query duration get longer, and we see
> > a
> > large number of LCK_M_X in sysprocesses with coupled with something like
> > TAB:
> > 5:736291420:0 [COMPILE].. ..what could be causing this issue?
> >
> > K
>
>|||The culprit for the performance problem should be stored procedure
recompilations. What happens is the sprocs are recompiled everytime they get
called. The recompliation doesn't happen in a timely fasion so that client
connections calling the sprocs have to be queued up waiting to be
recompiled.
Profiler is your friend. Please run a profiler trace to capture a series of
events to determine what caused the recomplications. The link
http://support.microsoft.com/kb/243586/ referenced in Kalen's post is a good
place to start.
"Ben UK" <BenUK@.discussions.microsoft.com> wrote in message
news:AA4556BF-995D-4201-B887-36C04FEB9E84@.microsoft.com...
> Yep it's a sproc and the blocking head does constantly change.. ..what
> causes
> this behaviour?
> Thanks in advance
> K
> "YPD" wrote:
>> Is 736291420 an object id for a stored proc? It sounds like what MS calls
>> "rolling block". Does the blocking head spid(s) constantly changing?
>> Did you run profiler trace to see where the SP:Recompile event occurs and
>> what Event Subclass it falls into?
>>
>> "Ben UK" <BenUK@.discussions.microsoft.com> wrote in message
>> news:6E5794CF-C439-4FC7-A0EE-C71478CF552A@.microsoft.com...
>> >
>> > We've recently been experiencing problems locking issues seemingly
>> > caused
>> > by
>> > compiles.. ..cpu max's out at 100%, query duration get longer, and we
>> > see
>> > a
>> > large number of LCK_M_X in sysprocesses with coupled with something
>> > like
>> > TAB:
>> > 5:736291420:0 [COMPILE].. ..what could be causing this issue?
>> >
>> > K
>>

Compile Blocking Issues

We've recently been experiencing problems locking issues seemingly caused by
compiles.. ..cpu max's out at 100%, query duration get longer, and we see a
large number of LCK_M_X in sysprocesses with coupled with something like TAB:
5:736291420:0 [COMPILE].. ..what could be causing this issue?
K
1) What is the table referenced in the lock?
2) It could be caused by lots of compiles? :-)) Seriously, do you do a
lot of sproc calls? Even worse, lots of badly-written ADO calls? Lots of
temptable useage (it is SCARY how many recompiles can be caused by this!!)?
SQL 2000 or 2005?
TheSQLGuru
President
Indicium Resources, Inc.
"Ben UK" <BenUK@.discussions.microsoft.com> wrote in message
news:6E5794CF-C439-4FC7-A0EE-C71478CF552A@.microsoft.com...
> We've recently been experiencing problems locking issues seemingly caused
> by
> compiles.. ..cpu max's out at 100%, query duration get longer, and we see
> a
> large number of LCK_M_X in sysprocesses with coupled with something like
> TAB:
> 5:736291420:0 [COMPILE].. ..what could be causing this issue?
> K
|||Have you read this:
"Description of SQL Server blocking caused by compile locks"
http://support.microsoft.com/kb/263889
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Ben UK" <BenUK@.discussions.microsoft.com> wrote in message
news:6E5794CF-C439-4FC7-A0EE-C71478CF552A@.microsoft.com...
> We've recently been experiencing problems locking issues seemingly caused
> by
> compiles.. ..cpu max's out at 100%, query duration get longer, and we see
> a
> large number of LCK_M_X in sysprocesses with coupled with something like
> TAB:
> 5:736291420:0 [COMPILE].. ..what could be causing this issue?
> K
|||On Jun 13, 11:49 am, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
> 1) What is the table referenced in the lock?
> 2) It could be caused by lots of compiles? :-)) Seriously, do you do a
> lot of sproc calls? Even worse, lots of badly-written ADO calls? Lots of
> temptable useage (it is SCARY how many recompiles can be caused by this!!)?
> SQL 2000 or 2005?
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Ben UK" <B...@.discussions.microsoft.com> wrote in message
> news:6E5794CF-C439-4FC7-A0EE-C71478CF552A@.microsoft.com...
>
>
>
> - Show quoted text -
I've observed this behavior with sprocs that are called very often and
make use of temp tables. As data is inserted into the temp table one
or more sp-recompile events will happen. If many concurrent spids are
calling this sproc at the same time, SQL appears to allow only one
spid at a time to perform the recompile - this will reduce the level
of concurrency in the system and you will see blocking spids that are
marked with [COMPILE]. Try to use profiler to trace stored procedure
recompiles, and RPC:Completed events to identify what is being
recomplied, then ideally try to tune the code to reduce recompiles.
|||Thanks for the responses, we're SQL 2005, SP1, the objects referenced in the
lock are primarily 2 sp's and 1 udf.. ..when querying sysprocesses we can see
around 30 occurances of this lock from around 600 connections.
The problems *seem*to have started since we changed the schema and removed a
table containing denormalized data and replaced it with a view. Both the
sp's that have compile issues reference the new view (as do around 50-60
more), the udf doesn't reference any new tables... ...the udf uses a table
variable, but neither sp uses temp tables of any kind.
http://support.microsoft.com/kb/263889
^ I did read the article earlier today.. ..it was kinda useful, but I didn't
see anything in there that would indicate the cause of our issue. The only
thing it made me question was some of the table with the sp's weren't fully
qualified.. ..but this has always been the case so it would be strange for
this to only just start causing a problem..
Again thanks for the responses.. ..any help is greatly appreciated
K
Unfortunately I can't analyse new traces, as currently our frontend is being
redirected..
|||Is 736291420 an object id for a stored proc? It sounds like what MS calls
"rolling block". Does the blocking head spid(s) constantly changing?
Did you run profiler trace to see where the SP:Recompile event occurs and
what Event Subclass it falls into?
"Ben UK" <BenUK@.discussions.microsoft.com> wrote in message
news:6E5794CF-C439-4FC7-A0EE-C71478CF552A@.microsoft.com...
> We've recently been experiencing problems locking issues seemingly caused
> by
> compiles.. ..cpu max's out at 100%, query duration get longer, and we see
> a
> large number of LCK_M_X in sysprocesses with coupled with something like
> TAB:
> 5:736291420:0 [COMPILE].. ..what could be causing this issue?
> K
|||Yep it's a sproc and the blocking head does constantly change.. ..what causes
this behaviour?
Thanks in advance
K
"YPD" wrote:

> Is 736291420 an object id for a stored proc? It sounds like what MS calls
> "rolling block". Does the blocking head spid(s) constantly changing?
> Did you run profiler trace to see where the SP:Recompile event occurs and
> what Event Subclass it falls into?
>
> "Ben UK" <BenUK@.discussions.microsoft.com> wrote in message
> news:6E5794CF-C439-4FC7-A0EE-C71478CF552A@.microsoft.com...
>
>
|||The culprit for the performance problem should be stored procedure
recompilations. What happens is the sprocs are recompiled everytime they get
called. The recompliation doesn't happen in a timely fasion so that client
connections calling the sprocs have to be queued up waiting to be
recompiled.
Profiler is your friend. Please run a profiler trace to capture a series of
events to determine what caused the recomplications. The link
http://support.microsoft.com/kb/243586/ referenced in Kalen's post is a good
place to start.
"Ben UK" <BenUK@.discussions.microsoft.com> wrote in message
news:AA4556BF-995D-4201-B887-36C04FEB9E84@.microsoft.com...[vbcol=seagreen]
> Yep it's a sproc and the blocking head does constantly change.. ..what
> causes
> this behaviour?
> Thanks in advance
> K
> "YPD" wrote:

Compile Blocking Issues

We've recently been experiencing problems locking issues seemingly caused by
compiles.. ..cpu max's out at 100%, query duration get longer, and we see a
large number of LCK_M_X in sysprocesses with coupled with something like TAB
:
5:736291420:0 [COMPILE].. ..what could be causing this issue?
K1) What is the table referenced in the lock?
2) It could be caused by lots of compiles' :-)) Seriously, do you do a
lot of sproc calls? Even worse, lots of badly-written ADO calls? Lots of
temptable useage (it is SCARY how many recompiles can be caused by this!!)?
SQL 2000 or 2005'
TheSQLGuru
President
Indicium Resources, Inc.
"Ben UK" <BenUK@.discussions.microsoft.com> wrote in message
news:6E5794CF-C439-4FC7-A0EE-C71478CF552A@.microsoft.com...
> We've recently been experiencing problems locking issues seemingly caused
> by
> compiles.. ..cpu max's out at 100%, query duration get longer, and we see
> a
> large number of LCK_M_X in sysprocesses with coupled with something like
> TAB:
> 5:736291420:0 [COMPILE].. ..what could be causing this issue?
> K|||Have you read this:
"Description of SQL Server blocking caused by compile locks"
http://support.microsoft.com/kb/263889
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Ben UK" <BenUK@.discussions.microsoft.com> wrote in message
news:6E5794CF-C439-4FC7-A0EE-C71478CF552A@.microsoft.com...
> We've recently been experiencing problems locking issues seemingly caused
> by
> compiles.. ..cpu max's out at 100%, query duration get longer, and we see
> a
> large number of LCK_M_X in sysprocesses with coupled with something like
> TAB:
> 5:736291420:0 [COMPILE].. ..what could be causing this issue?
> K|||On Jun 13, 11:49 am, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
> 1) What is the table referenced in the lock?
> 2) It could be caused by lots of compiles' :-)) Seriously, do you do a
> lot of sproc calls? Even worse, lots of badly-written ADO calls? Lots of
> temptable useage (it is SCARY how many recompiles can be caused by this!!)
?
> SQL 2000 or 2005'
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Ben UK" <B...@.discussions.microsoft.com> wrote in message
> news:6E5794CF-C439-4FC7-A0EE-C71478CF552A@.microsoft.com...
>
>
>
>
> - Show quoted text -
I've observed this behavior with sprocs that are called very often and
make use of temp tables. As data is inserted into the temp table one
or more sp-recompile events will happen. If many concurrent spids are
calling this sproc at the same time, SQL appears to allow only one
spid at a time to perform the recompile - this will reduce the level
of concurrency in the system and you will see blocking spids that are
marked with [COMPILE]. Try to use profiler to trace stored procedure
recompiles, and RPC:Completed events to identify what is being
recomplied, then ideally try to tune the code to reduce recompiles.|||Thanks for the responses, we're SQL 2005, SP1, the objects referenced in the
lock are primarily 2 sp's and 1 udf.. ..when querying sysprocesses we can se
e
around 30 occurances of this lock from around 600 connections.
The problems *seem*to have started since we changed the schema and removed a
table containing denormalized data and replaced it with a view. Both the
sp's that have compile issues reference the new view (as do around 50-60
more), the udf doesn't reference any new tables... ...the udf uses a table
variable, but neither sp uses temp tables of any kind.
http://support.microsoft.com/kb/263889
^ I did read the article earlier today.. ..it was kinda useful, but I didn't
see anything in there that would indicate the cause of our issue. The only
thing it made me question was some of the table with the sp's weren't fully
qualified.. ..but this has always been the case so it would be strange for
this to only just start causing a problem..
Again thanks for the responses.. ..any help is greatly appreciated
K
Unfortunately I can't analyse new traces, as currently our frontend is being
redirected..|||Is 736291420 an object id for a stored proc? It sounds like what MS calls
"rolling block". Does the blocking head spid(s) constantly changing?
Did you run profiler trace to see where the SP:Recompile event occurs and
what Event Subclass it falls into?
"Ben UK" <BenUK@.discussions.microsoft.com> wrote in message
news:6E5794CF-C439-4FC7-A0EE-C71478CF552A@.microsoft.com...
> We've recently been experiencing problems locking issues seemingly caused
> by
> compiles.. ..cpu max's out at 100%, query duration get longer, and we see
> a
> large number of LCK_M_X in sysprocesses with coupled with something like
> TAB:
> 5:736291420:0 [COMPILE].. ..what could be causing this issue?
> K|||Yep it's a sproc and the blocking head does constantly change.. ..what cause
s
this behaviour?
Thanks in advance
K
"YPD" wrote:

> Is 736291420 an object id for a stored proc? It sounds like what MS calls
> "rolling block". Does the blocking head spid(s) constantly changing?
> Did you run profiler trace to see where the SP:Recompile event occurs and
> what Event Subclass it falls into?
>
> "Ben UK" <BenUK@.discussions.microsoft.com> wrote in message
> news:6E5794CF-C439-4FC7-A0EE-C71478CF552A@.microsoft.com...
>
>|||The culprit for the performance problem should be stored procedure
recompilations. What happens is the sprocs are recompiled everytime they get
called. The recompliation doesn't happen in a timely fasion so that client
connections calling the sprocs have to be queued up waiting to be
recompiled.
Profiler is your friend. Please run a profiler trace to capture a series of
events to determine what caused the recomplications. The link
http://support.microsoft.com/kb/243586/ referenced in Kalen's post is a good
place to start.
"Ben UK" <BenUK@.discussions.microsoft.com> wrote in message
news:AA4556BF-995D-4201-B887-36C04FEB9E84@.microsoft.com...[vbcol=seagreen]
> Yep it's a sproc and the blocking head does constantly change.. ..what
> causes
> this behaviour?
> Thanks in advance
> K
> "YPD" wrote:
>

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)))

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.

|||

Okay let me be bit specific what I am trying to achieve.--will also explain what i mean by "recompile"

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

|||

If you know the pattern you are looking for, like =* or *=, you could use a search to find these cases:

--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.

|||

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)))

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 [really link] question

Hi,
I'm having trouble linking a simple example using the bcp functions...
The error:
************************************************
test.obj : error LNK2001: unresolved external symbol _bcp_init@.20
************************************************
Environment: Windows 2000, SQL Server 2000, VS C++ 6.0 Enterprise
I know my environment and connection handles are working...but my bcp_init()
call is driving me crazy...
Headers I'm including:
////////////////////////////////
//C HEADERS
#include <stdio.h>
#include <stdlib.h>
//WINDOWS
#include <windows.h>
//ODBC HEADERS
#include <Sql.h>
#include <Sqlext.h>
#include <Sqltypes.h>
//ODBC INSTALLER
#include <Odbcinst.h>
//BCP HEADER
#include <Odbcss.h>
////////////////////////////////
Library I'm including
Odbcbcp.lib
I use both
a) /libpath:"C:\Program Files\Microsoft SQL Server\80\Tools\DevTools\Lib"
and
b) LIB environmental variable points there too
I've checked the folder and the file is def. there!
Thinking I might not be using the ODBC 3.0 headers and lib...
I've check my LIB path and "Odbcbcp.lib" only exists once!
If I comment out the bcp_init() call, my program compiles and links fine
The call to bcp_exec() is FINE...
Anyone have a clue as to what I'm doing wrong?
Included below is my c file (test.c) pasted just in case I'm doing something
else wrong.
// START test.c
#include <stdio.h>
#include <stdlib.h>
#include <windows.h>
#include <Sql.h>
#include <Sqlext.h>
#include <Sqltypes.h>
//ODBC INSTALLER
#include <Odbcinst.h>
#include <Odbcss.h>
#define MAXBUFLEN 255
#define SQLSERVER1
#defineDSN2
int createHandles();
void destroyHandles();
SQLHENV henv = SQL_NULL_HENV;//Environment Handle
SQLHDBC hcn = SQL_NULL_HDBC;//Connection Handle
main()
{
intret;//internal return codes
RETCODEretcode;//ODBC return codes
UCHARszDSN[SQL_MAX_DSN_LENGTH+1] = "cnABL",//DSN info
szDSN_UID[MAXNAME] = "sa",
szDSN_PSWD[MAXNAME] = "";
SQLCHARconnectionStringIn[MAXBUFLEN] = "";//SQL SERVER driver
connection string
SQLCHAR connectionStringOut[MAXBUFLEN];//SQL SERVER driver
connection string out
SQLSMALLINT lengthOfConnectionStringOut = 0;
// Bulk copy variables.
SDWORD cRows;
intmyType = DSN;
//Create Handles
if ( ( ret = createHandles() ) )
{
destroyHandles();
returnEXIT_FAILURE;
}
switch (myType)
{
case SQLSERVER:
//Build up the ConnStrIn...
sprintf(connectionStringIn, "DRIVER={SQL
Server};SERVER=%s;UID=%s;PWD=%s;DATABASE=%s;", "", "sa", "", "abldb");
printf("\nCONNECTION STRING->%s<-", connectionStringIn);
//connect
retcode = SQLDriverConnect(hcn,// Connection handle
NULL,// Window handle
connectionStringIn,// Input connect string
SQL_NTS,// Null-terminated string
connectionStringOut,// Address of output buffer
MAXBUFLEN,// Size of output buffer
&lengthOfConnectionStringOut,// Address of output length
SQL_DRIVER_NOPROMPT);
//check it
if (retcode == SQL_ERROR)
//if ( (retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO) )
{
// Connect failed, call SQLGetDiagRec for errors.
printf("\nSQL SERVER CONNECTION FAILED!");
destroyHandles();
return EXIT_FAILURE;
}
else
{
// Connects to SQL Server always return
// informational messages. These messages can be
// retrieved by calling SQLGetDiagRec.
printf("\nSQL SERVER CONNECTION SUCCESS!");
}
break;
case DSN:
//connect
retcode = SQLConnect(hcn, szDSN, (SWORD)strlen(szDSN), szDSN_UID,
(SWORD)strlen(szDSN_UID), szDSN_PSWD, (SWORD)strlen(szDSN_PSWD));
//check it
if ( (retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO) )
{
// Connect failed, call SQLGetDiagRec for errors.
printf("\nDSN CONNECTION FAILED!");
destroyHandles();
return EXIT_FAILURE;
}
else
{
// Connects to SQL Server always return
// informational messages. These messages can be
// retrieved by calling SQLGetDiagRec.
printf("\nDSN CONNECTION SUCCESS!");
}
break;
}
// Initialize the bulk copy.
//retcode = bcp_init(hcn, "abldb..trendtable", "c:\\ben\\BCPODBC.bcp",
"c:\\ben\\BCPERROR.out", DB_OUT);
// Note that the test is for the bulk copy return of SUCCEED,
// not the ODBC return of SQL_SUCCESS.
if ( (retcode != SUCCEED) )
{
printf("bcp_init(hcn) Failed\n\n");
destroyHandles();
return EXIT_FAILURE;
}
// Execute the bulk copy.
retcode = bcp_exec(hcn, &cRows);
if ( (retcode != SUCCEED) )
{
printf("bcp_exec(hcn) Failed\n\n");
destroyHandles();
return EXIT_FAILURE;
}
printf("Number of rows bulk copied out = %d.\n", cRows);
//clean up
destroyHandles();
return EXIT_SUCCESS;
}
int createHandles()
{
RETCODE retcode;
// Allocate the ODBC Environment and save handle.
retcode = SQLAllocHandle (SQL_HANDLE_ENV,
SQL_NULL_HANDLE,
&henv);
if ( (retcode != SQL_SUCCESS) && (retcode == SQL_SUCCESS_WITH_INFO))
return 0;
// Notify ODBC that this is an ODBC 3.0 application.
retcode = SQLSetEnvAttr(henv,
SQL_ATTR_ODBC_VERSION,
(SQLPOINTER)SQL_OV_ODBC3,
SQL_IS_INTEGER);
if ( (retcode != SQL_SUCCESS) && (retcode == SQL_SUCCESS_WITH_INFO))
return 0;
// Allocate an ODBC connection handle
retcode = SQLAllocHandle(SQL_HANDLE_DBC,
henv,
&hcn);
if ( (retcode != SQL_SUCCESS) && (retcode == SQL_SUCCESS_WITH_INFO))
return 0;
return 1;
}
void destroyHandles()
{
SQLDisconnect(hcn);
SQLFreeHandle(SQL_HANDLE_DBC, hcn);
SQLFreeHandle(SQL_HANDLE_ENV, henv);
}
// END test.c
thanks in advance,
georgejetson
Nevermind...
Although I had my environmental variables pointing to the more recent dirs
first, it was not working...I'm still learning ms vc dev environment...
FYI: To compile ODBC using BCP in VC6 Enterprise...
My Solution:
a) Downloaded latest and greatest mdac sdk
b) Added mdac lib & include dirs to LIB & INCLUDE environmental variable
list at front of list
c) For all configurations (Release/Active...) in VC6
1) Made sure all required libs existed
Odbc32.lib Odbccp32.lib Odbcbcp.lib
(probably don't need Odbccp32.lib)
2) Set Additional Library Path to FIRST use new mdac/lib folder, then
sqlserver/lib folder
ie:C:\mdacSdk28\Libs\x86,C:\Program Files\Microsoft SQL
Server\80\Tools\DevTools\Lib
d)Did the same thing for additional include directories
ie:C:\mdacSdk28\Inc,C:\Program Files\Microsoft SQL
Server\80\Tools\DevTools\Include
In my source code, I included the following...
//ALL ODBC CALLS NEED
#include <Sql.h>
#include <Sqlext.h>
#include <Sqltypes.h>
//ODBC INSTALLER
#include <Odbcinst.h>
//ODBC UNICODE
#include <Sqlucode.h>
//ODBC BCP
#include <Odbcss.h>
e) Finally, I took OUT the /nologo option so I could see where the compiler
and linker were really looking
the result below
Compiling...
Microsoft (R) 32-bit C/C++ Optimizing Compiler Version 12.00.8168 for 80x86
Copyright (C) Microsoft Corp 1984-1998. All rights reserved.
cl /MLd /W3 /Gm /GX /ZI /Od /I "C:\mdacSdk28\Inc" /I "C:\Program
Files\Microsoft SQL Server\80\Tools\DevTools\Include" /D "WIN32" /D "_DEBUG"
/D "_CONSOLE" /D "_MBCS" /FR"Debug/" /Fp"Debug/prjOdbcTest.pch" /YX
/Fo"Debug/" /Fd"Debug/" /FD /GZ /c
"C:\sqlutil\odbcTest\test.c"
test.c
Note: Using precompiled header
Linking...
Creating browse info file...
prjOdbcTest.exe - 0 error(s), 0 warning(s)
thanks in advance,
georgejetson

compile [really link] question

Hi,
I'm having trouble linking a simple example using the bcp functions...
The error:
****************************************
********
test.obj : error LNK2001: unresolved external symbol _bcp_init@.20
****************************************
********
Environment: Windows 2000, SQL Server 2000, VS C++ 6.0 Enterprise
I know my environment and connection handles are working...but my bcp_init()
call is driving me crazy...
Headers I'm including:
////////////////////////////////
//C HEADERS
#include <stdio.h>
#include <stdlib.h>
//WINDOWS
#include <windows.h>
//ODBC HEADERS
#include <Sql.h>
#include <Sqlext.h>
#include <Sqltypes.h>
//ODBC INSTALLER
#include <Odbcinst.h>
//BCP HEADER
#include <Odbcss.h>
////////////////////////////////
Library I'm including
Odbcbcp.lib
I use both
a) /libpath:"C:\Program Files\Microsoft SQL Server\80\Tools\DevTools\Lib"
and
b) LIB environmental variable points there too
I've checked the folder and the file is def. there!
Thinking I might not be using the ODBC 3.0 headers and lib...
I've check my LIB path and "Odbcbcp.lib" only exists once!
If I comment out the bcp_init() call, my program compiles and links fine
The call to bcp_exec() is FINE...
Anyone have a clue as to what I'm doing wrong?
Included below is my c file (test.c) pasted just in case I'm doing something
else wrong.
// START test.c
#include <stdio.h>
#include <stdlib.h>
#include <windows.h>
#include <Sql.h>
#include <Sqlext.h>
#include <Sqltypes.h>
//ODBC INSTALLER
#include <Odbcinst.h>
#include <Odbcss.h>
#define MAXBUFLEN 255
#define SQLSERVER 1
#define DSN 2
int createHandles();
void destroyHandles();
SQLHENV henv = SQL_NULL_HENV; //Environment Handle
SQLHDBC hcn = SQL_NULL_HDBC; //Connection Handle
main()
{
int ret; // internal return codes
RETCODE retcode; // ODBC return codes
UCHAR szDSN[SQL_MAX_DSN_LENGTH+1] = "cnABL", //DSN info
szDSN_UID[MAXNAME] = "sa",
szDSN_PSWD[MAXNAME] = "";
SQLCHAR connectionStringIn[MAXBUFLEN] = ""; //SQL SERVER driver
connection string
SQLCHAR connectionStringOut[MAXBUFLEN]; //SQL SERVER driver
connection string out
SQLSMALLINT lengthOfConnectionStringOut = 0;
// Bulk copy variables.
SDWORD cRows;
int myType = DSN;
//Create Handles
if ( ( ret = createHandles() ) )
{
destroyHandles();
return EXIT_FAILURE;
}
switch (myType)
{
case SQLSERVER:
//Build up the ConnStrIn...
sprintf(connectionStringIn, "DRIVER={SQL
Server};SERVER=%s;UID=%s;PWD=%s;DATABASE
=%s;", "", "sa", "", "abldb");
printf("\nCONNECTION STRING->%s<-", connectionStringIn);
//connect
retcode = SQLDriverConnect( hcn, // Connection handle
NULL, // Window handle
connectionStringIn, // Input connect string
SQL_NTS, // Null-terminated string
connectionStringOut, // Address of output buffer
MAXBUFLEN, // Size of output buffer
&lengthOfConnectionStringOut, // Address of output length
SQL_DRIVER_NOPROMPT);
//check it
if (retcode == SQL_ERROR)
//if ( (retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO) )
{
// Connect failed, call SQLGetDiagRec for errors.
printf("\nSQL SERVER CONNECTION FAILED!");
destroyHandles();
return EXIT_FAILURE;
}
else
{
// Connects to SQL Server always return
// informational messages. These messages can be
// retrieved by calling SQLGetDiagRec.
printf("\nSQL SERVER CONNECTION SUCCESS!");
}
break;
case DSN:
//connect
retcode = SQLConnect(hcn, szDSN, (SWORD)strlen(szDSN), szDSN_UID,
(SWORD)strlen(szDSN_UID), szDSN_PSWD, (SWORD)strlen(szDSN_PSWD));
//check it
if ( (retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO) )
{
// Connect failed, call SQLGetDiagRec for errors.
printf("\nDSN CONNECTION FAILED!");
destroyHandles();
return EXIT_FAILURE;
}
else
{
// Connects to SQL Server always return
// informational messages. These messages can be
// retrieved by calling SQLGetDiagRec.
printf("\nDSN CONNECTION SUCCESS!");
}
break;
}
// Initialize the bulk copy.
//retcode = bcp_init(hcn, "abldb..trendtable", "c:\\ben\\BCPODBC.bcp",
"c:\\ben\\BCPERROR.out", DB_OUT);
// Note that the test is for the bulk copy return of SUCCEED,
// not the ODBC return of SQL_SUCCESS.
if ( (retcode != SUCCEED) )
{
printf("bcp_init(hcn) Failed\n\n");
destroyHandles();
return EXIT_FAILURE;
}
// Execute the bulk copy.
retcode = bcp_exec(hcn, &cRows);
if ( (retcode != SUCCEED) )
{
printf("bcp_exec(hcn) Failed\n\n");
destroyHandles();
return EXIT_FAILURE;
}
printf("Number of rows bulk copied out = %d.\n", cRows);
//clean up
destroyHandles();
return EXIT_SUCCESS;
}
int createHandles()
{
RETCODE retcode;
// Allocate the ODBC Environment and save handle.
retcode = SQLAllocHandle ( SQL_HANDLE_ENV,
SQL_NULL_HANDLE,
&henv);
if ( (retcode != SQL_SUCCESS) && (retcode == SQL_SUCCESS_WITH_INFO))
return 0;
// Notify ODBC that this is an ODBC 3.0 application.
retcode = SQLSetEnvAttr( henv,
SQL_ATTR_ODBC_VERSION,
(SQLPOINTER)SQL_OV_ODBC3,
SQL_IS_INTEGER);
if ( (retcode != SQL_SUCCESS) && (retcode == SQL_SUCCESS_WITH_INFO))
return 0;
// Allocate an ODBC connection handle
retcode = SQLAllocHandle( SQL_HANDLE_DBC,
henv,
&hcn);
if ( (retcode != SQL_SUCCESS) && (retcode == SQL_SUCCESS_WITH_INFO))
return 0;
return 1;
}
void destroyHandles()
{
SQLDisconnect(hcn);
SQLFreeHandle(SQL_HANDLE_DBC, hcn);
SQLFreeHandle(SQL_HANDLE_ENV, henv);
}
// END test.c
thanks in advance,
georgejetsonNevermind...
Although I had my environmental variables pointing to the more recent dirs
first, it was not working...I'm still learning ms vc dev environment...
FYI: To compile ODBC using BCP in VC6 Enterprise...
My Solution:
a) Downloaded latest and greatest mdac sdk
b) Added mdac lib & include dirs to LIB & INCLUDE environmental variable
list at front of list
c) For all configurations (Release/Active...) in VC6
1) Made sure all required libs existed
Odbc32.lib Odbccp32.lib Odbcbcp.lib
(probably don't need Odbccp32.lib)
2) Set Additional Library Path to FIRST use new mdac/lib folder, then
sqlserver/lib folder
ie:C:\mdacSdk28\Libs\x86,C:\Program Files\Microsoft SQL
Server\80\Tools\DevTools\Lib
d)Did the same thing for additional include directories
ie:C:\mdacSdk28\Inc,C:\Program Files\Microsoft SQL
Server\80\Tools\DevTools\Include
In my source code, I included the following...
//ALL ODBC CALLS NEED
#include <Sql.h>
#include <Sqlext.h>
#include <Sqltypes.h>
//ODBC INSTALLER
#include <Odbcinst.h>
//ODBC UNICODE
#include <Sqlucode.h>
//ODBC BCP
#include <Odbcss.h>
e) Finally, I took OUT the /nologo option so I could see where the compiler
and linker were really looking
the result below
--
Compiling...
Microsoft (R) 32-bit C/C++ Optimizing Compiler Version 12.00.8168 for 80x86
Copyright (C) Microsoft Corp 1984-1998. All rights reserved.
cl /MLd /W3 /Gm /GX /ZI /Od /I "C:\mdacSdk28\Inc" /I "C:\Program
Files\Microsoft SQL Server\80\Tools\DevTools\Include" /D "WIN32" /D "_DEBUG"
/D "_CONSOLE" /D "_MBCS" /FR"Debug/" /Fp"Debug/prjOdbcTest.pch" /YX
/Fo"Debug/" /Fd"Debug/" /FD /GZ /c
"C:\sqlutil\odbcTest\test.c"
test.c
Note: Using precompiled header
Linking...
Creating browse info file...
prjOdbcTest.exe - 0 error(s), 0 warning(s)
--
thanks in advance,
georgejetson

compile

Anyone have an idea why a program that compiles in
developer will give a 'not responding' message when being
compiled to create an executable?Does this have anything to do with SQL Server? I suspect you posted to the
wrong newsgroup...
Sincerely,
Stephen Dybing
This posting is provided "AS IS" with no warranties, and confers no rights.
"Syl" <clemnsyl@.netscape.net> wrote in message
news:f58801c43dcc$89775b60$a501280a@.phx.gbl...
> Anyone have an idea why a program that compiles in
> developer will give a 'not responding' message when being
> compiled to create an executable?

compile

Anyone have an idea why a program that compiles in
developer will give a 'not responding' message when being
compiled to create an executable?
Does this have anything to do with SQL Server? I suspect you posted to the
wrong newsgroup...
Sincerely,
Stephen Dybing
This posting is provided "AS IS" with no warranties, and confers no rights.
"Syl" <clemnsyl@.netscape.net> wrote in message
news:f58801c43dcc$89775b60$a501280a@.phx.gbl...
> Anyone have an idea why a program that compiles in
> developer will give a 'not responding' message when being
> compiled to create an executable?

Compilations vs recompilations/sec

I see high number of compilations/sec and not recompilations/sec ? Whats the
difference between the two.. in other words when does it compile vs when
does it recompile ? I could not get a good feel about this even after
skimming through this article
http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
In addition, it states "Note in particular that the query plans for the
batch need not have been cached. Indeed, some types of batches are never
cached, but can still cause recompilations. Take, for example, a batch that
contains a literal larger than 8 KB. Suppose that this batch creates a
temporary table, and then inserts 20 rows in that table. The insertion of
the seventh row will cause a recompilation, but because of the large
literal, the batch is not cached."
What does a literal mean ? Can someone give me the SQL for when it may
recompile in the above condition ? Also will this show as recompilation/sec
or compilation/sec in perfmon ?Are you having performance issues, or is this a general question?
"Hassan" <hassan@.hotmail.com> wrote in message
news:efT12rd4HHA.5212@.TK2MSFTNGP04.phx.gbl...
>I see high number of compilations/sec and not recompilations/sec ? Whats
>the difference between the two.. in other words when does it compile vs
>when does it recompile ? I could not get a good feel about this even after
>skimming through this article
> http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
> In addition, it states "Note in particular that the query plans for the
> batch need not have been cached. Indeed, some types of batches are never
> cached, but can still cause recompilations. Take, for example, a batch
> that contains a literal larger than 8 KB. Suppose that this batch creates
> a temporary table, and then inserts 20 rows in that table. The insertion
> of the seventh row will cause a recompilation, but because of the large
> literal, the batch is not cached."
> What does a literal mean ? Can someone give me the SQL for when it may
> recompile in the above condition ? Also will this show as
> recompilation/sec or compilation/sec in perfmon ?
>
>
>|||First, thanks for the link, it was good reading. However, I would hardly
consider it useful if you're only going to skim it.
Clearly, the queries on your system is either not getting cached, or plans
are timing out and being removed from the cache (I don't know those
specifics about SQL Server). But again I must ask why you are asking the
question in the first place.
The plan/procedure/query cache is part of the Query Optimizer and is quite
possibly the single most complicated portion of a database engine. It is
also an area that DBA's seldom have to muck with, with the exception of some
basic understanding.
So, I must ask why you are asking the question in the first place? Are you
having performance problems, or are you just curious?
Jay
"Hassan" <hassan@.hotmail.com> wrote in message
news:efT12rd4HHA.5212@.TK2MSFTNGP04.phx.gbl...
>I see high number of compilations/sec and not recompilations/sec ? Whats
>the difference between the two.. in other words when does it compile vs
>when does it recompile ? I could not get a good feel about this even after
>skimming through this article
> http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
> In addition, it states "Note in particular that the query plans for the
> batch need not have been cached. Indeed, some types of batches are never
> cached, but can still cause recompilations. Take, for example, a batch
> that contains a literal larger than 8 KB. Suppose that this batch creates
> a temporary table, and then inserts 20 rows in that table. The insertion
> of the seventh row will cause a recompilation, but because of the large
> literal, the batch is not cached."
> What does a literal mean ? Can someone give me the SQL for when it may
> recompile in the above condition ? Also will this show as
> recompilation/sec or compilation/sec in perfmon ?
>
>
>|||I am seeing high compilations/sec on one of our SQL Servers as high as 500
and zero recompilations/sec. No one is complaining yet, but curious to know
why compile vs recompile..
"JayKon" <spam@.nospam.org> wrote in message
news:uUkmBPe4HHA.2312@.TK2MSFTNGP06.phx.gbl...
> First, thanks for the link, it was good reading. However, I would hardly
> consider it useful if you're only going to skim it.
> Clearly, the queries on your system is either not getting cached, or plans
> are timing out and being removed from the cache (I don't know those
> specifics about SQL Server). But again I must ask why you are asking the
> question in the first place.
> The plan/procedure/query cache is part of the Query Optimizer and is quite
> possibly the single most complicated portion of a database engine. It is
> also an area that DBA's seldom have to muck with, with the exception of
> some basic understanding.
> So, I must ask why you are asking the question in the first place? Are you
> having performance problems, or are you just curious?
> Jay
>
> "Hassan" <hassan@.hotmail.com> wrote in message
> news:efT12rd4HHA.5212@.TK2MSFTNGP04.phx.gbl...
>>I see high number of compilations/sec and not recompilations/sec ? Whats
>>the difference between the two.. in other words when does it compile vs
>>when does it recompile ? I could not get a good feel about this even after
>>skimming through this article
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
>> In addition, it states "Note in particular that the query plans for the
>> batch need not have been cached. Indeed, some types of batches are never
>> cached, but can still cause recompilations. Take, for example, a batch
>> that contains a literal larger than 8 KB. Suppose that this batch creates
>> a temporary table, and then inserts 20 rows in that table. The insertion
>> of the seventh row will cause a recompilation, but because of the large
>> literal, the batch is not cached."
>> What does a literal mean ? Can someone give me the SQL for when it may
>> recompile in the above condition ? Also will this show as
>> recompilation/sec or compilation/sec in perfmon ?
>>
>>
>|||Well try not to "skim" next time since this article is very specific about
what recompilation is. But in a nut shell a recompile is when the plan is in
cache and it gets invalidated for one of the many reasons the article
explains so that the next time a user tries to use that plan it must be
recreated or recompiled. A compile is when it never was in cache to begin
with. If you have lots of compiles it means you have lots of adhoc queries
and sql server is either not caching them at all or you have so many that
they don't stay in cache long enough to be reused. I would read the article
several times in depth as it is one of the very best articles on cache
behavior out there.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Hassan" <hassan@.hotmail.com> wrote in message
news:efT12rd4HHA.5212@.TK2MSFTNGP04.phx.gbl...
>I see high number of compilations/sec and not recompilations/sec ? Whats
>the difference between the two.. in other words when does it compile vs
>when does it recompile ? I could not get a good feel about this even after
>skimming through this article
> http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
> In addition, it states "Note in particular that the query plans for the
> batch need not have been cached. Indeed, some types of batches are never
> cached, but can still cause recompilations. Take, for example, a batch
> that contains a literal larger than 8 KB. Suppose that this batch creates
> a temporary table, and then inserts 20 rows in that table. The insertion
> of the seventh row will cause a recompilation, but because of the large
> literal, the batch is not cached."
> What does a literal mean ? Can someone give me the SQL for when it may
> recompile in the above condition ? Also will this show as
> recompilation/sec or compilation/sec in perfmon ?
>
>
>|||Thanks Andrew.
What about this statement ? Can you help me here ?
In addition, it states "Note in particular that the query plans for the
batch need not have been cached. Indeed, some types of batches are never
cached, but can still cause recompilations. Take, for example, a batch that
contains a literal larger than 8 KB. Suppose that this batch creates a
temporary table, and then inserts 20 rows in that table. The insertion of
the seventh row will cause a recompilation, but because of the large
literal, the batch is not cached."
What does a literal mean ? can you give an example of a literal ?
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eOU1RDh4HHA.5740@.TK2MSFTNGP04.phx.gbl...
> Well try not to "skim" next time since this article is very specific about
> what recompilation is. But in a nut shell a recompile is when the plan is
> in cache and it gets invalidated for one of the many reasons the article
> explains so that the next time a user tries to use that plan it must be
> recreated or recompiled. A compile is when it never was in cache to begin
> with. If you have lots of compiles it means you have lots of adhoc queries
> and sql server is either not caching them at all or you have so many that
> they don't stay in cache long enough to be reused. I would read the
> article several times in depth as it is one of the very best articles on
> cache behavior out there.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Hassan" <hassan@.hotmail.com> wrote in message
> news:efT12rd4HHA.5212@.TK2MSFTNGP04.phx.gbl...
>>I see high number of compilations/sec and not recompilations/sec ? Whats
>>the difference between the two.. in other words when does it compile vs
>>when does it recompile ? I could not get a good feel about this even after
>>skimming through this article
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
>> In addition, it states "Note in particular that the query plans for the
>> batch need not have been cached. Indeed, some types of batches are never
>> cached, but can still cause recompilations. Take, for example, a batch
>> that contains a literal larger than 8 KB. Suppose that this batch creates
>> a temporary table, and then inserts 20 rows in that table. The insertion
>> of the seventh row will cause a recompilation, but because of the large
>> literal, the batch is not cached."
>> What does a literal mean ? Can someone give me the SQL for when it may
>> recompile in the above condition ? Also will this show as
>> recompilation/sec or compilation/sec in perfmon ?
>>
>>
>|||A literal is an actual value. I'm not going to give you an actual example
because I would have to type in more than 8000 characters!
But suppose you have a table with a column of varchar(max). A query like the
following would be an example of one with a literal longer than 8K:
UPDATE mytable
SET bigcolumn = 'Some very very long string that is longer than 8000
characters ....'
WHERE key_column = 42
The plan for the above query would not be cached.
Of course I could have made the column nvarchar(max) and then I would only
have to type in 4001 characters, but that is still to many for me to type
right now.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Hassan" <hassan@.hotmail.com> wrote in message
news:%23mYZNJh4HHA.5844@.TK2MSFTNGP02.phx.gbl...
> Thanks Andrew.
> What about this statement ? Can you help me here ?
> In addition, it states "Note in particular that the query plans for the
> batch need not have been cached. Indeed, some types of batches are never
> cached, but can still cause recompilations. Take, for example, a batch
> that
> contains a literal larger than 8 KB. Suppose that this batch creates a
> temporary table, and then inserts 20 rows in that table. The insertion of
> the seventh row will cause a recompilation, but because of the large
> literal, the batch is not cached."
> What does a literal mean ? can you give an example of a literal ?
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eOU1RDh4HHA.5740@.TK2MSFTNGP04.phx.gbl...
>> Well try not to "skim" next time since this article is very specific
>> about what recompilation is. But in a nut shell a recompile is when the
>> plan is in cache and it gets invalidated for one of the many reasons the
>> article explains so that the next time a user tries to use that plan it
>> must be recreated or recompiled. A compile is when it never was in cache
>> to begin with. If you have lots of compiles it means you have lots of
>> adhoc queries and sql server is either not caching them at all or you
>> have so many that they don't stay in cache long enough to be reused. I
>> would read the article several times in depth as it is one of the very
>> best articles on cache behavior out there.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Hassan" <hassan@.hotmail.com> wrote in message
>> news:efT12rd4HHA.5212@.TK2MSFTNGP04.phx.gbl...
>>I see high number of compilations/sec and not recompilations/sec ? Whats
>>the difference between the two.. in other words when does it compile vs
>>when does it recompile ? I could not get a good feel about this even
>>after skimming through this article
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
>> In addition, it states "Note in particular that the query plans for the
>> batch need not have been cached. Indeed, some types of batches are never
>> cached, but can still cause recompilations. Take, for example, a batch
>> that contains a literal larger than 8 KB. Suppose that this batch
>> creates a temporary table, and then inserts 20 rows in that table. The
>> insertion of the seventh row will cause a recompilation, but because of
>> the large literal, the batch is not cached."
>> What does a literal mean ? Can someone give me the SQL for when it may
>> recompile in the above condition ? Also will this show as
>> recompilation/sec or compilation/sec in perfmon ?
>>
>>
>>
>|||Kalen,
What about
UPDATE mytable
SET bigcolumn = 42
WHERE key_column = 'Some very very long string that is longer than 8000
characters ....'
Would the plan for this query not be cached too ?
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:u6sueOh4HHA.1208@.TK2MSFTNGP05.phx.gbl...
>A literal is an actual value. I'm not going to give you an actual example
>because I would have to type in more than 8000 characters!
> But suppose you have a table with a column of varchar(max). A query like
> the following would be an example of one with a literal longer than 8K:
> UPDATE mytable
> SET bigcolumn = 'Some very very long string that is longer than 8000
> characters ....'
> WHERE key_column = 42
> The plan for the above query would not be cached.
> Of course I could have made the column nvarchar(max) and then I would only
> have to type in 4001 characters, but that is still to many for me to type
> right now.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://sqlblog.com
>
> "Hassan" <hassan@.hotmail.com> wrote in message
> news:%23mYZNJh4HHA.5844@.TK2MSFTNGP02.phx.gbl...
>> Thanks Andrew.
>> What about this statement ? Can you help me here ?
>> In addition, it states "Note in particular that the query plans for the
>> batch need not have been cached. Indeed, some types of batches are never
>> cached, but can still cause recompilations. Take, for example, a batch
>> that
>> contains a literal larger than 8 KB. Suppose that this batch creates a
>> temporary table, and then inserts 20 rows in that table. The insertion of
>> the seventh row will cause a recompilation, but because of the large
>> literal, the batch is not cached."
>> What does a literal mean ? can you give an example of a literal ?
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:eOU1RDh4HHA.5740@.TK2MSFTNGP04.phx.gbl...
>> Well try not to "skim" next time since this article is very specific
>> about what recompilation is. But in a nut shell a recompile is when the
>> plan is in cache and it gets invalidated for one of the many reasons the
>> article explains so that the next time a user tries to use that plan it
>> must be recreated or recompiled. A compile is when it never was in cache
>> to begin with. If you have lots of compiles it means you have lots of
>> adhoc queries and sql server is either not caching them at all or you
>> have so many that they don't stay in cache long enough to be reused. I
>> would read the article several times in depth as it is one of the very
>> best articles on cache behavior out there.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Hassan" <hassan@.hotmail.com> wrote in message
>> news:efT12rd4HHA.5212@.TK2MSFTNGP04.phx.gbl...
>>I see high number of compilations/sec and not recompilations/sec ? Whats
>>the difference between the two.. in other words when does it compile vs
>>when does it recompile ? I could not get a good feel about this even
>>after skimming through this article
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
>> In addition, it states "Note in particular that the query plans for the
>> batch need not have been cached. Indeed, some types of batches are
>> never cached, but can still cause recompilations. Take, for example, a
>> batch that contains a literal larger than 8 KB. Suppose that this batch
>> creates a temporary table, and then inserts 20 rows in that table. The
>> insertion of the seventh row will cause a recompilation, but because of
>> the large literal, the batch is not cached."
>> What does a literal mean ? Can someone give me the SQL for when it may
>> recompile in the above condition ? Also will this show as
>> recompilation/sec or compilation/sec in perfmon ?
>>
>>
>>
>>
>|||No, this would give you an error, because key columns cannot be longer than
900 bytes.
But if the WHERE included a non-key column that was compared to a literal
longer than 8000 bytes, it is the same as the example I gave. A literal
anywhere in the query that is longer than 8000 bytes will keep the plan from
being cached.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Hassan" <hassan@.hotmail.com> wrote in message
news:u4qDLJi4HHA.3684@.TK2MSFTNGP02.phx.gbl...
> Kalen,
> What about
> UPDATE mytable
> SET bigcolumn = 42
> WHERE key_column = 'Some very very long string that is longer than 8000
> characters ....'
>
> Would the plan for this query not be cached too ?
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:u6sueOh4HHA.1208@.TK2MSFTNGP05.phx.gbl...
>>A literal is an actual value. I'm not going to give you an actual example
>>because I would have to type in more than 8000 characters!
>> But suppose you have a table with a column of varchar(max). A query like
>> the following would be an example of one with a literal longer than 8K:
>> UPDATE mytable
>> SET bigcolumn = 'Some very very long string that is longer than 8000
>> characters ....'
>> WHERE key_column = 42
>> The plan for the above query would not be cached.
>> Of course I could have made the column nvarchar(max) and then I would
>> only have to type in 4001 characters, but that is still to many for me to
>> type right now.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://sqlblog.com
>>
>> "Hassan" <hassan@.hotmail.com> wrote in message
>> news:%23mYZNJh4HHA.5844@.TK2MSFTNGP02.phx.gbl...
>> Thanks Andrew.
>> What about this statement ? Can you help me here ?
>> In addition, it states "Note in particular that the query plans for the
>> batch need not have been cached. Indeed, some types of batches are never
>> cached, but can still cause recompilations. Take, for example, a batch
>> that
>> contains a literal larger than 8 KB. Suppose that this batch creates a
>> temporary table, and then inserts 20 rows in that table. The insertion
>> of
>> the seventh row will cause a recompilation, but because of the large
>> literal, the batch is not cached."
>> What does a literal mean ? can you give an example of a literal ?
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:eOU1RDh4HHA.5740@.TK2MSFTNGP04.phx.gbl...
>> Well try not to "skim" next time since this article is very specific
>> about what recompilation is. But in a nut shell a recompile is when the
>> plan is in cache and it gets invalidated for one of the many reasons
>> the article explains so that the next time a user tries to use that
>> plan it must be recreated or recompiled. A compile is when it never was
>> in cache to begin with. If you have lots of compiles it means you have
>> lots of adhoc queries and sql server is either not caching them at all
>> or you have so many that they don't stay in cache long enough to be
>> reused. I would read the article several times in depth as it is one
>> of the very best articles on cache behavior out there.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Hassan" <hassan@.hotmail.com> wrote in message
>> news:efT12rd4HHA.5212@.TK2MSFTNGP04.phx.gbl...
>>I see high number of compilations/sec and not recompilations/sec ?
>>Whats the difference between the two.. in other words when does it
>>compile vs when does it recompile ? I could not get a good feel about
>>this even after skimming through this article
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
>> In addition, it states "Note in particular that the query plans for
>> the batch need not have been cached. Indeed, some types of batches are
>> never cached, but can still cause recompilations. Take, for example, a
>> batch that contains a literal larger than 8 KB. Suppose that this
>> batch creates a temporary table, and then inserts 20 rows in that
>> table. The insertion of the seventh row will cause a recompilation,
>> but because of the large literal, the batch is not cached."
>> What does a literal mean ? Can someone give me the SQL for when it may
>> recompile in the above condition ? Also will this show as
>> recompilation/sec or compilation/sec in perfmon ?
>>
>>
>>
>>
>>
>

Sunday, February 19, 2012

Compile store procedures

Compile SQL Please Help

compile SP after creation?

compile SP after creation?

Friday, February 17, 2012

Compile Error: Redefinition of IRowsetBookmark (VS 2005)

Compile Error: Redefinition of IRowsetBookmark (VS 2005)

Compile Blocking Issues

Compile Blocking Issues

Compile Blocking Issues

compile a store proc again

compile a store proc again

compile [really link] question

compile [really link] question

compile

compile

Compilations vs recompilations/sec

Sql Comparing two tables

Blog Archive

About Me