SQL Server 2019 – String Truncation Error Demystified…


Before SQL Server 2019, String Truncation error is big mystery in SQL Server, especially if you have multiple String fields used in your Stored Procedure, it will hard to find out which field its failing and which value is causing the issue

See, Sample screenshot of String truncation error from SQL Server 2017 or before

image

In SQL Server 2019 CTP, If you enable DBCC TRACE FLAG 460, the String truncation error will now display the column name and truncated value which is causing the error

Sample Code for SQL Server 2019

DBCC TRACEON(460)

CREATE TABLE tmpDemo
 (
     intPK INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
     strSmallField VARCHAR(20)
 )

INSERT INTO [tmpDemo]
 (
     [strSmallField]
 )
 VALUES
 ('small string'),
 ('This is a large string')

DROP TABLE [tmpDemo]

Screenshot of Error message from SQL Server 2019

image

I believe this will be included as part of the main release without having to enable Trace flag 460, This will be significantly helpful for all SQL developers !!!

Advertisement

SQL Server 2019 First Public CTP Released


Microsoft has released SQL Server 2019 First Public CTP in Ignite conference today, There are lots & lots of new features are included as part of this release

For more information, Please visit below links

https://docs.microsoft.com/en-us/sql/sql-server/what-s-new-in-sql-server-ver15?view=sqlallproducts-allversions

https://cloudblogs.microsoft.com/sqlserver/2018/09/24/sql-server-2019-preview-combines-sql-server-and-apache-spark-to-create-a-unified-data-platform/

SQL Server Management Studio v 17.8.1 is released


SQL Server Management Studio v 17.8.1 is released and available for download from below links

New Install – https://go.microsoft.com/fwlink/?linkid=875802

Upgrade from previous v 17.x – https://go.microsoft.com/fwlink/?linkid=875804

There are lot of fixes and changes in this release, for more information, Please visit

https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-2017#new-in-this-release

The main highlight feature of this release is support for AutoGrow All files for Database file groups

Autogrow

Hope you find this information helpful !!!

SQL Server Management Studio v 17.7 is released


SQL Server Management Studio v 17.7 is released and available for download from below links

New Install – https://go.microsoft.com/fwlink/?linkid=870039

Upgrade from previous v 17.x – https://go.microsoft.com/fwlink/?linkid=873128

There are lot of fixes and changes in this release, for more information, Please visit

https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-2017#new-in-this-release

The main highlight feature of this release is support for Scheduling SSIS Packages deployed in SQL Azure and this will give a consistent experience for deploying and scheduling SSIS Package similar to on-premise SQL Server

Hope you find this information helpful !!!

Vulnerability Assessment using SQL Server Management Studio


As part of SQL Server Management Studio v 17.4 release, Microsoft has released the Vulnerability Assessment tool, This is a very easy to use tool and helpful in identify the vulnerabilities at database level and tool also provides scripts and suggestions to re-mediate the vulnerabilities as well

In this article, we will see step by step how to run a vulnerability assessment, review the results and apply the remediation

Step 1: Please download and install SQL Server Management Studio v 17.4 or higher, Please refer to this link for details on download https://sqlxpertise.com/2017/12/11/sql-server-management-studio-v-17-4-is-released/

Step 2: After installing, Please run Management Studio and connect to target SQL Server on which you would like to run the assessment and select the database and right click on database name and click on Tasks and click on Vulnerability Assessment –> Scan for Vulnerabilities…

capture20180409120215159

Step 3: Please select the location where you would like to store the reports and click “Ok” to start the assessment

capture20180409120305266

Step 4: After assessment is completed, the report will be open as a new tab, The report will provide us the following details

a. How many security checks were completed ?

b. Number of Failed Checks and details

c. Number of Passed Checks and details

capture20180409120607579

Step 5: To review details of each failed check, Please select the failed check and see details on the bottom

capture20180409120633813

capture20180409120703593

capture20180409120716207

Depending on the check, the tool provides query to run the Rule and also script to remediate the issues as well

capture20180409120941664

After running the remediation scripts, we can re-run the assessment and verify the results, in below screenshot you can see that the High Risk vulnerability is resolved and also one of the Low risk vulnerability is resolved

capture20180409123120495

Step 6: If some of the checks are not applicable, Please use “Approve as Baseline” option

capture20180409123759719

If you want to revert the change, you can re-run the scan and go to Passed tab and select the check and “Clear Baseline”

Step 7: To review previously run assessment results, Please select the database and right click and click on Tasks –> Vulnerability Assessment –> Open Existing Scan and select the folder and file where the scan was saved

capture20180409120815476

I hope you find this article useful !!!

%d bloggers like this: