.sqlProj not updating with changes

Recently I have experienced issue with .sqlProj file not updating changes with my Visual Studio Version 16.8.3.

To Resolve the issue, Please follow below steps:

Go to Tools –> Options –> General –> AutoRecover
Uncheck “Save Auto Recover” Option.

You can follow the thread here:
https://developercommunity.visualstudio.com/content/problem/1260411/sqlproj-not-updating-with-changes.html?inRegister=true

INSERT (T-SQL)

Insert Statement in SQL SERVER adds one or more rows to a table or a view.

Syntax 1:
INSERT INTO TableName(Column1 , Column2 , Column3….)
VALUES (Column1Value , Column2Value , Column3Value);
Here Column1, Column2, Column3 etc… represents the column name in the table into which we want to insert the data.
Example: Let say I want to add Employee’s Id, Name, Date Of Birth and Salary into table called dbo.Employee.

INSERT dbo.Employee(Id , Name , DateOfBirth , Salary)
VALUES(1 , ‘John Smith’ , ‘1998-12-24’ , 50000);

Syntax 2:
INSERT INTO TableName(Column1 , Column2, Column3….)
SELECT SourceColumn1 , SourceColumn2, SourceColumn3
From SourceTableName
According to syntax 2, we can insert all or certain columns from SourceTableName.

Stored Procedure Silently truncate the Parameter Value – SQL Server.

Practically it doesn’t make sense that SQL Server throws an error if we insert a record with longer value string to the column but If that insert happens with stored procedure than stored procedure will silently truncate the parameter value to the specified length value. For example, if I have defined a parameter @Param Varchar (10) and if I pass my Parameter value as ‘SQL is trimmer’ it will truncate all the characters after the 10 character and stored first 10 character in a database table. Let run this as an example below.

Define the Stored Procedure and Database.

–Create Database
Create Database TSQLTest
GO
—Use TSQLTest
USE TSQLTest;
GO
—Create ParameterCheck
Create Table dbo.ParameterCheck
(
    Id        Int    Identity,
    Col1        VARCHAR (10) ,
    Col2        Varchar (5) ,
    ConstraintPrimary Key (Id)
);
GO
—Create Procedure with the parameter with same length of the table.
Create Procedure usp_ParameterCheck_Insert
    @Param_Col1        Varchar (10),
    @Param_Col2        Varchar (5)
AS
BEGIN
    SET NOCount ON;
    INSERT dbo.ParameterCheck(Col1, Col2)
    SELECT @Param_Col1, @Param_Col2;
    SET NOCOUNT OFF;
END;
GO
Now Let’s call the stored procedure and see the result.
—Stored Procedure call by passing parameter value.
EXEC usp_ParameterCheck_Insert
             @Param_Col1 = ‘SQL Is Trimmer.’
            , @Param_Col2 = ‘Trust Me.’
GO
—Get the Result From the table.
SELECT * From dbo.ParameterCheck

Result:

What are the Alternatives?

Alternative 1: I have read on other forums and people will say allow one character more than the actual length of the column and get exception which return to the application but the error doesn’t say which parameter value or on which column has longer string so this solution isn’t going to work. Please see below screen shot for it.

Alternative 2: The second approach I was trying with Table Valued Parameter as below but it has also the same problem the error doesn’t say which column has longer string as below.

Conclusion:

Max Character length should be at Application Level. It would be idle that client stop typing extract character than allowed length.

Azure SQL Database Transparent Data Encryption

Azure SQL Database transparent data encryption helps protect against the threat of malicious activity by performing real-time encryption and decryption of the database, associated backups, and transaction log files at rest without requiring changes to the application.

TDE encrypts the storage of an entire database by using a symmetric key called Database Encryption Key (DEK). In SQL Database the Database Encryption Key is protected by built-in server certificate. The built-in server certificate is unique for each SQL Database server. If the database is in a GeoDR relationship, it is protected by a different key on each server. If 2 databases are connected on a same server, they share the same built-in certificate. Microsoft automatically rotates these certificates at least every 90 days.

Note: TDE does not provide encryption across communication channels.

When using TDE with SQL Database V12, the server-level certificate stored in the master database is automatically created for you by SQL Database. To move a TDE database on SQL Database you must decrypt the database, move the database, and then re-enable TDE on the Destination Database.

Enable TDE on a Database Using the Portal

Visit the Azure portal at https://portal.azure.com and sign with your Azure Administrator or contributor account.
On the left banner, click to BROWSE, and then click SQL Databases.
With SQL Databases selected in the left pane, click the user database, here in our example I have selected the Sample AdventureWorks2012 Database, as per Image 1.0
Click the Transparent Data Encryption part to open the Transparent Data Encryption blade.
In the Data Encryption blade, move the Data Encryption button to On.
Finally, click Save (at the top of the page) to apply setting. The Encryption status will approximate the progress of the transparent data encryption.
Once the encryption completed, you should be able to see the Encryption Status as Encrypted as per the image below.

