EXEC Query Performance

I am running into a situation where a program runs a stored procedure, withi
n that stored procedure a SQL statement is built and then executed using the
EXEC command. It looks like that when the generated statement exceeds a ce
rtain time threshold, the s
tored procedure exits without any warning.
I know there is no warning because I have run profiler and I get a statement
start time but no end time. I have also logged every step in the stored pr
ocedure and it just bails on me. Any ideas?Funny you mention that Peter, I run the stored procedure in QA and it runs f
ine. The funny thing is that when the stored proc fails, the rest of the pro
cess completes. I have not been able to get access to the .Net code to view
how it is executed yet.
"Dan" wrote:

> I am running into a situation where a program runs a stored procedure, within that
stored procedure a SQL statement is built and then executed using the EXEC command.
It looks like that when the generated statement exceeds a certain time threshold,
the
stored procedure exits without any warning.
> I know there is no warning because I have run profiler and I get a statement start
time but no end time. I have also logged every step in the stored procedure and it
just bails on me. Any ideas?|||Your connection timeout setting is probably too low. Try adjusting it.
Andrew J. Kelly SQL MVP
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:DDE31E4A-8E88-4F76-90EE-6D04541D13C5@.microsoft.com...
> I am running into a situation where a program runs a stored procedure,
within that stored procedure a SQL statement is built and then executed
using the EXEC command. It looks like that when the generated statement
exceeds a certain time threshold, the stored procedure exits without any
warning.
> I know there is no warning because I have run profiler and I get a
statement start time but no end time. I have also logged every step in the
stored procedure and it just bails on me. Any ideas?|||Andrew, that is what I am thinking too. I have finally gotten a hold of the
code the developer uses to call this. Can any of you see anything wrong wi
th this code?
Dim cnTemp As Connection
Dim rsTemp As Recordset '--ADODB.Recordset
Dim strSQL As String
Set cnTemp = New ADODB.Connection
Set rsTemp = New ADODB.Recordset
cnTemp.ConnectionString = "Provider=SQLOLEDB;Data Source=PowerWare2000;Initi
al Catalog=PWProd; User ID=*******;Password=********;"
cnTemp.Open
strSQL = "Exec " & SP_CreatePrintJob & " " & JobTicketNr
rsTemp.CursorLocation = adUseClient
rsTemp.CursorType = adOpenStatic
rsTemp.LockType = adLockOptimistic
rsTemp.Open strSQL, cnTemp, , , adCmdText
"Andrew J. Kelly" wrote:

> Your connection timeout setting is probably too low. Try adjusting it.
> --
> Andrew J. Kelly SQL MVP
>
> "Dan" <Dan@.discussions.microsoft.com> wrote in message
> news:DDE31E4A-8E88-4F76-90EE-6D04541D13C5@.microsoft.com...
> within that stored procedure a SQL statement is built and then executed
> using the EXEC command. It looks like that when the generated statement
> exceeds a certain time threshold, the stored procedure exits without any
> warning.
> statement start time but no end time. I have also logged every step in th
e
> stored procedure and it just bails on me. Any ideas?
>
>|||You don't actually set the timeout and by default I believe it is set to 30
seconds. This should help with that:
http://www.aspfaq.com/show.asp?id=2066
But you also want to optimize these queries so they don't take so long to
begin with. Using stored procedures with parameters instead of adhoc sql is
a good start.
Andrew J. Kelly SQL MVP
"Daniel Avsec" <DanielAvsec@.discussions.microsoft.com> wrote in message
news:9F7A3F1B-3868-4B19-9A5F-63462C3D48A4@.microsoft.com...
> Andrew, that is what I am thinking too. I have finally gotten a hold of
the code the developer uses to call this. Can any of you see anything wrong
with this code?
> Dim cnTemp As Connection
> Dim rsTemp As Recordset '--ADODB.Recordset
> Dim strSQL As String
> Set cnTemp = New ADODB.Connection
> Set rsTemp = New ADODB.Recordset
> cnTemp.ConnectionString = "Provider=SQLOLEDB;Data
Source=PowerWare2000;Initial Catalog=PWProd; User
ID=*******;Password=********;"[vbcol=seagreen]
> cnTemp.Open
> strSQL = "Exec " & SP_CreatePrintJob & " " & JobTicketNr
> rsTemp.CursorLocation = adUseClient
> rsTemp.CursorType = adOpenStatic
> rsTemp.LockType = adLockOptimistic
> rsTemp.Open strSQL, cnTemp, , , adCmdText
> "Andrew J. Kelly" wrote:
>
the[vbcol=seagreen]sql

EXEC Query Performance

