CAST AS NVARCHAR(0) error

dwperry

Hi,

I am raising this issue although it was already raised in the "previous versions" forum as the problem still seems to exist in 6.0.0.1124 using SQL Server 2000.

I have a problem when synchronizing data between columns that are sql_variant type where both rows contain blank strings. The error returned is provided below and is caused up by the following invalid statement - CAST(N'' AS nvarchar(0)).

No differences are found between the two rows in the previous version I was using - 3.3.7.256

The error appears to be something to do with the underlying data type of the variant column because if i convert the field to a nvarchar(50) and then back to a sql variant the CAST statement below is then correct - ie CAST(N'' AS nvarchar(50)).

On further examination it appears that the MaxLength property returned by the SQL_VARIANT_PROPERTY function for one of the databases returns zero. I do not believe this to be a valid value for this property but have no idea as to how this value has been set. I use a VB client and ADO 2.7 to write the data into the table.

Using Enterprise Manager to create the tables and data always appears to syncronise correctly - with underlying datatables of nvarchar and MaxLength of 60. No amount of fiddling with the columns can I get it to fail.

Databases can be provided if requested.

---

ERROR

---

The following error message was returned from the SQL Server:

[1001] Line 7: Length or precision specification 0 is invalid.

The following SQL command caused the error:

-- Pointer used for text / image updates. This might not be needed, but is declared here just in case
DECLARE @pv binary(16)

BEGIN TRANSACTION

-- Update 1 row in [dbo].[DT_ADDRESS]
UPDATE [dbo].[DT_ADDRESS] SET [Tel_Number]=CAST(N'' AS nvarchar(0)) COLLATE SQL_Latin1_General_CP1_CI_AS WHERE [Ndx]=13036

richardjm

I have looked into this error but didn't get a satisfactory solution for how to enter an empty string in a variant column. The best solution I came up with was (replicate('',0)) but I'm not entirely happy with it.

Why would you ever have an empty string variant data in a column?

We're currently working on 6.1 so could get a fix in there if there's a nice enough solution.

dwperry

Hi Richard,

Thanks for the quick reply. It's easy enough to get an empty string into the sql_variant column using an INSERT statement.

INSERT INTO ([Variant_Field]) SELECT CAST('' as nvarchar)

This however sets the MaxLength property for that field, correctly, to 60

I will continue to look into how the maxlength property has been set to 0 in some rows of my table as this appears to be the root of the issue.

David

richardjm

That will be the issue as I try to match the data size and of course your data size being 0 means I try to use the 0 without actually checking. If I the insert without the size it means the variant strings would never be identical as the maxlength of them would be different offering you to migrate each time which is less than ideal.

dwperry

After some further investigation I get what you mean by the REPLICATE statement - it does indeed duplicate the issue.

Having looked at the code I am using to update the records I think I now understand how VB6 using ADO2.7 also causes this.

If you have a VB Variant variable declared and you set this to equal a blank string and then you set your ADO field object (sql_variant) Value to equal the VB Variant and then call BatchUpdate the MaxLength property =0

I have not been able to find out if this is behavior by design but it would seem logical to be able to set the value of a sql_variant to the value of a VB Variant without this issue.

If you manually convert the variant to a string before you set the ADO field then the field gets the type varchar and its standard length (30)

'Illustrative (non working) code
dim vData1 as Variant
dim vData2 as Variant
dim aField as ADODB.Field

vData1 = ""

'This line will set MaxLength = 0
afield.value = vData1

vData2 = vData1

'This line will set MaxLength = 30
afield.value = CSTR(vData2)

'Show that both values are the same - returns TRUE
Msgbox vData1 = vData2

'Show that both variant types are the same - returns TRUE
Msgbox TypeName(vData1) = TypeName(vData2)

So the difference appears to be that sql_server (or ADO) interprets vData1 as a NULL STRING and vData2 as a String of length zero.

Although I can change my code to work around this problem I have many existing live databases that contain NULL Strings that I need to compare.

Would the REPLICATE() method of matching really be awkward? Would you not only use it when the variant contained a NULL string (ie. had zero maxlength) - otherwise do as normal.

richardjm

OK I've put the fix in so now SQL Data Compare 6.1 can generate the following for your data....

-- Add rows to [dbo].[SDC10]
SET IDENTITY_INSERT [dbo].[SDC10] ON
INSERT INTO [dbo].[SDC10] ([i], [data]) VALUES (1, CAST(N'Hello' AS varchar(30)) COLLATE Latin1_General_CI_AS)
INSERT INTO [dbo].[SDC10] ([i], [data]) VALUES (2, CAST(N'' AS varchar(30)) COLLATE Latin1_General_CI_AS)
INSERT INTO [dbo].[SDC10] ([i], [data]) VALUES (3, replicate('',0) COLLATE Latin1_General_CI_AS)
INSERT INTO [dbo].[SDC10] ([i], [data]) VALUES (4, CAST(N'Hello' AS nvarchar(15)) COLLATE Latin1_General_CI_AS)
INSERT INTO [dbo].[SDC10] ([i], [data]) VALUES (5, CAST(N'' AS nvarchar(15)) COLLATE Latin1_General_CI_AS)
INSERT INTO [dbo].[SDC10] ([i], [data]) VALUES (6, replicate(N'',0) COLLATE Latin1_General_CI_AS)
INSERT INTO [dbo].[SDC10] ([i], [data]) VALUES (7, NULL)
SET IDENTITY_INSERT [dbo].[SDC10] OFF
-- Operation applied to 7 rows out of 7

With luck the point release will be out by Christmas.



HTH

dwperry

Thanks very much, and can i just say that not only are your products awesome but your help desk seems, if anything, even better. You turned this issue around in under 24 hours with prompt responses and a positive outcome.

Many thanks,

David Perry

richardjm

Oh well that's possibly because I'm not help desk.

Richard Mitchell
Development Manager
Project Lead SQL Data Compare 6
Professional Geek
C# Guru
Factotum

dwperry

Ahhh! That would explain it...

dwperry

I'd be happy to help out with testing that change if you thought it worthwhile. - My databases are made almost entirely of sql_variants storing lots of different underlying types.

dwperry

"With luck the point release will be out by Christmas. "

Do you know if you are anywhere near this release yet?

richardjm

Luck wasn't on our side and we thought that rather than get something out mostly tested just before Christmas we'd do it afterwards.

We're in the final testing now and next week is looking likely.

(We've added temporary file compression as an option too )

dwperry

Looking forward to it 8)