This is mostly a bug fix release, but with one new SQL feature implemented which could be very useful for testing and ALM usage scenarios.
This SQL statement pauses your script for a period of time. This can be useful if you need to wait for some async operation to happen in the background after you run an
DELETE command. You could also leave a script running in a loop to see how your data changes over time. For example, while you’re running a data migration:
DECLARE @count AS INT; WHILE 1 = 1 BEGIN SET @count = (SELECT count(*) FROM account); PRINT CONVERT (VARCHAR, CURRENT_TIMESTAMP, 120) + ' - ' + CONVERT (VARCHAR, @count); WAITFOR DELAY '00:10'; END
This produces a log in the Messages pane that updates every 10 minutes with the number of accounts in your system so you can monitor the progress of your data import process. Just hit the Stop button in the toolbar when you’re done.
WAITFOR statement you can either specify a
DELAY or a
TIME. Both should be in the format
WAITFOR DELAY '12:00' will pause your script for 12 hours, while
WAITFOR TIME '12:00' will wait until 12pm.
This release fixes a few bugs with how the
null literal is handled in different situations, particularly with
One other useful enhancement is with clearing out polymorphic lookup fields. Normally if you want to set the value of one of these lookups that can refer to more than one other table (like
<anything>.ownerid) you also have to set the corresponding
___type field. For example, if you write:
UPDATE contact SET parentcustomerid = 'BBD2BDBF-2E1B-4225-93E2-A17FC8F55761' WHERE firstname = 'Mark'
it won’t know if that ID relates to a contact or an account. Instead you have to write:
UPDATE contact SET parentcustomerid = 'BBD2BDBF-2E1B-4225-93E2-A17FC8F55761', parentcustomeridtype = 'account' WHERE firstname = 'Mark'
null is a special case, so you can now remove any existing values from a polymorphic lookup field without having to specify the type:
UPDATE contact SET parentcustomerid = null WHERE firstname = 'Mark'
If you query the metadata using a SELECT statement like:
SELECT displayname FROM metadata.entity WHERE logicalname = 'Account'
this would previously not produce any results due to the incorrect case of the logical name (should be all lower case). These filters are now translated to the correct case so you get the expected results.
Because filters have to be supplied in the correct case when SQL 4 CDS issues the metadata query to the server, filters on other columns where SQL 4 CDS doesn’t know what case the value will be in can’t be sent to the server. In these cases SQL 4 CDS will instead retrieve all the possible values and perform the filtering itself. This means you’ll get the correct results but the queries may take a little longer to run.
10 thoughts on “SQL 4 CDS 6.2 Released”
Hi Mark, seems this new version the Bypass custom plugins is not longer working.
Can I add a code on my UPDATES while this bug gets fixed?
Thanks for letting me know. There isn’t a workaround for this but I should be able to get an update out shortly. If you need something quicker you can download an earlier release from GitHub.
This should be fixed now in 6.2.1
There seem to be an issue with the OPTION (USE HINT ‘BYPASS_CUSTOM_PLUGIN_EXECUTION’)) when the “Use TDS Endpoint where possible” option is activated, as we get the following error :
“‘BYPASS_CUSTOM_PLUGIN_EXECUTION’ is not a valid hint.
See the Execution Plan tab for details of where this error occurred”
And in the Execution plan tab we can see that the connector sent the instruction through TDS altought there is a “non SQL” hint.
Thank your for this great tool.
Thanks, I’ve got an issue on GitHub about this at https://github.com/MarkMpn/Sql4Cds/issues/215. This should be fixed in the next release. In the meantime you can use the option to bypass plugins in the settings page instead of using the query hint.
First let me say that I think that ‘SQL 4 CDS’ is a great tool and coming from a SQL-Server background it’s enabled me to get up and running with the dataverse really quickly.
After using the tool for some time for selects I’ve just attempted the first Update and am having an issue.
I have a attribute lets call it flag which current has values of null,0,1 and I want to set all null values to 0.
Update table set flag-0 where flag is null
but is gives me the error:-
Error updating table – The given key was not present in the dictionary.
I’m assuming that because the value of the attribute is null, the attribute does not exist in the object and therefore can’t be updated.
Is there any way round this in ‘SQL 4 CDS’? I did try searching through the blog to see if it had come up before, but no luck.
Oh and it would be great if the query results could be saved as a csv or/and Excel.
Thanks for any help you can give and again congrats on such a useful tool!
I haven’t seen this before, but it definitely should work. Can you reproduce it with standard OOB fields on account/contact/… so I can get the same error?
Think we’ve traced the issue to a custom plug-in. I’ll let you know if fixing the custom plug-in resolves it.
Just discovered SQL 4 CDS… and it’s great.
So far just one question: if I save a new query I get the ‘Save as’ dialog box. If I re-save a saved query, it just saves so it writes over the original file. Is there a way of re-accessing ‘Save As’ so I can save as a different name / check or change where I’m saving?
Good question – no, that’s currently not possible. The workaround for now would be to copy & paste the query to a new query tab and save it again from there. I’ve logged this as an issue on the GitHub site now and hopefully I’ll include something for this in a future update.