Sql Server & SSRS Tips

If you want to verify database backup file to see if it is corrupt or not you have to use following T-Sql statement. RESTORE VERIFYONLY FROM DISK =  'E:\abc.bak' just you have to write the path of backup file in the statement.

SELECT   propertyid AS PropertyID, [2] as 'Type2',[7]as 'Type7',[8] as 'Type8' FROM (SELECT prop.propertyid,prop.relationshiptypeid     FROM  vw_SpaceContactRelationship as prop         ) AS SourceTable PIVOT ( MAX(relationshiptypeid) FOR relationshiptypeid  IN ([2],[7],[8]) ) AS PivotTable RESULT OF THIS QUERY PropertyID          Type2    Type7    Type8 707312                  NULL     7             8 707313                  NULL     7         NULL 707329                  2             7...

For example you have a student table and you would like to see the record of a student  who have previous record row from student  id  12. For this the query is: DECLARE @intStudentID int=12 SELECT top 1 * from STUDENT A WHERE A. intStudentID  < @intStudentID  order by 1 desc

@intPageId int, @intPageSize int select * from ( Select     row_number() over (order by  TableA.intID asc ) r ,     TableA.intID,tableA.hName,TableB.Address  from TableA   Join TableB on TableA.intid = TableB.intId   ) A   where A.r  between ( (@intpageid-1)*@intpagesize)+1 and ((@intpageid-1)*@intpagesize)+@intpagesize The query in bold text is for paging, and the normal text query is that which returns your records. OR SELECT * from PressRelease  order by CreatedDate desc OFFSET (@PageID-1)*@PageSize ROWS FETCH NEXT @PageSize ROWS ONLY

select *, row_number() over(order by intID )as RowNumber from TableName You can also use d Partition By in place of Order by to used partition select *, row_number() over(partition by intID )as RowNumber from TableName

SELECT CONVERT(varchar, GETDATE(), 101) as Date SELECT CONVERT(varchar, GETDATE(), 108) as Time You can give your own datetime fields instead of GETDATE().

ALTER PROC [dbo].[SearchAllTables] ( @SearchStr nvarchar(100) ) AS BEGIN -- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved. -- Purpose: To search all columns of all tables for a given search string -- Written by: Narayana Vyas Kondreddi -- Site: http://vyaskn.tripod.com -- Tested on: SQL Server 7.0, SQL Server 2000, SQL server 2005 -- Date modified: 28th July 2002 22:50 GMT CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630)) SET NOCOUNT ON DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110) SET  @TableName = '' SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''') WHILE @TableName IS NOT NULL BEGIN SET @ColumnName = '' SET @TableName = ( SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND QUOTENAME...

ERROR=" Database diagram support objects cannot be installed because this database does not have a valid owner. To continue, first use the Files page of the Database Properties dialog box or the ALTER AUTHORIZATION statement to set the database owner to a valid login, then add the database diagram support objects. " SOLUTION In SQL Server Management Studio do the following: 1. Right Click on your database, go to properties 2. Goto the Options  3. In the Dropdown at right hand "Compatibility Level" choose "SQL Server 2005(90)" 4. Goto the Files  5. Enter "sa" in the owner textbox. 6. click OK Now you will be able to create a database diagram.

--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...

use this command to disable all the constraints: EXEC sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL' and after deletion or any operation on database set the constraints in by this command EXEC sp_MSforeachtable 'ALTER TABLE ? CHECK CONSTRAINT ALL'

For displaying report name or anything only at the last page of report. then click on the text box, go to the properties panel at the rite side. there is an option of " Hidden ". write this expression in the hidden values expression is :  =IIF(Globals!TotalPages=Globals!PageNumber,False,True)

For setting the identity column of a table of database to 1 again then use the following command: DBCC CHECKIDENT ( TableName )

GO /****** Object:  StoredProcedure [dbo].[sp_generate_inserts]    Script Date: 12/13/2011 17:22:07 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO --sp_generate_inserts @table_name='address_type',@WhereClause = 'where cityid = 6' ALTER PROCEDURE [dbo].[sp_generate_inserts] ( @table_name varchar(776),   -- The table/view for which the INSERT statements will be generated using the existing data @target_table varchar(776) = NULL, -- Use this parameter to specify a different table name into which the data will be inserted @include_column_list bit = 1, -- Use this parameter to include/ommit column list in the generated INSERT statement @from varchar(800) = NULL, -- Use this parameter to filter the rows based on a filter condition (using WHERE) @include_timestamp bit = 0, -- Specify 1 for this parameter, if you want to include the TIMESTAMP/ROWVERSION column's data in the INSERT statement @debug_mode bit = 0, -- If @debug_mod...

SQL Server 2008 R2 version information The following table lists the major releases of SQL Server 2008 R2. Release Product version SQL Server 2008 R2 Service Pack 1 10.50.2500.0 SQL Server 2008 R2 RTM 10.50.1600.1 SQL Server 2008 version information The following table lists the major releases of SQL Server 2008. Release Product version SQL Server 2008 Service Pack 3 10.00.5500.00 SQL Server 2008 Service Pack 2 10.00.4000.00 SQL Server 2008 Service Pack 1 10.00.2531.00 SQL Server 2008 RTM 10.00.1600.22 SQL Server 2005 version information The following table lists the major releases of SQL Server 2005. Release Product version SQL Server 2005 Service Pack 4 9.00.5000.00 SQL Server 2005 Service Pack 3 9.00.4035 SQL Server 2005 Service Pack 2 9.00.3042 SQL Server 2005 Service Pack 1 9.00.2047 SQL Server 2005 RTM 9.00.1399 SQL Server 2000 version information The following table lists version number of the Sqlservr.exe file.  Release Product version SQL ...