|
| [WB19] setting NULL via Record to MSSQL nullable Column |
| Iniciado por guest, 17,abr. 2015 09:23 - 5 respuestas |
| |
| | | |
|
| |
| Publicado el 17,abril 2015 - 09:23 |
Hi,
I have a table in MSSQL connected via OLEDB that has a column (uniqueidentifier) that has the option "Allow Nulls" marked.
The analysis of the project from this table also displays this option set correctly.
[attachment 1450 Analysis.png]
So this also looks ok.
I create a record of the destination file
TmpRecord is Record = {DestFile,indFile}
and I fill this record from other sources like this
{"pRecord."+sFieldName} = sFieldValue
Now when I detect that there is a NULL value or 00000000-0000-0000-0000-000000000000 then I would like to also put a NULL in my record variable.
I do this via:
{"pRecord."+sFieldName}..Null = true also tried {"pRecord."+sFieldName} = null
But when I HADD or HMODIFY the record, I get an error:
'HModify' function, syntax 1
What happened?
OLE DB access error.
Error Number = 170137
Failure saving value of < xxxxxID > item present in <> file.
Check whether items are supported by your provider.
Value = <0>
Error code: 73001
Level: fatal error (EL_FATAL)
WD55 error code: 3001
System error message:
Description = Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.
Source = Microsoft OLE DB Provider for SQL Server
Error Number = -2147217887 (0x80040e21)
Dump of the error of 'WD190HF.DLL' module (19.0.102.2).
Identifier of detailed information (.err): 72801
Debugging information:
IEWDOLDB=113.9
Module=
Version=<19.0.14.0>
Provider: SQLOLEDB
User: xxxxxxx
Data source: xxxxxxxx
Database: xxxxxxxx
Unicode supported: 1
Page code of WL: 1252
Page code of the connection: UTF-16
MDAC Version = <6.1.7601.17514>.
[Data format]
Query Parameter Checked = <0> = <>
Query Parameter Needing conversion = <96> =
DecimalSeparator =
DateFormat =
[Cursor settings Match]
LockType = , Location = , Type = , Capacities = <10fef00>
LockType = , Location = , Type = , Capacities =
LockType = , Location = , Type = , Capacities =
[Recordset 1]
Settings = , Location = , Type = , Capacities = <10fef00>>
CapacitiesSupported =
CapacitiesNotsupported =
HFFunctionsSupported =
HFFunctionsNotsupported = <>
Fonction (7,16)
Additional Information:
EIT_NATIVECODE : <170137>
EIT_ADOCODE : <-2147217887>
EIT_BASECODE : <0>
EIT_ODBCDESCRIPTION :
EIT_LOGICALTABLENAME :
EIT_PILEWL :
When I set the record variable to GUID null string like this
{"pRecord."+sFieldName} = "00000000-0000-0000-0000-000000000000"
Then this works, but in the DB the value is not NULL but a blanco GUID !
How can I set a real NULL via a record variable via HADD or HMODIFY to a certain column ? This should be possible i guess :confused: What I'm doing wrong here ?
Thanks
Danny |
| |
| |
| | | |
|
| | |
| |
| Publicado el 17,abril 2015 - 10:31 |
Hi Danny,
Not sure if this would help but did you try assigning the character directly based on the encoding scheme:
E.g. :
x = charact(0) //ASCII or ANSI scheme - Position 0 = NULL
y = ansitounicode(charact(0))
Cheers,
Peter H. |
| |
| |
| | | |
|
| | |
| |
| Publicado el 17,abril 2015 - 10:41 |
why null in one uniqueidentifier field?
what is the data type of this field in the mssql database? |
| |
| |
| | | |
|
| | |
| |
| Publicado el 17,abril 2015 - 13:47 |
Peter,
Assigning a ansitounicode(charact(0)) does not work either ? Same error. <img src="/NG2013_WEB/ui/smiley/2.gif" align=absmiddle border=0 alt=":-(">
Paulo,
Yes the MSSQL datatype is an uniqueidentifier, but because the analysis does not know this type it is defined automatically as a Text of 38 when you update or load the analysis from an existing MSSQL database.
Thanks
Danny |
| |
| |
| | | |
|
| | |
| |
| Publicado el 17,abril 2015 - 15:46 |
i never used it with nulls, can you check in the SQL Server Management Studio or using some tool like wireshark what is the SQL sentence WB is genetating
From my experience sometimes this the only way we can check what WB is doing when converting HADD,... to the SQL format, sometimes the convertion is very strange. |
| |
| |
| | | |
|
| | |
| |
| Publicado el 17,abril 2015 - 22:33 |
Hi Danny,
I suggest you check with Tech Support...
Alternatively, to move on until you have (or will never have) an answer from TS you might create a 'Before Insert' trigger on the SQLServer side where you modify "" to NULL for the key field. In WX you just assign "" to the field...
Just my 2 cents,
Peter H. |
| |
| |
| | | |
|
| | | | |
| | |
|