Using Reporting Services to Search the SQL Server Log

  • Carolyn Richardson

    SSCrazy Eights

    Points: 8352

    Comments posted to this topic are about the item Using Reporting Services to Search the SQL Server Log

    Facts are stubborn things, but statistics are more pliable - Mark Twain
    Carolyn
    SQLServerSpecialists[/url]

  • fthomas-735932

    SSC Enthusiast

    Points: 140

    Great article ! I will use this . Thanks...

  • Wayne West

    SSC-Insane

    Points: 22586

    Very cool, very useful. I can definitely use something like this, but I have GOT to learn Reporting Services first! :hehe:

    -----
    [font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]

  • Anipaul

    SSC-Insane

    Points: 24681

    Very good practical and useful article. 🙂

  • Charley Jones

    SSC Enthusiast

    Points: 103

    Great article.

    I liked the technique of using the parameter in the connection string.

    Didn't think that was possible,

    Nice hack!

    Just a couple of small problems though:

    1) Your creation script does not match the xml sample report:

    Should read:

    CREATE TABLE [dbo].[ServerList]

    ( [ServerName] [nchar](50) NULL )

    ON [PRIMARY]

    2) Reporting Services doesn't like extensions of XML for reports,

    It prefers RDL.

    Rename the example file to RDL and it will be easier to integrate.

    Thank you for including the sample files!

    Really made life easier!

    Charley Jones
    A+, ITIL, MCAD, MCDBA, MCITP: Sql Server 2005 Adminstration, MCT,
    MCTS: Sql Server 2005, MCP, MCSA, MCSE, MOUS, PMP
    President and Founder S3oLV.Com: Sql Server Society of Las Vegas
    President and Founder LVXUG.Com: Las Vegas XNA Users Group

  • Carolyn Richardson

    SSCrazy Eights

    Points: 8352

    Points noted thanks.

    You can use the xml file:-

    In Reporting Services create a new blank report and from the top menu select View then Code. Replace all the XML displayed with the XML contained in the attached file. If you switch to layout the report will now appear in design view.

    Facts are stubborn things, but statistics are more pliable - Mark Twain
    Carolyn
    SQLServerSpecialists[/url]

  • loi.nguyen2000

    Old Hand

    Points: 352

    Hello Carolyn, this is a great article. It is exactly what I was looking for in my new job assignment.

    One problem I have is I could not set the data source name dynamically. I'm in SSRS 2K5 environment. I got an error message " Microsoft Report Designer - Connection string expressions are not allowed in shared data sources."

    Right now, I just hard coded the server name that I need to see the log most often to use it.

    What do I need to do to be able to configure the datasources dynamically?

  • Carolyn Richardson

    SSCrazy Eights

    Points: 8352

    I think there may be something wrong with the syntax of the connection string your using, can you post it here?

    Facts are stubborn things, but statistics are more pliable - Mark Twain
    Carolyn
    SQLServerSpecialists[/url]

  • loi.nguyen2000

    Old Hand

    Points: 352

    I cut and pasted the scripts from your article, and it is: ="Data Source=" & Parameters!ServerName.Value & ";Initial Catalog=master"

    One thing I notice from my data source designer screen is mine had Edit button highlighted and the F(x) button greyed out. Yours is the opposite - F(x) highlighted and the Edit button greyed out. Is this something we set on server configuration?

    I'm not able to insert screen prints from my designer screen here for you to see.

  • Carolyn Richardson

    SSCrazy Eights

    Points: 8352

    I've not seen this before but Googled it and it seems that this is by design. The data source for reports with dynamic connection functionality needs to be local (i.e. not shared) to the report itself. So the the table where you pull the list of server names from needs to be a table on your local instance.

    Facts are stubborn things, but statistics are more pliable - Mark Twain
    Carolyn
    SQLServerSpecialists[/url]

  • loi.nguyen2000

    Old Hand

    Points: 352

    You have correctly identified the problem. I defined a shared datasource instead of local. Thank you for pointing it out.

    Since it's not a shared datasource, I ran into another challenge. I cannot test the ErrorList by clicking the "!" button and have to use the preview tab instead. I now have a different error to debug. Something about [rsErrorReadingDataSetField] and [rsMissingFieldInDataSet]. I will work on this.

    Again, thank you for your help. It will be a great tool for us when I get this working.

    Loi.

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply