Exclusive Lock Table like Oracle?

In Oracle, I can do "LOCK TABLE table IN EXCLUSIVE MODE" if I want to
hold a table, is there an equivalent in MSSQL?
I have a table of "next available id numbers", and when one is
requested, I want to lock the table, get the next one, delete it, and
then unlock the table.
Thanks.What version are you using?
Lookup LOCK hints in the BOL
<bradwiseathome@.hotmail.com> wrote in message
news:1145280771.375136.20130@.j33g2000cwa.googlegroups.com...
> In Oracle, I can do "LOCK TABLE table IN EXCLUSIVE MODE" if I want to
> hold a table, is there an equivalent in MSSQL?
> I have a table of "next available id numbers", and when one is
> requested, I want to lock the table, get the next one, delete it, and
> then unlock the table.
> Thanks.
>|||I believe you can do this with table hints.
BEGIN TRANSACTION
SELECT ... WITH TABLOCK
DELETE ...
END TRANSACTION
You can find samples from the BOL.
Mel

Exclusive Lock Table like Oracle?

In Oracle, I can do "LOCK TABLE table IN EXCLUSIVE MODE" if I want to
hold a table, is there an equivalent in MSSQL?
I have a table of "next available id numbers", and when one is
requested, I want to lock the table, get the next one, delete it, and
then unlock the table.
Thanks.What version are you using?
Lookup LOCK hints in the BOL
<bradwiseathome@.hotmail.com> wrote in message
news:1145280771.375136.20130@.j33g2000cwa.googlegroups.com...
> In Oracle, I can do "LOCK TABLE table IN EXCLUSIVE MODE" if I want to
> hold a table, is there an equivalent in MSSQL?
> I have a table of "next available id numbers", and when one is
> requested, I want to lock the table, get the next one, delete it, and
> then unlock the table.
> Thanks.
>|||I believe you can do this with table hints.
BEGIN TRANSACTION
SELECT ... WITH TABLOCK
DELETE ...
END TRANSACTION
You can find samples from the BOL.
Mel

exception_int_divied_by_zero..

