quinta-feira, 17 de maio de 2018

Converting a ASC character column to UTF-8

On a recent task, I had to import a simple text file for data enrichment purposes, and the "name" column when imported to SQL SERVER, got messed up, event after I changed the file encoding to UTF-8.

So basically, the data was like this:

"Jão Pedro Valênciano"

When it should be like this:

"João Pedro Valenciano"

After some googling, I found some dead end alleys and a good answer. On the SQL TEAM forum there is thread on the subject, proposing a function that solves the issue.

Bellow is the function and the link:

CREATE FUNCTION dbo.Utf8_to_utf16 (@s VARCHAR(8000)) 
returns NVARCHAR(4000) 
  BEGIN 
      IF @s IS NULL 
        RETURN NULL 

      DECLARE @n      INT, 
              @r      NVARCHAR(4000), 
              @cn     INT, 
              @octets INT, 
              @ch     NVARCHAR(2) 

      SET @r = N'' 

      WHILE 1 = 1 
        BEGIN 
            -- dubious: unexpected octets (0x80-0xBF, 0xF8-0xFF) are treated like 0x00-0x7F 
            SET @n = Patindex('%[ร€-รท]%', @s COLLATE latin1_general_bin 
                     ) 

            IF @n = 0 
              BEGIN 
                  SET @r = @r + @s 

                  BREAK 
              END 
            ELSE 
              BEGIN 
                  SET @r = @r + Substring(@s, 1, @n-1) 
                  SET @cn = Ascii(Substring(@s, @n, 1)) 

                  IF @cn <= 0xDF 
                    BEGIN 
                        SET @octets = 2 
                        SET @ch = Nchar(( @cn & 0x1F ) * 0x40 + ( Ascii( 
                                        Substring(@s, @n + 1, 1)) 
                                                                  & 
                                                                  0x3F )) 
                    END 
                  ELSE IF @cn <= 0xEF 
                    BEGIN 
                        SET @octets = 3 
                        SET @ch = Nchar(( @cn & 0x0F ) * 0x1000 + ( Ascii( 
                                        Substring(@s, @n + 1, 1) 
                                                                    ) 
                                                                    & 0x3F ) * 
                                                                            0x40 
                                        + 
                                        ( 
                                                  Ascii(Substring(@s, @n + 2, 1) 
                                                  ) 
                                                  & 
                                                  0x3F 
                                        )) 
                    END 
                  ELSE 
                    BEGIN 
                        -- code point in a supplementary plane: output UTF-16 surrogate pair 
                        SET @octets = 4 
                        SET @ch = Nchar((@cn & 0x07) * 0x100 + (Ascii(Substring( 
                                  @s 
                                  , @n 
                                  +1 
                                  , 1) 
                                  ) 
                                  & 
                                  0x3F 
                                  ) * 0x04 + ( 
                                  Ascii(Substring(@s, @n+2, 1)) & 0x30) / 0x10 + 
                                  0xD7C0) 
                                  + Nchar((Ascii(Substring(@s, @n+2, 1)) & 0x0F) 
                                  * 
                                  0x40 
                                  + ( 
                                  Ascii( 
                                  Substring(@s, @n+3, 1)) & 0x3F) + 0xDC00) 
                    END 

                  SET @r = @r + @ch 
                  SET @s = Substring(@s, @n + @octets, 8000) 
              END 
        END 

      RETURN @r 
  END 

go 


Link to it: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=62406

Use it like this:
SELECT dbo.utf8_to_utf16 ('João Pedro Valênciano')

The result is:
João Pedro Valênciano


Check out my new book about R Language http://www.amazon.com/dp/B00SX6WA06

segunda-feira, 14 de maio de 2018

[From Technet] A new update for mssql-cli, an interactive CLI, is now available

We have released our second major update for mssql-cli since our public preview announcement in December. You can view the public preview announcement here.

mssql-cli is a new and interactive command line query tool for SQL Server. This open source tool works cross-platform and is part of the dbcli community.

See the install guide to download mssql-cli and get started.

mssql-cli auto-completion in action

In this release (v0.13.0), highlights include:

  • Apt-get and Linux packaging support
  • GDPR Compliance
  • New demo video

