Reporting Service Error - The report server cannot decrypt the symmetric key used to access sensitive or encrypted data in a report server database

---

I ran into one issue while configuration of the reporting service in sql server and got the error to load the reports,

The report server cannot decrypt the symmetric key used to access sensitive or encrypted data in a report server database. You must either restore a backup key or delete all encrypted content. Check the documentation for more information. (rsReportServerDisabled) (rsRPCError)

Finally i got the solution to resolve this issue as following,
Go to the Report Service Configuration Option and delete the encrypted content.  Please take a backup of key before deleting it.

Sql Server Trigger

SQL Server Trigger

- A type of stored procedure that run automatically when an event occurs within database server.

3 Type of Trigger:

1) DML: Data Manipulation Language

   - Run on manipulation of data event such as Insert, Update or Delete

2) DDL: Data Definition Language

   - Run on manipulation of data structure or schema event such as CREATE, ALTER and DROP

3) Logon:

   - Run on user Logon event when user session is being established.

   
You can get more detail from Microsoft website, but our site article is meant cover practicle most used code that include tips and tricks to solve some usual problem.
Below is a sample of a DML trigger that does the following:
1) One trigger that handle event for all 3 Insert, Update, Delete
2) Answer common questions such as:
   1)How to distinguish which event is fired from Insert, Update or Delete?
       - You determine that through the two tables provided by trigger result:
           1) Inserted: stores all new record being inserted
           2) Deleted: stores all record being deleted
           NOTE:
               In an update action, SQL server would do 2 actions:
                   1) delete the original row
                   2) inserted a new updated row
               -> Therefore, in an update statement, you will see old record in Deleted table, and updated row in Inserted table
   2) How to know which column is updated?
       -> There are a SQL Server called COLUMNS_UPDATED() which return binary value that represents all columns within a table and tell you which one is updated based on its bit position being (0/1) -> 0: not updated, 1: updated
           Example: I have 5 columns, and my 3rd column is updated and others are not:
               COLUMNS_UPDATED() = 00100 = 4
               To know if 3 column is updated, you mask the binary with 4 and if the result greater than 0, then it's updated.
               (COLUMNS_UPDATED() & 4)>0
                   -> this will be true if 3 columns is updated
       -> The example above is too much work and it's still doesn't give you the column name or anything related to the column so you know right there.
           -> Therefore, we'll make use of a function called sys.fn_IsBitSetInBitmask(), buildin function to get the detail data you want

SQL Server Transaction Log Full Error

Error:

"The
transaction log for database "DatabaseName" is full. To find out why space in
the log cannot be reused, see the log_reuse_wait_desc column in Sys.database on
"Source where sql statement is run from the code"

Cause:

SQL
Server trying to log the changes in database to its log file .LDF and the disk
space is out or it hits the limits allowed by SQL Server.

In
Sql Server Management Studio -> Right click on Database -> Select
Properties -> Select Files to find out where the .MDF and .LDF files are
stored

->
Look at the AutoGrouth column

Fix:

Backup
your DB and shrink the log file

USE
YourDatabase;

GO

ALTER
DATABASE YourDatabase SET RECOVERY SIMPLE;

GO

DBCC
SHRINKFILE (YourDatabase_Log, 10); --10MB

GO

ALTER
DATABASE AdventureWorks SET RECOVERY FULL;

GO

Good
Reference Link: http://blogs.lessthandot.com/index.php/DataMgmt/DBAdmin/MSSQLServerAdmin/do-not-truncate-your-ldf-files

Enable Remote Access to SQL Server 2008 R2 on Win 2008 R2

Enable Remote Access to SQL Server 2008 R2 hosted on Window Server 2008 R2

Grant User Access
To allow access to users in a Windows domain
1.Open the SQL Server Management Tool.
2.Log on to the instance of SQL Server. This step presumes that you are the administrator of the computer and the instance of SQL Server.
3.In Object Explorer, expand the Security node.
4.Right-click Logins and click New Login....
5.Type the name of the user to allow by using the domain\username format.
6.Click Search and use the dialog to confirm that the user is a Windows authenticated user. Then close the New Login dialog.
7.In Object Explorer, expand the Databases node.
8.Expand the node of the database that you want to grant access to.
9.Expand the Security node of the database.
10.Right-click the Users node and click New User.
11.In the User name box, type in a name for the user.
12.In the Login name box, type the name of the user by using the domain\username format.
13.In the Role Members list box, select the role that you want to grant the user for the database. Common options are db_dataread and db_datawriter.