I am running into a situation where a program runs a stored procedure, within that stored procedure a SQL statement is built and then executed using the EXEC command. It looks like that when the generated statement exceeds a certain time threshold, the stored procedure exits without any warning.
I know there is no warning because I have run profiler and I get a statement start time but no end time. I have also logged every step in the stored procedure and it just bails on me. Any ideas?How are you executing the original SP, and have you tried
it in QA ?
>--Original Message--
>I am running into a situation where a program runs a
stored procedure, within that stored procedure a SQL
statement is built and then executed using the EXEC
command. It looks like that when the generated statement
exceeds a certain time threshold, the stored procedure
exits without any warning.
>I know there is no warning because I have run profiler
and I get a statement start time but no end time. I have
also logged every step in the stored procedure and it just
bails on me. Any ideas?
>.
>|||Your connection timeout setting is probably too low. Try adjusting it.
--
Andrew J. Kelly SQL MVP
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:DDE31E4A-8E88-4F76-90EE-6D04541D13C5@.microsoft.com...
> I am running into a situation where a program runs a stored procedure,
within that stored procedure a SQL statement is built and then executed
using the EXEC command. It looks like that when the generated statement
exceeds a certain time threshold, the stored procedure exits without any
warning.
> I know there is no warning because I have run profiler and I get a
statement start time but no end time. I have also logged every step in the
stored procedure and it just bails on me. Any ideas?|||You don't actually set the timeout and by default I believe it is set to 30
seconds. This should help with that:
http://www.aspfaq.com/show.asp?id=2066
But you also want to optimize these queries so they don't take so long to
begin with. Using stored procedures with parameters instead of adhoc sql is
a good start.
--
Andrew J. Kelly SQL MVP
"Daniel Avsec" <DanielAvsec@.discussions.microsoft.com> wrote in message
news:9F7A3F1B-3868-4B19-9A5F-63462C3D48A4@.microsoft.com...
> Andrew, that is what I am thinking too. I have finally gotten a hold of
the code the developer uses to call this. Can any of you see anything wrong
with this code?
> Dim cnTemp As Connection
> Dim rsTemp As Recordset '--ADODB.Recordset
> Dim strSQL As String
> Set cnTemp = New ADODB.Connection
> Set rsTemp = New ADODB.Recordset
> cnTemp.ConnectionString = "Provider=SQLOLEDB;Data
Source=PowerWare2000;Initial Catalog=PWProd; User
ID=*******;Password=********;"
> cnTemp.Open
> strSQL = "Exec " & SP_CreatePrintJob & " " & JobTicketNr
> rsTemp.CursorLocation = adUseClient
> rsTemp.CursorType = adOpenStatic
> rsTemp.LockType = adLockOptimistic
> rsTemp.Open strSQL, cnTemp, , , adCmdText
> "Andrew J. Kelly" wrote:
> > Your connection timeout setting is probably too low. Try adjusting it.
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "Dan" <Dan@.discussions.microsoft.com> wrote in message
> > news:DDE31E4A-8E88-4F76-90EE-6D04541D13C5@.microsoft.com...
> > > I am running into a situation where a program runs a stored procedure,
> > within that stored procedure a SQL statement is built and then executed
> > using the EXEC command. It looks like that when the generated statement
> > exceeds a certain time threshold, the stored procedure exits without any
> > warning.
> > >
> > > I know there is no warning because I have run profiler and I get a
> > statement start time but no end time. I have also logged every step in
the
> > stored procedure and it just bails on me. Any ideas?
> >
> >
> >

EXEC Query Performance

I am running into a situation where a program runs a stored procedure, within that stored procedure a SQL statement is built and then executed using the EXEC command. It looks like that when the generated statement exceeds a certain time threshold, the s
tored procedure exits without any warning.
I know there is no warning because I have run profiler and I get a statement start time but no end time. I have also logged every step in the stored procedure and it just bails on me. Any ideas?
Funny you mention that Peter, I run the stored procedure in QA and it runs fine. The funny thing is that when the stored proc fails, the rest of the process completes. I have not been able to get access to the .Net code to view how it is executed yet.
"Dan" wrote:

> I am running into a situation where a program runs a stored procedure, within that stored procedure a SQL statement is built and then executed using the EXEC command. It looks like that when the generated statement exceeds a certain time threshold, the
stored procedure exits without any warning.
> I know there is no warning because I have run profiler and I get a statement start time but no end time. I have also logged every step in the stored procedure and it just bails on me. Any ideas?
|||Your connection timeout setting is probably too low. Try adjusting it.
Andrew J. Kelly SQL MVP
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:DDE31E4A-8E88-4F76-90EE-6D04541D13C5@.microsoft.com...
> I am running into a situation where a program runs a stored procedure,
within that stored procedure a SQL statement is built and then executed
using the EXEC command. It looks like that when the generated statement
exceeds a certain time threshold, the stored procedure exits without any
warning.
> I know there is no warning because I have run profiler and I get a
statement start time but no end time. I have also logged every step in the
stored procedure and it just bails on me. Any ideas?
|||Andrew, that is what I am thinking too. I have finally gotten a hold of the code the developer uses to call this. Can any of you see anything wrong with this code?
Dim cnTemp As Connection
Dim rsTemp As Recordset '--ADODB.Recordset
Dim strSQL As String
Set cnTemp = New ADODB.Connection
Set rsTemp = New ADODB.Recordset
cnTemp.ConnectionString = "Provider=SQLOLEDB;Data Source=PowerWare2000;Initial Catalog=PWProd; User ID=*******;Password=********;"
cnTemp.Open
strSQL = "Exec " & SP_CreatePrintJob & " " & JobTicketNr
rsTemp.CursorLocation = adUseClient
rsTemp.CursorType = adOpenStatic
rsTemp.LockType = adLockOptimistic
rsTemp.Open strSQL, cnTemp, , , adCmdText
"Andrew J. Kelly" wrote:

