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

Hi

I have a strange problem with my SQL server 2k instllation - every 10
mintutes when I have the Profiler trace with the entire "Errors" Event
category selected, the following 5 exceptions show up in the trace and
that too for the same SPID.

Error: 16955, Severity: 16, State: 2
Error: 16945, Severity: 16, State: 1
Error: 16955, Severity: 16, State: 2
Error: 16945, Severity: 16, State: 1
Error: 16955, Severity: 16, State: 2
Error: 16945, Severity: 16, State: 1

I have no clue why this is occurring - I tried running a trace with the
SP:StmtCompleted event on, but no other stored procedures show up with
the same spid close to the time where this exception is logged.

Does anyone have a clue as to why this error is occurring ?

RahulPondy (fd96121@.yahoo.com) writes:
> I have a strange problem with my SQL server 2k instllation - every 10
> mintutes when I have the Profiler trace with the entire "Errors" Event
> category selected, the following 5 exceptions show up in the trace and
> that too for the same SPID.
> Error: 16955, Severity: 16, State: 2
> Error: 16945, Severity: 16, State: 1
> Error: 16955, Severity: 16, State: 2
> Error: 16945, Severity: 16, State: 1
> Error: 16955, Severity: 16, State: 2
> Error: 16945, Severity: 16, State: 1
> I have no clue why this is occurring - I tried running a trace with the
> SP:StmtCompleted event on, but no other stored procedures show up with
> the same spid close to the time where this exception is logged.

So what you in a such situation like this is this:

select * from master..sysmessages where error in (16955, 16945)

You could also have looked up the errors in Books Online, by simply
searching for them. This could give you the bonus that there might be
entire topic to troubleshoot the problem. I would not expect that in
this case, though.

These are the messages:

16945 The cursor was not declared.
16955 Could not create an acceptable cursor.

I would guess that 16945 is a consequence of 16955.

Apparently there is some code out there where the cursor declaration
fails, and where there is no error handling, so that execution continues.
Note that this may not have to be a stored procedure. Hypothetically
it could be a server-side cursor initiated by some client API as well.

In any case, it's a problem specific to that process, and it is not that
your server is about to go belly-up.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Exception in Release mode - not in debug mode

