June 2013 – SQLMint

Maximum Request Length Exceeded when Deploying a Model to SSRS

Error “Maximum request length exceeded” in Reporting Services

This error came up while trying to upload a 3.9Mb SSRS report to a new SQL 2008 R2 reporting server.

Solution:

open the web.config file found in:

C:Program FilesMicrosoft SQL ServerMSRS10_50.#####Reporting ServicesReportServer

#### stands for your instance name

Modify line 40 or the line with below:

to:

Restart just the reporting service. There is no need to restart the database service or reboot the server box.

You should now be able to upload reports up to 15000kb ie 15mb in this case. You can modify the value as needed.

Restore msdb ssytem database from backup

Quick steps to restore the SQL Server system database – msdb from a valid backup file, especially useful if you run SSIS packages stored in the msdb database and SQL server agent jobs. Doing this will bring back all SSIS packages as well as existing job schedules.

– Set msdb database to restricted access

USE [master]
GO
ALTER DATABASE [msdb] SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE
GO

– shut down the SQL server agent service for the SQL instance you are running

– restore the msdb database from a good backup file

– restart the SQL server agent service

SQL Server 2014

Click here to learn more

“Microsoft SQL Server 2014 builds on the mission-critical capabilities delivered in the prior release by providing breakthrough performance, availability and manageability for your mission critical applications. SQL Server 2014 delivers new in-memory capabilities built into the core database for OLTP and data warehousing, which complement our existing in-memory data warehousing and BI capabilities for the most comprehensive in-memory database solution in the market.”

Truncate Requires Control Rights

Below is an output from a sql login session, the sql login account has select permissions, and attempts to truncate [quickly delete rows without logging each transaction] the table which has over 10,000 rows of data.

SQL Code

truncate table [AdventureWorks2012].[dbo].[MyList]

Result ==========

Msg 1088, Level 16, State 7, Line 1 Cannot find the object “MyList” because it does not exist or you do not have permissions.

Solution

use [AdventureWorks2012]
GO
GRANT CONTROL ON [dbo].[MyList] TO [myuid]
GO

Result

In the above example, myuid is the name of the sql login account

SELECT INTO

This is a quick way to copy data from one table to the other. The other table will be created.

See example below, using the AdventureWorks2012 sample database from Microsoft

SELECT
[AddressID]       ,[AddressLine1]       ,[AddressLine2]       ,[City]       ,[StateProvinceID]       ,[PostalCode]       ,[SpatialLocation]       ,[rowguid]       ,[ModifiedDate]
into   MyList
FROM   [AdventureWorks2012].[Person].[Address]

Result (19614 row(s) affected)