Apt-get and Linux packaging support

One of the key issues Linux users run into when setting up mssql-cli for the first time is not having the right version of Python or having to install Python for the first time. We want to make the first experience with mssql-cli painless, thus we added apt-get support in order to package Python with your installation to help improve the acquisition experience.

For full instructions to acquire mssql-cli for each Linux distribution, please check out the Linux installation guide.

Note: For those who already installed mssql-cli via pip install, please run

sudo pip uninstall mssql-cli

Then, follow the installation instructions.

GDPR compliance

As many of us are familiar with, GDPR is approaching and we made some updates. In the past, file history stored entire T-SQL queries. However, if the query contained any secrets or passwords, it wasnt smart enough to scrub those out. This is no longer the case, and now file history has been updated to no longer store secrets or passwords.

In addition, we have added 24-hour rotation of UserID when we collect telemetry.

New Channel9 video

One of our engineers, Abhi Abhishek, presented a demo of mssql-cli for Channel9, the Microsoft Developer studio. If you are new to mssql-cli, please check out this video and please share with those you feel would benefit from using this tool.

Contact us

We are open to any questions, feedback, or any feature suggestions for future releases, which can be submitted on our GitHub Issues. You can also Tweet at us @sqldatatools.



from SQL Server Blog https://ift.tt/2GgxwuW

[From Technet] Visualize geographic spatial data from SQL Server directly in Tableau 2018.1

This is a partner post authored with the Tableau team

As part of Microsofts open and flexible platform for data and analytics, were always excited when partners add features that will expand customer options and extend business functionality. Business intelligence partner Tableau has recently introduced enhancements that will make it easier for their users to process spatial data stored in SQL Server.

The SQL Server database engine has two spatial types geometry and geography. The geography spatial type helps organize geospatial mapping data into SQL Server tables and works with several SQL-native graphing functions to answer questions like how far apart two geographic locations are, or what locations fall within a certain radius.

Before, customers had to process geography spatial data stored in SQL Server into Shapefiles before they could access it from Tableau. Now with Tableau 2018.1, customers can connect to and visualize data stored in SQL Server directly. This means Tableau will recognize spatial data in your SQL Server tables without any intermediate steps, and customers can leverage spatial operations for geography supported by SQL Server or custom queries to work with geographic data stored in SQL Server. Alongside support for native spatial data, Tableau 2018.1 also includes new RAWSQL functions that can leverage the spatial operations from the database and allow you to create calculations that return generated spatial objects.

Here are some steps to try it out:

Get started with spatial data

Tableau will recognize if your table has a spatial field and allow you to utilize it, with no intermediary steps. Just drag and drop the geographic field to get started.

Using spatial operations

Connections to SQL Server support Custom SQL queries. You can use a Custom SQL query to leverage operations supported by the database. Lets say you wanted to understand how many parks are located near schools. You could perform a proximity analysis using the query below to produce the following visualization. This query uses a spatial join based on a buffer of the schools point geometry.

SELECT
S.[common nam] as SchoolName,
P.[common nam] as ParkName,
S.geom.STBuffer(750) as circle_geom,
P.[Geom] as park_geom

FROM
TestSpatial.dbo.seattleelementaryschools S

LEFT JOIN
TestSpatial.dbo.allseattleparks P

ON
S.geom.STBuffer(750).STIntersects(P.geom) = 1

Make the data dynamic by adding parameters to the query. You can also apply a buffer to the park geometries to make them more prominent and to create a polygon geometry that can be used in further analysis.

SELECT
S.[common nam] as SchoolName,
P.[common nam] as ParkName,
S.geom.STBuffer(<Parameters.Radius>) as circle_geom,
P.[Geom] as park_geom

FROM
TestSpatial.dbo.seattleelementaryschools S

LEFT JOIN
TestSpatial.dbo.allseattleparks P

ON
S.geom.STBuffer(<Parameters.Radius>).STIntersects(P.geom.STBuffer(200)) = 1

Expand your analysis with RAWSQL

