Showing posts with label successfully. Show all posts
Showing posts with label successfully. Show all posts

Wednesday, March 28, 2012

ReportViewer control and Aggregate RDL Expressions

I've been successfully using the WinForm ReportViewer control to view reports from relational databases. I'm starting to investigate viewing reports based on Analysis Services 2005 multi-dimensional sources.

From my reading (including the following thread http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1432935&SiteID=1), it seems that the RDL Aggregate function is key to getting proper results in expressions.

The RDL spec indicates that the Aggregate function behaviour is tightly coupled with the Data Processing Extension (DPE). This is fine for web-based Reporting Services, but with the ReportViewer control, we do all data processing and provide a .NET DataTable object that the viewer consumes and evaluates expressions from.

How does the WinForm ReportViewer handle evaluating the Aggregate function when there is no DPE to talk to?

Thanks!

This has nothing to do with "web based" versus "winform based". There are two versions of the ReportViewer control, one for web forms and one for win forms, and they both handle getting data from a report server versus handling the data and rendering locally (using an RDLC).

The answer to your question is that the local processing mode does data processing, IOW the internal calculation of values, including aggregates, should be the same as what you would get from server processing mode. So I'm not sure why you're worried about this.

I think you might be a little confused about what a Data Processing Extension is compared to the part that I'm saying is also done by the local mode. One is "what am I going to do to get data out of a data source". The other is "once the data is held in datasets as resources for the reports, how am I going to process and calculate expressions on those rows". The aggregate functions belong in the latter set, so I would say, no, there is no tight coupling that could concern you.

>L<

|||

Indeed, I am confused Smile

Here's a more concise description of my scenario: a WinForm ReportViewer control running in local processing mode. The hosting application populates the ReportViewer.LocalReport.DataSources on the ReportViewer's behalf, the viewer renders the report.

The "tight coupling" I worry about is regarding http://technet.microsoft.com/en-us/library/ms155830.aspx, i.e., the Aggregate function "returns a custom aggregate of the specified expression, as defined by the data provider" (my emphasis).

The way I understand this, the report processor consults the Data Processing Extension for help in interpreting the result set via the DPE's IDataReaderExtension interface, if implemented (http://msdn2.microsoft.com/en-us/library/microsoft.reportingservices.dataprocessing.idatareaderextension.aspx). Please correct me if I'm wrong.

In my local processing mode scenario, the ReportViewer doesn't consult any data provider to get aggregate information, so I don't understand how it figures out values for the Aggregate function.

First question: what exactly is IOW? Smile

Second question: how does the local report processing figure out the RDL Aggregate function? I understand simple aggregates like Sum & Count, but what about the Aggregate aggregate function.

|||

IOW = "in other words".

I think (re: your emphasis) that you are misinterpreting what you are reading. How exactly do you think the custom aggregate comes into place and is not available to you in an RDLC, where your code is the data provider <s>?

It is not really true that "the ReportViewer doesn't consult any data provider". When it's in local processing mode it's consulting the data provider that you gave it references to... It is asking this provider for rows in whatever way is necessary to evaluate content and evaluate expressions during the processing of the report.

Am I still missing whatever it is that is concerning you?

>L<

|||

So, I am the data provider because I provide the data. Fair enough Smile

In the ReportViewer local processing case, I fetch data in my own code, and I execute the query and populate a System.Data.DataTable and pass that into the ReportViewer. Here's code snippet:

string dataSetName = "dataSetName"; //The name of the RDL Data Set in the Report

System.Data.DataSet data = ExecuteQueryAndGetData(); //My method to execute the query

_reportViewer.LocalReport.DataSources.Add(new ReportDataSource(dataSetName, data.Tables[0]));

So the data fetching code runs, gets the data rows, and then I toss it into the ReportViewer.

Now, consider the following rowset, which I copied from http://msdn2.microsoft.com/en-us/library/microsoft.reportingservices.dataprocessing.idatareaderextension.isaggregaterow.aspx:

City State Sales
====================================
Seattle WA 150.65
Seattle (null) 150.65
Tacoma WA 75.54
Tacoma (null) 75.54
(null) WA 226.19
Portland OR 112.25
Portland (null) 112.25
(null) OR 112.25
(null) (null) 338.44

The rows in blue have aggregates. From what I understand, RS would need assistance from the DPE in order to understand that the following result set includes aggregates, via the IDataReaderExtension interface (that's what the documentation page is talking about).

I understand that the RS's OLEDB-MD DPE does just this by implementing the IDataReaderExtension interface that I mentioned before.

I don't implement the DPE interfaces in my application at all -- I only give the ReportViewer a result set of rows in a DataTable. So, if I am a data provider for an Analysis Services data source (of course I'm not OLEDB-MD), how exactly do I tell the ReportViewer which rows/fields contain aggregates?

Can ReportViewer really figure this out from the DataTable rowset alone?

|||

>>

I don't implement the DPE interfaces in my application at all -- I only give the ReportViewer a result set of rows in a DataTable. So, if I am a data provider for an Analysis Services data source (of course I'm not OLEDB-MD), how exactly do I tell the ReportViewer which rows/fields contain aggregates?

<<

If YOU don't provide a source with custom aggregation, it is not going to see custom aggregation.

Look: I don't know for sure that ReportViewer's data processing implements IDataReaderExtension. If you look, I think you may see that you can't set a data source using the the analysis services provider in local mode (I went through the wizard and tried -- it isn't showing there although I have the drivers etc). This may just mean that they are ensuring that, when they do their behind-the-scenes wizard-binding to a plain-vanilla DataTable, the ReportViewer does not have deal with it <shrug>.

OR -- and this is equally likely, in fact more so because I doubt they re-implemented the whole data processing engine just for ReportViewer -- the local processing engine may in fact implement IDataReaderExtension but DataTable, their out-of-the-box host, isn't up to the task so it's not in the wizard and the out-of-the-box binding stuff.

