Sql Server & SSRS Tips

for displaying two columns one in first line and second in other line in the same column Function: replace(field!NetID.value,",",Environment.NewLine) in "," instead of comma you can write any character for which you want to replace that character with a new line. "Environment.NewLine" it will replace the , comma with the new line For example replace(Fields!NetIDLease.Value & "," & Fields!NetIDSale.Value,",",Environment.NewLine),

Read article from that link: http://www.mssqltips.com/sqlservertip/2485/ssis-interview-questions--part-1/?utm_source=dailynewsletter&utm_medium=email&utm_content=headline&utm_campaign=20111122

visit the link for the basic information of query optimization. http://beginner-sql-tutorial.com/sql-query-tuning.htm

If you want to search a specific column in the whole database in sql server then you can the following query: select * from INFORMATION_SCHEMA.COLUMNS where COLUMN_NAME like   '%yourColumnName%' in the place of yourColumnName between the Percent sign you have to write the column name which you want to search

where PropPropertySubtype.Description in ('Class A','Class B') and PropProperty.PropertyID in    (SELECT PROPERTYID FROM DBO.FN_SEARCHPROPERTYID(@SearchID,1)) 

For displaying only date and eliminating time from date in SSRS =formatdatetime(Parameters!ReportDate.Value,dateformat.shortdate) =formatdatetime(Fields!ReportDate.Value,dateformat.shortdate) use appropriate from above two formulas. " dateformat.shortdate " will show only date value and eliminate time from it. and it will be used in " formatdatetime " function.

There are three methods for retrieving data base information in sql server. EXEC sp_databases EXEC sp_helpdb SELECT *  FROM sys.sysdatabases order by name

If  want to Set the default value of a parameter to null then right click on parameter and select parameter properties . In GENERAL tab check Allow null value .Then click on DEFAULT VALUES tab, select the radio button of specify values . click on Fx button. A dialogue box open for writing formula. Write the formula "= nothing"  (without double quotes)  and click OK. Now your report will automatically take the Null value for that parameter. This is mostly used for date-time parameters and also used when you have to pass report parameters from Application (asp page).

 SELECT taf_no, SUBSTRING((SELECT ','+ CAST(dtafdet_from AS VARCHAR)+','+dtafdet_to             FROM CTE B             WHERE A.taf_no = B.taf_no             FOR XML PATH('')              ),2,100)As Temp                         FROM CTE A GROUP BY A.taf_no

If you want to apply some checks in query then best option is to use Cases. example of case is below: Select Building.ID,Building.Name, case when Building.Status='Completed' or Building.Status='Renovate' then Building.Size else 0  end as 'BuildingSize' It will check for building status and return Building size if building status is completed or renovate and return 0 when the above condition is not satisfied.

If you want to display all selected values for multi value parameters in SSRS report then you have to use this function: =Join(Parameters!ParameterName.Label, ",") "This will display all selected values for a parameter but  comma seperated" =Join(Parameters!ParameterName.Label, VBCRLF)    "This will display all selected values for a parameter but  every value on a new line" Now its up to you which function you use.

If you want to convert date-time to date only in sql query then you use the following function: for example you have 2 parameters ''StartDate" and "EndDate"  firse declare them... ----------------------------------------------------------------------------------- @StartDate datetime, @EndDate datetime SELECT  * where  tablename.Startdate >=convert(varchar, @StartDate, 102) and tablename.enddate <=convert(varchar, @EndDate, 102) ----------------------------------------------------------------------------------- ************************** Explanation ************************** @StartDate datetime = Declaration of parameter StartDate @EndDate datetime = Declaration of parameter EndDate convert(varchar, @StartDate, 102)  = function of converting date-time to date  tablename.Startdate >=convert(varchar, @StartDate, 102) = Proper syntax of function

For displaying page number and total pages on ssrs reports like (Page 1 of 10) you have to use the following command which you write in test box as an expression. command is ( = "Page " & Globals!PageNumber & " of " & Globals!TotalPages )  "Page "                         (for displaying Page)  &                                   (for concatenating text with formula) Globals!PageNumber   (formula for displaying current page number)  " of "                             (for displaying of)  Globals!TotalPages      (formula for displaying total page numbers)

Right click on table select Script table as then select drop and create to. a sql file with queries will open. copy all the contents of this file and the make new query file paste it in to te nw query file. Change name of data base from old (the one which you are coping) to new (the one in which you want to copy table) with find replace option. which is written in use[database_Name]. After changing the name press F5 or execute query. and then refresh database from object explore.Now you will se that table is copied to the new database. 