Support for SQL Server Spatial includes a new set of RAWSQL functions that are usable in calculations. RAWSQL functions allow you to pass values into a SQL statement where the query is executed when the calculated field is utilized within the worksheet. This means you can leverage the spatial operations supported by the database to generate a new spatial object. The calculation below returns the INTERSECTION of two spatial objectsin this case, the school and park buffers. STIntersection is like a cookie-cutter operation. The resulting geometry will represent only the area that was shared by the two inputs.

RAWSQL_SPATIAL("Select %1.STIntersection(%2.STBuffer(200))",[circle_geom], [park_geom])

You canput buffered parks and the intersection results on a dual axis to produce a visualization that only shows the areas that overlap between the two fields.

Join the pre-release community to test new geospatial features as theyre released in beta. Were excited to see how you leverage this feature to create even more beautiful (and useful) maps!

For more information on this feature, read up on SQL Server documentation for geography types or visit the Tableau Help page. Learn about other features in the 2018.1 release on our feature highlights page.



from SQL Server Blog https://ift.tt/2jXdlcY

quarta-feira, 9 de maio de 2018

[From Technet] SSMS 17.7 is now available

This post is co-authored bySandy Winarko, Principal PM, SQL Server.

We are excited to announce the release of SQL Server Management Studio (SSMS) 17.7!

Download SSMS 17.7 and review the Release Notes to get started.

SSMS 17.7 provides support for almost all feature areas on SQL Server 2008 through the latest SQL Server 2017, which is now generally available.

In addition to enhancements and bug fixes, SSMS 17.7 comes with several exciting new features:

  • Support package scheduling in Azure-SSIS integration runtime.
  • Support for SSIS package scheduling in SQL Agent on SQL Managed instance. It is now possible to create SQL Agent jobs to execute SSIS packages on the managed instance.
  • Replication monitor now supports registering a listener for scenarios where publisher database and/or distributor database is part of Availability Group. So with this release of SSMS, you can monitor replication environments where publisher database and/or distribution database is part of Always On.
  • Fixed bug fixes

Scheduling SSIS Activities in ADF Pipelines via SSMS

Unlike on-premises SQL Server/Azure SQL Managed Instance (MI) that have a native/first-class SSIS job scheduler as part of SQL Server/MI Agent, Azure SQL Database does not have any. Consequently, if you use Azure SQL Database to host SSISDB, you can automatically generate Azure Data Factory (ADF) pipelines/activities/triggers to schedule SSIS package executions via SSMS with UI that is similar to using on-premises SQL Server Agent. This SSMS scheduling feature offers rapid authoring of ADF pipelines/activities/triggers that can be edited/extended on ADF app.

For more info on ADF pipelines/activities/triggers, see:

Bug fixes

In SSMS 17.7, there were many bug fixes.

To highlight a few of these:

Intellisense:

  • Perf improvement: reduced volume of intellisense queries for column data. This is especially beneficial when working on tables with a huge number of columns.

SMO:

  • Exposed Error Log Size setting in SMO. See more details here.
  • Fix linefeed scripting in SMO on Linux
  • Miscellaneous perf improvement when retrieving rarely used properties

Maintenance Plan:

  • Fixed an issue where trying to change the schedule of an existing Maintenance Plan was throwing an exception. See more details here.

Scripting:

  • Fixed an issue where scripting stored procedure against Azure SQLDW was not working for non-admin user
  • Fixed an issue where scripting a database against Azure SQLDB was not scripting the “SCOPED CONFIGURATION” properties

Telemetry:

  • Fixed issue where SSMS crashes then trying to connect to a server, after opting out of sending telemetry.

To learn more about other bug fixes covered in this release, check the Release Notes.

Call to Action

Try it out and let us know what you think! You can message us on our twitter @sqldatatools or reach out to Ken Van Hynings twitter @sqltoolsguy.



from SQL Server Blog https://ift.tt/2KO3u5t

terça-feira, 8 de maio de 2018

[From Technet] The May release of SQL Operations Studio is now available

This post is co-authored by Karl Burtram, Senior Software Engineer, SQL Server.

We are excited to announce the May release of SQL Operations Studio is now available.

Download SQL Operations Studio and review the Release Notes to get started.

SQL Operations Studio is a data management tool that enables you to work with SQL Server, Azure SQL DB and SQL DW from Windows, macOS and Linux. To learn more, visit our Github.