> Your connection timeout setting is probably too low. Try adjusting it.
> --
> Andrew J. Kelly SQL MVP
>
> "Dan" <Dan@.discussions.microsoft.com> wrote in message
> news:DDE31E4A-8E88-4F76-90EE-6D04541D13C5@.microsoft.com...
> within that stored procedure a SQL statement is built and then executed
> using the EXEC command. It looks like that when the generated statement
> exceeds a certain time threshold, the stored procedure exits without any
> warning.
> statement start time but no end time. I have also logged every step in the
> stored procedure and it just bails on me. Any ideas?
>
>
|||You don't actually set the timeout and by default I believe it is set to 30
seconds. This should help with that:
http://www.aspfaq.com/show.asp?id=2066
But you also want to optimize these queries so they don't take so long to
begin with. Using stored procedures with parameters instead of adhoc sql is
a good start.
Andrew J. Kelly SQL MVP
"Daniel Avsec" <DanielAvsec@.discussions.microsoft.com> wrote in message
news:9F7A3F1B-3868-4B19-9A5F-63462C3D48A4@.microsoft.com...
> Andrew, that is what I am thinking too. I have finally gotten a hold of
the code the developer uses to call this. Can any of you see anything wrong
with this code?
> Dim cnTemp As Connection
> Dim rsTemp As Recordset '--ADODB.Recordset
> Dim strSQL As String
> Set cnTemp = New ADODB.Connection
> Set rsTemp = New ADODB.Recordset
> cnTemp.ConnectionString = "Provider=SQLOLEDB;Data
Source=PowerWare2000;Initial Catalog=PWProd; User
ID=*******;Password=********;"[vbcol=seagreen]
> cnTemp.Open
> strSQL = "Exec " & SP_CreatePrintJob & " " & JobTicketNr
> rsTemp.CursorLocation = adUseClient
> rsTemp.CursorType = adOpenStatic
> rsTemp.LockType = adLockOptimistic
> rsTemp.Open strSQL, cnTemp, , , adCmdText
> "Andrew J. Kelly" wrote:
the[vbcol=seagreen]

Excessive stored procedure [COMPILE] lock

Hello!
I am trying to investigate strange problem with particular stored
procedure. It runs OK for several days and suddenly we start getting and lot
of locks. The reason being [COMPILE] lock placed on this procedure. As a
result, we have 40-50 other connections waiting, then next connection using
this procedure has [COMPILE] lock etc. Client is fully qualifying stored
procedure by database/owner name and it doesn't start with sp_. I know
these are the reasons for [COMPILE] lock being placed. Is there something
else that might trigger this lock? When troubleshooting this issue, I
noticed there was no plan for this procedure in syscacheobjects. The stored
procedure is very simple (I know it could be rewritten/optimized but our
developer wrote it):
CREATE PROCEDURE [dbo].[vsp_mail_select]
@.user_id int,
@.folder_id int,
@.is_read bit = 1, --IF 1, pull everything, else just pull unread mail
@.start_index int = null, --unused for now, we return everything
@.total_count int = null output, -- count of all mail in specified folder
@.unread_count int = null output -- count of unread mail in specified folder
AS
SET NOCOUNT ON
select m1.* from mail m1(nolock) where m1.user_id=@.user_id and
folder_id=@.folder_id and ((@.is_read=0 and is_read=0) or (@.is_read=1)) order
by date_sent desc
select @.total_count = count(mail_id) from mail m1(nolock) where
m1.user_id=@.user_id and folder_id=@.folder_id and ((is_read=0 and @.is_read=0)
or (@.is_read=1))
select @.unread_count = count(mail_id) from mail m1(nolock) where
m1.user_id=@.user_id and folder_id=@.folder_id and is_read=0
GO
I was monitoring server for a couple of day before and I am not sure why
this happens every 3-4 days only!
Any help on this matter would be greately appreciated!
Thanks,
igor
This looks like a Parameter Sniffing problem to me.
take a look at this link:
http://www.microsoft.com/technet/pro...05/recomp.mspx
regards,
sarav...
"Igor Marchenko" <igormarchenko@.hotmail.com> wrote in message
news:OZRXncnWFHA.3464@.TK2MSFTNGP10.phx.gbl...
> Hello!
> I am trying to investigate strange problem with particular stored
> procedure. It runs OK for several days and suddenly we start getting and
> lot of locks. The reason being [COMPILE] lock placed on this procedure. As
> a result, we have 40-50 other connections waiting, then next connection
> using this procedure has [COMPILE] lock etc. Client is fully qualifying
> stored procedure by database/owner name and it doesn't start with sp_. I
> know these are the reasons for [COMPILE] lock being placed. Is there
> something else that might trigger this lock? When troubleshooting this
> issue, I noticed there was no plan for this procedure in syscacheobjects.
> The stored procedure is very simple (I know it could be
> rewritten/optimized but our developer wrote it):
>
> CREATE PROCEDURE [dbo].[vsp_mail_select]
> @.user_id int,
> @.folder_id int,
> @.is_read bit = 1, --IF 1, pull everything, else just pull unread mail
> @.start_index int = null, --unused for now, we return everything
> @.total_count int = null output, -- count of all mail in specified folder
> @.unread_count int = null output -- count of unread mail in specified
> folder
> AS
> SET NOCOUNT ON
> select m1.* from mail m1(nolock) where m1.user_id=@.user_id and
> folder_id=@.folder_id and ((@.is_read=0 and is_read=0) or (@.is_read=1))
> order by date_sent desc
> select @.total_count = count(mail_id) from mail m1(nolock) where
> m1.user_id=@.user_id and folder_id=@.folder_id and ((is_read=0 and
> @.is_read=0) or (@.is_read=1))
> select @.unread_count = count(mail_id) from mail m1(nolock) where
> m1.user_id=@.user_id and folder_id=@.folder_id and is_read=0
> GO
> I was monitoring server for a couple of day before and I am not sure why
> this happens every 3-4 days only!
> Any help on this matter would be greately appreciated!
> Thanks,
> igor
>
|||I think recompilations may be caused by modifcations done in base tables.
Developer confirmed that we have an hourly job running that can potentially
modify alot of data in base table. I am thinking of using
KEEPFIXED PLAN option to alleviate this problem.
Igor
"Sarav" <sarav@.sqlservertips.com> wrote in message
news:u8upDynWFHA.2420@.TK2MSFTNGP12.phx.gbl...
> This looks like a Parameter Sniffing problem to me.
> take a look at this link:
> http://www.microsoft.com/technet/pro...05/recomp.mspx
> regards,
> sarav...
> "Igor Marchenko" <igormarchenko@.hotmail.com> wrote in message
> news:OZRXncnWFHA.3464@.TK2MSFTNGP10.phx.gbl...
>

