Report Server: The webpage cannot be found

Problem: When attempting to view the report server internet explorer give the error The webpage cannot be found. A look at event viewer shows the following error: Unable to bind to the underlying transport for [::]:80. The IP Listen-Only list may contain a reference to an interface which may not exist on this machine. The data field contains the error number.

Solution: No solution as yet.

Workaround: Webservice url and report manager url should be changed from 80, I used 85 and it worked. Use Configuration manager to make this change. Drawback to this approach is that when accessing the report server you need to use the following url: http://localhost:85/Reports/Pages/Folder.aspx

Solution:

I ran netstat -aon from command prompt and discovered that port 80 was being used by PID 3532
I used task manager, processes tab and added the PID column to identity process 3532
The culprit was identifed as Skype
I opened up Skype and unticked the advanced connection setting “Use port 80 and 443 sa alternatives for incoming connections”
I reconfigured report server to use port 80 for Report Manager and Web Service URL
I can now access report server using http://localhost/Reports/Pages/Folder.aspx

Unable to launch configure distribution wizard

PROBLEM

When attempting to launch the configure distribution wizard I receive the following error:

SQL Server is unable to connect to server ‘ABC-LAP-DEF03’.SQL Server replication requires the actual server name to make a connection to the server. Connections through a server alias, IP address, or any other alternate name are not supported. Specify the actual server name, ‘ABC-LAP-DEFGHI’.

Note the second server name in the error is different from the first.

SOLUTION

1. Drop all servers found in select * from sysservers (It may be sufficient just to drop the local server)

2. create the server name for my local using sp_addserver @server=’SERVERNAME’, @local=’LOCAL’

and then recreate the rest of the servers.

3. restart the server service

4. I always connect using “localhost” when I attempted to configure distribution I was greeted with the same error, however when I changed my server to the actual server name it worked.

Pivot Function with dynamic column headings

Problem: You need to use the pivot function but the columns heading are not static

