SSRS Reporting Services IE Issues

SSRS Prompt for username and password in IE
This is for IE9 with Windows 7 operating systems
1. Open Internet Explorer (IE9) and from the tools menu, choose Internet Options
2. From the Security Tab, choose Local intranet and click on Sites
3. Click on Advanced button
4. Type in localhost in Add this website text field and click on Add button (Un-tick the box Require server verification (https:) for all sites in this zone)

There are two issues that you will find out when you finish the installation of SQL Server 2012 or SQL Server 2008 R2:
1. When you try to access Reporting Services using your IE browser, it will constantly prompt you to enter username and password.
2. After the successful entry of login details in IE, you will get the following error message:
User ‘Domain\User’ does not have required permissions. Verify that sufficient permissions have been granted and Windows User Account Control (UAC) restrictions have been addressed.
1. From Start > All Programs > Internet Explorer, Right Click and choose Run as administrator
2, Enter the Reporting Services URL: http://localhost/Reports_SQLExpress
3. Running as administrator will allow you to have access to Site Settings. Click on it
4. You will see the General Site Settings page
5. Click on Security from the left side menu and choose New Role Assignment 6. Add the username or Group that you want to grant permission to and select the appropriate role
7. Now go to Home page and click on Folder Settings. Add the username or Group that you want to grant permission to the main Home page
8. All done now. You can normally open IE and it will work without an AUC error. You can also create Folders within the Home page and restrict access to specific users.

Configure the Unattended Execution Account
1.Start the Reporting Services Configuration tool and connect to the report server instance you want to configure.
2.On the Execution Account page, select Specify an execution account.
3.Type the account and password, retype the password, and then click Apply

Run SSIS ETL from SQL

SET @SQLQuery= 'DTEXEC /ISSERVER "\"\SSISDB\TWC\TWC_ABAQAS\'+@ETLName+'.dtsx\"" /SERVER "\".\"" /X86 /ENVREFERENCE 1 /Par "\"$ServerOption::LOGGING_LEVEL(Int16)\"";1 /Par "\"$ServerOption::SYNCHRONIZED(Boolean)\"";True /Par "\"$Package::sProcessName(String)\"";'+@ProcessName+' /Par "\"$Package::sJobName(String)\"";'+@JobName+' /CALLERINFO SQLAGENT /REPORTING E'

EXECUTE @returncode = master .. xp_cmdshell @SQLQuery,NO_OUTPUT

Common Table Expressions (CTE) and Recursive Queries

A CTE is a temporary result set and are similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. A CTE is generally considered to be more readable than a derived table and does not require the extra effort of declaring a Temp Table . CTE is more powerful than a derived table as it can also be self-referencing, or even referenced multiple times in the same query.

CTE defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement

CTE can be referenced multiple ties in the same statement

WITH EmpCTE

AS ( SELECT EmpID, FirstName, LastName, ManagerID, 1 Level

FROM Employee

WHERE ManagerID IS NULL

UNION ALL

SELECT EmpID, FirstName, LastName, ManagerID, Level+1

FROM Employee

INNER JOIN EmpCTE ON Employee.ManagerID = EmpCTE.EmpID

WHERE Employee.ManagerID IS NOT NULL )

SELECT *

FROM EmpCTE

PIVOT and UNPIVOT in SQL Server

Transforming rows to columns (PIVOT/CROSS TAB) and columns to rows is (UNPIVOT). Reverse operation of PIVOT is UNPIVOT.

Actual Table :

StudentName	Marks	Grade
A	10	I
B	20	II
C	40	IV
D	30	III

Select studentname, [I], [II], [III], [IV]
 from
