Insert Nulls into Microsoft SQL Table
Paul Haag asked 6 years ago
I am trying to load data from CRM into a SQL table (destination type = Microsoft SQL Table), but SmartConnect is trying to insert every null value as either an empty string or an empty string converted to a specific data type.
This is the SQL string it’s generating even though every column in my table allows nulls:
INSERT INTO [dbo].[CrmContributions] ([SynchedBankAccount],[BatchCrmId],[ReceivedOn],[Job],[ContributionId],[FundId],[GLAccount],[BankAccount],[Dimension],[SynchedGLAccount],[SynchedDimension],[SynchedJob],[Amount],[StateCode],[DepositCrmId],[SynchedAmount],[OriginalDepositCrmId],[PaymentMethod],[AdjustmentDate]) VALUES ( ”, ‘e7776531-9a5d-e711-80df-9cb654b2bbec’, CONVERT(datetime, ‘6/30/2017 4:00:00 AM’) , ‘MISSIONS 2017 Haiti Fall’, ‘421d6f0e-9b5d-e711-80df-9cb654b2bbec’, ’70a50904-975d-e711-80df-9cb654b2bbec’, ‘MISSIONS’, ‘VNB Operating’, ‘MISSIONS’, ”, ”, ”, ‘20.0000’, CONVERT(int, 0) , ”, ”, ”, CONVERT(int, 722930000) , CONVERT(datetime, ”) )
Ultimately, this fails because, “Conversion failed when converting from a character string to uniqueidentifier,” because it is trying to insert an empty string instead of the NULL value. Is it possible to control this?
This is the SQL string it’s generating even though every column in my table allows nulls:
INSERT INTO [dbo].[CrmContributions] ([SynchedBankAccount],[BatchCrmId],[ReceivedOn],[Job],[ContributionId],[FundId],[GLAccount],[BankAccount],[Dimension],[SynchedGLAccount],[SynchedDimension],[SynchedJob],[Amount],[StateCode],[DepositCrmId],[SynchedAmount],[OriginalDepositCrmId],[PaymentMethod],[AdjustmentDate]) VALUES ( ”, ‘e7776531-9a5d-e711-80df-9cb654b2bbec’, CONVERT(datetime, ‘6/30/2017 4:00:00 AM’) , ‘MISSIONS 2017 Haiti Fall’, ‘421d6f0e-9b5d-e711-80df-9cb654b2bbec’, ’70a50904-975d-e711-80df-9cb654b2bbec’, ‘MISSIONS’, ‘VNB Operating’, ‘MISSIONS’, ”, ”, ”, ‘20.0000’, CONVERT(int, 0) , ”, ”, ”, CONVERT(int, 722930000) , CONVERT(datetime, ”) )
Ultimately, this fails because, “Conversion failed when converting from a character string to uniqueidentifier,” because it is trying to insert an empty string instead of the NULL value. Is it possible to control this?
Answers
Patrick Roth Staff answered 6 years ago
Paul,
No, you don’t have full control of what gets sent. SmartConnect won’t take “NULLS” at all – it’ll always convert a null to an empty string.
The only workaround that I can think of is to put in a value like an empty GUID with all zeroes. make a calculation so that if the string is empty use that ortherwise use the value you have.
Then use an ‘after map’ task to run an update against your table looking for 000-0000000-etc and set then set to NULL. Do that with any other column that is having this issue (which would be them all).
There was talk about changing SC to handle nulls in SQL/CRM/etc but hasn’t happened as far as I’ve seen.
No, you don’t have full control of what gets sent. SmartConnect won’t take “NULLS” at all – it’ll always convert a null to an empty string.
The only workaround that I can think of is to put in a value like an empty GUID with all zeroes. make a calculation so that if the string is empty use that ortherwise use the value you have.
Then use an ‘after map’ task to run an update against your table looking for 000-0000000-etc and set then set to NULL. Do that with any other column that is having this issue (which would be them all).
There was talk about changing SC to handle nulls in SQL/CRM/etc but hasn’t happened as far as I’ve seen.