Move SSRS 2008 Reports to a SSRS 2014 (or Above) Server

Problem:

There are more than 800 SSRS 2008 reports on an old server, and I will need to move them to a SSRS 2014 server which hosts some existing reports. Microsoft sample Reporting Services rs.exe script can be used to copy the content from old server to the new one.  However, the sample script only works for SQL Server 2008 R2 and above, and will not work for SQL Server 2008.

Solution:

The workaround is to migrate/copy all the reports to an empty SSRS 2014 server first, then run the Microsoft sample script to copy the reports from this SSRS 2014 server to the final destination server.

First step, merge all the SSRS 2008 reports from old server to the SSRS 2014 server, following the Microsoft online book: https://docs.microsoft.com/en-us/sql/reporting-services/install-windows/migrate-a-reporting-services-installation-native-mode?view=sql-server-2017.

The next step is to copy the reports from the SSRS 2014 server to the destination server by running the Microsoft sample script “ssrs_migration.rss”, which can be downloaded from here. This article explains how to use the script.

Limitations

·         Passwords are not migrated, and must be re-entered (e.g. data sources with stored credentials, or subscriptions with stored credentials)

Deleting server logins does not delete the database users associated with the logins

Problem

When you delete a login using SSMS, a message will show up:
"Deleting server logins does not delete the database users associated with the logins. To complete the process, delete the users in each database. It may be necessary to first transfer the ownership of schemas to new users. "

If you have multiple databases, then it will take some time to manually check each database and find out all the database user associated with the login you just deleted.

Solution

How to find all the database user associated with the login to be deleted? A cursor will do, however there is a better way: stored procedure sp_MSforeachdb. The following example will find all the database user associated with the server login "test4".  

exec sp_msForEachDb ' use [?] 

select db_name() as [database_name], [name] as [user member] 

from  

sys.database_principals p  