I'm having a strange problem/bug that I hope you can help me with.
(Cross-posting since I'm not sure where exactly this issue belongs)
First a little background:
- Visual Studio 2003 (Framework version 1.1) and Windows XP on Client PC
- Windows 2000 Server with SQL Server 2003
- Solution with
- C# ASP.NET project
- C# Class Library (contains DB access routines which calls SP's)
The ASP.NET code calls my Class Library which again executes a Stored
Procedure, see code:
(The connection IS ok, it have just before this executed another, but
simpler, Stored Proc.)
SqlDataAdapter myAdapter = new SqlDataAdapter("sp_SelectSomething",
myConnection);
myAdapter.SelectCommand.CommandType = CommandType.StoredProcedure;
DataTable dt=new DataTable();
myAdapter.Fill(dt);
return dt;
When building my project in debug mode and running the code (both in VS and
outside) everything works OK.
But when running after a release build I receive a exception (see stack
trace below) which happens at the myAdapter.Fill(dt) line.
Stack trace:
Exception: System.NullReferenceException
Message: Object reference not set to an instance of an object.
Source: System.Data
at System.Data.SqlClient.SqlDataReader.SeqRead(Int32 i, Boolean
useSQLTypes, Boolean byteAccess, Boolean& isNull)
at System.Data.SqlClient.SqlDataReader.SeqRead(Int32 i, Boolean
useSQLTypes, Boolean byteAccess)
at System.Data.SqlClient.SqlDataReader.GetValues(Object[] values)
at System.Data.Common.SchemaMapping.LoadDataRow(Boolean clearDataValues,
Boolean acceptChanges)
at System.Data.Common.DbDataAdapter.FillLoadDataRow(SchemaMapping
mapping)
at System.Data.Common.DbDataAdapter.FillFromReader(Object data, String
srcTable, IDataReader dataReader, Int32 startRecord, Int32 maxRecords,
DataColumn parentChapterColumn, Object parentChapterValue)
at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable, IDataReader
dataReader)
at System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable, IDbCommand
command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
at yyy.xxx.www.Business.DataSource.GetSomething()
My Stored Proc. works perfectly when run in Query Analyser and looks like
this:
CREATE PROCEDURE sp_SelectSomething
AS
select
m.[ID],
m.Navn,
m.UnikID,
m.Foretaksnummer,
m.Epost1,
m.Epost2,
m.Adresse,
m.PostNr,
m.KommuneNr,
m.Telefon,
m.Faks,
m.InnmeldtDato,
m.GyldigTilDato,
case m.Automatisk when 1 then convert(bit,0) else convert(bit,1) end as
Manuell,
m.BransjeID,
m.UnderbransjeID,
m.Passord,
m.Aktiv,
m.StotteMottatt,
m.Rapporteringsplikt,
m.StotteNavn,
m.StotteDato,
m.StotteKommentar,
m.Kommentar,
m.InternKommentar,
p.PostSted,
k.Kommune,
f.FylkeNr,
f.Fylke,
b.Navn as Bransje,
m.SistEndret,
isnull(case r.Status
when 3 then 2
else r.Status end,0)
as Status,
convert(bit,
case r.Status
when 4 then 1
else 0 end)
as Endret,
convert(bit,isnull(Inkluderes,1)) as Inkluderes,
convert(bit,isnull(Purres,1)) as Purres
from
Something m
left join Poststed p on p.PostNr=m.PostNr
left join Kommune k on k.KommuneNr=m.KommuneNr
left join Fylke f on f.FylkeNr=k.FylkeNr
inner join Bransje b on b.[ID]=m.BransjeID
left join Rapport r on r.MedlemID=m.[ID] and r.RapportPeriodeID in (Select
top 1 [ID] from RapportPeriode where Aktiv=1)
order by
m.Navn
GO
Thanks and Best regards,
GeirHi Geir,
Weird stuff.
Try few things:
a) Run the project in Relase mode *within* VS.NET - the code shouldn't be
optimized in this way
b) for fun, try switching to OleDb managed provider and see what happens
--
Miha Markic - RightHand .NET consulting & development
miha at rthand com
"Geir Aamodt" <geir_aamodt@.SPAMFILTER.hotmail.com> wrote in message
news:Oh7oX$StDHA.2244@.TK2MSFTNGP09.phx.gbl...
> I'm having a strange problem/bug that I hope you can help me with.
> (Cross-posting since I'm not sure where exactly this issue belongs)
> First a little background:
> - Visual Studio 2003 (Framework version 1.1) and Windows XP on Client PC
> - Windows 2000 Server with SQL Server 2003
> - Solution with
> - C# ASP.NET project
> - C# Class Library (contains DB access routines which calls SP's)
> The ASP.NET code calls my Class Library which again executes a Stored
> Procedure, see code:
> (The connection IS ok, it have just before this executed another, but
> simpler, Stored Proc.)
> SqlDataAdapter myAdapter = new SqlDataAdapter("sp_SelectSomething",
> myConnection);
> myAdapter.SelectCommand.CommandType = CommandType.StoredProcedure;
> DataTable dt=new DataTable();
> myAdapter.Fill(dt);
> return dt;
> When building my project in debug mode and running the code (both in VS
and
> outside) everything works OK.
> But when running after a release build I receive a exception (see stack
> trace below) which happens at the myAdapter.Fill(dt) line.
> Stack trace:
> Exception: System.NullReferenceException
> Message: Object reference not set to an instance of an object.
> Source: System.Data
> at System.Data.SqlClient.SqlDataReader.SeqRead(Int32 i, Boolean
> useSQLTypes, Boolean byteAccess, Boolean& isNull)
> at System.Data.SqlClient.SqlDataReader.SeqRead(Int32 i, Boolean
> useSQLTypes, Boolean byteAccess)
> at System.Data.SqlClient.SqlDataReader.GetValues(Object[] values)
> at System.Data.Common.SchemaMapping.LoadDataRow(Boolean
clearDataValues,
> Boolean acceptChanges)
> at System.Data.Common.DbDataAdapter.FillLoadDataRow(SchemaMapping
> mapping)
> at System.Data.Common.DbDataAdapter.FillFromReader(Object data, String
> srcTable, IDataReader dataReader, Int32 startRecord, Int32 maxRecords,
> DataColumn parentChapterColumn, Object parentChapterValue)
> at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable,
IDataReader
> dataReader)
> at System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32
> startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
> CommandBehavior behavior)
> at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable,
IDbCommand
> command, CommandBehavior behavior)
> at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
> at yyy.xxx.www.Business.DataSource.GetSomething()
> My Stored Proc. works perfectly when run in Query Analyser and looks like
> this:
> CREATE PROCEDURE sp_SelectSomething
> AS
> select
> m.[ID],
> m.Navn,
> m.UnikID,
> m.Foretaksnummer,
> m.Epost1,
> m.Epost2,
> m.Adresse,
> m.PostNr,
> m.KommuneNr,
> m.Telefon,
> m.Faks,
> m.InnmeldtDato,
> m.GyldigTilDato,
> case m.Automatisk when 1 then convert(bit,0) else convert(bit,1) end as
> Manuell,
> m.BransjeID,
> m.UnderbransjeID,
> m.Passord,
> m.Aktiv,
> m.StotteMottatt,
> m.Rapporteringsplikt,
> m.StotteNavn,
> m.StotteDato,
> m.StotteKommentar,
> m.Kommentar,
> m.InternKommentar,
> p.PostSted,
> k.Kommune,
> f.FylkeNr,
> f.Fylke,
> b.Navn as Bransje,
> m.SistEndret,
> isnull(case r.Status
> when 3 then 2
> else r.Status end,0)
> as Status,
> convert(bit,
> case r.Status
> when 4 then 1
> else 0 end)
> as Endret,
> convert(bit,isnull(Inkluderes,1)) as Inkluderes,
> convert(bit,isnull(Purres,1)) as Purres
> from
> Something m
> left join Poststed p on p.PostNr=m.PostNr
> left join Kommune k on k.KommuneNr=m.KommuneNr
> left join Fylke f on f.FylkeNr=k.FylkeNr
> inner join Bransje b on b.[ID]=m.BransjeID
> left join Rapport r on r.MedlemID=m.[ID] and r.RapportPeriodeID in
(Select
> top 1 [ID] from RapportPeriode where Aktiv=1)
> order by
> m.Navn
> GO
> Thanks and Best regards,
> Geir
>|||Hi Miha.
a) Running within VS produces the same results
b) Going to try that one soon
c) Found something else
- First time i call my Stored Proc it is from Global.asax (and it fails)
- On subsequent calls from other places in my code the same Stored proc
works OK!
- Other Stored procs called at the same time from global.asax works also
OK.
This is getting stranger and...
</Geir>
"Miha Markic" <miha at rthand com> wrote in message
news:ujkLqETtDHA.2208@.TK2MSFTNGP10.phx.gbl...
> Hi Geir,
> Weird stuff.
> Try few things:
> a) Run the project in Relase mode *within* VS.NET - the code shouldn't be
> optimized in this way
> b) for fun, try switching to OleDb managed provider and see what happens
> --
> Miha Markic - RightHand .NET consulting & development
> miha at rthand com
> "Geir Aamodt" <geir_aamodt@.SPAMFILTER.hotmail.com> wrote in message
> news:Oh7oX$StDHA.2244@.TK2MSFTNGP09.phx.gbl...
> > I'm having a strange problem/bug that I hope you can help me with.
> > (Cross-posting since I'm not sure where exactly this issue belongs)
> >
> > First a little background:
> > - Visual Studio 2003 (Framework version 1.1) and Windows XP on Client PC
> > - Windows 2000 Server with SQL Server 2003
> > - Solution with
> > - C# ASP.NET project
> > - C# Class Library (contains DB access routines which calls SP's)
> >
> > The ASP.NET code calls my Class Library which again executes a Stored
> > Procedure, see code:
> > (The connection IS ok, it have just before this executed another, but
> > simpler, Stored Proc.)
> > SqlDataAdapter myAdapter = new SqlDataAdapter("sp_SelectSomething",
> > myConnection);
> > myAdapter.SelectCommand.CommandType = CommandType.StoredProcedure;
> > DataTable dt=new DataTable();
> > myAdapter.Fill(dt);
> > return dt;
> >
> > When building my project in debug mode and running the code (both in VS
> and
> > outside) everything works OK.
> >
> > But when running after a release build I receive a exception (see stack
> > trace below) which happens at the myAdapter.Fill(dt) line.
> >
> > Stack trace:
> > Exception: System.NullReferenceException
> > Message: Object reference not set to an instance of an object.
> > Source: System.Data
> > at System.Data.SqlClient.SqlDataReader.SeqRead(Int32 i, Boolean
> > useSQLTypes, Boolean byteAccess, Boolean& isNull)
> > at System.Data.SqlClient.SqlDataReader.SeqRead(Int32 i, Boolean
> > useSQLTypes, Boolean byteAccess)
> > at System.Data.SqlClient.SqlDataReader.GetValues(Object[] values)
> > at System.Data.Common.SchemaMapping.LoadDataRow(Boolean
> clearDataValues,
> > Boolean acceptChanges)
> > at System.Data.Common.DbDataAdapter.FillLoadDataRow(SchemaMapping
> > mapping)
> > at System.Data.Common.DbDataAdapter.FillFromReader(Object data,
String
> > srcTable, IDataReader dataReader, Int32 startRecord, Int32 maxRecords,
> > DataColumn parentChapterColumn, Object parentChapterValue)
> > at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable,
> IDataReader
> > dataReader)
> > at System.Data.Common.DbDataAdapter.FillFromCommand(Object data,
Int32
> > startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
> > CommandBehavior behavior)
> > at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable,
> IDbCommand
> > command, CommandBehavior behavior)
> > at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
> > at yyy.xxx.www.Business.DataSource.GetSomething()
> >
> > My Stored Proc. works perfectly when run in Query Analyser and looks
like
> > this:
> >
> > CREATE PROCEDURE sp_SelectSomething
> > AS
> >
> > select
> > m.[ID],
> > m.Navn,
> > m.UnikID,
> > m.Foretaksnummer,
> > m.Epost1,
> > m.Epost2,
> > m.Adresse,
> > m.PostNr,
> > m.KommuneNr,
> > m.Telefon,
> > m.Faks,
> > m.InnmeldtDato,
> > m.GyldigTilDato,
> > case m.Automatisk when 1 then convert(bit,0) else convert(bit,1) end as
> > Manuell,
> > m.BransjeID,
> > m.UnderbransjeID,
> > m.Passord,
> > m.Aktiv,
> > m.StotteMottatt,
> > m.Rapporteringsplikt,
> > m.StotteNavn,
> > m.StotteDato,
> > m.StotteKommentar,
> > m.Kommentar,
> > m.InternKommentar,
> > p.PostSted,
> > k.Kommune,
> > f.FylkeNr,
> > f.Fylke,
> > b.Navn as Bransje,
> > m.SistEndret,
> > isnull(case r.Status
> > when 3 then 2
> > else r.Status end,0)
> > as Status,
> > convert(bit,
> > case r.Status
> > when 4 then 1
> > else 0 end)
> > as Endret,
> > convert(bit,isnull(Inkluderes,1)) as Inkluderes,
> > convert(bit,isnull(Purres,1)) as Purres
> > from
> > Something m
> > left join Poststed p on p.PostNr=m.PostNr
> > left join Kommune k on k.KommuneNr=m.KommuneNr
> > left join Fylke f on f.FylkeNr=k.FylkeNr
> > inner join Bransje b on b.[ID]=m.BransjeID
> > left join Rapport r on r.MedlemID=m.[ID] and r.RapportPeriodeID in
> (Select
> > top 1 [ID] from RapportPeriode where Aktiv=1)
> > order by
> > m.Navn
> > GO
> >
> > Thanks and Best regards,
> >
> > Geir
> >
> >
>|||Hi Geir,
"Geir Aamodt" <geir_aamodt@.SPAMFILTER.hotmail.com> wrote in message
news:uargSPTtDHA.2208@.TK2MSFTNGP10.phx.gbl...
> Hi Miha.
> a) Running within VS produces the same results
> b) Going to try that one soon
> c) Found something else
> - First time i call my Stored Proc it is from Global.asax (and it
fails)
> - On subsequent calls from other places in my code the same Stored
proc
> works OK!
> - Other Stored procs called at the same time from global.asax works
also
> OK.
> This is getting stranger and...
Yup. Maybe app needs to warm up :)
Check also the connection string & connection object...
--
Miha Markic - RightHand .NET consulting & development
miha at rthand com|||Hi again!
Now testing your suggestion b)
And it works -> Thats fun!
Now I have a workaround, little bit messy, but i'll have to do until
further.
To summarize my supisions:
1. There's a bug somewhere in System.Data.SqlClient namespace
2. It's just occuring only if the following is satified
- The App is built in release mode
- We have a bit medium to complex sql statement run by a stored proc
- When the Web App loads/initializes (in the Global.asax file)
Lots of stuff happening there, maybe I'll try the lotto this weekend :-)
Any other thoughts most welcome to clarify this issue.
</Geir>
"Geir Aamodt" <geir_aamodt@.SPAMFILTER.hotmail.com> wrote in message
news:uargSPTtDHA.2208@.TK2MSFTNGP10.phx.gbl...
> Hi Miha.
> a) Running within VS produces the same results
> b) Going to try that one soon
> c) Found something else
> - First time i call my Stored Proc it is from Global.asax (and it
fails)
> - On subsequent calls from other places in my code the same Stored
proc
> works OK!
> - Other Stored procs called at the same time from global.asax works
also
> OK.
> This is getting stranger and...
> </Geir>
>
> "Miha Markic" <miha at rthand com> wrote in message
> news:ujkLqETtDHA.2208@.TK2MSFTNGP10.phx.gbl...
> > Hi Geir,
> >
> > Weird stuff.
> > Try few things:
> > a) Run the project in Relase mode *within* VS.NET - the code shouldn't
be
> > optimized in this way
> > b) for fun, try switching to OleDb managed provider and see what happens
> >
> > --
> > Miha Markic - RightHand .NET consulting & development
> > miha at rthand com
> >
> > "Geir Aamodt" <geir_aamodt@.SPAMFILTER.hotmail.com> wrote in message
> > news:Oh7oX$StDHA.2244@.TK2MSFTNGP09.phx.gbl...
> > > I'm having a strange problem/bug that I hope you can help me with.
> > > (Cross-posting since I'm not sure where exactly this issue belongs)
> > >
> > > First a little background:
> > > - Visual Studio 2003 (Framework version 1.1) and Windows XP on Client
PC
> > > - Windows 2000 Server with SQL Server 2003
> > > - Solution with
> > > - C# ASP.NET project
> > > - C# Class Library (contains DB access routines which calls SP's)
> > >
> > > The ASP.NET code calls my Class Library which again executes a Stored
> > > Procedure, see code:
> > > (The connection IS ok, it have just before this executed another, but
> > > simpler, Stored Proc.)
> > > SqlDataAdapter myAdapter = new SqlDataAdapter("sp_SelectSomething",
> > > myConnection);
> > > myAdapter.SelectCommand.CommandType = CommandType.StoredProcedure;
> > > DataTable dt=new DataTable();
> > > myAdapter.Fill(dt);
> > > return dt;
> > >
> > > When building my project in debug mode and running the code (both in
VS
> > and
> > > outside) everything works OK.
> > >
> > > But when running after a release build I receive a exception (see
stack
> > > trace below) which happens at the myAdapter.Fill(dt) line.
> > >
> > > Stack trace:
> > > Exception: System.NullReferenceException
> > > Message: Object reference not set to an instance of an object.
> > > Source: System.Data
> > > at System.Data.SqlClient.SqlDataReader.SeqRead(Int32 i, Boolean
> > > useSQLTypes, Boolean byteAccess, Boolean& isNull)
> > > at System.Data.SqlClient.SqlDataReader.SeqRead(Int32 i, Boolean
> > > useSQLTypes, Boolean byteAccess)
> > > at System.Data.SqlClient.SqlDataReader.GetValues(Object[] values)
> > > at System.Data.Common.SchemaMapping.LoadDataRow(Boolean
> > clearDataValues,
> > > Boolean acceptChanges)
> > > at System.Data.Common.DbDataAdapter.FillLoadDataRow(SchemaMapping
> > > mapping)
> > > at System.Data.Common.DbDataAdapter.FillFromReader(Object data,
> String
> > > srcTable, IDataReader dataReader, Int32 startRecord, Int32 maxRecords,
> > > DataColumn parentChapterColumn, Object parentChapterValue)
> > > at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable,
> > IDataReader
> > > dataReader)
> > > at System.Data.Common.DbDataAdapter.FillFromCommand(Object data,
> Int32
> > > startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
> > > CommandBehavior behavior)
> > > at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable,
> > IDbCommand
> > > command, CommandBehavior behavior)
> > > at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
> > > at yyy.xxx.www.Business.DataSource.GetSomething()
> > >
> > > My Stored Proc. works perfectly when run in Query Analyser and looks
> like
> > > this:
> > >
> > > CREATE PROCEDURE sp_SelectSomething
> > > AS
> > >
> > > select
> > > m.[ID],
> > > m.Navn,
> > > m.UnikID,
> > > m.Foretaksnummer,
> > > m.Epost1,
> > > m.Epost2,
> > > m.Adresse,
> > > m.PostNr,
> > > m.KommuneNr,
> > > m.Telefon,
> > > m.Faks,
> > > m.InnmeldtDato,
> > > m.GyldigTilDato,
> > > case m.Automatisk when 1 then convert(bit,0) else convert(bit,1) end
as
> > > Manuell,
> > > m.BransjeID,
> > > m.UnderbransjeID,
> > > m.Passord,
> > > m.Aktiv,
> > > m.StotteMottatt,
> > > m.Rapporteringsplikt,
> > > m.StotteNavn,
> > > m.StotteDato,
> > > m.StotteKommentar,
> > > m.Kommentar,
> > > m.InternKommentar,
> > > p.PostSted,
> > > k.Kommune,
> > > f.FylkeNr,
> > > f.Fylke,
> > > b.Navn as Bransje,
> > > m.SistEndret,
> > > isnull(case r.Status
> > > when 3 then 2
> > > else r.Status end,0)
> > > as Status,
> > > convert(bit,
> > > case r.Status
> > > when 4 then 1
> > > else 0 end)
> > > as Endret,
> > > convert(bit,isnull(Inkluderes,1)) as Inkluderes,
> > > convert(bit,isnull(Purres,1)) as Purres
> > > from
> > > Something m
> > > left join Poststed p on p.PostNr=m.PostNr
> > > left join Kommune k on k.KommuneNr=m.KommuneNr
> > > left join Fylke f on f.FylkeNr=k.FylkeNr
> > > inner join Bransje b on b.[ID]=m.BransjeID
> > > left join Rapport r on r.MedlemID=m.[ID] and r.RapportPeriodeID in
> > (Select
> > > top 1 [ID] from RapportPeriode where Aktiv=1)
> > > order by
> > > m.Navn
> > > GO
> > >
> > > Thanks and Best regards,
> > >
> > > Geir
> > >
> > >
> >
> >
>

