Alter Table UI Azure Sql Throwing Collation error

Toad for SQL Server
1

Any azure sql table altered from Gui, if table has constraints or FKs it throws a cannot covert collation error from KW to sql Latin general 1 (American default install). Upon error ing it leaves the tmp.* table it creates as it cannot finish.

On latest toad version.

2

Hi Logan,
has this worked fine before? Which is your "latest" version, just to make sure:-)
Thanks

3

I haven't tried to use Toad SQL Server with Azure SQL Before this, on prem sql 2019 and lower work amazing with Toad for sql server.

current version is Toad for SQL Server 7.1.2.166 (64 bit).

4

Here's the Reproduce steps,

Alter Table
Change DataType of Column from varchar(xx) to nvarchar(xx)
click ok or goto script tab
error happens

50% of the time clicking okay will work
50% of the time, it mentions, it cannot delete tmp.* table due to FKs (I assume toad rebuilds the table as a tmp table then drops the old and renames the tmp.* table or something similar). If this one happens the tmp.* will remain and be visible. at this point manual clean up of this table is required.

See 2 screen shots where 1 shows the column dataType change.
and 2nd shows the error message. Looks like a weird Korean collation? not sure I'd have to look that one up :smiley:

SS1
SS11205×1027 152 KB

5

SS2
SS21220×1038 106 KB

6

Thanks Logan. Just to check again - did you mean SQL Server in Azure or SQL Azure Database?
I'm playing around with some settings in SQL Server 2019 in Azure and we'll be discussing it with developers. Right now we've been implementing SQL Server 2019 support so please be patient.

7

I meant SQL 2019 on prem is working amazing with TFSS
The above error/screen shot is with Azure SQL.

Does that make sense?

8

Yes, it does. Please hold on while we test it.

9

Logan, I can't reproduce it. I had a table with an FK, several columns and I tried it back and forth - convert NVARCHAR to VARCHAR with no errors.
Maybe you could send a DDL of your table? Is it only one specific table or any?

10

Any table

Is there a way I can pm you the ddl?

11

Either open up a support request with Quest or PM me here.

12

CREATE TABLE [dbo].[MyTable] (
[TableID] bigint IDENTITY(1, 1),
[TableName] nvarchar(50) NOT NULL,
[Created] datetime2(7) NOT NULL DEFAULT (getutcdate()),
[CreatedBy] uniqueidentifier NOT NULL,
[Modified] datetime2(7) NULL,
[ModifiedBy] uniqueidentifier NULL,
[Deleted] datetime2(7) NULL,
[DeletedBy] uniqueidentifier NULL,
[DeletedReason] nvarchar(255) NULL
)

13

Hi, I created a table with your DDL and tried it back and forth again and still wasn't able to reproduce it.