Change of safety level not allowed.

Today I was trying to change the transaction safety level in a database mirroring session from high-safety mode (safety full) to high-performance mode using T-SQL and I received below error message:

Msg 1473, Level 16, State 6, Line 1

This SQL Server edition does not allow changing the safety level. ALTER DATABASE command failed.

The reason for above error is: 
High-Performance Mode (Asynchronous Database Mirroring) is only supported on Enterprise Edition or Datacenter Edition.

There is no default instance of SQL Server on the computer

Sometime when trying to browse the SSIS packages from SQL Server Management Studio the following error can come:

The SQL Server specified in Integration Services service configuration is not present or is not available. This might occur when there is no default instance of SQL Server on the computer. For more information, see the topic "Configuring the Integration Services Service" in SQL Server 2008 Books Online.

This error is coming when the default instance runs on a different port than the default one (1433) or when you have only named SQL instances on the machine. 

To solve the error you had to make some changes in the MsDtsSrvr.ini.xml file:

SQL 2005 location:
C:\Program Files\Microsoft SQL Server\90\DTS\Binn\MsDtsSrvr.ini.xml

SQL 2008 location:
C:\Program Files\Microsoft SQL Server\100\DTS\Binn\MsDtsSrvr.ini.xml

SQL 2012 location:
C:\Program Files\Microsoft SQL Server\110\DTS\Binn\MsDtsSrvr.ini.xml

Modify the MsDtsSrvr.ini.xml file to point to the your named instance or to the default instance with the right port, and then restart the SSIS service:

<?xml version="1.0" encoding="utf-8"?>

<DtsServiceConfiguration xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

  <StopExecutingPackagesOnShutdown>true</StopExecutingPackagesOnShutdown>

  <TopLevelFolders>

    <Folder xsi:type="SqlServerFolder">

      <Name>MSDB</Name>

      <ServerName>SERVER_NAME\INSTANCE_NAME</ServerName>

    </Folder>

    <Folder xsi:type="FileSystemFolder">

      <Name>File System</Name>

      <StorePath>..\Packages</StorePath>

    </Folder>

  </TopLevelFolders>

</DtsServiceConfiguration>

OR:

<?xml version="1.0" encoding="utf-8"?>

<DtsServiceConfiguration xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

  <StopExecutingPackagesOnShutdown>true</StopExecutingPackagesOnShutdown>

  <TopLevelFolders>

    <Folder xsi:type="SqlServerFolder">

      <Name>MSDB</Name>

      <ServerName>SERVER_NAME,PORT_NUMBER</ServerName>

    </Folder>

    <Folder xsi:type="FileSystemFolder">

      <Name>File System</Name>

      <StorePath>..\Packages</StorePath>

    </Folder>

  </TopLevelFolders>

</DtsServiceConfiguration>

Find outdated statistics in SQL Server

SQL server uses the STATISTICS to find the appropriate Query plan.

if you are experiencing performance issues for your queries than it may be due to outdated statistics. An outdated statistic can make the sql server choose a wrong plan, use the following query to find outdated stats:

SELECT OBJECT_NAME(ID) AS [Table], SI.Name, STATS_DATE(ID, IndID) AS [Date Last Update], RowModCtr [Rows Modified Since Last Update]

FROM SYS.Tables AS ST INNER JOIN SYS.SYSIndexes AS SI ON SI.[ID] = ST.[object_ID]

INNER JOIN SYS.Schemas AS SS ON ST.[schema_ID] = SS.[Schema_ID]

WHERE STATS_DATE(ID, IndID) <= DATEADD(DAY,-1,GETDATE()) AND RowModCtr > 10

ORDER BY [RowModCtr] DESC

Once you identified the outdated stats, you can use the "Update Statistics" to do the update.

Query to find out parameters for Stored Procedure

Use below query to find out parameters for Stored Procedure

SELECT Schema_Name(Schema_ID) AS Schema_Name,
 O.Type_Desc,
 O.Name AS Object_Name, 
 P.Parameter_ID,
 P.Name AS Parameter_Name,
 Type_Name(P.User_Type_ID) AS Parameter_Type,
 P.MAX_Length,
 P.Precision,
 P.Scale,
 P.IS_Output
FROM SYS.Parameters AS P INNER JOIN SYS.Objects AS O 
ON O.Object_ID = P.Object_ID 
WHERE O.Object_ID = Object_ID('<Stored Procedure>') 
ORDER BY Schema_Name, P.Parameter_ID;

Replace <Stored Procedure> with Original Stored Procedure name.

Selecting a random number for each row

Sometime you may want to return a random number for each row. You can not do this directly selecting a RAND() function in query:

SELECT Rand() AS Random_Number, Name, Code from MyTable.

The above query will return the same random number for each row.

But you want to select different random number for each row. Follow below steps to do so:

create view rand_view
as
select rand( ) as random_value
go

create function New_rand()
returns float
as
begin
declare @f float
set @f = (select random_value from rand_view)
return @f
end

SELECT dbo.New_rand() wrapped_rand, Name, Code FROM MyTable