Pay attention to the line of code you quoted:

new ReportDataSource(dataSetName, data.Tables[0]));

Look at the constructor for the object you just created. Where does it say anything about the second arg being a DataTable?

An RDLC can use an object data source.

An object data source can contain nested data (see http://blogs.msdn.com/tudortr/archive/2006/04/10/NestedObjectsDataSource.aspx for an example). If you can nest, you can provide aggregate functionality for what you have nested.. you see where I'm going right?

>L<

sql

Monday, March 26, 2012

ReportViewer (ASP.NET) and Forms Authentication

I have successfully implemented forms authentication in RS. It works great to navigate to https://myserver/Reports. I get redirected to the login-page etc. It also works great to use the ReportViewer from within my WinForms app, using the

reportViewer.ServerReport.ReportServerCredentials.SetFormsCredentials

method. I'm also able to communicate with RS through it's web services by first calling the 'LogonUser' method. However, to get that to work I had to inherit the ReportExecutionService for the cookie-handling to work.

Now over to my problem (the only thing so far I haven't got working). I'm trying to use a ReportViewer on an ASP.net web page to access the reports. I have understood that 'SetFormsCredentials' is not avaliable here. Instead, as far as I have understood, I should implement my own IReportServerCredentials . The critical method there I guess is

GetFormsCredentials(out Cookie authCookie, out string formsUser, out string formsPassword, out string formsAuthority)

, am I right?

It's here my problem starts. The examples I have seen haven't implemented this method since they use other types of authentication. So, I would need an example of what to fill that method with...

Regards Andreas

Is there at least some documentation where I can read about this...?

(Can't find any examples on it...)

|||

After some more testing, I'm now able to render a report. This is possible if I implement the method like this:

public bool GetFormsCredentials(out Cookie authCookie, out string user, out string password, out string authority)

{

authCookie = null;

user = "validUser";

password = "validPassword";

authority = "";

return true;

}

But from what I can understand this method should be returning a cookie...

And I have done testings with the method described here: http://blogs.msdn.com/bimusings/archive/2005/11/04/489100.aspx, but if I return something else then null as cookie-Value, I get an exception like this:

Client found response content type of 'text/html; charset=utf-8', but expected 'text/xml'.....

So, if I return null as cookie-value I'm able to render the report, but I run into trouble when clicking on a link in the report that navigates to another report. The report isn't rendered, instead the message

  • Execution 'lvzt54q0lq1nrbjjmmubr02b' cannot be found
    is shown.

    Any kind of help here would be of great value!

    Regards Andreas

  • Friday, March 23, 2012

    ReportServer Web Service Problem

    Hi,
    We're getting some really odd behavior from ReportServer calls (e.g.
    CreateReport, CreateDataSource). They all complete successfully, but
    SOMETIMES create no objects! (at least they are not visible in the Report
    Manager and Report Server). It looks like something must "committed" in order
    to make changes permanent, I don't know. The behavior is really strange,
    because it happens only from time to time, maybe every 5th run in a row (just
    by running the same code multiple times and deleting objects between runs).
    This is on SQL Express 2005 (haven't tried on other editions, because we
    need the free one).
    The sequence is as follows:
    ReportServer.CreateFolder("test", "/", null);
    ReportServer.CreateDataSource("testds", "/test", true, dsdef, null);
    ReportServer.CreateReport("testrep", "/test", true, binaryRdl, null);
    Report server calls are made through the stub generated from WSDL file taken
    from the same server.
    Any ideas why this could happen? Maybe servers transactions must be
    committed somehow? Or do we need to close report server connection, or call
    Logoff method?
    Thanks,
    MikeMore info on this issue. When I hit refresh in IE in the Report Manager, the
    newly created item is not listed, but when I hit F5 multiple times, it
    sometimes dumps the following: The item '/test' cannot be found.
    (rsItemNotFound). "/test" - is the object that is "half-created" through
    CreateFolder call. Looks like a bug in SRS, right?|||Looks like SRS maintains a sort of internal cache for deleted objects and all
    management functions (Create*) get stuck on this cache. The workaround for
    the issue I explained above is to wait "a little bit" (like 30-50 seconds),
    until deleted objects finally "disappear". Microsoft should address this
    issue in their future releases.

    Wednesday, March 21, 2012

    Reports work, Reports Manager not

    I've done a number of RS installs, this one is on a new 2003 server. I have
    VS.NET on the computer and can develop and deploy successfully.
    I can view the report from VS.NET preview or using IE, typing
    http://localhost/ReportServer?/yada/yada
    However if I want to run Reports Manager, typing http://localhost/Reports
    then I get an error,
    Access denied to 'C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
    Services\ReportManager\BIN'. Failed to start monitoring file changes.
    I've changed permissions on the folder and the ReportServer service is now
    started with the Admin account, no success yet.It is fixed now. This is what I did in case it can help someone in the
    future.
    Un-install Reporting Services.
    Use Enterprise Manager to delete the 2 Reporting Services databases, the
    un-install did not.
    Delete the C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services
    directory and sub-directories.
    Install again.
    "Chris Botha" <chris_s_botha@.AThotmail.com> wrote in message
    news:#5EIp0$HFHA.2564@.tk2msftngp13.phx.gbl...
    > I've done a number of RS installs, this one is on a new 2003 server. I
    have
    > VS.NET on the computer and can develop and deploy successfully.
    > I can view the report from VS.NET preview or using IE, typing
    > http://localhost/ReportServer?/yada/yada
    > However if I want to run Reports Manager, typing http://localhost/Reports
    > then I get an error,
    > Access denied to 'C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
    > Services\ReportManager\BIN'. Failed to start monitoring file changes.
    > I've changed permissions on the folder and the ReportServer service is now
    > started with the Admin account, no success yet.
    >

    Monday, March 12, 2012

    Reports deployed to Standard edition RS and SQL Server

    Hello,
    We have successfully developed the reports on our testing machine with SQL Server 2000 Enterprise + Reporting Services 2000 Enterprise. Is there any problem if we deploy the reports to the production machine that runs SQL Server 2000 Standard and Reporting Services 2000 Standard?

    Thanks in advance!

    Here is some info on licensing:

    http://msmvps.com/blogs/anguslogan/archive/2004/10/03/14788.aspx

    The report RDL files should be the same in both environments so you should not have any problems.

    cheers,

    Andrew

    Wednesday, March 7, 2012

    ReportIoError 2 - any ideas?

    Problem:
    The following SQL is scheduled to run via Enterprise
    manager. It has been successfully running for months.
    BACKUP LOG [Proclaim] TO DISK = N'D:\mssql7
    \backup\Proclaim_backup.bak' WITH NOINIT , NOUNLOAD ,
    NAME = N'Proclaim backup', SKIP , STATS = 10, NOFORMAT
    Friday night the job failed with the log reporting:
    BackupMedium::ReportIOError: write failure on backup
    device 'D:\mssql7\backup\Proclaim_backup.bak'.
    Operating system error 2 (error not found).
    To add to the problem, the log file is now full and there
    is limitted space in this D: on the server.
    Configuration:
    Operating System: Windows 2000 Server sp3
    Full recovery mode.
    SQL Server 2000 sp 3 (upgraded from SQL 7 12 months ago)
    Hardward Raid (1) on C: (and 500MB free)
    Hardware Raid (5) on D: (and 5Gb free)
    Hardware Raid (1) on E: (and 6Gb free)
    ..mdf file = 4491MB allocated (autogrow)
    ..log file = 2101MB allocated (at limit)
    ..bak file = 4110MB
    Attempted Action so far:
    * Invoking log backup from Enterprise manager:
    - to same location -> failed with same error
    - to E:\ -> failed with ReportIoError 112
    * Retried above after deleting existing file
    D:\mssql7\backup\Proclaim_backup.bak' - no change
    * Retried above after changing to bulk recovery mode.
    - to same location -> failed with same error
    - to E: -> successful, but log file still the same size
    I've read Knowledge base article 325334; but since the
    striping is hardware based on all available drives - see
    no means of utilising this information.
    Any suggestions would be greatly appreciated.
    I don't know where D:\ and E:\ are, but have you tried backing up to a
    different machine?
    http://www.aspfaq.com/
    (Reverse address to reply.)
    "Kim" <anonymous@.discussions.microsoft.com> wrote in message
    news:2631501c46229$3f11d150$a401280a@.phx.gbl...
    > Problem:
    > --
    > The following SQL is scheduled to run via Enterprise
    > manager. It has been successfully running for months.
    > BACKUP LOG [Proclaim] TO DISK = N'D:\mssql7
    > \backup\Proclaim_backup.bak' WITH NOINIT , NOUNLOAD ,
    > NAME = N'Proclaim backup', SKIP , STATS = 10, NOFORMAT
    > Friday night the job failed with the log reporting:
    > BackupMedium::ReportIOError: write failure on backup
    > device 'D:\mssql7\backup\Proclaim_backup.bak'.
    > Operating system error 2 (error not found).
    >
    > To add to the problem, the log file is now full and there
    > is limitted space in this D: on the server.
    >
    > Configuration:
    > --
    > Operating System: Windows 2000 Server sp3
    > Full recovery mode.
    > SQL Server 2000 sp 3 (upgraded from SQL 7 12 months ago)
    > Hardward Raid (1) on C: (and 500MB free)
    > Hardware Raid (5) on D: (and 5Gb free)
    > Hardware Raid (1) on E: (and 6Gb free)
    > .mdf file = 4491MB allocated (autogrow)
    > .log file = 2101MB allocated (at limit)
    > .bak file = 4110MB
    >
    > Attempted Action so far:
    > --
    > * Invoking log backup from Enterprise manager:
    > - to same location -> failed with same error
    > - to E:\ -> failed with ReportIoError 112
    > * Retried above after deleting existing file
    > D:\mssql7\backup\Proclaim_backup.bak' - no change
    > * Retried above after changing to bulk recovery mode.
    > - to same location -> failed with same error
    > - to E: -> successful, but log file still the same size
    >
    > I've read Knowledge base article 325334; but since the
    > striping is hardware based on all available drives - see
    > no means of utilising this information.
    >
    > Any suggestions would be greatly appreciated.
    |||Sounds you are out of disk space on D drive. Take the
    backup of transaction log and shrink the .ldf. Don't you
    take frequent transaction log backups?. If you are
    concerned only about FULL and DIFFERENTIAL backups , you
    can set the Database to SIMPLE recovery, so the log will
    be truncated automatically.
    INF: Shrinking the Transaction Log in SQL Server 2000 with
    DBCC SHRINKFILE
    http://support.microsoft.com/default.aspx?scid=kb;EN-
    US;272318
    Also look for some other pointers which tells, why the log
    file grows rapidly.
    Finally I think u may need to buy some Hard disks.
    HTH
    Regards
    ~Thirumal~
    www.thirumal.com

    >--Original Message--
    >I don't know where D:\ and E:\ are, but have you tried
    backing up to a
    >different machine?
    >--
    >http://www.aspfaq.com/
    >(Reverse address to reply.)
    >
    >
    >"Kim" <anonymous@.discussions.microsoft.com> wrote in
    message[vbcol=seagreen]
    >news:2631501c46229$3f11d150$a401280a@.phx.gbl...
    NOFORMAT[vbcol=seagreen]
    there
    >
    >.
    >
    |||I've only ever backed up to the local PC. I use the
    Enterprise Manager GUI to generate the SQL as a scheduled
    job - then modify the schedule as required. The GUI will
    not list mapped network drives - so I'd assumed that it
    wasn't possible to back up to an external machine.
    In any case, Our only other Windows 2000 server has
    exactly the same RAID settings.
    I have got around the immediate problem by:
    * increasing the ldf file by 500MB
    * backing up the transaction log to the E: (which has Raid
    1).
    * using DBCC SHRINKFILE to reduce the ldf file size.
    * Deleting the file 'D:\mssql7\backup\Proclaim_backup.bak'
    * Rerunning the backups through Enterprise Manager.
    I still don't really understand what ReportIoError 2
    means. I can only assume that it is related somehow to
    the striping because of Q325334.
    As a consequence I've no idea whether the problem will
    continue and in what timeframe. (I work in an IT
    department of 3, and we support a number of disparate
    platforms. My colleagues know even less about SQL Server
    than I do - so I want to avoid this situation recurring in
    my absence).
    Thanks for your assistance.
    Kim.

    >--Original Message--
    >I don't know where D:\ and E:\ are, but have you tried
    backing up to a
    >different machine?
    >--
    >http://www.aspfaq.com/
    >(Reverse address to reply.)
    >
    >
    >"Kim" <anonymous@.discussions.microsoft.com> wrote in
    message[vbcol=seagreen]
    >news:2631501c46229$3f11d150$a401280a@.phx.gbl...
    NOFORMAT[vbcol=seagreen]
    there
    >
    >.
    >
    |||Thanks for the suggestions. It's hard to think of all the
    relevant info to post when asking the question.
    * The job that should back up the transaction log is the
    job that failed. Attempts to back up the transaction log
    via other means (as listed) also failed.
    * We have limited space on all 3 drives. On both D: and
    E: there is the equivalent of the existing database size
    free however (figures in original post).
    * The transaction log is backed up 6 times during a
    working day, and once each weekend.
    * Recovery mode is "full" except for when one scheduled
    script is run each weekend. It rebuilds indices so before
    it is run recovery mode is changed to "bulk logged". The
    log backup following this task was the first to fail.

    >--Original Message--
    >Sounds you are out of disk space on D drive. Take the
    >backup of transaction log and shrink the .ldf. Don't you
    >take frequent transaction log backups?. If you are
    >concerned only about FULL and DIFFERENTIAL backups , you
    >can set the Database to SIMPLE recovery, so the log will
    >be truncated automatically.
    >INF: Shrinking the Transaction Log in SQL Server 2000
    with
    >DBCC SHRINKFILE
    >http://support.microsoft.com/default.aspx?scid=kb;EN-
    >US;272318
    >Also look for some other pointers which tells, why the
    log[vbcol=seagreen]
    >file grows rapidly.
    >Finally I think u may need to buy some Hard disks.
    >HTH
    >--
    >Regards
    >~Thirumal~
    >www.thirumal.com
    >backing up to a
    >message
    >NOFORMAT
    >there
    ago)[vbcol=seagreen]
    see
    >.
    >

    ReportIoError 2 - any ideas?

    Problem:
    --
    The following SQL is scheduled to run via Enterprise
    manager. It has been successfully running for months.
    BACKUP LOG [Proclaim] TO DISK = N'D:\mssql7
    \backup\Proclaim_backup.bak' WITH NOINIT , NOUNLOAD ,
    NAME = N'Proclaim backup', SKIP , STATS = 10, NOFORMAT
    Friday night the job failed with the log reporting:
    BackupMedium::ReportIOError: write failure on backup
    device 'D:\mssql7\backup\Proclaim_backup.bak'.
    Operating system error 2 (error not found).
    To add to the problem, the log file is now full and there
    is limitted space in this D: on the server.
    Configuration:
    --
    Operating System: Windows 2000 Server sp3
    Full recovery mode.
    SQL Server 2000 sp 3 (upgraded from SQL 7 12 months ago)
    Hardward Raid (1) on C: (and 500MB free)
    Hardware Raid (5) on D: (and 5Gb free)
    Hardware Raid (1) on E: (and 6Gb free)
    .mdf file = 4491MB allocated (autogrow)
    .log file = 2101MB allocated (at limit)
    .bak file = 4110MB
    Attempted Action so far:
    --
    * Invoking log backup from Enterprise manager:
    - to same location -> failed with same error
    - to E:\ -> failed with ReportIoError 112
    * Retried above after deleting existing file
    D:\mssql7\backup\Proclaim_backup.bak' - no change
    * Retried above after changing to bulk recovery mode.
    - to same location -> failed with same error
    - to E: -> successful, but log file still the same size
    I've read Knowledge base article 325334; but since the
    striping is hardware based on all available drives - see
    no means of utilising this information.
    Any suggestions would be greatly appreciated.I don't know where D:\ and E:\ are, but have you tried backing up to a
    different machine?
    http://www.aspfaq.com/
    (Reverse address to reply.)
    "Kim" <anonymous@.discussions.microsoft.com> wrote in message
    news:2631501c46229$3f11d150$a401280a@.phx
    .gbl...
    > Problem:
    > --
    > The following SQL is scheduled to run via Enterprise
    > manager. It has been successfully running for months.
    > BACKUP LOG [Proclaim] TO DISK = N'D:\mssql7
    > \backup\Proclaim_backup.bak' WITH NOINIT , NOUNLOAD ,
    > NAME = N'Proclaim backup', SKIP , STATS = 10, NOFORMAT
    > Friday night the job failed with the log reporting:
    > BackupMedium::ReportIOError: write failure on backup
    > device 'D:\mssql7\backup\Proclaim_backup.bak'.
    > Operating system error 2 (error not found).
    >
    > To add to the problem, the log file is now full and there
    > is limitted space in this D: on the server.
    >
    > Configuration:
    > --
    > Operating System: Windows 2000 Server sp3
    > Full recovery mode.
    > SQL Server 2000 sp 3 (upgraded from SQL 7 12 months ago)
    > Hardward Raid (1) on C: (and 500MB free)
    > hardware Raid (5) on D: (and 5Gb free)
    > hardware Raid (1) on E: (and 6Gb free)
    > .mdf file = 4491MB allocated (autogrow)
    > .log file = 2101MB allocated (at limit)
    > .bak file = 4110MB
    >
    > Attempted Action so far:
    > --
    > * Invoking log backup from Enterprise manager:
    > - to same location -> failed with same error
    > - to E:\ -> failed with ReportIoError 112
    > * Retried above after deleting existing file
    > D:\mssql7\backup\Proclaim_backup.bak' - no change
    > * Retried above after changing to bulk recovery mode.
    > - to same location -> failed with same error
    > - to E: -> successful, but log file still the same size
    >
    > I've read Knowledge base article 325334; but since the
    > striping is hardware based on all available drives - see
    > no means of utilising this information.
    >
    > Any suggestions would be greatly appreciated.|||Sounds you are out of disk space on D drive. Take the
    backup of transaction log and shrink the .ldf. Don't you
    take frequent transaction log backups?. If you are
    concerned only about FULL and DIFFERENTIAL backups , you
    can set the Database to SIMPLE recovery, so the log will
    be truncated automatically.
    INF: Shrinking the Transaction Log in SQL Server 2000 with
    DBCC SHRINKFILE
    http://support.microsoft.com/default.aspx?scid=kb;EN-
    US;272318
    Also look for some other pointers which tells, why the log
    file grows rapidly.
    Finally I think u may need to buy some Hard disks.
    HTH
    --
    Regards
    ~Thirumal~
    www.thirumal.com

    >--Original Message--
    >I don't know where D:\ and E:\ are, but have you tried
    backing up to a
    >different machine?
    >--
    >http://www.aspfaq.com/
    >(Reverse address to reply.)
    >
    >
    >"Kim" <anonymous@.discussions.microsoft.com> wrote in
    message
    > news:2631501c46229$3f11d150$a401280a@.phx
    .gbl...
    NOFORMAT[vbcol=seagreen]
    there[vbcol=seagreen]
    >
    >.
    >|||I've only ever backed up to the local PC. I use the
    Enterprise Manager GUI to generate the SQL as a scheduled
    job - then modify the schedule as required. The GUI will
    not list mapped network drives - so I'd assumed that it
    wasn't possible to back up to an external machine.
    In any case, Our only other Windows 2000 server has
    exactly the same RAID settings.
    I have got around the immediate problem by:
    * increasing the ldf file by 500MB
    * backing up the transaction log to the E: (which has Raid
    1).
    * using DBCC SHRINKFILE to reduce the ldf file size.
    * Deleting the file 'D:\mssql7\backup\Proclaim_backup.bak'
    * Rerunning the backups through Enterprise Manager.
    I still don't really understand what ReportIoError 2
    means. I can only assume that it is related somehow to
    the striping because of Q325334.
    As a consequence I've no idea whether the problem will
    continue and in what timeframe. (I work in an IT
    department of 3, and we support a number of disparate
    platforms. My colleagues know even less about SQL Server
    than I do - so I want to avoid this situation recurring in
    my absence).
    Thanks for your assistance.
    Kim.

    >--Original Message--
    >I don't know where D:\ and E:\ are, but have you tried
    backing up to a
    >different machine?
    >--
    >http://www.aspfaq.com/
    >(Reverse address to reply.)
    >
    >
    >"Kim" <anonymous@.discussions.microsoft.com> wrote in
    message
    > news:2631501c46229$3f11d150$a401280a@.phx
    .gbl...
    NOFORMAT[vbcol=seagreen]
    there[vbcol=seagreen]
    >
    >.
    >|||Thanks for the suggestions. It's hard to think of all the
    relevant info to post when asking the question.
    * The job that should back up the transaction log is the
    job that failed. Attempts to back up the transaction log
    via other means (as listed) also failed.
    * We have limited space on all 3 drives. On both D: and
    E: there is the equivalent of the existing database size
    free however (figures in original post).
    * The transaction log is backed up 6 times during a
    working day, and once each weekend.
    * Recovery mode is "full" except for when one scheduled
    script is run each weekend. It rebuilds indices so before
    it is run recovery mode is changed to "bulk logged". The
    log backup following this task was the first to fail.

    >--Original Message--
    >Sounds you are out of disk space on D drive. Take the
    >backup of transaction log and shrink the .ldf. Don't you
    >take frequent transaction log backups?. If you are
    >concerned only about FULL and DIFFERENTIAL backups , you
    >can set the Database to SIMPLE recovery, so the log will
    >be truncated automatically.
    >INF: Shrinking the Transaction Log in SQL Server 2000
    with
    >DBCC SHRINKFILE
    >http://support.microsoft.com/default.aspx?scid=kb;EN-
    >US;272318
    >Also look for some other pointers which tells, why the
    log
    >file grows rapidly.
    >Finally I think u may need to buy some Hard disks.
    >HTH
    >--
    >Regards
    >~Thirumal~
    >www.thirumal.com
    >
    >backing up to a
    >message
    >NOFORMAT
    >there
    ago)[vbcol=seagreen]
    see[vbcol=seagreen]
    >.
    >

    ReportIoError 2 - any ideas?

    Problem:
    --
    The following SQL is scheduled to run via Enterprise
    manager. It has been successfully running for months.
    BACKUP LOG [Proclaim] TO DISK = N'D:\mssql7
    \backup\Proclaim_backup.bak' WITH NOINIT , NOUNLOAD ,
    NAME = N'Proclaim backup', SKIP , STATS = 10, NOFORMAT
    Friday night the job failed with the log reporting:
    BackupMedium::ReportIOError: write failure on backup
    device 'D:\mssql7\backup\Proclaim_backup.bak'.
    Operating system error 2 (error not found).
    To add to the problem, the log file is now full and there
    is limitted space in this D: on the server.
    Configuration:
    --
    Operating System: Windows 2000 Server sp3
    Full recovery mode.
    SQL Server 2000 sp 3 (upgraded from SQL 7 12 months ago)
    Hardward Raid (1) on C: (and 500MB free)
    Hardware Raid (5) on D: (and 5Gb free)
    Hardware Raid (1) on E: (and 6Gb free)
    .mdf file = 4491MB allocated (autogrow)
    .log file = 2101MB allocated (at limit)
    .bak file = 4110MB
    Attempted Action so far:
    --
    * Invoking log backup from Enterprise manager:
    - to same location -> failed with same error
    - to E:\ -> failed with ReportIoError 112
    * Retried above after deleting existing file
    D:\mssql7\backup\Proclaim_backup.bak' - no change
    * Retried above after changing to bulk recovery mode.
    - to same location -> failed with same error
    - to E: -> successful, but log file still the same size
    I've read Knowledge base article 325334; but since the
    striping is hardware based on all available drives - see
    no means of utilising this information.
    Any suggestions would be greatly appreciated.I don't know where D:\ and E:\ are, but have you tried backing up to a
    different machine?
    --
    http://www.aspfaq.com/
    (Reverse address to reply.)
    "Kim" <anonymous@.discussions.microsoft.com> wrote in message
    news:2631501c46229$3f11d150$a401280a@.phx.gbl...
    > Problem:
    > --
    > The following SQL is scheduled to run via Enterprise
    > manager. It has been successfully running for months.
    > BACKUP LOG [Proclaim] TO DISK = N'D:\mssql7
    > \backup\Proclaim_backup.bak' WITH NOINIT , NOUNLOAD ,
    > NAME = N'Proclaim backup', SKIP , STATS = 10, NOFORMAT
    > Friday night the job failed with the log reporting:
    > BackupMedium::ReportIOError: write failure on backup
    > device 'D:\mssql7\backup\Proclaim_backup.bak'.
    > Operating system error 2 (error not found).
    >
    > To add to the problem, the log file is now full and there
    > is limitted space in this D: on the server.
    >
    > Configuration:
    > --
    > Operating System: Windows 2000 Server sp3
    > Full recovery mode.
    > SQL Server 2000 sp 3 (upgraded from SQL 7 12 months ago)
    > Hardward Raid (1) on C: (and 500MB free)
    > Hardware Raid (5) on D: (and 5Gb free)
    > Hardware Raid (1) on E: (and 6Gb free)
    > .mdf file = 4491MB allocated (autogrow)
    > .log file = 2101MB allocated (at limit)
    > .bak file = 4110MB
    >
    > Attempted Action so far:
    > --
    > * Invoking log backup from Enterprise manager:
    > - to same location -> failed with same error
    > - to E:\ -> failed with ReportIoError 112
    > * Retried above after deleting existing file
    > D:\mssql7\backup\Proclaim_backup.bak' - no change
    > * Retried above after changing to bulk recovery mode.
    > - to same location -> failed with same error
    > - to E: -> successful, but log file still the same size
    >
    > I've read Knowledge base article 325334; but since the
    > striping is hardware based on all available drives - see
    > no means of utilising this information.
    >
    > Any suggestions would be greatly appreciated.|||Sounds you are out of disk space on D drive. Take the
    backup of transaction log and shrink the .ldf. Don't you
    take frequent transaction log backups?. If you are
    concerned only about FULL and DIFFERENTIAL backups , you
    can set the Database to SIMPLE recovery, so the log will
    be truncated automatically.
    INF: Shrinking the Transaction Log in SQL Server 2000 with
    DBCC SHRINKFILE
    http://support.microsoft.com/default.aspx?scid=kb;EN-
    US;272318
    Also look for some other pointers which tells, why the log
    file grows rapidly.
    Finally I think u may need to buy some Hard disks.
    HTH
    --
    Regards
    ~Thirumal~
    www.thirumal.com
    >--Original Message--
    >I don't know where D:\ and E:\ are, but have you tried
    backing up to a
    >different machine?
    >--
    >http://www.aspfaq.com/
    >(Reverse address to reply.)
    >
    >
    >"Kim" <anonymous@.discussions.microsoft.com> wrote in
    message
    >news:2631501c46229$3f11d150$a401280a@.phx.gbl...
    >> Problem:
    >> --
    >> The following SQL is scheduled to run via Enterprise
    >> manager. It has been successfully running for months.
    >> BACKUP LOG [Proclaim] TO DISK = N'D:\mssql7
    >> \backup\Proclaim_backup.bak' WITH NOINIT , NOUNLOAD ,
    >> NAME = N'Proclaim backup', SKIP , STATS = 10,
    NOFORMAT
    >> Friday night the job failed with the log reporting:
    >> BackupMedium::ReportIOError: write failure on backup
    >> device 'D:\mssql7\backup\Proclaim_backup.bak'.
    >> Operating system error 2 (error not found).
    >>
    >> To add to the problem, the log file is now full and
    there
    >> is limitted space in this D: on the server.
    >>
    >> Configuration:
    >> --
    >> Operating System: Windows 2000 Server sp3
    >> Full recovery mode.
    >> SQL Server 2000 sp 3 (upgraded from SQL 7 12 months ago)
    >> Hardward Raid (1) on C: (and 500MB free)
    >> Hardware Raid (5) on D: (and 5Gb free)
    >> Hardware Raid (1) on E: (and 6Gb free)
    >> .mdf file = 4491MB allocated (autogrow)
    >> .log file = 2101MB allocated (at limit)
    >> .bak file = 4110MB
    >>
    >> Attempted Action so far:
    >> --
    >> * Invoking log backup from Enterprise manager:
    >> - to same location -> failed with same error
    >> - to E:\ -> failed with ReportIoError 112
    >> * Retried above after deleting existing file
    >> D:\mssql7\backup\Proclaim_backup.bak' - no change
    >> * Retried above after changing to bulk recovery mode.
    >> - to same location -> failed with same error
    >> - to E: -> successful, but log file still the same size
    >>
    >> I've read Knowledge base article 325334; but since the
    >> striping is hardware based on all available drives - see
    >> no means of utilising this information.
    >>
    >> Any suggestions would be greatly appreciated.
    >
    >.
    >|||I've only ever backed up to the local PC. I use the
    Enterprise Manager GUI to generate the SQL as a scheduled
    job - then modify the schedule as required. The GUI will
    not list mapped network drives - so I'd assumed that it
    wasn't possible to back up to an external machine.
    In any case, Our only other Windows 2000 server has
    exactly the same RAID settings.
    I have got around the immediate problem by:
    * increasing the ldf file by 500MB
    * backing up the transaction log to the E: (which has Raid
    1).
    * using DBCC SHRINKFILE to reduce the ldf file size.
    * Deleting the file 'D:\mssql7\backup\Proclaim_backup.bak'
    * Rerunning the backups through Enterprise Manager.
    I still don't really understand what ReportIoError 2
    means. I can only assume that it is related somehow to
    the striping because of Q325334.
    As a consequence I've no idea whether the problem will
    continue and in what timeframe. (I work in an IT
    department of 3, and we support a number of disparate
    platforms. My colleagues know even less about SQL Server
    than I do - so I want to avoid this situation recurring in
    my absence).
    Thanks for your assistance.
    Kim.
    >--Original Message--
    >I don't know where D:\ and E:\ are, but have you tried
    backing up to a
    >different machine?
    >--
    >http://www.aspfaq.com/
    >(Reverse address to reply.)
    >
    >
    >"Kim" <anonymous@.discussions.microsoft.com> wrote in
    message
    >news:2631501c46229$3f11d150$a401280a@.phx.gbl...
    >> Problem:
    >> --
    >> The following SQL is scheduled to run via Enterprise
    >> manager. It has been successfully running for months.
    >> BACKUP LOG [Proclaim] TO DISK = N'D:\mssql7
    >> \backup\Proclaim_backup.bak' WITH NOINIT , NOUNLOAD ,
    >> NAME = N'Proclaim backup', SKIP , STATS = 10,
    NOFORMAT
    >> Friday night the job failed with the log reporting:
    >> BackupMedium::ReportIOError: write failure on backup
    >> device 'D:\mssql7\backup\Proclaim_backup.bak'.
    >> Operating system error 2 (error not found).
    >>
    >> To add to the problem, the log file is now full and
    there
    >> is limitted space in this D: on the server.
    >>
    >> Configuration:
    >> --
    >> Operating System: Windows 2000 Server sp3
    >> Full recovery mode.
    >> SQL Server 2000 sp 3 (upgraded from SQL 7 12 months ago)
    >> Hardward Raid (1) on C: (and 500MB free)
    >> Hardware Raid (5) on D: (and 5Gb free)
    >> Hardware Raid (1) on E: (and 6Gb free)
    >> .mdf file = 4491MB allocated (autogrow)
    >> .log file = 2101MB allocated (at limit)
    >> .bak file = 4110MB
    >>
    >> Attempted Action so far:
    >> --
    >> * Invoking log backup from Enterprise manager:
    >> - to same location -> failed with same error
    >> - to E:\ -> failed with ReportIoError 112
    >> * Retried above after deleting existing file
    >> D:\mssql7\backup\Proclaim_backup.bak' - no change
    >> * Retried above after changing to bulk recovery mode.
    >> - to same location -> failed with same error
    >> - to E: -> successful, but log file still the same size
    >>
    >> I've read Knowledge base article 325334; but since the
    >> striping is hardware based on all available drives - see
    >> no means of utilising this information.
    >>
    >> Any suggestions would be greatly appreciated.
    >
    >.
    >|||Context
    --
    The first of the failed log backups was run after a series
    of indexes on the database were rebuilt.
    Recovery mode is changed to "bulk-logged" before rebuiding
    indexes. This is followed by the log backup and then
    recovery mode is changed back to "full".
    Additional Error Messages in SQL Server Log
    ---
    BackupMedium::ReportIoError: write failure on backup
    device 'D:\mssql7\backup\Proclaim_backup.bak'. Operating
    system error 2(error not found).
    Internal I/O request 0x2E45D1A8: Op: Write, pBuffer:
    0x3BE50000, Size: 65536, Position: 4293497344, UMS:
    Internal: 0x103, InternalHigh: 0x0, Offset: 0xFFE99200,
    OffsetHigh: 0x0, m_buf: 0x3BE50000, m_len: 65536,
    m_actualBytes: 0, m_errcode: 2, BackupFile: D:\mssql7
    \backup\Proclaim_backup.bak
    BACKUP failed to complete the command BACKUP LOG
    [Proclaim] TO DISK = N'D:\mssql7
    \backup\Proclaim_backup.bak' WITH NOINIT , NOUNLOAD ,
    NAME = N'Proclaim backup', SKIP , STATS = 10, NOFORMAT|||Thanks for the suggestions. It's hard to think of all the
    relevant info to post when asking the question.
    * The job that should back up the transaction log is the
    job that failed. Attempts to back up the transaction log
    via other means (as listed) also failed.
    * We have limited space on all 3 drives. On both D: and
    E: there is the equivalent of the existing database size
    free however (figures in original post).
    * The transaction log is backed up 6 times during a
    working day, and once each weekend.
    * Recovery mode is "full" except for when one scheduled
    script is run each weekend. It rebuilds indices so before
    it is run recovery mode is changed to "bulk logged". The
    log backup following this task was the first to fail.
    >--Original Message--
    >Sounds you are out of disk space on D drive. Take the
    >backup of transaction log and shrink the .ldf. Don't you
    >take frequent transaction log backups?. If you are
    >concerned only about FULL and DIFFERENTIAL backups , you
    >can set the Database to SIMPLE recovery, so the log will
    >be truncated automatically.
    >INF: Shrinking the Transaction Log in SQL Server 2000
    with
    >DBCC SHRINKFILE
    >http://support.microsoft.com/default.aspx?scid=kb;EN-
    >US;272318
    >Also look for some other pointers which tells, why the
    log
    >file grows rapidly.
    >Finally I think u may need to buy some Hard disks.
    >HTH
    >--
    >Regards
    >~Thirumal~
    >www.thirumal.com
    >>--Original Message--
    >>I don't know where D:\ and E:\ are, but have you tried
    >backing up to a
    >>different machine?
    >>--
    >>http://www.aspfaq.com/
    >>(Reverse address to reply.)
    >>
    >>
    >>"Kim" <anonymous@.discussions.microsoft.com> wrote in
    >message
    >>news:2631501c46229$3f11d150$a401280a@.phx.gbl...
    >> Problem:
    >> --
    >> The following SQL is scheduled to run via Enterprise
    >> manager. It has been successfully running for months.
    >> BACKUP LOG [Proclaim] TO DISK = N'D:\mssql7
    >> \backup\Proclaim_backup.bak' WITH NOINIT , NOUNLOAD ,
    >> NAME = N'Proclaim backup', SKIP , STATS = 10,
    >NOFORMAT
    >> Friday night the job failed with the log reporting:
    >> BackupMedium::ReportIOError: write failure on backup
    >> device 'D:\mssql7\backup\Proclaim_backup.bak'.
    >> Operating system error 2 (error not found).
    >>
    >> To add to the problem, the log file is now full and
    >there
    >> is limitted space in this D: on the server.
    >>
    >> Configuration:
    >> --
    >> Operating System: Windows 2000 Server sp3
    >> Full recovery mode.
    >> SQL Server 2000 sp 3 (upgraded from SQL 7 12 months
    ago)
    >> Hardward Raid (1) on C: (and 500MB free)
    >> Hardware Raid (5) on D: (and 5Gb free)
    >> Hardware Raid (1) on E: (and 6Gb free)
    >> .mdf file = 4491MB allocated (autogrow)
    >> .log file = 2101MB allocated (at limit)
    >> .bak file = 4110MB
    >>
    >> Attempted Action so far:
    >> --
    >> * Invoking log backup from Enterprise manager:
    >> - to same location -> failed with same error
    >> - to E:\ -> failed with ReportIoError 112
    >> * Retried above after deleting existing file
    >> D:\mssql7\backup\Proclaim_backup.bak' - no change
    >> * Retried above after changing to bulk recovery mode.
    >> - to same location -> failed with same error
    >> - to E: -> successful, but log file still the same size
    >>
    >> I've read Knowledge base article 325334; but since the
    >> striping is hardware based on all available drives -
    see
    >> no means of utilising this information.
    >>
    >> Any suggestions would be greatly appreciated.
    >>
    >>.
    >.
    >|||Hi Kim,
    When ever you perform a recovery model change, it is always suggested to do
    a full database backup and
    then goahead with Transaction log backup.
    This I/O issue can araises due to free space in hard disk, permissions on
    the disk.
    Thanks
    Hari
    MCDBA
    "Kim" <anonymous@.discussions.microsoft.com> wrote in message
    news:25bce01c46248$42024970$a601280a@.phx.gbl...
    > Context
    > --
    > The first of the failed log backups was run after a series
    > of indexes on the database were rebuilt.
    > Recovery mode is changed to "bulk-logged" before rebuiding
    > indexes. This is followed by the log backup and then
    > recovery mode is changed back to "full".
    >
    > Additional Error Messages in SQL Server Log
    > ---
    > BackupMedium::ReportIoError: write failure on backup
    > device 'D:\mssql7\backup\Proclaim_backup.bak'. Operating
    > system error 2(error not found).
    > Internal I/O request 0x2E45D1A8: Op: Write, pBuffer:
    > 0x3BE50000, Size: 65536, Position: 4293497344, UMS:
    > Internal: 0x103, InternalHigh: 0x0, Offset: 0xFFE99200,
    > OffsetHigh: 0x0, m_buf: 0x3BE50000, m_len: 65536,
    > m_actualBytes: 0, m_errcode: 2, BackupFile: D:\mssql7
    > \backup\Proclaim_backup.bak
    > BACKUP failed to complete the command BACKUP LOG
    > [Proclaim] TO DISK = N'D:\mssql7
    > \backup\Proclaim_backup.bak' WITH NOINIT , NOUNLOAD ,
    > NAME = N'Proclaim backup', SKIP , STATS = 10, NOFORMAT|||Thanks for the suggestion.
    I'll add a full backup to the scheduled job before and
    after the recovery mode change and see if the issue
    persists.

    Monday, February 20, 2012

    Reporting Services: Subsciption E-mail Not Sending

    After setting up a subscription in reporting services you send an email and it reports that it sent it successfully, but no email is sent. It will send to a file share. Any suggestionsMake sure you have the correct SMTP server value set up. Are you on RS 2000 or 2005?|||It is all setup I can email from intergration services packages and backup jobs, but the reporting services I believe is being blocked at our firewall. What format is the body of the document written, and is there a way to change it? Is the report inserted or attached?|||

    The report is embeded in the body if you choose Web Archive format, otherwise it is attached. The format for the body is UTF-8 (I believe, going off memory here).

    We generally suggesting using a local SMTP to relay the messages and having RS drop the files to the SMTP Servers pickup directory. More info on configuring the email extension can be found here:

    http://msdn2.microsoft.com/en-us/library/ms159155.aspx