SQL Operations Studio was announced for Public Preview on November 15th at Connect(), and this May release is the sixth major update since the announcement. If you missed it, you can view the April release announcement.

The May Public Preview release is focused on improving our Extensibility experience with the release of new extensions as well as addressing top Github issues.

Highlights for this build include the following.

  • Announcing Redgate SQL Search extension available in Extension Manager
  • Community Localization available for 10 languages: German, Spanish, French, Italian, Japanese, Korean, Portuguese, Russian, Simplified Chinese and Traditional Chinese!
  • GDPR-compliant build has reduced telemetry collection, improved opt-out experience and in-product links to Privacy Statement
  • Extension Manager has improved Marketplace experience to easily discover community extensions
  • SQL Agent extension Jobs and Job History view improvement
  • Updates for whoisactive and Server Reports extensions
  • Fix GitHub Issues

For complete updates, refer to the Release Notes.

Announcing Redgate SQL Search extension

Redgate SQL Search in SQL Operations Studio extension is now available for installation through the Extension Manager Marketplace!The SQL Search extension lets you find database objects across an active connection. The search results makes it simple to jump to the object in the Object Explorer or to view the object’s definition.

We definitely recommend installing this extension. As SQL Ops Studio evolves, we will rely heavily on extensions to provide core functionality, so we expect that most users will want to have several extensions installed for the best experience.

Startup Performance improvements

We want SQL Operations Studio to be fast and efficient to maximize user productivity. Reducing the time it takes to launch SQL Ops Studio is one of the key scenarios where we’ve been making continual investments. Fortunately, our upstream platform, Visual Studio Code, is also optimizing this same scenario. One key improvement we merged from VS Code’s February release was ASAR Node module bundling.

We use product telemetry from the Startup scenario to understand how long users are waiting for SQL Ops Studio to launch. This telemetry allowed us to confirm that the April Public Preview delivered approximately 30-40 percent startup time improvements for most users. Particularly users experiencing the slowest startups have seen the largest improvements.

The below chart shows startup times bucketed into key percentiles: 25, 50, 75, 90, 95, 98, 99 percent. At the risk of being pedantic, this would mean, for example, at the 50 percent bucket half the startups were faster than that time and half the startups were slower than that time.

Community Localization available

Community Localization resources for German, Spanish, French, Italian, Japanese, Korean, Portuguese, Russian, Simplified Chinese and Traditional Chinese are available for the May Public Preview. We’ll continue working over the next couple months to get the product 100 percent localized.

We’d like to thank the translation community for helping to make SQL Operations Studio available to users in their preferred language!

The below screenshot shows an example using the Italian locale.

GDPR compliance

There are several updates in May Public Preview that were needed for GDPR compliance. Specifically, we reduced the telemetry being collected, improved the opt-out experience by adding a notification on first launch, and added a Privacy Statement item to the Help menu.

Extension Manager enhancements

The Extension Manager Recommended section has been replaced with a Marketplace section. The Marketplace will display all available extensions. Recommended extensions are sorted to the top.

Check out some of our 1st-party and community contributed extensions. And take a look at our Extensibility Getting Started page to see how easy it is to add your own extension to the marketplace!

Fix GitHub issues

Here is a summary of issues addressed:

  • #703 Entering HTML-like text in edit data causes value to display incorrectly until refresh
  • #821 sqlopsstudio.deb package dependency
  • #1260 keyword ‘distinct’ not highlighted
  • #1332 Edit data revert row doesn’t work
  • #1215 SQL Agent extension and the status bar
  • #1316 SQL Agent Dont resize after change windows size
  • Improve Manage Dashboard Properties scrolling

Contact us

If you have any feature requests or issues, please submit to our Github issues page. For any questions, feel free to comment below, message us on Gitter, or tweet us @SQLOpsStudio.



from SQL Server Blog https://ift.tt/2roNw9N

[From Technet] Database scoped default online options is in preview for Azure SQL DB

This post is authored byJosh Gnanayutham, Program Manager, Azure SQL Database.

We are happy to announce that database scoped default options for online and resumable are now available for public preview. With this feature, you can set a default behavior for online and resumable rather than defining the option for each individual statement.

You can also avoid accidentally performing an operation that takes your database offline. By setting online as the default for your database, you can ensure that every supported operation will be elevated to online. You can override these set defaults by defining an option for online or resumable in the statement.

Users can set these defaults using the ELEVATE_ONLINE and ELEVATE_RESUMABLE database scoped configuration options. Both options will cause the engine to automatically elevate supported operations to online or resumable execution. You can enable the following behaviors using these options:

  • You can set either option to FAIL_UNSUPPORTED if you would like to execute all operations online or resumable and fail operations that are not supported for online or resumable.
  • You can set either option to WHEN_SUPPPORTED if you would like to execute supported operations online or resumable and run unsupported operations offline or nonresumable. You can set either option to OFF to maintain the current behavior of executing all operations offline and nonresumable unless explicitly specified in the statement.

Behavior of options:

Supported Operation Unsupported Operation
FAIL_UNSUPPORTED Execute Online/Resumable Operation will fail
WHEN_SUPPPORTED Execute Online/Resumable Execute Offline/Nonresumable
OFF Execute Offline/Nonresumable Execute Offline/Nonresumable

 

For more information about Online operations please review the following documents:



from SQL Server Blog https://ift.tt/2rqSYsQ

quarta-feira, 25 de abril de 2018

[From Technet] The April release of SQL Operations Studio is now available

This post is authored by Alan Yu, Program Manager, SQL Server.

We are excited to announce the April release of SQL Operations Studio is now available.

Download SQL Operations Studio and review the Release Notes to get started.

SQL Operations Studio is a data management tool that enables you to work with SQL Server, Azure SQL DB and SQL DW from Windows, macOS and Linux. To learn more, visit our GitHub.

SQL Operations Studio was announced for Public Preview on November 15th at Connect(), and this April release is the fifth major update since the announcement. If you missed the March release announcement can be viewed here.

The April Public Preview release is focused on improving our Extensibility experience with the release of new extensions as well as addressing top Github issues.

Highlights for this build include the following.

  • Public preview release of SQL Agent extension
  • Added new extensions and improved existing extensions
    • Improvements to Server Reports Extension
    • Release of SSMS Keymap extension
    • Release of AlwaysOn Insights extension
    • Release of MSSQL Instance Insights
    • Release of MSSQL Db Insights
  • Added Visual Studio Code 1.21 platform source code refresh
    • Improved large and protected file support for saving Admin protected and >256M files within SQL Ops Studio
    • Integrated Terminal splitting to work with multiple open terminals at once
    • Reduced installation on-disk file count footprint for faster installs and startup times
  • Continue to fix GitHub issues

For complete updates, refer to the Release Notes.

Preview release of SQL Agent extension

Since SQL Operations Studio was released for public preview, one of the most requested features was providing SQL Agent support. Bringing over the most popular SSMS features has always been on our roadmap, but we wanted to make sure we did this the right way. For years, customers have submitted issues that were difficult to change due to being built on old dialog and wizard frameworks. With SQL Operations Studio, we had an opportunity to bring a modern user experience to our features while maintaining the same functionality that our users are experts with.

To make this possible, the engineering team reached out to the SQL Server community to learn more about your top scenarios and get direct feedback about our initial mock-ups. This involved creating surveys, scheduling user interviews, and promoting community discussion through a demo on Youtube showing our initial prototype. With your help and the release of Extensions Manager in the March release, we have provided you an initial preview release of SQL Agent.

When you install SQL Agent from the Extension Manager, you can view the SQL Agents extension as a tab on your server dashboard. To learn how to install an extension, please view this how-to guide.

This initial release focuses on providing a great View Jobs and Job History experience. You can see a list of all jobs including color-coded successful and filed jobs, names of jobs, and error messages. To see the job history of a specific job, you simply click on that job. This view lets you see a history of past runs, and also provides the ability run or stop the job.

The next step will be to add Job Configuration functionalities, including providing support for creating a job, setting alerts, and scheduling jobs. We would love to hear your feedback about this initial release through our GitHub Issues page and also any suggestions you may have as we build out Job Configuration.

This is the first step as we continue to bring over popular features to SQL Operations Studio from SSMS. Please continue to let us know what are your must-have features and feel free to join the discussion.