Hi all, once I try to change to the design mode of any table, an error
message "Exception_Int_Divided_By_Zero" display, what does this mean? And
alos, when I try to generate SQL script, drop a view, use DTS to export
data, I also receive this error message, any idea how to solve it?
begin 666 Au Yeung.vcf
M0D5'24XZ5D-!4D0-"E9%4E-)3TXZ,BXQ#0I..D%U(%EE=6YG#0I&3CI!=2!9
M975N9PT*14U!24P[4%)%1CM)3E1%4DY%5#IA<G1H=7)A>4!H; W1M86EL+F-O
D;0T*4D56.C(P,#4P-C$S5# W-#@.R-EH-"D5.1#I60T%21 T*
`
end
You mean, you are getting this error from Enterprise Manager? If so, could
you try the same from Enterprise Manager on another machine?
What exactly are you trying to do? You can probably do the same using
scripts from Query Analyzer.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Chung" <chung@.utaxhk.com.hk> wrote in message
news:%238S2Dw%23bFHA.584@.TK2MSFTNGP15.phx.gbl...
Hi all, once I try to change to the design mode of any table, an error
message "Exception_Int_Divided_By_Zero" display, what does this mean? And
alos, when I try to generate SQL script, drop a view, use DTS to export
data, I also receive this error message, any idea how to solve it?
|||I found that this error is associates with the database, not with the
Enterprise Management. Because not all of database has these error, only one
database. And even I backup, and restore it, the error still there. Without
EM, how can I generate the SQL script of the database? and copy data from
the database?
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> glsD:%23HfYeNAcFHA.3184@.TK2MSFTNGP15.phx .gbl...
> You mean, you are getting this error from Enterprise Manager? If so, could
> you try the same from Enterprise Manager on another machine?
> What exactly are you trying to do? You can probably do the same using
> scripts from Query Analyzer.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Chung" <chung@.utaxhk.com.hk> wrote in message
> news:%238S2Dw%23bFHA.584@.TK2MSFTNGP15.phx.gbl...
> Hi all, once I try to change to the design mode of any table, an error
> message "Exception_Int_Divided_By_Zero" display, what does this mean? And
> alos, when I try to generate SQL script, drop a view, use DTS to export
> data, I also receive this error message, any idea how to solve it?
>
>
>
|||Sounds more like either your connection, API, or database environment
settings...like SET NUMERIC_ROUNDABORT or SET ARITHABORT. Check your
database properties.
Another possibility would be that your client tools have been corrupted.
Did you install the Beta 1 for SQL Server 2005? There was an MDAC 9 Beta 1
that was included that caused all sorts of issues with SQL Server 2000
tools. You basically have to do a file replace and registry clean to clear
everything off. A new download of an older MDAC won't fix anything becuase
version 9 is newer, the MDAC will not deploy.
Sincerely,
Anthony Thomas

"Chung" <chung@.utaxhk.com.hk> wrote in message
news:u$cDkLLcFHA.3120@.TK2MSFTNGP12.phx.gbl...
I found that this error is associates with the database, not with the
Enterprise Management. Because not all of database has these error, only one
database. And even I backup, and restore it, the error still there. Without
EM, how can I generate the SQL script of the database? and copy data from
the database?
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com>
glsD:%23HfYeNAcFHA.3184@.TK2MSFTNGP15.phx .gbl...
> You mean, you are getting this error from Enterprise Manager? If so, could
> you try the same from Enterprise Manager on another machine?
> What exactly are you trying to do? You can probably do the same using
> scripts from Query Analyzer.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Chung" <chung@.utaxhk.com.hk> wrote in message
> news:%238S2Dw%23bFHA.584@.TK2MSFTNGP15.phx.gbl...
> Hi all, once I try to change to the design mode of any table, an error
> message "Exception_Int_Divided_By_Zero" display, what does this mean? And
> alos, when I try to generate SQL script, drop a view, use DTS to export
> data, I also receive this error message, any idea how to solve it?
>
>
>

exception_int_divied_by_zero..

Hi all, once I try to change to the design mode of any table, an error
message "Exception_Int_Divided_By_Zero" display, what does this mean? And
alos, when I try to generate SQL script, drop a view, use DTS to export
data, I also receive this error message, any idea how to solve it?
begin 666 Au Yeung.vcf
M0D5'24XZ5D-!4D0-"E9%4E-)3TXZ,BXQ#0I..D%U(%EE=6YG#0I&3CI!=2!9
M975N9PT*14U!24P[4%)%1CM)3E1%4DY%5#IA<G1H=7)A>4!H;W1M86EL+F-O
D;0T*4D56.C(P,#4P-C$S5# W-#@.R-EH-"D5.1#I60T%21 T*
`
endYou mean, you are getting this error from Enterprise Manager? If so, could
you try the same from Enterprise Manager on another machine?
What exactly are you trying to do? You can probably do the same using
scripts from Query Analyzer.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Chung" <chung@.utaxhk.com.hk> wrote in message
news:%238S2Dw%23bFHA.584@.TK2MSFTNGP15.phx.gbl...
Hi all, once I try to change to the design mode of any table, an error
message "Exception_Int_Divided_By_Zero" display, what does this mean? And
alos, when I try to generate SQL script, drop a view, use DTS to export
data, I also receive this error message, any idea how to solve it?|||I found that this error is associates with the database, not with the
Enterprise Management. Because not all of database has these error, only one
database. And even I backup, and restore it, the error still there. Without
EM, how can I generate the SQL script of the database? and copy data from
the database?
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> ¼¶¼g©ó¶l¥ó·s»D:%23HfYeNAcFHA.3184@.TK2MSFTNGP15.phx.gbl...
> You mean, you are getting this error from Enterprise Manager? If so, could
> you try the same from Enterprise Manager on another machine?
> What exactly are you trying to do? You can probably do the same using
> scripts from Query Analyzer.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Chung" <chung@.utaxhk.com.hk> wrote in message
> news:%238S2Dw%23bFHA.584@.TK2MSFTNGP15.phx.gbl...
> Hi all, once I try to change to the design mode of any table, an error
> message "Exception_Int_Divided_By_Zero" display, what does this mean? And
> alos, when I try to generate SQL script, drop a view, use DTS to export
> data, I also receive this error message, any idea how to solve it?
>
>
>|||Sounds more like either your connection, API, or database environment
settings...like SET NUMERIC_ROUNDABORT or SET ARITHABORT. Check your
database properties.
Another possibility would be that your client tools have been corrupted.
Did you install the Beta 1 for SQL Server 2005? There was an MDAC 9 Beta 1
that was included that caused all sorts of issues with SQL Server 2000
tools. You basically have to do a file replace and registry clean to clear
everything off. A new download of an older MDAC won't fix anything becuase
version 9 is newer, the MDAC will not deploy.
Sincerely,
Anthony Thomas
"Chung" <chung@.utaxhk.com.hk> wrote in message
news:u$cDkLLcFHA.3120@.TK2MSFTNGP12.phx.gbl...
I found that this error is associates with the database, not with the
Enterprise Management. Because not all of database has these error, only one
database. And even I backup, and restore it, the error still there. Without
EM, how can I generate the SQL script of the database? and copy data from
the database?
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com>
¼¶¼g©ó¶l¥ó·s»D:%23HfYeNAcFHA.3184@.TK2MSFTNGP15.phx.gbl...
> You mean, you are getting this error from Enterprise Manager? If so, could
> you try the same from Enterprise Manager on another machine?
> What exactly are you trying to do? You can probably do the same using
> scripts from Query Analyzer.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Chung" <chung@.utaxhk.com.hk> wrote in message
> news:%238S2Dw%23bFHA.584@.TK2MSFTNGP15.phx.gbl...
> Hi all, once I try to change to the design mode of any table, an error
> message "Exception_Int_Divided_By_Zero" display, what does this mean? And
> alos, when I try to generate SQL script, drop a view, use DTS to export
> data, I also receive this error message, any idea how to solve it?
>
>
>

Exception with ReportViewer in local mode

I use a ReportViewer to display a report in local mode (WinForm). As long as I use VS2005 in debug mode, the report is displayed correct without any problems. If I run the compiled application directly, I get a crash message :

"xxx has encountered a problem and needs to close. We are sorry for the inconvenience."....

None of the exception handlings are called (the one where the "Me.ReportViewer1.RefreshReport()" is located and the one from the application "MyApplication_UnhandledException")

Thanks for help

Peter

Hi Panpan,

I'm experiencing the exact same problem right now and I can't find out what's the cause. Did you ever figure it out? If you did I'd appreciate your answer!

Greetings,

Sjako

Exception with ReportViewer in local mode

I use a ReportViewer to display a report in local mode (WinForm). As long as I use VS2005 in debug mode, the report is displayed correct without any problems. If I run the compiled application directly, I get a crash message :

"xxx has encountered a problem and needs to close. We are sorry for the inconvenience."....

None of the exception handlings are called (the one where the "Me.ReportViewer1.RefreshReport()" is located and the one from the application "MyApplication_UnhandledException")

Thanks for help

Peter

Hi Panpan,

I'm experiencing the exact same problem right now and I can't find out what's the cause. Did you ever figure it out? If you did I'd appreciate your answer!

Greetings,

Sjako

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 MODE function - value that occurs the most

Hi

I am looking for an implementation of the EXCEL MODE function for Analysis Services calculation. MEDIAN is available as integrated function, but MODE is missing.

Has someone of you an MDX implementation for this ? (for an Sum Measure) (- I am concerned about the performance if I do this over >50.000 Member Sets manually.)

Best Regards

HANNES

I have programmed my one procedure the do the job.

If you are interested the code is available at http://www.hmayer.net/tiki-list_file_gallery.php?galleryId=24

HANNES

|||

Interesting problem, Hannes. It is possible in MDX - here's a solution:

Code Snippet

with

member measures.countsame as

count(

filter(union([Date].[Calendar].currentmember.level.members, {[Date].[Calendar].currentmember} as currentmonth)

, ([Date].[Calendar].currentmember, [Measures].[Customer Count])

= (currentmonth.item(0).item(0), [Measures].[Customer Count])

)

)

member measures.mode as

(topcount([Date].[Calendar].[Month].members,1, measures.countsame).item(0).item(0), [Measures].[Customer Count])

select {[Measures].[Customer Count], measures.countsame, measures.mode} on 0,

[Date].[Calendar].[Month].members on 1

from [Adventure Works]

Although I suspect there might be a more a efficient way of doing it in MDX (I need to think a bit), custom code may well give you the best performance. If you could test this against your procedure I'd be interested to hear the result!

Regards,

Chris

Excel MODE function - value that occurs the most

Hi

I am looking for an implementation of the EXCEL MODE function for Analysis Services calculation. MEDIAN is available as integrated function, but MODE is missing.

Has someone of you an MDX implementation for this ? (for an Sum Measure) (- I am concerned about the performance if I do this over >50.000 Member Sets manually.)

Best Regards

HANNES

I have programmed my one procedure the do the job.

If you are interested the code is available at http://www.hmayer.net/tiki-list_file_gallery.php?galleryId=24

HANNES

|||

Interesting problem, Hannes. It is possible in MDX - here's a solution:

Code Snippet

with

member measures.countsame as

count(

filter(union([Date].[Calendar].currentmember.level.members, {[Date].[Calendar].currentmember} as currentmonth)

, ([Date].[Calendar].currentmember, [Measures].[Customer Count])

= (currentmonth.item(0).item(0), [Measures].[Customer Count])

)

)

member measures.mode as

(topcount([Date].[Calendar].[Month].members,1, measures.countsame).item(0).item(0), [Measures].[Customer Count])

select {[Measures].[Customer Count], measures.countsame, measures.mode} on 0,

[Date].[Calendar].[Month].members on 1

from [Adventure Works]

Although I suspect there might be a more a efficient way of doing it in MDX (I need to think a bit), custom code may well give you the best performance. If you could test this against your procedure I'd be interested to hear the result!

Regards,

Chris