mise à jour: voici mon Solution
J'ai une table définie comme suit: J'ai un code: erreur: Erreur lors de la tentative de bulkcopy Table CSVRF_References J'ai regardé partout et je n'arrive pas à trouver une réponse pour cela. La classe datatable avec des colonnes correspondant aux noms de colonne de table et Types de données. Le DataTable est rempli avec dbnull.value dans créatedon , lastupdatedon et lastupdateduser . référence est déjà généré. Lorsque j'appelle le code suivant, j'obtiens l'erreur ci-dessous.
System.InvalidoperationException: la colonne 'Createon' n'autorise pas dbnull.value.
à System.Data.SQLCLIENT.SQLBULKCOPY.CONVERTVALUE (valeur d'objet, métadonnées _sqlmetadata, Boolean Isnull, Boolean et Issqltype, Boolean & CoercedToudaFeed)
sqlbulkcopy semble ne pas honorer les valeurs par défaut, même si elle le dit. Qu'est-ce que je fais mal ici?
3 Réponses :
lire la documentation concernant Cependant, dans ce cas, je soupçonne que la documentation est subtilement incorrecte; Si ce n'est pas incorrect, il est certainement trompeur. P> Comme vous l'avez observé, avec un champ non nullable avec une contrainte par défaut (dans ce cas comme test, essayez de créer une deuxième table qui imite le premier, mais cette fois, rendez le Un autre test, en utilisant les mêmes (champs nullables ) Tableau, Effectuez le SQLBulkCopy uniquement cette fois-ci Utilisez l'attribut Ce comportement est similaire à celui Exécution d'une "vanille" instruction T-SQL pour insérer des données dans la table. p> à l'aide de la table d'origine (champs non nullables) à titre d'exemple, si vous exécutez P> sqlbulkcopy code>, en particulier sqlbulkcopyoptions , je dessinerais la même conclusion que vous l'avez fait: SQL Server devrait être" intelligente "suffisamment pour utiliser la contrainte par défaut, le cas échéant, surtout que vous n'utilisez pas le sqlbulkcopyoption.ebleNulls code> attribut. getDate () code>) Le SQLBULKCOPY échoue avec le Erreur susmentionnée. p> créatedon code> et lastupdatedon code> champs nullables. Dans mes tests, utiliser les options par défaut ( sqlbulkcopyoption.default code>) Le processus fonctionne sans erreur et strong> créatedon code> et lastupdatedon code> Les deux ont la valeur DateTime correcte peuplée dans la table malgré le fait que les valeurs de la DataTable pour ces champs étaient dbnull.value code>. p> sqlbulkcopyoptions.keeweNulls code>. Je suppose que vous verrez les mêmes résultats que je l'ai fait, c'est-à-dire Createon code> et LastUpDatedon code> sont tous deux null dans la table. P> INSERT INTO csvrf_References ([Type], [Location], [Description]
VALUES ('test', 'test', 'still testing')
Oui, je m'attends à ce que SQL Server remplisse la colonne avec la valeur par défaut lorsqu'une null est fournie. Voir: Stackoverflow.com/a/4244132/3874334 Cet exemple démonte les champs que je quitterai probablement toujours null, mais j'ai Autres tables où je vais avoir des colonnes contenant des données ainsi que des nulls.
@Jamesnix Ce n'est pas la manière dont SQL Server par défaut fonctionne, avec ou sans SQLBULKCopy. Les valeurs par défaut ne sont appliquées que lorsque la colonne est non spécifiée . Si vous spécifiez NULL pour une colonne, il tentera de le rendre null (qui provoquera une erreur si elle est déclarée non nulle) et la valeur par défaut ne sera pas utilisée. Lorsque le DOC dit " ... observera toutes les valeurs par défaut. " signifie que les valeurs par défaut seront observées par SQLBulkCopy de la même manière qu'elles sont observées par toute autre commande SQL.
pour la partie 1, "champ qui n'est pas null avec une valeur par défaut", vous ne devez pas envoyer le champ en premier lieu. Il ne devrait pas être cartographié. Il n'est pas nécessaire de changer ce champ pour accepter les nulls juste pour cela.
pour la partie 2, "champ null avec une valeur par défaut", que sera em> pour obtenir la valeur par défaut lors du passage dans dbnull.value, tant que vous n'avez pas le jeu SQLBULKCOPTIONOPTIONS à car il y a une certaine confusion sur la SQLBULKCOPYOption de Préservez les valeurs NULL dans la table de destination, quel que soit les paramètres des valeurs par défaut. Lorsqu'il n'est pas spécifié, les valeurs NULL sont remplacées par des valeurs par défaut, le cas échéant. P>
blockQuote> Cela signifie qu'un ensemble de données de DataColumn à Pour être clair sur la distinction: strong> p> Si un champ de la base de données est défini sur passe dans le champ (c'est-à-dire qu'il pas em> ramasser la valeur par défaut), auquel cas il ne peut jamais être défini sur ne passe pas du tout dans le champ (c'est-à-dire ne l'a pas dans la clé ou la requête ou le datareader ou tout ce qui est envoyé en tant que source, auquel cas vous n'avez peut-être pas besoin de spécifier la collection code> ColumnMappings Code> du tout p> li>
Si le champ est dans la source, vous devez spécifier la collection CODE> ColumnMappings CODE> afin que vous puissiez quitter ce champ des mappages. P> LI>
ul> li>
réglage ou non réglage, Si un champ de la base de données est défini sur ne passe pas du tout dans le champ (c'est-à-dire ne l'a pas dans la clé ou la requête ou le datareader ou tout ce qui est envoyé en tant que source, auquel cas vous n'avez peut-être pas besoin de spécifier la collection code> ColumnMappings Code> du tout p> li>
Si le champ est dans la source, vous devez spécifier la collection CODE> ColumnMappings CODE> afin que vous puissiez quitter ce champ des mappages. P> LI>
ul> li>
passe dans le champ défini sur une valeur qui n'est pas passe dans le champ comme Voici un test simple pour voir comment le mot clé code> par défaut fonctionne: p> résultats: p > keepnulls code>, sinon il insérera une base de données réelle null code>. p> Keepnulls code> , regardons sa définition: p>
dbnull.value code> sera inséré sous forme de base de données null code>, même si la colonne a une contrainte par défaut, Si em> l'option keepnulls code> est spécifiée. Il n'est pas spécifié dans votre code. Qui conduit à la deuxième partie qui dit dbnull.value code> sont remplacées par "Valeurs par défaut", le cas échéant. Ici "applicable" signifie que la colonne a une contrainte par défaut définie dessus. Par conséquent, lorsqu'une contrainte par défaut existe, une valeur non- dbnull.value code> sera envoyée dans le cas échéant, tandis que dbnull.value code> devrait em> traduire vers le Mot-clé SQL Par défaut code>. Ce mot clé est interprété dans une instruction insertion en prenant la valeur de la contrainte par défaut. Bien sûr, il est également possible que sqlbulkcopy code> si émetteur des instructions d'insertion individuelles, pourrait simplement laisser ce champ hors de la liste de colonnes si défini sur NULL pour cette ligne, qui prendrait la valeur par défaut. Dans les deux cas, le résultat final est que cela fonctionne comme prévu. Et mes tests montrent que cela fonctionne effectivement de cette manière. P>
non null code> et a une contrainte par défaut définie dessus, vos options sont les suivantes: p>
dbnull.value code> p> li>
keepnulls code> ne modifie pas le comportement ci-dessus. p> li>
ul> li>
null code> et contient une contrainte par défaut définie dessus, vos options sont les suivantes: p>
dbnull.value code>, auquel cas il sera défini sur cette valeur et ne décroche pas la valeur par défaut p>
dbnull.value code>, auquel cas l'effet est déterminé par si sqlbulkcopyoptions code> est passé et a été réglé sur KeepNulls Code>: P>
keepnulls code> est pas em> définir choisira la valeur par défaut p> li>
keepnulls code> est em> Set laissera le champ défini sur null code> p> p> l>
ul> li>
ul> li>
ul>
Col1 CreatedOn LastUpdatedOn
1 2014-11-20 12:34:31.610 2014-11-20 12:34:31.610
2 2014-11-20 12:34:31.610 2014-11-20 12:34:31.610
3 2014-11-20 12:34:31.610 NULL
4 2014-11-20 12:34:31.613 3333-11-22 00:00:00.000
Vous disent que vous avez pu obtenir cela pour que cela fonctionne avec SQLBulkCopy ou que cela devrait fonctionner car dbnull.value devrait traduire vers par défaut lorsqu'il y a une contrainte par défaut sur le champ question? Pour moi, en utilisant .NET 4.5 et SQL Server 2012, je n'ai pas pu obtenir que SQLBulkCopy fonctionne de passer à Dbnull.Value à un champ non nullable avec une contrainte par défaut.
@Timlentin: hé là-bas. Pour clarifier, je dis que a) j'ai reçu cela pour travailler via sqlbulkcopy , et b) que vous ne pouvez pas Spécifier un champ marqué pas null < / Code> du tout si vous souhaitez récupérer la valeur par défaut (que j'ai dit dans la partie 1). Partie 2 Où Je mentionnerai la traduction de par défaut concerne les champs nullables, qui couvre ce que James a dit dans le commentaire de votre réponse concernant "Autres tableaux où je disposerai de colonnes contenant des données ainsi que des nulls". Mais oui, il est confus que sqlbulkcopy poignées dbnull.value basé différemment sur la base du champ déclaré null vs non nul .
@TIMLENTINE: Je viens de mettre à jour ma réponse avec une liste de puces qui espérons espérons que les différents scénarios sont plus clairement.
Merci pour la liste, cela aide à clarifier vos points. (Je pensais que j'avais manqué une façon de le faire fonctionner comme l'OP décrit dans sa question, mais ce n'est pas le cas.) Le comportement comme se rapporte à dbnull.value est vraiment original.
Si seulement les documents de la SEP indiquaient cela pivotant! Merci pour l'explication.
@Jamesnix Oui, il est certainement déroutant en raison d'être un comportement incohérent. dbnull.value est tout simplement bien pour utiliser une valeur par défaut sur un champ nullable, alors pourquoi pas aussi sur un champ NON NULL? La seule raison à laquelle je puisse penser, c'est qu'il existe une étape de validation qui se produit d'abord, ce qui compare les données au schéma, mais ne sont pas au courant de la valeur groupcopypoptionoption. Je soupçonne que ce comportement n'est pas intentionnel et n'est pas inconnu par les développeurs (qui sonne comme un bogue) ou connu, mais "ne valent pas la peine d'être fixé" ou "pas encore corrigé". Dans les deux cas, :( .
Premièrement, merci pour l'explication détaillée. Une clarification cependant. Si un champ de la DB est défini sur NO NULLL et que vous avez une contrainte par défaut définie, et que vous ne passez pas du tout dans le champ (il récupère donc la valeur par défaut) et que vous ne l'avez pas dans la source (c.-à-d. Le 4ème point de bullet), vous avez dit "Vous n'avez peut-être pas besoin de spécifier la collection de colonnes." J'ai eu un comportement étrange avec cela; J'ai des non-nuls, mais pas ceux définis par la contrainte par défaut. Pour obtenir les valeurs attendues, je devais avoir les colonnes dans la source, mais laissez-les sortir de la cartographie (c'est-à-dire le 5ème point de bullet).
@RRREEEE YER Bienvenue, et merci pour cette information. Votre colonne Not NULL avec la contrainte par défaut: Quelle est sa position ordinale dans la table? Est-ce la dernière et la dernière colonne la plus droite, ou au milieu? Je suppose que c'est au milieu? Si je suis correct, je suppose que cela remplit des colonnes de la position ordinale la plus basse (c'est-à-dire la plupart des gauche), comme indiqué dans Sys.Columns . Dans ce cas, cela fonctionnerait probablement si le Not NULL avec une colonne par défaut était à la fin (c'est-à-dire la position ordinale la plus élevée), que je suppose que je suppose que ce que je voulais dire par " pourrait pas besoin de spécifier les mappages" . Si vous pouvez confirmer, je peux mettre à jour.
J'ai deux pas NULL de colonnes avec des contraintes par défaut et ils sont la seconde dans la dernière et dernière colonnes. Je les ai laissés hors de mon genre, mais le comportement était bizarre; L'une des colonnes est définie sur par défaut (@@ spid) mais quand je suis copié en volume dans 1000 rangées, j'ai fini par des valeurs 0 à 999 dans la colonne. Au début, j'ai préféré ne spécifiant pas les colonnes dans le jeu de données, mais ma préférence a changé. Maintenant, je veux être explicite. Lorsque j'ai essayé uniquement la dernière colonne laissée, et sans mappage, cela fonctionne comme prévu, alors votre mise à jour serait correcte, si je retiens l'étrangeté de ce que je fais.
@RRREEEE Quelle est la valeur par défaut de l'autre colonne NON NULLL? Et lequel est le dernier et lequel 2ème à la dernière fois? Le comportement était-il également étrange pour cette autre colonne NO NULL? pour la colonne avec par défaut (@@ spid) , étaient ceux 0 à 999 valeurs de la même manière pour toutes les lignes d'une exécution particulière de witetoServer () , ou les valeurs soient-elles différent par rangée dans une exécution particulière?
La valeur par défaut de l'autre colonne est le résultat d'une fonction, et (via un peu plus de complexité) me donne le login_time de sys.dm_exec_sessions. La définition de la table se termine par session_id smallint non null par défaut (@@ spid), session_login_time Datetime non null par défaut (dbo.getsanceLogintime ()) . Le comportement de l'autre colonne n'était pas aussi étrange: j'ai eu la même valeur dans toutes les lignes (dont j'ai besoin), mais je ne sais pas si c'était la bonne valeur. Une invocation de WritetoServer () avec 1000 lignes entraîne les valeurs 0 à 999 pour session_id.
@RRreeee intéressant, surtout comment la colonne session_id aura des valeurs variables. Avez-vous essayé de spécifier le ColoClapping avant d'ajouter les deux colonnes à la source DataTable ? En outre, avez-vous la capacité / le temps d'exécuter SQL Profiler tout en l'exécutant de la manière originale qui devrait produire les valeurs "impaires"? Je suis curieux de savoir ce que l'instruction réelle insérer ressemble.
Le colonne fait partie de la sqlbulkcopy, non du type de données, alors existe-t-il un moyen de faire la cartographie en premier? Je n'aurai pas le temps au cours des prochains jours pour diriger le profileur, même si je suis plutôt curieux aussi. Je vais essayer de revenir à cela, car d'autres seront probablement intéressés (voir la partie 2 de ce commentaire).
Partie 2: Ce que je fais réellement travaille avec toujours crypté, essayant de soutenir les processus stockés qui avaient inséré des données fournies via TVP ou Table Table, mais qui doit maintenant utiliser SQLBulkCopy, car c'est le seul moyen pris en charge pour insérer des données cryptées. La correspondance la plus proche est de sqlbulkcopy à une table optimisée de mémoire que la Proc est ensuite accessible. Une stratégie de sécurité filtre sur ces deux colonnes, elle agit comme une table Temp. Ma solution est la seule il y a maintenant, donc Microsoft peut me poser les mêmes questions.
"La colonne SQLBulkCopy n'autorise pas dbnull.Value", une erreur est due à la table de source et de destination a une commande de colonne différente.
J'ai reçu cette erreur lorsque vous transférez des données d'une table de base de données à une autre. Comme @james Nix résume, la cause première est la manière dont nous définissons la propriété par défaut de la colonne de la colonne. Merci.
C'est la bonne réponse! Je cherchais des problèmes dans la colonne d'entier MySQL, puis mon code et des valeurs null possibles, etc. Mais c'était la chose idiote! Merci