If you want that your ssrs report show drop down list for the values of parameters insted of entering a value by hand. then first create a procedure for that paramete. eg you have a parameter Project and you want to display all projects names in drop down list on report. then create procedure which retrieves you all projectsID's and projectNames like:   (select Projet.ProjectID, Projet.ProjectName from Projet) and add this procedure as a dataset in the report then return on report select parameter from Report Data tool bar go to its properties in general properties check the box " Allow multiple values ", now click on Avaliable values option from left pane.In this option check the radio button " get values from a query " Select dataset which you have creted for that paramenter(which retrieves you id and name like i have created for Project). In "value field" select id (which you want to match with query parameters eg ProjectID) and in " La...

If you want to duaplay system current date on SSRS report .then first create a parameter like "Date" and set its Data type to "Date/time" from properties. and the fo to the default value option in propoerties then check specify value then click on button named " fx ", a window will be open for writing formula then write " = today() " in the window and click on ok. and now go to the test box where you want to disply date. right click on text box and go to expression . then select parameters from left pane. all parameters will be shown at the right pane. then go to right pane and double click on date parameter.now a formula will be created like " = parameters!Date.value" . then click ok. now run your report your report will show system current date.

If you want to retrieve comma seperated values instead of all values in multiple line. then first set the values to varchar.. eg if you have a parameter which you want to retrieve comma seperated values for this parameter then initilize it as varchar. eg @ProjectID varchar(25), @RegistrationNo varchar(25) then in where clause use function "select item from fn_split" and pass your parameter which you want to split as the first argument of this function and pass an operator as second argument on which you want to spit value eg. where (Project.intProjectID in (select item from fn_split(@ProjectID, ','))) and (Student.intRegNo in (select item from fn_split(@RegistrationNo, ','))) This is a sql server built in function "select item from fn_split"

Read article from their http://download.microsoft.com/download/C/C/A/CCA2C3E3-D441-4161-AE5F-94F0A5D70ED4/SQL%20Server%202008%20R2%20Spatial%20Data%20Whitepaper.DOCx "Only for reading purpose" Delivering Location Intelligence with Spatial Data White Paper Published: August 2007 Summary: The growing ability of businesses and consumers to quickly absorb large volumes of data, together with the increased availability of digital maps and spatially-enabled applications has created an unprecedented opportunity to incorporate geographic factors into decision making processes and analysis. The new spatial support in Microsoft SQL Server™ 2008 R2 can help you to make better decisions through visual analysis of location data that can be stored and manipulated in a SQL Server database... Contents Introduction ...................................................................................................................... 1 Comprehensive Spatial Support ........................

Read this article by this link "http://www.petri.co.il/create-backup-job-in-sql-server.htm" First you start by opening a query window in SSMS and connect to the server you want to backup.  It’s worth noting that it doesn’t matter which database you’re connect to, but I typically do it from master just to be consistent. The basic backup syntax that you need to type is here: BACKUP DATABASE databaseName TO DISK = 'file location' OK, let me explain a couple of these items. BACKUP Database – T-SQL command for what you want to do, which in this case is backing up a database. databaseName – this is the name of the database you want to backup. To disk = 'file location' – Backups are written to files.  This is the full path to the file.  Typically, a full backup such as this one will have the extension of .bak, but that’s just what everyone does.  It can have any extension you like or no extension at all if you like. Now let’s see a real example with all o...

If you have many rows in details of SSRS reports and in preview mode your one or more line moves to nect page but you want to keep them together. so first select all rows and set "keep together" to true from properties and also set keep together property from advanced (a small triangle at the groups). If the records are still move to next page then right click on report background and click on reports properties and increase the height of report a little bit. your problem will be solved

When an empty page is coming at the end of SSRS report , then check the width of report in its properties and increase its width....

SQL Server backup media A database may be backed up to disk or to tape. The examples in this article assume a disk backup directly into a disk file (as opposed to a disk backup device). Any database can be backed up to a random disk file at any time. The file may either be initialized (using WITH INIT) or appended with the new backup. Types of backups in SQL Server SQL Server provides several different kinds of backups including Complete , Differential , Transaction Log , and File(s) and Filegroup(s) backup. A combination of these backups may be used to formulate a robust disaster recovery strategy. The following paragraphs explain each SQL Server backup type Complete database backup A complete database backup creates a stand-alone image of the entire database. A complete database backup is self-dependent and may be restored to either the same or a new database on the same or a different server. This provides plenty of flexibility at the time when this backup has to be ...