Solution: Produce the pivot select using dynamic sql
IF Object_id(‘tempdb..#data’) IS NOT NULL DROP TABLE #data

CREATE TABLE #data (label VARCHAR(5),value int)
INSERT INTO #data VALUES (‘A’,1),(‘A’,2),(‘B’,1),(‘B’,3),(‘A’,1)
SELECT * FROM #data

/***************************************************
our column headings will come from the values column
***************************************************/

/*
Step 1: Create the pivot
*/

DECLARE @pivot varchar(MAX) = (select STUFF ((SELECT distinct ‘, ‘ + ‘[‘+ cast(value as varchar(1)) +’]’ AS ‘data()’
FROM #data
FOR XML PATH (”)) , 1, 1, ”))

print @pivot
/*******************************
Step 2: Create the select string
*******************************/

DECLARE @sql VARCHAR(MAX)=
‘SELECT * FROM (SELECT label,value from #data) as a PIVOT (count(value) for value IN (‘ + @pivot + ‘)) AS Piv’

PRINT @sql
EXEC (@sql)

Drop all foreign keys which reference a table

Problem: Cant drop a table because other tables reference it with their foriegn keys

Solution:

SELECT fk.name,’ALTER TABLE ‘ + s.name + ‘.’ + o_parent.name + ‘ DROP CONSTRAINT ‘ + fk.name ‘tsql’
FROM sys.foreign_keys fk
INNER JOIN sys.objects o on o.object_id = fk.referenced_object_id
INNER JOIN sys.objects o_parent on o_parent.object_id = fk.parent_object_id
INNER JOIN sys.schemas s on s.schema_id = o.schema_id
WHERE s.name = @schema and o.name=@table and o.object_id != o_parent.object_id

Copy the second column into a new window and execute

JSON formatted output in SQL Server

Problem: Customer wishes to see output from a stored proc in JSON format

Solution: 

As always first stop is Google and I found a nice JSON transformer script which will do the job

http://www.adampresley.com/2010/07/experimenting-with-sql-to-json-in-sql.html

The code we are interested in can be found here jsuntest.cs

Compile the code by opening up a command prompt at C:\Windows\Microsoft.NET\Framework\v3.5 and run csc.exe /target:library /out:jsun.dll jsuntest.cs

This will create a dll named jsun from jsun.cs file which we have placed in C:\Windows\Microsoft.NET\Framework\v3.5

Next we create the assembly in SQL Server

CREATE ASSEMBLY jsun FROM ‘C:\Windows\Microsoft.NET\Framework\v3.5\jsun.dll‘

Now Create a function in your database

CREATE FUNCTION [dbo].[SQLJsonEncode](@a [nvarchar](4000), @b [nvarchar](4000), @c [nvarchar](4000))
RETURNS [nvarchar](4000) WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [jsun].[JSONTransformer].[Encode]

finally create the stored proc which creates your dataset and outputs it in JSUN format

CREATE Proc jsun_demo

@customer_number INT

AS

DECLARE @resultSetXML VARCHAR(max);
DECLARE @rootNodeName VARCHAR(75);
DECLARE @elementNodeName VARCHAR(75);
DECLARE @result VARCHAR(max);

SET @rootNodeName = ‘customer’;
SET @elementNodeName = ‘customer’;

DECLARE @SQL VARCHAR(MAX)=

‘SELECT CustomerID,field1,field2 FROM SOMETABLES

WHERE CustomerID=’ + cast(@customer_number as varchar(10)) + ‘)’
DECLARE @result_set TABLE
(
CustomerNumber INT
,field1 VARCHAR(MAX)
,field2 VARCHAR(MAX)
)
insert @result_set
exec (@SQL)

SET @resultSetXML = (
select *
from @result_set customer FOR XML AUTO, ROOT(‘customer’)
)

SELECT dbo.SQLJsonEncode(@resultSetXML, @rootNodeName, @elementNodeName) AS jsonResult;

GO

Find amount of Memory consumed by SPID

Problem: Report Server running extremely slowly, memory possibly a culprit, find the cause

Solution: sys.dm_exec_requests contains a useful column named granted_query_memory which represents the number of pages allocated to each running process. To find the amount of memory allocated to the process multiply this value by 8.

The following query provides this information as well as other useful bits

select er.session_id, es.host_name, db_name(er.database_id) as 'database', er.status,
er.command, er.blocking_session_id as 'blocker',
er.wait_type, er.wait_time, er.wait_resource, er.percent_complete,er.start_time,
er.cpu_time, er.total_elapsed_time, er.total_elapsed_time - er.cpu_time as 'signal time',
er.reads, er.writes, er.logical_reads, er.granted_query_memory * 8 as 'query mem kb',
st.text, qp.query_plan
from sys.dm_exec_requests er
join sys.dm_exec_sessions es
on er.session_id = es.session_id
cross apply sys.dm_exec_sql_text(er.sql_handle) st
cross apply sys.dm_exec_query_plan(er.plan_handle) qp
where er.session_id > 50
and er.session_id @@SPID
order by er.session_id;

Unrelated note – I found an easy way of deterining when the SQL server started

select sqlserver_start_time from sys.dm_os_sys_info

http://sqlavenger.wordpress.com/2011/04/
http://stevenormrod.com/2012/04/sql-server-start-time/

Query Stored Procs and SQL within reports on your report server

Problem: Extract Stored Procs and SQL from reports on your report server

Solution:

WITH ItemContentBinaries AS
(
SELECT
ItemID,Name,[Type]
,CASE Type
WHEN 2 THEN 'Report'
WHEN 5 THEN 'Data Source'
WHEN 7 THEN 'Report Part'
WHEN 8 THEN 'Shared Dataset'
ELSE 'Other'
END AS TypeDescription
,CONVERT(varbinary(max),Content) AS Content
FROM ReportServer.dbo.Catalog
WHERE Type IN (2,5,7,8)
),
ItemContentNoBOM AS
(
SELECT
ItemID,Name,[Type],TypeDescription
,CASE
WHEN LEFT(Content,3) = 0xEFBBBF
THEN CONVERT(varbinary(max),SUBSTRING(Content,4,LEN(Content)))
ELSE
Content
END AS Content
FROM ItemContentBinaries
)
,ItemContentXML AS
(
SELECT
ItemID,Name,[Type],TypeDescription
,CONVERT(xml,Content) AS ContentXML
FROM ItemContentNoBOM
)
SELECT
ItemID,Name,[Type],TypeDescription,ContentXML
,ISNULL(Query.value('(./*:CommandType/text())[1]','nvarchar(1024)'),'Query') AS CommandType
,Query.value('(./*:CommandText/text())[1]','nvarchar(max)') AS CommandText
FROM ItemContentXML
--Get all the Query elements (The "*:" ignores any xml namespaces)
CROSS APPLY ItemContentXML.ContentXML.nodes('//*:Query') Queries(Query)

http://bretstateham.com/?m=201105

Transposing a dataset in SSRS

Problem: We wish to tranpose a dataset in SSRS so that the columns headings become row headings, we dont want to do this in T-SQL

Solution: Move the detail group from the default row group to a column group

The linked document below includes detailed images on how to achieve this, this post contains the same information minus images.

Transpose Dataset using SSRS

The above report is our starting point, we want our end point to be a report where the first row is a row of business entity ids the second is a row of first names etc
1 Add New Table

2 Delete Row Group (and related rows)

3. Add Column Group

4. Delete non detail columns

5. Add rows to the table

6. Bind each cell to a value in the dataset

7. Add a header column

Report Builder With Dynamic SQL

Problem: You wish to create a report using Report Builder which utilises a text type dataset with dynamic SQL

Solution:
Declare your sql variable in the query text
Write TSQL which populates the variable using dynamic sql and execute the variable

Manually create a parameter and map this parameter within the dataset properties.

You wont be able to run this query within the query designer and it wont recognise any fields therefore you will need to manually add each field to the dataset.
Example
DECLARE @sql VARCHAR(MAX) = ‘SELECT ‘ + cast(@i as varchar(10)) + ‘ as field1’
EXEC (@sql)

Create a parameter called @i
Map @i to @i

Add a new query field called field1 to the dataset

User unable to Set up subscription in “My Reports” folder

Problem: A user was unable to set up an email subscription to a report in his My Reports folder. The To field was greyed out.

Solution: Amended users security on My Reports folder in addition to My Reports the user also requires Content Manager