Excel Source Returning NULL

I've a package that has a excel source. But i'm having a strange problem with it. One of the columns in the source file have a lot of null values but not all of them. But when i run the package a put a data viewer right after the source and i can see that it's showing that the few fields that should have values are also null. I've tried a lot of things but they didn't work. I need some help and fast if possible.
Example: Source file.xls
Name Grade OtherGrade
John 30 30.23
In the DataViewer
Name Grade OtherGrade
John 30 NULL

thanks

Adriano Coura

Try setting IMEX=1 in your Excel connection properties.

http://support.microsoft.com/default.aspx/kb/194124|||I've tried to put in the extended properties of the connection string the value IMEX=1 and it does return a value but it takes the decimal cases of. LIke 13,98 becomes 1398. Amazing.|||

I have never heard of decimal points being stripped out.

Are you in fact in a locale that uses the comma rather than the period as in US settings? (One of your messages shows the dot, the other message shows a comma.) If regional settings seem to be an issue, I would be inclined to try an OLE DB Connection Manager and set Locale ID, or to add "Locale Identifier=nnn" to your Excel connection string.

-Doug

|||

abcoura wrote:

I've tried to put in the extended properties of the connection string the value IMEX=1 and it does return a value but it takes the decimal cases of. LIke 13,98 becomes 1398. Amazing.

