Incorrect syntax near 'nvarchar' when using column type Sql

[steveiwonder]

Incorrect syntax near 'nvarchar' when using column type Sql

[louagej]

👍
🤘

[Steveiwonder]

This seems odd. Are you able to share a small sample database with the problem?

[louagej]

@Steveiwonder ,

I can send a wetransfer link with the demo database I'm using WeTransfer Demo database

Maybe this can already help:
When I try to debug the source code, the DataMasker.SqlDataProvider.GetValue performs a function DynamicParameters param = new DynamicParameters(obj);
the object param seems to null:

[Steveiwonder]

It looks like an issue with the SQL querying being generated, due to the spaces in column name. This is what is produced by dapper (even on the latest version)

exec sp_executesql N'SELECT UPPER(Name) FROM [CRONUS International Ltd_$Contact$437dbf0e-84ff-417a-965d-ed2bb9650972] WHERE [No_] = @No_',N'
@No_ nvarchar(4000),@Search Name nvarchar(4000)',
@No_=N'CT100006',
@Search Name=N'A. GIBSON"S LAW FIRM'

However it should be

exec sp_executesql N'SELECT UPPER(Name) FROM [CRONUS International Ltd_$Contact$437dbf0e-84ff-417a-965d-ed2bb9650972] WHERE [No_] = @No_',N'
@No_ nvarchar(4000),@SearchName nvarchar(4000)',
@No_=N'CT100006',
@SearchName=N'A. GIBSON"S LAW FIRM'

Changing "Search Name" to be "SearchName".

I will need to write some integration with dapper to strip spaces and replace them with something that won't clash with other column names.

[Steveiwonder]

@louagej If you have time, then feel free to write a work around, I can accept a PR. If not it may have to wait a until I have time to figure out a good solution

[louagej]

@Steveiwonder ,

is this a possible solution?
https://dapper-tutorial.net/knowledge-base/14814972/dapper--map-to-sql-column-with-spaces-in-column-names

[louagej]

Hi @Steveiwonder ,

This feature request was already a long time on my trying to complete list, but I never got time for it.
Today I spent a couple of hours trying to get this working.

The databases are Microsoft Dynamics 365 designed by Microsoft, so there is no change we can change the column names.
But I think I found a possible workaround for this problem.
I tried to change the code myself, but since I'm not a dotnet developer... :(
But I think I know the place were it could be fixed:

public static string GetUpdateColumns(
            this IList<ColumnConfig> columns,
            string paramPrefix = null)
        {
            return string.Join(", ", columns.Where(x => !x.Ignore).Select(x => $"[{x.Name}] = @{paramPrefix}{x.Name}"));
        }

Before and after the {paramPrefix}{x.Name}, there should be a double quote added to the string.
So the SQL value return the would be:

 "UPDATE [dbo].[CRONUS BELGIË NV$Contact] SET [Search Name] = @"Search Name" WHERE [No_] = @No_"

This way the SQL variables should be able to contain spaces

[Steveiwonder]

Hey @louagej, the issue was with the rows being passed back into the UPDATE query, and in your case the column name having spaces in them, SQL variable/params cannot contain spaces.

The solution is to replace all spaces inside a parameter with '_', so in your case the update query will look like this when generated

UPDATE [dbo].[CRONUS BELGIË NV$Contact] SET [Search Name] = @Search_Name WHERE [No_] = @No_

The fix is available in
https://www.nuget.org/packages/DataMasker/2.0.57
https://github.com/Steveiwonder/DataMasker/releases/tag/2.0.57

I'll leave this issue open for a few days, can you please let me know if this works for you?

Thanks

Steve

[louagej]

@Steveiwonder ,

this is working perfect like this.

I ended up updating a Column that has a space in the name and with an SQL Select statement

Thanks a lot!