Open DBDiff: open source database comparison tool

Open DBDiff is an open source database schema comparison tool for MS SQL Server 2005/2008.
It reports differences between two database schemas and provides a synchronization script to upgrade a database from one to the other.

Open DBDiff can synchronize:

• Tables (including Table Options like vardecimal, text in row, etc.)
• Columns (including Computed Columns, XML options, Identities, etc.)
• Constraints
• Indexes (and XML Indexes)
• XML Schemas
• Table Types
• User Data Types (UDT)
• CLR Objects (Assemblies, CLR-UDT, CLR-Store Procedure, CLR-Triggers)
• Triggers (including DDL Triggers)
• Synonyms
• Schemas
• File groups
• Views
• Functions
• Store Procedures
• Partition Functions/Schemes
• Users
• Roles

References:

WebSite: http://opendbiff.codeplex.com
Download: http://opendbiff.codeplex.com/Release/ProjectReleases.aspx?ReleaseId=31876

SCOPE_IDENTITY and IDENT_CURRENT

IDENT_CURRENT is similar to the SQL Server 2000 identity functions SCOPE_IDENTITY and @@IDENTITY. All three functions return last-generated identity values. However, the scope and session on which last is defined in each of these functions differ:

· IDENT_CURRENT returns the last identity value generated for a specific table in any session and any scope.

· @@IDENTITY returns the last identity value generated for any table in the current session, across all scopes.

· SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope.

When the IDENT_CURRENT value is NULL (because the table has never contained rows or has been truncated), the IDENT_CURRENT function returns the seed value.

Failed statements and transactions can change the current identity for a table and create gaps in the identity column values. The identity value is never rolled back even though the transaction that tried to insert the value into the table is not committed. For example, if an INSERT statement fails because of an IGNORE_DUP_KEY violation, the current identity value for the table is still incremented.

Be cautious about using IDENT_CURRENT to predict the next generated identity value. The actual generated value may be different from IDENT_CURRENT plus IDENTITY_SEED because of insertions performed by other sessions.

For details visit http://msdn.microsoft.com/hi-in/library/ms175098(en-us).aspx

MS SQL Linked server

What is a Linked Server?

Linked Server is an alias on your local SQL server that points to an external data source. This external data source can be Access, Oracle, Excel or almost any other data system that can be accessed by OLE or ODBC–including other MS SQL servers. An MS SQL linked server is similar to the MS Access feature of creating a “Link Table.”

A linked server configuration allows Microsoft® SQL Server™ to execute commands against OLE DB data
sources on different servers. Linked servers offer these advantages:

• Remote server access.
• The ability to issue distributed queries, updates, commands, and transactions on heterogeneous data sources across the enterprise.
• The ability to address diverse data sources similarly.

In below e.g. i have added mssql server as linked server.

EXEC sp_addlinkedserver
@server='MyServer',  --name
@srvproduct='',
@provider='SQLNCLI',
@datasrc='localhostsqlexpress'  -- server name
EXEC sp_addlinkedsrvlogin 'MyServer', false, null, sa, sa  -- last two parameters are the user name and password

for more info visit: http://msdn.microsoft.com/en-us/library/aa213778(SQL.80).aspx

MSSQL 2005 transaction syntax

Simple stored procedure syntax using try catch block and transaction.

create Procedure sp_name
As
  Begin
    BEGIN TRY
      BEGIN TRAN
          --sql query here
        Commit
    END TRY
    BEGIN CATCH
     ROLLBACK TRANSACTION;
    END CATCH
End