This seems to indicate to me that perhaps the data isn't stored as a number, but rather it has a format applied to it in Excel. Can you confirm or deny this?|||The regional settings are all ok. i may have write two diferent things but that′s not the problem. But it was something like Phil said, sometimes the SSIS consider the column number and sometimes text. In the excel they are all number. But its working now. Thanks for all the replys they're very useful.

Thanks Again|||I will take advantage of this tread to ask the important question about the problem. Here in Brazil the id of a person can come with ou without letters like: m11.333.444 or 11.333.444. So if i have a column and in the first 8 rows i got 5 with only numbers and 3 with letters. So the excel source handle the column as double and put nulls on every row that got a letter in it. I'm thinking if there's a way to always consider the column as text, avoiding the problem with looking to the values as double. Anybody can help solve this question. And putting the excel in order by the value so that the ones with a letter come first doesn't count.

Thanks in Advance.|||You need to override the "guess" of the Excel data type and set it to a text string.|||

IMEX=1. Please see Phil's response earlier in this thread.

-Doug

Excel Source Returning NULL

I've a package that has a excel source. But i'm having a strange problem with it. One of the columns in the source file have a lot of null values but not all of them. But when i run the package a put a data viewer right after the source and i can see that it's showing that the few fields that should have values are also null. I've tried a lot of things but they didn't work. I need some help and fast if possible.
Example: Source file.xls
Name Grade OtherGrade
John 30 30.23
In the DataViewer
Name Grade OtherGrade
John 30 NULL