Azure SQL Database Encrypting Data in Transit

Azure SQL Database connections are encrypted using TLS/SSL for the Tabular Data Stream (TDS) transfer of data. When connection with Latest version of ADO.Net (4.6), JDBC (4.2) Azure SQL Database V12 now supports the strongest version for the TLS (Transport Layer Security) 1.2 Connections.

Support for ODBC on Linux, PHP and node.js coming soon.

Microsoft provides as a valid certificate for the TLS connection for Azure SQL Database. We can implement increased security and eliminate the possibility of “man-on-the-middle” attacks, by implementing below changes to each the different drivers.

On below connection String Setting Encrypt=True will assure that the client is using a connection that is encrypted. Setting TrustServerCertificate=False ensures that the client will verify the certificate before accepting the connection.

Error: Cannot Connect Azure SQL Database

Error: Can not Connect to *.windows.net.

I was getting below Database connection Error When try to connect via Azure Active Directory Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’. (Microsoft SQL server, Error: 18456).

You must require SQL SERVER Management Studio 2016 to connect to Azure SQL Database via Management Studio using Windows Authentication.

You can download SQL Server Management Studio 2016 from below URL
SQL SERVER Management Studio 2016 Download.

I have set up Database user on the Azure SQL Database, technically I should be able to connect to Azure SQL Database (for example, Adventure Works), but I can’t and gives me below error.

Solution: On the Connect to Server Dialog box in the Connection Properties page put Database name and Click Connect.

Myth: Excel Connection Manager works only in 32-bit mode of SSIS

When we try to use Excel Source/Excel Destination as Data Flow component we have to use Excel Connection Manager. The limitations of using Excel Connection Manager is that we have to run SSIS Solution in 32 bit Runtime otherwise we will get below error.

Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.

The AcquireConnection method call to the connection manager “” failed with error code 0xC0209303. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

Error: The requested OLE DB provider Microsoft.Jet.OLEDB.4.0 is not registered. If the 64-bit driver is not installed, run the package in 32-bit mode. Error code: 0x00000000.

An OLE DB record is available. Source: “Microsoft OLE DB Service Components” Hresult: 0x80040154 Description: “Class not registered”.

Image:

My findings with Excel Connection Managers are as follows.

Excel Connection Manager works in 64 bit (Run64BitRuntime = True) in SQL SERVER 2012 Integration Services (SSIS 2012).

That’s not true. Many of us believe that in SQL Server 2012 Integration Services Microsoft has fixed the issue to run SSIS Solution in 64-bit using Excel Connection Manager. The reason it is not working because Microsoft provides Microsoft Visual Studio or SQL SERVER Data Tools (SSDT) as 32 bit application. Above solution I have created in SSIS 2012 and it proves that Excel Connection Manager fails if I run my solution in in 64 bit (Run64BitRuntime = True).

What is the alternative?
We believe that Office OLE DB Provider are 32 bit only which not the true case. We can download 64 bit version of Microsoft Access Database Engine 2010 Redistributable or Microsoft Access Database Engine 2013 Redistributable. As mentioned earlier Microsoft Visual Studio or SQL SERVER Data Tools are 32 bit applications only so we cannot use 64 bit OLEDB Providers so, In the development Environment we have to use 32 bit provider but we can use 64-bit Provider in Production.

After I installed Microsoft Access Database Engine 2010 Redistributable 32 bit in my development machine (SSIS 2012) and replaced Microsoft.Jet.OLEDB.4.0 to Microsoft.ACE.OLEDB.12.0. Run the solution in 32 bit (Run64BitRuntime = False) it works fine. After I change Run64BitRuntime = True, Build and Deployed it to Production where I have installed 64-Bit Microsoft Access Database Engine 2010 Redistributable it works fine.
1) Change settings in Excel Connection Manager as Microsoft.Jet.OLEDB.4.0 to Microsoft.ACE.OLEDB.12.0

2) Another alternative is we can use OLEDB Source and OLEDB Connection Provider as below.

Change the extended property to Excel 8.0;HDR=YES. It works fine in 32 bit in Development machine and 64 bit in production machine.

If you are connecting to Microsoft Office Excel Data, add the appropriate Extended Properties of the OLEDB Connection String based on the Excel File type:

File Type (Extension)	Extended Properties.
Excel 97-2003 Workbook (.xls)	“Excel 8.0”
Excel 2007-2010 Workbook (.xlsx)	“Excel 12.0 Xml”
Excel 2007-2010 Macro-enabled workbook (.xlsm)	“Excel 12.0 Macro”
Excel 2007-2010 Non-XML binary workbook (.xlsb)	“Excel 12.0”

There is also a myth that we must have installed Microsoft Office on the box or on the machine or server where we running or the developing the SSIS Solution, that’s not the true case.

Conclusion: It is possible that we can load Microsoft Office Excel Files in 64 bit of SSIS.

