Enable SQL Job notifications for all Jobs in single shot

Please execut the below script on MSDB database .... :)

declare @sql nvarchar(max) = '';

select
@sql += N'exec msdb.dbo.sp_update_job @job_name = ''' + name + N''',
@notify_level_email=2,
@notify_level_netsend=2,
@notify_level_page=2,
@notify_email_operator_name=N''NMS_Alerts''
' from msdb.dbo.sysjobs
order by name;

print @sql;

exec (@sql);

Cannot alter the user 'dbo'. (Microsoft SQL Server, Error: 15150)

To resolve the below issue: Cannot alter the user 'dbo'. (Microsoft SQL Server, Error: 15150)

 FIX: If he is already mapped as a database owner, first we need to change the database owner to another user. Then afterwards we are able to grant him individual permissions as needed...

 Thanks,
Satish Kumar.

Upgrade edition in SQL Server

Hi All, Some times we are in the situation of Upgrading the current version of SQL Server to Different versions.. like Developer to Enterprise. During this scenario, the below links would be help full with great charm.....! Upgrade from SQL Server Developer to SQL Server Enterprise is supported: http://msdn.microsoft.com/en-us/library/ms143393.aspx How to: Upgrade to a Different Editions of SQL Server http://msdn.microsoft.com/en-us/library/cc707783.aspx Thanks, Satish Kumar.

[264] An attempt was made to send an email when no email session has been established

The following error message found in the SQL Agent logs:

Message: [264] An attempt was made to send an email when no email session has been established

This is known issue and workaround provided as below:

• right-click on SQL Server Agent and select "Properties."
• Click "Alert System"
• Unclick "Enable mail profile"
• Right-click SQL Server Agent again and select "Properties."
• Click "Alert System"
• Re-click "Enable mail profile"
• Click "OK"

After performing the above actions.. to take affect please restart the SQL Agent:
• Restart SQL Server Agent

Please let us know your comments…

Thanks,
Satish Kumar.

database principal owns a schema in the database, and cannot be dropped

Hi ALL,

Some times we need to drop the users on Database, during the phase we may encounter the below situtation like:

The database principal owns a schema in the database, and cannot be dropped. (Microsoft SQL Server, Error: 15138)

Check if any schmes are pointed user (unable to delete)

select * from sys.schemas where principal_id = user_id('USERNAME')

if you find any thing: do the transfer those dbo by executing the below command.

SELECT N'ALTER AUTHORIZATION ON SCHEMA::' + QUOTENAME(name) + N' TO dbo;'
FROM sys.schemas
WHERE principal_id = USER_ID('USERNAME');

NOTE: copy the above output and execute...

After doing this, you are able to DROP the desrired users.... :)

Thanks,
Satish Kumar.

Finding Database with MDF and LDF file sizes

Hi All,

Some times we will notify the requests like databases sizes with MDF and LDF files with locations. At the case please use the beloe script:

SELECT DB_NAME(mf.database_id) AS databaseName,
mf.physical_name,
size_on_disk_bytes / 1024 / 1024 AS size_in_MB
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS divfs
JOIN sys.master_files AS mf ON mf.database_id = divfs.database_id
AND mf.file_id = divfs.file_id
ORDER BY databaseName

Note***: If the value is in 0 (zero) means - the size is less than 1 MB because the sizes are calculated in MB’s.

Thanks,
Satish Kumar.

Msg 7311, Level 16, State 2, Line 1

The reason for this error message is as follows: “You may receive an error message when you try to run distributed queries from a 64-bit SQL Server 2005 client to a linked 32-bit SQL Server 2000 server”.

Resolution:

To Fix the above issue we have two options:

* Creating Store procedure on Master database (or)

1) Connect to SQL Server
2) Create the below store procedure in Master database
2.1) create procedure sp_tables_info_rowset_64 @table_name sysname, @table_schema sysname = null, @table_type nvarchar(255) = null
as
declare @Result int
select @Result = 0
exec @Result = sp_tables_info_rowset @table_name, @table_schema, @table_type

2.2) GRANT EXECUTE ON sp_tables_info_rowset_64 TO USER

* Updating SQL Server system catalog files

Please refer the KB article to do this --> http://support.microsoft.com/kb/906954

Note: the kb article updates several things on Master database instaed of updating system catalogs, i would prefer step 1... bcoz i m done with step 1 and its successfull with out any issues :)

Thanks,
Satish Kumar.