thanks

Adriano Coura

Try setting IMEX=1 in your Excel connection properties.

http://support.microsoft.com/default.aspx/kb/194124|||I've tried to put in the extended properties of the connection string the value IMEX=1 and it does return a value but it takes the decimal cases of. LIke 13,98 becomes 1398. Amazing.|||

I have never heard of decimal points being stripped out.

Are you in fact in a locale that uses the comma rather than the period as in US settings? (One of your messages shows the dot, the other message shows a comma.) If regional settings seem to be an issue, I would be inclined to try an OLE DB Connection Manager and set Locale ID, or to add "Locale Identifier=nnn" to your Excel connection string.

-Doug

|||

abcoura wrote:

I've tried to put in the extended properties of the connection string the value IMEX=1 and it does return a value but it takes the decimal cases of. LIke 13,98 becomes 1398. Amazing.

This seems to indicate to me that perhaps the data isn't stored as a number, but rather it has a format applied to it in Excel. Can you confirm or deny this?|||The regional settings are all ok. i may have write two diferent things but that′s not the problem. But it was something like Phil said, sometimes the SSIS consider the column number and sometimes text. In the excel they are all number. But its working now. Thanks for all the replys they're very useful.

Thanks Again|||I will take advantage of this tread to ask the important question about the problem. Here in Brazil the id of a person can come with ou without letters like: m11.333.444 or 11.333.444. So if i have a column and in the first 8 rows i got 5 with only numbers and 3 with letters. So the excel source handle the column as double and put nulls on every row that got a letter in it. I'm thinking if there's a way to always consider the column as text, avoiding the problem with looking to the values as double. Anybody can help solve this question. And putting the excel in order by the value so that the ones with a letter come first doesn't count.

Thanks in Advance.|||You need to override the "guess" of the Excel data type and set it to a text string.|||

IMEX=1. Please see Phil's response earlier in this thread.

-Doug

Excel problem connecting to the SQL

Hi To all,
I am facing a strange problem. I used to have an excel
file that extracts data from one of my SQL servers. with
some filterings.
A week ago, some problem happen to the SQL server the
ntldr problem (which i have solved by reloading the files
into the server)which is back to normal.
But, i am having problem with my excel file that shows SQL
Server Error : 53 and 6. No configuration had been
changed.
Please advice. ThanksError 53 == Network Name not found. Normally this equates to Netbios Name
Resolution issues. Make a network trace of the problem and review it with
your network staff.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.