where p.name = ''test4'''

WARNING: The following job steps cannot be reached with the current job step flow logic

Problem

When I try to add a step to an existing SQL Server agent job, a warning message shows up:

WARNING: The following job steps cannot be reached with the current job step flow logic:

        [1] test0

 Is this the intended behavior?

Solution

Click "No" on the warning window, then double check the value in the "Start step" in the "Job Properties" window. In my case, although I have moved the new created step "test0" to the top of the "Job step list", the Start step is still "test1". Changing it to "test0" fixed the issue.

Unable to Delete SQL Server Maintenance Plan and SQL Server Agent Job

Problem

I have encountered an error recently when I was doing some test to build a maintenance plan in SSMS. I tried to delete a maintenance plan which I did not need any more, an error showed up:

Exception has been thrown by the target of an invocation. (mscorlib)
Additional information:
Index was out of range. Must be non-negative and less than the size of the collection. Parameter name: index (mscorlib).

When I tried to delete the SQL Server Agent job related to this maintenance plan, this error showed up:

Drop failed for Job "Database Maintenance.Subplan_1". (Microsoft.SqlServer.Smo)
Additional Information:
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
The DELETE statement conflicted with the REFERENCE constraint "FK_subplan_job_id". The conflict occurred in database "msdb", table "dbo.sysmaintplan_subplans", column 'job_id'.
The statement has been terminated. (Microsoft SQL Server, Error: 547)

Solution

We will have to remove the data entries of the jobs and  plans manually from "msdb" system database.
The following is the steps to be taken:

Step 1. find the job_id for the job

use this query to get the job_id:
use msdb
go
select job_id from sysjobs where name = 'yourjobname'

Replace yourjobname with your SQL Server Agent job name shown in SSMS. In my case, it is 'Database Maintenance.Subplan_1'.

Step 2. find the plan_id

use this query to get the plan_id
use msdb
go
select plan_id from dbo.sysmaintplan_subplans
where job_id ='yourjobid'

Replace 'yourjobid' with the job_id obtained from Step 1.

Step 3. delete the data entries of the jobs and plans

Use these queries to delete the records:
use msdb
go
delete from [dbo].[sysjobschedules] where job_id = 'yourjobid'
go
delete from [dbo].[sysmaintplan_log] where plan_id = 'yourplanid'
go
delete from [dbo].[sysmaintplan_subplans] where job_id = 'yourjobid'
go
delete from sysjobs where job_id = 'yourjobid'

Step 4. delete the maintenance plan package

Connect SSMS to Integrated Service.

The expand "Stored Packages" -- "MSDB" --- "Maintenance Plans", right click on the maintenance plan, and select "Delete" from the right click menu.

IFTTT Applet - Save Home Depot Electronic Receipt PDF to Google Drive Automatically

Home Depot provides a cool service for customers: sending electronic receipts by email.

It really helps me to manage the receipts. I am not good at keeping paper receipts, often lost them or put them in a place hard to find. The receipts are sent to customers as a PDF attachment of the email.

At first, I organize the receipts manually, adding a star or adding a label. After a while, I setup a filter to add a label "HomeDepotReceipt" to all the receipts automatically. Then I think it will be better to save all the receipt in one single folder in google drive.

IFTTT is a perfect tool to implement it. IFTTT stands for IF "This" Then "That". It is a free platform to automate your digital life, including email, social medias, cloud storage and devices etc. This tutorial will explain how to setup a IFTTT applets to save receipt PDF to google drive.

Step 1. Register IFTTT, connect Gmail and Google Drive

First, you will need to sign up an account with IFTTT if you don't have one.

Then search for "Gmail" service and "Connect", and search for "Google Drive" service and "Connect".

Step 2. Create a Filter for Home Depot Electronic Receipt in Gmail

Open Gmail, Check the checkbox next to the email with Home Depot electronic receipt, click "More", and "Filter messages like these". 

Then click "Create filter with this search".

check the checkbox next to "Apply the label", then select "New label" from the pull down menu on the right of "Apply the label".

Input a new label name, for example, "Homedepotreceipt", then select "Create" to create the label, click "Create filter" to create the filter.

The new created filter will assign label "Homedepotreceipt" to all the electronic receipts from Home Depot.

Step 3. Create the IFTTT Applet

Login to IFTTT.com, select "My Applets", then "New Applet". 

Select "This" from the New Applet window. 

Select "Gmail" service.

Select "New email in inbox labeled" trigger.

Input the label name, for example "Homedepotreceipt", then select "Create trigger".

Select "That", then select "Google Drive" as action service.

Select "Upload file from URL" as action, you can customize the path on your google drive to suit your needs, then click "Create Action" on the next screen.

The last step is to select "Finish" to complete the setup of the Applet. 

Next time when you receive an electronic receipt by email from Home Depot, the PDF will save to your google drive automatically.

Troubleshooting for Failed Maintenance Plan Reorganize Index Task

Problem

I have setup a scheduled maintenance plan for a server, to do basic maintenance tasks such as checking database integrity, reorganizing index and cleaning up history. It shows "failed" in job history.

Solution

1. Check the error log

The error log doesn' t provide enough information for trouble shooting:

"DBA Maintenance Plan.Subplan_1,Error,,ServerName,DBA Maintenance Plan.Subplan_1,,,The job failed. The Job was invoked by Schedule 17 (DBA Maintenance Plan.Subplan_1). The last step to run was step 1 (Subplan_1).,00:41:08,0,0,,,,0"

2. Revise Maintenance Plan to Enable "Send report to an email recipient"

Start SSMS, double click the maintenance plan, then click the "Reporting and Logging" icon, then check the button on the left of "Send report to an email recipient", and add an Agent operator.

Running the maintenance plan again will generate an email report. In my case, the report includes the following error message:

"Reorganize Index Task (servername)
Reorganize index on Local server connection
Databases that have a compatibility level of 70 (SQL Server version 7.0) will be skipped.
Databases: All user databases
Object: Tables and views
Compact large objects
Task start: 2017-09-03T10:32:03.
Task end: 2017-09-03T10:33:58.

Failed:(-1073548784) Executing the query "ALTER INDEX [Indexname" failed with the following error: "The index "Indexname" (partition 1) on table "tablename" cannot be reorganized because page level locking is disabled.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly."

3. Find the Table and Index across All Databases on the Server & Change Index Property

The above error message lists the name of the table and index name, but not the database name. We can use this query to determine which database they belong to:

sp_MSforeachdb 'SELECT "?" AS DB, * FROM [?].sys.tables WHERE name like ''tablename'''

Then we can use SSMS to modify the index property, change "Allow page locks" from "False" to "True".

Run the maintenance plan again, and there is no error anymore.

Trouble Shooting for "No global profile is configured"

Problem

A weekly maintenance plan failed this weekend. I checked the history of the maintenance job, and found the following error message:

01/29/2017 21:13:00, MaintenancePlan.Sundays, Error,, SQL001, MaintenancePlan.Sundays,,, The job failed.  The Job was invoked by Schedule 39 (! DBA - MaintenancePlan.Sundays).  The last step to run was step 1 (Sundays).,00:00:04,0,0,,,,0

01/29/2017 21:13:00, MaintenancePlan.Sundays, Error,1, SQL001, MaintenancePlan.Sundays, Sundays,, Executed as user: NT Service\SQLSERVERAGENT. Microsoft (R) SQL Server Execute Package Utility  Version 12.0.5000.0 for 64-bit  Copyright (C) Microsoft Corporation. All rights reserved.    Started:  9:13:00 PM  Progress: 2017-01-29 21:13:01.80     Source: {06474EBF-18F1-49F5-960B-B11B5686CA69}      Executing query "DECLARE @Guid UNIQUEIDENTIFIER      EXECUTE msdb..sp...".: 100% complete  End Progress  Error: 2017-01-29 21:13:02.40     Code: 0xC002F210     Source: Notify Operator Task Execute SQL Task     Description: Executing the query "EXECUTE msdb.dbo.sp_notify_operator @name=N'The DB..." failed with the following error: "No global profile is configured. Specify a profile name in the @profile_name parameter.". Possible failure reasons: Problems with the query "ResultSet" property not set correctly parameters not set correctly or connection not established correctly.  End Error  Warning: 2017-01-29 21:13:02.40     Code: 0x80019002     Source: Sundays      Description: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED.  The Execution method succeeded but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.  End Warning  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  9:13:00 PM  Finished: 9:13:02 PM  Elapsed:  2.464 seconds.  The package execution failed.  The step failed.,00:00:04,0,0,,,,0