Enable a Port
1.On the Start menu, click Run, type WF.msc, and then click OK.
2.In the Windows Firewall with Advanced Security, in the left pane, right-click Inbound Rules, and then click New Rule in the action pane.
3.In the Rule Type dialog box, select Port, and then click Next.
4.In the Protocol and Ports dialog box, select TCP. Select Specific local ports, and then type the port number of the instance of the Database Engine, such as 1433 for the default instance. Click Next.
5.In the Action dialog box, select Allow the connection, and then click Next.
6.In the Profile dialog box, select any profiles that describe the computer connection environment when you want to connect to the Database Engine, and then click Next.
7.In the Name dialog box, type a name and description for this rule, and then click Finish.

Set a Protocol
This is also a one-time procedure that you perform on the computer that hosts the instance of SQL Server to indicate which protocol to use when communicating with remote clients. This procedure uses the TCP/IP protocol.
To set the protocol
1.Open the SQL Server Configuration Manager application. This is found in the Configuration Tools folder of the Microsoft SQL Server 2008 folder.
2.Expand the SQL Server Network Configuration Manager node.
3.Expand the SQL Server Network Configuration node.
4.Click Protocols for MSSQLSERVER.
5.Right-click TCP/IP and click Enable.

After setting the protocol you must restart the SQL Server service.
To restart the SQL Server service
1.In the SQL Server Configuration Manager application, click the SQL Server Services node.
2.Right-click SQL Server (MSSQLSERVER) and click Restart.

Filegroup Backups

Filegroup Backups

A quick runthrough experimenting with Filegroup Backups

view plaincopy to clipboardprint?

1. BACKUP DATABASE Adventureworks FILEGROUP = 'PRIMARY'   
2. TO DISK = 'c:\Adventureworks.BAK'  

Msg 3004, Level 16, State 1, Line 1

The primary filegroup cannot be backed up as a file backup because the database is using the SIMPLE recovery model. Consider taking a partial backup by specifying READ_WRITE_FILEGROUPS.

Msg 3013, Level 16, State 1, Line 1

BACKUP DATABASE is terminating abnormally.

So , to use this technique, you can't use SIMPLE recovery mode.
Why am i using SIMPLE? - It's a development box!
Setting to FULL resolves this -

view plaincopy to clipboardprint?

1. ALTER DATABASE [AdventureWorks] SET RECOVERY FULL WITH NO_WAIT  
2. GO  
3.   
4. BACKUP DATABASE Adventureworks FILEGROUP = 'PRIMARY'   
5. TO DISK = 'c:\Adventureworks.BAK'  

Processed 21280 pages for database 'Adventureworks', file 'AdventureWorks_Data' on file 2.

Processed 1 pages for database 'Adventureworks', file 'AdventureWorks_Log' on file 2.

BACKUP DATABASE...FILE= successfully processed 21281 pages in 8.921 seconds (18.636 MB/sec).

Using the system table sys.filegroups, i can build a list of backup commands to backup all filegroups, like this ...

view plaincopy to clipboardprint?

1. DECLARE @backupPath VARCHAR(500)  
2. SET @backupPath = 'd:\sqlbackups\'   
3.   
4. DECLARE @backuptimestamp VARCHAR(30)  
5. SET @backuptimestamp =  REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(30),GETDATE(),20),'-',''),':',''),' ','_')  
6.   
7. SELECT 'BACKUP DATABASE [' + DB_NAME()+'] FILEGROUP = ''' + name + ''' TO DISK = ''' + @backupPath + @backuptimestamp + '_' + DB_NAME() + '_' + name +'.BAK''' + ' WITH COMPRESSION ' AS BACKUPCOMMAND  
8. FROM sys.filegroups  

view plaincopy to clipboardprint?

1.    

NB : My commands feature the compression setting as am on SQL 2008 Enterprise.

Read more: http://sqlsolace.blogspot.com/search/label/backups#ixzz2EpPK313j