Excessive stored procedure [COMPILE] lock

Hello!
I am trying to investigate strange problem with particular stored
procedure. It runs OK for several days and suddenly we start getting and lot
of locks. The reason being [COMPILE] lock placed on this procedure. As a
result, we have 40-50 other connections waiting, then next connection using
this procedure has [COMPILE] lock etc. Client is fully qualifying stored
procedure by database/owner name and it doesn't start with sp_. I know
these are the reasons for [COMPILE] lock being placed. Is there somethin
g
else that might trigger this lock? When troubleshooting this issue, I
noticed there was no plan for this procedure in syscacheobjects. The stored
procedure is very simple (I know it could be rewritten/optimized but our
developer wrote it):
CREATE PROCEDURE [dbo].[vsp_mail_select]
@.user_id int,
@.folder_id int,
@.is_read bit = 1, --IF 1, pull everything, else just pull unread mail
@.start_index int = null, --unused for now, we return everything
@.total_count int = null output, -- count of all mail in specified folder
@.unread_count int = null output -- count of unread mail in specified folder
AS
SET NOCOUNT ON
select m1.* from mail m1(nolock) where m1.user_id=@.user_id and
folder_id=@.folder_id and ((@.is_read=0 and is_read=0) or (@.is_read=1)) order
by date_sent desc
select @.total_count = count(mail_id) from mail m1(nolock) where
m1.user_id=@.user_id and folder_id=@.folder_id and ((is_read=0 and @.is_read=0)
or (@.is_read=1))
select @.unread_count = count(mail_id) from mail m1(nolock) where
m1.user_id=@.user_id and folder_id=@.folder_id and is_read=0
GO
I was monitoring server for a couple of day before and I am not sure why
this happens every 3-4 days only!
Any help on this matter would be greately appreciated!
Thanks,
igorThis looks like a Parameter Sniffing problem to me.
take a look at this link:
http://www.microsoft.com/technet/pr...005/recomp.mspx
regards,
sarav...
"Igor Marchenko" <igormarchenko@.hotmail.com> wrote in message
news:OZRXncnWFHA.3464@.TK2MSFTNGP10.phx.gbl...
> Hello!
> I am trying to investigate strange problem with particular stored
> procedure. It runs OK for several days and suddenly we start getting and
> lot of locks. The reason being [COMPILE] lock placed on this procedure
. As
> a result, we have 40-50 other connections waiting, then next connection
> using this procedure has [COMPILE] lock etc. Client is fully qualifyin
g
> stored procedure by database/owner name and it doesn't start with sp_. I
> know these are the reasons for [COMPILE] lock being placed. Is there
> something else that might trigger this lock? When troubleshooting this
> issue, I noticed there was no plan for this procedure in syscacheobjects.
> The stored procedure is very simple (I know it could be
> rewritten/optimized but our developer wrote it):
>
> CREATE PROCEDURE [dbo].[vsp_mail_select]
> @.user_id int,
> @.folder_id int,
> @.is_read bit = 1, --IF 1, pull everything, else just pull unread mail
> @.start_index int = null, --unused for now, we return everything
> @.total_count int = null output, -- count of all mail in specified folder
> @.unread_count int = null output -- count of unread mail in specified
> folder
> AS
> SET NOCOUNT ON
> select m1.* from mail m1(nolock) where m1.user_id=@.user_id and
> folder_id=@.folder_id and ((@.is_read=0 and is_read=0) or (@.is_read=1))
> order by date_sent desc
> select @.total_count = count(mail_id) from mail m1(nolock) where
> m1.user_id=@.user_id and folder_id=@.folder_id and ((is_read=0 and
> @.is_read=0) or (@.is_read=1))
> select @.unread_count = count(mail_id) from mail m1(nolock) where
> m1.user_id=@.user_id and folder_id=@.folder_id and is_read=0
> GO
> I was monitoring server for a couple of day before and I am not sure why
> this happens every 3-4 days only!
> Any help on this matter would be greately appreciated!
> Thanks,
> igor
>|||I think recompilations may be caused by modifcations done in base tables.
Developer confirmed that we have an hourly job running that can potentially
modify alot of data in base table. I am thinking of using
KEEPFIXED PLAN option to alleviate this problem.
Igor
"Sarav" <sarav@.sqlservertips.com> wrote in message
news:u8upDynWFHA.2420@.TK2MSFTNGP12.phx.gbl...
> This looks like a Parameter Sniffing problem to me.
> take a look at this link:
> http://www.microsoft.com/technet/pr...005/recomp.mspx
> regards,
> sarav...
> "Igor Marchenko" <igormarchenko@.hotmail.com> wrote in message
> news:OZRXncnWFHA.3464@.TK2MSFTNGP10.phx.gbl...
>

Excessive stored procedure [COMPILE] lock