Adding and improving extensions

In the March Public Preview release, we first introduced the Extension Manager to SQL Operations Studio. With this release, we introduce 5 new extensions that you can now try out in the Extensions Manager (to get started, read the how-to install extension guide).

  • SQL Agent is the extension to view and run SQL Agent jobs as described earlier.
  • SSMS Keymap ports the most popular SSMS keyboard shortcuts to SQL Operations Studio. Created by Kevin Cunnane.
  • AlwaysOn Insights is a collection of widgets designed to provide insights into AlwaysOn Availability Group components to assist DBA’s or similar in managing their environment. Created by Matt Lavery.
  • MSSQL Instance Insights is a collection of widgets designed to provide insights into MSSQL Instance to further extend the built-in default widgets. Created by Matt Lavery.
  • MSSQL Db Insights is a collection of widgets that are designed to provide insights into MSSQL Database to further extend the built-in default widgets. Created by Matt Lavery.

Over the past month, we have received a lot of emails and tweets from the community who were interested in creating their own extension. One of our engineers, Kevin Cunnane, wrote awesome blog posts that includes his process to create and publish the SSMS Keymap extension:

For additional resources to get started writing an extension, please refer to our GitHub Extensibility Wiki Guide. Feel free to also reach out to @sqlopsstudio on Twitter if you need help getting started after checking out these resources.

In addition to adding new extensions, we also brought updates to existing extensions, especially Server Reports. These changes include:

  • Fixed DB Space Usage where it threw an error when database names contain special characters
  • Changed DB Space Usage and DB Buffer Usage to show only top 10 data
  • Updated ReadMe to reference Paul Randal’s wait types library for more info about the Wait Counts widget

As we build out our extensibility story, we will continue to collaborate with the community to learn and improve the way we build and publish extensions. This is the way we envision bringing over SSMS features while also empowering the community to contribute and build their own extensions to share with the community.

Visual Studio Code Refresh

One of the most significant highlights of this release is the Visual Studio Code 1.21 platform source code refresh. Since we fork from VS Code, we do these periodic updates and also get feature improvements. The key highlights with this refresh specifically for SQL Operations Studio are:

  • Improved large and protected file support for saving Admin protected and >256M files within SQL Ops Studio
  • Integrated Terminal splitting to work with multiple open terminals at once
  • Reduced installation on-disk file count footprint for faster installs and startup times

For additional details, checkout the Visual Studio Code February Release Notes, and the Visual Studio Code January Release Notes.

Fix GitHub issues

Fixing user-reported issues may not always get as much recognition as new feature releases, but it is definitely worth calling out. If we truly want to be a community driven tool, we will continue to work on addressing your submitted issues. Here is a summary of issues addressed:

  • #37 When the chart viewer throws an error, unexpected behavior occurs.
  • #462 Feature Request: Option for Server Groups to be expanded by default
  • #1023 Add square brackets for ms_foreachdb call from flyfishingdba
  • #1050 Clear insights view before showing error
  • #1057 Restore and new query actions in explorer-widget are broken
  • #1068 Dashboard Output windows pops-up with error message for Azure DB
  • #1069 Connection Dialog shows Server Required error when initially displayed
  • #1070 Server Groups now require a double-click to expand
  • #1072 Select control background is semi-transparent
  • #1115 Fix all high contrast accessibility issues in sqlops
  • #1101 Extension fails to upgrade “Download Manually” link goes to wrong location
  • #1103 V Scroll not working on Home Tab
  • #1104 SQL extension tabs stopped working
  • #967 Expect query plan when select XML showplan in the result grid
  • #606 intellisense – Bad suggestion for ‘update’ command
  • #1048 Pre-login SSL/TLS handshake error
  • #1150 Various types of query charts throw exceptions and don’t render

Contributions and thank you

We would like to thank all our users who raised issues, and in particular the following users who helped contribute fixes:

  • flyfishingdba for Add square brackets for ms_foreachdb call (#1023)

Contact us

If you have any feature requests or issues, please submit to our GitHub issues page. For any questions, feel free to comment below, message us on Gitter, or tweet us @SQLOpsStudio.



from SQL Server Blog https://ift.tt/2KfcOib