Mysterious loss of data from mssql2k

Hi All,

I'm trying to track down a mysterious problem we're experiencing in
which updates and inserts to tables in our mssql2k server appear to be
'disappearing.'

To explain our situation:

We have a web page (written in ASP, if that's relevant) on which we
accept enrollment information.

When that page is submitted, the form data is passed to a stored
procedure on our mssql2k server, which performs several operations,
all of which are wrapped in a transaction.

In particular, the stored procedure performs an update operation on a
record in one table (i'll call it TableA) and an insert into another
table (TableB).

If the procedure encounters a problem (ie after each update / insert
operation in the procedure we test for IF @.@.Error<>0) it performs a
rollback, performs a select similar to the one immediately below, and
then RETURNs.

SELECT '1' as error, 'Unable to update TableA' as errormsg

If the procedure doesn't fail any of the @.@.Error tests, the
transaction is committed, and a membership number is SELECTed to be
returned.

SELECT '0' as error, @.memnum as membershipnumber

The @.memnum variable is populated within the transaction.

Back in the ASP page we test both for the proc returning an empty
recordset, or for it passing an explicit value in the error field, and
push the page to an error page if either of these conditions are met.

If, on the other hand, none of these conditions are met, and the
membershipnumber field in the recordset is populated with a valid
membership number, we push to a confirmation page.

This confirmation page receives the membership number in a session
variable, performs a SELECT against TableB (the table that received
the insert during the proc) using that membership number in the WHERE
clause, and the resultant recordset is used to populate the
confirmation details on that page. That recordset is also then used to
populate the details of a confirmation email, which is automatically
sent by the confirmation page.

And now here's our problem: we've become aware of a handfull of people
who have gone through the enrollment process, have received the
confirmation email containing the information they supplied as
expected, but the data appears to be entirely missing from our tables.
By that I mean that the record in TableA does not appear to have been
updated (under normal circumstances that record should have had
several flags set, and several other fields updated with information
supplied by the person enrolling), and the record in TableB does not
appear to have been inserted.

In essence, looking at our tables, it *feels* like the transaction in
the stored procedure for that particular enrollment hit a problem and
was rolled back. However, the evidence that we have in the form of the
confirmation email argues strongly that the data must have existed in
our tables (particularly in TableB), if only for an unknown period of
time.

We're kind of at our wit's end to work out what is going wrong with
these enrollments. From my understanding of transactions (and I could
well be wrong) any changes to data (ie updates, inserts etc) contained
within are essentially 'invisible' to any other operation (ie the
SELECT that happens in the confirmation page) until the transaction is
committed, implying that the effect of the update and insert should
have been 'permanently' successful if no error code is received and if
a valid membership number was returned. I ask, because someone in our
team has suggested that maybe the operations in the transaction
'lasted long enough' in the tables to have been visible for the SELECT
on the confirmation page to have worked, but were then subsequently
rolled back, explaining why the confirmation email is appropriately
populated and why the data then appears to be missing. However, as I
said, this doesn't match my understanding of how transactions behave.

Sorry for the length of this post, but I felt it was best to explain
this as best as I could.

Does anyone have any advice they can give us on this situation? ie,
are there any known problems with operations in transactions 'bleeding
over' into tables, but then being rolled back at some later point?
Does anyone have any thoughts or suggestions on how we can further
diagnose this issue?

Truly, any help will be immensely appreciated...

Thanks in advance,

M Wells"M Wells" <planetthoughtful@.gmail.com> wrote in message
news:hmba41p1gvtsmudtbus027ad3rapgo1ghd@.4ax.com...
> Hi All,
> I'm trying to track down a mysterious problem we're experiencing in
> which updates and inserts to tables in our mssql2k server appear to be
> 'disappearing.'
> To explain our situation:
> We have a web page (written in ASP, if that's relevant) on which we
> accept enrollment information.

<snip
First, to address your question about data inside a transaction being
visible to other connections, this would only happen if the other connection
explicitly sets its transaction level to READ UNCOMMITTED, which would allow
it to see data which has been inserted/updated but not committed. See SET
TRANSACTION ISOLATION LEVEL in Books Online for more details - I suppose
your ASP connections could be setting this isolation level, but it isn't the
default, so it would be somewhat unusual.

As for tracking down what's going on with the data, you can use Profiler to
run a trace, perhaps filtered on those specific tables and any relevant
stored procedures. If this problem happens fairly often, then running it
interactively may be possible, otherwise see the sp_trace_% procs in Books
Online for details of setting up a server-side trace.

You might also want to check for any triggers on the tables, as sometimes
they can be fired at times you don't expect. And if you have a middle tier
layer, you could also see if it's initiating a transaction before calling
the procedure - it could be that the procedure itself commits correctly, and
the email is sent, but there's an additional outer transaction started by
the middle tier which is then sometimes rolled back after sending the email.
Checking @.@.TRANCOUNT inside the procedure would give you a clue.

Simon|||M Wells (planetthoughtful@.gmail.com) writes:
> If the procedure doesn't fail any of the @.@.Error tests, the
> transaction is committed, and a membership number is SELECTed to be
> returned.
> SELECT '0' as error, @.memnum as membershipnumber
> The @.memnum variable is populated within the transaction.
> Back in the ASP page we test both for the proc returning an empty
> recordset, or for it passing an explicit value in the error field, and
> push the page to an error page if either of these conditions are met.
> If, on the other hand, none of these conditions are met, and the
> membershipnumber field in the recordset is populated with a valid
> membership number, we push to a confirmation page.
> This confirmation page receives the membership number in a session
> variable, performs a SELECT against TableB (the table that received
> the insert during the proc) using that membership number in the WHERE
> clause, and the resultant recordset is used to populate the
> confirmation details on that page. That recordset is also then used to
> populate the details of a confirmation email, which is automatically
> sent by the confirmation page.
> And now here's our problem: we've become aware of a handfull of people
> who have gone through the enrollment process, have received the
> confirmation email containing the information they supplied as
> expected, but the data appears to be entirely missing from our tables.

As I understand, this is an intermittent problem, and you don't have a
reproducible scenario. This make such a problem much more difficult
to track down. And if you make changes to address, you cannot really
be sure that you fixed the right thing.

One thing that is not clear to me is whether it is the same SQL Server
process that runs the stored procedure and the gets the data to the
confirmation page, or whether they are two different. (I should butt
in that I don't know ASP or IIS, so this talk about session variables
etc, tells me little.)

If it is the same SQL Server process, here is something that could
happen:

1) The stored procedure first run unsuccessfully, and a transaction
is started, but then neither committed nor rolled back.
2) The process then runs the procedure successfully, and then gets
the data to the confirmation page. This time a nested transaction
was started and committed. However, "commit" in the case of an
inner transaction just means that transaction count is decremented.
3) The process goes on and registers and confirms more enrollments.
4) Eventually the process is logged out, still with an open transaction.
All enrollments are now rolled back.