Hello!

I am trying to investigate strange problem with particular stored
procedure. It runs OK for several days and suddenly we start getting
and lot
of locks. The reason being [COMPILE] lock placed on this procedure. As
a
result, we have 40-50 other connections waiting, then next connection
using
this procedure has [COMPILE] lock etc. Client is fully qualifying
stored
procedure by database/owner name and it doesn't start with sp_. I know

these are the reasons for [COMPILE] lock being placed. Is there
something
else that might trigger this lock? When troubleshooting this issue, I
noticed there was no plan for this procedure in syscacheobjects. The
stored
procedure is very simple (I know it could be rewritten/optimized but
our
developer wrote it):

CREATE PROCEDURE [dbo].[vsp_mail_select]

@.user_id int,
@.folder_id int,
@.is_read bit = 1, --IF 1, pull everything, else just pull unread mail
@.start_index int = null, --unused for now, we return everything
@.total_count int = null output, -- count of all mail in specified
folder
@.unread_count int = null output -- count of unread mail in specified
folder

AS
SET NOCOUNT ON

select m1.* from mail m1(nolock) where m1.user_id=@.user_id and
folder_id=@.folder_id and ((@.is_read=0 and is_read=0) or (@.is_read=1))
order
by date_sent desc
select @.total_count = count(mail_id) from mail m1(nolock) where
m1.user_id=@.user_id and folder_id=@.folder_id and ((is_read=0 and
@.is_read=0)
or (@.is_read=1))
select @.unread_count = count(mail_id) from mail m1(nolock) where
m1.user_id=@.user_id and folder_id=@.folder_id and is_read=0

GO

I was monitoring server for a couple of day before and I am not sure
why
this happens every 3-4 days only!

Any help on this matter would be greately appreciated!

Thanks,
IgorSee:
http://support.microsoft.com/defaul...B;en-us;q263889
http://support.microsoft.com/?kbid=836136
Maybe one of them (or the related articles) will help.

Razvan

Excessive stored procedure [COMPILE] lock

Hello!
I am trying to investigate strange problem with particular stored
procedure. It runs OK for several days and suddenly we start getting and lot
of locks. The reason being [COMPILE] lock placed on this procedure. As a
result, we have 40-50 other connections waiting, then next connection using
this procedure has [COMPILE] lock etc. Client is fully qualifying stored
procedure by database/owner name and it doesn't start with sp_. I know
these are the reasons for [COMPILE] lock being placed. Is there something
else that might trigger this lock? When troubleshooting this issue, I
noticed there was no plan for this procedure in syscacheobjects. The stored
procedure is very simple (I know it could be rewritten/optimized but our
developer wrote it):
CREATE PROCEDURE [dbo].[vsp_mail_select]
@.user_id int,
@.folder_id int,
@.is_read bit = 1, --IF 1, pull everything, else just pull unread mail
@.start_index int = null, --unused for now, we return everything
@.total_count int = null output, -- count of all mail in specified folder
@.unread_count int = null output -- count of unread mail in specified folder
AS
SET NOCOUNT ON
select m1.* from mail m1(nolock) where m1.user_id=@.user_id and
folder_id=@.folder_id and ((@.is_read=0 and is_read=0) or (@.is_read=1)) order
by date_sent desc
select @.total_count = count(mail_id) from mail m1(nolock) where
m1.user_id=@.user_id and folder_id=@.folder_id and ((is_read=0 and @.is_read=0)
or (@.is_read=1))
select @.unread_count = count(mail_id) from mail m1(nolock) where
m1.user_id=@.user_id and folder_id=@.folder_id and is_read=0
GO
I was monitoring server for a couple of day before and I am not sure why
this happens every 3-4 days only!
Any help on this matter would be greately appreciated!
Thanks,
igorThis looks like a Parameter Sniffing problem to me.
take a look at this link:
http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
regards,
sarav...
"Igor Marchenko" <igormarchenko@.hotmail.com> wrote in message
news:OZRXncnWFHA.3464@.TK2MSFTNGP10.phx.gbl...
> Hello!
> I am trying to investigate strange problem with particular stored
> procedure. It runs OK for several days and suddenly we start getting and
> lot of locks. The reason being [COMPILE] lock placed on this procedure. As
> a result, we have 40-50 other connections waiting, then next connection
> using this procedure has [COMPILE] lock etc. Client is fully qualifying
> stored procedure by database/owner name and it doesn't start with sp_. I
> know these are the reasons for [COMPILE] lock being placed. Is there
> something else that might trigger this lock? When troubleshooting this
> issue, I noticed there was no plan for this procedure in syscacheobjects.
> The stored procedure is very simple (I know it could be
> rewritten/optimized but our developer wrote it):
>
> CREATE PROCEDURE [dbo].[vsp_mail_select]
> @.user_id int,
> @.folder_id int,
> @.is_read bit = 1, --IF 1, pull everything, else just pull unread mail
> @.start_index int = null, --unused for now, we return everything
> @.total_count int = null output, -- count of all mail in specified folder
> @.unread_count int = null output -- count of unread mail in specified
> folder
> AS
> SET NOCOUNT ON
> select m1.* from mail m1(nolock) where m1.user_id=@.user_id and
> folder_id=@.folder_id and ((@.is_read=0 and is_read=0) or (@.is_read=1))
> order by date_sent desc
> select @.total_count = count(mail_id) from mail m1(nolock) where
> m1.user_id=@.user_id and folder_id=@.folder_id and ((is_read=0 and
> @.is_read=0) or (@.is_read=1))
> select @.unread_count = count(mail_id) from mail m1(nolock) where
> m1.user_id=@.user_id and folder_id=@.folder_id and is_read=0
> GO
> I was monitoring server for a couple of day before and I am not sure why
> this happens every 3-4 days only!
> Any help on this matter would be greately appreciated!
> Thanks,
> igor
>|||I think recompilations may be caused by modifcations done in base tables.
Developer confirmed that we have an hourly job running that can potentially
modify alot of data in base table. I am thinking of using
KEEPFIXED PLAN option to alleviate this problem.
Igor
"Sarav" <sarav@.sqlservertips.com> wrote in message
news:u8upDynWFHA.2420@.TK2MSFTNGP12.phx.gbl...
> This looks like a Parameter Sniffing problem to me.
> take a look at this link:
> http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
> regards,
> sarav...
> "Igor Marchenko" <igormarchenko@.hotmail.com> wrote in message
> news:OZRXncnWFHA.3464@.TK2MSFTNGP10.phx.gbl...
>> Hello!
>> I am trying to investigate strange problem with particular stored
>> procedure. It runs OK for several days and suddenly we start getting and
>> lot of locks. The reason being [COMPILE] lock placed on this procedure.
>> As a result, we have 40-50 other connections waiting, then next
>> connection using this procedure has [COMPILE] lock etc. Client is fully
>> qualifying stored procedure by database/owner name and it doesn't start
>> with sp_. I know these are the reasons for [COMPILE] lock being placed.
>> Is there something else that might trigger this lock? When
>> troubleshooting this issue, I noticed there was no plan for this
>> procedure in syscacheobjects. The stored procedure is very simple (I know
>> it could be rewritten/optimized but our developer wrote it):
>>
>> CREATE PROCEDURE [dbo].[vsp_mail_select]
>> @.user_id int,
>> @.folder_id int,
>> @.is_read bit = 1, --IF 1, pull everything, else just pull unread mail
>> @.start_index int = null, --unused for now, we return everything
>> @.total_count int = null output, -- count of all mail in specified folder
>> @.unread_count int = null output -- count of unread mail in specified
>> folder
>> AS
>> SET NOCOUNT ON
>> select m1.* from mail m1(nolock) where m1.user_id=@.user_id and
>> folder_id=@.folder_id and ((@.is_read=0 and is_read=0) or (@.is_read=1))
>> order by date_sent desc
>> select @.total_count = count(mail_id) from mail m1(nolock) where
>> m1.user_id=@.user_id and folder_id=@.folder_id and ((is_read=0 and
>> @.is_read=0) or (@.is_read=1))
>> select @.unread_count = count(mail_id) from mail m1(nolock) where
>> m1.user_id=@.user_id and folder_id=@.folder_id and is_read=0
>> GO
>> I was monitoring server for a couple of day before and I am not sure why
>> this happens every 3-4 days only!
>> Any help on this matter would be greately appreciated!
>> Thanks,
>> igor
>

EXCEPTION_ACCESS_VIOLATION: Odd Error in Event Viewer

I have a stored procedure that I use for call a aspx page from within SQL. The stored procedure itself runs fine
with no errors but I keep getting errors in the event viewer everytime
it runs (with .vbs, .NET, .vb6 client) outside Enterprise manager or SQL Query Analyzer....

This is error:

Error: 0, Severity: 19, State: 0
SqlDumpExceptionHandler: Process 52 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.

I think that this is the same problem seen in

http://www.sqlmonster.com/Uwe/Forum...in-Event-Viewer

I've test with XP sp2, W2003 server, SQL 2000 sp3a.....on the same machine.

ANY SUGGESTIONS??

Thanks

--
Message posted via http://www.sqlmonster.com"Flavio 2006 via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:713ecc4e10e34ee3baaee91a9fafe36d@.SQLMonster.c om...
>I have a stored procedure that I use for call a aspx page from within SQL.
>The stored procedure itself runs fine
> with no errors but I keep getting errors in the event viewer everytime
> it runs (with .vbs, .NET, .vb6 client) outside Enterprise manager or SQL
> Query Analyzer....
> This is error:
> Error: 0, Severity: 19, State: 0
> SqlDumpExceptionHandler: Process 52 generated fatal exception c0000005
> EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
>
> I think that this is the same problem seen in
> http://www.sqlmonster.com/Uwe/Forum...in-Event-Viewer
> I've test with XP sp2, W2003 server, SQL 2000 sp3a.....on the same
> machine.
>
> ANY SUGGESTIONS??
> Thanks
> --
> Message posted via http://www.sqlmonster.com

If your problem also involves the MSXML COM object, then have you checked
the KB article from the previous post? If it doesn't apply to you, then you
might want to post more details of what your procedure does, but since
exceptions are basically problems with MSSQL itself, contacting PSS would
probably be the best idea.

Simon|||Hi Simon,

the procedure is quite similar to spSendWebServiceRequest, (i mean procedure in old thread...)

I've contact M$ PSS, and I will post here the response (if exists...:))

Thanks for interest

Flavio

--
Message posted via http://www.sqlmonster.com|||Solution:

MUST install msxml4_sp2 ...

--
Message posted via http://www.sqlmonster.com

EXCEPTION_ACCESS_VIOLATION error

