Sql Server & SSRS Tips

--WITH XMLNAMESPACES ( DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition', 'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd ) --SELECT ReportName = name --    ,DataSourceName = x.value('(@Name)[1]', 'VARCHAR(250)') --  ,DataProvider = x.value('(ConnectionProperties/DataProvider)[1]','VARCHAR(250)') --  ,ConnectionString = x.value('(ConnectionProperties/ConnectString)[1]','VARCHAR(250)') -- FROM ( SELECT C.Name,CONVERT(XML,CONVERT(VARBINARY(MAX),C.Content)) AS reportXML --      FROM ReportServer.dbo.Catalog C --     WHERE C.Content is not null --      AND C.Type = 2 -- ) a -- CROSS APPLY reportXML.nodes('/Report/DataSources/DataSource') r ( x ) --ORDER BY name ; WITH XMLNAMESPACES ( DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition', 'http://schemas.microsoft.com/SQLServer/reporting/reportd...

exec sp_MSforeachtable 'select count(*) as nr_of_rows, ''?'' as table_name from ?' This will show table name and sum of all the rows of that table

Read from the link below: http://weblogs.asp.net/sreejukg/archive/2010/01/20/scheduling-automated-backup-using-sql-server-2008.aspx

When the  database  is in  SUSPECT   mode , you can change the  database  status to the EMERGENCY  mode . This could permit the system administrator read-only access to the  database . Only members of the sysadmin fixed server role can set a  database  to the EMERGENCY state. You can run the following SQL query to get the  database  to the EMERGENCY  mode . ALTER DATABASE dbName SET EMERGENCY After that, you set the  database  to the single-user  mode . Single user  mode  allows you to recover the damaged  database . ALTER DATABASE dbName SET SINGLE_USER Then you can run DBCC CheckDB command. This command checks the allocation, structural, logical integrity and errors of all the objects in the  database . When you specify “ REPAIR_ALLOW_DATA_LOSS ” as an argument of the DBCC CheckDB command, the procedure will check and repair the reported errors. But these repairs can caus...