( Select grade, studentname, marks from tbl_student) as sourcetable
Pivot (  avg(marks) for grade in ([I],[II],[III],[IV)) as pivotable order by IV desc,III desc,II desc,I desc

PIVOT Result Table :

StudentName	I	II	III	IV
A	10
B		20
C				40
D			30

Actual Table :

StudentName	I	II	III	IV
A	10
B		20
C				40
D			30

select studentname,Marks,Grade

from tbl_stdmarksdata

unpivot

(Marks for Grade in (I,II,III,IV) ) as tblunpvt

UNPIVOT Result Table :

StudentName	Marks	Grade
A	10	I
B	20	II
C	40	IV
D	30	III

Change Data Capture in SQL Server 2008

CDC is one of the new data tracking and capturing features of SQL Server 2008. It only tracks changes in user-created tables. Because captured data is then stored in relational tables, it can be easily accessed and retrieved subsequently, using regular T-SQL.

When you apply Change Data Capture features on a database table, a mirror of the tracked table is created with the same column structure of the original table, but with additional columns that include the metadata used to summarize the nature of the change in the database table row.  The SQL Server DBA can then easily monitor the activity for the logged table using these new audit tables .

CDC first has to be enabled for the database. Because CDC is a table-level feature, it then has to be enabled for each table to be tracked. you will see that a schema with the name ‘cdc’ has now been  created.

Some System Tables will have been created within the  database as part of the cdc schema.

The table which have been created are listed here.

· cdc.captured_columns – This table returns result for list of captured column.

· cdc.change_tables – This table returns list of all the tables which are enabled for capture.

· cdc.ddl_history – This table contains history of all the DDL changes since capture data enabled.

· cdc.index_columns – This table contains indexes associated with change table.

· cdc.lsn_time_mapping – This table maps LSN number (for which we will learn later) and time.

Before enabling CDC at the table level, make sure that you have  enabled SQL Server Agent. When CDC is enabled on a table, it creates two CDC-related jobs that are specific to the database,  and executed using SQL Server Agent. Without SQL Server Agent enabled, these jobs will not execute.

Additionally, it is very important to understand the role of the required parameter @role_name. If there is any restriction of how data should be extracted from database, this option is used to specify any role which is following restrictions and gating access to data to this option if there is one.  If you do not specify any role and, instead, pass a NULL value, data access to this changed table will not be tracked and will be available to access by everybody.

The Stored Procedure sys.sp_cdc_enable_table enables CDC. There are several options available with this SP but we will only mention the required options for this SP. CDC is very powerful and versatile tool. By understanding the Stored Procedure  sys.sp_cdc_enable_table you will gain the true potential of the CDC feature. One more thing to notice is that when these jobs are created they are automatically enabled as well

As you will see there are five additional columnsto the mirrored original table

· __$start_lsn

· __$end_lsn

· __$seqval

· __$operation

· __$update_mask

There are two values which are very important to us is __$operation and __$update_mask.

Column _$operation contains value which corresponds to DML Operations. Following is quick list of value and its corresponding meaning.

· Delete Statement = 1

· Insert Statement = 2

· Value before Update Statement = 3

· Value after Update Statement = 4

The column _$update_mask shows, via a bitmap,   which columns were updated in the DML operation that was specified by _$operation.  If this was  a DELETE or INSERT operation,   all columns are updated and so the mask contains value which has all 1’s in it. This mask is contains value which is formed with Bit values.

stored procedure sys.sp_cdc_enable_table has several parameters

The parameter @capture_instance determines the name of the capture instance for this table. The name itself has no impact on performance. However, this parameter can be used to create a second capture instance for the same table. The feature of having a second capture instance is only intended for schema upgrade scenarios where a change to the schema of the source table is performed. The old capture instance should be disabled as soon as it is no longer needed, because having two capture instances active on one table means that twice as much change data needs to be written. This can significantly impact performance.

When you have a schema change on your source table, you should do the following:

1.      Change the schema of your source table.

2.      Create a new capture instance. That new capture instance is created with the new (changed) schema of the source table.

3.      Wait for a change to occur, and then read the minimum LSN from the new capture instance (using sys.fn_cdc_get_min_lsn(new capture instance name)).

4.      Read and process all changes from the old capture instance up to but not including the first LSN from the new capture instance.

5.      Disable the old capture instance.

6.      Update all references to the old capture instance name with the new name.

7.      Continue reading from the new capture instance.

The parameter @captured_column_listdetermines which columns of the source table are included in the change table and captured by change data capture. If this parameter is not specified or NULL, all columns of the source table are included in the change table. We found that the number and size of captured columns has a significant impact on change data capture performance and required disk space. Change data capture performance is generally better when the number of captured columns is smaller because the amount of data that needs to be written to the change tables is smaller.

The parameter @supports_net_changes determines whether net change queries to the change table (using cdc.fn_cdc_get_net_changes_<capture_instance>) are possible. Net change queries result in only one change row for each source row that was changed, independent of the number of changes. For instance, if stock market prices are tracked per stock and the price column is updated several times a day, an all changes query would return every change of every stock price in the queried LSN interval. A net change query would result in only one change row per stock with the final price in the data.

When @supports_net_changes is set to 1, an additional nonclustered index is created on the change table and the net changes query function is created. Because this index needs to be maintained, we found that enabling net changes can have negative impact on change data capture performance.

The parameter @filegroup_name determines the name of the filegroup where the change tables are created. If this parameter is not specified, the default filegroup will be used. Because in most cases the default filegroup is PRIMARY and it is a best practice to keep the PRIMARY filegroup small, a filegroup name should always be specified.

If you are using AdventureWorks database, it creates the jobs with following names.

1.      cdc.AdventureWorks_capture – When this job is executed it runs the system stored procedure sys.sp_MScdc_capture_job.  The procedure sys.sp_cdc_scan  is called internally by sys.sp_MScdc_capture_job. This procedure cannot be executed explicitly when a change data capture log scan operation is already active or when the database is enabled for transactional replication. This system SP enables SQL Server Agent, which in facts enable Change Data Capture feature.

2.      cdc.AdventureWorks_cleanup – When this job is executed it runs the system stored procedure sys.sp_MScdc_cleanup_job. This system SP cleans up database changes tables.

The capture job created in SQL Server Agent for change data capture (typically named cdc.database name_capture) contains only a call to the procedure sys.sp_MScdc_capture_job without parameters. This procedure determines the scan job parameters and calls sys.sp_cdc_scan with these parameters. The procedure sys.sp_cdc_scan does the actual work by scanning the log (using sys.sp_replcmds) and inserting the data that needs to be captured into the change tables.

There are four parameters in sys.sp_cdc_scan that determine the behavior of the capture job.

The first parameter is continuous (default value 1). It determines whether the capture job runs continuously (value 1) or exits after one scan phase (one shot mode, value 0). One shot mode is recommended for testing only, not for production use. The main reason for this is that the log records stay active until change data capture has processed them. So, the log will keep growing when the scan job is not running.

The other three parameters determine how often and how many transactions are read from the log and inserted to the change tables.

The parameter maxtrans (default value 500) determines how many transactions are read from the log and written to the change tables. This write is done in one transaction. The parameter maxscans (the default value is 10) determines how many of these scan cycles are attempted before the job is ended (continuous = 0) or before pausing for an interval (continuous=1). The length of this pause interval is set by the parameter pollinginterval (in seconds, with a default value of 5 seconds). WAITFOR is executed when a scan cycle drains the log completely or when maxscans scan cycles are completed.

The scan job parameters can be changed by using sys.sp_cdc_change_job. Because these parameters are only read during the initialization of the capture job, the capture job needs to be stopped using EXEC sys.sp_cdc_stop_job @job_type = 'capture' and subsequently restarted using EXEC sys.sp_cdc_start_job @job_type = 'capture' so the changed parameters are applied.