hi,
I am working on sql 2000 server. Jobs kept randomly
failing on one of my servers with the following error, and
the next runs will be successful randomly, any thought on
this?
'SqlDumpExceptionHandler: Process 72 generated fatal
exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server
is terminating this process. [SQLSTATE HY000] (Error 0).'
many thanks,
JJ
In this case, you may have to call SQL Server support. SQL Server
has choked on something.
"JJ Wang" <anonymous@.discussions.microsoft.com> wrote in message
news:427601c4c29e$5cc6f2e0$a301280a@.phx.gbl...
> hi,
> I am working on sql 2000 server. Jobs kept randomly
> failing on one of my servers with the following error, and
> the next runs will be successful randomly, any thought on
> this?
> 'SqlDumpExceptionHandler: Process 72 generated fatal
> exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server
> is terminating this process. [SQLSTATE HY000] (Error 0).'
> many thanks,
> JJ
|||There should be a sqldumpxxx.txt file in the LOG folder under the MSSQL
folder. This file should show you which processes were running and which
function call choked. You might be cleaver and figure something out;
however, I'm with Armando and agree you should put in a call to MS PSS.
Sincerely,
Anthony Thomas
"Armando Prato" wrote:

> In this case, you may have to call SQL Server support. SQL Server
> has choked on something.
> "JJ Wang" <anonymous@.discussions.microsoft.com> wrote in message
> news:427601c4c29e$5cc6f2e0$a301280a@.phx.gbl...
>
>
|||I am also facing the same problem.
I am able to insert a row using raw jdbc connection.But i am getting above problem when i tried to insert a row using datasource (was5)
************************************************** ********************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...

EXCEPTION_ACCESS_VIOLATION error

hi,
I am working on sql 2000 server. Jobs kept randomly
failing on one of my servers with the following error, and
the next runs will be successful randomly, any thought on
this?
'SqlDumpExceptionHandler: Process 72 generated fatal
exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server
is terminating this process. [SQLSTATE HY000] (Error 0).'
many thanks,
JJ
In this case, you may have to call SQL Server support. SQL Server
has choked on something.
"JJ Wang" <anonymous@.discussions.microsoft.com> wrote in message
news:427601c4c29e$5cc6f2e0$a301280a@.phx.gbl...
> hi,
> I am working on sql 2000 server. Jobs kept randomly
> failing on one of my servers with the following error, and
> the next runs will be successful randomly, any thought on
> this?
> 'SqlDumpExceptionHandler: Process 72 generated fatal
> exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server
> is terminating this process. [SQLSTATE HY000] (Error 0).'
> many thanks,
> JJ
|||There should be a sqldumpxxx.txt file in the LOG folder under the MSSQL
folder. This file should show you which processes were running and which
function call choked. You might be cleaver and figure something out;
however, I'm with Armando and agree you should put in a call to MS PSS.
Sincerely,
Anthony Thomas
"Armando Prato" wrote:

> In this case, you may have to call SQL Server support. SQL Server
> has choked on something.
> "JJ Wang" <anonymous@.discussions.microsoft.com> wrote in message
> news:427601c4c29e$5cc6f2e0$a301280a@.phx.gbl...
>
>

EXCEPTION_ACCESS_VIOLATION error

hi,
I am working on sql 2000 server. Jobs kept randomly
failing on one of my servers with the following error, and
the next runs will be successful randomly, any thought on
this?
'SqlDumpExceptionHandler: Process 72 generated fatal
exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server
is terminating this process. [SQLSTATE HY000] (Error 0).'
many thanks,
JJIn this case, you may have to call SQL Server support. SQL Server
has choked on something.
"JJ Wang" <anonymous@.discussions.microsoft.com> wrote in message
news:427601c4c29e$5cc6f2e0$a301280a@.phx.gbl...
> hi,
> I am working on sql 2000 server. Jobs kept randomly
> failing on one of my servers with the following error, and
> the next runs will be successful randomly, any thought on
> this?
> 'SqlDumpExceptionHandler: Process 72 generated fatal
> exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server
> is terminating this process. [SQLSTATE HY000] (Error 0).'
> many thanks,
> JJ|||There should be a sqldumpxxx.txt file in the LOG folder under the MSSQL
folder. This file should show you which processes were running and which
function call choked. You might be cleaver and figure something out;
however, I'm with Armando and agree you should put in a call to MS PSS.
Sincerely,
Anthony Thomas
"Armando Prato" wrote:
> In this case, you may have to call SQL Server support. SQL Server
> has choked on something.
> "JJ Wang" <anonymous@.discussions.microsoft.com> wrote in message
> news:427601c4c29e$5cc6f2e0$a301280a@.phx.gbl...
> > hi,
> >
> > I am working on sql 2000 server. Jobs kept randomly
> > failing on one of my servers with the following error, and
> > the next runs will be successful randomly, any thought on
> > this?
> >
> > 'SqlDumpExceptionHandler: Process 72 generated fatal
> > exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server
> > is terminating this process. [SQLSTATE HY000] (Error 0).'
> >
> > many thanks,
> > JJ
>
>

EXCEPTION_ACCESS_VIOLATION error

hi,
I am working on sql 2000 server. Jobs kept randomly
failing on one of my servers with the following error, and
the next runs will be successful randomly, any thought on
this?
'SqlDumpExceptionHandler: Process 72 generated fatal
exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server
is terminating this process. [SQLSTATE HY000] (Error 0).'
many thanks,
JJIn this case, you may have to call SQL Server support. SQL Server
has choked on something.
"JJ Wang" <anonymous@.discussions.microsoft.com> wrote in message
news:427601c4c29e$5cc6f2e0$a301280a@.phx.gbl...
> hi,
> I am working on sql 2000 server. Jobs kept randomly
> failing on one of my servers with the following error, and
> the next runs will be successful randomly, any thought on
> this?
> 'SqlDumpExceptionHandler: Process 72 generated fatal
> exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server
> is terminating this process. [SQLSTATE HY000] (Error 0).'
> many thanks,
> JJ|||There should be a sqldumpxxx.txt file in the LOG folder under the MSSQL
folder. This file should show you which processes were running and which
function call choked. You might be cleaver and figure something out;
however, I'm with Armando and agree you should put in a call to MS PSS.
Sincerely,
Anthony Thomas
"Armando Prato" wrote:

