Multilingual support

Hi,

My project has a website that uses a group user account to access our database. To identify who made a change, we've built a procedure to pass the correct username to DB Audit. However, DB Audit's 'audit_login' column is a varchar(128), but my data could be nvarchar (unicode). Is there a way to build the tables using a nvarchar column instead of varchar?

Thanks,
Dave

Hi,

Are you referring to the system audit trail table db_audit.sys_audit_trail table or to audit trail tables audit_xxxxxxx used for the data-change auditing?

Regards

The audit trail tables audit_xxxxxxx used for the data-change auditing. Each tables has a 'audit_login' column as varchar(128). I need this to be nvarchar(128), if possible.

Thanks,
Dave

Unfortunately there is no easy way. But here is an idea... I'm thinking about creating a lookup table for storing user name codes as varchar values and full user names as nvarchar values. In the audit trail tables you would then save the code and use it as a reference to the lookup table, while in reports you would display nvarchar values. Basically DB Audit supports generating custom views in db_audit schema for data audit trail reports. When you run a report, the console first checks for defined V_SCHEMA_TABLE alias for the selected table. SCHEMA and TABLE here are placeholders for actual schema and table names, for example, V_DBO_MY_TRANSACTIONS. If such alias is found, it then checks for existence of user defined DB_AUDIT.V_SCHEMA_TABLE view and uses it as a data source for the report. In the view, you can join the audit trail table and lookup table and return nvarchar value and the console will report on that value. As far as I know DB Audit graphical console has some limitations on display of double-byte values, while the web-based console can display them just fine.

That might work. I may even be able to use the user table in conjunction with view to get the desired result. It's unfortunately I can't use DB Audit directly, but this is a decent workaround.

Thanks,
Dave

I just though of another method which doesn't require additional tables and joins, extra codes, etc... Technically you can convert nvarchar values to hexadecimal strings and store resulting strings in the existing varchar column audit_login, then in the reporting view, you can convert these values back to nvarchar format.

Here is a little code snippet demonstrating this idea

	Code:
	-- Unicode multi-byte value DECLARE @mb_string NVARCHAR(64) SET @mb_string = N'My Name' -- convert it to hex DECLARE @hex VARCHAR(128) SET @hex = CONVERT(VARCHAR(128), CONVERT(VARBINARY(126), @mb_string)) -- convert it back to Unicode SELECT CONVERT(NVARCHAR(64), CONVERT(VARBINARY(126), @hex))