Solution

The reason is that the database mail profile we have configured, is not the global/default any more. Change the profile to "Default" will fix the problem.

Steps:

1. Right click Database Mail, select Configure Database Mail from the right-click menu.

2. Select Next on the Database Mail Configuration Wizard window.

3. Select Manage profile security in the window.

4. Select Yes, then Next in the Default Profile field.

5. Select Finish to complete the configuration in the next window

Trouble Shooting for Error 8152 and Error 3621

Problem

We have an ETL job which failed recently, an alert was sent to us:

SQL Server Job System: 'XXX-XXXXX' completed on \\SQL001

JOB RUN:            'XXX-XXXXX' was run on 1/1/2017 at 0:00:00 PM

DURATION:         0 hours, 3 minutes, 37 seconds

STATUS:               Failed

MESSAGES:         The job failed.  The Job was invoked by Schedule XX (Night).  The last step to run was step 1 (EXECUTE [DBXXXXX].[dbo].[SPXXX]).

Solution

Start SSMS, expand SQL Server Agent - Jobs, right click on the job 'XXX-XXXXX', select "View History" from the right-click menu.

This error message can be found in the log:

"START:[Table001] [SQLSTATE 01000] (Message 50000)  String or binary data would be truncated. [SQLSTATE 22001] (Error 8152)  The statement has been terminated. [SQLSTATE 01000] (Error 3621).  The step failed.,02:06:11,16,3621,,,,0"

This error is usually caused by the inserting data (VARCHAR or CHAR data type) is longer than the length of the column in the destination table. In our case, this statement in SP [DBXXXXX].[dbo].[SPXXX] caused the problem:

INSERT INTO [DBXXXXX].[dbo].[Table001]

(
[column1],[column2],[column3]...

)

(

SELECT 

[column1],[column2],[column3]...

from [SQL002].[DBYYYYY].[dbo].[Table001]
where identity_column > @V1
The length of columnA in destination table is 20, is 2 in source table. The solution is to change the length of columnA  in destination to 20. Then the job ran successfully.

Troubleshooting for "Login failed for user 'domainname\computername$'

Problem

I have received an email alert from a server:
Title: SQL Server Alert System: 'computername - Sev:014 - INSUFFICIENT PERMISSIONS' occurred on \\computername
DESCRIPTION:    Login failed for user 'domainname\computername$'. Reason: Could not find a login matching the name provided. [CLIENT: <local machine>]
The error message in the error log:

Login failed for user 'domainname\computername'. Reason: Could not find a login matching the name provided. [CLIENT: <local machine>]
Error: 18456   Severity: 14   State: 5.

Solution

My first step is to check the Event log on the server:

The actual user is Network Service. The Network Service account is a built-in account that has more access to resources and objects than members of the Users group. Services that run as the Network Service account access network resources by using the credentials of the computer account in the format domainname\computername$. The actual name of the account is NT AUTHORITY\NETWORK SERVICE.

In my case, adding computer account domainname\computername$ into the SQL Server instance solved the issue. Another option is to add the NT AUTHORITY\NETWORK SERVICE into SQL Server instance.

Patching SQL Server 2014 Failover Instance

We have a SQL Server 2014 failover instance need to patched. The failover instance include 2 nodes -- SQL001 (active) & SQL002(passive), the build before patching is RTM with no service pack and cumulative update installed. The patch is cumulative update 14 for RTM.

Install CU 14 on SQL Server 2014 Failover Instance

Step 1. Install CU 14 on passive node (SQL002)

We will install CU 14 on the passive node first.

1. Login to the passive node SQL002, run "SQLServer2014-KB3158271-x64.exe" to install CU 14.                                                              
   
2. Accept  License Terms
   
3. Select the features to be installed.
   
4. Select "Next" when files in use check completed.
   
5. Select "Update" to start installation.
   
6. Select "Close" when installation completed.
   

Step 2 Manually Failover

In order to patch SQL001, we will manually failover to SQL002, and make SQL001 passive node.

1. From Server Manager, select "Tools" -- "Failober Cluster Manager".
   
2. Select the cluster name in Failober Cluster Manager, then click "Roles" -- "SQL Server (MSSQLSERVER)", select "Move" -- "Select Node..." in Action panel.
   
3. Select SQL002 in the Move Clustered Role window.
   
4. In Roles panel, the Owner Node will change to "SQL002", the Status change to "Pending".
   

5. The failover is completed when the Status change to "Running".

   

Step 3 Install CU 14 on SQL001

1. Install CU 14 on SQL001 follow the procedure in Step 1.
2. After installation completed, manually failover to SQL001. The installation completed.

Reference

https://blogs.msdn.microsoft.com/john_daskalakis/2014/09/05/how-to-install-a-service-pack-at-a-sql-server-2012-failover-instance-best-practices/