Anthony Brown

If a server is behaving badly, or there is a long running process and you want to find out what it is actually doing, you might find these scripts useful. They can be used to show the query or stored procedure that is executing and the text of the statement that is executing within that procedure.

This gives a more complete picture of what is happening than by just using dbcc inputbuffer which  just displays the last statement sent by the client to sql server.

For example say have a stored procedure, uspProcedure1, which calls another procedure, uspProcedure2, inside it, and uspProcedure2 takes a long time to complete. If a client executes uspProcedure1 and you run dbcc inputbuffer on that spid, it will just show uspProcedure1 executing, even though the statement currently executing is in uspProcedure2

Both scripts create a procedure which does the same thing - sp_dba_showline is for sql 2000 with sp3 or greater installed and sp_dba_showline2005 is for sql 2005. The reason for the difference is the sql 2000 version uses fn_get_sql (a function introduced in sp3) which is being depreciated in favor of sys.dm_exec_sql_text (available in sql 2005). The sql 2005 version also uses a cross apply join to get the data.

The procedure takes 1 parameter, @spid, and the results returned are:

the results of dbcc inputbuffer to see what query or procedure was called from the client

the results of sp_who2 to show general process information

the first 100 lines of the procedure or query being executed (this can be useful to find the procedure name being executed)

the text of the statement actually being executed. 

If you create these on the master database as dbo, then they should be accessible from all user database

Whilst I was looking at building custom reports in management studio, I finally got round to looking at reporting services in sql 2005.

One really neat feature I found is the ability to use an expression to build a connection string. Using this, you can dynamically set which server the report connects to when it runs. You can then embed this report as a subreport on another report and so get it to run against several servers all from one report. If you look after several servers, this can give you a nice easy way to check all your servers are OK.

To do this, first create the report you want to run against each server. Don't worry about the data source while you are designing it - just connect to any machine and make sure it is designed how want.

When you are happy with it, add a parameter to the report called ServerName. This should be a string datatype and the ‘allow blank value’ checkbox should be unchecked.

Next you need to change the datasource to use an expression.(one thing to note is you can’t use an expression on a shared datasource, so you may need to create a new datasource for the report). Edit the datasource and click the fx button next to the connection string and change the expression to

="Data Source=" & Parameters!ServerName.Value

If you now try and preview the report, you need to enter the servername parameter and it runs against whichever server you specify.

Now for the second report. First create a dataset that will return all the names of the servers you want to run the report against.

On the layout tab, add a table and bind it to that dataset. Then drag a subreport into the detail part of the table. Right click on the subreport and select properties and in the subreport drop down box, select the first report you created. Finally go to the parameters tab and in the parameter name column select ServerName and in the parameter value column select the column from the dataset that contains the servername.

In the download on this entry are couple of reports to show how this all works.

The first report is SingleServerInfo that just shows some information that it connects to. You can change which server it connects to using the ServerName parameter.

The second report is MultipleServerInfo and has a table with SingleServerInfo as subreport in one of the cells. This means it is called for each server returned by the dataset on this report (which needs a table called ServerNames which can be created using the script in the zip file)

These reports will only run against sql 2005 servers as they use some of the dynamic management views available on there. However, there is no reason why you cant use this technique to run a report against several sql 2000 servers (or a mixture of versions)

Please make sure you look at the queries these reports run and are happy with them before running the reports against any of your servers. 

Following on from my last post, Microsoft have now published some documentation on creating custom reports in management studio. Its still a draft, but is available at http://blogs.msdn.com/sqlrem/archive/2006/11/20/custom-reports-in-management-studio.aspx if you want to read it

One of the things I really like in sql 2005 is the management reports available from within management studio.

In sp2, there is the ability to add your own report. However the documentation seems a bit sparse on how to do this, so here is a quick overview from what I have managed to find out. A lot of the information I found out by looking at the existing reports which I downloaded from http://blogs.msdn.com/sqlrem/archive/2006/08/30/SSMS-Reports-3.aspx

The reports need to be in rdlc format which is the format created when you create one for a report viewer control (ie a report embedded in another application). There are some notes on how to convert between rdl and rdlc files at http://msdn2.microsoft.com/en-us/library/ms252109(VS.80).aspx. I have found that for importing reports into management studio, you can just create an rdl file and then change the extension to rdlc.

There are some parameters passed to the report when you run it. You can use these to customize or get data for your report based on where in management studio the report was executed from. The parameters that I know about are

ObjectName

ObjectType

ServerName

FontName

DatabaseName

Most of them are fairly self explanatory.

The ObjectType parameter is annoying in that it gives you the type of the object (according to management studio) from where you ran the report. This means that if you run a report against, for example, the tables folder, instead of passing in tables as the object type, it returns folder.

The fontname just seems to be included to allow easier customization of the reports.

No matter what you set in the connection string, when you run the reports in management studio, they always seem to run against the server and database you are connected to. In fact, you can manually edit the rdlc file and remove all details from the connectString part of the data source so you are left with something like

    <DataSource Name="DataSource1">

      <ConnectionProperties>

        <ConnectString></ConnectString>

        <DataProvider>SQL</DataProvider>

      </ConnectionProperties>

      <rd:DataSourceID>b9ec3aac-9b6d-4f5e-96da-e43d7a094bfd</rd:DataSourceID>

    </DataSource>

(your datasource name and datasourceid will probably be different).

I have uploaded a simple report called TestReport.rdl. The report just lists the parameters passed into it and runs select @@servername, db_name() against the server to show where it is running.

You can view the design for this in visual studio 2005 as you would any other report.

To run the report in management studio, you must have sp2 installed.

First, rename the file so it has a .rdlc extension. Then right click somewhere in object explorer and select reports->custom reports. You will get an open file dialog box so just browse to where the file is and open it and the report will run.

One nice feature is once you have run a custom report from a specific place (eg against one table, or against one server), the reports stay in a recently used list against that object type, so you don’t have to keep selecting custom reports and browsing for the file if you want to run the report against another object of the same type.

You can run this report pretty much everywhere to see examples of the information passed into it (you can even run it against certain nodes on a sql 2000 database)