Skip to main content

Varbinary issue

SQL Server Management Studio doesn't handle very long strings and varbinaries very well in the query window. It hangs for several seconds as you try to scroll over that line. And in fact, some versions will even display a warning about long lines when opening a script that has at least one line over a certain length.

In the Thinkwise Platform this is a problem when you want to synchronize your project version to IAM with a script. This script can contain long varbinary values, used for pictures and documents. This has always been a potential issue, but from version 2020.2 of the Thinkwise Platform files like icons and reports are stored in the database. Which makes it a lot more likely that you will encounter this.

In SQL Server there is a way to split up these long varbinary values, by using line-continuation in T-SQL via the backslash character. This could avoid the problem. However, we don't want to go this way because:

1. Splitting up varbinary values is very time consuming
2. It would be a workaround for a weak spot in the Management Studio

Fortunately there are alternatives of which I will discuss three options briefly: 

  1. Azure Data Studio
  2. Visual Studio Code
  3. SQLCMD

These alternatives work fine with large varbinary values.

 

Azure Data Studio

Azure Data Studio is a cross-platform database tool for data professionals using the Microsoft family of on-premises and cloud data platforms on Windows, MacOS, and Linux.

Azure Data Studio look like this:

Azure Data Studio

You can find all information about Azure Data Studio here:

https://docs.microsoft.com/en-us/sql/azure-data-studio/download-azure-data-studio?view=sql-server-ver15

 

Visual Studio Code

Visual Studio Code is a lightweight but powerful source code editor which runs on your desktop and is available for Windows, macOS and Linux. It comes with built-in support for JavaScript, TypeScript and Node.js and has many extensions for other languages (such as C++, C#, Java, Python, PHP, Go) and runtimes (such as .NET and Unity).

Visual Studio Code looks like this:

Visual Studio Code

You can find all information about Visual Studio Code here:

https://code.visualstudio.com/docs

Important in this matter is that there is also an extension for T-SQL. You can read all about that option here:

https://code.visualstudio.com/docs/languages/tsql

If you like to run the Synchronization script in a source code editor, Visual Studio Code is a good alternative.

 

SQLCMD

SQLCMD allows executing queries and SQL Server scripts using the command line. If you don't mind working this way, SQLCMD is a fine alternative to run your synchronization scripts.

A command with SQLCMD looks like this:

sqlcmd -S SERVER_NAME -d IAM_DATABASE -i d:\sync_file.sql

You can read more about SQLCMD here:

https://docs.microsoft.com/en-us/sql/tools/SQLCMD-utility?view=sql-server-ver15

Be the first to reply!

Reply