SQL SERVER Database Samples URLs

You can download SQL SERVER Product Samples through below URL.

You can download Latest SQL SERVER 2005 Samples from below URL.
http://msftdbprodsamples.codeplex.com/releases/view/4004

You can download Latest SQL SERVER 2008 Samples from below URL.
http://sqlserversamples.codeplex.com/releases/view/30803

Adventure Works Databases – 2012, 2008R2 and 2008
http://msftdbprodsamples.codeplex.com/releases/view/93587

Adventure Works 2014 Sample Databases
http://msftdbprodsamples.codeplex.com/releases/view/125550

Adventure Works and Samples for SQL SERVER 2016 CTP
https://www.microsoft.com/en-us/download/details.aspx?id=49502

Microsoft Contoso BI Demo Dataset for Retail Industry
http://www.microsoft.com/en-us/download/details.aspx?id=18279

Temporal Table in SQL SERVER 2016. Removing System Versioning.

Note: This topic applies to Microsoft SQL SERVER 2016.

So far we have covered new feature introduced by Microsoft in SQL SERVER 2016 called Temporal Table, which will be useful for system version of the table. Then I have covered How to implement a temporal table on a new table or creating a new Temporal Table. You can find more information if we have to Migrate existing solution to use system versioning or Temporal Table or System Versioned table.

In this Blog Post, we will be learning on How to remove System Versioning? In our previous example on Changing the schema of a Temporal Table.

The following example shows how to completely remove System Versioning from dbo.SalesPerson Table and drop the dbo.SalesPersonHistory Table. We could also want to drop the PERIOD columns.

You can read the Previous Blog post Changing the schema of a Temporal Table
to continue with this series of the Blog Post or you can start from Temporal Table in SQL SERVER 2016.

Note: we cannot drop either the dbo.SalesPerson or the dbo.SalesPersonHistory tables while system versioning is enabled.

Step1: Below code Remove System Versioning from dbo.SalesPerson table, Drop the Period Column From dbo.SalesPerson table and Drop the dbo.SalesPersonHistory table.

USE SQL2016Demo;
GO
ALTER TABLE dbo.SalesPerson
SET (SYSTEM_VERSIONING = OFF);
ALTER TABLE dbo.SalesPerson
DROP PERIOD FOR SYSTEM_TIME;
DROP TABLE dbo.SalesPersonHistory;

Temporal Table in SQL SERVER 2016. Changing the schema of a Temporal Table.

Note: This topic applies to Microsoft SQL SERVER 2016.

So far we have covered new feature introduced by Microsoft in SQL SERVER 2016 called Temporal Table, which will be useful for system version of the table. Then I have covered How to implement temporal table on a new table or Creating a new Temporal Table. You can find more information if we have to Migrate existing solution to use system versioning or Temporal Table or System Versioned table.

In this Blog Post, we will be learning on How to change the schema of a Temporal Table in the scenario where we need to change the schema of Temporal Table? In our previous example on Migrate existing solution to use system versioning or Temporal Table or System Versioned table, we were maintaining Sales Person’s Region in dbo.SalesPerson and dbo.SalesPersonHistory table. Now business want to track each Sales Person’s Area Sales Manager, which requires a schema changes on a Temporal Table to add a new column called AreaSalesManager, so let’s learn how we can alter the structure of a Temporal table.

Schema changes are blocked while SYSTEM_VERSIONING is ON to prevent the two table becoming out of sync, so in order to change the schema of a temporal table we must first temporarily disable the SYSTEM_VERSIONING by setting it as SYSTEM_VERSIONING = OFF. While SYSTEM_VERSIONING is OFF we can make changes to the both tables independently and then re-enable the SYSTEM_VERSIONING. Perform the above steps within a transaction to maintain data consistency.

The following example demonstrated this technique. You can read the Previous Blog post Migrate existing solution to use system versioning or Temporal Table or System Versioned table
to continue with this series of the Blog Post.

Step1: Below code implements schema changes in Transaction.

BEGIN TRAN
/* Takes schema lock on both tables */
ALTER TABLE dbo.SalesPerson SET (SYSTEM_VERSIONING = OFF)
/* add column to current table */
ALTER TABLE dbo.SalesPerson
ADD AreaSalesManager Varchar (50) NULL;
/* add column to history table */

ALTER TABLE dbo.SalesPersonHistory
ADD AreaSalesManager Varchar (50) NULL;
/* Re-establish versioning again given that this operation is under strict control (other transactions are blocked), DBA chooses to ignore data consistency check in order to make it fast*/

ALTER TABLE dbo.SalesPerson SET (SYSTEM_VERSIONING =ON (HISTORY_TABLE=dbo.SalesPersonHistory, DATA_CONSISTENCY_CHECK = OFF));
COMMIT;
GO

Output: Below Image shows that we have successfully added new column to the dbo.SalesPerson and dbo.SalesPersonHistory tables.