So why in step 1, would this happen? It can be a coding error in
the stored procedure, so that a rollback is not executed when it
should. But it could also be a client-side thing. Say that the
procedure is blocked for some reason, and the client gets a command
timeout. In this case the transaction started by the stored procedure
is *not* rolled back. This a really nasty gotcha.

If the confirmation page is really a separate SQL Server connection -
and you should really use the SQL Server Profiler to verify this - then
the data has been committed, and thus it has later been removed. Well,
if the confirmation reads with NOLOCK, are back to the previous
scenario.

One thing you should investigate in either case, is whether these missing
enrollments happened at different points in time, or if they are clustered.
That could give a clue of what may have happened.

I hope this has given you some more ideas of what to look for.

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

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

'My Reports' folder not being created for some users

Hi,
I was just nosing around the DB, and I noticed that all the users are in the
'users' table, but the user accounts don't appear in the 'PolicyUserRole'
table, and hence there are not any policies for them.
I don't know if this helps any or just confuses...
And there aren't any application events in the log on the server, either.
"Lance" <lance@.[nospam]keayweb.com> wrote in message
news:11869vtmjhs8i08@.corp.supernews.com...
> Hi,
> Thanks for the reply, but I am a bit confused about it! Maybe if I
explain
> a bit more about the setup: The ReportServer is set up with no common
> reports. Each user can *only* view the reports in their own 'My Reports'
> folder. As such each user *must* have a 'My Reports' folder! (obviously)
>
> Here is how the server is set up:
> * There are the standard 'Item-level' roles and an additional one,
> 'Viewer' - which is a limited acct, that just allows the user to 'view
> folders' and 'view reports'. None of the non-admin users can create
> reports.
> * The 'System Roles' are the standard 'System Administrator' and 'System
> User' - the 'System User' has the 'View report server properties' and
'View
> shared schedules' tasks.
> * The 'Enable My Reports to support user-owned folders for publishing and
> running personalized reports.' checkbox is checked in the 'Reporst Server'
> 'Site Settings'. The 'Choose the role to apply to each user's My Reports
> folder' dropdown is 'Viewer'
>
> To set up for a new user:
> * Each new user is added to the 'Users' group on the server (windows 2000
> server).
> * The user logs in, and a 'My Reports' folder should be created.
>
> But mostly, a 'My Reports' folder is *not* created. Or perhaps just not
> shown. I don't know how to check whether there is a 'My Reports' folder
for
> the user - I am thinking that there is a value in the DB somewhere that
> tells me...
>
> I was experimenting with a dozen new users, and the docs say that as soon
as
> a windows user logs in, a 'My Reports' folder should be created. I tried
> both creating a 'System Role' for the user and *not* creating a system
role,
> but the creation of the 'My Reports' folder seems to occur randomly. I
say
> *seem*, as I am sure that I am missing a step or something, as I confess
> that I find the role assignment somewhat confusing. But I'm new, and I am
> sure I'll get used to it...
>
>
> In response to your kindly answer:
> So, from my limited understanding, the 'My Reports' role is an
'item-level'
> role; I can only seem to assign to this at the report level (ie an 'item')
> *not* to a user. But if the 'My Reports' folder is not being created, I
> can't assign this 'tiem-level' role to it!
>
>
> In short: I am still stuck with some users having a 'My Reports' folder
and
> most users *not* having one!
> Thanks,
> Lance
>
>
>
>
>
> > Lance,
> >
> > Have you checked to ensure all users are are included in the "My
Reports"
> > role - or in some custom role that includes the required permissions.
> >
> > Regards,
> >
> >
> > --
> > Rob Labbé, MCP, MCAD, MCSD, MCT
> > Lead Architect/Trainer
> > Fidelis
> >
> > Blog: http://spaces.msn.com/members/roblabbe
> >
> > "Lance" <lance@.[nospam]keayweb.com> wrote in message
> > news:11849vnplj1n02@.corp.supernews.com...
> > > Hi,
> > > I am having some trouble getting the 'My Reports' folder to appear for
> all
> > > users. The docs just say that as long as the user exists as a windows
> > > user
> > > and the 'My Reports' option is ticked in 'Site Settings', ReportServer
> > > should just create a 'My Reports' folder the first time a user logs
onto
> > > the
> > > ReportServer. But this is not working for all users. About half of
the
> > > users have the 'My Reports' folder created. So I need to know how to
> > > create
> > > them manually, or at least some workaround to get each user a 'My
> Reports'
> > > folder!
> > >
> > > I don't see any difference between the user accts (other than the
> names).
> > >
> > > Thanks,
> > > Lance
> > >
> > >
> >
> >
>
>The my reports folder will not be created until the user browses to that
folder. (Yes, I know, a little confusing) The first time they browse to it
RS will create the folder and assign the My Reports permission to it.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Lance" <lance@.[nospam]keayweb.com> wrote in message
news:1188kvr7cuimc7a@.corp.supernews.com...
> Hi,
> I was just nosing around the DB, and I noticed that all the users are in
> the
> 'users' table, but the user accounts don't appear in the 'PolicyUserRole'
> table, and hence there are not any policies for them.
> I don't know if this helps any or just confuses...
> And there aren't any application events in the log on the server, either.
>
> "Lance" <lance@.[nospam]keayweb.com> wrote in message
> news:11869vtmjhs8i08@.corp.supernews.com...
>> Hi,
>> Thanks for the reply, but I am a bit confused about it! Maybe if I
> explain
>> a bit more about the setup: The ReportServer is set up with no common
>> reports. Each user can *only* view the reports in their own 'My Reports'
>> folder. As such each user *must* have a 'My Reports' folder!
>> (obviously)
>> Here is how the server is set up:
>> * There are the standard 'Item-level' roles and an additional one,
>> 'Viewer' - which is a limited acct, that just allows the user to 'view
>> folders' and 'view reports'. None of the non-admin users can create
>> reports.
>> * The 'System Roles' are the standard 'System Administrator' and 'System
>> User' - the 'System User' has the 'View report server properties' and
> 'View
>> shared schedules' tasks.
>> * The 'Enable My Reports to support user-owned folders for publishing and
>> running personalized reports.' checkbox is checked in the 'Reporst
>> Server'
>> 'Site Settings'. The 'Choose the role to apply to each user's My Reports
>> folder' dropdown is 'Viewer'
>> To set up for a new user:
>> * Each new user is added to the 'Users' group on the server (windows 2000
>> server).
>> * The user logs in, and a 'My Reports' folder should be created.
>> But mostly, a 'My Reports' folder is *not* created. Or perhaps just not
>> shown. I don't know how to check whether there is a 'My Reports' folder
> for
>> the user - I am thinking that there is a value in the DB somewhere that
>> tells me...
>> I was experimenting with a dozen new users, and the docs say that as soon
> as
>> a windows user logs in, a 'My Reports' folder should be created. I tried
>> both creating a 'System Role' for the user and *not* creating a system
> role,
>> but the creation of the 'My Reports' folder seems to occur randomly. I
> say
>> *seem*, as I am sure that I am missing a step or something, as I confess
>> that I find the role assignment somewhat confusing. But I'm new, and I
>> am
>> sure I'll get used to it...
>>
>> In response to your kindly answer:
>> So, from my limited understanding, the 'My Reports' role is an
> 'item-level'
>> role; I can only seem to assign to this at the report level (ie an
>> 'item')
>> *not* to a user. But if the 'My Reports' folder is not being created, I
>> can't assign this 'tiem-level' role to it!
>>
>> In short: I am still stuck with some users having a 'My Reports' folder
> and
>> most users *not* having one!
>> Thanks,
>> Lance
>>
>>
>> > Lance,
>> >
>> > Have you checked to ensure all users are are included in the "My
> Reports"
>> > role - or in some custom role that includes the required permissions.
>> >
>> > Regards,
>> >
>> >
>> > --
>> > Rob Labbé, MCP, MCAD, MCSD, MCT
>> > Lead Architect/Trainer
>> > Fidelis
>> >
>> > Blog: http://spaces.msn.com/members/roblabbe
>> >
>> > "Lance" <lance@.[nospam]keayweb.com> wrote in message
>> > news:11849vnplj1n02@.corp.supernews.com...
>> > > Hi,
>> > > I am having some trouble getting the 'My Reports' folder to appear
>> > > for
>> all
>> > > users. The docs just say that as long as the user exists as a
>> > > windows
>> > > user
>> > > and the 'My Reports' option is ticked in 'Site Settings',
>> > > ReportServer
>> > > should just create a 'My Reports' folder the first time a user logs
> onto
>> > > the
>> > > ReportServer. But this is not working for all users. About half of
> the
>> > > users have the 'My Reports' folder created. So I need to know how to
>> > > create
>> > > them manually, or at least some workaround to get each user a 'My
>> Reports'
>> > > folder!
>> > >
>> > > I don't see any difference between the user accts (other than the
>> names).
>> > >
>> > > Thanks,
>> > > Lance
>> > >
>> > >
>> >
>> >
>>
>
>|||Hi,
Thanks for the reply but if the user can't see the folder, how can they
browse to it? As is explained further on in the text, some users seem to
get them created automatically, while others don't. So I need either a way
to create the folder manually (some SQL?) or an explanation of the user
differences as to why a folder would be created for one user but not for
another...
Thanks,
Lance
"Daniel Reib (MSFT)" <danreib@.online.microsoft.com> wrote in message
news:#cE3Af8VFHA.1200@.TK2MSFTNGP14.phx.gbl...
> The my reports folder will not be created until the user browses to that
> folder. (Yes, I know, a little confusing) The first time they browse to
it
> RS will create the folder and assign the My Reports permission to it.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "Lance" <lance@.[nospam]keayweb.com> wrote in message
> news:1188kvr7cuimc7a@.corp.supernews.com...
> > Hi,
> > I was just nosing around the DB, and I noticed that all the users are in
> > the
> > 'users' table, but the user accounts don't appear in the
'PolicyUserRole'
> > table, and hence there are not any policies for them.
> >
> > I don't know if this helps any or just confuses...
> >
> > And there aren't any application events in the log on the server,
either.
> >
> >
> > "Lance" <lance@.[nospam]keayweb.com> wrote in message
> > news:11869vtmjhs8i08@.corp.supernews.com...
> >> Hi,
> >> Thanks for the reply, but I am a bit confused about it! Maybe if I
> > explain
> >> a bit more about the setup: The ReportServer is set up with no common
> >> reports. Each user can *only* view the reports in their own 'My
Reports'
> >> folder. As such each user *must* have a 'My Reports' folder!
> >> (obviously)
> >>
> >> Here is how the server is set up:
> >> * There are the standard 'Item-level' roles and an additional one,
> >> 'Viewer' - which is a limited acct, that just allows the user to 'view
> >> folders' and 'view reports'. None of the non-admin users can create
> >> reports.
> >> * The 'System Roles' are the standard 'System Administrator' and
'System
> >> User' - the 'System User' has the 'View report server properties' and
> > 'View
> >> shared schedules' tasks.
> >> * The 'Enable My Reports to support user-owned folders for publishing
and
> >> running personalized reports.' checkbox is checked in the 'Reporst
> >> Server'
> >> 'Site Settings'. The 'Choose the role to apply to each user's My
Reports
> >> folder' dropdown is 'Viewer'
> >>
> >> To set up for a new user:
> >> * Each new user is added to the 'Users' group on the server (windows
2000
> >> server).
> >> * The user logs in, and a 'My Reports' folder should be created.
> >>
> >> But mostly, a 'My Reports' folder is *not* created. Or perhaps just
not
> >> shown. I don't know how to check whether there is a 'My Reports'
folder
> > for
> >> the user - I am thinking that there is a value in the DB somewhere that
> >> tells me...
> >>
> >> I was experimenting with a dozen new users, and the docs say that as
soon
> > as
> >> a windows user logs in, a 'My Reports' folder should be created. I
tried
> >> both creating a 'System Role' for the user and *not* creating a system
> > role,
> >> but the creation of the 'My Reports' folder seems to occur randomly. I
> > say
> >> *seem*, as I am sure that I am missing a step or something, as I
confess
> >> that I find the role assignment somewhat confusing. But I'm new, and I
> >> am
> >> sure I'll get used to it...
> >>
> >>
> >> In response to your kindly answer:
> >> So, from my limited understanding, the 'My Reports' role is an
> > 'item-level'
> >> role; I can only seem to assign to this at the report level (ie an
> >> 'item')
> >> *not* to a user. But if the 'My Reports' folder is not being created,
I
> >> can't assign this 'tiem-level' role to it!
> >>
> >>
> >> In short: I am still stuck with some users having a 'My Reports' folder
> > and
> >> most users *not* having one!
> >> Thanks,
> >> Lance
> >>
> >>
> >>
> >>
> >>
> >> > Lance,
> >> >
> >> > Have you checked to ensure all users are are included in the "My
> > Reports"
> >> > role - or in some custom role that includes the required permissions.
> >> >
> >> > Regards,
> >> >
> >> >
> >> > --
> >> > Rob Labbé, MCP, MCAD, MCSD, MCT
> >> > Lead Architect/Trainer
> >> > Fidelis
> >> >
> >> > Blog: http://spaces.msn.com/members/roblabbe
> >> >
> >> > "Lance" <lance@.[nospam]keayweb.com> wrote in message
> >> > news:11849vnplj1n02@.corp.supernews.com...
> >> > > Hi,
> >> > > I am having some trouble getting the 'My Reports' folder to appear
> >> > > for
> >> all
> >> > > users. The docs just say that as long as the user exists as a
> >> > > windows
> >> > > user
> >> > > and the 'My Reports' option is ticked in 'Site Settings',
> >> > > ReportServer
> >> > > should just create a 'My Reports' folder the first time a user logs
> > onto
> >> > > the
> >> > > ReportServer. But this is not working for all users. About half
of
> > the
> >> > > users have the 'My Reports' folder created. So I need to know how
to
> >> > > create
> >> > > them manually, or at least some workaround to get each user a 'My
> >> Reports'
> >> > > folder!
> >> > >
> >> > > I don't see any difference between the user accts (other than the
> >> names).
> >> > >
> >> > > Thanks,
> >> > > Lance
> >> > >
> >> > >
> >> >
> >> >
> >>
> >>
> >
> >
> >
>|||When My Reports is enabled, ListChildren will always return a folder named
'My Reports', even if it has not been created for that user. That is why it
will show up in Report Manager. The first time a user calls any soap method
that uses the My Reports folder, RS will create the corresponding folder in
the user directory that the My Reports folder points to. If you have each
user just call ListChildren on '/My Reports' then each user will have the
'My Reports' folder created.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Lance" <lance@.[nospam]keayweb.com> wrote in message
news:1189iffai01fh76@.corp.supernews.com...
> Hi,
> Thanks for the reply but if the user can't see the folder, how can they
> browse to it? As is explained further on in the text, some users seem to
> get them created automatically, while others don't. So I need either a
> way
> to create the folder manually (some SQL?) or an explanation of the user
> differences as to why a folder would be created for one user but not for
> another...
> Thanks,
> Lance
>
> "Daniel Reib (MSFT)" <danreib@.online.microsoft.com> wrote in message
> news:#cE3Af8VFHA.1200@.TK2MSFTNGP14.phx.gbl...
>> The my reports folder will not be created until the user browses to that
>> folder. (Yes, I know, a little confusing) The first time they browse to
> it
>> RS will create the folder and assign the My Reports permission to it.
>> --
>> -Daniel
>> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>>
>> "Lance" <lance@.[nospam]keayweb.com> wrote in message
>> news:1188kvr7cuimc7a@.corp.supernews.com...
>> > Hi,
>> > I was just nosing around the DB, and I noticed that all the users are
>> > in
>> > the
>> > 'users' table, but the user accounts don't appear in the
> 'PolicyUserRole'
>> > table, and hence there are not any policies for them.
>> >
>> > I don't know if this helps any or just confuses...
>> >
>> > And there aren't any application events in the log on the server,
> either.
>> >
>> >
>> > "Lance" <lance@.[nospam]keayweb.com> wrote in message
>> > news:11869vtmjhs8i08@.corp.supernews.com...
>> >> Hi,
>> >> Thanks for the reply, but I am a bit confused about it! Maybe if I
>> > explain
>> >> a bit more about the setup: The ReportServer is set up with no common
>> >> reports. Each user can *only* view the reports in their own 'My
> Reports'
>> >> folder. As such each user *must* have a 'My Reports' folder!
>> >> (obviously)
>> >>
>> >> Here is how the server is set up:
>> >> * There are the standard 'Item-level' roles and an additional one,
>> >> 'Viewer' - which is a limited acct, that just allows the user to 'view
>> >> folders' and 'view reports'. None of the non-admin users can create
>> >> reports.
>> >> * The 'System Roles' are the standard 'System Administrator' and
> 'System
>> >> User' - the 'System User' has the 'View report server properties' and
>> > 'View
>> >> shared schedules' tasks.
>> >> * The 'Enable My Reports to support user-owned folders for publishing
> and
>> >> running personalized reports.' checkbox is checked in the 'Reporst
>> >> Server'
>> >> 'Site Settings'. The 'Choose the role to apply to each user's My
> Reports
>> >> folder' dropdown is 'Viewer'
>> >>
>> >> To set up for a new user:
>> >> * Each new user is added to the 'Users' group on the server (windows
> 2000
>> >> server).
>> >> * The user logs in, and a 'My Reports' folder should be created.
>> >>
>> >> But mostly, a 'My Reports' folder is *not* created. Or perhaps just
> not
>> >> shown. I don't know how to check whether there is a 'My Reports'
> folder
>> > for
>> >> the user - I am thinking that there is a value in the DB somewhere
>> >> that
>> >> tells me...
>> >>
>> >> I was experimenting with a dozen new users, and the docs say that as
> soon
>> > as
>> >> a windows user logs in, a 'My Reports' folder should be created. I
> tried
>> >> both creating a 'System Role' for the user and *not* creating a system
>> > role,
>> >> but the creation of the 'My Reports' folder seems to occur randomly.
>> >> I
>> > say
>> >> *seem*, as I am sure that I am missing a step or something, as I
> confess
>> >> that I find the role assignment somewhat confusing. But I'm new, and
>> >> I
>> >> am
>> >> sure I'll get used to it...
>> >>
>> >>
>> >> In response to your kindly answer:
>> >> So, from my limited understanding, the 'My Reports' role is an
>> > 'item-level'
>> >> role; I can only seem to assign to this at the report level (ie an
>> >> 'item')
>> >> *not* to a user. But if the 'My Reports' folder is not being created,
> I
>> >> can't assign this 'tiem-level' role to it!
>> >>
>> >>
>> >> In short: I am still stuck with some users having a 'My Reports'
>> >> folder
>> > and
>> >> most users *not* having one!
>> >> Thanks,
>> >> Lance
>> >>
>> >>
>> >>
>> >>
>> >>
>> >> > Lance,
>> >> >
>> >> > Have you checked to ensure all users are are included in the "My
>> > Reports"
>> >> > role - or in some custom role that includes the required
>> >> > permissions.
>> >> >
>> >> > Regards,
>> >> >
>> >> >
>> >> > --
>> >> > Rob Labbé, MCP, MCAD, MCSD, MCT
>> >> > Lead Architect/Trainer
>> >> > Fidelis
>> >> >
>> >> > Blog: http://spaces.msn.com/members/roblabbe
>> >> >
>> >> > "Lance" <lance@.[nospam]keayweb.com> wrote in message
>> >> > news:11849vnplj1n02@.corp.supernews.com...
>> >> > > Hi,
>> >> > > I am having some trouble getting the 'My Reports' folder to appear
>> >> > > for
>> >> all
>> >> > > users. The docs just say that as long as the user exists as a
>> >> > > windows
>> >> > > user
>> >> > > and the 'My Reports' option is ticked in 'Site Settings',
>> >> > > ReportServer
>> >> > > should just create a 'My Reports' folder the first time a user
>> >> > > logs
>> > onto
>> >> > > the
>> >> > > ReportServer. But this is not working for all users. About half
> of
>> > the
>> >> > > users have the 'My Reports' folder created. So I need to know how
> to
>> >> > > create
>> >> > > them manually, or at least some workaround to get each user a 'My
>> >> Reports'
>> >> > > folder!
>> >> > >
>> >> > > I don't see any difference between the user accts (other than the
>> >> names).
>> >> > >
>> >> > > Thanks,
>> >> > > Lance
>> >> > >
>> >> > >
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>> >
>>
>|||Hi,
Thanks for the reply, but I don't understand how to get each user to call
this method...
** a few minutes pass ...**
Ok, here is what I did to get it to work:
I logged in as a user that had a working 'My Reports' folder
I copied the URL
(http://webstats.co.uk/Reports/Pages/Folder.aspx?ItemPath=%2fMy+Reports&IsDe
tailsView=False)
I then logged in as a user that was having a problem
I then pasted the preceeding URL and loaded it.
Then using the admin user, put some reports into the 'My Reports' folder of
the problem user.
And, voila! Now they have their own 'My Reports' folder! Woo hoo!
Thanks for the help,
Lance
"Daniel Reib (MSFT)" <danreib@.online.microsoft.com> wrote in message
news:#t70OrDWFHA.3424@.TK2MSFTNGP09.phx.gbl...
> When My Reports is enabled, ListChildren will always return a folder named
> 'My Reports', even if it has not been created for that user. That is why
it
> will show up in Report Manager. The first time a user calls any soap
method
> that uses the My Reports folder, RS will create the corresponding folder
in
> the user directory that the My Reports folder points to. If you have each
> user just call ListChildren on '/My Reports' then each user will have the
> 'My Reports' folder created.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "Lance" <lance@.[nospam]keayweb.com> wrote in message
> news:1189iffai01fh76@.corp.supernews.com...
> > Hi,
> > Thanks for the reply but if the user can't see the folder, how can they
> > browse to it? As is explained further on in the text, some users seem
to
> > get them created automatically, while others don't. So I need either a
> > way
> > to create the folder manually (some SQL?) or an explanation of the user
> > differences as to why a folder would be created for one user but not for
> > another...
> >
> > Thanks,
> > Lance
> >
> >
> > "Daniel Reib (MSFT)" <danreib@.online.microsoft.com> wrote in message
> > news:#cE3Af8VFHA.1200@.TK2MSFTNGP14.phx.gbl...
> >> The my reports folder will not be created until the user browses to
that
> >> folder. (Yes, I know, a little confusing) The first time they browse
to
> > it
> >> RS will create the folder and assign the My Reports permission to it.
> >>
> >> --
> >> -Daniel
> >> This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> >>
> >>
> >> "Lance" <lance@.[nospam]keayweb.com> wrote in message
> >> news:1188kvr7cuimc7a@.corp.supernews.com...
> >> > Hi,
> >> > I was just nosing around the DB, and I noticed that all the users are
> >> > in
> >> > the
> >> > 'users' table, but the user accounts don't appear in the
> > 'PolicyUserRole'
> >> > table, and hence there are not any policies for them.
> >> >
> >> > I don't know if this helps any or just confuses...
> >> >
> >> > And there aren't any application events in the log on the server,
> > either.
> >> >
> >> >
> >> > "Lance" <lance@.[nospam]keayweb.com> wrote in message
> >> > news:11869vtmjhs8i08@.corp.supernews.com...
> >> >> Hi,
> >> >> Thanks for the reply, but I am a bit confused about it! Maybe if I
> >> > explain
> >> >> a bit more about the setup: The ReportServer is set up with no
common
> >> >> reports. Each user can *only* view the reports in their own 'My
> > Reports'
> >> >> folder. As such each user *must* have a 'My Reports' folder!
> >> >> (obviously)
> >> >>
> >> >> Here is how the server is set up:
> >> >> * There are the standard 'Item-level' roles and an additional one,
> >> >> 'Viewer' - which is a limited acct, that just allows the user to
'view
> >> >> folders' and 'view reports'. None of the non-admin users can create
> >> >> reports.
> >> >> * The 'System Roles' are the standard 'System Administrator' and
> > 'System
> >> >> User' - the 'System User' has the 'View report server properties'
and
> >> > 'View
> >> >> shared schedules' tasks.
> >> >> * The 'Enable My Reports to support user-owned folders for
publishing
> > and
> >> >> running personalized reports.' checkbox is checked in the 'Reporst
> >> >> Server'
> >> >> 'Site Settings'. The 'Choose the role to apply to each user's My
> > Reports
> >> >> folder' dropdown is 'Viewer'
> >> >>
> >> >> To set up for a new user:
> >> >> * Each new user is added to the 'Users' group on the server (windows
> > 2000
> >> >> server).
> >> >> * The user logs in, and a 'My Reports' folder should be created.
> >> >>
> >> >> But mostly, a 'My Reports' folder is *not* created. Or perhaps just
> > not
> >> >> shown. I don't know how to check whether there is a 'My Reports'
> > folder
> >> > for
> >> >> the user - I am thinking that there is a value in the DB somewhere
> >> >> that
> >> >> tells me...
> >> >>
> >> >> I was experimenting with a dozen new users, and the docs say that as
> > soon
> >> > as
> >> >> a windows user logs in, a 'My Reports' folder should be created. I
> > tried
> >> >> both creating a 'System Role' for the user and *not* creating a
system
> >> > role,
> >> >> but the creation of the 'My Reports' folder seems to occur randomly.
> >> >> I
> >> > say
> >> >> *seem*, as I am sure that I am missing a step or something, as I
> > confess
> >> >> that I find the role assignment somewhat confusing. But I'm new,
and
> >> >> I
> >> >> am
> >> >> sure I'll get used to it...
> >> >>
> >> >>
> >> >> In response to your kindly answer:
> >> >> So, from my limited understanding, the 'My Reports' role is an
> >> > 'item-level'
> >> >> role; I can only seem to assign to this at the report level (ie an
> >> >> 'item')
> >> >> *not* to a user. But if the 'My Reports' folder is not being
created,
> > I
> >> >> can't assign this 'tiem-level' role to it!
> >> >>
> >> >>
> >> >> In short: I am still stuck with some users having a 'My Reports'
> >> >> folder
> >> > and
> >> >> most users *not* having one!
> >> >> Thanks,
> >> >> Lance
> >> >>
> >> >>
> >> >>
> >> >>
> >> >>
> >> >> > Lance,
> >> >> >
> >> >> > Have you checked to ensure all users are are included in the "My
> >> > Reports"
> >> >> > role - or in some custom role that includes the required
> >> >> > permissions.
> >> >> >
> >> >> > Regards,
> >> >> >
> >> >> >
> >> >> > --
> >> >> > Rob Labbé, MCP, MCAD, MCSD, MCT
> >> >> > Lead Architect/Trainer
> >> >> > Fidelis
> >> >> >
> >> >> > Blog: http://spaces.msn.com/members/roblabbe
> >> >> >
> >> >> > "Lance" <lance@.[nospam]keayweb.com> wrote in message
> >> >> > news:11849vnplj1n02@.corp.supernews.com...
> >> >> > > Hi,
> >> >> > > I am having some trouble getting the 'My Reports' folder to
appear
> >> >> > > for
> >> >> all
> >> >> > > users. The docs just say that as long as the user exists as a
> >> >> > > windows
> >> >> > > user
> >> >> > > and the 'My Reports' option is ticked in 'Site Settings',
> >> >> > > ReportServer
> >> >> > > should just create a 'My Reports' folder the first time a user
> >> >> > > logs
> >> > onto
> >> >> > > the
> >> >> > > ReportServer. But this is not working for all users. About
half
> > of
> >> > the
> >> >> > > users have the 'My Reports' folder created. So I need to know
how
> > to
> >> >> > > create
> >> >> > > them manually, or at least some workaround to get each user a
'My
> >> >> Reports'
> >> >> > > folder!
> >> >> > >
> >> >> > > I don't see any difference between the user accts (other than
the
> >> >> names).
> >> >> > >
> >> >> > > Thanks,
> >> >> > > Lance
> >> >> > >
> >> >> > >
> >> >> >
> >> >> >
> >> >>
> >> >>
> >> >
> >> >
> >> >
> >>
> >>
> >
> >
>|||Yes, this is one way. You could also make any SOAP call to the My Reports
folder and this will cause their My Reports folder to be created.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Lance" <lance@.[nospam]keayweb.com> wrote in message
news:118juil68hh0sc7@.corp.supernews.com...
> Hi,
> Thanks for the reply, but I don't understand how to get each user to call
> this method...
> ** a few minutes pass ...**
> Ok, here is what I did to get it to work:
> I logged in as a user that had a working 'My Reports' folder
> I copied the URL
> (http://webstats.co.uk/Reports/Pages/Folder.aspx?ItemPath=%2fMy+Reports&IsDe
> tailsView=False)
> I then logged in as a user that was having a problem
> I then pasted the preceeding URL and loaded it.
> Then using the admin user, put some reports into the 'My Reports' folder
> of
> the problem user.
> And, voila! Now they have their own 'My Reports' folder! Woo hoo!
> Thanks for the help,
> Lance
>
> "Daniel Reib (MSFT)" <danreib@.online.microsoft.com> wrote in message
> news:#t70OrDWFHA.3424@.TK2MSFTNGP09.phx.gbl...
>> When My Reports is enabled, ListChildren will always return a folder
>> named
>> 'My Reports', even if it has not been created for that user. That is why
> it
>> will show up in Report Manager. The first time a user calls any soap
> method
>> that uses the My Reports folder, RS will create the corresponding folder
> in
>> the user directory that the My Reports folder points to. If you have
>> each
>> user just call ListChildren on '/My Reports' then each user will have the
>> 'My Reports' folder created.
>> --
>> -Daniel
>> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>>
>> "Lance" <lance@.[nospam]keayweb.com> wrote in message
>> news:1189iffai01fh76@.corp.supernews.com...
>> > Hi,
>> > Thanks for the reply but if the user can't see the folder, how can they
>> > browse to it? As is explained further on in the text, some users seem
> to
>> > get them created automatically, while others don't. So I need either a
>> > way
>> > to create the folder manually (some SQL?) or an explanation of the user
>> > differences as to why a folder would be created for one user but not
>> > for
>> > another...
>> >
>> > Thanks,
>> > Lance
>> >
>> >
>> > "Daniel Reib (MSFT)" <danreib@.online.microsoft.com> wrote in message
>> > news:#cE3Af8VFHA.1200@.TK2MSFTNGP14.phx.gbl...
>> >> The my reports folder will not be created until the user browses to
> that
>> >> folder. (Yes, I know, a little confusing) The first time they browse
> to
>> > it
>> >> RS will create the folder and assign the My Reports permission to it.
>> >>
>> >> --
>> >> -Daniel
>> >> This posting is provided "AS IS" with no warranties, and confers no
>> > rights.
>> >>
>> >>
>> >> "Lance" <lance@.[nospam]keayweb.com> wrote in message
>> >> news:1188kvr7cuimc7a@.corp.supernews.com...
>> >> > Hi,
>> >> > I was just nosing around the DB, and I noticed that all the users
>> >> > are
>> >> > in
>> >> > the
>> >> > 'users' table, but the user accounts don't appear in the
>> > 'PolicyUserRole'
>> >> > table, and hence there are not any policies for them.
>> >> >
>> >> > I don't know if this helps any or just confuses...
>> >> >
>> >> > And there aren't any application events in the log on the server,
>> > either.
>> >> >
>> >> >
>> >> > "Lance" <lance@.[nospam]keayweb.com> wrote in message
>> >> > news:11869vtmjhs8i08@.corp.supernews.com...
>> >> >> Hi,
>> >> >> Thanks for the reply, but I am a bit confused about it! Maybe if I
>> >> > explain
>> >> >> a bit more about the setup: The ReportServer is set up with no
> common
>> >> >> reports. Each user can *only* view the reports in their own 'My
>> > Reports'
>> >> >> folder. As such each user *must* have a 'My Reports' folder!
>> >> >> (obviously)
>> >> >>
>> >> >> Here is how the server is set up:
>> >> >> * There are the standard 'Item-level' roles and an additional one,
>> >> >> 'Viewer' - which is a limited acct, that just allows the user to
> 'view
>> >> >> folders' and 'view reports'. None of the non-admin users can
>> >> >> create
>> >> >> reports.
>> >> >> * The 'System Roles' are the standard 'System Administrator' and
>> > 'System
>> >> >> User' - the 'System User' has the 'View report server properties'
> and
>> >> > 'View
>> >> >> shared schedules' tasks.
>> >> >> * The 'Enable My Reports to support user-owned folders for
> publishing
>> > and
>> >> >> running personalized reports.' checkbox is checked in the 'Reporst
>> >> >> Server'
>> >> >> 'Site Settings'. The 'Choose the role to apply to each user's My
>> > Reports
>> >> >> folder' dropdown is 'Viewer'
>> >> >>
>> >> >> To set up for a new user:
>> >> >> * Each new user is added to the 'Users' group on the server
>> >> >> (windows
>> > 2000
>> >> >> server).
>> >> >> * The user logs in, and a 'My Reports' folder should be created.
>> >> >>
>> >> >> But mostly, a 'My Reports' folder is *not* created. Or perhaps
>> >> >> just
>> > not
>> >> >> shown. I don't know how to check whether there is a 'My Reports'
>> > folder
>> >> > for
>> >> >> the user - I am thinking that there is a value in the DB somewhere
>> >> >> that
>> >> >> tells me...
>> >> >>
>> >> >> I was experimenting with a dozen new users, and the docs say that
>> >> >> as
>> > soon
>> >> > as
>> >> >> a windows user logs in, a 'My Reports' folder should be created. I
>> > tried
>> >> >> both creating a 'System Role' for the user and *not* creating a
> system
>> >> > role,
>> >> >> but the creation of the 'My Reports' folder seems to occur
>> >> >> randomly.
>> >> >> I
>> >> > say
>> >> >> *seem*, as I am sure that I am missing a step or something, as I
>> > confess
>> >> >> that I find the role assignment somewhat confusing. But I'm new,
> and
>> >> >> I
>> >> >> am
>> >> >> sure I'll get used to it...
>> >> >>
>> >> >>
>> >> >> In response to your kindly answer:
>> >> >> So, from my limited understanding, the 'My Reports' role is an
>> >> > 'item-level'
>> >> >> role; I can only seem to assign to this at the report level (ie an
>> >> >> 'item')
>> >> >> *not* to a user. But if the 'My Reports' folder is not being
> created,
>> > I
>> >> >> can't assign this 'tiem-level' role to it!
>> >> >>
>> >> >>
>> >> >> In short: I am still stuck with some users having a 'My Reports'
>> >> >> folder
>> >> > and
>> >> >> most users *not* having one!
>> >> >> Thanks,
>> >> >> Lance
>> >> >>
>> >> >>
>> >> >>
>> >> >>
>> >> >>
>> >> >> > Lance,
>> >> >> >
>> >> >> > Have you checked to ensure all users are are included in the "My
>> >> > Reports"
>> >> >> > role - or in some custom role that includes the required
>> >> >> > permissions.
>> >> >> >
>> >> >> > Regards,
>> >> >> >
>> >> >> >
>> >> >> > --
>> >> >> > Rob Labbé, MCP, MCAD, MCSD, MCT
>> >> >> > Lead Architect/Trainer
>> >> >> > Fidelis
>> >> >> >
>> >> >> > Blog: http://spaces.msn.com/members/roblabbe
>> >> >> >
>> >> >> > "Lance" <lance@.[nospam]keayweb.com> wrote in message
>> >> >> > news:11849vnplj1n02@.corp.supernews.com...
>> >> >> > > Hi,
>> >> >> > > I am having some trouble getting the 'My Reports' folder to
> appear
>> >> >> > > for
>> >> >> all
>> >> >> > > users. The docs just say that as long as the user exists as a
>> >> >> > > windows
>> >> >> > > user
>> >> >> > > and the 'My Reports' option is ticked in 'Site Settings',
>> >> >> > > ReportServer
>> >> >> > > should just create a 'My Reports' folder the first time a user
>> >> >> > > logs
>> >> > onto
>> >> >> > > the
>> >> >> > > ReportServer. But this is not working for all users. About
> half
>> > of
>> >> > the
>> >> >> > > users have the 'My Reports' folder created. So I need to know
> how
>> > to
>> >> >> > > create
>> >> >> > > them manually, or at least some workaround to get each user a
> 'My
>> >> >> Reports'
>> >> >> > > folder!
>> >> >> > >
>> >> >> > > I don't see any difference between the user accts (other than
> the
>> >> >> names).
>> >> >> > >
>> >> >> > > Thanks,
>> >> >> > > Lance
>> >> >> > >
>> >> >> > >
>> >> >> >
>> >> >> >
>> >> >>
>> >> >>
>> >> >
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>

