Search

Monday, November 24, 2014

compare current row with previous / next row in a table

There must be a numeric identity / Unique column for comparing current with next / previous Rows in a table. 

Lets say you want to compare both Previous and next rows with current row in ItemMaster Table with unique numeric columns ItemID:

Select Cur.ItemName [Curent Value],
IsNull(Pre.ItemName,0) [Previous Value],
IsNull(Nex.ItemName,0) [Next Value]
From ItemMaster AS Cur
LEFT JOIN ItemMaster AS Pre On Pre.ItemID = Cur.ItemID - 1
LEFT JOIN ItemMaster AS Nex On Nex.ItemID = Cur.ItemID + 1

Monday, November 17, 2014

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.

Monday, November 3, 2014

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>