Systems Engineering and RDBMS

Archive for November 17th, 2008

More on SQL Server and Virtualization

Posted by decipherinfosys on November 17, 2008

We have covered virtualization quite a bit in our blog posts before. Saw this blog post at MSDN regarding the support for SQL Server in a virtual environment which is something that you should be familiar with if you are (or are planning to) running your SQL Server environments in a virtual environment. In addition, as we are playing with Hyper-V nowadays and were researching our choices for SQL Server 2008, here are two sites that you might want to bookmark for your reading purposes:

1) The SQL Server 2008 virtualization section – this has a lot of very good links and resources.

2) Whitepaper by the SQLCAT team – running SQL Server 2008 in a Hyper-V environment.

Posted in Hyper-V, SQL Server, VMWare | Leave a Comment »

More on the ReportServer DB Queries

Posted by decipherinfosys on November 17, 2008

Yesterday, we had covered how to glean meaningful information from the Catalog table contents in the ReportServer database for SSRS reports. We had seen how to convert the image data type to XML and apply the nodes() method to it along with the CROSS APPLY operator to extract out meaningful information.

Another scenario that you might have faced in some of the shops would be if there are multiple data sources (many different instances for different application databases) and there is one single reporting server (which is the case most of the time), then having a query to see all the data sources at one time via a simple query can help the developer/DBA to troubleshoot issues.

Here is such a query – it adds on to the query that we had shown yesterday and adds more information to it:

;WITH XMLNAMESPACES (
DEFAULT
http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition’,
http://schemas.microsoft.com/SQLServer/reporting/reportdesigner’ AS rd
)
SELECT
name,
i.value(‘@Name[1]‘, ‘varchar(100)’) as Data_Set,
t.value(‘DataSourceName[1]‘, ‘varchar(100)’) as Data_Source,
Replace(t.query(‘data(QueryParameters/QueryParameter/@Name)’).value(‘.’, ‘varchar(100)’), ‘ ‘, ‘, ‘) as Query_Parameters
FROM (
select
name,
cast(content as varbinary(max)) as content_as_binary,
cast(cast(content as varbinary(max)) as xml) as content_as_xml
from Catalog
WHERE content is not null
and name not like ‘%gif%’
) as IV
CROSS APPLY content_as_xml.nodes(‘/Report/DataSets/DataSet’) rpt(i)
CROSS APPLY i.nodes(‘Query’) as rpt2(t)

The query uses the nodes() method and the CROSS APPLY operator and also uses the query method to generate a comma separated list of the parameters used in the report.

Posted in SQL Server | 2 Comments »

 
Follow

Get every new post delivered to your Inbox.

Join 82 other followers