Configure SQL Reporting Service, to Access Reports using External IP With IIS 7

Hi All, Recently I come to situation where I need to Configure Reporting services on server having configuration: Window server 2008 and IIS 7. I need to configure reports in manner, so User can access this reports thru External IP Address. I found configuration for IIS 7.0, so user can access reports externally. To able to access the reports externally, do the steps as mentioned below: 1. Create Virtual Directory in IIS 7.0 : 2. Go to Handler Mappings : 3. Double Click on "Handler Mapping". You will get screen like the given below: 4. Now see Top Right Corner: Click On "Add Managed Handler". Here you need to fill up the info like: Fill this info as: Request path: Reserved.ReportViewerWebControl.axd Type: Microsoft.Reporting.WebForms.HttpHandler Name: Reserved-ReportViewerWebControl-axd Thats it. Now try with External IP. You can access the reports with External IP too.

SQL SERVER: Cross tab query with PIVOT

We had a User group meeting for "Cross Tab Queries", where I have explained how to write PIVOT queries. Many times developer has an issue while writing query with PIVOT. Let me have a sample code for query with PIVOT keyword.

 

PIVOT is used to transform rows to column, which gives result in such a way which can be send to the user directly. Let me take an example. We have an order table where we have each order stored in a row in a table as displayed below:

 

ctrl_no	id	Amount	OrderDate
1000029	100016	990	10/25/2012
1000029	100018	990	10/25/2012
1000029	100016	660	10/26/2012
1000029	100018	660	10/26/2012
1000029	206007	660	10/26/2012
1000029	206007	660	10/27/2012

This information has to be saved in a row in relational database. When this information needs to be display it on the application, it needs to be displayed as below, so user can use this info for decision making:

id	10/25/2012	10/26/2012	10/27/2012
100016	990.00	660.00	NULL
100018	990.00	660.00	NULL
206007	NULL	660.00	660.00

Did you see the difference? how easy user can review the information. Lets see an query how to achieve the expected result with PIVOT keyword.

CREATE TABLE #TestTable (
	ctrl_no INT
	,id INT
	,Amount NUMERIC(18, 2)
	,OrderDate DATE
	)

INSERT INTO #TestTable(ctrl_no , id , Amount , OrderDate )
SELECT 1000029,100016,990.0000,'10/25/2012'
UNION ALL
SELECT 1000029,100018,990.0000,'10/25/2012'
UNION ALL
SELECT 1000029,100016,660.0000,'10/26/2012'
UNION ALL
SELECT 1000029,100018,660.0000,'10/26/2012'
UNION ALL
SELECT 1000029,206007,660.0000,'10/26/2012'
UNION ALL
SELECT 1000029,206007,660.0000,'10/27/2012'

SELECT id
	,[10/25/2012]
	,[10/26/2012]
	,[10/27/2012]
FROM (
	SELECT id
		,Amount
		,OrderDate
	FROM #TestTable t
	) x
PIVOT(SUM(Amount) FOR OrderDate IN (
			[10/25/2012]
			,[10/26/2012]
			,[10/27/2012]
			)) p

DROP TABLE #TestTable

 

Let me know if you have any question/comment.

Reference: Tejas Shah (www.SQLYoga.com)

SQL SERVER: Limit memory usage of SQL SERVER

Recently, we have found that SQL SERVER is using most of the memory on the server, due to heavy queries are running on my different teams and it freezes the whole server. Due to time constraint team doesn’t have time to optimize those queries, so we have found a way to configure SQL SERVER to not to use all available memory. We have an option to configure how much memory can be used by SQL SERVER as below: 1. Connect to the SQL SERVER instance for which we need to limit memory usage and click on properties: 2. Go to “Memory”, where you can see the memory options that can be used by SQL SERVER as below: By default, SQL SERVER is using maximum server memory, so to resolve the freezing issue, we need to change it as per requirement. So SQL SERVER will use only that much memory and other processes can run in the server. Let me know your comments. Reference: Tejas Shah (http://www.SQLYoga.com)