> In this case, you may have to call SQL Server support. SQL Server
> has choked on something.
> "JJ Wang" <anonymous@.discussions.microsoft.com> wrote in message
> news:427601c4c29e$5cc6f2e0$a301280a@.phx.gbl...
>
>

Exception Error while exporting to Excel

Hello,
I have a report that runs ok at the report manager. However, when I try to
export it to excel
I receive the following error message. Any ideas?
Thank you.
CD
Reporting Services Error
----
--
a.. Exception of type
Microsoft.ReportingServices.ReportRendering.ReportRenderingException was
thrown. (rrRenderingError) Get Online Help
a.. Exception of type
Microsoft.ReportingServices.ReportRendering.ReportRenderingException was
thrown.
a.. Item has already been added. Key in dictionary: "20" Key being
added: "20"
----
--
Microsoft Reporting ServicesDo you have a matrix in your report? Does the matrix has subtotals?
--
Nico Cristache [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"DC" <DC@.yahoo.com> wrote in message
news:eWjCLQAnEHA.2680@.TK2MSFTNGP15.phx.gbl...
>
> Hello,
> I have a report that runs ok at the report manager. However, when I try to
> export it to excel
> I receive the following error message. Any ideas?
> Thank you.
> CD
> Reporting Services Error
> ----
--
> --
> a.. Exception of type
> Microsoft.ReportingServices.ReportRendering.ReportRenderingException was
> thrown. (rrRenderingError) Get Online Help
> a.. Exception of type
> Microsoft.ReportingServices.ReportRendering.ReportRenderingException was
> thrown.
> a.. Item has already been added. Key in dictionary: "20" Key being
> added: "20"
> ----
--
> --
> Microsoft Reporting Services
>|||Yes, I do have a matrix report which has subtotals.
CD
"Nico Cristache [MSFT]" <nipirvan@.microsoft.com> wrote in message
news:uBPuy1AnEHA.3292@.TK2MSFTNGP15.phx.gbl...
> Do you have a matrix in your report? Does the matrix has subtotals?
> --
> Nico Cristache [MSFT]
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "DC" <DC@.yahoo.com> wrote in message
> news:eWjCLQAnEHA.2680@.TK2MSFTNGP15.phx.gbl...
> >
> >
> > Hello,
> > I have a report that runs ok at the report manager. However, when I try
to
> > export it to excel
> > I receive the following error message. Any ideas?
> >
> > Thank you.
> > CD
> >
> > Reporting Services Error
> ----
> --
> > --
> >
> > a.. Exception of type
> > Microsoft.ReportingServices.ReportRendering.ReportRenderingException was
> > thrown. (rrRenderingError) Get Online Help
> > a.. Exception of type
> > Microsoft.ReportingServices.ReportRendering.ReportRenderingException was
> > thrown.
> > a.. Item has already been added. Key in dictionary: "20" Key being
> > added: "20"
> >
> ----
> --
> > --
> > Microsoft Reporting Services
> >
> >
>|||Here comes another bug. If you have a matrix with subtotals, you might
experience some difficulty while exporting reports to excel.
Here is the procedure I followed:
-Get rid of the total column.
-Deploy the report, check if export works.
-Put it back in.
-Deploy the report, check if export works.
When I apply column color changes it failed, and gave me the error message.
Even without color I am happy.
Regards,
Cem
"DC" <DC@.yahoo.com> wrote in message
news:eki3H7AnEHA.3628@.TK2MSFTNGP09.phx.gbl...
> Yes, I do have a matrix report which has subtotals.
> CD
>
> "Nico Cristache [MSFT]" <nipirvan@.microsoft.com> wrote in message
> news:uBPuy1AnEHA.3292@.TK2MSFTNGP15.phx.gbl...
> > Do you have a matrix in your report? Does the matrix has subtotals?
> >
> > --
> > Nico Cristache [MSFT]
> > Microsoft SQL Server Reporting Services
> >
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> >
> >
> > "DC" <DC@.yahoo.com> wrote in message
> > news:eWjCLQAnEHA.2680@.TK2MSFTNGP15.phx.gbl...
> > >
> > >
> > > Hello,
> > > I have a report that runs ok at the report manager. However, when I
try
> to
> > > export it to excel
> > > I receive the following error message. Any ideas?
> > >
> > > Thank you.
> > > CD
> > >
> > > Reporting Services Error
> >
> ----
> > --
> > > --
> > >
> > > a.. Exception of type
> > > Microsoft.ReportingServices.ReportRendering.ReportRenderingException
was
> > > thrown. (rrRenderingError) Get Online Help
> > > a.. Exception of type
> > > Microsoft.ReportingServices.ReportRendering.ReportRenderingException
was
> > > thrown.
> > > a.. Item has already been added. Key in dictionary: "20" Key
being
> > > added: "20"
> > >
> >
> ----
> > --
> > > --
> > > Microsoft Reporting Services
> > >
> > >
> >
> >
>