Archive

Archive for August, 2012

The EXECUTE permission was denied on the object ‘XXObject’ database ‘XXDatabase’, schema ‘XXschema’.

August 30, 2012 Leave a comment

In a recent build, we have got the following error.

The EXECUTE permission was denied on the object ‘XXObject’ database ‘XXDatabase’, schema ‘XXschema’.

Instead of granting the execute on just one object I started researching more.

Because it might be just temp fix and you want to find the root cause.

It looks like the user was able to execute all of the procedures before and now unable to execute any.

So the permanent solution is

GRANT EXECUTE ON SCHEMA::XXschema TO XXuser

Advertisements
Categories: Uncategorized

Shrink Sql Server database files

August 28, 2012 Leave a comment

This is the query to dynamically generate scripts to shrink sql server data files by keeping 20% space from free space.

SELECT ‘DBCC SHRINKFILE ( ”’, name , ””, ‘,’,

CAST(CAST(size/128.0 – CAST(FILEPROPERTY(name, ‘SpaceUsed’) AS int)/128.0 as INT)*.20 as INT)

, ‘);’

FROM sys.database_files;

Categories: Uncategorized

Taking care of Nan values in SSRS report

August 14, 2012 Leave a comment

1. n Design view, right-click the design surface outside the border of the report and click Report Properties.

2. Click Code.

3. In Custom code, add following code

Public Shared Function Ratio(Num1 as double, Num2 as double) AS object

IF ISNOTHING(Num2) Or Num2 = 0 Then

Ratio = 0

ELSEIF Num1 = 0 THEN

Ratio = 0

ELSE

Ratio = Num1 / Num2

END IF

End Function

Change your queries like this.

=Code.Ratio(Fields!yourfield1.Value, Fields!yourfield2.Value)

Categories: Uncategorized

Take backup of all SQL stored procedures

Use Management Studio for SQL 2005 and drill to the database you want the stored procedures from and right click the database name.

From the Task menu option select generate scripts to launch the wizard.

Click next.

Make sure the database you want is selected and click next and next again.

You should be on the Choose Object Types page and here just select Stored Procedures.

After clicking next you are on the Choose Stored Procedures page and now you can select the procedures that you want.

Click Finish to begin the process and when it finishes you will have a new query windows with a create script that you can save.

Categories: Uncategorized

SQL Server – Check when stored proc were modified

SELECT TOP 1000 [name]

,[object_id]

,[principal_id]

,[schema_id]

,[parent_object_id]

,[type]

,[type_desc]

,[create_date]

,[modify_date]

,[is_ms_shipped]

,[is_published]

,[is_schema_published]

FROM [RPT1].[sys].[all_objects]

where type_desc=’SQL_STORED_PROCEDURE’

order by modify_date desc

Categories: Uncategorized