Activity overview
Latest activity by dwperry
Looking forward to it 8) / comments
Looking forward to it 8)
"With luck the point release will be out by Christmas. "
Do you know if you are anywhere near this release yet? / comments
"With luck the point release will be out by Christmas. "
Do you know if you are anywhere near this release yet?
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. / comments
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.
Ahhh! That would explain it... [image] / comments
Ahhh! That would explain it...
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 / comments
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 respon...
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. / comments
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 unders...
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 / comments
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)
Th...
CAST AS NVARCHAR(0) error
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 synchroniz...