Database

Enable Database Mail XPs MSSQL Server – Configuration Option

sp_configure ‘show advanced options’, 1;

GO

RECONFIGURE;

GO

sp_configure ‘Database Mail XPs’, 1;

GO

RECONFIGURE

GO

Database

What’s new in SQL Server 2016

Benefits of SQL Server 2016:

Enhanced in-memory performance provides up to 30x faster transactions, more than 100x faster queries than disk-based relational databases and real-time operational analytics.

New Always Encrypted technology helps protect your data at rest and in motion, on-premises and in the cloud, with master keys sitting with the application, without application changes.

Stretch Database technology keeps more of your customer’s historical data at your fingertips by transparently stretching your warm and cold OLTP data to Microsoft Azure in a secure manner without application changes.

Built-in advanced analytics provide the scalability and performance benefits of building and running your advanced analytics algorithms directly in the core SQL Server transactional database.

Business insights through rich visualizations on mobile devices with native apps for Windows, iOS and Android.

Simplify management of relational and non-relational data by querying both with T-SQL using PolyBase.

Faster hybrid backups, high availability and disaster recovery scenarios to back up and restore your on-premises databases to Microsoft Azure and place your SQL Server AlwaysOn secondaries in Azure.

Reference:
https://msdn.microsoft.com/en-us/library/bb500435.aspx

Database

Oracle SQL Developer – Unable to create an instance of the Java Virtual Machine

After extracting sqldeveloper-2.1.0.63.73.zip and executing sqldeveloper.exe, I received following error
—————————
Oracle SQL Developer
—————————
Unable to create an instance of the Java Virtual Machine
Located at path:
<SQLDEVELOPER>\jdk\jre\bin\client\jvm.dll
—————————
OK  
—————————

Follow this workaround to fix this issue…
Open ide.conf file from

<SQLDEVELOPER>/jdev/bin/ide.conf
or
<SQLDEVELOPER>\ide\bin\ide.conf

In this file, change this line :
AddVMOption –Xmx512M to AddVMOption -Xmx256M
Now, you can launch sqldeveloper.exe without error.

Database

How to drop SQL Server Schema

USE MyData;
GO

— Drop failed because schema is not empty
DROP SCHEMA Sch;
GO
Msg 3729, Level 16, State 1, Line 1
Cannot drop schema ‘Sch’ because it is being referenced
   by object ‘DF__sch_test__creat__4316F928’.

— Move one table out
ALTER SCHEMA dbo TRANSFER Sch.test;
GO

— Delete one table
DROP TABLE Sch.test;
GO

— Dropped ok
DROP SCHEMA Sch;
GO

Command(s) completed successfully.

Database

T-SQL, Stored Procedure: Generate Entity Class from Database

if exists (select * from dbo.sysobjects where id = object_id(N’OP_GENERATE_ENTITY_CLASS’) and OBJECTPROPERTY(id, N’IsProcedure’) = 1)

drop procedure OP_GENERATE_ENTITY_CLASS

GO

/*======================================================================

Entity Class Generator: Generate Entity Class from Database (Table or View)

Usage:

USE DatabaseName

EXEC OP_GENERATE_ENTITY_CLASS  ‘TableOrViewName’

======================================================================*/

CREATE PROCEDURE OP_GENERATE_ENTITY_CLASS

@ObjectName varchar(100)

AS

DECLARE @name varchar(20),

@type varchar(20)

DECLARE objCursor CURSOR

FOR

SELECT sc.name, st.name type FROM syscolumns sc

INNER JOIN systypes st

ON st.xusertype = sc.xusertype

WHERE Id=OBJECT_ID(@ObjectName)

DECLARE @declarationCodes varchar(8000),

@ctorCodes varchar(8000),

@propertyCodes varchar(8000)

SET @declarationCodes = ”

SET @ctorCodes = ”

SET @propertyCodes = ”

OPEN objCursor

FETCH NEXT FROM objCursor

INTO @name, @type

DECLARE @cType varchar(20)– C# type

DECLARE @ctorParms varchar(4000)

SET @ctorParms = ”

IF @@FETCH_STATUS <> 0

    BEGIN

    CLOSE objCursor

    DEALLOCATE objCursor

    PRINT ‘Error… Please check passed parameter’

    RETURN

END

WHILE @@FETCH_STATUS = 0

    BEGIN

    SET @cType =

    CASE

    WHEN @type LIKE ‘%char%’ OR @type LIKE ‘%text%’

    THEN ‘string’

    WHEN @type IN (‘decimal’, ‘numeric’)

    THEN ‘double’

    WHEN @type = ‘real’

    THEN ‘float’

    WHEN @type LIKE ‘%money%’

    THEN ‘decimal’

    WHEN @type = ‘bit’

    THEN ‘bool’

    WHEN @type = ‘bigint’

    THEN ‘long’

    WHEN @type LIKE ‘%int%’

    THEN ‘int’

    ELSE

    @type

END

–PRINT CHAR(9) + ‘private ‘ + @ctype + ‘ ‘ + ‘m_’ + @name + ‘;’

SET @declarationCodes = @declarationCodes + CHAR(9) + ‘private ‘ + @ctype + ‘ ‘ + ‘m_’ + @name + ‘;’ + CHAR(13)

SET @ctorCodes = @ctorCodes + CHAR(9) + CHAR(9) + ‘m_’ + @name + ‘ = ‘ + LOWER(LEFT(@name, 1)) + SUBSTRING(@name, 2, LEN(@name)) + ‘;’ + CHAR(13)

SET @ctorParms = @ctorParms + @ctype + ‘ ‘ + LOWER(LEFT(@name, 1)) + SUBSTRING(@name, 2, LEN(@name)) + ‘, ‘

SET @propertyCodes = @propertyCodes + CHAR(9) + ‘public ‘ + @ctype + ‘ ‘ + @name + CHAR(13) +

CHAR(9) + ‘{’ + CHAR(13) +

CHAR(9) + CHAR(9) + ‘get { return m_’ + @name + ‘; }’ + CHAR(13) +

CHAR(9) + CHAR(9) + ‘set { m_’ + @name + ‘ = value; }’ + CHAR(13) +

CHAR(9) + ‘}’ + CHAR(13)

FETCH NEXT FROM objCursor

INTO @name, @type

END

PRINT ‘[Serializable]’

PRINT ‘public class ‘ + @ObjectName + ‘Entity’

PRINT ‘{’

PRINT @declarationCodes

PRINT ”

PRINT CHAR(9) + ‘public ‘ + @ObjectName + ‘Info(‘ + LEFT(@ctorParms, LEN(@ctorParms) – 1) + ‘)’

PRINT CHAR(9) + ‘{’

PRINT @ctorCodes

PRINT CHAR(9) + ‘}’

PRINT ”

PRINT @propertyCodes

PRINT ‘}’

CLOSE objCursor

DEALLOCATE objCursor