My report doesn't appear WYSWYG

I have a data field(text) which doesn't appear as what I see in preview. How
can I display the text with paragraphs?
I see squares between words from data I retrieved. I guess that is the
something indicates new line or new paragraph. In preview, the text is
properly displayed in different lines/paragraphs. But when I built ad
deployed it, the text becomes cramp into one paragraph. This reduce the
readability.
Appreciate if someone could give me an idea on where to set the format or
cast the data field. Thank you.Still couldn't solve my problem. Anyone can help?
More elaboration on this problem: -
When I preview I see the data field display nicely like below (eg.):
Stamp[xxx]
Subject: Server not up after rebooting
Assigned to mho3.
This has moderate impact to customer site.
--Then, after I deployed it, I browse it via IE and unfortunately I couldn't
get what I see in Preview. It gives me result like this:
Stamp[xxx] Subject: Server not up after rebooting Assigned to mho3. This has
moderate impact to customer site.
--Just imagine some have long data. It will be difficult to read.
Any help is highly appreciated. Thanks!
"sammy" wrote:
> I have a data field(text) which doesn't appear as what I see in preview. How
> can I display the text with paragraphs?
> I see squares between words from data I retrieved. I guess that is the
> something indicates new line or new paragraph. In preview, the text is
> properly displayed in different lines/paragraphs. But when I built ad
> deployed it, the text becomes cramp into one paragraph. This reduce the
> readability.
> Appreciate if someone could give me an idea on where to set the format or
> cast the data field. Thank you.
>|||Where do you get the data from? Is the new line formating something you get
by modifying fields from different columns in the database, or is it one
field that has some format in one column in your database?
Hilary Cotter suggested in a different thread that you use char(10) +
char(13) in your SQL select to get the line breaks and new lines directly
from the database. Like this:
SELECT @.a = '123' +char(10)+char(13)+ 'xyz'
I don't know why it works in preview but not on the server, though. Guess
it's the rendering format that is different. What happens when you export to
pdf on your server? Does it go all on one line or does the lines break?
Kaisa M. Lindahl Lervik
"sammy" <sammy@.discussions.microsoft.com> wrote in message
news:7D920AFA-1350-44A0-ABA9-1AA9CD71D65E@.microsoft.com...
> Still couldn't solve my problem. Anyone can help?
> More elaboration on this problem: -
> When I preview I see the data field display nicely like below (eg.):
> Stamp[xxx]
> Subject: Server not up after rebooting
> Assigned to mho3.
> This has moderate impact to customer site.
> --Then, after I deployed it, I browse it via IE and unfortunately I
> couldn't
> get what I see in Preview. It gives me result like this:
> Stamp[xxx] Subject: Server not up after rebooting Assigned to mho3. This
> has
> moderate impact to customer site.
> --Just imagine some have long data. It will be difficult to read.
> Any help is highly appreciated. Thanks!
>
>
> "sammy" wrote:
>> I have a data field(text) which doesn't appear as what I see in preview.
>> How
>> can I display the text with paragraphs?
>> I see squares between words from data I retrieved. I guess that is the
>> something indicates new line or new paragraph. In preview, the text is
>> properly displayed in different lines/paragraphs. But when I built ad
>> deployed it, the text becomes cramp into one paragraph. This reduce the
>> readability.
>> Appreciate if someone could give me an idea on where to set the format or
>> cast the data field. Thank you.|||Teng! Teng! Teng! The answer is:
=Replace(Fields!Activity_Log.Value,CHR(10),vbCrLf)
I found that those special character "squares" is represented by CHR(10) -
Line feed, from my try and error.
And in VB.NET, vbCrLf indicates new line as in "\n" for C.
Kaisa,
I got my data in a single field (not combining multiple fields), and that
field type is Text. I cast it to string so I found "squares" between words.
Before I applied the Replace function, I tried to export my report to PDF and
other format, it's just the same, no break lines. After applying the Replace
function, it works fine.
Anyway, Thks a lot for your hints, Kaisa. :-)
best regards,
sammy