EXEC(@SQL) And Unicode Result Bug

I've got a dynamic SQL query that is generated inside a SP, and is run using
the
EXEC (@.SQL)
or
EXEC sp_executesql @.SQL
This has worked fine until now, where we are now using a database for
unicode charachters to support Japanese language.
All fixed code stored procedures return data correctly in the unicode
format. However, i have had to use string splicing in certain situations to
generate a fully customisable query. These queries all run using EXEC /
sp_executesql from inside the SP. However, i have discovered that all data i
s
return '?' instead of unicode charachters.
This is a cause of some serious issues, and i hope someone can tell me if
there is a solution for this!
Cheers
TrisTris (Tris@.discussions.microsoft.com) writes:
> I've got a dynamic SQL query that is generated inside a SP, and is run
> using the
> EXEC (@.SQL)
> or
> EXEC sp_executesql @.SQL
> This has worked fine until now, where we are now using a database for
> unicode charachters to support Japanese language.
> All fixed code stored procedures return data correctly in the unicode
> format. However, i have had to use string splicing in certain situations
> to generate a fully customisable query. These queries all run using EXEC
> / sp_executesql from inside the SP. However, i have discovered that all
> data is return '?' instead of unicode charachters.
> This is a cause of some serious issues, and i hope someone can tell me if
> there is a solution for this!
First of all, you should use sp_executesql and parameterised statements
rather than EXEC() for dynamic SQL. For a longer disucssion see
http://www.sommarskog.se/dynamic_sql.html.
As for your actual problem, it's diffcult to say without seeing the code.
But my guess would be that you have some varchar variable somewhere that
causes problems, or that you use '' for literals rather than N''. Again,
I suspect that these are problems that would go away if you always use
parameterised statements and never interpolate values into the query string.
I like to stress that this is all guessworks.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Can you post a script that reproduces the problem? Erland mentioned causes
of these symptoms that are not bugs but a specific case is needed to clearly
determine whether or not your issue is a defect or expected behavior.
Hope this helps.
Dan Guzman
SQL Server MVP
"Tris" <Tris@.discussions.microsoft.com> wrote in message
news:A6E5E6A6-F96E-44C8-938F-EC0D459C7862@.microsoft.com...
> I've got a dynamic SQL query that is generated inside a SP, and is run
> using
> the
> EXEC (@.SQL)
> or
> EXEC sp_executesql @.SQL
> This has worked fine until now, where we are now using a database for
> unicode charachters to support Japanese language.
> All fixed code stored procedures return data correctly in the unicode
> format. However, i have had to use string splicing in certain situations
> to
> generate a fully customisable query. These queries all run using EXEC /
> sp_executesql from inside the SP. However, i have discovered that all data
> is
> return '?' instead of unicode charachters.
> This is a cause of some serious issues, and i hope someone can tell me if
> there is a solution for this!
> Cheers
> Tris|||Hi, thanks for the responses.
Yes, some of the arguments used to generate the string were VARCHAR, and
changing them to NVARCHAR has solved the problem.
Cheers
T

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 Pivot Table Limitations?

Dear Anyone,

We seem to have encountered a bug o a limitation but we're not sure what to make of it.

We are using Analysis Services 2005 RTM. Went have a 3 level dimension. We noticed that when we used the dimension in the page field of the pivot table, we noticed that not all members of some parents in the 2nd level does not display. We double checked by browsing the dimension using the Management studio and we confirmed that not all members in the lowest level is being displayed. The ratio is somewhere in the numbers of 45900/2500. The lower number being the only number of members that are being displayed.

The weird part is that we didnt encounter this problem with the Analysis Services 2000. Our current OLAP project now is a migrated version of our old data warehouse.

Any insights anyone?

Thanks,

Joseph

Couple of things to try.

One: Try to browse your cube using cube browser and not dimension browser in the SQL Management studio.

Two. Try building a simpler cube (just few dimensions) in AS2000 and migrate it to AS2005. Compare results in Excel. See if you see different results when browsing cubes of exact the same structure.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights