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:
- Azure Data Studio
- Visual Studio Code
- 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:
You can find all information about Azure Data Studio here:
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:
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.sqlYou can read more about SQLCMD here:
https://docs.microsoft.com/en-us/sql/tools/SQLCMD-utility?view=sql-server-ver15